September 15, 2008 at 6:33 am
Significant improvement of performance during defragmentation required
We have a big database running on a
Dell PowerEdge 2850 Server, 4GB ram, 2xXeon dual CPU 2,8GHz (logically 8 processors)
Windows 20003 SP2 Standard
The database is
SQL Server 2000 (8.00.2039) Standard Edition
configured to use 4 of the available 8 processors
3x136 GB Raid1 mirrored disk systems (1st system, 2nd transaction log, 3rd DB files)
Transaction log size 6 GB
DB on two partitions 52 GB and 24 GB on the same disk system.
The most lines in a table is 115564340.
Our problem is the defragmentation of indices, which lasts for 6-17 (or even more) hours and the database queries may timeout during the defragmentation. Timeout is interpreted as unavailability and we have very strict availability requirements. There are frequent transactions so defragmentation must be performed biweekly.
Intuitively, as a consequence of the varying defragmentation time, I think either SQLServer or OS consumes some resource, which is not fully released perhaps due to some transaction being incomplete?
As a result defragmentation requires so much resources that the queries time out. For instance memory, disk fragmentation.
Do you have a suggestion what to do in order to solve the timeouts, response times and availability:
- upgrade OS
- upgrade SQLSrerver
- add memory
- upgrade server to what type, CPU GHz (HP/Dell)
- upgrade # of processors
Thank you for your help!
Marten
September 15, 2008 at 8:23 am
Index rebuilds take time, they take resources and can lock things a bit.
In SQL 2005, there are more online operations (and 2008), so you can consider that. The other thing is not to blanketly do all indexes. Run dbcc showcontig and rebuild only those ones that need rebuilding.
There are no incomplete transactions. That's the point of SQL Server. While resources could be used, I doubt it.
September 15, 2008 at 8:52 am
You could also look at moving the clustered indexes onto columns that don't fragment as fast. It needs a lot of thought and analysis, but may help.
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
September 15, 2008 at 3:40 pm
Yes, I understand index defragmentation and/or rebuild take time and resources and make things happen slow motion. Through experience, how much effect on performance does these steps have:
- Windows 2003 Server Standard -> Windows 2003 Server Advanced
- SQLServer 2000 Standard -> SQLServer 2000 Enterprise
- Increasing memory 4GB -> 8GB
- Increasing server by more processors/more computing power (newer model / new brand)
Current application version does not support SQL 2005 or 2008.
"The other thing is not to blankedly do all indexes. Run dbcc showcontig and rebuild only those ones that need rebuilding." -> good idea, requires defragmentation script to be rewritten.
I understand the rollback and complete transactions of SQL as a basis. The client may crash before transaction complete/recorded. Sill the variation in time of the defragmentation job, 6 - 17 hours, make me think some resources are not properly released due to some malfunction. A simple work-around might be rebooting DB server before defragmentation
September 16, 2008 at 2:11 am
It might be worth running perfmon to see what your I/O speeds are.
My previous experience with index defrags were that they just ran nicely in the background without interfering with other queries... the only downside was that it created HUGE transaction logs.
On a completely different system, I was seeing problems similar to yours, where the defrag was causing a large amount of blocking (which it isn't supposed to do), eventually leading to timeouts in the client. Perfmon showed particularly slow I/O on the database disks (>800ms per write). A redesign of the array on the SAN reduced the I/O times, and the issues with defrag just went away.
September 16, 2008 at 2:31 am
The last 2 may help. Depends where the slow down is coming from.
Check your IO performance when the index defrags are running. I'd suggest the following counters:
Physical disk:avg sec/read
Physical disk:avg sec/write
Physical disk:% idle time
Processor:%processor time
SQL Server buffer manager: lazywrites/sec
SQL Server buffer manager: checkpoint pages/sec
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
September 17, 2008 at 3:14 am
The first step should be to follow up on the suggestions given for improving the speed of your index rebuild, and avoiding the need to do rebuilds. The biggest improvement is likely to come from a script that only rebuilds the indexes that need to be done.
If these suggestions do not work you can improve the hardware and upgrade to enterprise editions, but these will just be sticking plasters over your main problem:
You are outgrowing a SQL Server 2000 32-bit environment.
You need to plan a move to a 64-bit environment running SQL Server 2008 (or 64-bit Oracle or DB2 if you have got upset with SQL). A 64-bit environment will allow you to throw much more hardware at the problem than is possible with 32-bit, and SQL 2008 will give you better underlying technology than SQL 2000.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 17, 2008 at 8:43 pm
When "rebuilding" and index, the index is NOT available for use. That is also true when using DBCC DBReIndex. They are, however, available if you use DBCC IndexDefrag.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply