January 11, 2012 at 6:40 am
Hi
I would like to obtain something like this :
- First column : SP or ad-hoc query
- Second column : Table or index Scan due to the query
I am currently working on a database where a lot of implicit conversions leads to table or index scan (WHERE Varchar_field = int_value , awesome).
I would like to find a way to list all poor queries without having to analyze all stored procedures and ad-hoc queries.
I tried doing it with profiler taking into account :
- Scan:stopped
- all kinds of StmtCompleted
events but I can't find any efficient way to filter and keep all information I need.
Thanks
January 11, 2012 at 7:30 am
Scans are only one indicator of poor performance and are actually not even always a good indicator. SQL Server will run faster using a scan instead of a seek on a table that only consists of a few pages. If you really want to use scans as the mechanism for identifying poorly performing queries, there's no direct way to identify a scan and associate it with a query that I know of except one. You'd have to use an XQuery against the plans in cache. That'll do it.
I blogged about how to do that here[/url]. This query will actually get you want you want if you filter for physical operations that are table or clustered index scans.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 11, 2012 at 7:53 am
Perhaps a better option would be to query the plan cache (same method as Grant suggests) for plans that have CONVERT_IMPLICIT in them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2012 at 8:08 am
Querying the plan cache will do the trick ! Good idea !
Thanks a lot Grant and Gail.
January 11, 2012 at 8:19 am
azdzn (1/11/2012)
Querying the plan cache will do the trick ! Good idea !Thanks a lot Grant and Gail.
Sure thing. Just remember, as I say in the article, direct queries against the cache are somewhat expensive. Be cautious doing this on a production system.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply