April 29, 2009 at 12:31 am
Comments posted to this topic are about the item Partitioning in SQL Server 2008
April 29, 2009 at 2:34 am
A very nice interesting and easy to read article on partitioning.
There are a couple of errors in the syntax though with regards to creating a partition function:
CREATE PARTITION FUNCTION partfunc (int) AS
RANGE LEFT FOR VALUES (1000, 2000, 3000, 4000, 5000);
CREATE PARTITION SCHEME MyPartitionScheme AS
PARTITION Mypartfunc TO([FG1], [FG2])
GO
The partition function is named 'partfunc' though the partition scheme references partition function 'Mypartfunc'. Additionally, there are less filegroups set in the partition scheme than there are partitions in the partition function. I guess these are just typos.
I would also have split the article into several articles over a series as most people prefer short articles to absorb and try out the features themselves. Splitting, Merging, Switching and the rolling window scenario are detailed topics in their own right to which only a brief mention has been made in this article. Maybe a detailed follow up ?
Overall though I thought an interesting and worthwhile introductory piece to partitioning. Thanks.
Paul R Williams.
April 29, 2009 at 3:44 am
Hmmm if you compare with SQL Server 2005 here in SQL Server 2008 now is much easy how to partitioning because you have wizards, all what you need to do is to have logic what do you want to do with large data with partitioning!
Anyway, all these SQL users that have experience with partitioning in SQL Server 2005 they will do partitioning in SQL Server 2008 very easy and in brilliant way, but sure if they know the reason and steps why where and how to do partitioning!
😎
April 29, 2009 at 4:48 am
Alot info. Some of the things have really changed in SQL Server 2008.
April 29, 2009 at 4:54 am
Have they created a GUI for you to see how your data is split among partitions in SSMS 2008? Before I just used a custom RDL report in SSMS 2005 for querying all the system views pertaining to objects and partitioning.
April 29, 2009 at 7:02 am
Great article - glad you mentioned that horizontal partitioning is only available in the enterprise version.
However, I think it is worth mentioning that vertical partitioning is available in all flavours of SQL and this can help considerably with performance. For those who are not familiar with this, vertical partitioning allows you to split a database down into separate FILEGROUPs, which can then be spread over different physical file systems. This allows you to specify where tables reside, so keeping heavily accessed tables apart from each other and leveraging added performance gained from the separated underlying filesystems. In addition, you can keep your indexes in separate FILEGROUPS - again away from each other and the data, adding even more potential performance. The downside is that you can only specify the whole table or index to belong to a particular FILEGROUP, though the indexes can reside on different FILEGROUPS from each other and the data.
Although vertical partitioning will not give you the gains that horizontal does, it will give significant gains over a single homed database structure.
Also, for those with non Enterprise versions, you can still use partitioned views which will give you the same benefit as horizontal partitioning, but may involve a bit more complexity.
Regards,
Brian Jones
April 29, 2009 at 8:29 am
Great article and easy to read. Done it in 2005 last year but had not seen a significant increase in performance.
SQL DBA.
April 29, 2009 at 11:52 am
Could you tell me how to over come performance issue when using order by clause partition table.I have created couple partition tables with partition indexes and when I ran query including order by clause it seek index but sort 92% and same query without order by clause it doesn't sort and there is no performance issue.If I run same query with order by clause on same non partition table it works perfect.Any comment please.;-)
April 29, 2009 at 1:37 pm
SID (4/29/2009)
Could you tell me how to over come performance issue when using order by clause partition table.I have created couple partition tables with partition indexes and when I ran query including order by clause it seek index but sort 92% and same query without order by clause it doesn't sort and there is no performance issue.If I run same query with order by clause on same non partition table it works perfect.Any comment please.;-)
I don't understand your issue exactly, but remember if you are ordering on a partitioning table, you are doing a UNION ALL between each partition, then sorting. So if your index is aligned with the partition scheme, it will negate any sorting benefits that you had with a non-partitioned table. You can create a non-aligned index on that field that should give you better performance - however non-aligned indexes would prevent you from doing any partition switching.
April 29, 2009 at 1:55 pm
I expect you didn't expect anyone to try your code. If you had tested it, you would find it generates errors.
April 29, 2009 at 9:59 pm
Could you tell me what do you mean by create a non-aligned index on that field. Is there any example.
I used following code:
--------------This took 6 minutes on partition table
use mydb
go
declare @P1 char(9)
set @P1=' '
SELECT top 1000 a.t_hrea,a.t_bpid,a.t_bprt,a.t_dtbl,a.t_dtrl,a.t_erdt,a.t_lobl,a.t_lorl,a.t_opon,a.t_orno,a.t_pono,a.t_pstc,a.t_rlam,a.t_rlty,a.t_sqnb
FROM dbo.ttdsls421900_par a
WHERE (a.t_orno > @P1) ORDER BY 10,11,15,1,4 OPTION (FAST 5)
--------------This took seconds on non-partition table
use mydb
go
declare @P1 char(9)
set @P1=' '
SELECT top 1000 a.t_hrea,a.t_bpid,a.t_bprt,a.t_dtbl,a.t_dtrl,a.t_erdt,a.t_lobl,a.t_lorl,a.t_opon,a.t_orno,a.t_pono,a.t_pstc,a.t_rlam,a.t_rlty,a.t_sqnb
FROM dbo.ttdsls421900 a
WHERE (a.t_orno > @P1) ORDER BY 10,11,15,1,4 OPTION (FAST 5)
---------------------
If I remove ORDER BY Clause from code and run against partition table it will give same result as non partition table.
April 29, 2009 at 10:06 pm
Aligned index (separate indexes split by partition):
create index ix_pt_index on partitioned_table (indexed_value)
ON [ps_PartitionScheme](partition_key) --Name of partition scheme
Non-aligned index (one index that covers all values across all partitions):
create index ix_index on partitioned_table (indexed_value)
ON [FG_Index] --Name of filegroup
April 29, 2009 at 10:14 pm
You mean non partition index. I did created cluster index ( non allign) on partition table and after that Partition table became non partition table.
April 29, 2009 at 10:18 pm
Yes I meant a nonclustered index.
April 29, 2009 at 10:27 pm
for example when I creted cluster index, partition table lost the partition scheme and becomes non partion.
And when I created just the non cluster index , execution plan doesnt take this index and keeps go to sort 92%.
Thanks
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply