June 27, 2011 at 3:00 am
Hi,
I have added an index to a table, as my query was doing a clustered index scan on it. After adding the index, the scan has turned into an index seek, the overall reads have come down by around 20%, but the CPU and Duration have gone up, compared to the same query. before index addition. I am not sure, what could be the explanation for this, can someone please help?
Regards - Yasub
June 27, 2011 at 3:02 am
June 27, 2011 at 3:25 am
Here you go...
June 27, 2011 at 3:28 am
The above exec plan, iafter adding index, here's the one before:
The index is for the first half of the plan.
June 27, 2011 at 3:42 am
Apologies for posting info in bits and pieces, here are the stats:
Before Index:
Cpu 156, Reads 4356, Writes 10, Duration 155
After Index:
Cpu 265, Reads 3688, Writes 9, Duration 414
June 27, 2011 at 4:00 am
the query plan looks fine
Could you try running the client stats once more after executing
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
http://msdn.microsoft.com/en-us/library/ms187762.aspx
http://msdn.microsoft.com/en-us/library/ms174283.aspx
Probably this will make the results more accurate.
June 27, 2011 at 4:07 am
Hey Jayanth, this problem is on the pre production environment, cant run these DBCC commands there right...
June 27, 2011 at 5:07 am
You're retrieving 261000 rows out of a table that has 268000 unique values (not sure if that's how many rows it has or not). I'm a little surprised that it's able to do an index seek at all.
It's not surprise to me that the despite getting a seek, you're seeing slower performance. When it was doing the scan, it was an ordered value returned to a merge join, which is a very efficient join for larger data sets like this. With the seek now, you're getting a hash join, which requires a temporary table be created in order to process the data and it's just not as efficient as a merge in this situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 27, 2011 at 10:39 pm
Thanks Grant, i see the point. Any suggestions as to how i can solve this?
June 28, 2011 at 4:19 am
yasubmj (6/27/2011)
Thanks Grant, i see the point. Any suggestions as to how i can solve this?
Filter the data in some fashion is the best answer I have. Assuming the selectivity is somewhat analogous to the number of rows (it might be, it might not), then you're going through a very substantial portion of the table. Better to find a method for reducing that, some additional criteria you can add to cut it down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 29, 2011 at 4:03 am
Index Fill Factor ?
is this table is read extensive or dml extensive ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 30, 2011 at 4:44 am
Thats the big problem Grant, 🙁 , the filtering condition is on the joining table and not this one.... 🙁
Syed, While creating the new index, kept the fillfactor as its default value.
The table is read intensive.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply