Local-Global Aggregation

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 reduce the number of rows that a query has to process to return…

5

T-SQL Procedural Blog

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 in the expressability story to what we do in queries.  So, there are a lot of details…

0

Sorted Seeks

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 Team, and it affects plan selection, usually between clustered index or heap scan plans…

2

Indexed Views in SQL Server 2005

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 This white paper focuses on the functionality in our product.  If you have questions or things that we can make clearer, please…

4

Optimizing Distributed Queries

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/odp_tun_1a_6oxf.asp It’s slightly out-of-date, and I’ll work on trying to get it updated.  Specifically, SQL 2005 will remote uniqueidentifiers (and it will support statistics over them as…

6

Why should I create an index?

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 performance issues with them, we’ve found that this can often…

5