A lot of developers I’ve worked with in the past don’t have good experience with SQL out of the box, when is say good i mean beyond knowing the basics. A lot of the systems I work on are high performance so I end up training developers in SQL as a priority when they come onto the team. There is a few basic things I always show them which gets them up to speed pretty easily.

Common Mistakes to Avoid

Backwards Conversions in WHERE clauses

A common mistake is backwards where clauses, this causes the engine to create a temporary table and convert all the data in that column in the table, you should always convert the parameter not the column.

WHERE CONVERT(int,column1) = @param1

Cursors, they aren’t bad, just use them correctly

If you are using cursors for doing transactional workloads I will be scared and probably not talk to you again. If you are using them simply to iterate through a temporary table or table var and do “something” you are probably using them correctly.

Just remember the use these two hints all the time READ_ONLY and FAST_FORWARD, they will give you a speed boost of an order of magnitude.

DECLARE authors_cursor CURSOR READ_ONLY FAST_FORWARD FOR

limit them to a few thousand rows if you can, don’t use them for millions of rows if you can avoid it.

INSERT is your friend, UPDATE is your enemy

Try to design your schema with INSERTing rather than UDPATing, you will mitigate contention this way, contention of resources is what makes you build big indexes and will slow you down in the long run.

Get some CTEs up ya

Common Table Expressions (CTEs) are useful for breaking sub-queries out, and I find are cleaner in the code, performance difference is arguable though.

Table Variables over Temp Tables

Yes, I’ve said it, now I may get flamed. Temp tables have their place, primarily when you need to index your content, but if you have temporary data that is large enough to require an index, maybe it should be a real table.

Also Table variables are easier debugging because you don’t have to drop them before F5ing again in SSMS.

Missing Indexes

Number one cause of query slow down is bad indexing. If you are doing large amounts of UPDATE/INSERT on your tables though, too much indexing can be bad too.

SSMS is your friend in this case, there are a lot more advanced tools out there sure, but you should be starting with SSMS, you’ll be able to find out your basic slow downs

Look at query plans

Hit this button in the tool bar and your away

If there is an obvious slow down SSMS will recommend you an index to fix your problem.

You can see the below Green text it displays sometimes (not all the time) you can right click on this and select “Missing Index Details…” and it will give you are CREATE INDEX statement that you can use to create your index.

Most of the index hints in here are pretty spot on but there is a few things to consider before going “yeah! here’s the index that will solve my problem”

1. Don’t index bit columns, or columns that have a small Cardinality
2. Look for covering indexes, what it suggests might be the same as an index you have already but with one extra column, which means you could use a single index for both jobs
3. Think about any high volume updates you have, you might slow down your updating if you add more indexes

The query plan itself will give you some more detailed info than the hints

Each block will be broken up by the percent of the entire statement (below is 1 block which is 13% of the entire statement), then within each block it breaks it up further, the below 3 Index Seeks use 12% of the total performance each.

When looking at the above, it can get very confusing what to do if you are not very familiar with SQL this interface gives you a lot of info when you mouse over each point, I think this is why some developers like to hide behind entity frameworks 🙂

The basic thing i tell people to look out for is the below:

Index Scans are usually your source of pain that you can fix, and when you get big ones SSMS will generally suggest indexes for you based on these. You will want to make sure they are consuming a fair chuck of the query before creating an index for them though, the above example of 2% is not a good one.

Maintain your Database

Your indexes will need defragmeting/rebuidling and you will need to reclaim space by backing up db and logs.

I won’t go into this too much in the scope of this post, I might do another post about it as its a rather large subject. I recommend googling this for recommendation but at least use the wizard in SSMS to setup a “default” maintenance plan job nightly, don’t leave your database un-maintained, that will slow it down in the long run.

People to watch and Learn from

Pinal Dave from SQL Authority is “the man”, he has come up on more of my google searches for SQL issues than stackoverflow.