Some I/O intensive workloads can gain performance benefits through data compression. Compressed tables and indexes means more data stored in fewer pages, and hence require reading fewer pages from disk, which in turn can improve the performance of workloads that are I/O intensive.
For a data warehouse workload running on SQL Server in Azure VM, we found significant improvement in query performance by using page compression on tables and indexes, as shown in Figure 1.
Figure 1: Query Performance with Data Compression
Figure 1 compares performance of one query with no compression (NONE) and page compression (PAGE). As illustrated, the logical and physical reads are significantly reduced with page compression, and so is the elapsed time. As expected, CPU time of the query does go up with page compression, because SQL Server needs to decompress the data while returning results to the query. Your results will vary, depending upon your workload.
For an OLTP workload, we observed significant improvements in throughput (as measured by business transactions per second) by using page compression on selected tables and indexes that were involved in the I/O intensive workload. Figure 2 compares the throughput and CPU usage for the OLTP workload with and without page compression.
Figure 2: OLTP Throughput and CPU Usage with Data Compression
Note that you may see different results when you test your workloads in Azure Virtual Machine environment. But we recommend that you test data compression techniques for I/O intensive workloads and then decide which tables and indexes to compress. For more information, see Data Compression: Strategy, Capacity Planning and Best Practices.
Restore performance – instant file initialization
For databases of any significant size, enabling instant file initialization can improve the performance of some operations involving database files, such as creating a database or restoring a database, adding files to a database or extending the size of an existing file, autogrow, and so on. For information, see How and Why to Enable Instant File Initialization.
To take advantage of instant file initialization, you grant the SQL Server (MSSQLSERVER) service account with SE_MANAGE_VOLUME_NAME and add it to the Perform Volume Maintenance Tasks security policy. If you are using a SQL Server platform image for Azure, the default service account (NT Service\MSSQLSERVER) isn’t added to the Perform Volume Maintenance Tasks security policy. In other words, instant file initialization is not enabled in a SQL Server Azure platform image.
After adding the SQL Server service account to the Perform Volume Maintenance Tasks security policy, restart the SQL Server service.
The following figure illustrates observed test results for creating and restoring a 100 GB database with and without instant file initialization.
Figure 3: Performance Impact of Instant File Initialization
For more information, see Database File Initialization.
Other existing best practices
Many of the best practices when running SQL Server on premises are still relevant in Azure Virtual Machines, including:
-
Limit or disable autogrow on the database: Autogrow is considered to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow. If autogrow is used, pre-grow the file using the Size switch.
-
Disable autoshrink on the database: Make sure autoshrink is disabled to avoid unnecessary overhead that can negatively affect performance. For more information about autogrow and autoshrink, see Considerations for the "autogrow" and "autoshrink" settings in SQL Server.
-
Establish locked pages to reduce IO and any paging activities: Lock pages in memory is a Windows policy that determines, which account can use a process to keep memory allocations pinned in physical memory. It prevents the system from paging the data to virtual memory on disk. When the SQL Server service account is granted this user right, buffer pool memory cannot be paged out by Windows. For more information about enabling the Lock pages in memory user right, see How to: Enable the Lock Pages in Memory Option (Windows).
Performance troubleshooting fundamental concepts
This section provides an overview of how you troubleshoot SQL Server when running on a Azure virtual machine.
Traditional factors that govern performance in SQL Server
Performance analysis in SQL Server is well documented. The Troubleshooting Performance Problems in SQL Server 2008 article provides comprehensive information on this subject. You can also find other advanced performance-related blogs and articles on topics such as latch and spinlock analysis at SQLCAT.com. Here, let’s summarize the main performance factors:
-
Plan change/plan choice issues:
-
SQL Server Query Optimizer searches and chooses the optimal plan that can improve the system performance. The query optimizer’s choices are usually correct, but there are cases where it can be suboptimal. This can be caused by a number of factors, including out-of-date input data (such as statistics), poor index coverage and, in some cases, issues with the optimizer model or functionality. Query tuning and appropriate index creation and management can help you tune your I/O, CPU, and memory usage and optimize your workload performance.
-
As discussed previously, using SQL Server in Azure virtual machine has also other performance implications for the performance of your workload. Due to its multi-tenant nature and various capping and throttling mechanisms, you might notice different I/O performance results over time. That’s why we recommend that you minimize the number of reads and writes that your query requires by tuning the query plan, and also apply SQL Server performance optimization techniques, such as compression. For more information about the Optimizer, see the Optimizer chapter in the book SQL Server 2008 Internals or the upcoming SQL Server 2012 Internals.
-
Improperly configured software or hardware: One common cause of I/O bottleneck is when I/O intensive files, such as data and log files for a high throughput database, are placed on the operating system disk drive. As a general rule, we recommend that you should add more data disks and balance your data and log files according to your IOPS requirements when using SQL Server in Azure Virtual Machines.
-
Locking and latching: Excessive locking and latching can be caused by plan choice, or concurrency volumes due to overall system throughput; or certain schema and workload patterns. Locking is used to allow multiple users to make consistent changes to the same data in a controlled manner. Latching is used to adjudicate multi-user access to structures within SQL Server. Latching is somewhat orthogonal to locking; both issues can apply and the core resolution is often similar. If there is a hot latch or lock, the typical answer is to change the calling pattern (either by forcing different plan shapes or rearranging the calling code) to mitigate the impact of the blocking latch or lock on overall throughput. If you observe high PAGEIOLATCH waits on your system, this means that a user requests a page that is not in the buffer pool and I/O system is slow to respond. In this case, we recommend that you spread your I/O workload across more data disks or gain more memory by increasing the virtual machine instance size to improve the performance within Azure virtual machine.
-
Multi-user operations and system resource usage: Some user operations may not run, or they may run at a reduced level of performance because of insufficient system resources. Some type of maintenance and schema management operations can increase resource utilization. For example, index rebuild operations can cause increased I/O workload on your system. You may think that your application’s queries or operations are taking longer than usual. But the internal cause is pressure on one or more resources. We recommend that you consider the resource usage of both the user application’s queries and the maintenance tasks in your capacity planning. Especially for I/O intensive operations, we recommend that you choose your virtual machine size appropriately and plan your maintenance windows to avoid your application’s peaks periods.
-
Checkpoints and system operations: Flushing I/Os to disk during the checkpoint process can cause a spike in I/O operations. This might slow the performance of queries and impact throughput. It is important to determine your I/O workload and test for a period longer than the checkpoint frequency.
Dostları ilə paylaş: |
