I Smell a Parameter!

Parameters are a useful way to improve overall system performance when there are many common queries with the same shape.  Instead of compiling each query, one plan can be used for all similar queries.  This can significantly reduce CPU overhead and improve throughput.  As long as the queries would have really returned the same plan,…

14

Row Goals in Action

Today, we’ll talk about row goals.  The optimizer in SQL Server has a feature that can bias plan choices to retrieve a certain number of rows quickly instead of the whole results.  This shows up in a few places, but the primary areas are in TOP N queries and in subqueries that need to check…

7

Migrating Cardinality Eestimation Posts from Previous Blog

Ian had a blog that contains a number of interestig tips/tricks, mostly about cardinality estimation during query optimization. I’ve chatted with him and will be migrating/expanding some content over to the team site. If you have specific requests from topics in his Blog that would interest you, post them up and I’ll find someone on…

0

TOP 100 Percent ORDER BY Considered Harmful.

(Updated 2006-27-03 9:00am Pacfiic Time – at the bottom) SQL is a declarative language.  That means that the language declares the form of the output but not the method used to generate those results.  There are cases, however, where the language is not quite rich enough to describe what customers want.  There are also cases where it is possible…

18

Using Computed Columns to Fix Scalar Expression Estimation Errors

(Ok, let’s try something a bit more involved now.  Here’s a tip on how you can use computed columns to improve query plan quality in SQL Server 2005.  The optimizer supports building statistics on the results of expressions, and this can avoid debugging query plans later if you make sure that the system is doing this for complex expressions, user-defined expressions, or other…

7

Query Recompilation Details

SQL Server contains self-tuning functionality that will recompile your query as the source data changes to find more efficient query plans for your current data.  This process of recompiling queries is a great, general-purpose feature to keep your system running well.  We have a white paper describing some best practices for managing query recompilation in your…

0

Statistics in SQL Server 2005 White Paper

Statistics is one of the most challenging areas of our product.  While we have automated a number of the common cases so that the average installation does not need to worry about the details of statistics, it is still important for an administrator to have resources in case they are working on a complex installation….

0

Greetings!

On behalf of the Query Optimization Team for Microsoft’s SQL Server product, welcome to our humble virtual abode.  We decided that we’d start a blog to help people better understand query plans, physical schema design in databases, making your application perform better, and anything else related to query optimization.  Furthermore, we’ll be posting tips and…

4