sql explore 2012: p&t part 3
TRANSCRIPT
![Page 1: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/1.jpg)
DBA: Boost Your Server
![Page 2: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/2.jpg)
Row-1
Tran2 (Select)
Tran1 (Update)
X-Lock S-Lock BlockedRow-1
Reader Writer Blocking
Data Page
![Page 3: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/3.jpg)
Read Committed Snapshot
• 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
![Page 4: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/4.jpg)
Demo: RCSI
![Page 5: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/5.jpg)
Lock Escalation
HOBT
Page Page Page
Row Row Row
T1: IX
T1: IX
T1: XT1: X
T1: XT1: X
T1: XT1: X
T1: XT1: X
T1: X
Lock Escalation
![Page 6: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/6.jpg)
Lock Escalation
• Converting finer-grain locks to coarse grain locks.• Row to Table• Page to Table.
• Benefits• Reduced locking overhead• Reduces Memory requirement
• Triggered when• Number of locks acquired on a rowset > 5000• Memory pressure
![Page 7: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/7.jpg)
Partitioned Tables and Indexes
• SQL Server 2005 introduced partitioning, which some customers use to scale a query workload• Another common use is to streamline maintenance and enable fast
range inserts and removals from tables
FG1 FG2 FG3
PartitionedTable
![Page 8: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/8.jpg)
Lock Escalation: The Problem
• Lock escalation on partitioned tables reduces concurrency as the table lock locks ALL partitions
• Only way to solve this in SQL Server 2005 is to disable lock escalation
IXX
FG1 FG2 FG3
PartitionedTable
Partition 1 Partition 2 Partition 3
Query 1ESCALATE
Query 2
update update
![Page 9: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/9.jpg)
Lock Escalation: The Solution
• SQL Server 2008 & up allows lock escalation to the partition level, allowing concurrent access to other partitions
• Escalation to partition level does not block queries on other partitions
IX
XFG1 FG2 FG3
PartitionedTable
Partition 1 Partition 2 Partition 3
Query 1ESCALATE
Query 2
update update
![Page 10: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/10.jpg)
Demo: Partition
Level Lock Escalation
![Page 11: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/11.jpg)
Filtered Indexes
• An index with a WHERE clause to specify a criteria
• Essentially index only a subset of a the table• Query optimizer most likely to use when WHERE
clause matches that of the filtered index
![Page 12: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/12.jpg)
Using Filtered Indexes
• Advantages of Filtered Indexes• Improve query performance, partly by
enhancing execution plan quality• Smaller index maintenance costs• Less disk storage
![Page 13: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/13.jpg)
Using Filtered Indexes
• Scenarios for Filtered Indexes• Sparse columns, where most data is null• Columns with categories of values• Columns with distinct ranges of values
![Page 14: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/14.jpg)
Demo: Filtered Indexes
![Page 15: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/15.jpg)
Optimize for ad hoc workloads
• New server option in SQL Server 2008
• Only a stub is cached on first execution
• Full plan cached after second execution
• SP_CONFIGURE 'show advanced options',1RECONFIGUREGO
• SP_CONFIGURE 'optimize for ad hoc workloads',1RECONFIGUREGO
![Page 16: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/16.jpg)
How good is it?
![Page 17: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/17.jpg)
Demo: Optimize for
ad hoc workload
![Page 18: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/18.jpg)
Configuration:Don’t get confused!
![Page 19: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/19.jpg)
Configuration Cheats
![Page 20: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/20.jpg)
Processor Funny Games
![Page 21: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/21.jpg)
TempDB
• Create a file per CPU that SQL Server uses• Not more than 8
![Page 22: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/22.jpg)
Memory Configuration
• X86? Really?• Microsoft Knowledge Base article 274750
![Page 23: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/23.jpg)
Lock Pages in Memory
• Yes/No?• Enterprise Edition only
• Can be done on Standard using latest SPs for 2005/2008 and trace flag 845 for 2008 R2
• AWE is ignored in 64 bit• The ‘Local System’ account has the ‘lock pages in
memory’ privilege by default• Configure MaxServerMemory
![Page 24: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/24.jpg)
Min/Max Memory
• Yes/No?
![Page 25: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/25.jpg)
Min/Max Memory
![Page 26: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/26.jpg)
Min/Max Memory
• Yes/No?• Keep an eye on Available MB counter
![Page 27: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/27.jpg)
![Page 28: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/28.jpg)
Reason and Solution?
• Note the name Memory (Private Working Set) – • AWE APIs are used on 64bit to “lock” pages • That memory is not part of the working set
• Only trust:• Perfmon SQL Server memory counters• sys.dm_os_process_memory DMV
![Page 29: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/29.jpg)
Demo:Lock Pages in Memory
![Page 30: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/30.jpg)
Compression
![Page 31: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/31.jpg)
As data volume grows…
• Large databases =
• Storage Cost
• Workload Performance
• Manageability Cost
• Backup/Recovery
![Page 32: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/32.jpg)
Compression
• Store data efficiently in the row/page
• (+) More data can fit in memory
• (+) Better Performance for I/O bound workload
• (-) Performance degradation for CPU bound workload
![Page 33: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/33.jpg)
Data Compression
• SQL Server 2008 • ROW and PAGE compression• Backup Compression
• SQL Server 2008 R2• Unicode compression
• SQL Server 2008 R2• Spatial indexes compression
![Page 34: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/34.jpg)
Enabling Compression: Microsoft’s Perspective
• Latest partition uncompressed
Jan-Mar Apr-June July-Sept Oct-Dec
PAGE Compressed
Uncompressed
ROW Compressed
![Page 35: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/35.jpg)
Should it be so complex?
In real life – usually compress the entire large tables using page compression…
![Page 36: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/36.jpg)
Summary – Compression
• Can reduce size of database significantly• Lower total cost of ownership (TCO)• Easy to enable/disable• No application changes• Performance gains!
![Page 37: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/37.jpg)
Demo: Compression
![Page 38: SQL Explore 2012: P&T Part 3](https://reader035.vdocuments.us/reader035/viewer/2022062513/555e0e25d8b42a9e188b4dc5/html5/thumbnails/38.jpg)