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

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

Window Functions in SQL Server

When SQL Server introduced Window Functions in SQL Server 2005, it was done in a rather tentative way, with only a handful of functions being introduced. This was frustrating, as they remove the last excuse for cursor-based operations by providing aggregations over a partition of the result set, and imposing an ordered sequence over a partition. Now, with SQL Server 2012, we are soon to enjoy a full range of Window Functions. They are going to make for some much simpler SQL queries.… Read more

Complete Showplan Operators

Fabiano Amorim has taken the time to reallv drill into the behavior of a small set of execution plan operators in an effort to explain the optimizer's behavior. He's explored why things happen, how you can change them, positively or negatively, and he's done it all in an approachable style.… Read more

Statistics on Ascending Columns

It comes as rather a shock to find out that one of the commonest circumstances in an OLTP database, an ascending primary key with most querying on the latest records, can throw the judgement of the Query Optimiser to the extent that perfomance nose-dives. Fabiano once again puts on snorkel and goggles to explore the murky depths of execution plans to find out why. … Read more

Lipoaspiration in your SQL Server Database

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 the theme and inspiration for this guide to keeping your database fit and trim. … 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