Netherlands: Software

Introductie van Micorosoft SQL Server 2016

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

Contents of this Issue

Navigation

Page 68 of 212

57 C H A P T E R 4 | Improved database engine enough to manage execution plans and their related metadata. When this space fills up, the query store reverts to a read-only mode and no longer provides up-to-date execution statistics. The size of your query store is also directly related to the statistics collection interval. The default for this value is 60 minutes, but you can adjust it to a higher frequency if you need more finely grained data. However, capturing data at a higher frequency requires more space for the query store. Another setting to consider is size-based cleanup mode. By default, the query store converts to read- only mode when full. When you enable size-based cleanup, SQL Server flushes older queries and plans as new data comes in, thereby continually providing the latest data. Another option for space conservation is adjusting the capture mode of the query store from ALL to AUTO, which eliminates the capture of queries having insignificant compile and execution detail. Tuning with the query store After enabling the query store and collecting data over a baseline period, you now have a wealth of data and options to start troubleshooting performance issues. The query store allows you to spend more time troubleshooting problem queries and improving them, rather than on trying to find the proverbial needle in a haystack. A simple approach is to start troubleshooting queries on the basis of highest resource consumption. For example, you can look at queries consuming the most CPU and logical I/Os. After identifying poorly performing queries, you can then consider the following options: If multiple plans are associated with a query, identify the best-performing plan and use the Force Plan option to request it for future executions. If you observe a large gap between the estimated rows and the actual rows in a query, updating statistics might help performance. If query logic is problematic overall, work with your development team to optimize the query logic. Stretch Database One of the more common refrains in IT infrastructure organizations in recent years has been the high costs of storage. A combination of regulatory and business requirements for long-term data retention, as well as the presence of more data sources, means enterprises are managing ever-increasing volumes of data. While the price of storage has dropped, as anyone who owns enterprise storage knows, the total cost of ownership (TCO) for enterprise storage commonly used for databases is still very high. Redundant arrays of independent disks (RAID), support contracts, management software, geographical redundancy, and storage administrators all add to the high total cost of enterprise storage. Another factor in the cost of storage is the lack of support for online data archiving in many third- party applications. To address this problem, a common approach is to use file groups and partitioning to move older data to slower disks. Although this approach can be effective, it also comes with high managerial overhead because it involves storage administrators in provisioning the storage and requires active management of partitions. Perhaps more important than the TCO of enterprise storage is the impact of large databases and tables on overall administration and availability of the systems. As tables grow to millions and even billions of rows, index maintenance and performance tuning become significantly more complex. These large databases also affect availability service-level agreements as restore times can often exceed service-level agreements required by the business. SQL Server 2016 introduces a new hybrid feature called Stretch Database that combines the power of Azure SQL Database with an on-premises SQL Server instance to provide nearly bottomless storage at

Articles in this issue

Archives of this issue

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