Prior to implementing a flash solution, it is important to make sure the environment is ready for it so maximum value can be extracted from the flash investment. Watch this trailer from our Webinar “Which Flash is Best for MS-SQL? Server-side, hybrid or all-flash?” to learn the steps you should take to make sure your MS-SQL environment needs flash and what you should do to optimize that environment before you make the flash move.
As we discuss in the video the first step is to make sure you actually have a storage performance problem. The simplest way to confirm this is to look at CPU utilization. If CPU utilization is low, say less than 30% but MS-SQL is exhibiting signs of performance problems then you probably have a storage I/O issue that flash can solve. There are other issues like network latency as well, for a deep dive on confirming a storage I/O problem see our “Visualizing SSD Readiness” white paper
Is Windows the issue?
Once you’ve confirmed that you do have a storage performance problem the next step is to make sure the operating system is not causing storage performance problems. Since MS-SQL runs on Windows, make sure the volume that the instance resides on is defragmented. Although often a forgotten concern fragmentation still happens, no matter if the MS-SQL server is virtualized or resides on an array. As we discuss in the video below tools exist today to make sure that data is always written in a contiguous pattern to storage. This ensures that no IOPS are wasted reassembling fragmented data.
Once the potential operating system issues have been resolved, it is worth spending some time to make sure that the database design itself is optimized. For example storage and SQL administrators should work together to make sure the queries being run against the database are still valid. Many times orphaned queries can be quickly identified and eliminated. Second, indexes should be looked at and it should be confirmed that the right columns are indexed. Finally, DBAs may want to look at the database code itself to see if it can be optimized. See this article for a deep dive on optimizing MS-SQL design for Flash.
The Harsh Reality
The reality is that the above steps require time and effort and in some cases a skill set that the organization simply does not have. As we discuss in our webinar the value of all-flash and hybrid flash arrays is that they can overcome the residual impact of non-optimal designs. It then becomes a business decision on the part of IT as to whether or not they address the design issues prior to implementing flash, or if they simply move on with their flash strategy.