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 runs a Data Consultancy Sabin.io enabling companies to make the most of the data they have.

    He has worked with data for all his career and worked with companies across all industry sectors including online retail, insurance, finance, motor sport.

    He works with companies to help them

    1. Improve their data development practices including implementation of devops, agile methodologies and continuous integration.

    2. Understand and define a cloud data platform strategy.

    3. Optimise their data platform, including performance, scalability, security and certification

    Education of people is at the heart of what Simon and his company sabin.io stand for. it is epitomised by SQLBits, which Simon founded in 2007. Its the largest SQL Server conference outside of Europe and many view as the best in the world, always maintaining a free element and ensuring education for everyone.

    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.