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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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