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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel_PLDB 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_PLDB 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
pcelbaCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.