select statements not using indexes

Hi,

Is there any way in SQL Server to know which SQL Select statements are not using indexes and running full table scans?

I am asking as I want to know which reports needs creation of new indexes .
baberaminAsked:
Who is Participating?
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
Hi,

Do you want queries with table scans only or these with table scans and seeks?
Give it a try:
;WITH XMLNAMESPACES
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')   
SELECT
	dm_exec_sql_text.text AS sql_text,
	CAST(CAST(dm_exec_query_stats.execution_count AS DECIMAL) / CAST((CASE WHEN DATEDIFF(HOUR, dm_exec_query_stats.creation_time, CURRENT_TIMESTAMP) = 0 THEN 1 ELSE DATEDIFF(HOUR, dm_exec_query_stats.creation_time, CURRENT_TIMESTAMP) END) AS DECIMAL) AS INT) AS executions_per_hour,
	dm_exec_query_stats.creation_time, 
	dm_exec_query_stats.execution_count,
	CAST(CAST(dm_exec_query_stats.total_worker_time AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as cpu_per_execution,
	CAST(CAST(dm_exec_query_stats.total_logical_reads AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as logical_reads_per_execution,
	CAST(CAST(dm_exec_query_stats.total_elapsed_time AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as elapsed_time_per_execution,
	dm_exec_query_stats.total_worker_time AS total_cpu_time,
	dm_exec_query_stats.max_worker_time AS max_cpu_time, 
	dm_exec_query_stats.total_elapsed_time, 
	dm_exec_query_stats.max_elapsed_time, 
	dm_exec_query_stats.total_logical_reads, 
	dm_exec_query_stats.max_logical_reads,
	dm_exec_query_stats.total_physical_reads, 
	dm_exec_query_stats.max_physical_reads,
	dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.sql_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
WHERE (--query_plan.exist('//RelOp[@PhysicalOp = "Index Seek"]') = 0 AND query_plan.exist('//RelOp[@PhysicalOp = "Clustered Index Seek"]') = 0 AND
		query_plan.exist('//RelOp[@PhysicalOp = "Table Scan"]') = 1)
ORDER BY dm_exec_query_stats.total_worker_time DESC;

Open in new window


Great source of information:
https://www.sqlshack.com/searching-the-sql-server-query-plan-cache/

Open in new window


Reference of operators:
https://docs.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference

Open in new window


Regards,
Daniel
2
 
baberaminAuthor Commented:
Hi,

Thanks.
Not much aware of seek but my objective here is that if custom developed reports needs indexes to avoid full scan.
0
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
Hi,

Best is to retrieve actual execution plan and take a look what's happening there.
If possible please save your execution plan nad upload here so experts could take a look and provide you with recommendations.
https://www.red-gate.com/simple-talk/sql/database-administration/retrieving-sql-server-query-execution-plans/

Open in new window


Regards,
Daniel
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.