June 14, 2013 at 12:35 am
Hi everybody ,
I would like to retrieve queries that cause full scan table in sql server.
Someone can help me please?? :ermm:
June 14, 2013 at 1:13 am
Check out this Query
taken from http://gallery.technet.microsoft.com/scriptcenter/Get-all-SQL-Statements-0622af19
;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;
Neeraj Prasad Sharma
Sql Server Tutorials
June 14, 2013 at 1:32 am
That query will do part of the work. It'll pick up table scans only on tables that don't have a clustered index, i.e. heaps. For tables that do have a clustered index, you need to look for the operator 'Clustered Index Scan'
Not sure what the filters for ExecutionCount> 0 (which won't filter out ad-hoc queries) or for data size are there for, as the OP didn't ask for either.
In addition, it's polite to give credit where credit is due, that query is from http://gallery.technet.microsoft.com/scriptcenter/Get-all-SQL-Statements-0622af19
Also worth noting that a table scan or clustered index scan aren't always full scans of the entire table. If there's a MAX, MIN or TOP, the clustered index scan could just read a portion of the table, depending on the details of the query.
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
July 1, 2013 at 4:28 am
Hi ,
I tested the script but it not worked :/
I created a table with 4millions lines ( no index)
I did a select * from myTable ;
and executed the script but it returned nothing :doze:
Someone have an idea ?
July 1, 2013 at 6:36 am
You say no index, but did you specify a primary key? If so you have an index.
Tom
July 1, 2013 at 6:50 am
Hi , thk for reply
No index = no pk = no unique constraint
July 1, 2013 at 7:19 am
The query given above has a number of unnecessary filters in it (as mentioned earlier) that will result in some queries that do table scans not showing up. The given query is a decent start, but it's not correct for the OP's requirement.
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
July 4, 2013 at 12:11 am
Hi , thank you 🙂
last question : is it possible with this script to return a colum with the number of read block?
July 4, 2013 at 4:01 am
"Number of read block"?
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply