After a few months, we're pretty happy with the content and readership we have created. This is a great way to work more closely with our customers and to give them guidance that helps them be effective using our software. One thing we have realized...

I've been busy reading the SQL Customer Team's blog. Lots of good data on how to build a good application in there - I recommend it: http://blogs.msdn.com/sqlcat/ Thanks, Conor Cunningham

(2006-09-01 added a paragraph on parallel query plans) In SQL Server, “Statistics Profile” is a mode in which a query is run where you can see the number of invocations of each physical plan operator in the tree. Instead of running a query and just...

A quick post for today. It's actually more of a storage engine concept, but you'd be surprised how much this can help in understanding update plans. So, as I'm re-reading some of these to refresh my memory, I'll give you the chance to read along, so to...

If you read the Books Online page describing the UPDATE STATISTICS command, you will see that there are some undocumented options. UPDATE STATISTICS table | view [ { { index | statistics_name } | ( { index |statistics_name } [ ,...n ] ) } ] [ WITH...

Not everyone knows that query level hints (like loop join) will impact the entirety of a DML query plan. This includes foreign key validation and indexed view maintenance. Let us look at an example with two tables involved in a foreign key constraint...

A question we are frequently asked is what happens when an update statement assigns a column to its same current value. For example, use tempdb go create table t(i int, cc as i + 1) create index t_cc on t(cc) go insert into...

We've been hard at work up here on a little prototype for you guys to try. This attachment is a basic automatic indexing solution built on top of SQL Server 2005 using the Missing Index DMVs that we discussed in our previous blog post: http://blogs...

A question came in about when to use the FAST hint. If you remember from the row goals post ( http://blogs.msdn.com/controlpanel/blogs/posteditor.aspx?SelectedNavItem=Posts&sectionid=6255&postid=564912 ), it is possible to get a different plan...

SQL is a declarative language that returns multi-sets (sets allowing duplicates) of rows. The operations exposed in SQL, such as join, filter, group by, and project, do not inherently have any ordering guarantees. ANSI SQL does expose an ORDER BY clause...

Today's topic is a general primer on something that is called "local-global aggregation". Effectively, this is a technique to allow you to take a query containing joins and group bys and perform the group by partially "before" the joins. This can dramatically...

I ran into one of my colleagues in the hallway the other day from another part of the product, and he told me about the blog that the Programmability team has been doing. http://blogs.msdn.com/sqlprogrammability/ Effectively this is the compliment...

The Optimizer model makes several assumptions when making plan choice decisions. These decisions can be false for particular queries and data sets, and sometimes this can cause plan issues. One such problem is called “The Sorted Seek Problem” by the Optimizer...

I'd like to point you to a white paper that Eric, one of our program managers, did on Indexed Views in SQL 2005. This logic mostly applies to the Enterprise Edition of our product. http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx...

I saw a post in one of the newsgroups today that referenced a piece of information Microsoft published on how the Optimizer makes decisions about remoting certain operations. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql...

There are many cases where the database administrator does not control the queries being submitted of the system. As a result, the physical database design is often not tuned as well as it could be. In a number of actual customer cases where we investigated...

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...

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...

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...

(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...

(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...

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...

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...

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...