SQL Server Performance

To partition or not

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by kkt, Nov 23, 2006.

  1. kkt New Member

    Hello guys,

    What is your opinion on the following:

    1) I have a rather large table (for my definition of large). Approx 5 million rows
    2) There is one RAID - 10 disk where the database with the table is located (also log file and temp db is there)
    3) My table is both OLTP and also a reporting table. Access patterns vary but in most cases records are inserted on a monotonically increasing manner (clustered index is a datetime field), and select queries usually ask for the later two or three months (think of it as a typical sales table). This is not always the case. There are some consolidation queries (not many) during the day that may ask for arbitrary periods of data
    5) Assuming I CANNOT add another disk and also I cannot create an archiving table with my historical data

    Do you think that there will be any performance gains (not administration gains) if I partition the table (say per month - I have three years worth of data)?

    Thanx for your advicde

    KKT
  2. MohammedU New Member

    There is no choice of adding additional disk then you want to create partition on the same disk.
    I don't see any benifit using the table partitioning... on top of that you don't want to archive it... Partional will help in archiving too...

    In my view 5 mill. row table is small table as long as it is BLOB free table.
    What is the table size in MB?



    Mohammed U.
  3. kkt New Member

    quote:Originally posted by MohammedU

    There is no choice of adding additional disk then you want to create partition on the same disk.

    [KKT]Exactly.

    I don't see any benifit using the table partitioning... on top of that you don't want to archive it... Partional will help in archiving too...

    [KKT] Well this is the point of the question. I was wondering if partitioning the table may divert queries to smaller chunks of data that will be read faster. Archiving is not an option as I have lots of queries that will know be diverted to a new table (the archiving table). This is a lot of code that I have to change. I do not understand the concept of partitioning very well yet. I've only recently started investigating. Having a different disks for partitions is an obvious gain in parallelism, but my customer does want to buy disks (you've heard this before). So, I was wondering if I will have any benefits anyway.

    In my view 5 mill. row table is small table as long as it is BLOB free table.
    What is the table size in MB?

    [KKT] Table size is about 3GB. And it is BLOB free. Mostly decimals, nvarchars, integers and some uniqueidentifiers. PK is datetime



    Mohammed U.

    KKT

Share This Page