Pre-conference day seminar: Monday, September 26th 2011, 09-17h
Seminar level: 400
Description:
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 require 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
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
- 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.
