Check SQL Server Query Store performance impact before using
Many IT teams hesitate to use SQL Server Query Store due to performance concerns. Consultant Andy Warren offers tips on how to test and get started with Query Store.
Although there was significant fanfare surrounding SQL Server Query Store's initial release in SQL Server 2016, many companies have been hesitant about adopting the performance monitoring and troubleshooting tool, according to consultant Andy Warren. Ironically, that's largely because of its own potential impact on the performance of SQL Server systems.
When Query Store is activated, it captures runtime statistics and other information about all submitted queries and query execution plans on a per-database basis. This allows users to turn the feature on for all of the databases in a database server or only specific ones, based on workload priorities or a desire to minimize the overall Query Store performance impact in their SQL Server environments.
In a recent webinar, Warren described Query Store as a set of tables that contain detailed information about the queries run against a database. Once it's turned on, the tool runs 24/7 and saves the information in a way that's easy for users to access and understand, said Warren, president of consultancy Fourdeuce Inc. in Orlando, Fla.
Query Store can be used to detect regressions -- cases in which query performance slows down. It then allows users to choose their own query plans instead of relying on SQL Server's Query Optimizer to do so. For example, they can force a previous plan saved in Query Store to execute in place of the current one to reverse a regression. The tool can also help research database usage patterns, though Warren said users need to have the SQL Server Profiler or Extended Events monitoring software installed in order to do that.
As helpful as Query Store sounds, Warren said that it's underutilized. Many SQL Server database administrators (DBAs) already use third-party products that perform most of Query Store's tasks in a more user-friendly manner, he said in the webinar, which was posted on the MSSQLTips website and sponsored by IT management tools vendor Quest Software Inc.
Query Store performance impact a user concern
Warren noted that potential users are also intimidated by the perceived performance impact of Query Store. Because the tool runs all the time if it's on, it can take system resources away from other programs. According to Warren, DBAs are likely to assume that running Query Store will increase processing overhead in their database servers -- and often with good reason.
For those DBAs who are curious about using Query Store but want to assess its performance impact first, Warren recommended experimenting with the tool during off-hours. "This is something you turn on at six o'clock on Thursday night," he said. "Turn it on after the peak time of day."
Running Query Store does take up resources when it gathers information on queries and builds out its data store inside a SQL Server database, and Warren said the tool may well use more CPU cycles than originally anticipated. That's perfectly normal, despite the possible performance hit it can cause, he assured webinar listeners.
Warren cautioned, though, that DBAs typically will be guessing upfront at how much disk space is needed for the data collected by Query Store, which has a default capacity of 100 MB. The worst-case scenario, he said, is that the tool silently switches to read-only mode after reaching its storage limit; if so, it stops collecting data, which affects the accuracy of query performance analysis. Because of this, Warren recommended checking the disk space once a week for the first month.
How to turn on Query Store in SQL Server
Turn it on, let it run for a little while, turn it off. That's a great 'get comfortable' exercise for learning about Query Store.
Andy WarrenPresident, Fourdeuce Inc.
To enable Query Store to run in a database, right-click on that database in SQL Server Management Studio's Object Explorer UI, then click on Properties and open the Query Store page from the Database Properties dialog window. You then have the option to change the Operation Mode setting from Off to either Read Only or Read Write.
Warren recommended choosing the latter setting, as the Read Only one should only be used if you've run out of room for new data or decide that you don't want to collect more. You can also change the intervals at which Query Store collects statistics and its allotted storage capacity in Object Explorer. Alternatively, all of these steps can be done via Transact-SQL scripts using the ALTER DATABASE statement.
Either way, Query Store is easy to activate and deactivate, Warren said. "Turn it on, let it run for a little while, turn it off," he advised. "That's a great 'get comfortable' exercise for learning about Query Store."
Warren acknowledged that not every organization will have a use for the tool, partly because of the Query Store performance impact that users may see in their database servers. Nonetheless, he said it's still a technology that's worth knowing about for DBAs, especially because Query Store comes as a standard feature with SQL Server 2016 and beyond.
