January 6, 2009 at 4:43 am
Hi All,
I have a Java application that scrolls a resultset ([font="Courier New"]select * from my_table[/font]), and then fires back an update of the form:
[font="Courier New"]update my_table set my_col=<value> where my_id = <id>
[/font]
(Note, the table being scrolled and the table being updated are the same, so could this be a locking issue?)
There is a primary key (and thus a primary key index) on the column, [font="Courier New"]my_id[/font], and all index stats are calculated before and after the process, as there is an index on the column, [font="Courier New"]my_col[/font].
This application's code has not changed, and yet we were seeing a degradation in performance of the commits. 7,500,000 update statements are issued in total, these are batched up and committed into 25,000 statements at a time. Quite often we see the update query as suspended in Enterprise manager
Nothing else would be running on the box at the same time as this process. The box is a winXP 64-bit server with 2 1.86GhZ quad core processors running sqlserver 2005, and Java 1.6. It has a raid-5 striped disk array for the data and transaction log files, and all default parameters for initial sizes of log and data files were selected during initial installation and DB creation.
We were seeing a gradual slowdown in performance of the commits, to around 46 minutes for 1,000,000 rows to be committed. We were looking into this performance problem, and we had identified a couple of potential performance improvements, one of which being to move the transaction logs onto a separate disk array, or to shrink the transaction logs.
We then happened to have a powercut (and no, we don't have a UPS attached to this box!), and now the performance has improved to 15 minutes per 1,000,000 rows to be committed.
So, I guess my question is this:
What could have happened during the powercut/DB recovery to improve performance so dramatically?
And more generally, what are the main factors effecting commit performance?
TIA,
-James
January 6, 2009 at 12:29 pm
The only things I can think of that power failure would do that would improve performance like that would be to flush the execution cache, which might get rid of an inappropriate execution plan, or clean up a memory leak that was crowding out SQL Server from RAM it would otherwise use. I'd tend to suspect the later over the former.
One of the main causes of performance degradation is fragmentation. Look at table fragmentation and index fragmentation. Both can slow down all table operations.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2009 at 3:21 am
Thanks for the reply, is there a good/easy way to tell if SQL server is suffering a memory leak?
Could the fact that the update is quite often in the suspended state when the program is running slowly be a pointer to some lock contention?
Also, (I should have said before) the source table is truncated and recreated each time prior to running this application, so I wouldn't expect the table to be too fragmented.
cheers,
-James
January 7, 2009 at 7:00 am
As far as memory leaks go, I'd look at anything other than SQL running on that server. Are there any other services (other than necessary Windows ones) running? How about applications? Etc.
On table/index fragmentation, if you right-click the indexes in Management Studio, and go to Properties, you can find the fragmentation level in there. Table fragmentation = clustered index fragmentation, so check that one to see how bad the table itself is.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2009 at 7:11 am
Great, thanks...I'll keep an eye on those things and report back if I find anything....the annoying thing at the moment is that there isn't a problem!
(There's just no pleasing some people!)
cheers,
-James
January 8, 2009 at 2:59 am
Your batching strategy seems suspect - an update for number of rows should not be coming anywaere near your stated timeframes, provided the server box is adaquately powered. This is evidenced by your suspended queries also. I'd suspect you're updating single rows in multiple data pages, generating far higher volumes of pagelocks than needed.
How is it being implemented if "as rows are scrolled", individual "1 row update" statements are being generated?
At best, possibly you should be looking at committing in smaller batches. You may also want to revise your data clustering so that data eing updated in the batches are physically closer together.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply