Find Queries Taking Most CPU (Processor)

If your box is CPU bound then this is the script you need. The first query will order the results based on the queries that have used the most CPU time since the SQL Server instance has been restarted (or the server has been rebooted). The second query orders the results based upon the average CPU time that each query takes. — Find queries that take the most CPU overall SELECT TOP 50     ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid)     ,TextData           = qt.text     ,DiskReads          = qs.total_physical_reads   — The worst reads, disk reads     ,MemoryReads        = qs.total_logical_reads    –Logical Reads are memory reads     ,Executions         = qs.execution_count     ,TotalCPUTime       = qs.total_worker_time     ,AverageCPUTime     = qs.total_worker_time/qs.execution_count     ,DiskWaitAndCPUTime = qs.total_elapsed_time     ,MemoryWrites       = qs.max_logical_writes     ,DateCached         = qs.creation_time     ,DatabaseName       = DB_Name(qt.dbid)     ,LastExecutionTime  = qs.last_execution_time  FROM sys.dm_exec_query_stats AS qs  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  ORDER BY qs.total_worker_time DESC   — Find queries that have the highest average CPU usage SELECT TOP 50     ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid)     ,TextData           = qt.text       ,DiskReads          = qs.total_physical_reads   — The […]

Continue reading ...

Find Queries Using Most Memory (IO)

This query returns back the queries that use the most IO. This can mean that either the query is reading from disk more than usual or occupying and utilizing a large amount of buffer cache. These are typical symptoms of queries that do not have the proper indexes or queries that simply read a lot of data. /********************************************************** *   top procedures memory consumption per execution *   (this will show mostly reports & jobs) ***********************************************************/ SELECT TOP 100 * FROM (     SELECT          DatabaseName       = DB_NAME(qt.dbid)         ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid)         ,DiskReads          = SUM(qs.total_physical_reads)   — The worst reads, disk reads         ,MemoryReads        = SUM(qs.total_logical_reads)    –Logical Reads are memory reads         ,Executions         = SUM(qs.execution_count)         ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)         ,CPUTime            = SUM(qs.total_worker_time)         ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)         ,MemoryWrites       = SUM(qs.max_logical_writes)         ,DateLastExecuted   = MAX(qs.last_execution_time)             FROM sys.dm_exec_query_stats AS qs     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt     GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ) T ORDER BY IO_Per_Execution DESC /********************************************************** *   top procedures memory consumption total *   (this […]

Continue reading ...

View Active Connections

With SQL Server 2005+, it is very easy to view the specifics of connection information. This is very useful because when troubleshooting slowdowns. Luckily there are a few dynamic management views that provide insight into connection and session information. The following query groups the connections according the program that is connected to SQL Server. This information can be spoofed however using a connection string. When running this query, you will find how important it is to add the application name to the query string. The query also shows the number of connections opened by each application. — By Application SELECT      CPU            = SUM(cpu_time)     ,WaitTime       = SUM(total_scheduled_time)     ,ElapsedTime    = SUM(total_elapsed_time)     ,Reads          = SUM(num_reads)     ,Writes         = SUM(num_writes)     ,Connections    = COUNT(1)     ,Program        = program_name FROM sys.dm_exec_connections con LEFT JOIN sys.dm_exec_sessions ses     ON ses.session_id = con.session_id GROUP BY program_name ORDER BY cpu DESC This next query groups the same information by user: — Group By User SELECT      CPU            = SUM(cpu_time)     ,WaitTime       = SUM(total_scheduled_time)     ,ElapsedTime    = SUM(total_elapsed_time)     ,Reads          = SUM(num_reads)     ,Writes         = SUM(num_writes)     ,Connections    = COUNT(1)     ,[login]        = original_login_name FROM sys.dm_exec_connections con LEFT […]

Continue reading ...

Find Dependent Objects

One new feature that SQL Server 2008 offers is more reliable dependency information. SQL Server 2005 offered a DMV (dynamic management view) called sys.sql_dependencies. It is now replaced by a more reliable sys.sql_expression_dependencies. The following script will show all the procedures that reference a given table name, along with the columns the procedure references also. Please note, this does not take into account any dynamic SQL. USE MYDatabase GO DECLARE @TableName VARCHAR(100) SET @TableName = ‘mytable’ SELECT  SourceSchema                  = OBJECT_SCHEMA_NAME(sed.referencing_id)  ,SourceObject                 = OBJECT_NAME(sed.referencing_id)  ,ReferencedDB                 = ISNULL(sre.referenced_database_name, DB_NAME())  ,ReferencedSchema             = ISNULL(sre.referenced_schema_name, OBJECT_SCHEMA_NAME(sed.referencing_id))  ,ReferencedObject             = sre.referenced_entity_name  ,ReferencedColumnID   = sre.referenced_minor_id  ,ReferencedColumn             = sre.referenced_minor_name FROM sys.sql_expression_dependencies sed CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ‘.’ + OBJECT_NAME(sed.referencing_id), ‘OBJECT’) sre WHERE sed.referenced_entity_name = @TableName AND sre.referenced_entity_name = @TableName

Continue reading ...

Find Resource Currently Involved in Blocking

SELECT DISTINCT     objname = object_name(p.object_id) FROM sys.partitions p JOIN sys.dm_tran_locks t1 ON p.hobt_id = t1.resource_associated_entity_id

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!