Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Very long avg queue length for SQL Server Expand / Collapse
Posted Tuesday, December 4, 2012 9:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 30, 2015 1:12 AM
Points: 7, Visits: 210
I am looking into a very unwell data warehouse running on sql server 2005. For the size of the DW (~700Gb) they have provided a tiny amount of Ram (3Gb) and 4CPUs.

It is running on a VMWare ECX3.5, 32 bit SQL Server 2005. Database and Indexes are on shared SAN linked through to rack via a 4Gb HBA.

Some of the tables have 100m+ rows, with the largest being 2.5bn rows.

The ETL was originally struggling to finish by start of business hours, but now times have blown out by some time.

Apart from upgrading hardware and optimising poorly written queries, does anyone have any opinions on how to eliminate possible causes such as poorly performing SAN and HBA throughput?

Any ideas on which Perfmon counters can best be used to support apparent lack of hardware/resources?

Many thanks if you can provide any pointers....

Post #1392775
Posted Thursday, December 6, 2012 6:49 AM



Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 5,674, Visits: 8,201
This type of performance tuning and analytics is what I (and a few others here on the forums) do for a living. There are a WIDE range of potential issues that can be causing problems - not just the hardware. But clearly that hardware is SIGNIFICANTLY underpowered for the size of database being processed. Sounds like you may even be doing the ETL from the same server.

A few of the important things to look at are perf mon avg disk sec/read and avg disk sec/write as well as CPU utilization. From within SQL Server you need to do a file IO stall and wait stats analysis. Queries should be tuned, index fragmentation evaluated, statistics freshness checked (including on the source system), the ETL methodology itself reviewed/refactored, etc, etc, etc, etc.

I HIGHLY recommend getting a professional on board to help you with this project. That will be a win-win. Client gets a MUCH better set of work in a shorter period of time and you get some mentoring on how to tune/monitor/maintain a data warehouse.


Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1393488
Posted Thursday, December 6, 2012 7:50 AM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
Start with chapter 1.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1393530
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse