Guest post by Niall MacLeod, Sr. Staff Apps Engineer, The Data Propulsion Labs
T-SQL Statements are used to retrieve data from a database. Queries can be written in various ways to retrieve the same results. You tell SQL Server what data you want but not how to retrieve it. You want your queries to execute in the fastest time possible to keep customers happy. When query execution times are costly to critical applications we are likely to tune queries with the goal of increasing its efficiency to optimize the retrieval of data.
We all understand how quick access to data is critical for business success and indeed, survival.
It can be considered that query tuning is both an art and science, where only a few hard and fast rules exist. How we tune a query can vary depending on the infrastructure hardware, SQL Server implementation and schema. A query that performs well on one system may not do so on another. It can be a challenge.
More Ways to Manipulate Performance
Underpinning a SQL Server implementation is the hardware infrastructure. Understandably, tuning in this area can be overlooked as an opportunity for improving query performance as access to the hardware is typically off limits to DBAs and Developers.
With attention generally focused on improving query performance, our SQL Server Engineers at Western Digital Data Propulsion Lab speculated if there was potential for allowing people to see just how much query performance improvement could be gained by manipulating hardware only. The engineers then questioned how much more leverage could be gained by also allowing changes to the operating system and SQL Server. The caveat – no query tuning!
The SQL Server Query Challenge
That’s what we did at SQLBits in the UK. In the form of a competition, participants had a mere five minutes to improve the completion time of a standard query using a Microsoft® SQL Server 2014 Data Warehouse instance on a two socket server with SanDisk® flash storage.
The competition proved very popular with participant collaboration and experimentation that fostered valuable discussions and learning, with multiple attempts at reducing the completion time. The winner managed to reduce the default completion time from 31.58 seconds down to 5.9 seconds! That’s an incredible 80% reduction with optimized hardware – without modifying the query. This took serveral attempts. Five minutes is not sufficient to make changes and run the query. The competition became a focus on not only what settings to change, but also the order in which they were changed.
Test Your Skill at PASS Summit
We will run this competition at SQL PASS Summit (albeit on slightly newer hardware and SQL Server 2016). If you are interested, come see us in booth #222 and register to have a go. You can even enter as a team if you wish. Oh, and the prizes for the winners are awesome!
Learn how Western Digital SanDisk® and HGST® solutions can bring your database to the next level, and about our activities at PASS Summit here!
