Renaming/Moving Blog

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, however, is that we’d also like to get our query…

1

SQL Customer Advisory Team

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

0

How to Read Statistics Profile

(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 printing the output rows, this mode also collects and…

8

Locking Backgrounder

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 speak. For those interested in how and why…

0

UPDATE STATISTICS undocumented options

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         […

12

Hints for DML queries

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.   use tempdb go   create table department(deptid int primary key clustered,…

6

Non updating updates

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 t values(1) go   update t set i =…

6

Fun for the day – Automated Auto-Indexing!

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.msdn.com/queryoptteam/archive/2006/04/06/570176.aspx Effectively, this will periodically determine top index candidates for your workload. …

13

Row Goals revisited – FAST hint guidance

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 based on the row goal (for example, you can get a nested loops plan instead of a merge/hash join if you have a low enough row goal).  TOP…

1

Ordering Guarantees in SQL Server 2005

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 for the top-most SELECT scope in a query, and this can be used to…

2