Performance

22 July 2016

SQL Server Statistics Basics

Distribution statistics are used by SQL Server's Query Optimiser to determine a good execution plan for your SQL query. You don't need to know about them to execute queries, but the better you understand them, the better you can optimise your queries and sort out performance problems. Robert Sheldon once more provides a simple guide.… Read more
16 March 2016

T-SQL Window Function Speed Phreakery: The FIFO Stock Inventory Problem

Sometimes, in the quest for raw SQL performance, you are forced to sacrifice legibility and maintainability of your code, unless you then document your code lavishly. Phil Factor's SQL Speed Phreak challenge produced some memorable code, but can SQL features introduced since then help to produce code that performs as well and is also easy to understand? Aunty Kathi investigates.… Read more
07 September 2015

How to Avoid Conditional JOINs in T-SQL

Relational databases go out of their way to execute SQL, however bad the crimes against Codd and relational theory within the query. The 'conditional join', can be executed but at great cost. As always, it is much better to sit back and restate the problem in a set-based way. The results can be rewarding.… Read more
14 August 2015

Basic SQL Server Performance Troubleshooting For Developers

The speed of a slow SQL Query can almost always be improved. In SQL Server, the query optimizer determines the best way of executing the query, based on the evidence it has. The same query can be executed in many different ways as the data size increases, new indexes become available, or as the data distribution changes. If the appropriate index doesn't exist or can't be used, then SQL Server shrugs and does the best it can. Tony Davis explains how to find out what a query needs to perform w… Read more
18 March 2015

Never Ignore a Sort Warning in SQL Server

It is always bad news if your SQL queries are having to use the SORT operator. It is worse news if you get a warning that sort operations are spilling onto TempDB. If you have a busy, slow TempDB, then the effect on performance can be awful. You should check your query plans to try to eliminate SORTs and never leave a SORT warning unheeded. Fabiano Amorim shows the range of ways of getting information on what is going on with a query that is doing a SORT and when requests are made for memory.… Read more
10 March 2015

Identifying and Solving Index Scan Problems

When you're developing database applications, it pays to check for index scans in the SQL Server query plan cache. Once you've identified the queries, what next? Dennes Torres gives some preliminary guidelines on how to find out why these index scans are being chosen for these queries and how to make the queries run faster and more efficiently.… Read more
04 February 2015

The Importance of Caching

Performance tuning and optimization definitely have their place in minimizing SQL Server Licensing costs - by helping keep CPU utilization low. But it's important to remember that the fastest and most efficient query possible is the one that you never execute against your SQL Server. That might sound trite, but it's at the heart of caching - which is key to helping organizations save significant money on SQL Server licensing costs while simultaneously enabling better application performance and increased scalability. … Read more
12 January 2015

Why Developers Need to Understand Execution Plans

As a coder, the more you know about the business, the better code you're going to write. The more you know about the language in which you code regularly, the better code you're going to write. Similarly, if you're tasked with writing code against a relational database engine, you're going to need to know more about that engine. If you can examine and understand execution plans, you can achieve better understanding of the database system and you will write better database code. Grant Fritchey shows you how.… Read more
28 April 2014

The Performance of Traversing a SQL Hierarchy

Dwain Camps show that, depending on the size and characteristics of some hierarchical data, six different methods of traversal can each be the fastest at some point. He illustrates convincingly that It is dangerous to generalize from just one set of test data, and it is foolish to assume that, just because SQL code looks neat, it will perform well.… Read more
10 March 2014

Applying The Scientific Method to SQL Server Performance Tuning

Database administrators tend to rely more on science than intuition, and may therefore seem exasperatingly cautious to other IT people. A relational database is a complex machine, however, that is asked to deal with intricate problems. When things go wrong, it usually takes knowledge, deduction and a systematic approach to track down the root core of problems and to determine the best fix.… Read more
16 January 2014

SQL Server Deadlocks by Example

When a SQL Server instance deadlocks, it can be anything from minor irritation to something far more severe. In this article, Gail Shaw looks at how you can identify common types of deadlock, the difference between a deadlock and severe blocking, and how to avoid and fix the most common deadlock types.… Read more
15 October 2013

The ETL from Hell – Diagnosing Batch System Performance Issues

Too often, the batch systems that underlie a lot of database processing just grow without conscious design. When runs start to extend beyond their allotted time, and tuning no longer solves the problem, it is often discovered that batches are run in series, with draconian error handling. It is time to impose some rational design, and Nigel is a seasoned healer of batch processes.… Read more
19 April 2013

Introduction to SQL Server Filtered Indexes

SQL Server filtered indexes can save space and improve performance if they are used properly. Under what circumstances can they be used? When are they most effective, and what sort of performance gain or space-saving is likely? How does a filtered index affect the choice of execution plan? Seth explores these questions with practical experiments… Read more
03 April 2013

Managing SQL Server Statistics

Accurate statistics about the data held in tables are used to provide the best execution strategy for SQL queries. but if the statistics don't accurately reflect the current contents of the table you'll get a poorly-performing query. How do you find out if statistics are correct, and what can you do if the automatic update of statistics isn't right for the way a table is used? … Read more
05 March 2013

Join Reordering and Bushy Plans

Normally, the query Optimiser won't consider 'bushy' plans, where both operands to a join operator could be intermediate results from other joins. This means that it won't produce good query plans for some types of query. Hints, by themselves won't do it. More powerful magic is required.… Read more

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up

See what's happening behind the scenes

Take a peek at the bowels of the ship – the lower decks – the actual servers of SQL Server Central itself.

See what's happening