Transcript Advanced Query Techniques: Beyond the basics

Advanced Query Techniques
Statistics and Indexes
Taming SQL Performance issues
Identify (potential) Issues
How to solve common query questions
Core concepts
Reference
http://www.microsoft.com/en-us/download/details.aspx?id=22052
Core concepts
Inner Join = Intersection
System
(Computer)
Workstation
Status
Core concepts
Left Join = Intersection + Left
System
(Computer)
Workstation
Status
Understand the query process
Identify the underlying issue(s)
Remediate
Statistics
Indexes
Used by the Query Processor
May improve query optimization plan
Many plans (estimates) may be evaluated
Plan with the least cost, wins!
How are they created?
How are they maintained?
How do you know if they are stale?
How to update?
When to update?
Used by the Storage Engine
Index Types
How is Index data stored?
Page Splits
Index reorganization
Index rebuilds
Fill Factor
How to update?
ALTER INDEX
What’s best?
Measure, choose and monitor
SQLAudit
Document Implementation
Identify (potential) Issues
Sequence
Extensible!
Profiler
Show Query Plan
Custom database solution: CMMonitor
What does it do?
Why is it needed?
Key Advantages
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
How do we implement?
Steps involved
Query Scenarios
Query Process Summary
Document implementation: SQLAudit
Implement database solution: CMMonitor
The ask – report back!
http://SteveThompsonMVP.wordpress.com
[email protected]
Statistics
http://msdn.microsoft.com/en-us/library/ms190397(v=sql.110).aspx
Indexes
http://msdn.microsoft.com/en-us/library/ms175049.aspx
SQL Server Maintenance Solution
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
How to read graphical query plans
http://www.mssqltips.com/sqlservertip/1873/how-to-read-sql-server-graphical-query-executionplans/