Pinal Dave has done it again as he brought his signature style and enthusiasm to Quest’s Database Training Days. With over 1,000 people in attendance, Pinal addressed the age-old question, “Who doesn’t have a database performance problem?”
Since it’s perfectly normal, and in fact part of a DBA’s job to make systems run faster, we’re always looking for performance fixes. One of Pinal’s recommendations is not to focus on the SQL execution plan, but also to enable statistics and look at the live stats associated with SQL query execution.
One of the most common questions Pinal gets is around SQL Server performance after an upgrade. It makes sense that the system will perform better with upgraded hardware and software, but that’s not always what people experience. Pinal reminds us that a higher compatibility level plus a higher cardinality estimation will equal better performance. Why? Because the compatibility level is like a set of algorithms that makes the hardware and software work better together – they have to be in sync.
Pinal used a basic SQL query to demonstrate how changing the compatibility level can improve the use of system resources. He also showed how changing the compatibility level ensures you’re using the latest features included in your version of SQL Server. As an example, he showed how join types have evolved over the versions so they are now adaptive. This newest feature ensures that SQL Server decides which type of join will work best in the SQL query. Without the right compatibility level, you won’t be able to take advantage of this performance-boosting enhancement.
Pinal also took some time to discuss cardinality and the use of Row Goals while engaging in a systematic approach to diagnose problems. His takeaway lesson was that performance issues are often caused by poor configuration and not necessarily by poor queries. The easiest place to start is checking those configurations before diving deeper into the queries.
Toward the end of the program, Peter O’Connell from Quest spent a few minutes talking about how Foglight® for Cross-Platform Databases is a critical tool in keeping resource consumption down. Features like multi-platform monitoring and multi-dimensional workload analysis ensure that DBAs can spot issues and diagnose the root cause quickly before the problem grows. Get a free trial and see how it works.
You can watch this program any time, on-demand and you’ll be glad you did since there’s much more to learn in the session than what’s described here.
