Query Gives 'Insufficient System Memory' error

[JohnBates]

hi experts,

This is 2005 Std SP2 on Windows 2003.

I'm trying to find the cause of a performance problem, so I found this query which supposedly finds queries/tables that are doing full table scans.

When I run the following SQL statement, 9 times out of 10, it will fail with Error 701 Insufficient system memory.

By removing the ORDER BY, it completes quickly but, of course I dont get the 30 highest counts.

SELECT TOP 30 DB_NAME(dbid) AS DatabaseName
,OBJECT_NAME(ObjectID) AS ObjectName

,der.creation_time
,der.Last_execution_time

,total_logical_reads , total_logical_writes, execution_count
,Text

FROM sys.dm_exec_query_stats as der

CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) as dest

WHERE DB_NAME(dbid) = 'AVNAPPDB'

ORDER BY total_logical_reads DESC

Is there any way I can get this to run faster (or maybe a better method of finding which tables are being fully scanned?)

Thanks, John

 

[mutley1]

Hi John,

Not teaching grandmother to suck eggs, but what is your Server min / max memory set to?

 

[JohnBates]

Thanks mutley1.

The server has 20 GB memory. SQK Server memory setting is min 0 and max 214....... dynamically adjusted.

John

 

[JohnBates]

.... and the OS is 2003 Enterprise. But the Std edition of MSSQL 2005.