February 26, 2008 at 7:19 am
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
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
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
SQL Noob (2/26/2008)
two different issuesSAN 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
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
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