MS-SQL Performance – Things to do before you Flash

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.

Optimizing MS-SQL

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.

Twelve years ago George Crump founded Storage Switzerland with one simple goal; to educate IT professionals about all aspects of data center storage. He is the primary contributor to Storage Switzerland and is a heavily sought after public speaker. With over 25 years of experience designing storage solutions for data centers across the US, he has seen the birth of such technologies as RAID, NAS and SAN, Virtualization, Cloud and Enterprise Flash. Prior to founding Storage Switzerland he was CTO at one of the nation's largest storage integrators where he was in charge of technology testing, integration and product selection.

Tagged with: , , ,
Posted in Blog

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 22,219 other followers

Blog Stats
%d bloggers like this: