Conor vs. “Does Join Order Matter?”

I got this question/claim again this week, so I’ll write a blog post on it while I wait for my build to complete: It usually goes something like “When I rewrite my query, SQL Server gives me a different plan.  Therefore, I should rewrite all my queries until I find the best plan for each…

4

Conor vs. FOREIGN KEY join elimination

I received a question from a friend in Brazil related to foreign keys and join elimination in SQL Server.  Yes, SQL Server can detect that some joins are not needed to return results to the user and skip them.  What is this evil magic, you ask?  Well, let’s walk through it before we get out…

4

Conor vs. Windows 7

(Not a SQL Server post) I have been meaning to install Windows 7, and I got around to doing it last night.  I usually run Windows 2003/8 Server for work, so my interest in the consumer side is perhaps not as high as some others.  Nevertheless, I like Windows and I wanted to try it…

0

Conor vs. UNIQUE in Index Definitions

I gave a talk to the Austin-area PASS group last night on B-Tree indexing in SQL Server, and I received a question about whether to make clustered indexes UNIQUE or not (assuming the data is unique on the key columns).  The expectation was that this should perform better when the index was UNIQUE. Let’s back…

1

Conor vs. Statement Offsets

As most of you have figured out by now, I work mostly on queries :).  One of the things that I often need to do is to get a statement out of a batch so that I can go look more closely at a query plan (and, in my case, the code that generates it). …

0

Server Cursors

My coworker Marc posted up a good introduction into the different server cursor models and you can see it here: http://blogs.msdn.com/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx

0

Conor vs. Stats NORECOMPUTE

It’s been a busy summer.  I got back from vacation and have been hard at work on new features for a future version of SQL Server.  You can always still send me questions about the existing product, of course, and I will answer them here (conorc (at) microsoft (dot) com).   Today is a question…

0

Conor vs. Dynamic SQL vs. Procedures vs. Plan Quality for Parameterized Queries

I received a question from a customer today about query plan choice for a parameterized query – specifically that different plans get picked based on where the plan is located, and sometimes one might be better than another.  Apparently, for their application, a particular query plan was “better” when compiled in dynamic sql instead of…

1

Conor vs. Recompiles, part 2

Thanks to those of you who replied to my previous post.  I spent the last week or so talking with customers, coworkers, and looking at various applications to see how recompiles are modeled today in their applications.  At a high level, SQL Server doesn’t really “require” you to recompile at all.  The main set of…

2

Conor vs. Recompiles

I have a question for you guys about how do you force a recompile for a single query today?   There are a lots of different ways that one could cause the system to recompile a query: a) sp_recompile <object used in a query> b)DBCC FREEPROCCACHE (with optional arguments to restrict the set of impacted…

2