The Agenda

Most database performance issues are due to a combination of bad queries, bad database design or poor indexing. All of them are related to each other. In this session we will look at database performance from identification through to resolution.



This will include how to identify poor performance, how to dissect a query to understand what it should do and what indexes it should requires. Finally we resolve the performance problem by query rewriting, altering the database design, adding indexing and even not using the database.



During the session we will look at some of the common performance problems, like use of Common Table Expressions (CTEs), Temp tables and table variables, the OVER clause and parameter sniffing.



After this session you will understand

• the different tools for diagnosing problems, the impact and benefit of using them as well as practical examples.
• how queries are executed which enables you to rewrite queries effectively
• Common query patterns and the options for rewriting them to get better performance

The agenda for the day is as follows



Identifying performance problems

• Adhoc analysis, Trend analysis
• Tools to use, DMVs, Profiler, Extended EventsClear Trace
• Best practices

Diagnosing cause of performance

• Looking at resource usage, IO, CPU and memory
• Identifying contention locking/resource
  
  
  
  

Dissecting a query and its query plan

• How to work out what a query should do
• How tables are filtered using the “filter funnel process”
• The importance of seek predicates and normal predicates
• How are tables joined together, Hash, Merge and Loop joins
• Impact of statistics and parameterisation
• Understanding what indexes are needed for a query

Resolving performance

• Query rewriting
• Key areas that affect performance including
• Predicates
• Common table expressions
• Table variables and temp tables
• Functions
• Aggregations
• Parallelism
• Database design
• Normalisation
• Common pitfalls
• Partitioning should you or shouldn’t you
• Indexing
• How to determine the correct indexes,
• Missing index DMVs
• Impact of indexing