25 November 2010
25 November 2010

Index Selection and the Query Optimizer

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 Query Optimizer", he helps us to understand how the Query Optimizer selects indexes to improve execution plans.… Read more
05 August 2010
05 August 2010

SSN Matching Speed Phreakery

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 mission to explain the lessons learned and the tips that can be derived.… Read more
12 May 2010
12 May 2010

Reliable Storage Systems for SQL Server

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 have to take the right decisions fast to deal with it. Rod Colledge explains how a pessimistic mindset can be an advantag… Read more
25 February 2010
25 February 2010

Designing Efficient SQL: A Visual Approach

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 indexes, you'll see more easily the relative efficiency of the possible paths that your query could take through the tables.… Read more
04 February 2010
04 February 2010

Writing Efficient SQL: Set-Based Speed Phreakery

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 code. Mercifully, Kathi is on hand to explain some of the TSQL coding secrets that go to producing blistering performance. … Read more
04 February 2010
04 February 2010

Simple Query tuning with STATISTICS IO and Execution plans

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 out to be a useful ways of ensuring that Stored Procedures are carefully checked for performance before they are released… Read more
12 November 2009
12 November 2009

Controlling Execution Plans with Hints

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 by using hints can turn out to be entirely wrong as the database changes with the addition of data over time. Grant Fritchey, in a chapter from his highly acclaimed book, explains further. … Read more
03 April 2009
03 April 2009

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

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
09 March 2009
09 March 2009

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

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
26 February 2009
26 February 2009

Understanding More Complex Query Plans

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
03 February 2009
03 February 2009

How to Identify Slow Running Queries with SQL Profiler

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
24 July 2008
24 July 2008

Investigating SQL Server 2008 Wait Events with XEVENTS

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