Consistently executing sp_updatestats to resolve time out error

Question

Recently, I have been chasing an intermittant time out message on an intranet web application for our users.  When the uses open the web page, a datagrid loads with all the data from a stored procedure.  Pretty basic.  When I execute the stored procedure from SSMC the query runs but sometimes it does it in a few seconds and others times it takes over a minute.  The database is SQL 2005 and I came accross the sp_updatestats procedure.  When I execute that stored procedure and return to the web page, it works as it should.  the data loads and it does so in a short, timely manner.

The problem is I am frequently being told the page isn't working and when I re-execute the sp_updatestats procedure the page starts working again.  I checked the database settings and it indicates the auto update stats is true.

Why would I need to run this sp_updatestats several times a day?  That doesn't seem right!

WB

Answer 1

This may well be a result of "parameter sniffing".  When you first execute a stored procedure, SQL looks at the parameters and builds a query plan.  It builds the best plan it can for those parameter values.  When the same stored proc is called later, if that query plan is still in memory, SQL reuses it.  This is normally a good thing and improves performance, because the best plan for one set of parameter values is often the best plan for another set of parameter values.

However, there can be cases when the best plan for one set of parameters is very inefficient when another set of parameters is passed.  This looks like it might be one of those cases.  The reason sp_updatestats fixes your problem is not that you need to update stats, it's that updating the stats causes all the query plans for that table to be removed from memory and the next time you execute the stored proc you get a new (and hopefully efficient) plan build for that set of parameter values.

There is a recompile option you can specify that will force the plan to be rebuilt each time the stored proc is called.  If you know when statement in the stored proc is causing the problem you can use the RECOMPILE option on just that statement.  Or you can specify the whole stored proc is recompiled.  To do it for the entire stored proc, change the CREATE (or ALTER) procedure statement from

CREATE <proc name><parameter list> AS ...

to

CREATE <proc name><parameter list> WITH RECOMPILE AS ...

To do it on just one statement, not the whole procedure, just add OPTION(RECOMPILE) to that statement, for example

SELECT <column list> FROM <whatever> WHERE ID = @Parameter1 OPTION(RECOMPILE)

Hopefully, this will fix your problem.

Tom

Answer 2

The saga continues.  When I tested the web application this morning, the same time out error page appeared.  My plan was, after reading your response, if it failed i would modify the SP and add the WITH RECOMPILE statement.  I thought this would take care of it, as your response makes perfect sense.  However, with or without the RECOMPILE statment the page still times out.  Once again, I am able to execute the SP from within SSMC but when the webpage executes it times out.  Any other ideas?

WB

Answer 3

Although parameter sniffing could affect your situation, the possibility of stale statistics is real too.

The automatic updates of the statistics is triggered based on the heuristics SQL Server uses. It typically requires 20% changes to the table before the statistics are recalculated. If your changes are not in line with the statistics, they become non-representative before the auto-update threshold is reached. When you run sp_updatestats you manually force recalculation of the statistics.

One case where auto-update is typically not often enough, is when you have an ever increasing column such as an Order Date column. If the statistics are determined today, the optimizer many think there are hardly any orders for February 14. Come tomorrow, that assessment may be completely incorrect, leading to suboptimal query plans.

sp_updatestats is very coarse grained. It would be better to find out which statistics of which index is stale before the auto-update kicks in. You could schedule a (daily?) job with the UPDATE STATISTICS command for those particular indexes, preferably with the keyword WITH FULLSCAN.

Another reason not to run sp_updatestats when it is not needed, is because it will cause all cached query plans to be flushed, which causes recompilation (which slows down the next query invocation).

---

Gert-Jan

Answer 4

I would not be opposed to a nightly job that runs update statistics; however, now I am not convinced that is what will resolve the problem.  As I mentioned, when I would run that SP the time outs went away...for a while.  Now, however, when I run the SP the time outs still happen.  I am just not sure what to do!

WB

Answer 5

If updating the statistics doesn't solve your problem, then the statistics weren't the problem in the first place.

Maybe you simply have no suitable indexes on your tables to satisfy your query. If the database keeps growing, at some point you may hit your timeout setting.

You can increase your timeout setting, but that will only delay the problem until you hit the new boundary.

A properly indexed table will see only minor performance degradation when it grows. You should inspect the query plan of the query that times out.

---

Gert-Jan

Answer 6

As for why you see different performance in the web application and SSMS, this article on my web site describes why that happens. It also goes into a more detailed discussion on parameter sniffing and gives you some troubleshooting queries.

