Jeff Moden (7/31/2014)

---

murnilim9 (7/31/2014)

---

SQL Guy 1 (7/31/2014)

---

What is data type, length, and number of columns?

How intensively it is used - from sys.dm_db_index_usage_stats ?

Thanks for your kind response . Appreciate it!

Here is the data for 2 different databases ( the indexes get fragmented in only a few hours after rebuild ):

on Database DB1

Index IX_P (Non clustered index)

Index key column :

P1(datetime) , size : 8 , Identity : No

included column :

ReaderId (Int)

fragmentation : 92.31%

pages : 806

fill factor 65%

Usage stat :

USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

0 702 0 2826040

--------------------------------------------------------------------------------

on Database DB2

Index IX_A

Profile :

Index Key columns :

A1 (int)

A2 (TinyInt)

A3 (Bigint)

Fragmentation :81.44 %

Pagecount : 62593

Fill Factor : 70 %

Usage stat :

USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

49476 0 0 0

0 0 65176 4753949

8247327 0 2551491 2873118

0 1 0 2

-----------------------------------------------------------------------------

Index B (nonclustered)

Profile :

Index Key columns:

B1 (Bigint)

Included column :

X1 (tinyint)

X2 (varbinary(max)

X3 (bit)

X4 (int)

X5 (tinyint)

X6 (datetime)

X7 (bigint)

X8 (uniqueidentifier)

Fragmentation : 97.24%

Pagecount : 63379

Fill Factor : 70%

Usage stat :

USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

0 0 0 2873118

2 weeks ago :

USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

0 4 0 15808493

7580190 0 11550284

----------------------------------------------------------------------------

Index IX_C (nonclustered)

Profile :

Index key columns :

C1(bigint)

Fragmentation : 79.43%

Pagecount : 2163

Fillfactor : 70%

Usage stat:

USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

225313662780 563608

1 0 0 0

2845411199900 10

What do you think about it ?

Many thanks 🙂

Are any of these causing performance issues?

Also, except for unique indexes (which may be part of constraints), I'd be tempted to simply drop any index that had a large number of User_Updates and very few or zero index seeks. User_Scans (a bit faster than heap or CI scans) can be useful indexes but, with 2,826,040, 0 seeks, and only 702 scans, Index IX_P (Non clustered index) looks pretty useless to me and probably drop that one as well, unless it's a unique index.