[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

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.
0
wkyle
Asked:
wkyle
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
 
wkyleAuthor Commented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I made some small amendments to the procedure, without anything dramatic.

it worked fine for me:

CREATE procedure [dbo].[swapover2](@table_schema varchar(25), 
 @table_name sysname, 
 @columns nvarchar(500),
 @whereClause varchar(255)) as
set nocount on
if(@table_schema = '' OR @table_name = '' OR @columns = '' OR @whereClause = '') return
if(@table_schema IS NULL OR @table_name IS NULL  OR @columns IS NULL  OR @whereClause IS NULL ) return
 
declare @colCount int
declare @columnName  sysname
declare @sql nVARCHAR(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 ALL '
  end
  fetch next from tableCursor into @columnName 
  set @i = @i + 1
end
deallocate tableCursor
set @sql = substring(@sql, 0, len(@sql) - len('UNION ALL'))
--print @sql
exec(@sql)

Open in new window

0
 
wkyleAuthor Commented:
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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now