June 22, 2012 at 10:56 am
I want to include the schema as one of the columns since the tables are duplicated in two different schemas?
The schema Name is included in the CREATE INDEX Statement.
Any help would be greatly appreciated?
-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011
SELECT TOP 25
DB_NAME(dm_mid.database_id) AS DatabaseName,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 22, 2012 at 11:07 am
Join to sys.tables and then sys.schemas
p.s. Don't automatically create 'missing' indexes, they're guidelines not directives.
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
June 22, 2012 at 11:11 am
It is already getting the Schema Name.
I'm unsure about the JOINs.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 22, 2012 at 11:22 am
Join to sys.tables on object_id, then to sys.schemas on schema_id. Add sys.schemas.name to the list of columns
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
June 22, 2012 at 11:40 am
GilaMonster (6/22/2012)
Join to sys.tables on object_id, then to sys.schemas on schema_id. Add sys.schemas.name to the list of columns
Thanks!
I could exclude the sys.schemas table and use the Schama_Name Function on Schema_Id.
What existing Table & Column to a JOIN to from the sys.Tables Column?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 22, 2012 at 11:51 am
Welsh Corgi (6/22/2012)
I could exclude the sys.schemas table and use the Schama_Name Function on Schema_Id.
Yes, you could.
What existing Table & Column to a JOIN to from the sys.Tables Column?
GilaMonster (6/22/2012)
Join to sys.tables on object_id, then to sys.schemas on schema_id. Add sys.schemas.name to the list of columns
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
June 22, 2012 at 2:14 pm
The following script includes the schema Name.
What are some of the pros and cons of using the DMV's as opposed to the Database Tuning Advisor?
SELECT
DB_NAME(dm_mid.database_id) AS DatabaseName,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
Schema_Name(tbl.schema_id) AS SchemaName,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
INNER JOIN sys.tables AS tbl ON dm_mid.object_id = tbl.Object_ID
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 25, 2012 at 4:42 am
Welsh Corgi (6/22/2012)
The following script includes the schema Name.What are some of the pros and cons of using the DMV's as opposed to the Database Tuning Advisor?
http://dba.stackexchange.com/questions/4430/using-dta-vs-evaluating-dmvs
gsc_dba
June 25, 2012 at 1:06 pm
Thanks for the link @gsc_dba!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 25, 2012 at 3:13 pm
Welsh Corgi (6/22/2012)[/bI could exclude the sys.schemas table and use the Schama_Name Function on Schema_Id.
My $.02: The SCHEMA_NAME function can only be used in current database. Using sys.schemas will be better.
June 25, 2012 at 3:23 pm
Good point @Wildcat.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply