Post reply

Passing parameter take diffrent excecution time

  • December 18, 2014 at 3:18 am

    #145445

    Hi

    I have a quey which give salary report for when passing month and year as parameter. The query is working fine for all months(1,2,3,4,5,6,7,8,9,10) it give results within 10 seconds. But in november(11) month it keeps running for 2 hours and still no results.

    I checked for parameter sniffing and cleared all the cache but still same problem. The strange thing is that it working fine in development, testing and production. The problem is happening in DR server where for month = 11 it keeps executing.

    Any suggestions what will be the root cause

    Thanks

  • December 18, 2014 at 3:30 am

    #1765383

    Blocking? Something on that server has rows for november locked?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 18, 2014 at 3:55 am

    #1765393

    While the query is running, use the sys.dm_os_waiting_tasks view to determine the wait type for the query. Do that several times, just in case it's waiting on lots of different resources one after another. You might want to check out Adam Machanic's sp_whoisactive stored procedure, which takes the hard work out of doing stuff like this.

    John

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

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