January 7, 2013 at 11:19 am
I have both reporting and operational systems..
So looking at each system.. what becomes a good point for indexes?
(see query at the bottom of this post)
Reporting:
Improvement_measure > 1000
Table holding over 250,000 records
User_complies > 50% of User_Seeks
Last_user_seek less than 7 days ago
(PerfMon) Avg CPU < 90%
(PerfMon) Avg Wait times (ms) : PAGEIOLATCH_SH > 10, LATCH_EX > 5
(PerfMon) Avg Page Life < 10min
Operational Systems:
Improvement_mesure > 10000
Table holding over 1,000,000 records
User_compiles > 80% of User_Seeks
Last_user_seek less than 2 days ago
(PerfMon) Avg CPU < 70%
(PerfMon) Avg Wait times (ms) : PAGEIOLATCH_SH > 30, LATCH_EX > 10
(PerfMon) Avg Page Life < 10min
I use the following query to look for missing indexes:
SELECT migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 )
* ( migs.user_seeks + migs.user_scans ) AS improvement_measure ,
'CREATE INDEX [missing_index_'
+ CONVERT (VARCHAR, mig.index_group_handle) + '_'
+ CONVERT (VARCHAR, mid.index_handle) + '_'
+ LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' ON '
+ mid.statement
+ ' (' + ISNULL(mid.equality_columns, '')
+ CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(mid.inequality_columns, '') + ')'
+ ISNULL(' INCLUDE ('
+ mid.included_columns
+ ')', '')
AS create_index_statement ,
migs.* ,
mid.database_id ,
mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 )
* ( migs.user_seeks + migs.user_scans ) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact
* ( migs.user_seeks + migs.user_scans ) DESC
;
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT MissingIndexNode.value('(MissingIndexGroup/@Impact)[1]', 'float')
AS impact ,
OBJECT_NAME(sub.objectid, sub.dbid) AS calling_object_name ,
MissingIndexNode.value
('(MissingIndexGroup/MissingIndex/@Database)[1]',
'VARCHAR(128)') + '.'
+ MissingIndexNode.value
('(MissingIndexGroup/MissingIndex/@Schema)[1]',
'VARCHAR(128)') + '.'
+ MissingIndexNode.value
('(MissingIndexGroup/MissingIndex/@Table)[1]',
'VARCHAR(128)') AS table_name ,
STUFF(( SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)')
FROM MissingIndexNode.nodes
('MissingIndexGroup/MissingIndex/
ColumnGroup[@Usage="EQUALITY"]/Column')
AS t ( c )
FOR
XML PATH('')
), 1, 1, '') AS equality_columns ,
STUFF(( SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)')
FROM MissingIndexNode.nodes
('MissingIndexGroup/MissingIndex/
ColumnGroup[@Usage="INEQUALITY"]/Column')
AS t ( c )
FOR
XML PATH('')
), 1, 1, '') AS inequality_columns ,
STUFF(( SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)')
FROM MissingIndexNode.nodes
('MissingIndexGroup/MissingIndex/
ColumnGroup[@Usage="INCLUDE"]/Column')
AS t ( c )
FOR
XML PATH('')
), 1, 1, '') AS include_columns ,
sub.usecounts AS qp_usecounts ,
sub.refcounts AS qp_refcounts ,
qs.execution_count AS qs_execution_count ,
qs.last_execution_time AS qs_last_exec_time ,
qs.total_logical_reads AS qs_total_logical_reads ,
qs.total_elapsed_time AS qs_total_elapsed_time ,
qs.total_physical_reads AS qs_total_physical_reads ,
qs.total_worker_time AS qs_total_worker_time ,
StmtPlanStub.value('(StmtSimple/@StatementText)[1]', 'varchar(8000)') AS
statement_text
FROM ( SELECT ROW_NUMBER() OVER
( PARTITION BY qs.plan_handle
ORDER BY qs.statement_start_offset )
AS StatementID ,
qs.*
FROM sys.dm_exec_query_stats qs
) AS qs
JOIN ( SELECT x.query('../../..') AS StmtPlanStub ,
x.query('.') AS MissingIndexNode ,
x.value('(../../../@StatementId)[1]', 'int')
AS StatementID ,
cp.* ,
qp.*
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan
(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes
('/ShowPlanXML/BatchSequence/
Batch/Statements/StmtSimple/
QueryPlan/MissingIndexes/
MissingIndexGroup') mi ( x )
) AS sub ON qs.plan_handle = sub.plan_handle
AND qs.StatementID = sub.StatementID
January 7, 2013 at 11:32 am
Looking at this query, I wouldn't look at indexes with an improvement_measure of less than 1000. But that is nothing set in stone. It is a starting point.
What you really need to do is have a feel for the data in your databases. You need to know and understand the data, how the app & reports interact with that data and then tune that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 7, 2013 at 11:34 am
"When to index" isn't a question of the query. Not really. It's a question of what you're trying to do.
There's no way to tell, from what you wrote, wether you should index something or not.
Start out with a book on performance tuning. Read online articles on it, too, but pick out a good book as a first step.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 12:21 pm
SQLRNNR (1/7/2013)
Looking at this query, I wouldn't look at indexes with an improvement_measure of less than 1000. But that is nothing set in stone. It is a starting point.What you really need to do is have a feel for the data in your databases. You need to know and understand the data, how the app & reports interact with that data and then tune that.
Well in my last job, all projects started on my desk, and I set up all testing before go-live. So I had a great understanding of the database, data, and interaction with applications. Now I am in a new job, with none of that. I have no working knowledge of the data being stored, or how the applications are working (I had all the code, or I had wrote/de-bugged it, in my last job). In addition there are about 20 folks working on the application side, and they make database changes.... but almost never make any PK's, FK's and indexes. But I still need to monitor performance and improve performance.
January 7, 2013 at 12:29 pm
GSquared (1/7/2013)
"When to index" isn't a question of the query. Not really. It's a question of what you're trying to do.There's no way to tell, from what you wrote, wether you should index something or not.
Start out with a book on performance tuning. Read online articles on it, too, but pick out a good book as a first step.
My readings are:
A Guide for the Accidental DBA, Mastering Server Profiler, Performance Tuning DMV, SQL Server Tacklebox.
Any suggestions?
January 7, 2013 at 12:33 pm
Oh, on a side note... I do go through and look for indexes that have user updates, but no user seeks + user scans + user lookups. And pass that info back to the person who handles design work for the app that uses that databases/table. To see if the index should be deleted. (the other side of the coin)
January 7, 2013 at 1:07 pm
dwilliscp (1/7/2013)
GSquared (1/7/2013)
"When to index" isn't a question of the query. Not really. It's a question of what you're trying to do.There's no way to tell, from what you wrote, wether you should index something or not.
Start out with a book on performance tuning. Read online articles on it, too, but pick out a good book as a first step.
My readings are:
A Guide for the Accidental DBA, Mastering Server Profiler, Performance Tuning DMV, SQL Server Tacklebox.
Any suggestions?
I'd start with these
A Guide for the Accidental DBA
Performance Tuning DMV
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 7, 2013 at 1:28 pm
SQLRNNR (1/7/2013)
dwilliscp (1/7/2013)
GSquared (1/7/2013)
"When to index" isn't a question of the query. Not really. It's a question of what you're trying to do.There's no way to tell, from what you wrote, wether you should index something or not.
Start out with a book on performance tuning. Read online articles on it, too, but pick out a good book as a first step.
My readings are:
A Guide for the Accidental DBA, Mastering Server Profiler, Performance Tuning DMV, SQL Server Tacklebox.
Any suggestions?
I'd start with these
A Guide for the Accidental DBA
Performance Tuning DMV
Those are the books that I have already read, in the past year. They are helpful, but do not cover the details of when to create an index. (I believe the index script came out of the accidental DBA book.)
January 7, 2013 at 3:12 pm
My random wafflings on the subject: http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/
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
January 7, 2013 at 9:59 pm
dwilliscp (1/7/2013)
SQLRNNR (1/7/2013)
dwilliscp (1/7/2013)
GSquared (1/7/2013)
"When to index" isn't a question of the query. Not really. It's a question of what you're trying to do.There's no way to tell, from what you wrote, wether you should index something or not.
Start out with a book on performance tuning. Read online articles on it, too, but pick out a good book as a first step.
My readings are:
A Guide for the Accidental DBA, Mastering Server Profiler, Performance Tuning DMV, SQL Server Tacklebox.
Any suggestions?
I'd start with these
A Guide for the Accidental DBA
Performance Tuning DMV
Those are the books that I have already read, in the past year. They are helpful, but do not cover the details of when to create an index. (I believe the index script came out of the accidental DBA book.)
Besides the link that Gail just provided, you can use that script as a starting point. But do not rely solely on the output of that script. For scripts similar to that, I don't even start to look at the proposed index unless I know it is related to a poorly performing piece of the application. Or, I will loosely regard the results starting at an impact level of 1000 or greater.
You should be careful with the column order in the provided script as well. Investigate the Execution Plan that is associated to the missing index and ensure the column order is correct.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 9, 2013 at 6:57 am
SQLRNNR (1/7/2013)
dwilliscp (1/7/2013)
SQLRNNR (1/7/2013)
dwilliscp (1/7/2013)
GSquared (1/7/2013)
"When to index" isn't a question of the query. Not really. It's a question of what you're trying to do.There's no way to tell, from what you wrote, wether you should index something or not.
Start out with a book on performance tuning. Read online articles on it, too, but pick out a good book as a first step.
My readings are:
A Guide for the Accidental DBA, Mastering Server Profiler, Performance Tuning DMV, SQL Server Tacklebox.
Any suggestions?
I'd start with these
A Guide for the Accidental DBA
Performance Tuning DMV
Those are the books that I have already read, in the past year. They are helpful, but do not cover the details of when to create an index. (I believe the index script came out of the accidental DBA book.)
Besides the link that Gail just provided, you can use that script as a starting point. But do not rely solely on the output of that script. For scripts similar to that, I don't even start to look at the proposed index unless I know it is related to a poorly performing piece of the application. Or, I will loosely regard the results starting at an impact level of 1000 or greater.
You should be careful with the column order in the provided script as well. Investigate the Execution Plan that is associated to the missing index and ensure the column order is correct.
Thanks for the help you and Gail provided. I was trying to find a better approach than what I am currently doing... that is starting with the query, pulling the top 10 and looking at what tables they are using (to see if there are any indexes on them, and if they are similuar). Then I look at the execution and see if they are spending a lot of time doing table scans (well it is a percent, but you get the idea). I guess the one change is to work my way down to do all those with 1000 ranking or higher.
January 9, 2013 at 7:04 am
Oh, one other side note. (this has not happend yet, since there were almost no indexes) But I like to only have about 3 Non-Cluster indexes on tables, unless they are for reporting.
Since our reporting tables only get written to once per day, the overhead of having more indexes is offset by the bonus of not doing table scans... then again the most indexes I have added to any of these tables, so far, has been 4.
January 9, 2013 at 7:13 am
dwilliscp (1/9/2013)
Oh, one other side note. (this has not happend yet, since there were almost no indexes) But I like to only have about 3 Non-Cluster indexes on tables, unless they are for reporting.
Have you tested the impact of more than three indexes? Is it unacceptable?
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
January 9, 2013 at 7:17 am
dwilliscp (1/9/2013)
I was trying to find a better approach than what I am currently doing... that is starting with the query, pulling the top 10 and looking at what tables they are using (to see if there are any indexes on them, and if they are similuar). Then I look at the execution and see if they are spending a lot of time doing table scans (well it is a percent, but you get the idea). I guess the one change is to work my way down to do all those with 1000 ranking or higher.
Don't start with missing index stats. That's cart before horse. Start with queries that have poor performance. You're indexing to improve query performance, no indexing to remove entries from a list of index suggestions.
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
January 9, 2013 at 11:13 am
GilaMonster (1/9/2013)
dwilliscp (1/9/2013)
I was trying to find a better approach than what I am currently doing... that is starting with the query, pulling the top 10 and looking at what tables they are using (to see if there are any indexes on them, and if they are similuar). Then I look at the execution and see if they are spending a lot of time doing table scans (well it is a percent, but you get the idea). I guess the one change is to work my way down to do all those with 1000 ranking or higher.Don't start with missing index stats. That's cart before horse. Start with queries that have poor performance. You're indexing to improve query performance, no indexing to remove entries from a list of index suggestions.
Ok must be missing something... the only other way I know.. of keeping things tuned.. is the trace that I have running with every reboot. It traps anything with SET @DurationFilter > 180000000. (3 min run time) If this is what you are talking about.. then why would you ever run the query looking for missing index?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply