Today we will see a very simple example of the Parameter Sniffing. Let us see a very simple example of the parameter sniffing.
NOCOUNT Performance: What SET NOCOUNT ON Saves in a Loop
NOCOUNT performance depends on the messages that SQL Server no longer sends to the client. This post counts the network packets of a procedure with a 5,000 step loop, with the setting off and on. It also explains why some tests show no time gain, and where the setting helps most.
Skipping No-Op Updates: Changing Only Rows That Really Differ
Skip no-op updates with NULL-safe EXCEPT or IS DISTINCT FROM, inspect affected counts, and avoid unnecessary rowversion and trigger changes.
Why Execution Plan Operator Read More Rows Than Available? – Interview Question of the Week #255
Question: Why Execution Plan Operator Read More Rows Than Available?
Answer: Statistics.
Now let us learn detailed answer in this blog post.
What is the Priority of Database Scoped Configurations? – Interview Question of the Week #254
Question: What is the Priority of Database Scoped Configurations over Global Configuration at Server Level?
Answer: This is a follow-up question.
Why Query Cost Percentages in a Plan Mislead You
Test why query cost percentages are estimates by comparing two statements with STATISTICS IO, TIME, and query DMV counters.
Comparing a Good and a Bad Plan for One Query in Query Store
Compare a good and a bad plan in Query Store using runtime history, SSMS Compare Showplan, estimates, joins, grants, and parallelism.