Kevin Kline : SQL Programming

Help Me Update the History of SQL Server

KKline — Wed, 12 Sep 2012 15:01:00 GMT

Find out the early history and evolution of Microsoft SQL Server in this engaging article from database expert Kevin Kline....(read more)

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)

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)

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)

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)

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)

Upcoming Seminar and SQL Saturday in Florida, Oct 13-17

KKline — Sun, 04 Oct 2009 21:24:00 GMT

Please join me for SQL Saturday #21 coming up on Saturday, October 17th. There are over 50 sessions on tap with great speakers from the area and across the nation. Some of the notable speakers include: Andy Leonard (blog here ) Andy Warren (blog here...(read more)

Bitemporal Data

KKline — Tue, 04 Aug 2009 14:12:00 GMT

Any IT pro with more than a year or two of experience will have faced the challenges of version control for an application, but what if you have to implement version control for data? The most common way to tackle this problem is implementing something...(read more)

Why Do I Keep Seeing This Mistake?

KKline — Fri, 10 Jul 2009 17:31:00 GMT

One of the fundamentals of loop optimization is that you should move stable operations outside of the loop. What I want to know is - if this is such a fundamental rule, why do so many people break it?!? If you are familiar with other programming languages,...(read more)

Looking for Good DMV/Database Admin Queries!

KKline — Tue, 07 Apr 2009 21:19:00 GMT

I like to collect useful database administration queries that leverage the SQL Server 2005 and 2008 DMVs. Heck, I'm still interested in SQL Server 2000 queries too. I thought I'd make my search public so that a) you can share your favorite queries here or great reference queries written by others and publicly posted on the Internet, and b) everyone can benefit from this collaborative approach to DMV queries. If you're aware of collections of scripts, for example like those available from the SQLCAT team, please post the location of the collections or libraries.

The intent is to provide ourselves with a set of scripts they can use to perform tasks that would otherwise require them to hit BOL heavily to research what DMVs or system catalog views they need to access to get what they want. These types of activities are not easily performed from within the query tool user interface.

I’m requesting everyone to post or reference your favorite queries in any of the following categories below. The queries could be in your notes, from web sites like MSDN or TechNet or SQLServerPedia.com, from our great SQL Server bloggers and MVPs. (Be sure to give credit to the originator when you post it here.) Speaking of favorite scripts, you might want to check out SQLServerPedia.com, if you haven't already done so. The wiki is getting quite large and there's lots of new information popping up daily. Looking for more good query samples? If you didn't already know it, be sure to check the Samples folder in your SQL Server installation. Microsoft has a lot of examples in their SQL Server 2005 Script Library.

When posting, please:

· Describe briefly what the snippet does

· Describe if this is a 2005/2008 query or just 2000

· Provide the SQL / Script and indicate if there are any parameters or if the SQL can be run without modification

Here are some categories I'm looking for, but if you have something not addressed here, please post it:

· Object Sizes – a list of objects in a database with their sizes

· Missing Indexes

· Index Utilization – all indexes

· Index Utilization - on a specific table

· Index Fragmentation – all indexes

· Index Fragmentation – on a specific table

· Index Defrag options – various with defrag, rebuild, online, offline, heap, etc.

· SQL Performance – leveraging the 2005+ DMVs for worst performers, active statements

· CPU and Optimization

· Buffer Cache

· Wait Stats

· Deadlocks

· Plan Guide Queries

· SQL Trace

· Backup History – or other backup related queries

Thanks in advance!

-Kevin

An Interview with Denis Gobo

KKline — Wed, 25 Feb 2009 03:28:00 GMT

I had the pleasure of speaking with SQL Server MVP, Denis Gobo, about my newest book, SQL in a Nutshell 3rd Edition. In the interview, we discuss the state of database industry, ANSI SQL, and other related questions.

Check out Denis' interview (and his fine blog) at http://tinyurl.com/bs5wea.

Thanks,

-Kevin

LINQ: Enabling or Entangling?

KKline — Tue, 23 Dec 2008 19:49:00 GMT

There’s been a lot of positive press for LINQ, such as the article about LINQ by Mike Otey at http://www.sqlmag.com/Article/ArticleID/48759/sql_server_48759.html. You can also find lots of glowing reviews and info about LINQ by Troy Magennis at http://blog.aspiring-technology.com/.

I've been trying to figure out exactly how I feel about LINQ, along with several other developer-oriented technologies that Microsoft has launched over the years, such as CLR. Ambivalence is the emotion that bubbles to the top most frequently. It's pretty obvious to me that Transact-SQL is the red-headed step child within Microsoft's overall ranking of languages.

One of the big problems I have with Microsoft’s approach is that it’s too tactical. Every 2-3 years, Microsoft launches TNBT (“the next big thing”). TNBT will make our code better, our developers faster, our applications more efficient, walk your dog, wash your cat, tie your shoelaces, end world hunger, and otherwise make everything better. The only problem is that TNBT is usually put together by one subteam of a major group, such as the languages group inside of the SQL Server development team. These small teams, while brilliant, often don’t get the top-down support to institute a major sweeping change to how things work. Consequently, we get a feature set that, while useful, doesn’t give us everything we need to sweep out the old and introduce the new.

This sort of 50% solution manifests itself in a lot of different ways, usually by making some aspects of the development process better and other aspects worse. My friend and fellow SQL Server MVP Andrew Kelly has an interesting blog post and subsequent comments at http://sqlblog.com/blogs/andrew_kelly/archive/2007/09/06/linq-to-the-rescue.aspx which strongly illustrates this idea. Basically, (most) developers still don’t understand the basics of building an efficient database solution. The thing that most improves database applications is proper planning and design. But tools like LINQ and the entity framework most obviously help developers speed up development process, in effect encouraging even less planning and design than ever before. A recipe for disaster? Almost certainly.

I hate to think that the language that I’m best at is the most likely to lose overall support. On the other hand, I’d love to get in on TNBT (“the next big thing”) while it’s still in its genesis. However, figuring out what, exactly, they are planning to replace Transact-SQL with is yet to be seen since each new offering is, IMO, sadly lacking in value.

As an administrator, I’m putting a lot of eggs into the PowerShell basket – the first major scripting/programming language in many years that I’m taking the time to get really good at. However, I still don’t think we’ve hit TNBT in development languages and we probably won’t until Microsoft takes the time to convene a high-powered team composed of members from both the SQL Server and Visual Studio organizations.

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