When to Index

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » When to Index

16 posts, Page 1 of 2

1 2 »»

When to Index

Rate Topic

Display Mode

Topic Options

Author

Message

dwilliscp

Posted Monday, January 07, 2013 11:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187, Visits: 332

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

SQLRNNR

Posted Monday, January 07, 2013 11:32 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

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

GSquared

Posted Monday, January 07, 2013 11:34 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

"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

dwilliscp

Posted Monday, January 07, 2013 12:21 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187, Visits: 332

SQLRNNR (1/7/2013)

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

dwilliscp

Posted Monday, January 07, 2013 12:29 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187, Visits: 332

GSquared (1/7/2013)

My readings are:

A Guide for the Accidental DBA, Mastering Server Profiler, Performance Tuning DMV, SQL Server Tacklebox.

Any suggestions?

Post #1403811

dwilliscp

Posted Monday, January 07, 2013 12:33 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187, Visits: 332

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

SQLRNNR

Posted Monday, January 07, 2013 1:07 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

dwilliscp (1/7/2013)

GSquared (1/7/2013)

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

Post #1403839

dwilliscp

Posted Monday, January 07, 2013 1:28 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187, Visits: 332

SQLRNNR (1/7/2013)

dwilliscp (1/7/2013)

GSquared (1/7/2013)

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

GilaMonster

Posted Monday, January 07, 2013 3:12 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732, Visits: 29,996

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

SQLRNNR

Posted Monday, January 07, 2013 9:59 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

dwilliscp (1/7/2013)

SQLRNNR (1/7/2013)

dwilliscp (1/7/2013)

GSquared (1/7/2013)

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

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.

Post #1403962

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions