asked on

SQL 2005 Not using Page File

Please can Anyone assist with the following problem;

We are running a query that uses the physical memory allocated to the SQL instance and on using all available physical memory the query fall over with the following error

Msg 701, Level 17, State 123, Line 1
There is insufficient system memory to run this query.

SQL Settings;

SQL 2005 x64 SP2 Enterprise
2 Node Cluster Environment 1 SQL Instance and Analysis services but the analysis services Service currently disabled.
Physical memory 8Gb with 6Gb allocated to the SQL instance
Lock pages in memory Set
AWE NOT enabled

On viewing Page file activity with performance monitor none of the page file is being used and hence query dies when all of allocated physical memory used up.

This is also backed up by the fact that when we reduce the allocated physical memory to 3Gb the query fallsover all the sooner.

No other Users or processes are running on this SQL instance at the same time.

Any help appreciated as this server is still in a pre-production phase and I am unable to put into full production until this issue is resolved.

Guy Hengel [angelIII / a3]

first of all: it is GOOD when sql server does NOT use the page file. otherwise it will just be slow (and I mean, really slow)
second: if you had a query that really used 6GB at once, you have a problem. either the query is simply wrong, or you have a server with not enough RAM.
third: that error message is usually NOT that you have run out of RAM, but either the query being incorrectly written (with that I do not mean pure syntax, btw), or actually some sql server internal limitation (could be a bug also).

please clarify the query on which you get that error.

wkyle

ASKER

This is run against a table that has 400 columns but only 1 row.

The query memory usage was observed by using performance monitor and looking at the mem usage for the SQLServer process which slowly increased until it maxed out at the memory allocated to the SQL instance (6GB) and died with the MSG.701 error. Even if the Query does need more that 6GB surely the Page File should have come into play ? THe page file was not used at all.

USE [DBNAME]
GO
/****** Object:  StoredProcedure [dbo].[swapover2]    Script Date: 11/14/2008 10:47:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Note: Uses a global temp table and cursor loops 
--idea is to try and swap one row of sql into a vertical result set
--exec swapover2 'dbo','tmp5_v2','*','record_type=164'
ALTER  procedure [dbo].[swapover2](@table_schema varchar(25), 
 @table_name varchar(25), 
 @columns varchar(500),
 @whereClause varchar(255)) as
set nocount on
if(@table_schema = '' OR @table_name = '' OR @columns = '' OR @whereClause = '') return
declare @colCount int
declare @columnName  varchar(40)
declare @sql VARCHAR(max) ; set @sql = ''
declare @i int; set @i = 0
declare tableCursor cursor for select column_Name from information_schema.columns 
  where  table_schema=@table_schema and table_name= @table_name 
  order by table_schema, table_name
set @colCount=@@ROWCOUNT
open tableCursor
fetch next from tableCursor into @columnName
while @@FETCH_STATUS = 0 begin
  if(charindex('|' + @columnName + '|', @columns) > 0) OR charindex('*', @columns) > 0 begin
  set @sql = @sql + ' select ''' + @columnName + ''' as Name, + cast(' + @columnName + 
  ' as varchar(255)) as Value from ' + @table_schema + '.' + @table_name + 
  ' where ' + @whereClause + '
  UNION'
  end
  fetch next from tableCursor into @columnName 
  set @i = @i + 1
end
deallocate tableCursor
set @sql = substring(@sql, 0, len(@sql) - len('UNION'))
exec(@sql)

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wkyle

ASKER

Thank you angelIII Brilliant the Stored Proc now runs in 6 seconds and has certainly helped me out of a hole. Any idea why the page file wasn't utilized rather than cause the query to fall over?

Guy Hengel [angelIII / a3]

the main change, in terms of performance, is the UNION instead of the UNION ALL.
UNION performs a implicit DISTINCT over the complete result. UNION ALL does not that.