Optimization theories

  • I have a table that is getting bigger and bigger each day.  It is growing by about 11k rows a day.  It's a pretty wide table and there is about 3 million rows in it right now.  My inserts and selects run fine, if I run UPDATE STATS WITH FULLSCAN and UPDATEUSAGE on the table.   The optimizations are taking over an hour and a half to run. 

    I know that I can save a ton of time by taking the WITH FULLSCAN off, but I want this table optimized after the insert.

    The problem is that the users are trying to hit the table while the optimizations are running.  I want to give the users full access to the table as early in the morning as possible. 

    I cannot move the job to an earlier time because I am waiting on data from another system.

    I know that I can seperate the data into two tables and then concatenate them with a view and then only optimize the smaller table.  The problem with that is that when I age off data from the smaller to the bigger table, I will have to optimize the bigger table and I am back where I started.  (I know that I could also only age off the data and optimize the larger table on off times, but I wanted to try and automate this as much as possible cause I monitor way too many jobs right now as it is.)

    Is there anyway to do a 'selective' optimization, meaning only optimize the data that has been inserted or changed?


    Live to Throw
    Throw to Live
    Will Summers

  • Yeah, kinda.  Check out UPDATE STATISTICS in books online.

    It takes a table name + index +( several stats name) as parameters.

    This allows you to focus on the important stats rather than the whole lot.  Might give you an edge?

     

  • Don't forget to run the following:

    • UPDATEUSAGE -- for the table
    • sp_ercompile -- for the table
    • sp_refreshview -- for each view on the table

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

  • Is it essential to run that after updating the stats or even reindexing the DB?

     

     

  • In this case UPDATEUSAGE may help performance because of the possibly high insert activity.

    sp_recompile for the table is a must. After updating statistics, the execution plans of any stored procedures running against that table need to be recompiled to take advantage of the new statistics hopefully making a more efficient execution plan.

    sp_refreshview is needed only if the table whose statistics are being updated are in any views, again helping.

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

  • So basically after reindexing (not updating the stats) the whole db you need to clear the proccache??

  • No - unless that is the only application database on the SQL Server. sp_recompile 'tablename' marks any stored procedure that executes against that specific table for recompile the next time that it is used. However if that is your only application database on that SQL Server then DBCC FREEPROCCACH is probably quicker and more efficient. 

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

  • Ok thanx... 2 databases : single application.

     

    FREEPROCCACHE it is.

     

    How often would you suggest running the update stats / reindex commands?

  • On 90% of my databases (the ones smaller than 100 Gb) I do STATS and USAGE nightly. On the larger databases I execute weekly. As for the reindex, It's kind of on an as needed basis - when fragmentation hits a particular 'threshhold' of pain.

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

  • ok and for ultra small databases (250 MB >> 10 users)?

  • Unless you are having performance complaints, I'd reindex weekly. As for STATS and USAGE, daily with 100% sample.

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

  • Thanx for the info... will make the necessary modification to the maintenance plan!.

  • Glad to be able to share the information.

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

Viewing 13 posts - 1 through 12 (of 12 total)

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