Very deep, clear session. Great presentation. Would definitely like to polish my skills in sql and may be opt for paid training at some point. What I liked most was the practical scenarios you explained. Awesome work. Thanks !!
HI Brent, if you do a select into temp table rather than get all the result into display in SSMS, then the execution time will be better distinguished between bad and good plans.
Great explanation. I wonder whether another alternative would be possible to the OPTION FOR VALUE, to minimize the implied future maintenance overload of the procedure, which is to have a procedure to be called at server startup or when updating stats that in turn makes a call to the problematic store procedures with the desired VALUE. The drawback would be that at those times there would be a redundant and functionally unneeded load to the server, but then each bussiness logic store procedure would be devoted solely to the bussiness logic aspect as supposed to, and this startup called procedure would gather all the logic devoted to the parameter sniffing problem as a technical database proprietary aspect (or even better, if there exist a chance, to cache the SP plan without executing the actual queries, but I don't know how). Then, in the worst case in which data distribution changes, you would have the parameter sniffing issue triggered again but once detected, you need a single modifictation to the startup procedure to get the issue solved (moreover, DBA would be able to modify the startup procedure without altering bussiness logic procedures that may be critical for the customer which could be prone to introduce undesired side effects, not to mention a concurrent modification in which the programmer dealing with the bussiness logic could reintroduce the value you just corrected).
Hi Brent. You mentioned SQL tipping point for "screw-you, I'm going to to scan the entire table" was when having to read ~5% of total pages. However, my copy of stackoverflow (50GB) version, has Users table that is using ~50K pages. Reputation=2 query brings 1800 rows (5700 logical reads). That is ~11% of the pages. However, it is still happily doing the index seek + key lookup. Can you explain why? PS: running SQL2016 DEV Total rows in users table 2465713; consuming 50K pages
Hi Brent, Why am I not getting the same number that sql cardinal estimator uses when local params are used. Your example, you were able to work out the exact number 314 Using density x total rows I don't get the same number sql ce uses
Not often I find anything I can use in my job on RU-vid as an ERP developer. But wow this was extremely helpful. I spent all night last night figuring out a way to get around this exact problem and have now put a "patch" on it using a Plan Guide adding a index hint. Unfortunately the stats is weird and makes sometimes a very strange plan on a table with 200.000.000+ records. It will make it run for almost ever. When I add the hint it's done in 0 seconds. Any thoughts on Plan Guides? We are currently on a SQL Server 2012.
Hi, Question. You mentioned in the presentation that SQL remembers how much memory it needs to execute the proc when its first compiled; but I thought you also mentioned that sql determines there's "lots of work" or "small amount of work" involved when it first executes [by sniffing the params that comes in]... So which is correct?
Brent is such a good presenter ! 47:10 The compiler could obviously ignore ALL comment when parsing the statement for use of query parsing, so that you can actually put comments into a statement. Remember: developers are instructed to put "useful" info in there. Same with upper and lower reserved words, they should be treated the same. Other things have similar issues, but this is part of the compiler's job; it should rebuild the statement to a uniform format - similar to like when you read in into the document, where it states the default method of using reserved words and such.
