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

Detect missing indexes Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 8:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 155, Visits: 1,019
Hi all,
I have a script to detect missing indexes in my database, but I'm not sure about info in this script to help me determine to create these indexes. Could you explain the meaning of the info and which main factors will help us determine to create missing indexes?
Thanks,
SELECT  sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL
THEN ''
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ''
ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
ORDER BY Impact DESC


Post #1409764
Posted Monday, January 21, 2013 11:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 2,841, Visits: 3,985
definitely above query will provide you some hint BUT not tell you exact picture, i will suggest you to pick the long running or resource intensive queries, have a detailed look into it with the help of exec plan and then decide which tables need index modification or additions?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1409791
Posted Tuesday, January 22, 2013 1:37 AM


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 @ 9:22 AM
Points: 40,632, Visits: 37,094
You don't create indexes just based on that. The missing index DMV are suggestions, nothing more. Create an index on a test server, run a representative load, see if the index improved performance. If so, create on prod. If not, discard and test further.


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 #1409861
Posted Tuesday, January 22, 2013 2:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 155, Visits: 1,019
Thanks for all,
I will consider them before applying.
Post #1409877
Posted Tuesday, January 22, 2013 3:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 155, Visits: 1,019
GilaMonster (1/22/2013)
You don't create indexes just based on that. The missing index DMV are suggestions, nothing more. Create an index on a test server, run a representative load, see if the index improved performance. If so, create on prod. If not, discard and test further.

BTW, I have another question. Do we should create a non-clustered/clustered index on GUI column?
I'm monitoring a database and see that they always use GUI column as parent-child relationship. I remember that indexes GUI columns are frequently fragmentation. Is is right?
Post #1409893
Posted Tuesday, January 22, 2013 3:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 2,841, Visits: 3,985
Dung Dinh (1/22/2013)
[quote][b]Do we should create a non-clustered/clustered index on GUI column?
WHat are they ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1409897
Posted Tuesday, January 22, 2013 3:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 155, Visits: 1,019
Bhuvnesh (1/22/2013)
Dung Dinh (1/22/2013)
[quote][b]Do we should create a non-clustered/clustered index on GUI column?
WHat are they ?


I mean that the columns with data type = uniqueidentifier.
Post #1409900
Posted Tuesday, January 22, 2013 3:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 2,841, Visits: 3,985
Dung Dinh (1/22/2013)
I mean that the columns with data type = uniqueidentifier.
people use them as a key part though it is not recommended to have lengthy keys or indexes it leads to fragmentation
BUT you cant decide whether or which guid typed column will be part of index without looking into query.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1409902
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse