Querying Dynamic Management Views

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Querying Dynamic Management Views

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-11-26 : 10:08:28

Hi,

Could you please give the query for obtaining the following data from Dynamic Management Views ?

1. Time taken by each stored procedure
2. The time of execution of each stored procedure.
3. Tables/Index that has undergone Full Scan
4. Start Time of table scan and End Time of Table scan for each table

Thanks
Lijo Cheeran Joseph

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-26 : 10:26:11

Have you tried writing any of these yourself? Have you looked in Books Online for the documentation of the DMVs?

In addition to BOL I highly recommend Glenn Berry's blog, he has numerous examples of DMV use:

http://sqlserverperformance.wordpress.com/

If he doesn't have the specific queries you need you can almost certainly modify an existing query to get what you want.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-11-26 : 10:44:25

quote:
Originally posted by Lijo Cheeran Joseph

Hi,

1. Time taken by each stored procedure
2. The time of execution of each stored procedure.

With regards to 1 and 2, someone can correct if I am wrong, but they are not possible. But you can get somthing similar.

SELECT TOP 200
     a.last_execution_time, 
     cast(last_elapsed_time/1000000.0 as decimal(10, 3)) Last_Elasped_Seconds,
	 (select name from [Your Database].dbo.sysobjects where ID = ObjectID) Object_Name,
     SUBSTRING(b.text, (a.statement_start_offset/2) + 1,      
     ((CASE statement_end_offset           WHEN -1 THEN DATALENGTH(b.text)          ELSE a.statement_end_offset END
               - a.statement_start_offset)/2) + 1) AS statement_text,               
     total_worker_time as CPU_time, 
     *    
FROM 
     sys.dm_exec_query_stats a  CROSS APPLY 
     sys.dm_exec_sql_text (a.sql_handle) AS b  
WHERE 
     b.dbid = DB_ID('[Your Database]') 
ORDER BY 
     a.last_execution_time

(Remember to fill in [Your Database]). This is a good query. SQL server keeps query stats rather than stored proc stats. This query tells you the most recent queries executed and which Stored Procedure they belong to. But you get only 1 record per query rather than a log of each call.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-11-26 : 10:59:50

quote:
With regards to 1 and 2, someone can correct if I am wrong, but they are not possible. But you can get somthing similar.

Looks like I'm behind. You can do this with dm_exec_procedure_stats. It doesn't work on our 2008, I guess we need R2.

Thankyou Rob for posting that link.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources