SQL Server Performance Office Hours Episode 46
- The missing index DMVs keep recommending the same index with 15+ included columns that would basically duplicate our clustered index width. The equality columns make sense, but I’m suspicious about blindly adding all those includes. How do you evaluate whether a wide covering index is actually better than a narrower index with key lookups, especially for an OLTP workload?
- I thought table variables were immune to parameter sniffing issues because they’re not parameterized, but I’m seeing wildly different performance for the same stored procedure depending on the data volume in a table variable we populate. The cardinality estimate is always 1 row regardless. Is there a way to get better estimates for table variables, or should I just rewrite everything to use temp tables?
- We’re getting 1205 deadlocks on SELECT queries that don’t modify data. The deadlock graphs show intra-query parallelism exchanges as the resources. These are reporting queries with MAXDOP 8 hitting a columnstore index. Why would read-only parallel queries deadlock with themselves, and what’s the best way to prevent this without just setting MAXDOP 1?
- I’m getting sort warnings in execution plans even though the memory grant is 10x larger than what the sort actually needs according to the plan properties. The ‘GrantedMemory’ is 500MB, the sort only uses 50MB, but I still see a sort warning. I’ve ruled out parallelism issues – this happens even with MAXDOP 1. What causes sort warnings when there’s obviously enough memory?
- I created a filtered index on WHERE StatusId = 2 for a common query pattern, and the index is way smaller than the full table index. But the optimizer still chooses the full table index with a residual predicate instead of using my filtered index. I’ve updated statistics, rebuilt the index, even added query hints – nothing works. What are the gotchas that prevent filtered indexes from being selected, even when they seem perfect for the query?
To ask your questions, head over here.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.