Post reply

Strange performance problems

  • July 13, 2011 at 6:40 am

    #253579

    I've recently looked into an issue whereby an SP call was taking an age to execute from our ASP.net app, but ran fine when executed with the same parameters using Management Studio.

    This immediately made me think of parameter sniffing and putting in WITH RECOMPILE on the SP fixed the problem. However today we're seeing the same problem on almost every stored procedure call. I've never seen parameter sniffing suddenly affect all SPs across the database.

    I'm not a fully-fledged DBA (more a developer who's been made to do some dba stuff when needed) so I'm a bit short of ideas. I'm wondering about clearing the execution plan cache but worried that this might just mask the problem.

  • July 13, 2011 at 6:50 am

    #1352119

    Have a look http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • July 13, 2011 at 9:46 am

    #1352258

    However today we're seeing the same problem on almost every stored procedure call

    Is this correct? You are seeing this on everyproc?

  • July 13, 2011 at 9:52 am

    #1352268

    also if the proc runs fine in ssms ..then it's prob not parameter sniffing

  • July 13, 2011 at 10:02 am

    #1352270

    the general, wave of the hand, "it's all slow" is really hard to pin down.

    One of the first places i'd look is statistics. updating at a minimum the statsitics of the core tables might address the issue immediately;

    can you provide any more details?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 13, 2011 at 10:15 am

    #1352275

    krypto69 (7/13/2011)

    also if the proc runs fine in ssms ..then it's prob not parameter sniffing

    The cached plan used depends upon the user context.

    If the user that executes the stored procedure via the ASP.net code is different then the user executing the stored procedure in SSMS then I would suspect parameter sniffing as well.

  • July 13, 2011 at 10:18 am

    #1352278

    The cached plan used depends upon the user context

    true.. I was assuming all variables where the same..

  • July 13, 2011 at 10:22 am

    #1352280

    Thanks for all your replies, and apologies for the lack of information in my original post. I was going off the information I had at the time, which turned out to be incorrect once I'd actually got remote access to investigate first hand. "Every SP is running slowly" because "One SP is still having issues".

    So a bit of a false alarm, but thanks all the same!

  • July 13, 2011 at 10:28 am

    #1352283

    that's good news! i was also either a hardware /100%CPU pegged issue, or thinking maybe somebody dropped the indexes in the database, if everything was running slow would be the next things to looka t.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 13, 2011 at 10:35 am

    #1352288

    Adam McArdle (7/13/2011)

    Thanks for all your replies, and apologies for the lack of information in my original post. I was going off the information I had at the time, which turned out to be incorrect once I'd actually got remote access to investigate first hand. "Every SP is running slowly" because "One SP is still having issues".

    So a bit of a false alarm, but thanks all the same!

    Yes, users always think it was the same thing that happened last time so you have to start at square one and assume that it's not.

  • July 13, 2011 at 10:53 am

    #1352303

    On the subject of Parameter Sniffing, I had a SQL Server MVP tell me last week that the performance problems that are occasionally associated with it had been fixed in SQL 2008 and later versions of SQL 2005. But I recently had an issue with it on SQL Server 2008 SP2 so I don't believe this to be the case.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply