"query execution: expectation - reality (level 300)" Денис Резник
TRANSCRIPT
![Page 1: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/1.jpg)
SQL Server. Expectation – Reality
Denis ReznikIntapp
Email: [email protected]: denisreznikBlog: http://reznik.uneta.com.uaTwitter: @denisreznik
![Page 2: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/2.jpg)
Agenda
• Some Useful Stuff to Remind
• Expectation - Reality 1
• Expectation - Reality 2
• Expectation - Reality 3
• Expectation - Reality 4
• Expectation - Reality 5
![Page 3: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/3.jpg)
Index (B-Tree) - Seek
…
…
1 .. 1M
1 .. 2K 2K+1 .. 4K
1M-2K .. 1M
1 .. 300 301..800 801..1,5K 1,5K+1..2K
SELECT * FROM UsersWHERE Id = 523
![Page 4: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/4.jpg)
Index (B-Tree) - Scan
…
…
1 .. 1M
1 .. 2K 2K+1 .. 4K
1M-2K .. 1M
1 .. 300 301..800 801..1,5K 1,5K+1..2K
SELECT * FROM Users
![Page 5: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/5.jpg)
Statistics
500
1000
10
1200
800
1 800 2000 2800 4500 5400
SELECT * FROM UsersWHERE Id BETWEEN 2100 AND 2500SELECT * FROM UsersWHERE Id BETWEEN 1 AND 5300
![Page 6: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/6.jpg)
Exec SomeProc @p
Optimizer
Buffer Pool
Query Plan cache
@p
Plan is cached for the first value of Query Plan
cache
Parameter Sniffing – Intro
![Page 7: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/7.jpg)
Lock Types - Shared
S S
X
![Page 8: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/8.jpg)
Lock Types - Exclusive
X
X
S
![Page 9: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/9.jpg)
Lock Types - Update
U
U
S
SX
![Page 10: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/10.jpg)
READ COMMITTED
ID City
1 Kiev
2 London
3 London
4 London
5 London
6 New York
7 New York
ID City
1 London
2 London
3 London
4 London
5 London
6 New York
7 New York
SELECT * FROM UsersWHERE City = 'Kiev'
BEGIN TRANUPDATE UsersSET City = 'London'WHERE City = 'Kiev' SELECT * FROM Users
WHERE City = 'Kiev'
Wait for Shared lock on the row
X S
![Page 11: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/11.jpg)
READ COMMITTED SNAPSHOT
ID City
1 Kiev
2 London
3 London
4 London
5 London
6 New York
7 New York
ID City
1 London
2 London
3 London
4 London
5 London
6 New York
7 New York
BEGIN TRANUPDATE UsersSET City = 'London'WHERE City = 'Kiev'
SELECT * FROM UsersWHERE City = 'Kiev'
SELECT * FROM UsersWHERE City = 'Kiev'
X
tempdb
ID City
1 Kiev
Version Store
![Page 12: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/12.jpg)
READ UNCOMMITTED
ID City
1 Kiev
2 London
3 London
4 London
5 London
6 New York
7 New York
ID City
1 London
2 London
3 London
4 London
5 London
6 New York
7 New York
SELECT * FROM UsersWHERE City = 'Kiev'
BEGIN TRANUPDATE UsersSET City = 'London'WHERE City = 'Kiev' SELECT * FROM Users
WHERE City = 'Kiev'
0 RecordsROLLBACK
SELECT * FROM UsersWHERE City = 'Kiev'
X
![Page 13: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/13.jpg)
DEMO: Universal Query
![Page 14: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/14.jpg)
DEMO: Conditional Logic
![Page 15: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/15.jpg)
DEMO: Implicit Locks
![Page 16: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/16.jpg)
DEMO: NOLOCK Locks
![Page 17: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/17.jpg)
DEMO: The Most Confusing Behavior
![Page 18: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/18.jpg)
Summary
• Universal Query
• Conditional Logic
• Implicit Locks
• NOLOCK Locks
• The Most Confusing Behavior
![Page 19: "Query Execution: Expectation - Reality (Level 300)" Денис Резник](https://reader033.vdocuments.us/reader033/viewer/2022051710/58ef213e1a28ab135e8b45f3/html5/thumbnails/19.jpg)
Thank You!
@denisreznik
http://reznik.uneta.com.ua/
https://www.facebook.com/denis.reznik.5
https://www.linkedin.com/pub/denis-reznik/3/502/234