Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a daily ETL process in SSIS that builds my warehouse so we can provide day-over-day reports.

I have two servers - one for SSIS and the other for the SQL Server Database. The SSIS server (SSIS-Server01) is an 8CPU, 32GB RAM box. The SQL Server database (DB-Server) is another8CPU, 32GB RAM box. Both are VMWare virtual machines.

In its oversimplified form, the SSIS reads 17 Million rows (about 9GB) from a single table on the DB-Server, unpivots them to 408M rows, does a few lookups and a ton of calculations, and then aggregates it back to about 8M rows that are written to a brand new table on the same DB-Server every time (this table will then be moved into a partition to provide day-over-day reports).

I have a loop that processes 18 months worth of data at a time - a grand total of 10 years of data. I chose 18 months based on my observation of RAM Usage on SSIS-Server - at 18 months it consumes 27GB of RAM. Any higher than that, and SSIS starts buffering to disk and the performance nosedives.

I am using Microsoft's Balanced Data Distributor to send data down 8 parallel paths to maximize resource usage. I do a union before starting work on my aggregations.

Here is the task manager graph from the SSIS server

alt text

Here is another graph showing the 8 individual CPUs

alt text

As you can see from these images, the memory usage slowly increases to about 27G as more and more rows are read and processed. However the CPU usage is constant around 40%.

The second graph shows that we are only using 4 (sometimes 5) CPUs out of 8.

I am trying to make the process run faster (it is only using 40% of the available CPU).

How do I go about making this process run more efficiently (least time, most resources)?

share|improve this question
4  
Task Manager is not really good way to see resource use for SQL Server. You would get better idea by using Perfmon counters and SQL DMVs. –  Shawn Melton Nov 21 '11 at 14:26
3  
You are assuming that the process is CPU bound, which from your own graphs shows that it is not. My guess is that the perceived performance problem is I/O related, not CPU. What type of drive configuration are you using? –  datagod Nov 21 '11 at 15:18
2  
Person counters won't tell much from inside the vm, SQL counters will tell you SQL thinks is a bottleneck, which isn't a bad thing to look at because it will tell you what the app is waiting on. –  Jim B Nov 22 '11 at 6:39
    
additionally system performance counters inside a vm are always misleading, whereas application based counters are always accurate. –  Jim B Nov 22 '11 at 19:35
add comment

3 Answers 3

You aren't bottlenecked by CPU usage.

The majority of the time, SQL Server will be IO bound since physical disk access is many magnitudes slower than CPU or memory.

An analogy to what you are asking is:

I have this huge fire hose connected to my kitchen faucet. How come I can't get full pressure from the fire hose like they get from a hydrant?

The system is only as fast as the slowest part, unfortunately. And the process you describe sounds IO intensive.

share|improve this answer
    
I did create a Clustered Index on the source table on the only column in the WHERE clause wouldn't affect it too badly. What can I do to verify that IO is the culprit? –  Raj More Nov 21 '11 at 16:10
1  
@RajMore - indexes aside, your disks won't be as fast as your CPU no matter what. The only way you get CPU bound is by running a lot of transformations on data that's already been loaded to memory. –  JNK Nov 21 '11 at 16:11
    
Yep. The only way you can use more of your processor in this context is likely to use faster disks or use SSDs. SSDs would yield vastly faster I/O, but memory and CPU are still way faster. –  Ben Brocka Nov 21 '11 at 16:18
2  
@JimB - Because in SS you are almost never CPU bound. 99% of the time CPU pressure is lower than both memory and disk IO. –  JNK Nov 21 '11 at 20:05
1  
@JimB - VM or no, it's extremely unlikely that SS is bottlenecked by the CPU under almost any circumstances. –  JNK Nov 21 '11 at 21:20
show 3 more comments

+1 for @JNKs explanation.

Simplest way to verify your bottlenecks would be to reset wait stats before the process starts:

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

Immediately when the process is completed, capture wait stats. If you want to get a more fine grained picture of where the bottlenecks are occurring, capture the wait stats to a table on a schedule as the process runs.

sp_whoisactive might be a better tool for the job if you want to capture snapshots.

share|improve this answer
    
Jonathan Kehayias has a good script for capturing stats with the DMVs in a Red Gate book he just wrote: red-gate.com/our-company/about/book-store/accidental-dba It is free for download. –  Shawn Melton Nov 21 '11 at 17:14
add comment

There isn't enough data here to determine which piece of the system to augment. Generally speaking the limitations are usually first disk then network, then ram, then CPU. More importantly you are looking at statistics from INSIDE the VM- these numbers are worse than worthless, as it only shows you how much of the resources the OS thinks it has are used. You could very well be CPU bound but until you look at the hypervisor numbers you really have no idea about what's actually being used. You should also ensure that the 2 servers are on the same node if there is network traffic involved as it will avoid any physical nic contention. As an example since your graph shows a steady memory utilization increase is the memory on the vm is that much ram reserved? It could be that the memory allocation rate is sucking up CPU time.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.