04 February 2015
04 February 2015

The Promise – and the Pitfalls – of In-Memory OLTP

When SQL Server 2014 was released, it included Hekaton, Microsoft's much talked about memory-optimized engine that brings In-Memory OLTP into play. With memory-optimized tables 30 times faster than disk-based tables, higher performance is promised - but at what cost? Jonathan Watts looks at the features that have improved, and those that need careful consideration.… Read more
04 February 2015
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
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
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
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
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
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
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
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
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
and 11 October 2012
and 11 October 2012

Tune Your Indexing Strategy with SQL Server DMVs

SQL Server Indexes need to be effective. It is wrong to have too few or too many. The ones you create must ensure that the workload reads the data quickly with a minimum of I/O. As well as a sound knowledge of the way that relational databases work, it helps to be familiar with the Dynamic Management Objects that are there to assist with your indexing strategy.… Read more