InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 09:43:56 GMT20http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxI have been looking for some info on A.) Putting my most recent data and indexes in the partitioned table on SSD drives. B.) You touched on using Partitioned tables to archive the oldest data. I haven't found much on these topics.Tue, 25 Sep 2012 13:22:07 GMTKTDhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxWhat happen if one of my client using standard edition as i use partition in my product?Any other way to introduce the partition concept in standard editionMon, 18 Jun 2012 00:41:17 GMTjansub07http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxHi, I am new in table partitioning and this article gave me a clear picture of how to partition a table in 2008. I hope you are writing more articles about this subject matter. Step by step a working partitioning of a table would be appreciated. Thanks again you have been a great help.AliaMon, 30 Apr 2012 10:26:05 GMTnahka163http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxThere is an error with the create partition scheme; there are not enough filegroups to support the partition function.You create more filegroups or share partitions across filegroups like:CREATE PARTITION SCHEME MyPartitionScheme ASPARTITION MyPartFunc TO([FG1], [FG2],[FG1], [FG2],[FG1],[FG2])GOTue, 09 Nov 2010 15:02:36 GMTpdbrigham-671614http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxIs there any significant performance gain to partition tables on SSD drives? Is partitioning mainly to reduce seek times in rotational disk drives? Thx.Wed, 03 Nov 2010 11:55:32 GMTjslingrowdhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxI really don't think it is the number of partitions that really matter in this case, more of how you're using them. If you're always accessing your data based on the date (which is the field you're partitioning on), then its the right setup. I assume you're using the partitioning to load data based on a SWITCH statement, correct? Also, I believe SQL 2008 SP2 now allows up to 15000 partitions.Mon, 04 Oct 2010 06:30:53 GMTchapman.timhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxNice post. I have the following situation :My table contains 2 years of history and 5 billions of records.I took the option to create partition per week. Each week is about 70 millions of rows and size on disk of each partition is about 4-5 GB.After 2 years, I have 100+ physical partitions and same number of filegroups, one per partition. I must say it runs quiet well and my table is used only to feed an olap cube.Is this the right approach ?My collegue told me that create so many partitions was not necessary and that SQL server would better perform if there were , let's say 4 or 5 big partitions of 200 GB each ...Any feedback would be appreciated.Fri, 01 Oct 2010 11:27:37 GMTFrancis Incourthttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxOf course, if you're partitioning by date (the way it was really intended), and you always include a date in the predicate, the benefits of partitioning would likely outweigh the drawbacks.Fri, 01 Oct 2010 09:21:17 GMTchapman.timhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxIts actually not as much of a benefit as you'd expect. You'd always have to include your partitioning key in your predicate so that SQL is able to identify the partition directly. From there, it STILL only performs as well as a non-partitioned table. Does not perform better. No benefit in terms of reads as one would expect.Fri, 01 Oct 2010 09:18:37 GMTchapman.timhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspx[quote][b]chapman.tim (10/1/2010)[/b][hr]Solomon is correct. Partitioning is NOT intended to increase query performance, it is intended to increase data manageability and administration. SQL 2008 did some things to make better parallel plans against partitioned tables, but it still doesn't really do much in terms of helping query performance. Often, if misused, it can hinder performance significantly.[/quote]Agreed, and if your database is of such a size that you can not reasonably do a full backup, then filegroups and data partitioning is the way to go.However, I think it will also give you performance gains as a bonus. If you partition your data sensibly, then SQL will not have to read through millions and millions of rows to satisfy the criteria, just through the partitions that are required. That's got to give a performance gain.Fri, 01 Oct 2010 09:15:51 GMTBrian Jones-401554http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxSolomon is correct. Partitioning is NOT intended to increase query performance, it is intended to increase data manageability and administration. SQL 2008 did some things to make better parallel plans against partitioned tables, but it still doesn't really do much in terms of helping query performance. Often, if misused, it can hinder performance significantly.Fri, 01 Oct 2010 08:43:16 GMTchapman.timhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspx[quote][b]Philippe Cand (5/1/2010)[/b][hr]We have a debate about partitioned tables vs the old partitioned view in SQL2008. I have a troublesome table with over 30M rows. I thought that horizontal partitioning with aligned indexes and aligned cubes partitions would significantly increase performance since most queries touch 1 partition and SQL2008 allocate many threads to these queries as opposed to SQL2005 which allocate only one thread. A Colleague of mine bought the SQL Server 2008 Bible and there is a call-out that says that table partitioning will HURT performance unless your table is a real monster table with billions of rows.[/quote]Hello. In my experience from using SQL Server 2005 Partitioning on decently-sized tables I can offer two insights:1) 30M rows is generally NOT a large table. If you are having performance problems you first need to look into the data model (maybe it can be improved by breaking the table into two one-to-one tables, etc.) and index structure (and don't forget about index rebuilds / reorgs).2) The book is correct as far as I have seen. We have implemented Partitioning and I have tested a 125 Million row table that we partitioned and made a copy that was not partitioned and the performance was about the same between the two. Although to be fair, I believe I only tested queries that hit the PK.I believe Microsoft has also stated that Partitioning is really only meant to increase performance of large data loads, such as in a Data Warehouse and was not meant to increase query performance (even though intuition says that it would). I am going to forward this thread to someone who might be able to give more details.Take care,Solomon...Fri, 01 Oct 2010 08:35:03 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxSomeone mentioned that the author probably should have broken the article into a series. I agree. It would certainly make the material more digestable (if that's a word). However, it could ultimately be down to personal preference and learning style, so this is not to criticize the author. Just my $0.02. Good intro to the feature and it's good to know it's available. Might need it one day.Fri, 01 Oct 2010 06:54:10 GMTVinceVhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxYou can achieve a similar result using Standard and views should your budget not stretch to Enterprise. Really it's horses for courses, if you databases are large enough to warrant partitioning, then the other features that come with Enterprise (for instance compression, resource governing, rebuild indexes online) are probably essential too. I phased Enterprise out here due to cost/features benefit was not stacking up (databases are typically small, largest is less than 100GB). However, now need to deal with some big data (about 30 million rows per week) and as such to be assured that we can provide the data services required, Enterprise had to be figured into the budget.Fri, 01 Oct 2010 03:33:30 GMTBrian Jones-401554http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspx[quote][b]szmulder (9/30/2010)[/b][hr]Only enterprise version support this function, too expensive! I think most company only buy standard version that's why most people never have chance to use it.[/quote]I think you'd be wrong on that one. All the companies I have worked for (4 in the last 5 years) have had multiple instances of Enterprise Edition across different versions of SQL Server.Good article, about to go into this in more detail over the next couple of days (looking at implementing on one of our PROD systems), so should be fun!Fri, 01 Oct 2010 03:16:58 GMTgrahamchttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxOnly enterprise version support this function, too expensive! I think most company only buy standard version that's why most people never have chance to use it.Thu, 30 Sep 2010 21:51:48 GMTszmulderhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxHi when i am making a partition of a table base on date time. i am geting result like this STARTDATE ENDDATE PARTITION 1 = NULL NULL PARTITION 2 =2010-03-02 00:02:22.000 2010-03-31 23:59:33.000 PARTITION 3 =2010-04-01 00:01:06.000 2010-06-30 23:59:22.000 PARTITION 4 =2010-07-01 00:00:40.000 2010-09-08 03:25:23.000SO CAN U PLEASE HELP ME I WANT THE RESULT LIKE THIS: STARTDATE ENDDATE PARTITION 1 =2010-03-02 00:02:22.000 2010-03-31 23:59:33.000 PARTITION 2 =2010-04-01 00:01:06.000 2010-06-30 23:59:22.000 PARTITION 3 =2010-07-01 00:00:40.000 2010-09-08 03:25:23.000PARTITION 4 = NULL NULLTue, 07 Sep 2010 16:26:05 GMTsandeepyalhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxWe have a debate about partitioned tables vs the old partitioned view in SQL2008. I have a troublesome table with over 30M rows. I thought that horizontal partitioning with aligned indexes and aligned cubes partitions would significantly increase performance since most queries touch 1 partition and SQL2008 allocate many threads to these queries as opposed to SQL2005 which allocate only one thread. A Colleague of mine bought the SQL Server 2008 Bible and there is a call-out that says that table partitioning will HURT performance unless your table is a real monster table with billions of rows. As a consequence this colleague advise against table partitioning and instead advise to use the old technique of breaking down the table in multiple individual tables, then create a partitioned view on the top of this. I am a little confused since I cannot find any other advise against table partitioning in SQL2008. What is your take? Thanks.Sat, 01 May 2010 11:30:00 GMTPhilippe Candhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspx[quote][b]tomtait415 (9/23/2009)[/b][hr]Does any one know if partitioning on two fields is possible? I guess worst case I'd could combine two fields into one and do it that way but then I have to have all of the TSQL written with the concatenation...pretty ugly. I haven't been able to find anything on this on the web. I'd like to partition a table by geography and year. Thanks for any help.[/quote]AFAIK the only way to partition on multiple columns is to use a computed column.Thu, 14 Jan 2010 15:38:10 GMTDon Dayhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxDoes any one know if partitioning on two fields is possible? I guess worst case I'd could combine two fields into one and do it that way but then I have to have all of the TSQL written with the concatenation...pretty ugly. I haven't been able to find anything on this on the web. I'd like to partition a table by geography and year. Thanks for any help.Wed, 23 Sep 2009 12:06:43 GMTtomtait415http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxDo you plan to update your article with the correct t-sql?Thu, 17 Sep 2009 12:41:01 GMTrobert rogers-466427http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxVery nice article. Easy to read and very instructive. Thanks.Mon, 04 May 2009 08:36:33 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxGreat article presented with very easy to follow and implement approach.Rashid Akhter, PMPProject Manager, Scrum MasterThu, 30 Apr 2009 15:08:00 GMTrashid.inboxhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxfor 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%.ThanksWed, 29 Apr 2009 22:27:31 GMTSID-746731http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxYes I meant a nonclustered index.Wed, 29 Apr 2009 22:18:50 GMTGabriel Phttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxYou mean non partition index. I did created cluster index ( non allign) on partition table and after that Partition table became non partition table.Wed, 29 Apr 2009 22:14:18 GMTSID-746731http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxAligned index (separate indexes split by partition):[code]create index ix_pt_index on partitioned_table (indexed_value)ON [ps_PartitionScheme](partition_key) --Name of partition scheme[/code]Non-aligned index (one index that covers all values across all partitions):[code]create index ix_index on partitioned_table (indexed_value)ON [FG_Index] --Name of filegroup[/code]Wed, 29 Apr 2009 22:06:03 GMTGabriel Phttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxCould 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 tableuse mydbgo 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 tableuse mydbgo 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.Wed, 29 Apr 2009 21:59:11 GMTSID-746731http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxI expect you didn't expect anyone to try your code. If you had tested it, you would find it generates errors.Wed, 29 Apr 2009 13:55:11 GMTdon_goodmanhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspx[quote][b]SID (4/29/2009)[/b][hr]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.;-)[/quote]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.Wed, 29 Apr 2009 13:37:08 GMTGabriel Phttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxCould 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.;-)Wed, 29 Apr 2009 11:52:28 GMTSID-746731http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxGreat article and easy to read. Done it in 2005 last year but had not seen a significant increase in performance.Wed, 29 Apr 2009 08:29:24 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxGreat 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 JonesWed, 29 Apr 2009 07:02:17 GMTBrian Jones-401554http://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxHave 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.Wed, 29 Apr 2009 04:54:55 GMTGabriel Phttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxAlot info. Some of the things have really changed in SQL Server 2008. Wed, 29 Apr 2009 04:48:01 GMTAtif-ullah Sheikhhttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxHmmm 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!:cool:Wed, 29 Apr 2009 03:44:10 GMTDugihttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxA 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) ASRANGE LEFT FOR VALUES (1000, 2000, 3000, 4000, 5000);CREATE PARTITION SCHEME MyPartitionScheme ASPARTITION Mypartfunc TO([FG1], [FG2])GOThe 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.Wed, 29 Apr 2009 02:34:02 GMTPaul Williamshttp://www.sqlservercentral.com/Forums/Topic706495-1386-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/partition/64740/">Partitioning in SQL Server 2008</A>[/B]Wed, 29 Apr 2009 00:31:20 GMTMuhammadSiddiqi