script can be used for any database.
script lists all missing index with create statement.
go
/*
Description:- This Query will provide you detail of missing indexes on a table and also
prepare sql script for new index
Author:- Arun Kumar
Email:- arun_kumar112@yahoo.co.in,arun.kumar@iqor.com
Mobile:- 9811539923
*/SELECT
DISTINCT
@@SERVERNAME AS [ServerName]
, DB_NAME() AS [DatabaseName]
, SCHEMA_NAME([systemObject].[schema_id]) AS [SchemaName]
,[systemObject].Object_ID AS ObjectID
, [systemObject].[name] AS [ObjectName]
, CASE [systemObject].[type]
WHEN 'U' THEN 'Table'
WHEN 'V' THEN 'View'
ELSE 'Unknown'
END AS [ObjectType]
, [MissingIndexDetail].[equality_columns] AS [EqualityColumns]
, [MissingIndexDetail].[inequality_columns] AS [InequalityColumns]
, [MissingIndexDetail].[included_columns] AS [IncludedColumns]
, [MissingIndexGroupState].[user_seeks] AS [ExpectedIndexSeeksByUserQueries]
, [MissingIndexGroupState].[user_scans] AS [ExpectedIndexScansByUserQueries]
, [MissingIndexGroupState].[last_user_seek] AS [ExpectedLastIndexSeekByUserQueries]
, [MissingIndexGroupState].[last_user_scan] AS [ExpectedLastIndexScanByUserQueries]
, [MissingIndexGroupState].[avg_total_user_cost] AS [ExpectedAvgUserQueriesCostReduction]
, [MissingIndexGroupState].[avg_user_impact] AS [ExpectedAvgUserQueriesBenefitPct],
IndexName='[ix_' + tbl.name + N'_'
+ REPLACE(REPLACE(REPLACE(REPLACE(
ISNULL(equality_columns,N'')+
CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N'_' ELSE N'' END
+ ISNULL(inequality_columns,''),',','')
,'[',''),']',''),' ','_')
+ CASE WHEN included_columns IS NOT NULL THEN N'_includes' ELSE N'' END + N']' ,
IndexCreateScripts=N'CREATE INDEX [ix_' + tbl.name + N'_'
+ REPLACE(REPLACE(REPLACE(REPLACE(
ISNULL(equality_columns,N'')+
CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N'_' ELSE N'' END
+ ISNULL(inequality_columns,''),',','')
,'[',''),']',''),' ','_')
+ CASE WHEN included_columns IS NOT NULL THEN N'_includes' ELSE N'' END + N'] ON '
+ [statement] + N' (' + ISNULL(equality_columns,N'')
+ CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N', ' ELSE N'' END
+ CASE WHEN inequality_columns IS NOT NULL THEN inequality_columns ELSE N'' END +
') ' + CASE WHEN included_columns IS NOT NULL THEN N' INCLUDE (' + included_columns + N')' ELSE N'' END
+ N' WITH ('
+ N'FILLFACTOR=80, ONLINE=ON'
+ N')'
+ N';'
FROM
[sys].[dm_db_missing_index_details] AS [MissingIndexDetail]
CROSS APPLY sys.dm_db_missing_index_columns ([MissingIndexDetail].index_handle)
LEFT JOIN [sys].[dm_db_missing_index_groups] AS [MissingIndexGroup]
ON [MissingIndexDetail].[index_handle] = [MissingIndexGroup].[index_handle]
LEFT JOIN [sys].[dm_db_missing_index_group_stats] AS [MissingIndexGroupState]
ON [MissingIndexGroup].[index_group_handle] = [MissingIndexGroupState].[group_handle]
INNER JOIN [sys].[objects] AS [systemObject]
ON [MissingIndexDetail].[object_id] = [systemObject].[object_id]
inner join sys.databases db on [MissingIndexDetail].database_id=db.database_id
inner join sys.tables tbl on [systemObject].object_id=tbl.object_id
WHERE
[MissingIndexDetail].[database_id] = DB_ID() -- Look in the Current Database
AND [systemObject].[type] IN ('U','V') -- Look in Tables & Views
AND [systemObject].[is_ms_shipped] = 0x0 -- Exclude System Generated Objects
--and( tbl.name like '%Accounting%' or tbl.name like '%treasury%') --Add table name for filter
--and [MissingIndexGroupState].[avg_user_impact]>=50; -- filter impact percentage
go 
Are you rebuilding statistics twice on your tables? Longtime expert, Andy Warren, shows why you might be asking more work of your SQL Server than is required.
2008-07-29
9,130 reads
Optimize SQL Server non-clustered indexes and queries by considering index fields, compound indexes and SQL Server statistics' impact on non-clustered indexes.
2008-03-05
3,828 reads
Indexes directly affect the performance of database applications. This article uses analogies to describe how indexes work. The estimated execution plan feature of the Query Window is utilized to compare the performance of two queries in a batch.
2007-12-04
5,686 reads
SQL Server 2000 has indexed views, which can greatly improve database performance. However there are a number of restrictions on building the view, including the restriction against outer joins. So how can this work? New author Jean Charles Bulinckx brings us a technique that can help you get around this restriction.
2005-05-19
23,721 reads
There is nothing spectacular about using indexes per say. However, on many occasions I have come across a variety of SQL coders that never consider validating that the index they think they are using is efficient or even being used at all. We can all put indexes on the columns that we think will be required to satisfy individual queries, but how do we know if they will ever be used. You see, if the underlying table data is constructed, contains, or is ordered in a particular way, our indexes may never be used. One of the factors around the use of an index is its clustering factor and this is what this article is about.
2004-12-23
3,412 reads