Kevin Kline : Internals, SQL Programming

How Do You SKU?

KKline — Wed, 30 Mar 2011 14:49:00 GMT

I’d like your opinion here. Follow my logic here for a moment as I walk through a couple rhetorical questions. Have you ever had a friend developed an application entirely on SQL Server Developer Edition? (Not that YOU would ever do such a thing, but...(read more)

Join Me May 19th for 24 Hours of PASS

KKline — Mon, 26 Apr 2010 14:16:00 GMT

Check out all the great SQL Server sessions you can attend for FREE in this on-line event....(read more)

NASTY RUMORS ABOUT MAXDOP

KKline — Tue, 15 Apr 2008 17:48:00 GMT

You'd think an article called "NASTY RUMORS ABOUT MAXDOP" would have something to do with Britney Spears or maybe Robert Downy Jr, but in that case it'd be total fiction (at least, it would be coming from my pen).

So, I was en route to the 2008 Microsoft MVP Summit yesterday and I had a chance to catch up on my reading. You may have heard some rumors that you should only ever set MAXDOP (maximum degrees of parallelism) to an even number. I can neither confirm nor deny these rumors since I haven’t had time to test this yet myself. But I’m curious if any readers out there have definitive information one way or the other.

Evidently, there are two roles involved in parallel processing, a writer and a consumer. Naturally, when two roles are at work, the MAXDOP setting doesn’t have to share resources when it is set in increments of two.

Hope this helps,

-Kevin

Beware of computed columns

KKline — Thu, 11 Oct 2007 04:20:00 GMT

If you use computed columns a lot, you might want to know about some new behavior in SQL Server 2005.

One of the changes in SQL Server 2005 was to evaluate expressions in queries to match computed column expressions and identify redundant expressions, in order to prevent run-time errors in queries that have unsafe expressions. This is documented in the behavior changes topic:

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

Check it out,

-Kev

Useful Index Information Scripts for SQL Server 2005

KKline — Fri, 31 Aug 2007 22:31:00 GMT

Italian SQL Server MVP Davide Mauri has released four very useful scripts that simplify the usage of SQL Server 2005 DMVs to discover, analyze, and understand index usage and health. The queries are not rocket science, but they are long and rely on DMVs that you may not have learned about yet. They also provide useful and easy shortcuts to common questions on SQL Server 2005. Check them out at:

http://www.davidemauri.it/SqlScripts.aspx

I now use them a lot. So I'm sure you'll find them useful too.

Great job, Davide, and thank you for sharing!

Cheers,

-Kev

Which DMV Shows Plan Handles?

KKline — Wed, 25 Jul 2007 21:02:00 GMT

Sometimes, knowing which Dynamic Management View (DMV) to use is your biggest challenge when attempting to figure out SQL Server 2005 behavior. For example, what if you want the cached execution plans in XML format, similar to the output of SET SHOWPLAN_XML? As it turns out, Gert Draper pointed out that there are a couple of DMVs that expose a plan_handle:

SELECT dm_exec_query_plan(plan_handle),
*
FROM sys.dm_exec_query_stats
WHERE plan_handle IS NOT NULL
GO

SELECT dm_exec_query_plan(plan_handle),
*
FROM sys.dm_exec_requests
WHERE plan_handle IS NOT NULL
GO

SELECT dm_exec_query_plan(plan_handle),
*
FROM sys.dm_exec_cached_plans
WHERE plan_handle IS NOT NULL
GO

Note that the plan_handle is a varbinary(64) data type. Cheers,

-Kevin