Tuesday, June 26, 2012

MSTechEd Europe 2012 Pre-Con: SQL Server Performance Tuning and Optimization - It all depends...

Yesterday I was at the Pre-conference Day at the MSTechEd Europe. I learned a lot during the session and tried to make some useful notes so I can use them for later reference. Specially the part on DMVs was very useful, I didn't use them before and certainly am checking them in the next weeks!

By Denny Cherry and Thomas LaRock

1. Identifying performance bottlenecks
Tools used:
- Performance Monitor
- Activity Monitor
- DB Tuning Advisor (DTA)
- Data collection
- Profiler
- SQLTrace
- Dynamic Management Views (DMVs)
- Extended Events (xEvents)
Blocking processes / wait events (running, runnable, waiting) aren't shown in Profiler (= SQLTrace + visualization). Those can be shown with the xEvents Wizard under Management in SSMS. Always tune it to your needs, never use a default (See PAL).
Summary:
- Define the problem
- Decide for tool with the best metrics and least (perf.) cost
- Consider the time needed to analyze
- It depends..
2. Index optimization
1. Indexing
- Started at design time
- Data types matter!
- Changes over time
- First line of defense for (quick) perf. Tuning
Do we have the right indexes?
- It depends..
- Follow best practices
- Use DMVs for missing indexes
- Focus on Logical IO (Inside SQL Server 2005: T-SQL Querying by Itzik Ben-Gan)
SQL Diagramming > table with highest selectivity of data
Index seek is better than Index scan.
If an index has only writes and no reads > delete the index
2. Execution Plans
Look for:
- Correlation with wait events
- Examine first operator (Select | Update | ..)
- Warnings (missing index)
- "More costly"  operators: LIKE > Spool uses tempdb
- "Fat fingers": thick arrows means lots of rows
- Looking at the #rows in
□ An index > update statistics
□ Hash match > input vs output rows (do nothing)
3. Filtered Indexes
An index with a filter (where clause), but no extended logic (e.g. OR)
4. ColumnStore Indexes
- Based on the xVelocity in-memory analytics engine (a.k.a. Vertipaq)
- CREATE COLUMNSTORE INDEX [COLUMNS]
- Downside: the table is read-only, workarounds:
□ Delete index > load data > create index
□ Use 2 tables, 1 read-only and 1 read/write table
3. Storage optimization
1. Table partitioning
- Reduce maintenance window
- Improve query response time
- Reduce recovery time
Create filegroups, a partition function and a scheme referencing the function.
Business (logic) knowledge is essential before starting partitioning!
When moving data: meta data (page headers) is updated
When deleting data: 
- Move partition to another table > then truncate (faster and save)
- Also getting data back that shouldn't be deleted is faster. Let the data stay in the other table for a few days before actually deleting it.
2. AlwaysOn (Enterprise feature)
- No need for shared storage
- Offload backups
- Scaling out read-only activity
4. Troubleshooting
- Are all queries running slow?
- Use xEvents with DMVs
- Is a subset of queries running slow?
- Use Executions plans with DMVs
Useful DMVs:
- Sys.dm_exec_query_stats
- Sys.dm_exec_query_plan
- Sys.dm_exec_cached_plans
STATISTICS IO ON
STATISTICS TIME ON
Using GO 10 after a SQL statement will execute the statement 10 times, useful when inserting data.


Resources mentioned can be found @ http://mrdenny.com/res/teched-2012-precon