November 18, 2014 at 12:47 pm
On our TempDB volume Windows is showing the drive as 80% fragmented, so my Sys Admin asked the Storage Admin to check the Netapp.
The Storage Admin has reported the Netapp shows fine in terms of fragmentation (or whatever term NetApp admins use).
The Sys Admin is planning on running a defrag on the drive, but since he normally doesn't on any of our servers :ermm: he wants me to give him performance benchmarks from SQL before and after.
Since it's the tempdb drive and only has the tempdb on it, I've been trying to think of a good reliable way to do this. Create a script that creates some temp tables with lots of temp data, and perform a bunch of operations on them?
Thanks
November 18, 2014 at 2:14 pm
1) Your metric of 80% fragmented isn't really useful. You can have 5 20GB file chunks for tempdb with 4 of them not being adjacent to each other and you will show 80% frag in windows. But that would not at all affect performance of that 100GB tempdb file. However, if your SQL Server is defaulted with respect to tempdb (which most of my clients have) then you could have SUBSTANTIAL IO performance issues from 80% fragementation.
2) You cannot prove fragmentation is a performance issue unless you run the test as is and then AFTER you defrag.
3) I would be VERY wary of doing a defrag with SQL Server running. Disk Keeper does certify that their stuff will work live (and I have clients that have done it), but it honestly doesn't give me a warm and fuzzy. 🙂
4) A key point that almost everyone misses is that on most SANS your disks are NEVER running in SEQUENTIAL IO mode because they get carved to hell and back by the admins. Also more than a few SQL admins fall in love with file groups and wind up with FAR too many on too few disks, which CREATES performance issues.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply