1

I have a query that it seems taking about 50 seconds to execute from SQL Management Studio, but when a user tries to run it as a report from a front end application (that was built in VB), the application seems to through an error of "timing-out expired (after 20 seconds),The timeout period elapsed prior to completion of the operation or the server is not responding" So it's not waiting for SQL to finish executing the stored procedure.

It was running fine up until today, where one of the developers had to update (several 100's) rows for a column in one of the joined tables for that stored procedure, could that affected indexes, statistics..? I'm not sure where to start checking, please advice.

This report only acting up for one division (Central America), so when I log in as a West division user or any other division, it executes within 2 seconds for the same amount of rows (about 200 rows)...but I remember that it ran successfully couple of times this morning when I log back in from Central America. I checked it back again after peak hours, errors still the same!

I'm using SQL Server 2008 r2, Standard edition, not using any tools unfortunately.

Improve this question
2
  • What is the default command time out set to in the application? If it's timing out after only 20 seconds it seems like the application is setting it from the default of 30 seconds. Take a look at MSDN Command Timeout. If you have access to the application you could set it higher and see if the execution completes.
    – Aaron
    Feb 16, 2016 at 4:04
  • I don't have access to the application, but the app developer said that there is no time out number set in the VB code, so we assumed the default was about 30 seconds since we got the time out error msg at that time compared to the 50 seconds from the back end in SQL Server when I executed the SP. I will pass the link your provided for sure, thanks for posting it as we may have to set it higher if the error comes back. Some changes to the app code requires validation and approval.
    – Shayma Ahmad
    Feb 20, 2016 at 19:08

3 Answers 3

Reset to default
2

The most likely case is either parameter sniffing or, less likely, bad statistics, with a very unlikely side of index split pages (thousands of rows is pretty small for that).

To prove parameter sniffing:

  1. Turn on profiler or a trace for your particular Session_ID; watch Reads, Writes, CPU, Duration for events SQL:BatchCompleted and RPC:Completed

  2. Invalidate the query cache for your particular query, which you can do by either DBCC FREEPROCCACHE sql_handle

    a. Note that if you do DBCC FREEPROCCACHE without any arguments, it frees the entire procedure cache, which has serious though short lived bad performance impacts on production boxes, since EVERY query plan now needs to be rebuilt.

  3. Run your query for the most common set of parameters

  4. Run your query for the most common set of parameters (again; ignore the results from #2, since they may include caching data)

  5. Run your query for the least common, most unusual (index statistics wise) set of parameters.

  6. Run your query for the least common, most unusual (index statistics wise) set of parameters (again; ignore the results from #4, since they may include caching data)

  7. Do steps 1-5 again, EXCEPT do 1,2,5,6,3,4 - i.e. do the least common first, and the most common second

  8. If your results on the second run of each set are significantly different on reads, writes, and/or CPU, you've proved parameter sniffing! If not, you may still have parameter sniffing issues on other combinations, or you may not.

If it takes arguments that vary from division to division, it's probably parameter sniffing. Read Erland Sommarskog's article Dynamic Search Conditions in T‑SQL which covers several ways of handling the various execution plans required. WITH RECOMPILE is the quick band-aid, multiple stored procedures is the cadillac solution, and very careful dynamic SQL is another option (NEVER concatenating parameters into the SQL statement, only using them as actual parameters and as part of IF statements that put blocks of code in).

You can ALTER INDEX ALL on REBUILD and it'll also update statistics, and see if that makes a difference. Use ONLINE=ON if you're on Enterprise edition and need to, otherwise it's going to lock those tables.

Oh - make sure all tables have high cardinality clustered indexes first!

Improve this answer
3
  • I don't think there is enough information in the post to jump to those conclusions. The OP says the report works fines except for one region so there could be other underlying issues involved with either network latency or connectivity issues.
    – Aaron
    Feb 16, 2016 at 4:13
  • @Anti-weakpasswords thanks for introducing me to parameter sniffing, I have to fully understand it quickly as from what I read in the links you provided, I may have it without realizing it since I don't check execution plans.... I used to reorganize or rebuild indexes that are more than 80% fragmented but then I read online about it's page numbers matter too. So if pages filled are below 1000, then we don't need to rebuild or reorganize...
    – Shayma Ahmad
    Feb 20, 2016 at 19:18
  • @ShaymaAhmad - you're welcome, and I hope it helps; those two links explain a lot. I'll update with how to prove parameter sniffing issues :). Also, as far as indexes with less than 1000 pages, you'll see a lot of reasonable advice that they don't benefit much from a rebuild. Personally, I feel that they take so little resources to rebuild you might as well rebuild them anyway, AND you get fully updated statistics out of it, which may help. Whenever you can, rebuild instead of reorganize.
    – Anti-weakpasswords
    Feb 21, 2016 at 2:30
1

After searching for hours, I finally found a solution.

Using regedit, go to HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5\Engines\ODBC or HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC (depending on your version of Access).

Find the registry key QueryTimeout, and change the seconds there.

More details in this geeksengine.com article

Improve this answer
0

It seems Changing the "Remote Server Connection" option from the SQL Server instance properties to zero, helped: Right click on the instance name from SSMS, go to Properties, then choose Connections, set the "Remote Query timeout in seconds" to 0 (default seemed to be 600), then I ran the report from the front end app, it took only 1 second. I set it back to 600, still works like a charm!

Not sure if that is the exact solution, but so far it helped us for now. Thanks to all the suggestions and useful links provided.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.