I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2005 profiling with Dynamic Management Views (DMV's)

DMV's are one of the most usefull features in SQL Server 2005.

There are 2 that I like more than others because they can give you information about
executed SQL statements without the use of SQL Profiler tool.
Of course they aren't a substitute for SQL profiler but rather an another tool you can use
in situations when you can't run SQL Profiler.
Those 2 DMV's are:
 - sys.dm_exec_query_stats
 - sys.dm_exec_sql_text
 
They provide much info about executed statements but i used them in this procedure for searching through
executed sql statemnts.
Since dm_exec_query_stats is based on cached query plans the row in it lasts until the plan is removed from cache.
So for better and more usefull data i put the results of each run into a separate log table.

this is the stored procedure:

create table StatementExecLog
(
    StatementText nvarchar(max),
    LastExecuted datetime,
    ElapsedTimeInSeconds int,
    ExecCountFromLastCompile int
)
go
create proc GetExecutedQueries
    @Filter varchar(8000),
    @DateFrom datetime = '19000101',
    @DateTo datetime = '99990101'    
as
select * from
(
SELECT    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS StatementText,
        last_execution_time as LastExecuted,
        last_elapsed_time/1000000.0 as ElapsedTimeInSeconds,
        execution_count as ExecCountFromLastCompile
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where last_execution_time between @DateFrom and @DateTo
) t
where StatementText like '%' + @Filter + '%'
order by LastExecuted desc
go
select * from person.address
go
-- for simple searches
exec GetExecutedQueries '.address'

-- for long term logging
insert into StatementExecLog
exec GetExecutedQueries '.address'

Print | posted on Thursday, September 21, 2006 10:00 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# Bitacle Blog Search Archive - SQL Server 2005 profiling with Dynamic Management Views (DMV's)

[...]
DMV's are one of the most usefull features in SQL Server 2005. [...]
9/23/2006 4:56 AM | bitacle.org
Gravatar

# re: SQL Server 2005 profiling with Dynamic Management Views (DMV's)

I think your sample code above has a bug. SQL BOL says that 'last_elapsed_time' column is in microseconds. This means you need to divide by 1,000,000 and not 1,000 to derive the seconds.

Randy
10/20/2006 4:57 PM | Randy Minder
Gravatar

# re: SQL Server 2005 profiling with Dynamic Management Views (DMV's)

Thanx Randy for catching that.

I'm still so used to miliseconds from SS2k :)
10/20/2006 5:17 PM | Mladen
Gravatar

# re: SQL Server 2005 profiling with Dynamic Management Views (DMV's)

Come with small examples and then proceed with critical examples. Its hard to get the point...not satisfied.
3/24/2008 10:39 AM | Madhu
Gravatar

# re: SQL Server 2005 profiling with Dynamic Management Views (DMV's)

How do i eliminate the microseconds to seconds
10/8/2008 1:25 AM | Tutu
Gravatar

# re: SQL Server 2005 profiling with Dynamic Management Views (DMV's)

multiply them by 1.000.000??
10/8/2008 10:10 AM | Mladen
Gravatar

# re: SQL Server 2005 profiling with Dynamic Management Views (DMV's)

Yes DMVs are probably the most useful feature that came out with SQL 2005. Be careful though as the stats are kept in memory and will get refreshed when the server / service gets restarted. If you are interested in more DMV related queries then see this article http://blog.strictly-software.com/2008/09/sql-performance-tuning-queries.html
and if you want a beast of a report that has near on 20 different performance related reports (CPU, I/O, Index Fragmentation, Wait time, Query Plan Cache etc) then take a look at this: http://www.strictly-software.com/scripts/downloads/database_performance_script.txt
12/7/2008 5:48 AM | Rob Reid
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET