Has your identity column ever jumped by 1000 after a restart? SQL Server 2017 fixes it with IDENTITY_CACHE. Here is a demo with the setting ON and OFF.
SQL SERVER – Performance Observation of TRIM Function
Does the new TRIM function slow your queries like other functions in a WHERE clause do? Here is my performance observation of TRIM function in SQL Server 2017.
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.
NOLOCK Without WITH: Table Hint or Table Alias?
NOLOCK without WITH and without parentheses is not a hint, because SQL Server reads it as a table alias. This post proves it with a lock test, shows why the compatibility level doesn’t change it, and gives a query that finds the bad syntax in your code. It ends with the safer ways to write the hint.
Optimize for Ad Hoc Workloads: When to Turn It On
Optimize for Ad Hoc Workloads shrinks the memory that one-time queries take in the plan cache. This post builds a demo of single-use plans, measures their share of the cache, and shows how to read the result. It also covers the cost of the setting and why parameterized queries are the better fix.
How Readable Secondary Replicas Build Their Own Statistics
Learn why readable secondary replicas create temporary statistics in tempdb, how to find them, and when the primary needs a permanent statistic.
Querying a Histogram With sys.dm_db_stats_histogram Instead of DBCC
Query sys.dm_db_stats_histogram to inspect statistics steps, compare stored estimates with data, and identify values beyond the last step.