Post reply

Same SP Same DB Same Server Spec - Different Performace

  • September 8, 2005 at 4:30 am

    #168347

    Hi,

    Running SQL2000 SP3 on Windows 2000 Server

    I have a Stored Procedure that Take 7 seconds on one server (Live) and 4 minutes on the other (Test). Both servers have the exact same spec. The databases are the same as we backup and restore the DB to the Test server each night.

    I won't bore you with the code of the SP as it is 12 pages long, suffice to sat that it pumps a lot of data into about 10 temp tables and draws it's final result from that. Again the code is the SAME for the SP's on both server. And the Indexes are rebuilt each night (I know! - Customers request)

    I ran the SP's from Query analyser on both servers and the Execution plan is the exact same. I was sure that was going to show up the problem from me.

    Next I tracked what was happening using profiler and I found something weird.

    The SP when run on test has NO reads or writes. However on live it has Over 1 million reads, and five writes.

    What's going on?

    Is is something to do with the tempdb

     

     

  • September 8, 2005 at 7:07 am

    #588373

    You forgot one.. are ou using the same set of parameters for both procs?

  • September 8, 2005 at 7:28 am

    #588376

    My bad. Yeah same Params. Same everything. Just completed a reboot. No effect. Do you think it's got anything to do with use of the tempdb? They are saying the system is slow, but if everything was running as slowly as this SP then is would have ground to a halt completely

  • September 8, 2005 at 7:35 am

    #588378

    Try running this : DBCC FREEPROCCACHE

    Then retest the query.

  • September 8, 2005 at 7:49 am

    #588379

    Well that worked. You're a genius.What just happened?

    Also will that return to the old speed with different params? 

     

    I suppose I'm pushing it to ask why Profiler on one server is showing 1,000,000 reads and the other server is showing none. Even though they are both now running at similar speeds

  • September 8, 2005 at 7:51 am

    #588380

    Check out parameter sniffing in the search section of this site, you'll see lots of solutions to this problem.

  • September 8, 2005 at 7:53 am

    #588382

    Cheers RGR'us

  • September 8, 2005 at 8:03 am

    #588391

    HTH.

  • September 8, 2005 at 8:50 am

    #588403

    I changed the Param and the SP goes back to the OLD poor performance levels. Head wrecking

  • September 8, 2005 at 9:00 am

    #588410

    What else did you do?

  • September 8, 2005 at 9:06 am

    #588417

    That's it. I ran DBCC DROPPROCCACHE. The reran the SP. Same speed (Slow) - Ran it again it went to normal speed. Changed One of the input param (Added and extra day to the search .i.e 20 days instead of 19) ran it - Slow again. Reran it, no change. Tried the same on the test server and it was way faster. 

    Is the SP conitinuously recompiling?

    If so - Can I stop that?  

  • September 8, 2005 at 9:07 am

    #588418

    What did you find on parmaeter sniffing?

  • September 8, 2005 at 9:14 am

    #588421

    Still looking -

  • September 8, 2005 at 9:21 am

    #588427

    That'll solve your problem.

  • September 8, 2005 at 9:39 am

    #588436

    So you think that my sp complitation keeps disappearing - Therefore forcing a recompile each time it's run?

Viewing 15 posts - 1 through 15 (of 19 total)

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