December 4, 2012 at 9:11 pm
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....
December 6, 2012 at 6:49 am
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.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 6, 2012 at 7:50 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply