SQL Server Performance

Query Gives Insufficient System Memory error

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by jbates99, Sep 30, 2008.

  1. jbates99 Member

    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
    ,TextFROM
    sys.dm_exec_query_stats as derCROSS
    APPLY sys.dm_exec_sql_text(der.sql_handle) as destWHERE
    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
  2. ghemant Moderator

  3. gurucb New Member

    Can you please retry the query and go the error log and share snapshot of error log. Better would be if you can run DBCC MemoryStatus before, during and after you run the query collect it in a file and share it then may be it will throw some light.

Share This Page