TSQL Tuesday #2

SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL
I've wracked my brain for some bit of puzzle that I could present as part of TSQL Tuesday #2¬†and I finally came up with a bit of something. When you're looking at an execution plan for a query, you know that this represents SQL Servers best attempt at a good execution plan. But, it may not represent the best possible plan. Or, it could be the only possible plan. The puzzle is, how do you know what you're looking at? Is this a trivial plan, meaning it's the only possible execution method for the query? Is this plan fully optimized, or did the optimizer go through it's prescribed cycles and simply take the best plan it had generated up to that point? These questions can be answered directly from information…
Read More