SQL Server creates statistics on its own, but DBAs and the Tuning Advisor add more. Here is a script to drop all user Created Statistics safely.
Partition Elimination: Proving a Query Reads Only What It Needs
Prove partition elimination with the actual plan, test date predicates, and use $PARTITION to check which slice SQL Server targets.
Spreading Rows Into Hash Buckets With CHECKSUM and Modulo
Equal row counts do not mean equal work, and patterned keys can pile into one bucket. Here is how to test your buckets before adding workers.
Wait Stats Collection Script for Current SQL Server Versions
A wait stats collection script saves snapshots of sys.dm_os_wait_stats into a table, so you can compare two snapshots instead of reading totals since the last restart. This post builds the tables and a snapshot procedure, creates a small load, and reports the change with a category for each wait. It also covers idle waits, restarts and retention.
Auto-Created Statistics: What the _WA_Sys Names Tell You
Read auto-created statistics through sys.stats and sys.stats_columns, then use their columns as clues for better query tuning.
Measure Page Splits in SQL Server With T-SQL
To measure page splits in SQL Server, read the Page Splits/sec counter twice and subtract the first reading from the second. This post loads a table with sequential keys and another with random keys, so you can see which splits are normal and which cost you. It also names the index behind the splits and shows how a lower fill factor removes most of them.
SQL SERVER – How to Change Database Compatibility Level?
Compatibility Level controls selected query behavior within a database. I baseline the workload before changing it.