SQL Server Performance Diagnosis and Query Tuning

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
Presented by Simon Sabin at SQLBits XII
  • Downloads
    Sorry, there are no downloads available for this session.
  • SpeakerBIO

    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

    http://sqlblogcasts.com/blogs/simons http://sqlblogcasts.com/blogs/simons/privaterss.aspx
  • Video
    The video is not available to view online.