1

I am having an issue where an update query with about 70 parameters times-out on occasion. Based on some research, I believe this is due to packet sniffing. I saw that in newer versions of SQL Server, I can use the Option(recompile) clause, but that does not work in my case, since I am using server 2000.

I am using sqlhelper.executeNonQuery and not a stored procedure.

PFranchise
  • 6,642
  • 11
  • 56
  • 73
  • @MitchWheat I do not have a ton of SQL Server experience, so that might be a shortcoming on my end in this discussion. But, I did not rebuild the indexes and update statistics yet. Your post had a warning about doing those in Prod. What things should I be concerned about? The table does take part in replication. – PFranchise Oct 31 '12 at 17:34

2 Answers2

3

As far as I know there is no "out of the box" way like Option(recompile), however I remember I found a way to fool the optimizer. It seems to sniff only the parameter that you're actually PASSING externally to the query, not all of them. So, if you try to run

SELECT MyField1, MyField2 FROM MyTable WHERE MyOtherField = @MyParm

Parameter sniffing WILL happen, however if you write something like

DECLARE @MyUnsniffableParm varchar(30)
SET @MyUnsinffableParm = @MyParm    
SELECT MyField1, MyField2 FROM MyTable WHERE MyOtherField = @MyUnsniffableParm

ad of course pass to your script just the @MyParm parameter, parameter sniffing doesn't seems to happen! Let me know If I recall correctly I have no SQL 2000 instances to try!
EDIT:
Looks like something else is doing the same out here: http://blogs.msdn.com/b/khen1234/archive/2005/06/02/424228.aspx

mCasamento
  • 1,413
  • 1
  • 11
  • 21
  • This will lead to a very generic execution plan that works for all values of all parameters, like `option (optimize for unknown)` would. That can be a good choice, but it does not provide an execution plan for the specific values of the current parameters, like `option (recompile)` does. – Andomar Oct 30 '12 at 17:47
1

An easy fix is not to use parameters. Instead of:

SELECT * FROM YourTable WHERE UserName = @myUserName;

Pass:

SELECT * FROM YourTable WHERE UserName = 'PFranchise'

If SQL Server does not know about parameters, it can't sniff them! SQL Server will recompile the query plan for every query.

Two notes about this approach:

  • Be careful about SQL Injection
  • In later versions of SQL Server, the server option "forced parameterization" can sniff even queries without parameters. It's turned off by default. But it's something to keep in mind when you upgrade SQL Server.
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • This seems like a solid approach. For an update statement, is it necessary to replace all `@attributes` (those used in the update and where clauses), or just the one in the where clause? What i mean is, is `UPDATE [table] SET val1=@val1 WHERE UserName = 'Pfranchise'` good enough, or do I need to replace @val1 as well? For reference, my query has 1 where, and ~74 vals. – PFranchise Oct 30 '12 at 17:59
  • You'd just need to replace the `where` value. BUT-- with just one variable in the `where` clause, parameter sniffing should not be an issue at all! Perhaps you can further explain the kind of issue that you have? – Andomar Oct 30 '12 at 18:11
  • Here is a link to my original question that explains in greater detail the issue I am experiencing. http://stackoverflow.com/questions/13013486/sqlparameter-and-executenonquery-causing-unrepeatable-time-outs – PFranchise Oct 30 '12 at 18:33
  • Ok. In that case parameter sniffing is unlikely to be the issue. Check the definition of table `VendorInfo` and if there is an index on the `ID` column. If not, add it, like `alter table VendorInfo add constraint PK_VendorInfo primary key (id)` – Andomar Oct 30 '12 at 18:48
  • Ok, thanks for the info. id is already the PK of that table and if I remember correctly, that means it should already have an index. – PFranchise Oct 30 '12 at 19:04
  • Yes a primary key implies an index. Double-check that `id` is the primary key column. – Andomar Oct 30 '12 at 19:08
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/18815/discussion-between-pfranchise-and-andomar) – PFranchise Oct 30 '12 at 19:15
  • You marked down my answer (to poster's previous question) because I mantioned param sniffing, and then you say its the reason here. How does that make sense? – Mitch Wheat Oct 31 '12 at 03:34
  • @MitchWheat: Well, how can sniffing cause issues if `@id` is the only parameter in the `where` clause? This question just asks to prevent sniffing in SQL Server 2000. The other question contains enough information so that you can see the timeout problem can't be caused by sniffing. – Andomar Oct 31 '12 at 06:47
  • @MitchWheat: The questions are really different. This one doesn't contain the SQL query and just says "how to prevent sniffing in 2000". Which I answered, and then it turned out not to be the problem. (I'm off to work now...) – Andomar Oct 31 '12 at 06:54
  • No, they appear to be the same: "an update query with about 70 parameters" - see the other question... – Mitch Wheat Oct 31 '12 at 06:55
  • @MitchWheat: So how would you determine from this question that only 1 of the 70 parameters is used in the `where` clause? – Andomar Oct 31 '12 at 06:59
  • Actually I think the question actually is: how did YOU determine from this question that only 1 of the 70 parameters is used in the where clause? – Mitch Wheat Oct 31 '12 at 07:00
  • @MitchWheat: The OP linked to his earlier question in the comments. The earlier question had an answer suggesting the sniffing as the cause of the timeouts. But the query in that question makes that answer unlikely. – Andomar Oct 31 '12 at 08:39