Install the following procedure in your usual database admin folder and run it occasionally to see where new indexes would benefit your queries.
Don’t install all the indexes it suggests. Some will be huge and pointless as they will have a bigger overhead on maintenance than their benefit on queries. As a general rule I suggest that no index should have more than four main columns and preferably less.
When adding new indexes, look at existing indexes to see if they are now obsolete.
--=============================================
-- Author: David Bridge
-- Create date: September 2011
-- Description: Suggest missing indexes using DMV's
-- =============================================
CREATE PROCEDURE DBA_MissingIndexes
AS
BEGIN
SET NOCOUNT ON;
;WITH CTE AS (
SELECT TOP 20
mi.statement
,equality_columns
,inequality_columns
,included_columns
,database_id
,object_id
,unique_compiles
,avg_user_impact
FROM sys.dm_db_missing_index_group_stats gs
INNER JOIN sys.dm_db_missing_index_groups ig on ig.index_group_handle = gs.group_handle
INNER JOIN sys.dm_db_missing_index_details mi on mi.index_handle = ig.index_handle
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC
)
SELECT
*,
CREATE_statement =
REPLACE(
REPLACE(
REPLACE(
REPLACE('CREATE NONCLUSTERED INDEX IX_OBJECTNAME_XXXX ON TABLENAME (COLUMNS) INCLUDED'
,'OBJECTNAME' , PARSENAME(statement,1) )
,'TABLENAME' , statement )
,'COLUMNS' , REPLACE(CTE.equality_columns + ',' + CTE.inequality_columns, ',,' , ',') )
,'INCLUDED' , CASE
WHEN included_columns IS NULL THEN ''
ELSE (REPLACE(' INCLUDE (INCLUDEDCOLUMNS)', 'INCLUDEDCOLUMNS' , included_columns)) END )
FROM CTE
END
How do you know which of these index suggestions are useful if you don’t have the sql statements that would have run against them?
Here’s an idea…
Assuming you use stored procedures, you can search for procedures that use the main columns. This should give you a good idea how the tables are used.
To find procedures using the column you could do a search like this
SELECT object_name(object_id) FROM sys.sql_modules where definition like '%ColumnName%'
Alternatively copy this procedure to your admin database and use it to search for all occurrences of the column
-- =============================================
-- Author: David Bridge
-- Create date: July 2011
-- Description: Search all stored procedures for a string
-- =============================================
ALTER PROCEDURE [dbo].[DBA_SearchAllStoredProcedures]
(
@SearchArg varchar(200)
)
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #temp (DB_name varchar(128), Object_name varchar(128),Type varchar(2));
DECLARE @sql varchar(1000) = 'USE [?];
INSERT INTO #temp
SELECT
db_name() as db_name
,object_name(sm.object_id) as object_name
,so.type as type
FROM sys.sql_modules sm WITH (NOLOCK)
INNER JOIN sys.sysobjects so on sm.object_id = so.id
WHERE sm.definition like ''%@SearchArg%''
ORDER BY 1,2,3'
SET @sql = REPLACE (@sql,'@SearchArg',@SearchArg)
exec sp_MSforeachdb @sql
SELECT * FROM #temp
SELECT
'USE [' + db_name + ']; SELECT ''USE [' + db_name + '];'' EXEC sp_helptext [' + object_name + ']'
FROM #temp
DROP TABLE #temp
END
This will generate two results
The first is a list of all the procedures together with their holding database
The second is a command list that (assuming you have output results to text) you can cut and paste in to a new window to see the body of all the selected procedures.
e.g.
USE [ABC]; SELECT ‘USE [ABC];’ EXEC sp_helptext [procedurename]
USE [XYZ]; SELECT ‘USE [XYZ];’ EXEC sp_helptext [procedurename]
Search (CTRL+F) the resulting script of the affected procedures to see how they are used without having to navigate to the actual procedures
