August 25, 2014 at 11:48 am
Hi,
We have a database and have 6-7 growing tables. All the tables have Primary and foreign key relation. I want to do partition based on the date column.
I need 3 partitions
1 partition has to hold present data
second partition need to hold the previous year data (SAS storage)
Third parition need to hold all the old data and need to be in the archive database
I understand that first we need to disable the constraints (Indexes PK & FK)
then create partition function and partition schema
Then Create the Constraints again
Is any point am I missing?
August 25, 2014 at 12:05 pm
Please state WHY you want to implement partitioning. What problems will it help you address? Partitioning is a COMPLEX subsystem with lots of caveats, provisos, limitations, gotchas, etc. I have seen countless clients and forum posters pursue partitioning (often with BAD results) when they didn't need it in the first place or it wouldn't actually help them address an actual problem they were having.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 25, 2014 at 1:50 pm
Hi Kevin,
Thanks for the reply.
For now we don't have any issues but just now data is growing. We are estimating it will grow very large in the future. We do generate reports from OLTP. So we thought of implementing partitioning to maintain the data and retrieve the data faster.
August 26, 2014 at 8:00 am
ramana3327 (8/25/2014)
Hi Kevin,Thanks for the reply.
For now we don't have any issues but just now data is growing. We are estimating it will grow very large in the future. We do generate reports from OLTP. So we thought of implementing partitioning to maintain the data and retrieve the data faster.
As I suspected. Table Partitioning was not built into SQL Server to make queries run faster. It was added as a feature to improve ETL performance and options and maintenance on data warehouse systems. It CAN make your queries run faster, but only if they are a) coded properly and b) have appropriate filters to allow for significant partition elimination. If you do NOT get partition elimination your plan can actually make queries run SLOWER since you will routinely be hitting those slower disks where you put the "older stuff".
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 26, 2014 at 10:54 am
ramana3327 (8/25/2014)
Is any point am I missing?
Yes. That you can't partition across databases, and partitioning across filegroups will involve time consuming maintenance to move files to slower storage and that fast switching only works when you're switching within a filegroup.
Are you sure you need it?
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
August 27, 2014 at 6:25 am
Yes.
The problem is that tables have foreign key constraints. We don't have any proper partition key. So now we created two additional columns on those tables created date & Updated date. These two columns automatically populates with triggers inserted data & updated data and for previous we use the dummy data.
Now we decided to partition those tables and take create-date as partition key.
We want to do partition that one partition holds current year data all the time and another partition holds previous year data and last partition holds all the historical data.
I do have 1 doubt about this
If the table has Primary key on ID1 column and has the clustered Index also it is child table to another table by using ID2 column. If I want to do partition on created date. How can I do?
I am thinking that
First I need to disable that primary key and foreign key constraints and then do the partition then created clustered index on create date and make the id column as primary but creates make that it creates non-clustered index then recreate the foreign keys.
Thanks,
August 27, 2014 at 6:56 am
ramana3327 (8/27/2014)
Yes.The problem is that tables have foreign key constraints. We don't have any proper partition key. So now we created two additional columns on those tables created date & Updated date. These two columns automatically populates with triggers inserted data & updated data and for previous we use the dummy data.
Now we decided to partition those tables and take create-date as partition key.
We want to do partition that one partition holds current year data all the time and another partition holds previous year data and last partition holds all the historical data.
I do have 1 doubt about this
If the table has Primary key on ID1 column and has the clustered Index also it is child table to another table by using ID2 column. If I want to do partition on created date. How can I do?
I am thinking that
First I need to disable that primary key and foreign key constraints and then do the partition then created clustered index on create date and make the id column as primary but creates make that it creates non-clustered index then recreate the foreign keys.
Thanks,
Let me get this straight - you added TWO columns, likely 16 bytes for full date time, AND added triggers to populate them (why not populate them with whatever does the INSERT/DELETE??), JUST so you could implement partitioning to make your stuff run faster?? Zowie - I am just speechless! :blink:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 27, 2014 at 7:19 am
Are you sure you need partitioning?
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
August 27, 2014 at 8:52 am
Yes Kevin,
We don't have any auditing. So we created triggers for those tables for those tables and inserting that inserted and modified date into that columns.
August 27, 2014 at 8:59 am
Yes GailShaw.
I need to do the partition. Do you think it will degrade the performance?
What is your suggestion?
Thanks,
August 27, 2014 at 11:05 am
ramana3327 (8/27/2014)
Yes GailShaw.I need to do the partition. Do you think it will degrade the performance?
What is your suggestion?
Thanks,
You said you didn't have problems now but data is growing and you want partitioning to make things faster. I submit that you, like SOOO many others that have tried to go down the table partitioning road for such a reason, simply need to TUNE WHAT YOU HAVE (schema, indexing, maintenance practices, hardware, SQL Server, windows, IO subsystem, code, etc, etc) and then you will have ZERO "need" for partitioning!!! 😎
Now, if you are trying to run 1TB+ of data on a $5000 server you may also need "bigger hardware", but that is a simple and cheap thing to deal with if it is really required. But that should only be considered AFTER you have done the tuning.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 27, 2014 at 2:47 pm
Mainly we need this for archiving purpose.
August 27, 2014 at 3:07 pm
ramana3327 (8/27/2014)
Mainly we need this for archiving purpose.
Again, why?
You can't partition across databases, so the 'old stuff into the archive database' can't be done with partitioning. Fast switching works when the table and the partition are in the same filegroup, which means you can't use just fast switching to move data to a slower filegroup. You'll need to add in extra steps. Partitioning won't automatically move old data to a different filegroup. If you try merging two partitions that both have data in them, you've looking at long running operations (the rows have to be moved) with nasty locks, especially if those two partitions are not in the same filegroup.
It doesn't look like the solution solves the problem. Not without creating a whole pile more problems.
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
September 8, 2014 at 7:35 am
Hi,
When I generate the script for partition from GUI it is finally dropping the Index. Why it is dropping the Index?
USE [Test]
GO
BEGIN TRANSACTION
ALTER TABLE [dbo].[IsDeletedTest] DROP CONSTRAINT [PK_IsDeletedTest];
ALTER TABLE [dbo].[IsDeletedTest] ADD CONSTRAINT [PK_IsDeletedTest] PRIMARY KEY NONCLUSTERED
(
[IsDeletedTestID] ASC
) WITH (
PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF
, ONLINE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY]
CREATE CLUSTERED INDEX [ClusteredIndex_on_IsDeletedPS_635194638000795374] ON [dbo].[IsDeletedTest]
(
[IsDeleted]
) WITH (
SORT_IN_TEMPDB = OFF
, DROP_EXISTING = OFF
, ONLINE = OFF
)
ON [IsDeletedPS]([IsDeleted])
DROP INDEX [ClusteredIndex_on_IsDeletedPS_635194638000795374] ON [dbo].[IsDeletedTest]
COMMIT TRANSACTION
September 8, 2014 at 8:07 am
Never, ever, EVER use the SSMS GUI to do DDL activities!!! It is FULL of issues/bugs. You could possibly use it to do what you think is right and then Generate Script from it, but you should really know what you are doing to make sure it isn't screwing you or doing something completely stupid/wrong.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply