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:
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.Permalink
Layer 1 of The Layer Model is the physical layer.
The physical layer is the hardware used to make your database run. If you add more/better hardware, you can get better performance. Just add a faster SAN array, more RAM, more CPU cores, etc. Hardware upgrades will improve your application’s performance. Of course, you need to know which hardware to add.
The best way to do this is to use the Waits and Queues method. It quickly identifies the bottlenecks in your system, so you can tune hardware in the places you need it most.
The main Waits and Queues whitepaper is here, written by Tom Davidson. In addition, I’ve found a few blog posts to be very helpful. Paul Randal (b / t) has a great post expanding on the different wait types, with some example queries to get you started. The other fantastic resource is Brent Ozar’s (b / t) series of blog posts on scaling up or scaling out your SQL Server setups.
I strongly urge you to read over the Waits and Queues whitepaper. It’ll take less than an hour. You’ll end up with a strong understanding of which parts of your hardware setup are working well, and which aren’t.
The Good: You don’t need to make code changes, risk big outages because of some fat-fingered LINQ statement, or have huge amounts of deployment risk. Modest hardware upgrades can be affordable.
The Bad: Eventually you need to start buying thousands of machines or really, really big servers. The cost of hardware, datacenter space, and HVAC will grow very quickly if you don’t do additional performance tuning.
The Ugly: That developer doing SELECT * FROM BIGTABLE (WITH HOLDLOCK, SERIALIZABLE, MAXDOP(1)) is still running his query after you upgrade.
No, that’s not the developer I meantPermalink