25 March 2011
Layer 2 of The Layer Model is the logical layer.
You could also use the OPTION (LUDICROUS SPEED) query hint
The logical layer represents the configuration of your indexes, statistics, and the queries that are run on your system. If you tune your indexes and your worst queries, you will get better performance. The best way I know of to do this is to use DMVs and logging.
Before we start, however, it's important to remember that you'll never run out of ways to tune performance. It is important to have goals before doing performance tuning. Know when to stop and declare victory. The 80/20 rule works really well here.
How do you do index tuning? You ESCape! Eliminate. Swap. Create.
There have been many, many blog posts on index tuning. My favorites are below. These links also have pointers to a lot of additional content. Brent Ozar's video on index tuning SQLServerPedia's best practices on creating tuning. Kimberly Tripp's posts on spring cleaning for your indexes. SQLServerPedia's list of index-related DMV queries.
Query tuning can tricky. I have found that having the right data before you start makes everything easier. So, first, collect all of the query information you can get. Get lists of queries that have run, with details on CPU time, execution counts, and missing indexes or missing stats mentioned in the query plan. The sys.dm_exec_query_stats DMV is great for this.
From there, I would suggest you: - Identify long-running queries - Identify queries that run constantly - Identify blocking/deadlocking queries - Document everything. Excel is handy for this sort of thing.
Now you have a big list of queries, along with details of their usage. The next step is to prioritize what to work on. I like to prioritize query optimization based on user pain, error counts, and then system stability.
The Good: It's easy to find & eliminate bottlenecks one by one, using fairly simple query tuning tricks and the DMVs built into SQL Server.
The Bad: Logical-layer tuning requires modest development time, and it can be a little risky. Changing indexes can slow your system down further. Query tuning can return incorrect results to users, or slow down the queries even more.
The Ugly: That web application that runs 10 million SELECT queries per minute against your database? It's still there. That reporting sproc that does a 25-way join with GROUP BY? It can only be tuned so much. And no amount of tuning will make that 50TB database run fast on your Pentium Pro-based server with 2GB of RAM.