September 23, 2008 at 8:54 am
I added an index on a table which was supposed to improve performance. When i checked the performance before applying the index the logical reads was a 4-digit number, after applying the index the logical reads for the same table shot up by 15% (6-digit number). Whereas the response time had dramatically reduced by 10%. The application crashed as a result of this.
Can you please explain why this would have happened and what I can do to improve the performance of this query/table?
September 23, 2008 at 9:06 am
What is the execution plan and what are the types/indexes on?
Need more information.
September 23, 2008 at 9:08 am
Hi
Have u indexed the correct column?? one more thing is that when u are using a SQL to retrive data from the table then the index must match the where clause??
Can you post your structure of the table and then SQL you are using to retrive data from the table
Cheers
🙂
September 23, 2008 at 9:50 am
I applied a non clustered index on the table which we have tested. We have also applied the index on our other sites with no issues.
This is one of the indexes which was recommended by DTA (Database Tuning Advisor) and DMVs (Dynamic Management Views, dm_* tables) for performance.
September 23, 2008 at 10:06 am
Doesn't mean that the index is completely appropriate. The DTA sometimes gets it wrong.
Can you please post table structure, index defs, query and exec plan please. (exec plan saved as a .sqlplan file, zipped and attached)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2008 at 10:55 am
Have a look at:
about What you should know if you are using Logical IOs to measure query plan quality ?
MJ
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply