1 / 83

Performance Tuning in SQL Server

Performance Tuning in SQL Server. Antonios Chatzipavlis. Software Architect , Development Evangelist, IT Consultant. MCT, MCITP, MCPD, MCSD, MCDBA, MCSA, MCTS, MCAD, MCP, OCA. Objectives. Why is Performance Tuning Necessary? How to Optimize SQL Server for performance

monifa
Download Presentation

Performance Tuning in SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Performance Tuning in SQL Server Antonios Chatzipavlis Software Architect , Development Evangelist, IT Consultant MCT, MCITP, MCPD, MCSD, MCDBA, MCSA, MCTS, MCAD, MCP, OCA

  2. Objectives Why is Performance Tuning Necessary? How to Optimize SQL Server for performance How to Optimize Database for performance How to Optimize Query for performance Define and implement monitoring standards for database servers and instances How to troubleshoot SQL Server

  3. Performance Tuning in SQL Server Why is performance tuning necessary?

  4. Why is Performance Tuning Necessary? Allowing your system to scale • Adding more customers • Adding more features Improve overall system performance Save money but not wasting resources • The database is typically one of the most expensive resources in a datacenter

  5. General Scaling Options Purchase a larger server, and replace the existing system. Works well with smaller systems. Cost prohibitive for larger systems. Can be a temporary solution. Scaling SQL Server with Bigger Hardware

  6. General Scaling Options Purchase more hardware and split or partition the database. Partitioning can be either vertical or horizontal • Vertical: Split the databases based on a specific demographic such as time zone or zip code. • Horizontal: Split components out of one database into another Scaling SQL Server with More Hardware

  7. General Scaling Options Adjusting and rewriting queries. Adding indexes. Removing indexes. Re-architecting the database schema. Moving things that shouldn’t be in the database. Eliminating redundant work on the database. Caching of data. Other performance tuning techniques. Scaling SQL Server without adding hardware

  8. demo • Database Partitioning

  9. Performance Tuning in SQL Server How to Optimize SQL Server for performance

  10. Performance Factors CPU Memory IO Network TempDB

  11. CPU and SQL Server CPU Intensive Operations • Compression • Bulk Load operations • Compiling or Recompiling Queries Hyper-Threading • Is just 1.3 times better than non hyper-threaded execution • The currently accepted best practice recommendation is that you should run SQL Server with Hyper-Threading disabled L3 Cache

  12. CPU and SQL Server Performance Counters

  13. Memory and SQL Server Tuning 32-bit Systems • Use /PAE and /3GB Together (Windows 2003) • Running BCDEDIT /set increaseUserVA 3072 (Windows 2008) Tuning 64-bit Systems • If needed, enable AWE on Enterprise Edition of SQL Server • If needed, enable AWE on Standard Edition of SQL Server only when SP1 with Cumulative Update 2 applied. Read more info at http://support.microsoft.com/kb/970070 Enable Address Windowing Extensions (AWE)

  14. Memory and SQL Server Control the allowable size of SQL Server’s buffer pool. Do not control all of SQL Server’s memory usage, just the buffer pool. When the SQL Server service starts, it does not acquire all the memory configured in Min Server Memory but instead starts with only the minimal required, growing as necessary. Once memory usage has increased beyond the Min Server Memory setting, SQL Server won’t release any memory below that figure. Max Server Memory is the opposite of Min Server Memory, setting a “ceiling” for the buffer pool Min and Max Server Memory

  15. Memory and SQL Server Look at the buffer pool’s maximum usage. • Set SQL Server to dynamically manage memory • Monitor MSSQLSERVER : MemoryManager\Total Server Memory (KB) counter using Performance Monitor Determine the maximum potential for non-buffer pool usage. • 2GB for Windows • xGB for SQL Server worker threads • Each thread use 0.5MB on x86, 2MB on x64, and 4MB on Itanium. • 1GB for multi-page allocations, linked servers, and other consumers of memory outside the buffer pool • 1–3GB for other applications that might be running on the system, such as backup programs How to configure Max Server Memory

  16. Memory and SQL Server In 8-CPU cores and 16GB of RAM running SQL Server 2008 x64 and a third-party backup utility, you would allow the following: • 2GB for Windows • 1GB for worker threads (576 Χ 2MB rounded down) • 1GB for MPAs, etc. • 1GB for the backup program The total is 5GB, and you would configure Max Server Memory to 11GB. Example of Max Server Memory configuration

  17. Memory and SQL Server Performance Counters

  18. IO and SQL Server RAID 5 • Loved by storage administrators • Dominated choice for non-database applications • It’s cost effective and cost efficient • Minimize the space required in the datacenter (fewer drives need fewer bays) RAID 10 • Microsoft recommendation for log files Storage Area Networks (SANs) • Performance is not always predictable if two servers share the same drive iSCSI Storage Area Networks • For good performance needs dedicated switches. Choose the right hard disk subsystem

  19. IO and SQL Server Best practices dictate that SQL Server • data files, • logs, • tempdb files • backup files are all written to separate arrays Put log files on RAID 10 Put data files on RAID 5 (to save money) Choosing Which Files to Place on Which Disks

  20. IO and SQL Server Increase IO performance but has CPU penalty The SQL Server engine has to compress the data before writing the page, and decompress the data after reading the page However, in practice this penalty is far outweighed by the time saved waiting for storage. Read more at http://msdn.microsoft.com/en-us/library/dd894051.aspx Example: If a 10GB index is compressed down to 3GB, then an index scan will be completed 70% faster simply because the data takes less time to read off the drives. Is Enterprise Edition feature Using Compression to Gain Performance

  21. IO and SQL Server Performance Counters • Monitor the Page Faults/sec counter in the Memory object to make sure that the diskactivity is not caused by paging. • If you have more than one logical partition on the same hard disk, use the Logical Disk counters rather than the Physical Disk counters.

  22. demo • Use Performance Monitor

  23. Performance Tuning in SQL Server How to Optimize Database for performance

  24. Performance Optimization Model Server Tuning Locking Indexing Query Optimization Schema Design

  25. Schema Design Optimization In this process you organize data to minimize redundancy, which eliminates duplicated data and logical ambiguities in the database Normalization

  26. Schema Design Optimization In this process you re-introduce redundancy to the database to optimize performance. When to use denormalization: • To pre-aggregate data • To avoid multiple/complex joins When not to use denormalization: • To prevent simple joins • To provide reporting data • To prevent same row calculations Denormalization

  27. Schema Design Optimization In this process you group similar entities together into a single entity to reduce the amount of required data access code. Use generalization when: • A large number of entities appear to be of the same type • Multiple entities contain the same attributes Do not use generalization when: • It results in an overly complex design that is difficult to manage Generalization

  28. Schema Design Optimization Generalization Example

  29. Performance Tuning in SQL Server How to Optimize Query for performance

  30. Key factors for query performance: SQL Server tools to measure query performance: • Resources used to execute the query • Time required for query execution • Performance Monitor • SQL Server Profiler Key Measures for Query Performance

  31. Logical Execution of Query (8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (6) WITH {CUBE | ROLLUP} (7) HAVING <having_condition> (10) ORDER BY <order_by_list>

  32. Logical Execution of Query Example Data

  33. Logical Execution of Query Example Query & Results SELECT C.customerid, COUNT(O.orderid) AS numorders FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O ON C.customerid = O.customerid WHERE C.city = 'Athens' GROUP BY C.customerid HAVING COUNT(O.orderid) < 3 ORDER BY numorders;

  34. Logical Execution of Query 1st Step - Cross Join FROM dbo.Customers AS C ... JOIN dbo.Orders AS O

  35. Logical Execution of Query 2nd Step- Apply Join condition ON Filter ON C.customerid = O.customerid

  36. Logical Execution of Query 3rd Step - Apply OUTER Join FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O

  37. Logical Execution of Query 4th Step - Apply WHERE filter WHERE C.city = 'Athens'

  38. Logical Execution of Query 5th Step - Apply Grouping GROUP BY C.customerid

  39. Logical Execution of Query 6th Step - Apply Cube or Rollup

  40. Logical Execution of Query 7th Step - Apply HAVING Filter HAVING COUNT(O.orderid) < 3

  41. Logical Execution of Query 8th Step - Apply SELECT List SELECT C.customerid, COUNT(O.orderid) AS numorders

  42. Logical Execution of Query 9th Step - Apply DISTINCT

  43. Logical Execution of Query 10th Step - Apply ORDER BY ORDER BY numorders

  44. Logical Execution of Query 11th Step - Apply TOP

  45. Logical Execution of Query Get the Result

  46. Performance Tuning in SQL Server How to Optimize Query for performance Top 10 for Building Efficient Queries

  47. Top 10 for Building Efficient Queries The most important factor to consider when tuning queries is how to properly express logic in a set-based manner. Cursors or other procedural constructs limit the query optimizer’s ability to generate flexible query plans. Cursors can therefore reduce the possibility of performance improvements in many situations 1.Favor set-based logic over procedural or cursor logic

  48. Top 10 for Building Efficient Queries The query optimizer can often produce widely different plans for logically equivalent queries. Test different techniques, such as joins or subqueries, to find out which perform better in various situations. 2.Test query variations for performance

  49. Top 10 for Building Efficient Queries You must work with the SQL Server query optimizer, rather than against it, to create efficient queries. Query hints tell the query optimizer how to behave and therefore override the optimizer’s ability to do its job properly. If you eliminate the optimizer’s choices, you might limit yourself to a query plan that is less than ideal. Use query hints only when you are absolutely certain that the query optimizer is incorrect. 3.Avoid query hints.

  50. Top 10 for Building Efficient Queries Since the query optimizer is able to integrate subqueries into the main query flow in a variety of ways, subqueries might help in various query tuning situations. Subqueries can be especially useful in situations in which you create a join to a table only to verify the existence of correlated rows. For better performance, replace these kinds of joins with correlated subqueries that make use of the EXISTSoperator 4.Use correlated subqueries to improve performance. --Using a LEFT JOIN SELECT a.parent_key FROM parent_table a LEFT JOIN child_table b ON a.parent_key = b.parent_key WHERE B.parent_key IS NULL --Using a NOT EXISTS SELECT a.parent_key FROM parent_table a WHERE NOT EXISTS (SELECT * FROM child_table b WHERE a.parent_key =b.parent_key)

More Related