Type of indexes on tables:
-
Is there anyway apart from DTA we can find out using any DMV, to let us know what kind of index need is required on table. Does the table requires unique or clustered or nonclusted.. I have a million row table and working on DTA is taking lot of time, so just wnat to see if there is any dmv which says what type of indexes required.
-
sys.dm_db_missing_index_details
But take what it says with a pinch of salt, create it test it apply it to live.
But DTA is the tool to analyse the workload to see if indexes can be dropped and others create in its place etc.
-
I actually tried to query with
sys.dm_db_missing_index_group_stats
INNER JOIN sys.dm_db_missing_index_groups
INNER JOIN sys.dm_db_missing_index_details
but it is not much helping me.
-
Use both DTA and missing indexes as guidelines, nothing more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks Gila
-
At the end of the day you need to look at the query and the execution plan, and use your judgement and experience to work out the best indexes.
This script looks at the most expensive queries in terms of logical reads (those with entries in sys.dm_exec_query_stats) and returns the query text and execution plan for any with either a missing index suggestion, or an index scan.
This allows you to focus on the "low hanging fruit".
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select last_logical_reads, last_elapsed_time, qp.query_plan, st.text
from (
select top 500 plan_handle, sql_handle, last_logical_reads, last_elapsed_time
from sys.dm_exec_query_stats
order by last_logical_reads desc) qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
where qp.query_plan.exist('//MissingIndex') = 1
or qp.query_plan.exist('//RelOp[@PhysicalOp eq "Clustered Index Scan" or @PhysicalOp eq "Index Scan" or @PhysicalOp eq "Nonclustered Index Scan" or @PhysicalOp eq "Table Scan"]') = 1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply