kapil_kk (12/14/2013)

---

ScottPletcher (12/13/2013)

---

I suggest making index changes scripted below, following these steps:

1) Capture and save the existing index missing/usage stats for that table immediately, before any hanges are made.

2) Run the code to make index changes; the code may run a while, depending on the table size.

3) Allow some time for table activity, then re-capture the index missing/usage stats and compare to original.

DECLARE @list_missing_indexes bit

DECLARE @table_name_pattern sysname

SET @list_missing_indexes = 1

SET @table_name_pattern = 'Employee'

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

-- list missing index info

IF @list_missing_indexes = 1

BEGIN

SELECT

GETDATE() AS capture_date,

DB_NAME(mid.database_id) AS Db_Name,

OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,

mid.equality_columns, mid.inequality_columns, mid.included_columns,

migs.*,

mid.statement, mid.object_id, mid.index_handle

FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)

LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON

mig.index_handle = mid.index_handle

LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON

migs.group_handle = mig.index_group_handle

--order by

--DB_NAME, Table_Name, equality_columns

WHERE

1 = 1

AND mid.database_id = DB_ID()

AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern

ORDER BY

--avg_total_user_cost * (user_seeks + user_scans) DESC,

Db_Name, Table_Name, equality_columns, inequality_columns

END --IF

-- list index usage stats (seeks, scans, etc.)

SELECT

ius2.row_num, DB_NAME() AS db_name, OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name, i.name AS index_name,

FILEGROUP_NAME(i.data_space_id) AS filegroup_name,

(SELECT DATEDIFF(DAY, create_date, GETDATE()) FROM sys.databases WHERE name = 'tempdb') AS sql_up_days,

dps.row_count,

fk.Reference_Count AS fk_ref_count,

ius.index_id, --ius.user_seeks + ius.user_scans AS total_reads,

ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,

ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,

ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,

ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update

FROM sys.indexes i WITH (NOLOCK)

INNER JOIN sys.objects o WITH (NOLOCK) ON

o.object_id = i.object_id

LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON

dps.object_id = i.object_id AND

dps.index_id = i.index_id

LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON

ius.database_id = DB_ID() AND

ius.object_id = i.object_id AND

ius.index_id = i.index_id

LEFT OUTER JOIN (

SELECT

database_id, object_id, MAX(user_scans) AS user_scans,

ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans

FROM sys.dm_db_index_usage_stats WITH (NOLOCK)

WHERE

database_id = DB_ID()

--AND index_id > 0

GROUP BY

database_id, object_id

) AS ius2 ON

ius2.database_id = DB_ID() AND

ius2.object_id = i.object_id

LEFT OUTER JOIN (

SELECT

referenced_object_id, COUNT(*) AS Reference_Count

FROM sys.foreign_keys

WHERE

is_disabled = 0

GROUP BY

referenced_object_id

) AS fk ON

fk.referenced_object_id = i.object_id

WHERE

i.object_id > 100 AND

i.is_hypothetical = 0 AND

i.type IN (0, 1, 2) AND

o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND

(

o.name LIKE @table_name_pattern

)

ORDER BY

--row_count DESC,

--ius2.row_num, --user_scans&|user_seeks

-- list clustered index first, if any, then other index(es)

db_name, table_name, CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END, index_name

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [uq1_Employee];

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [PK_Employee];

ALTER TABLE [dbo].[Employee] ADD

CONSTRAINT [uq1_Employee] UNIQUE CLUSTERED

(

[InstanceId] ASC,

[SiteId] ASC,

[LocalEmployeeId] ASC

)WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

ALTER TABLE [dbo].[Employee] ADD

CONSTRAINT [PK_Employee] PRIMARY KEY NONCLUSTERED

(

[EmployeeId] ASC

)WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]

Hey Scott,

Can you please explain me what this script will do ?

DO I need to drop indexes for whole database tables and need to run this script for every tables and recreate the indexes?