Today, again, I have a BASIC issue with what appears to be sniffing parameters in SQL Server 2005.
I have a query comparing some results with known good results. I added a column to the results and the known good results, so that every month I can upload new monthly results in both directions and compare only the current month. The new column is the first in the cluster index, so new months will be added to the end.
I am adding criteria to my proposal WHERE- this is generated by the code, so it has a constant constant:
WHERE DATA_DT_ID = 20081231 - This is redundant, because all DATA_DT_ID is 20081231.
Productivity goes to the bank. From 7 seconds to comparison, about 1.5 m lines to 2 hours and nothing ends. Running generated SQL rights in SSMS - no SP.
I have been using SQL Server for the transition to 12 years, and I have never had as many problems with the sniffing parameter as I had on this production server since October (build build 9.00.3068.00). And in each case, this is not because it was started for the first time with a different parameter, or the table has changed. This is a new table, and it starts only with this parameter or without a suggestion WHERE.
And no, I do not have access to the DBA, and they did not give me enough rights to view the execution plans.
To such an extent that I am not sure that I can process this system for SQL Server users in just a couple of years.
. , , , UPDATE STATISTICS WITH FULLSCAN .
SP, WITH RECOMPILE UPDATE STATISTICS, , , NOT IN LEFT JOIN NULL .