June 4, 2013 at 9:58 pm
I have a database where most of the tables have an integer field say "SetId" denoting a batch of data.
If a new SetId is created all these tables will get data related to the new SetId.
SetId governs data retention, purging, it also forms part of filtering in many user queries on these tables.
I am thinking of partitioning all these tables on the SetID column, which I am sure would be beneficial for performance and data maintenance.
My question is, do I use a common partition function and partition scheme to partition all the tables on SetId, or is it more advisable to use separate partition functions and schemes for each tables?
June 7, 2013 at 8:28 am
hi,
I can't give you a full answer but maybe some hints.
In general you can use the same partitioning schema and function for all tables. The question is what happens if you have to update the scheme and the function?
In our system we have some large table using the same scheme and functions. When adding a new partition key it takes a while for all tables to be locked so that the function and scheme can be modified. These locks are, as far as I know, table locks.
Depending on number and size of the tables the locks might take quite a few minutes. You have to determine how much these locks are allowed to be without disturbing your system.
On the other hand side, having only 1 scheme and function is very convenient for maintening the system.
Hope this helps at least a little bit...
June 8, 2013 at 8:34 pm
The one time I have seen someone use one scheme and function for many tables they inevitably regretted doing so when it came time when they needed to change how one of the tables stored its data.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 11, 2013 at 3:28 pm
Thank you WolfgangE and opc.three for your replies.
I think I will use a mixed approach where I'll use individual partition schemes for larger high impact tables, while for smaller ones I'll use a common partition function and schemes.
June 11, 2013 at 4:14 pm
Yusuf Ali Bhiwandiwala (6/4/2013)
I am thinking of partitioning all these tables on the SetID column, which I am sure would be beneficial for performance and data maintenance.
Maintenance yes, performance probably not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2013 at 4:38 am
Thanks Gail for your reply, but what do you think about partitition elimination for performance benefit?
Also, I would really like to know your view on the actual question of the thread, of having a single partition function and scheme common across tables or having one each for each table?
June 20, 2013 at 4:43 am
Yusuf Ali Bhiwandiwala (6/20/2013)
Thanks Gail for your reply, but what do you think about partitition elimination for performance benefit?
Fine, when it works and when it's better than what the optimiser could come up with without partitioning
Also, I would really like to know your view on the actual question of the thread, of having a single partition function and scheme common across tables or having one each for each table?
Depends on your design, on why you're partitioning, on the data loads and data elimination you're doing across those tables, on the columns that exist in those tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2013 at 10:41 am
GilaMonster (6/20/2013)
Yusuf Ali Bhiwandiwala (6/20/2013)
Thanks Gail for your reply, but what do you think about partitition elimination for performance benefit?Fine, when it works ...partitioning
That's a very good point. Partitioning can even decrease your performance. That's the case if you cannot realize the partition elimination in your queries.
Example: You have a table containing 10 partitions. You have a query that produces a clustered index seek. If you cannot serve a partition key, the optimizer will not only make 1 lookup but 10 lookups.
Not knowing the partition key can happen quite often when joining tables. Depending on your data model you might or might not have the partition key of a table you are joining to.
March 15, 2018 at 8:45 am
You can use the same partition function and scheme for several table. This is usefull if you work on all table as a "set", you only need to work on one partition function and everything will be in phase.
On the other side, when you alter the partition, the procedure needs to lock all table that have that partition linked to them. This is a fast action, but if you have a system that has heavy load and don't have rest time to do this, you might end up with dead locks with result as your partition will not be created or removed....
The situation will be more problematic if you have more than 16 cpu on your server as Sql server will use "partition" lock and grant more granular lock and so add more chance to get a dead lock...
If you have many partitions, then you will have a lot of work to manage all the objects and see that every partitions is switched and you have enough partitions empty,...
In either case, with big databases, the work is consequent. It also depends on the granularity of your partition... We use the hour, so we only have 1,5 year possible if we want to keep the data and so the management of such table is trickier...
As for partition elimination, as you can see in some place, if you don't cast your parameters in the where clause (up to sql 2014, after I didn't test yet), sql server will use parameter sniffing and so will not use partition elimination. In small architecture, you will not notice it as it can be fast, when data begins to be big, then the scan of the whole table begins to be a problem...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply