Post reply

Query across linked servers slowed down to a stop

  • March 3, 2005 at 5:51 am

    #65670

    Hi All,

    I have a strange problem.

    A stored procedure running some quite complex sql slowed down yesterday for no apparent reason, to the point that the calling app is now timing out and has become un-useable. 

    This app is he one we use to Invoice customers so it is quite a big problem.....

    This is a search procedure, so it accepts input parameters to search on and it runs across a linked server and 2 databases to get it's information.

    I have been at this all morning and can find no good reason for the sudden, dramatic drop in performance. The only thing I have found is: After ripping the code out of the sproc and running it in QA - If I use the input variable to search with, it takes forever. If I change this to an integer, the query executes in 1 - 2 tenths of a second. (The variable is declared as an integer and the field it is searching on is an integer field.)

    This sproc has been in daily use since November last year and has never given a hint of trouble, why would it suddenly die now? It feels to me like somebody has been fiddling with the server settings - but which one?

    Anybody got any ideas? Had the same / similar problems?

    Steve

    We need men who can dream of things that never were.

  • March 3, 2005 at 8:13 am

    #545233

    Got it - that was a bit of a pig.....

    When you create a new linked server using EM, under 'Provider Name' you get to set 'Provider Options'.

    All make note, if you change these options, they affect ALL linked servers using this provider and this is the ONLY time you get to set these options - once your linked server is created, you can't get to these oledb provider options without creating new again. (Or - this is the only way i have found up to press.. )

    So, if someone else creates a new linked server and changes any of these settings - they immediately take effect on all your linked servers.

    Guess what the problem was.

    Have fun

    Steve

    We need men who can dream of things that never were.

  • March 3, 2005 at 8:58 am

    #545260

    NICE!

    I'll keep this one in mind, thank you for sharing


    * Noel

Viewing 3 posts - 1 through 2 (of 2 total)

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