http://www.sommarskog.se/query-plan-mysteries.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

I would try re-writing the procedure by breaking the offending query out into possibly 2 separate queries with an intermediary temp table if possible.  It sounds like the execution plan goes south because I guess your stats are out of wack.  Auto update stats may be causing the issue if it's not using the same sample % as your sp_updatestats.  Either way, I personally find breaking queries out, the safest way to keep the optimizer from getting confused.

---

Derek SQLPlanet.com

Answer 8

How much time is it taking with recompile option?

in SSMS

Answer 9

Can you please post the T-SQL of the stored procedure? This is just in-case you query might need some re-structuring and we might come up with some workable solutions.

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 10

With the Recompile option it is taking about 30 sec consistantly.

Answer 11

Here is the SP.  I didn't create this and the person who did is no longer here.

ALTER PROCEDURE [dbo].[CustomerForm_s]
	-- Add the parameters for the stored procedure here
@CustomerID nvarchar(6)
AS
BEGIN
if len(ltrim(rtrim(@CustomerID))) = 0
BEGIN
Set @CustomerID=''
END
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for procedure here
	/*SELECT cf.*, (Select count(*) from DepartmentApproval where CustomerFormID=cf.CustomerFormID and Approved is null) as DeptApprovalCount, 
		(Select count(*) from DepartmentApproval where CustomerFormID=cf.CustomerFormID) as DeptCount
	from CustomerForm cf
	Order by cf.CustomerFormID desc
	*/
;WITH ApprovalList (RowNumber,  CustomerFormID, DisplayName, NotApprovedList) AS
(
  SELECT 1, DA.CustomerFormID, MIN(D.DisplayName), CAST(MIN(D.DisplayName) AS NVARCHAR(max)) 
  FROM DepartmentApproval DA
  INNER JOIN Department D ON DA.DepartmentID = D.DepartmentID
  AND DA.Approved is null
  GROUP BY CustomerFormID
  
  UNION ALL

  SELECT AL.RowNumber + 1, DA.CustomerFormID, D.DisplayName, AL.NotApprovedList + ', ' + D.DisplayName 
  FROM DepartmentApproval DA 
  INNER JOIN Department D ON DA.DepartmentID = D.DepartmentID
  INNER JOIN ApprovalList AL ON AL.CustomerFormID = DA.CustomerFormID 
  WHERE D.DisplayName > AL.DisplayName and DA.Approved is null
)

SELECT Distinct AL.CustomerFormID, CF.CustomerID, CF.SystemID, convert(nvarchar(100),CF.Created,1) as Created, NotApprovedList 
FROM ApprovalList AL
Inner JOIN CustomerForm CF ON CF.CustomerFormID = AL.CustomerFormID
Inner JOIN (SELECT CustomerFormID, MAX(RowNumber) 
AS MaxRow FROM ApprovalList GROUP BY CustomerFormID) R
ON AL.RowNumber = R.MaxRow AND AL.CustomerFormID = R.CustomerFormID
where cf.CustomerID like @CustomerID + '%'
--ORDER BY CustomerFormID desc

UNION ALL

Select distinct cf.CustomerFormID, cf.CustomerID, cf.SystemID, convert(nvarchar(100),cf.Created,1) as Created, ''
from CustomerForm cf
where (Select count(*) from DepartmentApproval where CustomerFormID=cf.CustomerFormID and Approved is null) = 0
and cf.CustomerID like @CustomerID + '%'
order by NotApprovedList desc, Created desc
END

I have seen this working and not working from the web page. When it works, the result appears within 1 second. when it does not work, the result times out. Inside SSMS, the query might take less than a second other times it might take over 1 minute. But it never times out.

Answer 12

It's probably returning the entire virtual table select before it correlates the external queries.  Maybe try changing the virtual table join to a cross apply.

I would try:

SELECT Distinct AL.CustomerFormID, CF.CustomerID, CF.SystemID, convert(nvarchar(100),CF.Created,1) as Created, NotApprovedList 
FROM ApprovalList AL
Inner JOIN CustomerForm CF ON CF.CustomerFormID = AL.CustomerFormID
CROSS APPLY (SELECT MAX(RowNumber) 
AS MaxRow FROMApprovalList WHERE CustomerFormID = AL.CustomerFormID) R
ON AL.RowNumber = R.MaxRow
where cf.CustomerID like @CustomerID + '%'

Then maybe force an inner loop join on CustomerForm just to see if the optimizer will limit the result set there *before* going to the CTE.

