Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi all! We have been experiencing an issue on site where threads have been missing the latest postings. The platform host Vanilla are working on this issue. A workaround that has been used by some is to navigate back from 1 to 10+ pages to re-sync the thread and this will then show the latest posts. Thanks, Mike.
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Wierd TSQL Conundrum

  • 04-04-2014 4:45pm
    #1
    RangeR
    Registered Users, Registered Users 2 Posts: 7,265 ✭✭✭


    So, we've been having consistently inconsistent [poxymoron I know] speed issues with one department over the past few months. It was a wierd problem that took a lot of debugging. I won'pt bore everyone with the details but the end result is that we narrowed the cause of the problem down to one Stored Proc.

    This Stored Proc isn't too detailed. It does three or four data integrity checks, then two or three inserts/updates if all is ok. When all is good, this SP runs in about 30 ms, even with verbose logging on. After a few days, this drops down to 2 to 3 seconds per execution. This SP is a critical piece of the entire system and could potentially be run 40 to 100 times a minute. 3 seconds per execution cycle is a serious impediment.

    The problem isn't one of indexes, or data manipulation. It's the actual Stored Proc.

    I don't understand it myself but when the department in question rings down and complains of slowdowns, we finally identified [by pure accident as it makes no sense] that if we open this Stored Proc into SQL Management Studio and just "Execute" the ALTER STORED PROCEDURE complete script without any change, it would speed things up for a few days.

    It's gotten to a stage where I've written a tiny C# utility with one button. Once clicked, this button simply connects to SQL Server and executes the exact same ALTER STORED PROCEDURE command script. In essence just re-compiling the SP.

    This makes no sense to me. I know it speeds things up by performing this simple process that appears to do nothing more than just recompile the SP but it's probably indicative of some other more serious issue. I've never heard of an SP "going bad", consistently, every few days.

    I'd be very interested in anything anyone can throw at this issue.


Comments

  • #2
    Procasinator
    Registered Users, Registered Users 2 Posts: 1,311 ✭✭✭Procasinator


    Look through this stuff for tips:
    http://stackoverflow.com/questions/1850172/stored-procedure-performance-randomly-plummets-trivial-alter-fixes-it-why

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

    When you alter the sproc, the plan gets re-compiled with a good plan - for a while.

    You should try to identify the queries that are causing these problems using the SQL Profiler.


  • #3
    amen
    Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


    you most likely have a parameter sniffing issue. Basically when the SP runs with a set of parameters a query plan is created which tells SQL server how to search for the data.

    Occasionally when your SP runs you are getting a set parameters that are very dissimilar from those for the cached query plan. This causes SQL Server to perform the search in a potentially inefficient manner leading to the behaviour.

    When you recompile the SP the cached query plan is thrown away and a new one created which may be efficient for the data that was causing the issue.

    If you take offending SP and parameters and run in SQL Server Enterprise Query window you may find that the SP is very quick and efficient as queries run through the Query window may use different query plan from when the SP is execute from an application.

    I know you say your query is efficient but it may be worth looking at io statistics, disk io, the execution plan ( looking for scans, key look ups etc)

    Of course you may also be having Disk io issues.

    Does this happen around the same time each day ?

    Have you looked at the lock types when this occurs ?What type of locks are you seeing ? cx_latchs? Whats the memory, cpu etc usage like when this occurs

    These can be a pain to correct


  • #4
    RangeR
    Registered Users, Registered Users 2 Posts: 7,265 ✭✭✭RangeR


    amen wrote: »
    you most likely have a parameter sniffing issue. Basically when the SP runs with a set of parameters a query plan is created which tells SQL server how to search for the data.

    Occasionally when your SP runs you are getting a set parameters that are very dissimilar from those for the cached query plan. This causes SQL Server to perform the search in a potentially inefficient manner leading to the behaviour.

    When you recompile the SP the cached query plan is thrown away and a new one created which may be efficient for the data that was causing the issue.

    If you take offending SP and parameters and run in SQL Server Enterprise Query window you may find that the SP is very quick and efficient as queries run through the Query window may use different query plan from when the SP is execute from an application.

    I know you say your query is efficient but it may be worth looking at io statistics, disk io, the execution plan ( looking for scans, key look ups etc)

    Of course you may also be having Disk io issues.

    Does this happen around the same time each day ?

    Have you looked at the lock types when this occurs ?What type of locks are you seeing ? cx_latchs? Whats the memory, cpu etc usage like when this occurs

    These can be a pain to correct


    Nice one. What you've said has given me a HUGE amount to look into. We use parameters. Some are optional. The SP has one main function but performs differently depending on the optional parameters that are used.

    I think you are spot on in what you are saying. I'll investigate this on Monday. You may have saved me a few weeks of work. Cheers.


Advertisement