Performance Tuning and Query Optimisation–SQLBits Training Day
I will be doing a training day at SQLbits in April on Performance Tuning and Query Optimisation.
This is the outline for the day. Its going to be an intense day, I look forward to seeing you there. To register go to http://www.sqlbits.com/information/registration.aspx. Places are limited so make sure you register soon.
Outline of the day.
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 and finally how to 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.
The agenda for the day is as follows
Identifying performance problems
- Adhoc analysis
- Trend analysis
- Tools to use, DMVs, Profiler, Extended Events, Clear 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
- Query rewriting
- Key areas that affect performance including
- Common table expressions
- Table variables and temp tables
- Database design
- Common pitfalls
- Partitioning should you or shouldn’t you
- How to determine the correct indexes,
- Missing index DMVs
- Impact of indexing
- Not use the DB
- When to push back to the application
After this session you will be able to analyse the performance of your database application, identify areas that can be improved, dissect the relevant queries to find the cause of the poor performance and resolve the problem by changing the queries, the database, adding indexes or getting the application to change.
To register go to http://www.sqlbits.com/information/registration.aspx