The VLF thing has less to do with disk fragmentation and more to do with the way SQL Server manages virtual log files. Simple tuning techniques like tweaking the read/write bias on the RAID controller’s cache, right-sizing the NTFS allocation units, and working with different stripe sizes can usually yield double the storage performance without spending a dime. Keep these original CrystalDiskMark screenshots in a shared folder for the group to access, and then challenge everyone involved to do better. Often I find that the OS’s drives perform even better because we just haven’t configured and tuned our storage. Compare the numbers for where you’re storing the valuable, high-performance data to where you’re storing the OS, and you might be surprised. So what’s a good or bad number? If your server boots from a mirrored pair of local drives, and stores its SQL Server data somewhere else (like on a larger array or on a SAN), then test the local mirrored pair too. The text version of the results has more details: You can get IOPs, latency, and throughput numbers from CrystalDiskMark too by clicking File, Save Text, then go into a text editor and open the results. You can see why people are tempted to store databases on there, but that’s a discussion for another day. Notice how the ephemeral SSD is 10x-30x faster on reads, and 4x-18x faster on writes? Not to mention that the ephemeral drive is completely free with your VM. Note how you can type in the bottom box of CrystalDiskMark’s results – see how I typed Amazon General Purpose SSD? That’s great for making notes that will be visible in the screen shots to help you determine which test results came from which machine.Īnd here is a set from an ephemeral SSD locally attached to that same EC2 VM: Here’s a sample set of results from a 335GB general purpose SSD volume in Amazon EBS: Usually I find that during the first round of storage tests, it’s not performing well period – and it doesn’t make sense to bring SQL Server into the game just yet. We’re not looking to get an exact representation of SQL Server’s IO patterns here – we’re just trying to get a fast, one-button-click-easy measurement of how storage performs. SQL Server stores stuff on disk in 8K pages, and zooming out a little, groups of 8 8KB pages (64K extents). The more astute readers (and by that I mean you, you good-looking charmer) will notice that 4K operations don’t really measure SQL Server’s IO.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |