Post reply

Partitioning tables Vs San Hardware

  • February 26, 2008 at 7:19 am

    #120104

    Guys,

    I am trying to use table partition feature from Sql Server 2005 enterprise edition.

    I have Names table with columns FNAME, LNAME and DISPLAYNAME (concatenation of FNAME and LNAME) which I partitioned across 2 drives and 4 file groups based on the below criteria.

    CREATE PARTITION FUNCTION pfNameRange(varchar(200))

    AS RANGE RIGHT FOR VALUES ('F', 'I', 'S');

    Currently there are 5 mill rows in this partitioned tables - partitioned table has clustered index on ID (identity property) and LNAME.

    I also created another table with the same data without partition on the table.

    When I run the following query I get the same response time of 10secs from both tables.

    Names - partitoned table with clustered index on ID and Lname

    NameSEARCH - with no partition and no index

    select * from names where lname = 'smith'

    select * from namesearch where lname = 'smith'

    Is it safe to assume that if the data files are on San it doesnt give any advantage of table paritioning?

    How can paritioning be made effective with data files on San?

    Any suggestions and inputs would help.

    Thanks

  • February 26, 2008 at 7:29 am

    #781980

    two different issues

    SAN will let you manage your storage centrally and you can replicate the data to another SAN at the storage level without worrying about mirroring or log shipping

  • February 27, 2008 at 9:50 am

    #782647

    Data partitioning gives you added advantage on the table that is heavy. You need to place the datafiles on different drives on the disk to get the performance benefit.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • February 27, 2008 at 2:22 pm

    #782881

    SQL Noob (2/26/2008)

    two different issues

    SAN will let you manage your storage centrally and you can replicate the data to another SAN at the storage level without worrying about mirroring or log shipping

    What I am trying to get at is with SAN data partitioning does not make the retreival faster since SAN is central storage and drive letters assigned are for Windows OS - but it is still a central storage. When data patitioned on SAN it is going to same location and not different disks - I am not sure how effective data partitioning is when SAN is used.

    Thanks

  • February 27, 2008 at 2:45 pm

    #782890

    SAN's are large sets of disks. How they're organized is a question for whoever designed the SAN solution for your org.

    Partitioning your tables may or may not send them to the same disks. If you partition across multiple files, and the files are on any number of SAN LUN's , you may very well be utilizing more disks, and thus seeing a performance gain.

    There's no definite answer to your question given what we know about your setup (i.e. very little).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • February 27, 2008 at 4:00 pm

    #782929

    Hi Am,

    Table paritioning and storage are two different, but related concepts. As you will know, partioning a table is logically slicing your data for improved data access performance and growth management ideally using separate disk(s) to support partition files. Running a query against a partioned or non-partitioned table on the same storage media (e.g. SAN) with same volume of data wouldn't deviate too much in runtime, but with significant data volumes, you would see a difference. You will need to make sure your query filters include the partioning key to make further gains in performance using partitioned table(s).

    Thanks,

    Phillip Cox

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

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