Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 62 of 212

51 C H A P T E R 4 | Improved database engine leading to the hotspot issue described earlier in this chapter. Enabling this trace flag in earlier versions of SQL Server is a minor tradeoff in performance. For example, if you were using multiple data files in user databases, this trace flag affects them as well as TempDB's data files. Depending on your scenario, that could be problematic—an example would be if you had a file group that you did not want to grow as a single unit. Starting with SQL Server 2016, the behavior to grow all data files at the same rate is built into TempDB by default, which means you no longer need this trace flag. Trace flag 1118 Administrators use trace flag 1118 to change page allocation from a GAM page. When you enable TF 1118, SQL Server allocates eight pages, or one extent, at a time to create a dedicated (or uniform) extent, in contrast to the default behavior to allocate a single page from a mixed extent. Unlike with TF 1117, there was no potential downside to enabling TF 1118—it is generally recommended for all SQL Server implementations in earlier releases. Starting with SQL Server 2016, all allocations of TempDB pages use uniform extent allocation, thus eliminating the need to use TF 1118. Query Store One of the most common scenarios you likely encounter is a user reporting that a query is suddenly running more slowly than in the past or that a long-running job that once took 3 hours is now taking 10. These performance degradations could be the result of changes in data causing out-of-date statistics or changes in execution parameters or be caused simply by reaching a tipping point in hardware capabilities. In previous versions of SQL Server, troubleshooting these issues requires you to gather data from the plan cache and parse it by using XML Query (xQuery), which can take considerable effort. Even then, you might not have all the information you need, unless you are actively running traces to baseline the user's environment. The new Query Store feature in SQL Server 2016 simplifies identification of performance outliers, manages execution plan regression, and allows for easier upgrades between versions of SQL Server. It has two main goals—to simplify identification of performance issues and to simplify performance troubleshooting for queries caused by changes in execution plans. The query store also acts as a flight data recorder for the database, capturing query run-time statistics and providing a dashboard to sort queries by resource consumption. This vast collection of data serves not only as a resource for the automated functions of the query store, but also as a troubleshooting resource for the DBA. This feature is one of the biggest enhancements to the SQL Server database engine since the introduction of dynamic management views (DMVs) into the database engine in SQL Server 2005. The query store gives unprecedented insight into the operations of a database. Whether you want to find the workloads in an instance, perform an in-depth analysis across executions of the same code, or fix a pesky parameter-sniffing problem, the query store offers a vast metastore of data, allowing you to quickly find performance issues. Enabling Query Store Query Store manages its metadata in the local database, but it is disabled by default. To enable it in SQL Server Management Studio (SSMS), open Object Explorer, connect to the database engine, navigate to the database for which you want to enable Query Store, right-click the database, select Properties, and then click Query Store in the Database Properties dialog box. You can change the Operation Mode (Requested) value from Off to Read Only or Read Write. By selecting Read Write, as shown in Figure 4-3, you enable Query Store to record the run-time information necessary to make better decisions about queries.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016