June 25, 2009 at 4:38 pm
So we have a problem:
Our SQL Servers run upwards of 90% CPU usage for this nasty ETL process we run. It takes over two days to run.
A couple of thoughts:
1. Optimize the ETL and sprocs that run.. but let us assume it is as optimized as possible.
2. Locate the bottleneck after doing this - it appears to be the CPU (disk I/O is massive SAN and shows no issues)
3. Get a faster server - did that
4. Get more servers.... "daisy chain together"
This takes us to a couple of things:
A. We are running a handful of SQL virtual machines and each of these shares a single box.
They ONLY spike out every few weeks when this process runs, otherwise they are wasting resources.
B. They have these as virtual machines on VM ware, but currently they are stationary on the assigned boxes.
So I want to look at options for:
1. Adding to a VM Ware cluster so
When the SQL VMs CPUs spike, VMWare can off load them each to their own machine on the VM cluster
2. Maybe bypass that and look at setting up an SQL cluster.... benefits?
Thoughts, ideas?
June 26, 2009 at 1:55 am
Maxer (6/25/2009)
2. Maybe bypass that and look at setting up an SQL cluster.... benefits?
SQL clustering is purely high availability. There's no load balancing or scale-out capabilities within it.
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
June 26, 2009 at 5:37 am
So basically the only real option is to setup additional SQL Servers and program to spread the work out among them?
June 26, 2009 at 5:45 am
I'd start by trying to optimise the ETL process. It's possible that there's no more that can be done, but it's unlikely.
If that gives no joy, then spreading the work out among a couple servers is a good option, but not a simple one.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply