Post reply

Can some one suggest

  • February 16, 2004 at 10:19 am

    #107269

    Hi

    I am having a problem in the database (SQL Server 2000) A table is growing quite rapidly and it has already reached few millions so giving timeouts on sps.This table is about to grow more.

    I was thinking of implementing some thing inteligent dont know what...So that the code accessing this table does  not change and I split the table into few tables and create something (don't know what) so that logically the code sees the same table but physically i have more than one table thus easy to manage

    Can some one help me out on this

    Vivek

  • February 16, 2004 at 11:53 am

    #494227

    Take a look at Partitioned VIEWS on BOL

     


    * Noel

  • February 16, 2004 at 2:29 pm

    #494254

    And maybe CREATE TRIGGER ... INSTEAD OF INSERT ....

    for inserting into the partitioned view while having new records going to correct uderlying table.



    Once you understand the BITs, all the pieces come together

  • February 16, 2004 at 10:37 pm

    #494304

    Before doing the tricky bits, have you profiled the statements that are timing out and run index tuning wizard against them?


    Cheers,
    - Mark

  • February 16, 2004 at 11:17 pm

    #494305

    I agree with mccork!

    Before embarking on Partioned Views, how bout ascertaining the query plans of your stored procedures to see whether Indexes are used and perhaps create Indexes where appropriate!

    Just because a table has a few million records it doesn't mean that you have to begin Horizontal Partioning!

    Kindest Regards,

  • February 17, 2004 at 12:18 am

    #494306

    profile, profile, profile and don't forget index-tuning-wizard.

    There has to be a reason for this latency.

    Maybe an other clustering index can help out. Choose it wizely, based upon your usage info.(profiler, ITW)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 17, 2004 at 4:31 am

    #494322

    When you have worked out what indexes you need if at all possible place them on a different disk to your datafiles, with your log files on yet another disk and your virtual memory file and system files on yet another disk.

    Of course you need to have the right indexes and well tuned Stored procedures and SQL in place first!

     

     

    Nigel Moore
    ======================

  • February 17, 2004 at 2:31 pm

    #494438

    If you use a composite clustered index on all your lookup columns you will get "Index Seek" lookups.  These are the fastest by far; it doesn't matter if you have millions of rows or 100s of millions.

    That's the payoff, but the trade-off is the time taken to maintain the clustered index. 

    cl

    Signature is NULL

Viewing 8 posts - 1 through 7 (of 7 total)

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