SELECT Distinct AL.CustomerFormID, CF.CustomerID, CF.SystemID, convert(nvarchar(100),CF.Created,1) as Created, NotApprovedList 
FROM ApprovalList AL
Inner LOOP JOIN CustomerForm CF ON CF.CustomerFormID = AL.CustomerFormID
CROSS APPLY (SELECT MAX(RowNumber) 
AS MaxRow FROMApprovalList WHERE CustomerFormID = AL.CustomerFormID) R
ON AL.RowNumber = R.MaxRow
where cf.CustomerID like @CustomerID + '%'

---

Derek SQLPlanet.com

Answer 13

And...as others have mentioned parameter sniffing...I'd give that a quick try.  I've run into many times with web apps and ssrs.

You should be able to quickly check this by changing the code to use a local parameter.

So, in the beginning have something like:

Begin
     Declare @LocalCustID nvarchar(6) 
     Set @LocalCustID = @CustomerID
  ......

And then later on just change ALL the reference in the query from @CustomerID to @LocalCustID

where (Select count(*) from DepartmentApproval where CustomerFormID=cf.CustomerFormID and Approved is null) = 0
and cf.CustomerID like @LocalCustID + '%'

- will

ALTER PROCEDURE [dbo].[CustomerForm_s]
-- Add the parameters for the stored procedure here
@CustomerID nvarchar(6)
AS
BEGIN
if len(ltrim(rtrim(@CustomerID))) = 0
BEGIN
Set @CustomerID=''
END

---

- will

Answer 14

Of course, it never times out in SSMS. And for that matter, it never times out in the application either. It is the application that gets tired of waiting. If the timeout is the main problem, change the CommandTimeout on the Command object. SQL Server lets queries run as long as they wish.

The condition

   like @CustomerID + '%'

can be problematic, if users can enter leading % for the customer id. Can that happen?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 15

This code:

if len(ltrim(rtrim(@CustomerID))) = 0
BEGIN
Set @CustomerID=''

Effectively kills any attempt at paremeter sniffing, which dispite it's bad press, is a good thing!
Microsoft added the feature becasue it makes things go faster!

Anyway, that is why "With Recompile" has no effect because tou always get the same plan.

A quick solution might be to remove this code and change the web site to clean up the parameter before passing it and then add the "With Recompile". That way the parameter sniffing will work as designed.

An alternative is to nest the stored procedures like this:

FIRST: Rename the original procedure as say: [dbo].[CustomerForm_s_Inner]

then add the "With Recompile" and remove this code

if len(ltrim(rtrim(@CustomerID))) = 0
BEGIN
Set @CustomerID=''

SECOND:

create a new stored procdeure called [dbo].[CustomerForm_s]

CREATE PROCEDURE [dbo].[CustomerForm_s]
-- Add the parameters for the stored procedure here
@CustomerID nvarchar(6)
AS

BEGIN
   BEGIN
   if len(ltrim(rtrim(@CustomerID))) = 0
   BEGIN
      Set @CustomerID=''
   END

   EXEC  [dbo].[CustomerForm_s_Inner] @CustomerID

END

This will allow the inner procedure to be recompiled with parameter sniffing so a queryplan appropriate to the parameter can be created

A third option which would safe the recompilations and therefore be more efficeint (if the proc is heavily used) is to create multiple inner procedures which handle different lenght parameters

e.g.

create a new stored procdeure called [dbo].[CustomerForm_s]

CREATE PROCEDURE [dbo].[CustomerForm_s]
-- Add the parameters for the stored procedure here
@CustomerID nvarchar(6)
AS

BEGIN
   BEGIN
   if len(ltrim(rtrim(@CustomerID))) = 0
   BEGIN
      Set @CustomerID=''
   END

   EXEC  [dbo].[CustomerForm_s_Inner] @CustomerID

END

So far so good.

Bear in mind that a query plan will be cached and re-used, and that when more than one user runs the query a new plan will be creaed, then

Answer 16

Now that we can see the stored proc it is clear that this code is preventing parameter sniffing as it is manipulating the parameter

if len(ltrim(rtrim(@CustomerID))) = 0
BEGIN
Set @CustomerID=''

Despite the bad press, parameter sniffing is usually  a good thing and in this case it should help a lot because the parameter is a wildcard.

To enable parameter sniffing we need to remove the above code from the stored procedure without breaking the functionality.

You can either:
edit the web pages so that they trim the parameter before calling the stored procedure, this making the code redundant.

