back to t-sql basics (intro)

18 Nov

At the SEMSSUG meeting a couple months ago I was one of the early prize winners and selected the book Microsoft SQL Server 2008 T-SQL Fundamentals by Itzik Ben-Gan.
T-SQL Fundamentals
It is on the top 10 books lists for many DBAs. I’ve been a DBA for a fortune 500 company over 2 years now but do not get much practice in T-SQL. A large percentage of the almost 100 SQL servers I maintain run ISV applications. This doesn’t remove the need for me to learn database design and T-SQL it just removes a lot of the practice and experience I would get if we instead hosted more in-house applications. I tend to stay on the server side of SQL. I don’t get my hands dirty writing a lot of queries, designing table layouts or even adding or removing indexes. What I do is roll out new servers and balance a semi-consolidated virtual environment.

When things start to go poorly I examine the hardware, virtual, OS and SQL server layers to see if there are any tweaks or upgrades that would be justified. In the past I have stuck to the big four resources CPU/MEM/DISK/NETWORK to identify which may be the one that is most heavily used and causing a bottleneck. I like to couple this information with the top SQL and SQL waits during the timeframe that things started to go poorly. Often times this information gets packed up and shipped off to the software vendor where it enters some kind of black hole.

Learning T-SQL and database design will help me get to the root of the 5th resource bottleneck, blocking. Blocking, along with stupidly bad RBAR has been the root cause for a lot of performance problems that users actually complain about. “My report took 2 seconds yesterday but 10 minutes today, what gives?” Users tend to complain when things are 1.5x or more slower than expected. On the flip side they will only give a compliment if the software is no less than 100x faster than it used to be.

Sometimes it is a physical resource problem. Its hard to estimate how much hardware a 3rd party app is going to need because of that 5th resource bottleneck. Sometimes we choose accurately up front but then the user base will grow and queries can’t get in and out fast enough. Blocking would be evident in most of these cases but a simple hardware upgrade could reduce blocking by getting queries in and out faster.

The purpose for me putting a lot of hours recently into T-SQL and database design is because of that black hole where the “code vs. servers” support tickets tend to go. Of course I want to be a more well rounded DBA but the new job requirement has gotten be motivated. I would like to recover some of these tickets that enter the black hole by giving valid solutions to more of the top (or bad) SQL statements that lock servers and chew up shared resources. I would not need to defend my big 4 resource choices if I could identify and offer solutions to the poorly performing queries.

I have been thoroughly reading, re-reading, taking notes and doing the exercises in Mr. Ben-Gan’s book. I have made it through the first 8 chapers and will post some of my notes in the near future. Most of the examples are based around a small database that can be downloaded with all scripts here:

Leave a comment

Posted by on November 18, 2011 in SQL Dev


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: