InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:01:49 GMT20http://www.sqlservercentral.com/Forums/Topic1409764-391-1.aspx[quote][b]Dung Dinh (1/22/2013)[/b][hr]I mean that the columns with data type = uniqueidentifier.[/quote] 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.Tue, 22 Jan 2013 03:46:57 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1409764-391-1.aspx[quote][b]Bhuvnesh (1/22/2013)[/b][hr][quote][b]Dung Dinh (1/22/2013)[/b][hr][quote][b]Do we should create a non-clustered/clustered index on GUI column? [/quote] WHat are they ?[/quote]I mean that the columns with data type = uniqueidentifier.Tue, 22 Jan 2013 03:38:13 GMTDung Dinhhttp://www.sqlservercentral.com/Forums/Topic1409764-391-1.aspx[quote][b]Dung Dinh (1/22/2013)[/b][hr][quote][b]Do we should create a non-clustered/clustered index on GUI column? [/quote] WHat are they ?Tue, 22 Jan 2013 03:34:53 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1409764-391-1.aspx[quote][b]GilaMonster (1/22/2013)[/b][hr]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.[/quote]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?Tue, 22 Jan 2013 03:27:48 GMTDung Dinhhttp://www.sqlservercentral.com/Forums/Topic1409764-391-1.aspxThanks for all,I will consider them before applying.Tue, 22 Jan 2013 02:35:08 GMTDung Dinhhttp://www.sqlservercentral.com/Forums/Topic1409764-391-1.aspxYou 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.Tue, 22 Jan 2013 01:37:31 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1409764-391-1.aspxdefinitely 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?Mon, 21 Jan 2013 23:12:50 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1409764-391-1.aspxHi 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,[code="sql"]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[/code]Mon, 21 Jan 2013 20:11:35 GMTDung Dinh