February 20, 2013 at 2:32 pm
Hi there
I'm looking for a way to find the queries that are causing index scans on the SQL Server instance.I know it's easy to do so with Extended_Events in SQL server 2012 but the "sqlserver.scan_started" event does not exists in 2008 version of extended events collection. By using SQL Profiler it's possible to track indexes and tables which has been scanned but I can't easily figure out which SQL query caused the scan and have to search the plan catch to find the queries referencing the index that has been scanned.
Is there a better way to do so?
Pooyan
February 21, 2013 at 4:11 am
-- Get all SQL Statements with "table scan" in cached query plan
;WITH
XMLNAMESPACES
(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)
,EQS AS
(SELECT EQS.plan_handle
,SUM(EQS.execution_count) AS ExecutionCount
,SUM(EQS.total_worker_time) AS TotalWorkTime
,SUM(EQS.total_logical_reads) AS TotalLogicalReads
,SUM(EQS.total_logical_writes) AS TotalLogicalWrites
,SUM(EQS.total_elapsed_time) AS TotalElapsedTime
,MAX(EQS.last_execution_time) AS LastExecutionTime
FROM sys.dm_exec_query_stats AS EQS
GROUP BY EQS.plan_handle)
SELECT EQS.[ExecutionCount]
,EQS.[TotalWorkTime]
,EQS.[TotalLogicalReads]
,EQS.[TotalLogicalWrites]
,EQS.[TotalElapsedTime]
,EQS.[LastExecutionTime]
,ECP.[objtype] AS [ObjectType]
,ECP.[cacheobjtype] AS [CacheObjectType]
,DB_NAME(EST.[dbid]) AS [DatabaseName]
,OBJECT_NAME(EST.[objectid], EST.[dbid]) AS [ObjectName]
,EST.[text] AS [Statement]
,EQP.[query_plan] AS [QueryPlan]
FROM sys.dm_exec_cached_plans AS ECP
INNER JOIN EQS
ON ECP.plan_handle = EQS.plan_handle
CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST
CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP
WHERE EQP.[query_plan].exist('data(//RelOp[@PhysicalOp="Table Scan"][@EstimateRows * @AvgRowSize > 50000.0][1])') = 1
-- Optional filters
AND EQS.[ExecutionCount] > 1 -- No Ad-Hoc queries
AND ECP.[usecounts] > 1
ORDER BY EQS.TotalElapsedTime DESC
,EQS.ExecutionCount DESC;
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 21, 2013 at 7:41 am
Thanks for your reply. I was looking for a way other than searching the plan cache.
I was using the same DMVs but your script is a little bit more complete.
Pooyan
February 21, 2013 at 8:28 pm
Have a look at [sys].[dm_db_index_usage_stats] - it has info about scans and might be what you are after. You will need to join to sys.objects and sys.indexes to get the names of the tables and indexes
February 22, 2013 at 3:27 am
pooyan_pdm (2/21/2013)
Thanks for your reply. I was looking for a way other than searching the plan cache.
The only way to find out what queries had specific query plan operators is to search the query plan cache.
Also, be careful about scan started and those kind of events, that doesn't necessarily mean a full index scan. Could be a range scan (ie an index seek)
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
February 22, 2013 at 4:00 am
It takes almost 6 min for sql server to look into the plan catch on our production. What kind of overhead does this search have? I guess it should put some latches on the memory pages containing the plans am I right?
Pooyan
February 22, 2013 at 6:02 am
pooyan_pdm (2/22/2013)
What kind of overhead does this search have?
A lot. That XQuery is heavy on CPU on an unindexed column
Don't do that on a production server. At most, pull the plan cache unfiltered to a table, move the table to another server, do your filtering there.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply