Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

When to Index Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 11:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:06 AM
Points: 321, Visits: 569
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

Post #1403770
Posted Monday, January 7, 2013 11:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 21,619, Visits: 15,275
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1403778
Posted Monday, January 7, 2013 11:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
"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
Post #1403779
Posted Monday, January 7, 2013 12:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:06 AM
Points: 321, Visits: 569
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.
Post #1403808
Posted Monday, January 7, 2013 12:29 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:06 AM
Points: 321, Visits: 569
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?
Post #1403811
Posted Monday, January 7, 2013 12:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:06 AM
Points: 321, Visits: 569
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)
Post #1403816
Posted Monday, January 7, 2013 1:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 21,619, Visits: 15,275
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1403839
Posted Monday, January 7, 2013 1:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:06 AM
Points: 321, Visits: 569
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.)
Post #1403851
Posted Monday, January 7, 2013 3:12 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403890
Posted Monday, January 7, 2013 9:59 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 21,619, Visits: 15,275
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1403962
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse