May 2, 2011 at 2:08 am
Within MS SQL 2008 we have two seperate mirrored databases each with a number of tables (say DB1 & DB2). The databases are queried by our Web Server to access data for our end clients, with approx half million daily hits.
Every day we update the databases using the BCP utility to insert 2 million+ rows. During the update process on DB1 the web Server switches to query data from DB2. Once DB1 has been updated the Web Server switches back to getting data from DB1, then DB2 is updated using BCP utility. Quite straight forward you’d think.
Our problem is during this transfer the whole of SQL Server database gets jammed/slows down to the extent we receive website timeouts during the transfer. We have ample CPU / Ram capacity.
I have searched extensively but have not been able to find a solution. I am not a database administrator but work as an application developer using SQL server 2008 databases. I would really appreciate if anyone could guide me in the right direction.
Thanks
May 2, 2011 at 2:57 am
Are you using the SQL Server mirroring feature or are the 2 DB independent and manually synchronized?
Are the 2 dbs on different disks/LUN (using a SAN or direct attach)?
You could use the Activity monitor in the Management Studio 2008 to see if there are no lock and the kind of wait type you get during the BCP in.
May 2, 2011 at 5:39 am
Oliiii (5/2/2011)
Are you using the SQL Server mirroring feature or are the 2 DB independent and manually synchronized?Are the 2 dbs on different disks/LUN (using a SAN or direct attach)?
You could use the Activity monitor in the Management Studio 2008 to see if there are no lock and the kind of wait type you get during the BCP in.
The 2 DB are on the same machine and data is inserted using bcp.
The databases are on the same disk.
I will try the Activity Monitor and see if I could understand anything from that.
Thanks a lot for your quick reply.
May 2, 2011 at 5:58 am
If both DBs are on the same disk you might get timeout simply because the disk can barely keep up with the BCP in and has not much time left to handle the other DB request before the timeout.
You should at least split the data and the log to different disks and monitor the disk load (perfmon -> logical disk -> Avg sec/transfer), if your data disk can't keep up then put each DB on it's own disk.
May 2, 2011 at 6:13 am
Oliiii (5/2/2011)
If both DBs are on the same disk you might get timeout simply because the disk can barely keep up with the BCP in and has not much time left to handle the other DB request before the timeout.You should at least split the data and the log to different disks and monitor the disk load (perfmon -> logical disk -> Avg sec/transfer), if your data disk can't keep up then put each DB on it's own disk.
Thanks once again.
How to find out whether the disk is the actual issue. Is there a log within Windows or SQL Server 2008 which I could use to find that was the main cause? Sorry for stupid questions.
May 2, 2011 at 6:40 am
It's unfortunately not that easy 🙂
From the sql server's point of view there are no real problem, it's just slower than usual but given enough time it'll give you the result, so there is nothing to log or report.
To find out who's the culprit you'll have to gather some baseline performance during normal operation and compare it with the performance you get during the BCP in.
Monitor the CPU and the Disks (monitoring memory is a bit harder, so we can skip it now and do it later if the other 2 are fine).
If your avg CPU is above 80% then you have a bottleneck there, if the avg response time of the disk (avg sec/transfer) is above 20ms (that depend on your baseline, disks subsystem can have very different performances) then you have a bottleneck with the disk.
You can also look at the Activity monitor and look at the Session ID of a query being run from your web server and check what is the wait Type (PAGEIOLATCH means it's waiting for a disk, CXPACKETS means it's waiting for the CPU).
This is just a quick way to get an idea of what's going on, getting an accurate answer requires quite some knowledge of perfmon, dmvs and the way sql server works.
I would still shoot for the disk bottleneck though, BCP are heavy disk user (since it's very streamlined it's usually the slowest link the bottleneck). You are using 2 different DBs so you shouldn't have any locks blocking each other and unless you are doing some weird stuff you are probably not maxing out your CPUs.
May 2, 2011 at 7:07 am
Thanks once again for your valuable answers.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply