Performance

Home SQL .NET SysAdmin Opinion Blogs Forums Books

SQL Home > Performance

A Performance Troubleshooting Methodology for SQL Server

by Jonathan Kehayias | 28 September 2011 |

16 comments |

When healing a sick SQL Server, you must forget the idea that there could ever be a simple correspondence between symptom and disease: The art of troubleshooting is much more the art of discovering, and assembling, the various pieces of the puzzle so that you have... Read more...

Regular Rapid Releases: An Agile Tale

by Mark and Stephanie | 07 July 2011 |

3 comments |

While developing their new SQL Source Control tool, a team at Red Gate learned a lot about Agile development, as well as the benefits (and challenges) of rapid, regular Early Access releases. Stephanie Herr (project manager for SQL Source Control) and Mark Wightman... Read more...

Improving Comparison Operators and Window Functions

by Joe Celko | 01 April 2011 |

9 comments |

It is dangerous to assume that your data is sound. SQL already has intrinsic ways to cope with missing, or unknown data in its comparison predicate operators, or Theta operators. Can SQL be more effective in the way it deals with data quality? Joe Celko describes... Read more...

The default trace in SQL Server - the power of performance and security auditing

by Feodor Georgiev | 14 March 2011 |

21 comments |

Since the introduction of SQL Server 2005, there is a simple lightweight trace that is left running by default on every SQL Server. This provides some very valuable information for the DBA about the running server, but it isn't well-documented. Feodor reveals many... Read more...

Lipoaspiration in your SQL Server Database

by Fabiano Amorim | 03 March 2011 |

4 comments |

Once, when disk space was at a premium, DBAs fought hard to keep the size of their database down. Now there seems less motivation to 'fight the flab' of a database. Fabiano Amorim was watching television recently when the subject matter, cosmetic surgery, gave him... Read more...

The EXCEPT and INTERSECT Operators in SQL Server

by Robert Sheldon | 28 February 2011 |

10 comments |

The UNION, EXCEPT and INTERSECT operators of SQL enable you to combine more than one SELECT statement to form a single result set. The UNION operator returns all rows. The INTERSECT operator returns all rows that are in both result sets. The EXCEPT operator... Read more...

Collecting Performance Data into a SQL Server Table

by Feodor Georgiev | 20 January 2011 |

16 comments |

Occasionally, when tracking down a performance problem, you have to have information over time on the values of particular sets of performance counters. To track down the more insidious or sporadic problem, it is best to have the data in a SQL Server table, so you... Read more...

Which of Your Stored Procedures are Using the Most Resources?

by Greg Larsen | 11 January 2011 |

14 comments |

Dynamic Management Views and Functions aren't always easy to understand. However, they are the easiest way of finding out which of your stored procedures are using up the most resources. Greg takes the time to explain how and why these DMVs and DMFs get their... Read more...

Index Selection and the Query Optimizer

by Benjamin Nevarez | 25 November 2010 |

3 comments |

While we all know that the Query Optimizer uses Indexes to produce better execution plans, we don't all know exactly which indexes will give the best results. Benjamin Nevarez has done the research, and in this sample from his upcoming book, "Inside the SQL Server... Read more...

SQL Server Statistics: Problems and Solutions

by Holger Schmeling | 27 October 2010 |

14 comments |

SQL Server Statistics assist the query optimiser to calculate the best way of running the query. Holger describes every common way that things can go wrong with statistics, and how to put matters right. Read more...

Queries, Damned Queries and Statistics

by Holger Schmeling | 14 October 2010 |

8 comments |

In a perfect world, we should just be able to write queries and leave the RDBMS to decide the perfect strategy to get the data. Until then we have to understand the Query Optimizer and the way it uses distribution statistics. Read more...

SSN Matching Speed Phreakery

by Kathi Kellenberger | 05 August 2010 |

4 comments |

On Ask.SQLServerCentral.com, a group of people interested in experimenting with heavily optimised SQL techniques try them out on a problem, using reasonbly large amounts of data. They aren't so interested in explaining the techniques, so Kathi continues on her... Read more...

Set-based Speed Phreakery: The FIFO Stock Inventory SQL Problem

by Kathi Kellenberger | 25 May 2010 |

8 comments |

The SQL Speed Freak Challenge is a no-holds-barred competition to find the fastest way in SQL Server to perform a real-life database task. It is the programming equivalent of drag racing, but without the commentary box. Kathi has stepped in to explain what... Read more...

Reliable Storage Systems for SQL Server

