Are Index Scans Bad? The answer No. Index Scans are Not Always Bad. This was in-depth discussed with my client during the Health Check.
Table Variables and Parallelism: Reads Yes, Inserts No
Table variables and parallelism: reads can run in parallel, inserts cannot. See the plan reason, the 1-row estimate at old levels, and when to use a temp table.
Group by Query Hash: Find One Query With Many Plans
Group by query hash to add up every cached copy of one query. See why the top query hides across entries in SQL Server.
Same Query Plan, Different Cache Entry in SQL Server
The same query plan can sit in the cache many times when query text changes by a space, a letter case or a comment. See it, measure it and learn how to share one entry.
Rowstore to Columnstore: Replace the Clustered Index
Rowstore to columnstore is a change of the clustered index, and one command works in only one case. This post builds two tables of 600,000 rows, shows the error from DROP_EXISTING on a primary key, and converts each table the working way. It compares the sizes before and after.
Read-Ahead Reads in STATISTICS IO: What They Tell You
Interpret read-ahead reads in STATISTICS IO, compare isolated cold scans and seeks, and separate prefetch from storage-wait evidence.
SQL SERVER – 3 Queries to Detect Memory Issues
One of the most popular conversations during Comprehensive Database Performance Health Check is about CPU and memory issues.