SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


When to Index


When to Index

Author
Message
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 775
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
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32008 Visits: 18551
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

GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23325 Visits: 9730
"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
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 775
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.
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 775
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?
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 775
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)
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32008 Visits: 18551
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

dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 775
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.)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86630 Visits: 45251
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

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


SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32008 Visits: 18551
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search