The parameter sniffing local variable trick copies a parameter into a variable so SQL Server cannot sniff it. This post measures the estimate and the page reads of a sniffed and a local variable procedure, called in both orders. It shows what the trick fixes, and what it leaves slow.
SQL SERVER – Parameter Sniffing Simplest Example
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.
Copy Database Without Statistics and Query Store Data
To copy database without statistics, add NO_STATISTICS and NO_QUERYSTORE to DBCC CLONEDATABASE. This post shows why that matters, because histograms and Query Store texts hold real values. It also shows the plan estimate that changes, how to make a clone writable, and what VERIFY_CLONEDB does.
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.
Clone Database in SQL Server With DBCC CLONEDATABASE
Clone Database in SQL Server with DBCC CLONEDATABASE and you copy the structure and the statistics without a single row. This post runs the command on a demo database, checks what the clone holds and what it leaves out, and shows that the optimizer builds the same plan from the copied statistics.
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.