Strategies to Get Maximum Concurrency for Your Workload in Microsoft SQL Server

1. Required Slide SESSION CODE: DAT315 Strategies to Get Maximum Concurrency for Your Workload in Microsoft SQL Server Sunil Agarwal Senior Program Manager sunila@microsoft.com Microsoft Corporation

2. Goals • Concurrency challenges with applications • Identifying and troubleshooting concurrency issues • Strategies for creating applications for higher concurrency

3. Common Concurrency Issues with Applications • Wrong transaction isolation level • Incorrect locking granularity • Missing Indexes • Accessing large dataset leading to lock escalation • Long running transactions • User interactions in active transaction • Accessing objects in reverse order across transactions • Application induced hotspots

4. Isolation Levels • SQL-Standard specifies four isolation levels • SQL Server has implemented these all along using lock based implementation. • Read Uncommitted • Most concurrent, least restrictive, more anomalies are possible • SQL Server: No share locks • Read Committed • Only reads committed data. Data can change over multiple reads • SQL Server: Data access only after acquiring S-lock. Lock released when moved to next row/page • Repeatable Read • Read rows don’t change. Phantom rows can appear • SQL Server: Data access only after acquiring S-lock. Lock released at the end of the transaction • Serializable • Least concurrent, most restrictive, always ‘correct’ • SQL Server: Data access only after acquiring S-lock. Lock released at the end of the transaction • Isolation-levels impact the concurrency • SQL2005 introduced RCSI (Read Committed Snapshot) and SI (Snapshot Isolation) using non-blocking implementation.

5. Tran2 (Select) Tran1 (Update) Reader Writer Blocking Data Page S-Lock Blocked Row-1 Row-1 X-Lock

6. Read Committed SnapshotStatement-Level ‘Snapshot Isolation’ • New “flavor” of read committed • Turn ON/OFF on a database • Readers see committed values as of beginning of statement • Writers do not block Readers • Readers do not block Writers • Writers do block writers • Can greatly reduce locking / deadlocking without changing applications

7. Snapshot IsolationTransaction level ‘Snapshot Isolation’ • SQL Server proprietary isolation level • Turn ON/OFF on a database • Available since SQL Server 2005 • Readers see committed values as of beginning of the Transaction • Writers do not block Readers • Readers do not block Writers • Writers do block writers • Can greatly reduce locking / deadlocking but requires application change • Set Transaction Isolation level Snapshot • Scenario: Cross query data view consistency

8. Locking Granularity • Small lock granules, such as rows: • Maximize concurrency • Are expensive to maintain(require many lock manager calls). • Approx 100 byte overhead • Large granules, such as tables: • Expensive in terms of concurrency • Cheap to acquire (few lock manager calls) • Multi-granular lock protocols allow us to have it both ways • Decided at run-time using internal heuristic • Can be over-ridden using sp_indexoption or locking hint • Lock Escalation

9. Lock Modes Common locking modes Mode Description S Share - used for reading X Exclusive - typically used for writing (insert, update, delete) U Update - used to evaluate prior to writing IS Intent Share - share locking at finer level IX Intent Exclusive - X locking at finer level BU Bulk Update. Used in Bulk Load SCH-S Schema Share SCH-M Schema modify lock

10. Locking Hints • Locking hints are used to • Change locking behavior at statement level • Example: TABLOCK, UPDLOCK • To change isolation level at statement level • Examples: NOLOCK, READPAST, TABLOCK, READCOMMITTED, REPEATABLEREAD • Lock duration • Depends on isolation level it is acquired at.

11. Blocking Detection and Troubleshooting Sunil AgarwalSenior Program ManagerMicrosoft DEMO

12. HOBT T1: IX T1: X Page Page Page T1: X T1: X T1: X T1: X T1: X Row Row Row T1: X T1: X T1: X Lock Escalation Lock Escalation T1: IX

13. Lock Escalation • Converting finer-grain locks to coarse grain locks. • Row to Table • Page to Table. • No escalation from Row to Page • Benefits • Reduced locking overhead • Reduces Memory requirement. Each lock structure is 100 bytes • Impedes concurrency • Triggered when • Number of locks acquired on a rowset > 5000. Periodically after that • It is not based on the locks in a transaction • Memory pressure

14. Lock Escalation… Cont • Controlling Lock Escalation • TF-1211 disables lock escalation for the instance. • SQL Server can run out of lock memory • TF 1224 disables 5000 limit but keeps lock escalation due to memory pressure. • Using ALTER TABLE to disable/enable lock escalation (SQL2008) • Allows lock escalation at partition level

15. Lock Escalation Detection and Troubleshooting Sunil AgarwalSenior Program ManagerMicrosoft DEMO

16. Silent Blocker: Page Latches • What is a Pagelatch? • Schema and access patterns leading to latch contention • Clustering index has a increasing primary key with high rate of inserts • Relatively small tables with a lot of read/write/delete • Indexes with low cardinality in the key with high rate of inserts • Allocation Structures (e.g. SGAM) • Observed with 16+ concurrent threads Page Header R1 Free Offset R2 R3

17. Silent Blocker: Page Latches • What can you do? • Cluster on some other column • Partition the table on some other attribute (computed column (hash)) • Add another major key column • Change the application

18. Page Latch Contention and Troubleshooting Sunil AgarwalSenior Program ManagerMicrosoft DEMO

19. Deadlock • Example: • User 1 holds X lock on page P1 • User 2 holds X lock on page P2. • User 1 requests to acquire SH lock on P2 • It waits for User 2 • User 2 requests to acquire SH lock on P1 • It waits for User 1 • Leads to an indefinite wait

20. Deadlock: Detection • Background Thread (Lock Monitor) • Runs every 5 seconds (adjusted dynamically) • When deadlock detected, a victim is chosen based on • Transaction that has done minimum amount of work • Deadlock priority. • Victim transaction is rolled back and an error 1205 is generated. • Deadlock Troubleshooting • Use SQL profiler • User TF-1204 (SQL2000) • TF-1222 (SQL2005)

21. Deadlock Detection and Troubleshooting Sunil AgarwalSenior Program ManagerMicrosoft DEMO

22. Deadlock: Avoidance • Access objects/rows in same order • Use lower isolation level • Use read-committed-snapshot or snapshot isolation • Use shorter duration transactions • Watch out for lock escalation

23. Guidelines: Concurrent Application • Choose lowest isolation needed for consistency • Use shorter transaction when possible • Avoid using larger row sets • Break the statement into smaller rowsets • Minimize large scans • Enable index usage when possible • When possible, design to spread DML across multiple pages

24. Summary • Balance consistency vs. concurrency • Factors that impact concurrency • Troubleshooting Blocking • Best practices for developing concurrent applications

25. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. DAT Track Scratch 2 Win • Find the DAT Track Surface Table in the Yellow Section of the TLC • Try your luck to win a Zune HD • Simply scratch the game pieces on the DAT Track Surface Table and Match 3 Zune HDs to win

26. Required Slide Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn

27. Required Slide Complete an evaluation on CommNet and enter to win!

28. Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st http://northamerica.msteched.com/registration You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year

29. © 2010 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.

30. Required Slide