January 21, 2013 at 8:11 pm
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
January 21, 2013 at 11:12 pm
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;-)
January 22, 2013 at 1:37 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2013 at 2:35 am
Thanks for all,
I will consider them before applying.
January 22, 2013 at 3:27 am
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?
January 22, 2013 at 3:34 am
Dung Dinh (1/22/2013)
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;-)
January 22, 2013 at 3:38 am
Bhuvnesh (1/22/2013)
Dung Dinh (1/22/2013)
Do we should create a non-clustered/clustered index on GUI column?
WHat are they ?
I mean that the columns with data type = uniqueidentifier.
January 22, 2013 at 3:46 am
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;-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply