I am a SQL Server Performance Tuning Expert who acts as a consultant for many organizations across the world who have performance tuning issues. I am often showed one or more of the tables of the system and asked to tune the system around those tables. The task of table tuning is very challenging. In this blog post, we will discuss Database Performance Analyzer – Table Tuning Advisors.
Table Tuning Challenges
I am very confident that if I asked you to name the table creating the maximum trouble for your system, you could easily give me one or two names. However, if I ask you to fix only the performance related problems for those two tables, what would be your reaction?
When I am asked to tune tables, there are two major challenges in front of me.
- How exactly has the table performed across the system in the past?
- What is the history of the table relating to indexes, schema, row changes etc.?
I am very confident that you would find it difficult to answer the two questions above. Even though you are familiar with your system, it is practically impossible to know the impact of table tuning on your system beforehand.
The Alternative Route: Query Tuning
As we discussed, table tuning is very difficult and many experts take the alternative route of Query Tuning. This option is relatively easy as we only have to identify which queries are taking the most resources of your system and tune them.
However, when we start tuning a query, the first things we need to know are:
- Query Execution Plan
- Query IO and Time Statistics
Now, if you look carefully you will realize that once again we are talking about table tuning only.
When we look at the query execution plan, we are essentially looking which particular operator is costing the most in your query. The most of the time the operator costing the most is the Table Scan or Index Scan.
Similarly, in Query IO statistics we also look for the table or object doing the maximum logical (or physical) read for that particular query.
Trust me on this one – no matter what angle we look at the Query Tuning exercise from, we will eventually be forced to look at Table Tuning again!
Table Tuning – Back to Square One
Now that we have circled back to needing to tune the tables to get maximum performance for our application, let us address the elephant in the room.
How do we begin tuning the table if we do not know the critical data related to two important challenges discussed at the beginning of this blog post?
As SQL Server does not have all the necessary history for tables, we need to depend on a third-party solution to guide us in this situation.
Table Tuning Advisors
Fortunately, Database Performance Analyzer
(DPA) version 12.0 has introduced the new feature of Table Tuning Advisors.
Every day, DPA identifies tables that have inefficient queries run against them. For each table, the Table Tuning Advisor page displays aggregated information about the inefficient queries, the table structure, and any existing indexes.
The new Tuning Tab displays all query and table advice for the selected database so you can access both query and table tuning advice from one place. The advisors are ranked, and the most serious issues are listed first.
Let us look at some screenshots of Table Tuning Advisors in Database Performance Analyzer (DPA v 12).
Next Action
I strongly suggest that you try out Database Performance Analyzer (DPA). I am very confident that you won’t be disappointed by the new feature of Table Tuning Advisors.
Honestly, I have never seen any other tool out there that looks at the database performance issues from the same angle as DPA 12.0. Honestly, DPA 12.0 is more human than any of us and truly gets the sense of how to look at the system from the angle of SQL Server Performance Tuning Expert.
Reference: Pinal Dave (https://blog.sqlauthority.com)
