DaveWentzel.com            All Things Data

Useful Queries

Useful XML Queries

--view schemas installed in your db

select sys.xml_schema_collections.name

from sys.xml_schema_collections

 

--the xml_schema_namespace function gives information about a schema collection

select xml_schema_namespace('','')

 

Useful Execution Plan Queries

 

 

Basic Query Plan Metrics

SELECT * FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);

 

which query takes the most CPU:

select

highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time,

q.dbid, q.objectid, q.number, q.encrypted, q.[text] from

(select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs

order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q

order by highest_cpu_queries.total_worker_time desc

Alternatively, query against sys.dm_exec_cached_plans by using filters for various operators that may be CPU intensive, such as '%Hash Match%', '%Sort%' to look for suspects.

This will only show the queries that are currently in the cache.  So as things get removed from memory the query becomes less useful. 

For SQL 2000, use this query...it will also work in 2005.

/*

used to quickly determine the biggest CPU utilizes in the db by spid.

only reports the TOP 10 consumers.

default is 10 second compare.

*/

CREATE PROCEDURE CPUHeavyHitters @DelayInSeconds SMALLINT=10

AS

IF @DelayInSeconds NOT BETWEEN 1 AND 60

SET @DelayInSeconds = 10

 

SELECT cpu ,spid into #firstpass

FROM master..sysprocesses

WHERE spid > 50

 

DECLARE @SQL VARCHAR(100)

SELECT @SQL='WAITFOR delay ''00:00:'+right('0'+CONVERT(VARCHAR(2),@DelayInSeconds),2)+''''

 

EXEC (@SQL)

 

 

---------------------------------------------------

SELECT p.cpu - fp.cpu AS CPU, p.spid, p.program_name ,p.loginame ,p. hostname, p.last_batch

INTO #delta

FROM master..sysprocesses p

inner join #firstpass fp

on p.spid = u.spid

WHERE p.spid > 50

 

---------------------------------------------------

 

create table #IP (EventType varchar(30), Parameters varchar(30), EventInfo varchar(500), SPID int )

 

declare @spid int

declare CurP cursor for select TOP 10 spid from #delta ORDER BY cpu DESC

open CurP

fetch CurP into @spid

while @@fetch_status=0

BEGIN

SELECT @SQL='dbcc inputbuffer('+convert(varchar(5),@spid)+')'

insert into #IP (EventType, Parameters, EventInfo )exec(@SQL)

UPDATE #IP SET SPID=@SPID where SPID IS NULL

fetch CurP into @spid

END

close CurP

deallocate CurP

---------------------------------------------------

 

SELECT difference = p.cpu, p.spid, ISNULL(s.Eventinfo,'** SQL Only displayed for top 10 **') as SQLStmt, p.program_name ,p.loginame ,p. hostname, p.last_batch

FROM #delta p

left outer join #IP S

on p.spid = s.spid

ORDER BY 1 desc

 

 

 

Which query takes the most I/O:

SELECT DB_NAME(st.dbid) DBName

      ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName

      ,OBJECT_NAME(objectid,st.dbid) StoredProcedure

      ,max(cp.usecounts) execution_count

      ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO

      ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / (max(cp.usecounts)) avg_total_IO

      ,sum(qs.total_physical_reads) total_physical_reads

      ,sum(qs.total_physical_reads) / (max(cp.usecounts) * 1.0) avg_physical_read   

      ,sum(qs.total_logical_reads) total_logical_reads

      ,sum(qs.total_logical_reads) / (max(cp.usecounts) * 1.0) avg_logical_read 

      ,sum(qs.total_logical_writes) total_logical_writes

      ,sum(qs.total_logical_writes) / (max(cp.usecounts) * 1.0) avg_logical_writes 

FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle

  where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)

order by sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) desc

Again, as plans are aged out of cache the stats get stale.  This is really for active plans only. 

[Freelinking: unknown plugin indicator "2000 Version find the CPUDisk IO most consuming session|2000 Version"]

which procs take the longest duration:

SELECT DB_NAME(st.dbid) DBName

      ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName

      ,OBJECT_NAME(objectid,st.dbid) StoredProcedure

      ,max(cp.usecounts) execution_count

      ,sum(qs.total_elapsed_time) total_elapsed_time

      ,sum(qs.total_elapsed_time) / max(cp.usecounts) avg_elapsed_time

FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle

  where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)

order by sum(qs.total_elapsed_time) desc



Again, only for cached plans. 

active parallel queries:

From a running server, you can determine whether any active requests are running in parallel for a given session by using the following query.

select r.session_id, r.request_id, max(isnull(exec_context_id, 0)) as number_of_workers, r.sql_handle, r.statement_start_offset, r.statement_end_offset, r.plan_handle from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id = t.session_id join sys.dm_exec_sessions s on r.session_id = s.session_id where s.is_user_process = 0x1 group by r.session_id, r.request_id, r.sql_handle, r.plan_handle, r.statement_start_offset, r.statement_end_offset having max(isnull(exec_context_id, 0)) > 0

With this information, the text of the query can easily be retrieved by using sys.dm_exec_sql_text, while the plan can be retrieved using sys.dm_exec_cached_plan.

Procedure Cache Allocation

SELECT  TOP 6

LEFT([name], 20) as [name],

LEFT([type], 20) as [type],

[single_pages_kb] + [multi_pages_kb] AS cache_kb,

[entries_count]

FROM sys.dm_os_memory_cache_counters

order by single_pages_kb + multi_pages_kb DESC



This will return various types of plans:

  • CACHESTORE_OBJCP. These are compiled plans for stored procedures, functions and triggers.
  •  

    Basic Query Plan Metrics

    SELECT * FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);

     

    which query takes the most CPU:

    select

    highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time,

    q.dbid, q.objectid, q.number, q.encrypted, q.[text] from

    (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs

    order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q

    order by highest_cpu_queries.total_worker_time desc

    Alternatively, query against sys.dm_exec_cached_plans by using filters for various operators that may be CPU intensive, such as '%Hash Match%', '%Sort%' to look for suspects.

    This will only show the queries that are currently in the cache.  So as things get removed from memory the query becomes less useful. 

    For SQL 2000, use this query...it will also work in 2005.

    /*

    used to quickly determine the biggest CPU utilizes in the db by spid.

    only reports the TOP 10 consumers.

    default is 10 second compare.

    */

    CREATE PROCEDURE CPUHeavyHitters @DelayInSeconds SMALLINT=10

    AS

    IF @DelayInSeconds NOT BETWEEN 1 AND 60

    SET @DelayInSeconds = 10

     

    SELECT cpu ,spid into #firstpass

    FROM master..sysprocesses

    WHERE spid > 50

     

    DECLARE @SQL VARCHAR(100)

    SELECT @SQL='WAITFOR delay ''00:00:'+right('0'+CONVERT(VARCHAR(2),@DelayInSeconds),2)+''''

     

    EXEC (@SQL)

     

     

    ---------------------------------------------------

    SELECT p.cpu - fp.cpu AS CPU, p.spid, p.program_name ,p.loginame ,p. hostname, p.last_batch

    INTO #delta

    FROM master..sysprocesses p

    inner join #firstpass fp

    on p.spid = u.spid

    WHERE p.spid > 50

     

    ---------------------------------------------------

     

    create table #IP (EventType varchar(30), Parameters varchar(30), EventInfo varchar(500), SPID int )

     

    declare @spid int

    declare CurP cursor for select TOP 10 spid from #delta ORDER BY cpu DESC

    open CurP

    fetch CurP into @spid

    while @@fetch_status=0

    BEGIN

    SELECT @SQL='dbcc inputbuffer('+convert(varchar(5),@spid)+')'

    insert into #IP (EventType, Parameters, EventInfo )exec(@SQL)

    UPDATE #IP SET SPID=@SPID where SPID IS NULL

    fetch CurP into @spid

    END

    close CurP

    deallocate CurP

    ---------------------------------------------------

     

    SELECT difference = p.cpu, p.spid, ISNULL(s.Eventinfo,'** SQL Only displayed for top 10 **') as SQLStmt, p.program_name ,p.loginame ,p. hostname, p.last_batch

    FROM #delta p

    left outer join #IP S

    on p.spid = s.spid

    ORDER BY 1 desc

     

     

     

    Which query takes the most I/O:

    SELECT DB_NAME(st.dbid) DBName

          ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName

          ,OBJECT_NAME(objectid,st.dbid) StoredProcedure

          ,max(cp.usecounts) execution_count

          ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO

          ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / (max(cp.usecounts)) avg_total_IO

          ,sum(qs.total_physical_reads) total_physical_reads

          ,sum(qs.total_physical_reads) / (max(cp.usecounts) * 1.0) avg_physical_read   

          ,sum(qs.total_logical_reads) total_logical_reads

          ,sum(qs.total_logical_reads) / (max(cp.usecounts) * 1.0) avg_logical_read 

          ,sum(qs.total_logical_writes) total_logical_writes

          ,sum(qs.total_logical_writes) / (max(cp.usecounts) * 1.0) avg_logical_writes 

    FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

       join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle

      where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

    group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)

    order by sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) desc

    Again, as plans are aged out of cache the stats get stale.  This is really for active plans only. 

    [Freelinking: unknown plugin indicator "2000 Version find the CPUDisk IO most consuming session|2000 Version"]

    which procs take the longest duration:

    SELECT DB_NAME(st.dbid) DBName

          ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName

          ,OBJECT_NAME(objectid,st.dbid) StoredProcedure

          ,max(cp.usecounts) execution_count

          ,sum(qs.total_elapsed_time) total_elapsed_time

          ,sum(qs.total_elapsed_time) / max(cp.usecounts) avg_elapsed_time

    FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

       join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle

      where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

    group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)

    order by sum(qs.total_elapsed_time) desc



    Again, only for cached plans. 

    active parallel queries:

    From a running server, you can determine whether any active requests are running in parallel for a given session by using the following query.

    select r.session_id, r.request_id, max(isnull(exec_context_id, 0)) as number_of_workers, r.sql_handle, r.statement_start_offset, r.statement_end_offset, r.plan_handle from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id = t.session_id join sys.dm_exec_sessions s on r.session_id = s.session_id where s.is_user_process = 0x1 group by r.session_id, r.request_id, r.sql_handle, r.plan_handle, r.statement_start_offset, r.statement_end_offset having max(isnull(exec_context_id, 0)) > 0

    With this information, the text of the query can easily be retrieved by using sys.dm_exec_sql_text, while the plan can be retrieved using sys.dm_exec_cached_plan.

    Procedure Cache Allocation

    SELECT  TOP 6

    LEFT([name], 20) as [name],

    LEFT([type], 20) as [type],

    [single_pages_kb] + [multi_pages_kb] AS cache_kb,

    [entries_count]

    FROM sys.dm_os_memory_cache_counters

    order by single_pages_kb + multi_pages_kb DESC



    This will return various types of plans:

  • CACHESTORE_SQLCP.  These are cached SQL statements or batches that aren't in stored procedures, functions and triggers.  This includes any dynamic SQL or raw SELECT statements sent to the server.
  •  

    Basic Query Plan Metrics

    SELECT * FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);

     

    which query takes the most CPU:

    select

    highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time,

    q.dbid, q.objectid, q.number, q.encrypted, q.[text] from

    (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs

    order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q

    order by highest_cpu_queries.total_worker_time desc

    Alternatively, query against sys.dm_exec_cached_plans by using filters for various operators that may be CPU intensive, such as '%Hash Match%', '%Sort%' to look for suspects.

    This will only show the queries that are currently in the cache.  So as things get removed from memory the query becomes less useful. 

    For SQL 2000, use this query...it will also work in 2005.

    /*

    used to quickly determine the biggest CPU utilizes in the db by spid.

    only reports the TOP 10 consumers.

    default is 10 second compare.

    */

    CREATE PROCEDURE CPUHeavyHitters @DelayInSeconds SMALLINT=10

    AS

    IF @DelayInSeconds NOT BETWEEN 1 AND 60

    SET @DelayInSeconds = 10

     

    SELECT cpu ,spid into #firstpass

    FROM master..sysprocesses

    WHERE spid > 50

     

    DECLARE @SQL VARCHAR(100)

    SELECT @SQL='WAITFOR delay ''00:00:'+right('0'+CONVERT(VARCHAR(2),@DelayInSeconds),2)+''''

     

    EXEC (@SQL)

     

     

    ---------------------------------------------------

    SELECT p.cpu - fp.cpu AS CPU, p.spid, p.program_name ,p.loginame ,p. hostname, p.last_batch

    INTO #delta

    FROM master..sysprocesses p

    inner join #firstpass fp

    on p.spid = u.spid

    WHERE p.spid > 50

     

    ---------------------------------------------------

     

    create table #IP (EventType varchar(30), Parameters varchar(30), EventInfo varchar(500), SPID int )

     

    declare @spid int

    declare CurP cursor for select TOP 10 spid from #delta ORDER BY cpu DESC

    open CurP

    fetch CurP into @spid

    while @@fetch_status=0

    BEGIN

    SELECT @SQL='dbcc inputbuffer('+convert(varchar(5),@spid)+')'

    insert into #IP (EventType, Parameters, EventInfo )exec(@SQL)

    UPDATE #IP SET SPID=@SPID where SPID IS NULL

    fetch CurP into @spid

    END

    close CurP

    deallocate CurP

    ---------------------------------------------------

     

    SELECT difference = p.cpu, p.spid, ISNULL(s.Eventinfo,'** SQL Only displayed for top 10 **') as SQLStmt, p.program_name ,p.loginame ,p. hostname, p.last_batch

    FROM #delta p

    left outer join #IP S

    on p.spid = s.spid

    ORDER BY 1 desc

     

     

     

    Which query takes the most I/O:

    SELECT DB_NAME(st.dbid) DBName

          ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName

          ,OBJECT_NAME(objectid,st.dbid) StoredProcedure

          ,max(cp.usecounts) execution_count

          ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO

          ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / (max(cp.usecounts)) avg_total_IO

          ,sum(qs.total_physical_reads) total_physical_reads

          ,sum(qs.total_physical_reads) / (max(cp.usecounts) * 1.0) avg_physical_read   

          ,sum(qs.total_logical_reads) total_logical_reads

          ,sum(qs.total_logical_reads) / (max(cp.usecounts) * 1.0) avg_logical_read 

          ,sum(qs.total_logical_writes) total_logical_writes

          ,sum(qs.total_logical_writes) / (max(cp.usecounts) * 1.0) avg_logical_writes 

    FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

       join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle

      where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

    group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)

    order by sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) desc

    Again, as plans are aged out of cache the stats get stale.  This is really for active plans only. 

    [Freelinking: unknown plugin indicator "2000 Version find the CPUDisk IO most consuming session|2000 Version"]

    which procs take the longest duration:

    SELECT DB_NAME(st.dbid) DBName

          ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName

          ,OBJECT_NAME(objectid,st.dbid) StoredProcedure

          ,max(cp.usecounts) execution_count

          ,sum(qs.total_elapsed_time) total_elapsed_time

          ,sum(qs.total_elapsed_time) / max(cp.usecounts) avg_elapsed_time

    FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

       join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle

      where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

    group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)

    order by sum(qs.total_elapsed_time) desc



    Again, only for cached plans. 

    active parallel queries:

    From a running server, you can determine whether any active requests are running in parallel for a given session by using the following query.

    select r.session_id, r.request_id, max(isnull(exec_context_id, 0)) as number_of_workers, r.sql_handle, r.statement_start_offset, r.statement_end_offset, r.plan_handle from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id = t.session_id join sys.dm_exec_sessions s on r.session_id = s.session_id where s.is_user_process = 0x1 group by r.session_id, r.request_id, r.sql_handle, r.plan_handle, r.statement_start_offset, r.statement_end_offset having max(isnull(exec_context_id, 0)) > 0

    With this information, the text of the query can easily be retrieved by using sys.dm_exec_sql_text, while the plan can be retrieved using sys.dm_exec_cached_plan.

    Procedure Cache Allocation

    SELECT  TOP 6

    LEFT([name], 20) as [name],

    LEFT([type], 20) as [type],

    [single_pages_kb] + [multi_pages_kb] AS cache_kb,

    [entries_count]

    FROM sys.dm_os_memory_cache_counters

    order by single_pages_kb + multi_pages_kb DESC



    This will return various types of plans:

  • CACHESTORE_PHDR.  These are algebrizer trees for views, constraints and defaults.  An algebrizer tree is the parsed SQL text that resolves the table and column names.
  •  

    Basic Query Plan Metrics

    SELECT * FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);

     

    which query takes the most CPU:

    select

    highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time,

    q.dbid, q.objectid, q.number, q.encrypted, q.[text] from

    (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs

    order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q

    order by highest_cpu_queries.total_worker_time desc

    Alternatively, query against sys.dm_exec_cached_plans by using filters for various operators that may be CPU intensive, such as '%Hash Match%', '%Sort%' to look for suspects.

    This will only show the queries that are currently in the cache.  So as things get removed from memory the query becomes less useful. 

    For SQL 2000, use this query...it will also work in 2005.

    /*

    used to quickly determine the biggest CPU utilizes in the db by spid.

    only reports the TOP 10 consumers.

    default is 10 second compare.

    */

    CREATE PROCEDURE CPUHeavyHitters @DelayInSeconds SMALLINT=10

    AS

    IF @DelayInSeconds NOT BETWEEN 1 AND 60

    SET @DelayInSeconds = 10

     

    SELECT cpu ,spid into #firstpass

    FROM master..sysprocesses

    WHERE spid > 50

     

    DECLARE @SQL VARCHAR(100)

    SELECT @SQL='WAITFOR delay ''00:00:'+right('0'+CONVERT(VARCHAR(2),@DelayInSeconds),2)+''''

     

    EXEC (@SQL)

     

     

    ---------------------------------------------------

    SELECT p.cpu - fp.cpu AS CPU, p.spid, p.program_name ,p.loginame ,p. hostname, p.last_batch

    INTO #delta

    FROM master..sysprocesses p

    inner join #firstpass fp

    on p.spid = u.spid

    WHERE p.spid > 50

     

    ---------------------------------------------------

     

    create table #IP (EventType varchar(30), Parameters varchar(30), EventInfo varchar(500), SPID int )

     

    declare @spid int

    declare CurP cursor for select TOP 10 spid from #delta ORDER BY cpu DESC

    open CurP

    fetch CurP into @spid

    while @@fetch_status=0

    BEGIN

    SELECT @SQL='dbcc inputbuffer('+convert(varchar(5),@spid)+')'

    insert into #IP (EventType, Parameters, EventInfo )exec(@SQL)

    UPDATE #IP SET SPID=@SPID where SPID IS NULL

    fetch CurP into @spid

    END

    close CurP

    deallocate CurP

    ---------------------------------------------------

     

    SELECT difference = p.cpu, p.spid, ISNULL(s.Eventinfo,'** SQL Only displayed for top 10 **') as SQLStmt, p.program_name ,p.loginame ,p. hostname, p.last_batch

    FROM #delta p

    left outer join #IP S

    on p.spid = s.spid

    ORDER BY 1 desc

     

     

     

    Which query takes the most I/O:

    SELECT DB_NAME(st.dbid) DBName

          ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName

          ,OBJECT_NAME(objectid,st.dbid) StoredProcedure

          ,max(cp.usecounts) execution_count

          ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO

          ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / (max(cp.usecounts)) avg_total_IO

          ,sum(qs.total_physical_reads) total_physical_reads

          ,sum(qs.total_physical_reads) / (max(cp.usecounts) * 1.0) avg_physical_read   

          ,sum(qs.total_logical_reads) total_logical_reads

          ,sum(qs.total_logical_reads) / (max(cp.usecounts) * 1.0) avg_logical_read 

          ,sum(qs.total_logical_writes) total_logical_writes

          ,sum(qs.total_logical_writes) / (max(cp.usecounts) * 1.0) avg_logical_writes 

    FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

       join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle

      where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

    group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)

    order by sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) desc

    Again, as plans are aged out of cache the stats get stale.  This is really for active plans only. 

    [Freelinking: unknown plugin indicator "2000 Version find the CPUDisk IO most consuming session|2000 Version"]

    which procs take the longest duration:

    SELECT DB_NAME(st.dbid) DBName

          ,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName

          ,OBJECT_NAME(objectid,st.dbid) StoredProcedure

          ,max(cp.usecounts) execution_count

          ,sum(qs.total_elapsed_time) total_elapsed_time

          ,sum(qs.total_elapsed_time) / max(cp.usecounts) avg_elapsed_time

    FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

       join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle

      where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

    group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)

    order by sum(qs.total_elapsed_time) desc



    Again, only for cached plans. 

    active parallel queries:

    From a running server, you can determine whether any active requests are running in parallel for a given session by using the following query.

    select r.session_id, r.request_id, max(isnull(exec_context_id, 0)) as number_of_workers, r.sql_handle, r.statement_start_offset, r.statement_end_offset, r.plan_handle from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id = t.session_id join sys.dm_exec_sessions s on r.session_id = s.session_id where s.is_user_process = 0x1 group by r.session_id, r.request_id, r.sql_handle, r.plan_handle, r.statement_start_offset, r.statement_end_offset having max(isnull(exec_context_id, 0)) > 0

    With this information, the text of the query can easily be retrieved by using sys.dm_exec_sql_text, while the plan can be retrieved using sys.dm_exec_cached_plan.

    Procedure Cache Allocation

    SELECT  TOP 6

    LEFT([name], 20) as [name],

    LEFT([type], 20) as [type],

    [single_pages_kb] + [multi_pages_kb] AS cache_kb,

    [entries_count]

    FROM sys.dm_os_memory_cache_counters

    order by single_pages_kb + multi_pages_kb DESC



    This will return various types of plans:

    Add new comment