As seen at SQL Saturday Denver
Problem Statement:
- One of the most common relational database query performance problems stems from reading too much data in order to obtain a relatively small query result set.
- DBAs and Developers are often unaware of the excessive reads that cause queries to run slow.
- Excessive reads is often solved by proper indexing, index maintenance and query tuning.
- Determining proper indexing is not an exact science, as it requires an understanding of a variety of key factors, including how data is being accessed, performance trade-offs, as well as the size, structure and dynamic nature of the data.
- Long hours and extensive domain knowledge is required by a DBA or Developer to understand how queries are reading data in ways that lead to poor query performance. Analysis needs to be done on many queries, tables and indexes, and aggregating the results to obtain a holistic view of each table’s inefficient read activity. Armed with this hard-earned knowledge, a DBA or Developer can determine the appropriate indexes to add/change in order to decrease read activity that is contributing to poor query performance.
- The system dynamic changes when an indexing decision is made and acted upon, or when new queries start executing due to a software upgrade, or perhaps as table data grows … and the analysis needs to be done all over again.
Attached is the code we used to demonstrate the how to.
Our goal in the session was to provide you with the building blocks of "Table Tuning" in hopes you would make this script even better!
Enjoy!
