sql server 2012 performance tuning michelle gutzait
TRANSCRIPT
![Page 1: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/1.jpg)
SQL Server 2012 Performance tuning
Michelle Gutzait [email protected]
Blog: http://michelle-gutzait.spaces.live.com/default.aspx
![Page 3: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/3.jpg)
- Database Tuning advisor (DTA) Enhancements
- Column store indexes
- Online index operations
New in SQL 2012
![Page 4: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/4.jpg)
Servers
Applications
Users
WEB
Users
![Page 5: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/5.jpg)
Server is too slow Something is broken
Application crashes Weird behavior Timeouts
Heavy process
![Page 6: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/6.jpg)
![Page 7: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/7.jpg)
![Page 8: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/8.jpg)
![Page 9: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/9.jpg)
![Page 10: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/10.jpg)
Windows Performance Monitor
SQL Server Profiler &
SQL Traces
ReadTrace
SQL Server Management Studio
Windows Management Instrumentation
(WMI)
![Page 11: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/11.jpg)
Database Engine Tuning Advisor
DMVs and statistics
SQL Server 2008 Activity Monitor
SQL Server 2008 and R2 Ent tools…
![Page 12: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/12.jpg)
PSSDiag
SQLDiag and PerfStats
Performance Analysis of Logs
(PAL)
![Page 13: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/13.jpg)
SQL Nexus
Performance
Dashboard
![Page 14: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/14.jpg)
3-rd party tool
![Page 15: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/15.jpg)
The search engine…
![Page 16: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/16.jpg)
So also… SCRIPT REPOSITORIES
![Page 17: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/17.jpg)
Index and T-SQL
Analysis with
DMVs
![Page 18: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/18.jpg)
SQL Server 2005/2008 Index analysis - Database Tuning Advisor
![Page 19: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/19.jpg)
![Page 20: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/20.jpg)
![Page 21: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/21.jpg)
Significant enhancement
![Page 22: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/22.jpg)
![Page 23: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/23.jpg)
Reads/writes (IO)
![Page 24: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/24.jpg)
Large IO
Index design
Index and disk fragmentation
Query design
Heavy user activity
![Page 25: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/25.jpg)
Disk Contention
Memory constraints
Large indexes and tables
Network contention
Blocks/locks/deadlocks
![Page 26: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/26.jpg)
Index / Data
Page =
8K
![Page 27: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/27.jpg)
All
columns of
the index
key are in
one row
![Page 28: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/28.jpg)
Minimizing
IO
![Page 29: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/29.jpg)
![Page 30: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/30.jpg)
Traditional index ColumnStore
![Page 31: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/31.jpg)
Optimized for star schema design
Data Compression
Batch Execution Mode
Segment Elimination
![Page 32: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/32.jpg)
Data Compression Compress and store data in memory with Vertipaq™
technology
High compression for repetitive values
![Page 33: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/33.jpg)
Batch Execution Mode
Batch vs. Row Mode
Batch = around 1000 rows
Vector-based structure in-memory
Processed all at once
Up to 40 times CPU reduction
Optimized with large memory and multicores
DOP >= 2
![Page 34: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/34.jpg)
Segment Elimination
Indexes are partitioned into segments
![Page 35: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/35.jpg)
![Page 36: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/36.jpg)
Clustering
Database
mirroring
Log
Shipping
Disk mirroring
Replication Database
Snapshots
3-rd party
solutions
Always ON
![Page 37: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/37.jpg)
Integrity checks
Update Stats
Backups
Index
maintenance Clean History
Data archive
Patching
Upgrades/move
![Page 38: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/38.jpg)
An
available
running
database
![Page 39: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/39.jpg)
CREATE INDEX
ALTER INDEX
DROP INDEX
ALTER TABLE
(UNIQUE/PRIMARY
keys)
![Page 40: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/40.jpg)
![Page 41: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/41.jpg)
Large Value Type column *VAR*(MAX)
PRINT @@VERSION
go
-- Create a Table
CREATE TABLE Items
(
ItemID INT IDENTITY,
ItemName VARCHAR(100),
VarcharData VARCHAR(max)
)
go
-- Create an Index
CREATE NONCLUSTERED INDEX
[IDX_ItemIdxTest]
ON [dbo].[Items] ( [ItemID] ASC,
[ItemName] ASC )
include ( [VarcharData] )
WITH ( ONLINE = ON )
go
SQL < 2012
![Page 42: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/42.jpg)
![Page 43: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/43.jpg)
Always On
Increased number of partitions (15,000 vs. 1000)
Indirect checkpoints for database recovery time
Geographically dispersed Clustering
New in SQL 2012
![Page 44: SQL Server 2012 Performance tuning Michelle Gutzait](https://reader035.vdocuments.us/reader035/viewer/2022071601/613d4b48736caf36b75b9f7c/html5/thumbnails/44.jpg)
And more…