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
Use a profiler to measure before you optimise. If ever you doubt this advice, then you should read this fascinating account of how an application was given a five-fold speed increase just by finding out exactly where the performance problems were and then tackling them in detail.
… 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
Amirthalingam Prasanna gives a simple and practical guide about why you need to profile your .NET applications and how you would go about doing it.… 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
Robyn and Phil tackle the topic of how to make .NET applications perform well. As usual, they try to take a terse, practical approach to the mysteries of JIT, CLR and GC. After giving many performance tips, they come to the conclusion that rules and tips are less useful than rolling up your sleeves and measuring or profiling everything possible, to see what's really happening with your application.… Read more
Improving the performance of your application often boils down to rephrasing the problem you are trying to solve. James discusses common pitfalls and problems when creating applications in .NET.… Read more
Grant Fritchey provides an eagle-eye view of effective tools, strategies and techniques for SQL Server performance measurement, tuning and testing.… 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
Performance is a concern for all but the simplest of applications. Making your applications perform well seems a simple enough ask, but it can be something of a black art. This is where profiling comes in.… Read more
This White Paper investigates how XML web services are implemented, considers the customizable features of web services and looks at load testing a web service, specifically so that test strategies can be formulated.… Read more
This article aims to give an introduction to the challenges in testing web services, for those who do not have detailed technical knowledge about web services.… Read more