Filtered indexes and the new parameter sniffing option (spoiler alert it doesn’t help)

After writing a post on SQL Server’s new parameter sniffing option and how it helps performance of parameterized wildcard queries, I started to wonder if we can get similar benefits with filtered indexes. Filtered indexes come with the gotcha of requiring that the predicates of the index be hardcoded (non parameterized).  I can see the […]

Read More

Negative blocking session ids

On occasion while examining lock scenarios, I’ve seen a lead blocker with a negative session ID.  After looking in the documentation for the blocking_session_id, it explained why I am seeing this (taken directly from Microsoft’s sys.dm_exec_requests documentation): -2 = The blocking resource is owned by an orphaned distributed transaction. -3 = The blocking resource is […]

Read More