October 29, 2008 at 2:19 pm
I'm having a performance problem, while migrating a large amount of data from one database to another within the same SQL instance.
The main wait type is - by far - WRITELOG on the receiving database (the one accepting the data).
I have set my databases on SIMPLE recovery and moved the log file of the target db to a separate physical drive (RAID 5), in an effort to improve the performance (currently 12 hrs on an otherwise inactive server).
As an extra step I would also like to lessen the checkpoint frequency, to lessen the impact of this operation on the overall performance of my process.
Is is possible to configure the checkpoint frequency at the database level, and what is the config parameter?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 29, 2008 at 9:37 pm
First - is the migration of data occurring in a single database transaction or multiple ? If it is a single transaction then you should expect that your log file will grow to be quite large (depending on the data volume). If it is, can you break up the data import into multiple transactions ?
Second - putting the transaction log onto a RAID 5 disk array is generally a bad choice. This is because for every write operation you actually incur a number of read operations as well (from memory it is something like 4 : 1). This is because the very nature of RAID 5 which needs to be able to create a checksum of the data that is being written so it needs to read all relevant data (for the sectors that are affected) so that the new checksum can be calculated. You should consider something like mirrored disks or RAID 10 for transaction log files.
I would explore the above options before considering changing the checkpoint interval.
October 30, 2008 at 4:33 am
happycat59 (10/29/2008)
First - is the migration of data occurring in a single database transaction or multiple ? If it is a single transaction then you should expect that your log file will grow to be quite large (depending on the data volume). If it is, can you break up the data import into multiple transactions ?Second - putting the transaction log onto a RAID 5 disk array is generally a bad choice. This is because for every write operation you actually incur a number of read operations as well (from memory it is something like 4 : 1). This is because the very nature of RAID 5 which needs to be able to create a checksum of the data that is being written so it needs to read all relevant data (for the sectors that are affected) so that the new checksum can be calculated. You should consider something like mirrored disks or RAID 10 for transaction log files.
I would explore the above options before considering changing the checkpoint interval.
Thank you for the response.
Yes, I know that RAID 5 is no good for the log; unfortunately, in my organization, we have not yet invested in anything other than RAID 5. I will need to make the case to our storage techs, and your confirmation helps.
Thanks again!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 31, 2008 at 12:24 pm
recovery interval is a server level setting and cannot (to my knowledge) be configured for databases independently.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 31, 2008 at 1:03 pm
TheSQLGuru (10/31/2008)
recovery interval is a server level setting and cannot (to my knowledge) be configured for databases independently.
Thanks, how can I configure it at the server level?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 1, 2008 at 10:50 am
See here for description: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/e4734b3b-8fbe-4b65-9c48-91b5a3dd18e1.htm
and here for setting: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9f38eba6-39b1-4f1d-ba24-ee4f7e2bc969.htm
NOTE: you can really cause unintended consequences if you mess with this setting, so be careful and watch what the server does for a few hours/days after adjusting this one.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 1, 2008 at 12:05 pm
Hi,
Even if you are in simple recovery, everything needs to go into the log unless you are doing minimally logged operations ("Minimally Logged Operations" in BoL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f2a68e0f-9190-45c4-abee-1b2ebbb13817.htm).
And even if you fiddle with the recovery interval, log records will still be written by the Log manager when you commit your transactions.
Is there anyway at all you can change the way in which you are moving the data to use minimally logged operations?
🙂
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 1, 2008 at 1:10 pm
Bear in mind that if you lessen the checkpoint frequency you're going to increase the impact it has on your system. If it runs less often then when it does run it has a great deal more work to do and hence will strain the IO system more.
Also, if you're anywhere close to having memory pressure, the reducing the checkpoint frequency will increase the number of pages written to disk by the lazy writer.
It may seem counter-intuitive, but I'd suggest increase the frequency if you must mess with it. Have the checkpoint running more often and doing less each time it runs.
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
November 2, 2008 at 7:45 am
You're also going to change recovery when you start the server. Doesn't sound like a big deal until people are calling you constantly about how long the server takes to restart.
Don't mess with the checkpoint. It's an advanced option and if you don't really understand it (and it doesn't seem like it from your question), you can cause more issues.
rethink your migration instead. Batch it into smaller chunks and it often goes faster.
November 2, 2008 at 3:13 pm
Thanks all, after reading through your replies, it doesn't seem worth it fiddling with the checkpoint frequency.
After more detailed analysis, this looks very much like a disk-performance (contention) issue.
As I mentioned in my OP, the WRITELOG wait type is by far the dominant wait in the system. It turns out that the same script that takes 12 hrs to complete in production takes half the time (6 hrs) in our TEST environment and 4 hrs in a developer's workstation! In all 3 cases, the dominant wait is WRITELOG, as expected since we are writing heavily in the database, but its effect is smaller in the TEST and DEV environments.
So it looks like it is an issue with contention at the SAN level: our production and test environments are on 2 different SANs from different vendors (and with a lot more activity in prod than in test). The LOG drive where the log file of the target db resides is part of an array of LUNs, so contention is bound to be a factor. We are collecting perfmon data and will be sending them to our prod-SAN vendor for analysis.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 3, 2008 at 8:12 am
I should mention that, after moving the LOG file of the target db to a LUN physically located on the TEST SAN, performance was drastically improved from 12 to 6 hrs!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 3, 2008 at 9:05 am
perf mon counters avg disk sec/read and avg disk sec/write for each individual drives should reveal such performance problems, as would examining the virtual files stats DMV (or fn_virtualfilestats, which is still available).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 3, 2008 at 9:23 am
TheSQLGuru (11/3/2008)
perf mon counters avg disk sec/read and avg disk sec/write for each individual drives should reveal such performance problems, as would examining the virtual files stats DMV (or fn_virtualfilestats, which is still available).
Yes, we have collected these perfmon counters, and from the values it appears there is indeed latency in the PROD-SAN drive.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 3, 2008 at 9:33 am
Marios Philippopoulos (11/3/2008)
Yes, we have collected these perfmon counters, and from the values it appears there is indeed latency in the PROD-SAN drive.
Surprise surprise (not). Is it sharing drives with Exchange by any chance?
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
November 3, 2008 at 9:36 am
GilaMonster (11/3/2008)
Marios Philippopoulos (11/3/2008)
Yes, we have collected these perfmon counters, and from the values it appears there is indeed latency in the PROD-SAN drive.Surprise surprise (not). Is it sharing drives with Exchange by any chance?
I will need to ask our SAN people on this one. Chances are it is not, but nothing will surprise me at this point... 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply