| Author |
 Topic  |
|
|
rikleo2001
Posting Yak Master
New Zealand
180 Posts |
 Posted - 02/07/2013 : 05:34:26
|
Guys
I 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
India
47069 Posts |
Posted - 02/07/2013 : 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 MVP
http://visakhm.blogspot.com/
|
 |
|
|
rikleo2001
Posting Yak Master
New Zealand
180 Posts |
Posted - 02/07/2013 : 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
India
47069 Posts |
Posted - 02/07/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
rikleo2001
Posting Yak Master
New Zealand
180 Posts |
Posted - 02/07/2013 : 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
152 Posts |
Posted - 02/08/2013 : 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. |
|
|
| |
Topic |
|
GUID columns, NON-Unique, Indexing?
