The MAXDOP query hint limits how many CPUs a query can use, though not which ones. With OPTION (MAXDOP 1), the parallelism operator leaves the plan.
SQL SERVER – Checklist for Analyzing Slow-Running Queries
Before you call support, go through this Books Online checklist for analyzing slow-running queries: network, memory, statistics, indexes and more.
SQL SERVER – Force Index Scan on Table – Use No Index to Retrieve the Data – Query Hint
How do you force index scan instead of a seek, or read a table without using any index? Two reader questions with a similar answer, using a query hint.
SQL SERVER – Improve Performance by Reducing IO – Creating Covered Index
My T-SQL Tuesday #004 post on how a covered index can improve performance by reducing IO. Run the demo on a test server only, never on production.
SQL SERVER – Performance Comparison – INSERT TOP (N) INTO Table – Using Top with INSERT
A Performance Comparison of INSERT TOP (N) INTO a table versus INSERT with SELECT TOP (N). The first is clearly faster, but the two can give very different results.
SQL SERVER – Data and Page Compressions – Data Storage and IO Improvement
SQL Server 2008 data and page compressions cut disk space and I/O. I explain ROW and PAGE compression and how to set them on tables, indexes and partitions.
SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning
Plan recompilation hurts performance just like a first compile. Learn what causes it, from schema changes to SET options, and how to reduce recompilation.