Missing indexes = poor performance

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

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s