Earlier, when we talked about passing multiple values to a stored procedure, we had problems with delimiters. Some users had the pipe symbol (|) in their Location, and we probably want to stop them from doing that. Let’s say we wanna make a business rule that nobody’s Location column is allowed to hit the pipe….
Now that you understand how columnstore data is stored in row groups, column segments, and delta stores – that it’s kinda partitioning on steroids – let’s see how SQL Server uses this sliced-up, diced-up data to more rapidly find the rows you’re looking for. We’ll dig into the different reasons that columnstore indexes perform more…
Through the course of the, uh, course, we’ve been gradually layering on more ways that you can detect and prevent parameter sniffing performance problems. Now, it’s your final lab. No running workload this time: you’re going to take the query plans and parameter sets that you’ve been gathering so far, and actually get to work fixing…
There are 3 specialized causes for parameter sniffing: unparameterized queries flooding the plan cache, memory pressure due to query workspace grants, and plans aging out of the cache. Demo Script Transact-SQL /* Fundamentals of Parameter Sniffing What Triggers Parameter Sniffing Emergencies, Part 2 v1.2 – 2020-08-04 https://www.brentozar.com/go/snifffund This demo requires: * SQL Server 2016 or…
The biggest problem with parameter sniffing isn’t that SQL Server can produce different plans. The real problem is that the plans change unexpectedly, seemingly out of nowhere, with. no advanced warning. However, armed with an understanding of what triggers plans to be evicted from the cache, you can start to predict when these emergencies will…
