SQL Server Performance Diagnosis and Query Tuning
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
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.
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.
this session you will understand
- the different tools for
diagnosing problems, the impact and benefit of using them as well as
- how queries are executed
which enables you to rewrite queries effectively
- Common query patterns and the
options for rewriting them to get better performance
agenda for the day is as follows
cause of performance
- Adhoc analysis, Trend
- Tools to use, DMVs, Profiler,
Extended EventsClear Trace
- Best practices
a query and its query plan
- Looking at resource usage,
IO, CPU and memory
- Identifying contention
- How to work out what a query
- 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
- Understanding what indexes
are needed for a query
- Query rewriting
- Key areas that affect
- Common table expressions
- Table variables and temp
- Database design
- Common pitfalls
- Partitioning should you or
- How to determine the correct
- Missing index DMVs
- Impact of indexing
Sorry, there are no downloads available for this session.
Simon has worked with data for all his career enabling companies to make the most of the data they have .
He works with companies to help them
1. Understand and define a cloud data platform strategy.
2. Optimise their data platform, including performance, scalability, security and certification
3. Improve their data development practices including implementation of agile methodologies and continuous integration.
Education of teams is at the heart of everything Simon does.
The passion for education led Simon to found SQLBits, the largest SQL conference in Europe. He is also a SQL Microsoft Certified Master and has been Microsoft MVP since 2005
You can follow him @simon_sabin or read his blog http://sqlblogcasts.com/blogs/simons
The video is not available to view online.