Kevin Kline : SQL Programming, Tips

Dev Advice: Make a Tiny Dev Database Act Like a HUGE Prod Database

KKline — Fri, 16 Mar 2012 14:30:00 GMT

If you're struggling with doing development on a big SQL Server database, learn the ropes on cloned databases asap! ...(read more)

Database Maintenance Scripting Done Right

KKline — Fri, 18 Feb 2011 13:16:00 GMT

I first wrote about useful database maintenance scripts on my SQLBlog account way back in 2008. Hmmm - now that I think about it, I first wrote about my own useful database maintenance scripts in a journal called SQL Server Professional back in the mid-1990's...(read more)

2009: The Year in List Form

KKline — Tue, 05 Jan 2010 18:53:00 GMT

Before I jump onto the Goals and Themeword meme started by my buddy, Thomas LaRock ( blog | twitter ), I decided I'd spend a few minutes looking back on both the year 2009. (From a personal standpoint, the 00's were my most difficult decade yet. Major...(read more)

Better Ways to Get Transaction Log Information

KKline — Wed, 05 Nov 2008 19:12:00 GMT

If you've been around a while, you tend to do things the way you first learned how to do them. This can turn you into an "old timer" whose oblivious to new and better ways to do things that have appeared in the newer releases of the technology.

Take measuring log space, for example. If I wanted to find out how much log space has been utilized, I would dash off a DBCC SQLPERF(LOGSPACE) statement. But SQLPERF(LOGSPACE) only shows used/free space and not much more. I might use DBCC LOGINFO to see how the active log "moved across the ldf file", then examine the values columns such as "active" for more understanding. Or I could go even more old-school, and look at the PerfMon counters or perhaps at the PerfMon counters exposed through the system table sysperfinfo.

However, I want to thank Tibor Karaszi and Gert Drapers for this great tip. Simply use this query:

SELECT SUM([Log Record Length])

FROM ::fn_dblog(null, null)

WHERE...

The ::fn_dblog pseudotable provides a wonderful amount of information about the transaction log. Read more about this undocumented function at http://www.novicksoftware.com/udfOfWeek/Vol1/T-SQL-UDF-Volume-1-Number-27-fn_dblog.htm, or google '::fn_dblog' for lots of other sources of information about it.

Thanks,

-Kevin

Implementing A Hash Partition In SQL Server 2005

KKline — Mon, 21 Apr 2008 20:45:00 GMT

One of the best things about becoming a Microsoft MVP is meeting other MVPs. I bring this up because last week was the annual MVP Summit in Seattle, WA. I was really looking forward to meeting Steve Kass. Steve Kass is one of the smartest SQL Server MVPs I’ve encountered, especially when it comes to SQL questions. A while back, I noticed that Steve made an interesting recommendation for a hash function that you could use for partitioning that I thought was worth noting. A hash function would be very useful if you wanted to implement your own variation of a range partition using a hash function rather than the standard sort of range partitioning where colA values of A-H go to partition 1, values of I-P got to partition 2, and so forth.

Steve notes that you could use the following for hashing something small in size:

CAST(

SUBSTRING(

HASHBYTES('SHA1',

CAST(my_col AS NVARCHAR(appropriate_size))),8,1) AS tinyint)

This is just an off the cuff recommendation from Steve and might need some fine tuning, for example, the CAST might throw off persistence. However, it’s a good start.

Thanks, Steve, for sharing this and thanks, readers, for sharing any improvements you might develop out in the field.

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