If you look up Table Hints in Books Online, you’ll find the following statement: If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error. The interesting thing there is ...

This is not a post about Jim Gray, much as I wish I had some news to share. It's about one of the reasons you should be really careful about using undocumented trace flags. I always try to warn people, whether I'm writing or speaking, about the dangers inherent when using any undocumented feature. Undocumented means unsupported! Microsoft retains ...

Summary: More undocumented ways to explore how the query optimiser works. This post is part of a series: Part1 Part 2 Part3 Part4 Documented Rule-Affecting Options It might surprise you to learn that most T-SQL users have written queries that disable one or more of the optimiser rules already. Join hints { LOOP | HASH | MERGE | ...

At the Summit today I attended what was basically a Transaction Isolation Level boot-camp, with the ever-illuminating Kalen Delaney. If Isolation Levels were biceps, Locking Hints were pecs and transaction-related DMVs were six-pack abs, I have to say that Ms. Delaney whipped us all into amazing shape. In this, at least, I am now what the ...

Thank you everyone who attended my 24HOP session yesterday. It was a wonderful event to be part of. Here are a few of the questions that I was asked, that didn't get completely answered. Question: Can we set READ_COMMITTED_SNAPSHOT ON for just a table rather than at database level? I apologize that I answered this incorrectly.  I was ...

In the previous post, I mention that in an earlier post on IO cost structure, that the cost of a table scan was approximately 1 CPU-micro sec per page, plus approximately 0.05 us per row for just a SELECT COUNT(*) This was calibrated on a 2-way Xeon 5430 Quad-Core 2.66GHz. I should also add that the test tables have a clustered primary key, with ...

It isn’t news anymore that there is going to be big announcement during MS BI Conference next week. The signs of it show up in the session descriptions – like in this one “This session will describe and detail some important announcements that will be made during the BI conference”, and members of the product team dropped hints in their blogs – ...

Quad Core, 16Gb RAM, 1TB RAID, 1Gb Graphics, run dual 30' monitors see the video: http://www.dell.com/content/topics/topic.aspx/global/products/precn/topics/en/precision_concept_jumppage?c=us&l=en&s=gen

This post is part 2 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation. As mentioned in yesterday’s post, I have been working on Who ...

As we head into the US holiday season, I wanted to catch up on some things I've wanted to blog for some time.  Blogs Rolling First of all, it's good to see some blogging activity around MDS.  As is the usual case around the first release of a product, it takes some time for blog activity to get momentum. But, we're beginning to ...