Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
 Posted - 2008-02-26 : 09:19:55
|
| 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 LnameNameSEARCH - with no partition and no indexselect * 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 SanAny suggestions and inputs would help.Thanks |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-26 : 09:30:16
|
| You don't need to partition a table when you have 5 million record, but when you have 60 -70 million records then you will outstanding performance with table partitioning accross several drive.We are doing partition for 60-70 million records and we are using SAN as well. We are outstanding partition which is done according to date. |
 |
|
|
scelamko
Constraint Violating Yak Guru
309 Posts |
Posted - 2008-02-27 : 16:23:58
|
quote:
Originally posted by sodeep
You don't need to partition a table when you have 5 million record, but when you have 60 -70 million records then you will outstanding performance with table partitioning accross several drive.We are doing partition for 60-70 million records and we are using SAN as well. We are outstanding partition which is done according to date.
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 |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-27 : 16:31:07
|
| SAN will give outstanding Performance by spreading across LUN. We are using SAN as well. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-27 : 22:26:39
|
| You'll get better performance if configure SAN properly. |
|
|
|
|
|
|
Partitioning Tables Vs San Hardware