by Rod Colledge | 12 May 2010 |

2 comments |

By validating the IO path before commissioning the production database system, and performing ongoing validation through page checksums and DBCC checks, you can hopefully avoid data corruption altogether, or at least nip it in the bud. If corruption occurs, then you... Read more...

High Performance Storage Systems for SQL Server

by Rod Colledge | 29 April 2010 |

3 comments |

Rod Colledge turns his pessimistic mindset to storage systems, and describes the best way to configure the storage systems of SQL Servers for both performance and reliability. Even Rod gets a glint in his eye when he then goes on to describe the dazzling speed of... Read more...

Designing Efficient SQL: A Visual Approach

by Jonathan Lewis | 25 February 2010 |

4 comments |

Sometimes, it is a great idea to push away the keyboard when tackling the problems of an ill-performing, complex, query, and take up pencil and paper instead. By drawing a diagram to show of all the tables involved, the joins, the volume of data involved, and the... Read more...

Writing Efficient SQL: Set-Based Speed Phreakery

by Kathi Kellenberger | 04 February 2010 |

47 comments |

Phil Factor's SQL Speed Phreak challenge is an event where coders battle to produce the fastest code to solve a common reporting problem on large data sets. It isn't that easy on the spectators, since the programmers don't score extra points for commenting their... Read more...

Simple Query tuning with STATISTICS IO and Execution plans

by Josef Richberg | 04 February 2010 |

9 comments |

A great deal can be gleaned from the use of the STATISTICS IO and the execution plan, when you are checking that a query is performing properly. Josef Richberg, the current holder of the 'Exceptional DBA' award, explains how an apparently draconian IT policy turns... Read more...

Controlling Execution Plans with Hints

by Grant Fritchey | 12 November 2009 |

9 comments |

The Query Optimizer gets it right most of the time, but occasionally it chooses a plan that isn't the best possible. You can give the Query Optimiser a better idea by using Table, Join and Query hints. These come with a risk: Any choices you force on the Optimizer... Read more...

Reducing I/O with the 'Missing Indexes' DMVs

by Laerte Junior | 09 October 2009 |

20 comments |

Laerte recently experienced an I/O nightmare, which, as a happy accident, gave him an opportunity to test out the ‘Missing Indexes’ DMVs and see if they were up to scratch. He solved his problems, ran a few tests to get a feel for how well the DMVs performed, and... Read more...

Mastering SQL Server Profiler - eBook Download

by Brad McGehee | 07 October 2009 |

7 comments |

Download Brad McGehee's "from the ground up" guide to SQL Server's most powerful performance diagnostic tool. Read more...

SQL Server Execution Plans - eBook Download

by Grant Fritchey | 07 October 2009 |

12 comments |

Why is my query running slow? Why isn't my index getting used? In order to answer these questions, you have to ask the same return question in each case: have you looked at the execution plan? Grant Fritchey provides the only dedicated and detailed guide to this... Read more...

Finding the causes of poor performance in SQL Server, Part 2

by Gail Shaw | 03 April 2009 |

31 comments |

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... Read more...

Finding the Causes of Poor Performance in SQL Server, Part 1

by Gail Shaw | 09 March 2009 |

42 comments |

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... Read more...

Understanding More Complex Query Plans

by Grant Fritchey | 26 February 2009 |

4 comments |

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... Read more...

How to Identify Slow Running Queries with SQL Profiler

by Brad McGehee | 03 February 2009 |

7 comments |

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... Read more...

Graphical Execution Plans for Simple SQL Queries

by Grant Fritchey | 16 December 2008 |

15 comments |

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... Read more...

Investigating SQL Server 2008 Wait Events with XEVENTS

by Mario Broodbakker | 24 July 2008 |

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... Read more...

Execution Plan Basics

by Grant Fritchey | 11 May 2008 |

37 comments |

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... Read more...

SQL Server Performance Crib Sheet

by Grant Fritchey | 31 May 2007 |

18 comments |

Grant Fritchey provides an eagle-eye view of effective tools, strategies and techniques for SQL Server performance measurement, tuning and testing. Read more...

Taking the Guesswork out of SQL Server Performance Profiling Part 2

by Mario Broodbakker | 18 May 2007 |

5 comments |

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... Read more...

SQL Server Wait Events: Taking the Guesswork out of Performance Profiling

by Mario Broodbakker | 22 March 2007 |

8 comments |

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... Read more...

SQL Server Performance Testing

by Grant Fritchey | 15 May 2006 |

13 comments |

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...