March 2006 - Posts

• 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 Read More... Posted Friday, March 31, 2006 9:04 AM by QueryOptTeam | 9 Comments

• 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 Read More... Posted Thursday, March 30, 2006 8:20 AM by QueryOptTeam | 6 Comments

• 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 Read More... Posted Monday, March 27, 2006 10:25 AM by QueryOptTeam | 0 Comments

• 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 Read More... Posted Friday, March 24, 2006 2:55 PM by QueryOptTeam | 12 Comments

• 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 Read More... Posted Friday, March 24, 2006 9:52 AM by QueryOptTeam | 6 Comments

• 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 Read More... Posted Friday, March 24, 2006 9:08 AM by QueryOptTeam | 0 Comments

• 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 Read More... Posted Friday, March 24, 2006 9:03 AM by QueryOptTeam | 0 Comments

• 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 Read More... Posted Friday, March 24, 2006 8:54 AM by QueryOptTeam | 4 Comments