Chapter 1. 4 - Improving SQL Server Performance. It is provided as a courtesy for individuals who are still using these technologies. Meier, Srinath Vasireddy, Ashish Babbar, Sharon Bjeletich and Alex Mackman. Microsoft Corporation. May 2. 00. 4Related Links. Home Page for Improving . NET Application Performance and Scalability. Chapter 1. 2, Improving ADO. NET Performance. Checklist: SQL Server Performance. Send feedback to Scale@microsoft. SQL Server 2008 R2 went in with no problems. Benchmarking SQL Server IO with SQLIO. Instead I'm going to benchmark the L. Performance Monitoring and Tuning Tools. Microsoft SQL Server provides a comprehensive set of tools for monitoring events in SQL Server and for tuning the. Microsoft SQL Server 2008 sets ETL. The same file takes 2:50 min on my home PC using SQL Server 2005 ETL tools!! Sql Server 2008 Benchmark Tools For PcLibrary. Summary: This chapter provides proven development and configuration techniques to help improve the performance and scalability of SQL Server. Topics covered include designing efficient schemas, optimizing queries, tuning indexes, writing efficient stored procedures, analyzing and understanding execution plans, and much more. Contents. Objectives. Overview. How to Use This Chapter. SQL: Scale Up vs. Scale Out. Performance and Scalability Issues. Schema. Queries. Indexes. Transactions Stored Procedures. Execution Plans. Execution Plan Recompiles. SQL XMLTuning. Testing. Monitoring. Deployment Considerations.
Summary. Additional Resources. Objectives. Design efficient schemas. Optimize queries. Fine- tune indexes. Perform efficient transactions. Build efficient stored procedures. Analyze and understand execution plans. Identify and eliminate execution plan recompiles. Avoid scalability pitfalls when you use SQL XML. Tune Microsoft SQL Server. Test and monitor your data access performance. Consider how deployment decisions impact performance and scalability. Overview. There are many issues that affect SQL Server performance and scalability. This chapter discusses these issues, starting with data design and ending with deployment. The chapter emphasizes the techniques you can use to obtain the biggest gains in performance and scalability. You usually can obtain the biggest gains by creating efficient data access code for the application and by using correct development techniques. You usually do not obtain such big gains in performance and scalability by changing SQL Server configuration settings. Figure 1. 4. 1 shows where your performance design and tuning efforts are best focused. Figure 1. 4. 1: Focus for performance design and tuning efforts. The graph is meant to reflect the typical situation and to underscore the point that you obtain the best performance and scalability gains in application development. Indexing is considered part of the application development effort, although it is also part of administration. How to Use This Chapter. Use this chapter to apply proven strategies and best practices for designing and writing high- performance interop code. To get the most out of this chapter, do the following. Jump to topics or read from beginning to end. The main headings in this chapter help you locate the topics that interest you. Alternatively, you can read the chapter from beginning to end to gain a thorough appreciation of performance and scalability design issues. Measure your application performance. It is important for you to measure application performance so that you can accurately identify and resolve performance issues. Test your application performance. It is important for you to apply a coherent testing process and to analyze the results. Tune your application performance. Scale Out. Scaling up refers to moving an application to a larger class of hardware that uses more powerful processors, more memory, and quicker disk drives. Scaling out refers to an implementation of federated servers, where consumer- class computers are added and where data is then partitioned or replicated across them. You can scale out by using functional partitioning. For example, you might scale out by putting your Customer Relationship Management (CRM) functionality on one server and your Enterprise Resource Planning (ERP) functionality on another server. Or, you could scale out by using data partitioning. For example, you might scale out by creating updatable partitioned views across databases. Do not consider scaling up or scaling out until you are certain that you are getting the best performance that you can through application optimization. Consider the following scenarios when it comes to addressing two common scalability bottlenecks. Processor and memory- related bottlenecks. Scaling up is usually a good approach if your bottlenecks are processor related or memory related. By upgrading to a faster processor or by adding more processors, you maximize use of your existing hardware resources. You can resolve memory bottlenecks by adding additional memory or by upgrading existing memory. The /3. GB switch in the Boot. Address Windowing Extensions (AWE) also help maximize memory use. Scaling up can also help to resolve disk I/O. This form of bottleneck usually occurs in online transaction processing (OLTP) applications where an application performs random disk reads and writes, in contrast to sequential access in online analytical processing (OLAP) applications. For OLTP applications, the I/O load can be spread by adding disk drives. Adding memory also helps reduce I/O load. Because the I/O load is reduced, the size of the SQL Server buffer cache increases. As a result, page faults are reduced. Consider the following guidelines before you decide to scale up or scale out. Optimize the application before scaling up or scaling out. Address historical and reporting data. Scale up for most applications. Scale out when scaling up does not suffice or is cost- prohibitive. Optimize the Application Before Scaling Up or Scaling Out. Before you decide to scale up or to scale out, you need to be sure that it is required. Scaling out works best when you plan and design for it in the early stages of your application development life cycle. Changing your application after it is in production so that you can scale up or scale out is expensive. In addition, certain initial design decisions that you make may prevent you from scaling out later. You can resolve most performance and scalability issues by performing the optimization steps that are outlined in the rest of this chapter. These optimizations help reduce the impact of bottlenecks that are caused by specific design or implementation techniques. These optimizations also help ensure that existing resources are fully utilized. For example, with optimization, you can resolve bottlenecks that are caused by inefficient locking, unprepared SQL statements, poor indexes that lead to increased CPU utilization, and memory or disk I/O utilization. In practice, you need to simulate your data usage and growth early in the application life cycle by using a realistic workload. Simulating your data usage and growth helps you identify scalability issues sooner rather than later so that you can modify your design and approach to mitigate those issues. Address Historical and Reporting Data. Historical data may become very large over time and may cause long- running queries. Consider partitioning historical data by some range, and implement a way to limit older data. Either move the older data offline, or implement a separate data warehouse that contains the older data. Reporting needs may also be very resource intensive. You may consider upgrading your database server or scaling out to meet your reporting needs. By implementing a data warehouse or a reporting server, you may be able to provide faster response times and less resource contention. Additionally, a data warehouse or a reporting server is easier to manage than multiple servers in a federated server scheme. More Information. For more information about how to partition historical data, see . Or, consider adding more hardware to your existing server. High levels of system resource use include high CPU utilization, high memory use, and excessive disk I/O. The new components you might add include additional processors or memory. Alternatively, consider replacing your existing server with a new, more powerful server. Ensure that any configuration changes take full advantage of the new hardware. For example, you may need to use the /3. GB switch in the Boot. This is an easy next step for both migration and maintenance reasons. You should perform tests to help determine the new server capacity that you require. More Information. For more information about testing, see Chapter 1. These approaches usually require certain tables to be horizontally partitioned so that they reside on separate servers. The approaches may also require some replication between servers of the main domain tables that also have to be available on a partition. Disaster recovery and failover are also more complex for federated servers. You have to determine if the benefit of this added complexity outweighs the cost advantage of being able to use consumer- class computers for federated servers. More Information. For general information about SQL Server scalability, see . Later sections in this chapter provide strategies and technical implementation details to help you prevent or resolve each of the following issues. Not knowing the performance and scalability characteristics of your system. If performance and scalability of a system are important to you, the biggest mistake that you can make is to not know the actual performance and scalability characteristics of important queries, and the effect the different queries have on each other in a multiuser system. You achieve performance and scalability when you limit resource use and handle contention for those resources. Contention is caused by locking and by physical contention. Resource use includes CPU utilization, network I/O, disk I/O, and memory use. Retrieving too much data. A common mistake is to retrieve more data than you actually require. Retrieving too much data leads to increased network traffic, and increased server and client resource use. This can include both columns and rows. Misuse of transactions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2017
Categories |