The Temp Tables For Destruction counter stays flat at zero while Temp Tables Creation Rate moves, and that confuses people. This post explains what each counter counts, reads both from T-SQL, measures a creation loop, and shows that a cached temp table in a procedure moves neither counter.
Remove Bookmark Lookup – SQL in Sixty Seconds #089
We fixed the query performance by removing the bookmark lookup. Let us learn about that in today’s blog post, how we can fix this issue.
Identify Index Used for Query – SQL in Sixty Seconds #088
How do you identify index used for query? It is a popular question in my health checks, and this SQL in Sixty Seconds video shows the easy way.
Query Percentage Complete: Watch a Long Query Finish
Query percentage complete is not a column in any view, but sys.dm_exec_query_profiles reports how many rows each operator has produced against the plan’s estimate. This post builds a monitor query for a running statement, explains why a percentage can pass 100 or even run into the thousands, and shows which operators to trust.
Multiple Aggregates in One Query: Read the Table Once
Multiple aggregates in one query read a table once, where separate subqueries read it again and again. This post measures the logical reads of two subqueries against one SELECT, uses CASE inside SUM for different filters, and shows two cases where the rewrite gains nothing or loses to an index.
Kill a Running Shrink: Is It Safe to Stop DBCC SHRINKFILE?
You can kill a running shrink with KILL and the database stays safe. This post starts a shrink on a test database, stops it from a second window, checks the data and the file afterwards, and shows that the shrink itself, not the kill, is what fragments the table.
Scalar Function Statistics With sys.dm_exec_function_stats
Read dm_exec_function_stats to rank scalar UDF activity, inspect callers, verify collection settings, and recognize inlined work.