SSMS 2008 has built-in activity and Performance Reports most people never open. Right click the server, go to Reports, Standard Reports, and pick one.
SQL SERVER – MAXDOP Settings to Limit Query to Run on Specific CPU
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.