THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

James Luetkehoelter

Nearly any SQL topic presented at times in a slightly eclectic manner.

5 Things every DBA should know like the back of their hand...

..."hey, what's that spot on my hand?"

OK, I put in shameless plugs, but that got me thinking about topics that every DBA should really, really understand. I'm talking a Kalen level of understand (Geeks unite!).

I won't be explaining all of these things - take some initiative and hit Books Online and all of the other blogs and publications out there and really take the time to absorb it. If you've ever come one of my classes, you'll recognize these topics since rant about them so much.

1) BOL - If I have to tell you that means Books Online, you have a huge source of information close to you that you may not know about. There is a treasure trove of information, usually very easy to find. Personally I never use the search features of BOL but just start typing in the index - it's amazing how quickly you can get to what you want.

I do have a gripe for MS though - having it link online may seem like a nice feature, but I find that it just muddles the information includes more than is necessary. Finding something specific on can be a nightmare. PLEASE do not turn BOL into the same thing. 

2) Locking behaviour and Isolation Levels

The SQL engines (storage engine, query engine, the lock manager, etc) will always have oddities, but you better undertand exactly how locking works, and how each Isolation Level affects locking behaviour (can you name the basic 4 supported in SQL Server? How about the 2 new levels introduced in SQL 2005).

If you get the Isolation Levels and how locking performs, you may go into a panic thinking about how developers are connecting and what they are doing. Serializable mode from one incoming query can bring the database to it's knees.

3) Index Fragmentation - It never fails to amaze me how often I've met DBAs that don't know what DBCC SHOWCONTIG is for (or for 2005, sys.dm_db_index_physical_stats[sp?]). There are 4 distinct types of fragmentation that WILL occur for any OLTP database. It's just a matter of time before it occurs. Do you know the 4 different types? One is indirect and related more to autogrowing and then shrinking files...

4) The difference between RESTORE and RECOVER - When restoring a database, the default is automatically recover the database. What's the implication? Well, if you asked me to help restore and said "we got the full restore started for you" and left the defaults, guess what? I have to start over - I won't be able to apply differential or log backups...that does not make James happy.

5) The difference between a LOGIN and a USER - I hope everyone reading this has enjoyed moving a database from one server to another and experienced the results of using SQL authentication. And how to fix it. Also, for the record, I will not nor advise people to use third-party/freeware scripts to correct the problem on a production database - they access Master directly. I say let MS do that - I don't want to risk something I'm doing to a system table failing.

 That's it for now. I'll have more to rant about in the future - that's just me :)

Published Monday, September 17, 2007 3:45 PM by James Luetkehoelter
Filed under:



Adam Machanic said:

I can only think of three types of fragmentation: file-level, extent, and page.  What am I missing?

September 17, 2007 4:50 PM

James Luetkehoelter said:

Actual disk fragmentation - as database files (especially the log) "yo-yo" in size. Normally the server/storage admin takes care of it though. That's why it I said it was a sort of...

Then you have leaf layer - the worst for a clustered index

The non-leaf layer (unbalanced tree)

And extent fragmentation (in particular GAM vs SGAM extents being garbled)

September 17, 2007 5:28 PM

David said:

What are the 4 distinct types of fragmentation? I googled but didnt find anything..

September 17, 2007 5:32 PM

David said:

Never mind...

September 17, 2007 5:33 PM

Wanderer said:

What is the other isolation level added in SQL 2005?

snapshot and ?

where do the new isolation levels sit in the 'order' of:

Read Uncommitted

Read Committed

Repeatable REad


September 28, 2007 7:58 AM

James Luetkehoelter said:

Hi Wanderer,

Snapshot and Snapshot_Read_Committed are the two new isolation levels. In some way they don't fit into into the existing "order" of isolation levels - the exsiting are used in pessimistic locking, where as the two snapshot levels are optimistic locking. While they have basically the same name, I do consider them two different isolation levels in terms of their behaviour. Essentially they're a type of row-versioning - check out BOL for propellerhead and some practical info. The one thing - beware, TempDB is used extensively with both snapshot levels.

September 29, 2007 12:48 PM

Denis Gobo said:

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

December 27, 2007 4:11 PM
New Comments to this post are disabled

About James Luetkehoelter

I am passionate about what I do - which is DBA, development, IT and IT business consulting. If you don't know me, haven't met me or have never heard me speak, I'm a little on the eccentric side. One attendee recently described me as being "over the top". Yup, that about says it - because I only speak on topics that I'm passionate about.
Privacy Statement