SQL Tuning test environment

Setting up a stable and predictable test environment is crucial to any performance tuning and measurements.

Clear Caches:

• Execute Checkpoint to flush the dirty pages out into the IO sub-system
• Clear the plan cache using DBCC FREEPROCCACHE ( clears plan cache for all db instances in the box), you can clear for only a db with FLUSHPROCCACHEINDB(db_id) or even better just for that individual plan knowing the plan handle id.
• Run DBCC DROPCLEANBUFFERS - clears all data from memory -NEVER do this in PROD!

Set Measurements:

• SET Statistics time on - elapsed time and CPU time
• set statistics io on - logical reads (data from memory) and physical reads(from IO - slower)
• Use Graphics Execution plan or SET SHOWPLAN TEXT|XML

Use DMVs:

• System Info - sys.dm_os_wait_stats,  sys.dm_os_performance_counters
• Query Info - sys.dm_exec_requests
• Index Info - sys.dm_db_index_usage_stats

TIPS:

1) Look out for table scans and clustered index scans - not necessarily bad say if it is only 64Kb, that is eight 8Kb pages, but one has to assume that the table could be very large. Remember that the clustered index scan is the scan of the table in its physically sorted order!

2) Look out for lookups - bookmark(legacy), Key and RID lookups. We might benefit from a new index?

3) check for spools - means something is getting spooled out to tempdb - so a lot of IO is taking place.

4) Parallelism - may be good or bad for the plan with MAXDOP?

5) And here's one BIG tip - if the stats between the estimated and actual execution plans are wildly different, that means your stats are usually (not always) very outdated/stale - indexes have not been updated or rebuilt for a long time! It could also be incorrect because the optimiser has no information, like form a scalar UDF, table variable or multistatement TVF - assumes only 1 row coming out. Another cause could be due to parameter sniffing - so sniffing is a double edge sword.

6) If too many physically reads, usually a sign of lack of memory and you can throw a lot of memory to solve that problem!

7) Whenever there is Sorting , say in  hash joins or some kind of merge joins, it means Sql Server will use tempdb (physical io) a lot for the sorting operation,

8) and then there is Implicit Data Conversions - avoid them.

9) Note that Cursors are not always bad - in fact before sql 2012, they are the most efficient for running totals and say keeping a running balance - it will outperform self-joins! In 2012 they introduced windowing functions like LAG and LEAD that will outperform Cursors. A lot of DDL uses Cursors. Another good use case, is maintenance scripts - say calling a stored procedure for every database in some kind of loop, or sending an email from a list etc. There is no good way to do that in a set.

From Adam Machanic https://www.youtube.com/watch?v=GSZPvF2u6WY
The top 5 culprits are
Lookup
Sort
Spools
Hash
Nested Loops (serial)
and of course Table and Index scans

Also look at moving the fat arrows from left to right (logical interpretation) in the execution plan.
Then the optimizer can filter the rows earlier. That's another reason why Nested views are no good. The optimizer cannot filter the data using the where clause predicates. Replacing the views with an inline table valued function is the solution as the optimizer now knows how to build the correct estimated plan for the query.