SQL Server Performance Forum – Threads Archive
Help with strange performance issue (production!)
Hi everyone, just started browsing this website yesterday and I think it’s great. Sorry for the cross-posting but I wasn’t exactly sure where this belongs. I work for a financial services software devleopment firm. Our software is a medium-volume trading application. I’m here because we are having a SERIOUS production problem that seems to be stumping everyone and need some desperate advice. Can anyone figure out what could be causing this scenario: Monday: At 12:30pm we start getting timeout errors on a bunch of our stored procs. They grow more frequent as the minutes pass until the point where the entire system is unusable. Everything is slowed down. We narrow it down to a user def function which does nothing more than a simple join. However, every stored procedure which calls this function is timing out. One thing we are doing in the stored procs, which I’m not sure is correct or not – we are joining directly to the function itself (which returns a table), rather than inserting into a temp table first and then joining with that. In any case, we disable this function to allow the traders to continue with their business. This is a high-stress production environement so basically our worst nightmare. We alter the stored procs overnight to join with temp tables now instead of the function itself, hoping this will make a difference the next day. Tues: Problem ocurrs AGAIN – at the exact same time. We take no chances and implement our band-aid solution which is to temporarily disable the function in question to allow business to continue. This time luckily we are able to reproduce the timeout in our QA environment and we find that running SP_UPDATESTATS ‘resample’ solves the problem. We’re elated so we update the stats in production overnight to hopefully fix the problem. Wed: Problem ocurrs YET AGAIN at the same time of day. This time, instead of our band-aid solution, we run SP_UPDATESTATS on production right away and the problem gets cleared up. So – What kinds of operations could cause a problem, that running sp_update stats would fix? Should you ever need to run that daily? In our main tables, we’re affecting less than 1% of the main tables’ total size. Any suggestions would be GREATLY appreciated. For reference, I posted copies of two of the scripts in question.The function:http://www.dnsinteractive.com/query1.html
The stored procedure:http://www.dnsinteractive.com/query2.html So, we think that everything is fine until the above stored proc is executed. That stored proc calls the function above. After this point, any other stored procedure which calls this function will time out. We reproduced the timeout in query analyzer by the way. A call to that function took 2 seconds to return 5000+ rows in our environment, and 120 seconds to return 9 rows in their environment! Thank you everyone in advance!!! Wish us luck tomorrow! ————– Application Summary ——————-
It’s a 3-tier application. We are using ASP on the front end which talks to a COM component on the same machine as the web servers. This is our business layer. There is a two-server web farm. The business layer uses COM+ to talk to a data access layer COM component which is on another machine – the same one as our SQL Server. This communicates directly with the database. Our system is heavily stored proc-based. We have about 700 stored proces do most of our data logic. Around 150 tables, with back-office triggers, etc.
——————————————————-
Have started a thread in te Performance Tuning for DBAs forum Cheers
Twan
Please refer tohttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2209
]]>