SQL: really really defrag partition, defrag or leave it alone?
Hey guys,
So the past few days our SQL server works up until I leave at 7pm and then every morning I come in and our in-house ERP crashes at some point during the night. The server is whoofully under equipped with only 8 GB's of RAM, but has two Xeon's with 24 cores total; I know it need more memory like 32 GB at-least! - looking for a quick fix and the swap file and deleting the 13 GB's of Windows Update file in the 'softwaredistribution' folder solved that (just turned off windows updates, make it a .old and restart Windows updates it automatically made a new folder and deleted the .old folder and whalla, bloody windows downloaded them all!!!)
Today I made a change and re-sized a whoofully too large data partition and made 40 GB Swap partition for only swap since it was on C: (in the picture below) and had less that 2% free space; the server was performing like dog sh*t! - so now that I got the swap file moved, and then rebooted the server is performing way way better since the C: drive was very defragged so that wasn't helping.
Now the one thing I have a question about, can I defrag the
partition (in the picture) since its @ 97% but this is were the SQL server is installed with it's databases. I've seen some database be at 99% fragmentation on a SQL server but I'm pondering if that's normal or if it should be defragged? - ....before I presume figured I see if any of you database engineer have some light to shed on the matter.
So the past few days our SQL server works up until I leave at 7pm and then every morning I come in and our in-house ERP crashes at some point during the night. The server is whoofully under equipped with only 8 GB's of RAM, but has two Xeon's with 24 cores total; I know it need more memory like 32 GB at-least! - looking for a quick fix and the swap file and deleting the 13 GB's of Windows Update file in the 'softwaredistribution' folder solved that (just turned off windows updates, make it a .old and restart Windows updates it automatically made a new folder and deleted the .old folder and whalla, bloody windows downloaded them all!!!)
Today I made a change and re-sized a whoofully too large data partition and made 40 GB Swap partition for only swap since it was on C: (in the picture below) and had less that 2% free space; the server was performing like dog sh*t! - so now that I got the swap file moved, and then rebooted the server is performing way way better since the C: drive was very defragged so that wasn't helping.
Now the one thing I have a question about, can I defrag the
partition (in the picture) since its @ 97% but this is were the SQL server is installed with it's databases. I've seen some database be at 99% fragmentation on a SQL server but I'm pondering if that's normal or if it should be defragged? - ....before I presume figured I see if any of you database engineer have some light to shed on the matter.Comments
-
N2IT
Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
I'm more of a SQL analyst than a DBA, but I would shutdown the services to that drive and back those database files up before I did any physical defrag to the drive.
It might help, from my experience a lot of our issues were under performing hard ware and fragging at the database level. With indexes and such leaving gaps. -
Matt2
Member Posts: 97 ■■□□□□□□□□
Have you analyzed key performance counters to check for bottlenecks? Ram, CPU, Disk.... -
NotHackingYou
Member Posts: 1,460 ■■■■■■■■□□
Don't defrag the partition. SQL has its own fragmentation management system via indexes. Check the fragmentation level on your indexes and rebuild/reorg as needed.When you go the extra mile, there's no traffic.
-
Deathmage
Banned Posts: 2,496
CarlSaiyed wrote: »Don't defrag the partition. SQL has its own fragmentation management system via indexes. Check the fragmentation level on your indexes and rebuild/reorg as needed.
I thought I remembered reading this when I attempted the MCSA track two years ago (wasn't ready for it at the time) just wanted to make sure! - thanks!!!!!!
I did however defrag the normal windows OS partition that didn't have SQL on it and the server is blazing fast now...