April 8, 2010 at 9:20 pm
Correct me if I'm wrong but can’t you can gather a lot of information using scripts based on DMV's as an alternative to analyzing execution plans in SQL Server 2005 & 2008..
What are the pros & cons? I like the option of the DMV’s in addition to examining execution plans.
I would appreciate any constructive input on this subject matter.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 9, 2010 at 6:44 am
Yeah, you can gather a lot of performance metrics from DMV's. You can't get into what's occurring within an execution plan with anything except execution plans, but you can get metrics for running processes using sys.dm_exec_requests. You can get aggregate metrics for queries that are in cache from sys.dm_exec_query_stats. You can gather lots of great information about your indexes from several DMV's including sys.dm_db_index_physical_stats. You can even start combining these with queries agains sys.dm_exec_query_plan and retrieve the query plans from cache, combining them with the other information available. Further, because the query plans in cache are XML (all except really huge ones), you can run XQuery against them.
DMV's are great.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2010 at 6:44 am
I've never thought of using it before, but it's a good idea.
e.g. something like this would return all the cached plans I guess:
SELECT query_plan, text FROM sys.dm_exec_cached_plans
INNER JOIN sys.dm_exec_query_stats ON sys.dm_exec_query_stats.plan_handle=sys.dm_exec_cached_plans.plan_handle
CROSS APPLY sys.dm_exec_query_plan(sys.dm_exec_cached_plans.plan_handle)
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
In SSMS (2008 at least) you can even click on the plan XML and it will open the sqlplan UI in a new window.
You could even use xquery to filter table scans etc. Although I believe these are equivalent to an estimated execution plan rather than an actual one.
April 12, 2010 at 1:10 am
Thanks everyone!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 12, 2010 at 1:15 am
Grant,
Unfortunately I do not have your 2008 book but I have an older one written by Sajal Dam. 😎
Regards..
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply