June 10, 2008 at 12:46 am
Hi,
I am a developer that had to take over a database on a site.
The database is extremely critical to the company as it is used to calculate the employees work time.
We have to interface our data with the sites data with the ERP system, wages system and accounting system.
The company has expanded and the data load has increased 20X what the system was originally programmed for. Three of the tables that are used the most now have between 400 000 and 1.7 mil records.
We have a user client program that runs on about 30 client machines and users view and work with the three main tables constantly.
The system has become seriously unstable and I have been given the task of solving this major issue.
So firstly I tried UPDATE STATISTICS on the tables that were the slowest and there was a reasonable improvement in speed. Then I got a deadlock message on one of the PC's so I put a Trace on the database and found several system deadlock points which I have tried to sort out by using Updates WITH (ROWLOCK) and SELECTS with (readpast) or (NOLOCK) and this has helped the deadlock situation a bit but seems to have slowed the system down a great deal.
I have tried DBCC CHECKDB and I get no errors. I have tried creating indexes on the three big tables (using the fields that are sorted on the screens) and I have tried to rebuild these indexes.
Then on Thurday I started checking the server hardware and found that the data file was fragmented. The drive that contains the data was 78% fragmented and the data file itself was also fragmented.
So I was told that a defragment would take too long and the server is too old so I had to move the SQL database to a new machine.
I'm now on the second new machine with no speed improvement (it seems to be going backwards)
Original Machine:
Intel Xeon 2.8Ghz
70GB Hard Drive (Partitioned 20,50)
2GB RAM
Microsoft Windows 2003
Microsoft SQL 2000 and Service pack 3a
Second Machine (desktop):
Intel Dual Core 1.8Ghz
80GB Hard Drive (Partitioned 20, 60)
2GB RAM
Microsoft Windows 2003
Microsoft SQL 2000 and Service Pack 4
Third Machine (server):
HP SERVER TC2120
2X Intel Xenon Processors
2GB RAM
350GB IDE HDD
Windows 2003 Server
SQL 2000 and Service Pack 3a
SQL 2005 Components Only
Please help as I don't know what else to try.
Regards
June 10, 2008 at 8:55 am
Most likely you have a mixture of badly written queries and inadequate indexes. There's no quick fix for this. If it's as critical as you say, you may be best off hiring a competent consultant with expertise in performance tuning for a short time.
What I suggest to start is the following:
Run profiler for an hour or so during a period of high load. Capture the RPC completed and the SQLBatchCompleted events. Make sure that you have the TextData, duration, cpu and reads at a minimum.
Take the profiler results and find the top 5 worst performing queries in terms of duration, of CPU, of reads. There's a good chance that there's an overlap here.
Take those queries (I suggest no more than 10) and do what you can to optimise them. That may involve changing the query, it may involve changing or adding indexes.
Repeat until system performance is adequate. It probably will take no more than 3 itterations to get acceptable performance. Most often it's a small number of queries causing all the problems.
Adding things like nolock, readpast, etc are just temporarily fixing the symptoms of the problem and throwing hardware at a performance problem should always be the last thing that you do, not the first. That said, your server looks a little light on memory. Suggest an upgrade to 4GB memory, possibly even 8GB.
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply