In the first part of this series of articles, Gail showed how to find the procedures that contributed most to performance problems. In this final part, she shows how to use query statistics to pinpoint the queries that are causing the biggest problems, and then use the query execution plans to find out which operations are the root cause. With this information one can select and modify the indexes that can optimize the plans and therefore performance of the query.… Read more
To tackle performance problems with applications, you first find the queries that constitute a typical workload, using SQL Profiler: Then, from the trace, you find the queries or stored procedures that are having the most impact. After that, it is down to examining the execution plans and query statistics to identify queries that need tuning and indexes that need creating. You then See what effects you've had and maybe repeat the process. Gail explains all, in a two-part article.… Read more
In order to be able to tackle performance issues in SQL Server , and write effective SQL, it is essential to gain a sound understanding of execution plans. Grant's previous article described graphical execution plans for Simple SQL queries. He now moves on to cover some of the more complicated plans that every database programmer will see.… Read more
With SQL Server Profiler, it is easy to discover all those queries that are running slowly. Once poorly performing queries are identified, there comes the harder task of speeding them up. Once again, the Profiler can provide a lot of information that can help diagnose and resolve these performance problems. Brad describes this essential part of the process of performance-tuning in an article taken from a chapter to his new book, Mastering SQL Server Profiler.… Read more
Learning how to read and analyze execution plans takes time and effort. But once you gain some experience, you will find them an essential skill for getting to grips with performance issues in SQL Server Queries. Grant here describes the range of execution plans for different simple SQL Queries.… Read more
Some reasons for the slow-running of database applications aren't obvious. Occasionally, even the profiler won't tell you enough to remedy a problem, especially when a SQL Statement is being forced to wait.
Now, in SQL Server 2008, come XEvents, which allow you to look at those wait events that are slowing your SQL Statements.
Mario Broodbakker continues his series about SQL Server Wait Events
SQL Server 2008 wait event based performance analysis using XEvents… Read more
Every day, out in the various discussion boards devoted to Microsoft SQL Server, the same types of questions come up again and again: Why is this query running slow? Is my index getting used? Why isn't my index getting used? Why does this query run faster than this query?. The correct response is probably different in each case, but in order to arrive at the answer you have to ask the same return question in each case: have you looked at the execution plan? … Read more
SQL 2005 wait event statistics are a good step forward, but are only useful for isolated testing, and to get a global impression on how the SQL Server is spending its time. Mario Broodbakker demonstrates why it would be much better if SQL Server collected those statistics per session and per subtask.… Read more
Measuring what is actually happening is always the best course of action when investigating performance issues on databases, rather than relying on cache hit ratios, or best practices, or worst of all, guesswork. This article introduces some techniques that will allow you to pinpoint exactly where the performance issues are in your system, so you'll know exactly where to spend your time (and money) in solving them.
… Read more
When faced with two viable solutions to a badly compromised database design, one using clustered indexes and the other compound primary keys, Grant Fritchey took the only sensible route: he gathered hard performance data...… Read more