Simple Database Tuning for SQL Server

Simple Database Tuning for SQL Server 2000:
When you Google for database slowness issues, you get plenty of hits to give you grey hair reading through all of them. After going through a number of them, and reading through some great SQL Server books, here is another addition to that lock of grey hairs!
Most of the articles that I have read online assume that you have taken care of the basic stuff, and now hunting to further fine tune your installation. But I am not going to focus on “fine” tuning; rather on the basics.

I agree that SQL Profiler is a very powerful tool, and I am a big fan of it myself. But before you go near it, you should have some basics covered. So here goes nothing:

1. Primary Keys are a must: You will be surprised as to how many “underground” installations are out there that don’t even follow this basic rule. Then these self taught DBAs run the SQL Tuning Advisor tool, which asks you to build a non-clustered index on a column which is supposed to be the primary key! So, please, make sure ALL your tables have primary keys. (FYI: A table that does not have a defined primary key is technically called a heap)

2. 80-20 Rule: 80% of the database problems lie with 20% of the tables! SQL Server is a very robust piece of software and will take abuse from bad design, lack of indexing etc up to a limit. Then, it just grinds to an agonizing halt. To spare it from this fate, run the following set of command:

DBCC UPDATEUSAGE('{your database name here}')

CREATE TABLE #DbTableSizes
(DbTableSizesId INT IDENTITY (1,1)
, TableName varchar(100)
, [Rows] BIGINT
, Reserved varchar(50)
, Data varchar(50)
, IndexSize varchar(50)
, Unused varchar(50))

INSERT INTO #DbTableSizes
EXEC sp_MSforeachtable @command1='EXEC sp_spaceused ''?'''

SELECT * FROM #DbTableSizes
ORDER BY [rows] DESC

DROP TABLE #DbTableSizes

This will give you a list of the tables that are most likely to be in the 20% that are causing problems. Those with the most number of rows are the ones to focus on. If you have tables with large amount of data (meaning large number of columns), but not really a huge number of rows, focus on those as well.

3. Now start targeting tables in this list, one by one. Make sure these have primary keys defined.

4. Using the following query, find out what stored procedures use these tables. You can also use the Action -> All tasks -> Display Dependencies menu option for this.
SELECT OBJECT_NAME(id) AS [Name]
FROM syscomments
WHERE [text] LIKE '%{table name here}%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

5. Now, from within each of the stored procedures and functions, get the queries that are using the table under review. You should create a list of all unique WHERE clauses used on this table.

6. For each of those WHERE clauses, look at the Query Plans, to check none of the following evils are present:
a. Table Scans
b. Clustered Index Scans
c. Parallelism, unless you know you have multiple CPU’s on your server
d. Bookmark Lookups, unless you know exactly why this is happening

7. Modify/Build indexes on the table so that you rid the query plans of the above evils.

This sounds like a very tedious process, and it is. But the benefits are pretty big. You will see a significant improvement in database performance.
If you want to take it a step further, try to build a script that will do all the data collection (Steps 1 through 6) for you and you can review the report periodically. (Maybe I will work on that as a side project)

Advanced Database Tuning for SQL Server 2000:
Once you have taken care of the basics, let us suppose you still find that the database performance is slow. I will point you to a very interesting methodology by Itzik Ben-Gan in the book, Inside SQL Server 2005: T-SQL Querying, chapter 3.
I have used most of the techniques in this chapter and they have helped me to dig much deeper into the slowness/high CPU usage issue that we experienced with one of our production installations.

Stocks and Employees

Just a random thought popped in my mind on how investing in stocks makes you a better employee. The Jim Cramer (http://www.thestreet.com ) style of Buy and Homework instead of Buy and Hold really drives the point through.

When you have money invested in a stock, you should be spending at least one hour per week doing home work on it. Which includes reading all articles published on that stock by management, analysts and media.

I think we should apply the same philosophy for the company that you are employed at. Spend at least an hour or so googling your company every week. Believe me, it will be worth while. Make sure you do it on your own time though.

So why does this make sense to me? Well, if you look at it, your money is invested in your employer in multiple forms. Your future pay checks, benefits, 401(K) etc. So when your livelihood is "invested" in your employer, it only makes sense you do your homework.

Look at your company in the same way you would look at any other that you own stock in.
- Who are the competitors?
- How is it doing?
- How is it doing against the competitors?
- What are the analysts saying?
- What is management saying?
- What is the media saying?

Here are a few additional questions to ask as an employee:
- Is your job aligned with the company strategy?
- Does your job have an impact on the stock price (or the bottom line for private firms)?
- Is management looking at your position/department as an important part of the company strategy?

Unless you are on top of your homework on this big position that you have in your life's portfolio, you leave yourself vulnerable to the bears!

Anticipation - Key to Success Part I

I am coming up with examples of why Anticipation is the key to success. In this multi-part series, I am trying to come up with analysis of various examples that I come across which emphasize the point that the better you anticipate, the more successful you get. Of course this sounds like a no-brainer but it is interesting to observe how this appears in our "regular" life!

1. Star Wars Episode I - Over the past few weeks, the Star War movies have come back to life on TV and so I was catching up on them. In this classic movie, Qui-Gon Jinn says the following about Anakin Skywalker's Podracing skills "He seems to be able to see into the future. That is why he is the only human who can Podrace." And this statement got me thinking! What the Jedi Knights posses is strong anticipation.

Of course we know that nobody can "see" the future. But the closest you can get to it is by anticipating based on the present situation and past experiences.

2. Cricket - When you first start playing as a batsman, you tend to start off with reacting to what the bowler is bowling at you. So you are in the reaction mode, and hence one step behind.

As your experience grows, you start paying attention to the grip of the bowler to try and anticipate the movement of the ball. This increases the amount of time you have to react to the ball.

But the cricket greats like Sir Don Bradman, Sachin Tendulkar and Brian Lara take it to the next level. They have already moved past the stage of paying attention to the grips and the other visible cues. They reach the ultimate stage where they can anticipate what the bowler is "thinking" and get it right most of the time. That is what propels them to greatness!

Coming soon are some observations from Stock Picking and American Football.

Technical Interview Questions List

I know that there is a plethora of websites/blogs etc. that will list technical interview questions with answers. My focus here is to keep a running list of questions as they pop up in my mind.


General Logic/Programming.

1. How would you reverse a string most economically (Space and Time)? How would you test this?

2. How would you program a Fibonacci Series/Factorial? Recursively and Non-recursively? What are the pros and cons of both approaches?

3. What is the difference between passing parameters by value and by reference to a method?

4. Do you know pointers? What they mean and represent?

5. What is an Interface in OO parlance?



.NET Specific.

1. Difference between Overrides and Overloads.

2. Difference between Overrides and Shadows. (This is not easy)

3. What is the difference between a Function and Sub?



SQL SERVER 2000 Specific.

1. What is the difference between the following SQL Statements:
a. SELECT @MyVar = Column1 FROM MyTable
b. SET @MyVar = (SELECT Column1 FROM MyTable)


Java Specific.



Others.

1. What is the most technically challenging project you have ever worked on?

2. What is the work that you are most proud of? Why?

3. What is your faviourite Dilbert Cartoon?

4. Why do you want to work here?

5. Why are you looking?

6. Which is your faviourite Programming Language? Why? Second faviourite?

7. Which is your faviourite Database? Why? Second faviourite?

8. What positive feedback did you recieve in your last review? What were some of the areas to improve?