Or you can create nested stored procedures so that the trim is handled outside of the main procedure

e.g.

CREATE PROCEDURE [dbo].[CustomerForm_s]
 -- Add the parameters for the stored procedure here
@CustomerID nvarchar(6)
AS
BEGIN
   BEGIN
   if len(ltrim(rtrim(@CustomerID))) = 0
   BEGIN
      Set @CustomerID=''
   END

   EXECUTE [dbo].[CustomerForm_s_INNER] @CustomerID
END

-------

CREATE PROCEDURE [dbo].[CustomerForm_s_INNER]
 -- Add the parameters for the stored procedure here
@CustomerID nvarchar(6)
WITH RECOMPILE
AS
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
    -- Insert statements for procedure here
 /*SELECT cf.*, (Select count(*) from DepartmentApproval where CustomerFormID=cf.CustomerFormID and Approved is null) as DeptApprovalCount,
  (Select count(*) from DepartmentApproval where CustomerFormID=cf.CustomerFormID) as DeptCount
 from CustomerForm cf
 Order by cf.CustomerFormID desc
 */
;WITH ApprovalList (RowNumber,  CustomerFormID, DisplayName, NotApprovedList) AS
(
  SELECT 1, DA.CustomerFormID, MIN(D.DisplayName), CAST(MIN(D.DisplayName) AS NVARCHAR(max))
  FROM DepartmentApproval DA
  INNER JOIN Department D ON DA.DepartmentID = D.DepartmentID
  AND DA.Approved is null
  GROUP BY CustomerFormID

...etc

Don't forget the WITH RECOMPILE

This means that every execution will get it's own optimised queryplan.

-----------

If the system is heavily used, and this stored proc is also heavily used, then the recompiles can be a significant overhead, in which case PLAN 'B' would be a better option.  No time to explain plan 'B' tonight. let me know if you need it.

Good luck

Answer 17

It appears that you have dynamic table(s).

Consider the following:

1. REBUILD indexes with FILLFACTOR 80 (70 as a second try) on dynamic tables every weekend

2. UPDATE STATISTICS every night

3. Have development check the stored procedure for optimal design

I would not disturb the production system during the day time.  If performance still not satisfactory, vary the weekend or night activities to get it right.

Optimization article:

http://www.sqlusa.com/articles/query-optimization/

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 18

Now that we can see the stored proc it is clear that this code is preventing parameter sniffing as it is manipulating the parameter

if len(ltrim(rtrim(@CustomerID))) = 0
BEGIN
Set @CustomerID=''

But that is only in a special case.

On the other hand, this code indicates that "give me all customers" is a common request. And the best plan for getting all customers is not the same as the best plan to get data for a single customer.

Rather than adding WITH RECOMPILE, it's better to try OPTION (RECOMPILE) at the end of the query.

However, in this case, I think it may be better to identify the patterns how the procedure is used. I previously pointed n2teeth to this article on my web site: http://www.sommarskog.se/query-plan-mysteries.html
but I'm not sure that he read it. Yes, it is a long article, but there are queries that will help you to analyse from which parameters the plan was built.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 19

On the other hand, this code indicates that "give me all customers" is a common request. And the best plan for getting all customers is not the same as the best plan to get data for a single customer.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Hi Erland,

I agree totally. A single procedure is never going to handle 'get all customers' and 'get one customer' efficiently. That is sort of where my Plan B was going... Your article seems to cover it all nicely, so there is not much point repeating it here. (I've bookmarled that page so I can read it when I get a bit more time.)

Meanwhile, I have found that the "RECOMPILE" option can be very effective providing that the procedure is not heavily used, and the parameters are not modified inside the procedure.

RECOMPILE is very much a sticking plaster though. It is much better optimise the code.

Doug

Answer 20

Meanwhile, I have found that the "RECOMPILE" option can be very effective providing that the procedure is not heavily used, and the parameters are not modified inside the procedure.

You are thinking of the WITH RECOMPILE option that have been on in the product for a very long time. SQL 2005 added the query hint OPTION(RECOMPILE), and it was enhanced in SQL 2008 SP2 and SQL 2088 R2 SP1. With the new behaviour, all variables are taken as constants, which will you the best plan for these values.

The older behaviour, and this also applies to WITH RECOMPILE, handle the parameters as variables, that is, their values may change, and the plan is built to be correct with all possible values. This preclues the best plan whe have conditions like

   col = @par or @par is NULL

---

Erland Sommarskog, SQL Server MVP, [e-mail]