SQL Server Performance Programming René Balzano Technology Solution Professional Data Platform Microsoft Switzerland This Session is about How to design databases and T-SQL code in a way.
Download ReportTranscript SQL Server Performance Programming René Balzano Technology Solution Professional Data Platform Microsoft Switzerland This Session is about How to design databases and T-SQL code in a way.
SQL Server Performance Programming René Balzano Technology Solution Professional Data Platform Microsoft Switzerland This Session is about How to design databases and T-SQL code in a way that helps achieving good performance How to monitor and analyze what might decrease the performance of your database and application does not contain C#, EF, ODBC, Visual Studio, German, French, etc. Help your DBA The DBA who runs the database you have programmed can compensate for many design-flaws and improve performance without touching your code You don’t want to depend on a DBA’s skill and attention when it comes to defining your application’s performance Design your database and the interaction of your application with it in the best possible way, so that your database performs well even without a DBA’s intervention To design for optimal and less DBA-dependent performance, it helps to understand what goes on under the hood of SQL Server So let’s have a look… Scenario #1 Demo… Page Splits and Fragmentation Choosing the right keys Why a Clustered Index? Nonclustered (secondary) Index on a Heap: Ordered by index key: Date Record pointer is RID (file#:page#:position#) file #1:page #2131 (1st page of this index) 19620522 1:2132 20010319 1:2139 … … … … … … … … … … … … This example (simplified): 4000 index records, 16bytes/record, 506 records/page 9 index pages: 1 index b-tree page (#2131) + 8 index leaf pages (#2132-#2139) (8096 bytes data/page) file #1:page #2132 (2nd page of this index) 19620522 1:121:1 … 19811111 1:121:13 19811121 1:122:13 19880502 1:121:3 … … … … … … … … … … 19890212 1:122:14 When a record in a heap moves to a different disk location, its entry has to be updated in ALL secondary indexes , resulting in increased disk activity and reduced performance for other tasks … … … … … … … … … … … 19910414 1:121:14 20010108 1:122:4 20010219 1:121:2 … … file #1:page #121 (1st page of this table) Heap: Unordered Record identified by RID (file#:page#:position#) 121:1 1 This example (simplified): 4000 records, 550kb/record, 14 records/page 286 pages (#121 - #407) (8096 bytes data/page) 121:2 121:3 121:4 121:5 … 121:11 121:12 121:13 121:14 5 6 3 … … … 15 22 9 Huber Meier Meier Oberst … … … Glauser Keller Zurbriggen … … … … … … … … … … file #1:page #2139 (9th page of this index) 20070502 20010219 19880502 20110107 … … … 19620522 19811111 19910414 … 20010319 1:122:2 … … … … … … 20020722 1:122:12 20070502 1:121:1 … … … … … … … … … … … … … … … … … … … 20080511 1:122:3 … … 20110107 1:121:4 … … file #1:page #122 122:1 122:2 122:3 122:4 122:5 … 122:11 122:12 122:13 122:14 2 5 4 7 … … … 49 18 15 Amsler Kern Zorbas Klaus … … … Straub Djuric Dankner … file #1:page #407 (286th page of this table) … … … … … … … … … … 20080502 20010319 20080511 20010108 … … … 20020722 19811121 19890212 … 407:1 407:2 407:3 407:4 407:5 … 407:11 407:12 407:13 407:14 … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … …. … … Why a Clustered Index? file #1:page #112 (1st page of this clustered table) 1 22 … … … … … … Clustered Index: Ordered by clustering Key: ID Record identified by Clustering Key This example (simplified): 4000 data records, 550kb/record, 14 records/page 286 leaf (data) pages (#121 - #407) 4000 index records, 16bytes/record, 506 records/page 9 index pages: 1 index b-tree page (#112) + 8 index leaf pages (#113-#120) 1:113 1:120 … … … … … … file #1:page #113 (2nd page of this clustered table) file #1:page #120 (9th page of this clustered table) 1 1:121:1 … 1:121:2 1:121:3 1:121:4 1:121:5 1:121:6 1:121:7 … … 1:121:13 2 3 4 5.1 5.2 6 … … 7 Total of 295 pages for this clustered index (8096 bytes data/page) … … … … … … 9 … 15.1 15.2 … … … … … 1:122:4 … 1:122.12 1:122.13 22 … … … 49 … … … … … … 1:407:1 … … … … 1:407:5 … … … … … … file #1:page #121 (10st page of this table) file #1:page #122 121:1 121:2 121:3 121:4 121:5 121:6 121:7 … 121:13 121:14 1 2 3 4 5 5 6 … 7 … … Huber Amsler Oberst Zorbas Meier Kern Meier … Klaus … … … … … … … … … … … 20070502 20080502 20110107 20080511 20010219 20010319 19880502 … 20010108 … 122:1 122:2 122:3 122:4 122:5 122:6 … 122:12 122:13 122:14 … … … 9 … … … 15 15 18 … … … Zurbriggen … … … Dankner Glauser Djuric … … … … … … … … … … … … … … … … … … file #1:page #407 (295th page of this table) … … … … … … 19910414 … … … … 19890212 … 19620522 … 19811121 … 407:1 407:2 407:3 … 407:5 … 407:11 407:12 407:13 407:14 22 … … … 49 … … … … … Keller … … … Straub … … … … … … … … … … … … … … … 19811111 … … … 20020722 … … …. … … Why a Clustered Index? Avoid secondary updates when changing data file #1:page #2131 (1st page of this index) 19620522 1:2132 20010319 1:2139 … … … … … … … … … … … … Nonclustered (secondary) Index on a Clustered Table (Index): Ordered by index key: Date file #1:page #2132 (2nd page of this index) Record pointer is Clustering Key (ID) Same size as in previous example. When a record in a clustered table moves to a different disk location, its entry only has to be updated in the ONE clustered index, no secondary index has to be touched, no extensive disk activity results. Clustered Table (Index) 1 1:121:1 … … 1 22 … … … … … … 19620522 15 … 19811111 22 19811121 18 19880502 6 … … … … … … … … … … 19890212 15 … … … … … … … … … … … 19910414 9 20010108 7 20010219 5.1 … … 1:113 1:120 … … … … … … 22 … 1:407:1 … … … file #1:page #2139 (9th page of this index) … … 20010319 5.2 … … … … … … 20020722 49 20070502 1 … … … … … … … … … … … … … … … … … … … … 20080511 4 … … 20110107 3 … … Why a short Clustering Key? Avoid large secondary indexes and large numbers of disk IOs … file #1:page #2132 (2nd page of this index) Large clustering keys result in every secondary index being larger, thus increasing disk activity (number of pages to read from disk). Eventually this leads to additional levels in the B-tree, adding one or more extra IOs to EVERY read or update operation in EVERY secondary index. 19620522 0813A496-817E-43DBB01B-B7C5B0EDFA70 | Glauser | Peter 19811111 AEE9226F-0796-4D029C62-44B0FBCFB15B | Keller | Klara … … 19811121 9E2EF8A9-C1F5-482496B8-3669CF8FC875 | Djuric | Vladimir … … file #1:page #2230 (99th page of this index) … 20010319 036B285D-6D20-4BA2BA3D-A4AC40B6AD8E | Kern | Beat… … … … 20020722 2D498FB1-8085-436EA783-CB4E800F9AF7 | Straub | Trudi … … … … … … … … … … … … … … Why a monotonous growing Clustering Key? Avoid Page Splits Page splits occur when new data has to be inserted in ordered full pages. A page split results in increased disk activity. An index (including the clustered table) in which many pages splits have occured, is fragmented (pages with consecutive ordered data are spread over the disk, resulting in slower IO operations). If a clustering key’s values don’t grow monotonous, page splits occur on the base table, having a large negative impact on IO performance during writes and reads (fragmentation). Full page, sorted by Date 19620522 15 … 19811111 22 19811121 18 19880502 6 … … … … … … … … … … 19890212 15 … … … … … … … … … … … 19910414 9 20010108 7 20010219 5.1 … … Inserting 19850101 leads to page split: 19620522 15 19811111 22 19811121 18 19850101 66 19880502 6 … … … … … … … … … … 19890212 15 … … … … … … 19910414 9 20010108 7 20010219 5.1 … … Indexes and Performance To minimize disk activity when inserting and updating data and to reduce the number of disk IOs when reading data (= keep fragmentation low): Always have a clustered index Define clustering keys small monotonously growing with unchanging values Be prepared for rebuilding indexes as they show fragmentation Clustering Keys Great int with IDENTITY clause (SEQUENCE in Denali) date or datetime, e.g. a timestamp value To be avoided GUID, UNIQUEIDENTIFIER (or at least use NEWSEQUENTIALID) varchar fields, at least those that aren’t tiny composite keys with multiple fields Monitoring Fragmentation Check fragmentation of your indexes (tables) : select * from sys.dm_db_index_physical_stats (db_id(),null,null,null,null) Goal: As low as possible, reorg above 10%, rebuild above 30% Check page density DBCC SHOWCONTIG Goal: As high as possible (also depends on record size and fillfactor) Scenario #2 Demo… Parallelism Don’t be afraid of the CXPACKET Parallelism SQL Server tries to parallelize over all available cores (minus 1) by default Parallellism is generally great for querying, but not necessarily so in OLTP settings Be careful: Seeing CXPACKET waitstats often lets programmers use MAXDOP 1 to avoid parallelization CXPACKET waits are not necessarily bad, they occur in most ‘healthy’ parallelization settings too If SQL Server parallelizes wrongly (so that you would see high numbers for CXPACKET and use MAXDOP 1) this could also be due to bad indexing or outdated statistics Still: Parallelizing generates overhead for splitting up the workload and later recombining the results In certain settings (usually OLTP with many writes) MAXDOP 1 improves performance (e.g. recommended server-wide setting for SharePoint’s SQL Server configuration) Since as a developer you won’t know what the DBA sets with sp_configure, consider using the MAXDOP clause in your code when you know that parallelism isn’t useful Monitoring Parallelism If you think that parallelism might be the source of a performance bottleneck See if your query plan is a parallel one Check CXPACKET values in sys.dm_os_wait_stats Scenario #3 Demo… Don’t denormalize From Cubes to Columnstore: Life gets easier Denormalization Many techniques exist to denormalize a technical data model for improved performance Cubes with pre-calculated aggregates Temporary tables with redundant copies of values from related tables and pre-calculated aggregates Denormalized technical models with redundant values Technologies exist and evolve that make denormalization less necessary Indexed views can replace temporary tables Compressed tables and indexes improve performance per se Light indexing based on columnar storage can further improve performance without touching the base tables or indexing them Indexed Views An index on a view persists the view’s data content to disk Using indexed views instead of data ‘manually’ copied to redundant temporary tables or replicated columns relieves you from maintaining the redundant objects Using temporary objects in Stored Procedures can lead to increased recompiles Since for the majority of database applications updates represent only a single digit percentage of all operations, very often the update-overhead for an additional index (on the view) is neglegible Mind the schema-binding requirements for indexed views Compression Database compression is a feature of SQL Server 2008’s Enterprise Edition and above Compressing indexes and tables improves performance substantially, since a smaller number of disk pages have to be accessed For medium and large databases even processor load goes down, since fewer pages have to be maintained, reducing management overhead Compression is transparent for any application, you don’t have to touch any code when you start using compression Columnstore Indexes SQL Server 2012 introduces columnstore indexes, based on Vertipaq technology (PowerPivot) Columnstore indexes speed up data access hugely through A new storage architecture (columnar instead of column-wise) Much higher compression than prevoiusly Highly improved access algorithms A single columnstore index on a table covers any query that is run against that table You no longer need to create and maintain a separate covering index for every important query, making you less dependent on your DBA to rebuild them etc. The original technical data model performs much better, without denormalization and without the us of extensive indexing (-> light indexing, see PDW) Caveat in first release: The base table under a columnstore index will be read-only (workarounds exist) How columnstore speeds up queries ID Name City State 1 John Seattle WA 2 Jane Redmond WA 3 Jill Redmond OR 4 Jane Bellevue WA Row Store Column Store 1 John Seattle WA 2 Jane Redmond WA 3 Jill Redmond OR 4 Jane Bellevue, WA 1234 John Jane Jill Jane Seattle Redmond Redmond Bellevue WA WA OR WA How columnstore speeds up queries SELECT region, sum (sales) … C2 Fetches only needed columns from disk Less IO Better buffer hit rates C1 C3 C4 C5 C6 How columnstore speeds up queries Advanced query processing technology Batch mode execution of some operations Processes column data in batches Groups of batch operations in query plan Compact data representation Highly efficient algorithms Better parallelism How columnstore speeds up queries C1 C2 C3 C4 C5 C6 Column Segment Segment contains values from one column for a set of rows Segments for the same set of rows comprise a row group Segments are compressed Each segment stored in a separate LOB Segment is unit of transfer between disk and memory By the way: Recompilation Stored Procedures will be recompiled automatically, if relevant information was not available to the optimizer when they were compiled the last time or when such information has changed in the meantime (e.g. the structure of a referenced table) Recompiles have a negative impact on performance, increasing processor load and blocking (locked objects during compilation) Make sure that you place DML in a bloc at the beginning of a SP and use temporary objects and SET statements defensively Use Profiler (SP:Recompile Event Class) to analyze the reasons for recompilations SQL Server 2008 and 2012 reduce recompiles considerably too Scenario #4 Demo… Locking and Blocking Manage your isolation levels actively Transaction Isolation The transaction isolation level defines how you want to access data that is in use by others how you want others to be restricted when accessing the same data that you are using The transaction isolation level is a property of your database connection It is usually defined as a default of the client-application or –library (e.g. Tools-Options in SSMS) The default setting generally is READ COMMITTED With this setting, you prevent some operations for others while you’re in a transaction you often wait unnecessarily even if you just read uncritical and unchanging data (e.g. for reporting and data warehousing) Frequently waiting for locks to be lifted (being blocked) makes applications slow, they're just waiting alll the time... Consider setting your isolation level to READ UNCOMMITTED for reads in uncritical situations using SQL Server 2008’s READ COMMITTED SNAPSHOT ISOLATION mode (with this you depend on your DBA, it also implies increased load for tempdb) Monitoring for Blocking Via SSMS sp_lock sys.dm_tran_lock (allows WHERE) sys.dm_os_wait_stats (allows WHERE) Via Performance Monitor MSSQL$yourinstance:Locks Lock Waits/sec etc. Scenario #5 Demo… Varchar fields Beware of growing their content Varchar fields As long as their content is short, varchar fields are placed on the same disk page as the rest of their record If an existing varchar field value is updated to a longer value that no longer fits on the same page, it is offloaded to a separate disk area, with a link remaining on the original page This operation creates additional disk IOs that will impact the database’s performance (fragmentation could stay beneath the common 30% threshold for rebuilds) If your application follows the habit of first creating a new record with empty or default values then reading back the default values and doing some additional stuff on the client and finally updating the new record to its final values you might be doing just that by default… There is more to say Watch out for additional performance topics at http://blogs.technet.com/b/swisssql Review Page Splits and Fragmentation: Choosing the right keys. Parallelism: Don’t fear the CXPACKET Don’t denormalize: Indexed views, compression and columnstore Locking and Blocking: Manage your isolation levels actively Varchar fields: Beware of growing their content The Tools DMV select sys.dm_ (IntelliSense will help you further) SSMS Settings SET STATISTICS IO ON Profiler Watch out for Extended Events Performance Monitor Give us your feedback! Please help us make TechDays even better by evaluating this session. Thank you! © 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.