Large Writes to Centre of mdf File | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Large Writes to Centre of mdf File

Hi All I’m new to this (forum and SQL Server DBA) so please be gentle. I have a new OLTP system up and running which is no where near maximum capacity yet. The basic problem is at, what seem to be, random intervals SQL Server attempts to write large amounts of 1MB files to the centre of my single mdf file (according to perfmon). When this happens the entire system grinds to a halt with time out errors. The number of transactions per second is tiny, the disk queue is maxed out and the CPU usage is very low. The details of my configuration are not ideal (from reading various postings to this forum) so please don’t shout at me. I have 5 36GB disks in a RAID5 configuration, dual Xeon processors and 4GB of RAM running SQL Server 2000 Standard on Windows Server 2003 Standard. The "single large disk" has everything on it. The o/s, exe, mdf, ldf and backups. Yes I can hear you all shouting at me now. The mdf file is 6GB now and basically the system only has 3 users: 2 services that do more writing than reading and one user interface that does more reading than writing. Other than lookup tables and a few tables that hardly ever change, there are three main tables in the database. Two contain information about all the transactions that are happening and one (which contains a TEXT field) that contains the main transaction data. What I would like to know, is my hardware configuration significantly bad enough to be causing the problem I have? Or is there something else that I’ve missed? The database is set to grow automatically by 10%. If I manually run an optimization job (set up using the default settings in a new Maintenance Job) the writes to the mdf file stop and my system recovers. Iain
Welcome to the forum.
Do you have any other scheduled jobs when thsi large amounts of 1Mb files are written to the file.
Check event viewer, SQL Server error log for more information on any errors during this event.
I think there could be a fragmentation problem that is contributing this issue, so try to defrag the indexes and also physical files on server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
As far as I know nothing else is scheduled. I thought it might have been a full backup or transaction log backup getting in the way. Unfortunately I’ve now seen the problem happen when neither of these are happening. Is there a way of defragging all the indexes in one go or do I have to do dbcc indexdefrag on all the indexes individually? Any defrag would have to be done while the system is online as it is in use 24-7. Iain
IF possible try to schedule the DBCC DBREINDEX on individual table/index to see where and when this issue occurs. I believe this could be some sort of mismatch in the RAID configuration or disk sub system contributing the SQL services to halt. Refer thishttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx technet article for Defragmentation best practices. Refer to the books online for complete information on DBCC INDEXDEFRAG. DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Turns out that the problem was due to the auto increase of the .mdf file. Still not sure why SQL Server could not cope with it. Any way I now have a 60GB .mdf file and a new disk configuration with: 1 disk for O/S and SQL Server exe; 1 mirrored pair for the database file; 1 mirrored pair for the transaction log; 1 disk for backups. Everything is working fine now. Iain P.S Excellent web site. I wished I’d known about it earlier.
Turns out that the problem was due to the auto increase of the .mdf file. Still not sure why SQL Server could not cope with it.
May check any scheduled jobs during that time or any batch inserts to the database, this is the reason the database size will be increased and transaction log also sized to max. Ensure to maintin frequent tlog backups and do not run any open ended transactions, try to submit inserts in small chunks that can take care of transaction log size too. Glad to know it is working now with a workaround at your end, do follow the articles and references to keep up the performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>