can you use dynamic management views to find out how many times a query is called in a time period

Question

I've been learning more about the dynamic management views in sql server because we've been having some performance problems at work. Perhaps this is the wrong way to look at things but the tools that are available seem to be ... they just don't seem to be as flexible as I want.

I'd like to be able find out things like the following which I have examples of.

What queries are running more than 20% slower than normal? Below ...

--displays regarding about procedures that are currently taking longer to execute by more than 20% over the average. 
;
with data as (
select  s.plan_handle
        , db.name
        , ObjectName = OBJECT_NAME(t.objectid, t.dbid )
        , PlanCompileDate = s.creation_time
        , PlanExecutionCount = execution_count

        , AvgElapsedSeconds = ( total_elapsed_time/ CAST( 1000000 as float(15) ) ) / CAST( s.execution_count as float(15) )
        , LastElapsedSeconds = last_elapsed_time / CAST( 1000000 as float(38) )
        , LeastElapsedSeconds = min_elapsed_time / CAST( 1000000 as float(15) )
        , MostElapsedSeconds = max_elapsed_time / CAST( 1000000 as float(15) )
        , LastExecPercentageDevTime = (( ( s.total_elapsed_time /  cast(s.execution_count as float(15) ) ) - s.last_elapsed_time ) / ( case s.last_elapsed_time when 0 then -1.00 else ( s.total_elapsed_time /  cast( s.execution_count as float(15) ) ) end ) )  * -100
                                                    -- 0.00532225 
        , AvgRowsReturned = total_rows / CAST( s.execution_count as float )
        , LastRowsReturned = last_rows
        , LeastRowsReturned = min_rows
        , MostRowsReturned = max_rows
        , LastExecPercentageDevRows =  (( ( s.total_rows /  cast(s.execution_count as float ) ) - s.last_rows) / ( case s.last_rows when 0 then -1.00 else ( s.total_rows /  cast( s.execution_count as float ) ) end ) )  * -100 

        , AvgCPUTime = s.total_worker_time / CAST( s.execution_count as float )
        , LastCPUTime = s.last_worker_time
        , LeastCPUTime = min_worker_time
        , MostCPUTime = max_worker_time
        , LastExecPercentageDevCPUTime =  (( ( s.total_worker_time /  cast(s.execution_count as float ) ) - s.last_worker_time ) / ( case s.last_worker_time when 0 then -1.00 else ( s.total_worker_time /  cast( s.execution_count as float ) ) end ) )  * -100 

        , AvgPhysicalReads = s.total_physical_reads / CAST( s.execution_count as float )
        , LastPhysicalReads = s.last_physical_reads
        , LeastPhysicalReads = s.min_physical_reads
        , MostPhysicalReads = s.max_physical_reads
        , LastExecPercentageDevPhysicalReads = (( ( s.total_physical_reads /  cast(s.execution_count as float ) ) - s.last_physical_reads ) / ( case s.last_physical_reads when 0 then -1.00 else ( s.total_physical_reads /  cast( s.execution_count as float ) ) end ) )  * -100 

        , AvgLogicalReads = s.total_logical_reads / CAST( s.execution_count as float )
        , LastLogicalReads = s.last_logical_reads
        , LeastLogicalReads = s.min_logical_reads
        , MostLogicalReads = s.max_logical_reads
        , LastExecPercentageDevLogicalReads = (( ( s.total_logical_reads /  cast(s.execution_count as float ) ) - s.last_logical_reads ) / ( case s.last_logical_reads when 0 then -1.00 else ( s.total_logical_reads /  cast( s.execution_count as float ) ) end ) )  * -100 

        , AvgLogicalWrites = s.total_logical_Writes / CAST( s.execution_count as float )
        , LastLogicalWrites = s.last_logical_Writes
        , LeastLogicalWrites = s.min_logical_Writes
        , MostLogicalWrites = s.max_logical_Writes
        , LastExecPercentageDevLogicalWrites = (( ( s.total_logical_writes /  cast(s.execution_count as float ) ) - s.last_logical_writes ) / ( case s.last_logical_writes when 0 then -1.00 else ( s.total_logical_writes /  cast( s.execution_count as float ) ) end ) )  * -100 

        , t.text
        , t.number
        , t.objectid
        , s.query_hash
        --, s.* 
FROM sys.dm_exec_query_stats AS s 
     CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t 
     LEFT JOIN sys.databases AS DB 
        ON t.dbid = DB.database_id
)
select  *
from    data 
where   LastExecPercentageDevTime > 20 and -- changed by at least 20%
        PlanExecutionCount > 10 and -- executed more than 10 times 
        LastElapsedSeconds != 0 -- we don't care about queries that executed quickly
order by LastExecPercentageDevTime desc

When there is blocking I want to be able to determine the query that is doing the blocking and perhaps what are its dependent objects.

I believe the following accomplishes finding the queries doing the blocking

-- What queries are the source of all the blocking.
with blockingSessions as(
    select session_id,blocking_session_id
    from sys.dm_exec_requests
    where blocking_session_id > 0
), causal as(
    select  blocking_session_id
    from    blockingSessions
    where   blocking_session_id not in(select session_id from blockingSessions)
)--select * from causal 
select   t.text
        ,SPID               = con.most_recent_session_id
        ,STATUS             = s.STATUS
        ,[Login]            = s.login_name
        ,Host               = s.host_name
        --,BlkBy              = r.blocking_session_id
        ,DBName             = d.name
        ,CommandType        = r.command
        ,SQLStatement       = t.text
        ,ObjectName         = OBJECT_NAME(t.objectid)
        ,ElapsedMS          = r.total_elapsed_time
        ,CPUTime            = r.cpu_time
        ,IOReads            = r.logical_reads + r.reads
        ,IOWrites           = r.writes
        ,LastWaitType       = r.last_wait_type
        ,StartTime          = r.start_time
        ,Protocol           = con.net_transport
        ,ConnectionWrites   = con.num_writes
        ,ConnectionReads    = con.num_reads
        ,ClientAddress      = con.client_net_address
        ,Authentication     = con.auth_scheme 
        --,t.*
from    causal c
        join sys.dm_exec_connections con
            on c.blocking_session_id = con.session_id
        join sys.dm_exec_sessions s
            on con.most_recent_session_id = s.session_id
        left join sys.dm_exec_requests r
            on s.session_id = r.session_id
        outer APPLY sys.dm_exec_sql_text(con.most_recent_sql_handle) AS t
        left join sys.databases d   
            on t.dbid = d.database_id

But I have not been able to figure out how to achieve the following ...

How many times was a particular query run today (not since last plan compile time)? How many times today was a particular stored procedure run today? How many times was it called with certain parameters?

Maybe there are some others that you guys can think of that would be useful and maybe you guys can provide some suggestions to the ones I've given above. The other thing that I haven't been able to figure out is what exactly the relationship is between connections, requests and sessions in the dynamic management views?

EDIT

I guess I am specifically trying to use the views for a specific time period as in what happened between x time and y time. But they don't seem to be designed for that. Is this correct? Is there another way to get similar information by time period from sql server?

Answer 1

If you are looking at using DMVs to learn more about your SQL Server databases/server, head over to Glenn Berry's SQL Server Diagnostic Information Queries

Here are some queries from his SQL Server 2008 R2: Top Queries by execution count:

    SELECT TOP (250) qs.execution_count, qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time,
total_worker_time, total_logical_reads, 
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1,
(CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
      ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text 
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

Top SPs by execution count:

SELECT TOP(250) p.name AS [SP Name], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],  
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

You can easily modify these to get how many times a query/SP was executed.