Post reply

Newby: How to tune for slow running script

  • May 17, 2006 at 9:11 am

    #112245

    I have a script that is performing poorly in our production environment. Our developers claim that it only takes 10 seconds to run on their SQL server. On our prod server, which is a larger server, it takes roughly 50 seconds. Granted, our server is under a slight load and theirs is under no load, but I hope to do what I can to find out why it takes longer to run on our server. I chose the options for "show trace" and "show execution plan" but the results didn't really mean much to me.

    Can anyone provide me with the most basic performance tuning steps I should take? I'm just looking to get started with SQL performance tuning. Thanks for any advice you can offer.

  • May 17, 2006 at 9:36 am

    #638470

    Looking at the execution plan is an excellent start.  Here's a good reference to help understand it:

    http://www.sqlservercentral.com/columnists/jsack/sevenshowplanredflags.asp

     

     

  • May 18, 2006 at 2:34 pm

    #638869

    Here's my 'short list' for tuning:

    Round I

        DBCC UPDATEUSAGE

        UPDATE STATISTICS (with FULL scan) for all tables

        exec sp_recompile for all tables

        exec sp_refreshview for all views

    Round II

        DBCC DBREINDEX

        UPDATE STATISTICS (with FULL scan) for all tables

        exec sp_recompile for all tables

        exec sp_refreshview for all views

    Round III

        Profiler

        Query Hints

     

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • May 19, 2006 at 7:42 am

    #638981

    Thanks to you both for your suggestions.  I ended up just reindexing every table in the database.  That might be kind of heavy handed but it worked.  The script now runs in only 7 seconds.

    Two things:

    1. Would you recommend setting up a job to reindex all tables once a week?

    2. After reindexing the tables the I ran the script 3 times for testing.  The first time it took 50 seconds, the 2nd and 3rd time it took 7 seconds.  Is that because something is now cached?  And will it eventually be purged from chache, causing the script to take longer to run again?

    Thanks.

  • May 19, 2006 at 8:27 am

    #639013

    >>Would you recommend setting up a job to reindex all tables once a week

    Depends on the environment (volume of Insert/Update/Delete versus Select), and what your indexes look like - does every table have a clustered index ? What FillFactor are you using on the reindex ?

    Sql Server caches data pages in memory, which explains your different results on 2nd & 3rd tests.

    To obtain accurate timings of queries that are not impacted by cache, run:

    DBCC DropCleanBuffers

    ... before each test. This removes pages from the cache. Data pages will typically stay in cache until displaced by more recently used data pages.

     

Viewing 5 posts - 1 through 4 (of 4 total)

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