Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rikleo2001
Posting Yak Master
185 Posts |
 Posted - 2013-02-07 : 05:34:26
|
| GuysI am reviewing internal old application, had 5 GUID columns with non-unique values and 5 million records.someone created indexes per GUID column (non-clustered/non-unique).They keeps getting performance problems (slow), but as soon as we drop and re-create the indexes.. speed is faster.After reviewing the data, I found out the multiple queries and multiple joins and 5 millions, and indexing not helping too much.So I dropped all the indexes, speed is faster again.Not sure what is happening and what should be Indexing best practice in this situation?Thanks in advance.SKR |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-07 : 05:46:39
|
| may be statistics were not rebuild on indexes regularly. If table is target of frequent batch DML operations, fragmentation can occur------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
rikleo2001
Posting Yak Master
185 Posts |
Posted - 2013-02-07 : 05:49:56
|
| I use maintenance solution as per http://ola.hallengren.com and it is re-building indexes and update statistics daily basis where fragmentation is > 30. so that is not the case either.SKR |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-07 : 06:02:24
|
| Are you sure indexes are getting used? Also when you say performance is slow, what operations were you trying to perform? retrieval or DML operations?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rikleo2001
Posting Yak Master
185 Posts |
Posted - 2013-02-07 : 06:50:25
|
| yes, they are getting used, I checked them in execution plan.Only select operation with joins on those GUID columns with occasional inserts.SKR |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-02-08 : 14:06:18
|
| When you see Indexes being used in Query execution plan, make sure you check for Index Fragmentation at regular intervals and create a maintenance activity plan every one month or 45 days. Rebuilding or reorganizing Indexes will make your queries run faster. |
|
|
|
|
|
|
|
|
GUID columns, NON-Unique, Indexing?