September 15, 2010 at 1:26 pm
Hi,
Sql Server 2005 SP3
Working on a performance issue with a customer who had a deadlocking problem and they enabled read_committed_snapshot on the database. After doing this they had performance issues so they switched it back off again, however they state that the performance issue still remained.
I have checked the sys.databases and it shows it as being off.
I have run some simple queries against the database which retrieve single records from a table via a clustered index seek and they now use seconds of CPU time to execute. I have run some traces and can see that the CPU utilization on the server has now dramatically increased and when I compare the traces taken since the change with ones taken before the change I can see the massive increase in CPU time for the queries, and so an increase in elapsed time. Also the amount of I/O performed by the queries is the same so I do not believe it is I/O related, but could be wrong
The customer believes that switching on read_committed_snapshot has done something to the database and even though it is now disabled it has left something behind.
I do not know where to go with this issue now, so any suggestions are welcome.
Ronnie
September 15, 2010 at 3:12 pm
It sounds like the poor performance has a very weak correlation to the switching on and off for the setting. I have used read committed snapshot and toggled off/on with no negative affect. If CPU is already heavily utilized then your tests for single records may be skewed. This could cause you to have a big difference in CPU time and elapsed time. Execute "SET STATISTICS TIME ON" and then run your benchmark tests. This will show you how much CPU time that statement(s) used versus elapsed time. Also, and you may have already done this, but if not look in the SQL Log to make sure there aren't any problems like memory trimming or a mini stack dump.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply