April 30, 2012 at 2:43 pm
If you want the index partitioned on a partition scheme, then the name of the partition scheme, it you want it unpartitioned in a filegroup, then the name of the filegroup.
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
April 30, 2012 at 2:47 pm
Nevermind - one clustered index per table only - back to basics. My bad Gail. Thanks again...
April 30, 2012 at 2:50 pm
Huh?
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
April 30, 2012 at 2:57 pm
Here is what I have successfully parsed...
CREATE UNIQUE CLUSTERED INDEX PK_BW_RECORD_EVENT_NEWFORMAT2
ON dbo.BW_RECORD_EVENT_NEWFORMAT2 (ID, GPS_RECORD_DATE)
WITH (DROP_EXISTING = ON) ON [RecordsPartitionScheme]
GO
So - this will rebuild the Clustered Index / Primary Key be to a Unique Clustered Index / Primary Key?
It will be on the previously used Pkey column, along with the Partition Key column (as defined in the Partition Function),
and lastly is being told to replace the existing Clustered Index so that the Non-Clustered Indexes do not get rebuilt again during this operation, and spreads the index completely over all the Filegroups that were defined in the Partition Scheme...
Do I have that all correct?
April 30, 2012 at 3:03 pm
Rich Yarger (4/30/2012)
So - this will rebuild the Clustered Index / Primary Key be to a Unique Clustered Index / Primary Key?
Don't understand, A primary key is always enforced by a unique index, clustered or nonclustered
It will be on the previously used Pkey column, along with the Partition Key column (as defined in the Partition Function),
If you are changing the definition of the clustered index (different columns), you cannot use create ... with drop existing because it's the primary key. It only works if the columns remain the same and all you're doing is moving the index.
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
April 30, 2012 at 3:08 pm
GilaMonster (4/30/2012)
Rich Yarger (4/30/2012)
So - this will rebuild the Clustered Index / Primary Key be to a Unique Clustered Index / Primary Key?Don't understand, A primary key is always enforced by a unique index, clustered or nonclustered
It will be on the previously used Pkey column, along with the Partition Key column (as defined in the Partition Function),
If you are changing the definition of the clustered index (different columns), you cannot use create ... with drop existing because it's the primary key. It only works if the columns remain the same and all you're doing is moving the index.
Sorry Gail - got confused for a moment. Yes - I understand the uniqueness...
The current index on the partitioned table looks like this...
Clustered Index/PKey
USE [V3_2]
GO
/****** Object: Index [PK_BW_RECORD_EVENT_NEWFORMAT2] Script Date: 04/30/2012 13:52:56 ******/
ALTER TABLE [dbo].[BW_RECORD_EVENT_NEWFORMAT2] ADD CONSTRAINT [PK_BW_RECORD_EVENT_NEWFORMAT2] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[GPS_RECORD_DATE] 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 [NC1]
So - my code above should work - yes?
April 30, 2012 at 3:12 pm
Yes it should (columns are the same, ordering is the same)
Do feel free to test out on a dev server first.
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
April 30, 2012 at 3:15 pm
I am - quasi-DEV testing...Ish...It's a long story, but thank you so very much Gail. I will report my results back once this beast of a process finishes.
Rich
April 30, 2012 at 3:21 pm
Blah....
I get the following error (after having dropped and recreated the new PARTITION FUNCTION and PARTITION SCHEME)...
Msg 2726, Level 16, State 1, Line 2
Partition function 'RecordsPartitionFunction' uses 1 columns which does not match with the number of partition columns used to partition the table or index.
It's like it doesn't recognize one of the columns in the Index, but they are both there.
:w00t:
EDIT: This error occurs when I try to run the CREATE UNIQUE CLUSTERED INDEX script.
April 30, 2012 at 3:50 pm
What's the partition function, what's the exact code you're running?
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
April 30, 2012 at 4:06 pm
GilaMonster (4/30/2012)
What's the partition function, what's the exact code you're running?
USE [V3_2]
GO
/****** Object: PartitionFunction [RecordsPartitionFunction] Script Date: 04/30/2012 14:23:56 ******/
CREATE PARTITION FUNCTION [RecordsPartitionFunction](datetime)
AS RANGE LEFT FOR
VALUES (N'2011-04-30T23:59:59', N'2011-05-31T23:59:59', N'2011-06-30T23:59:59', N'2011-07-31T23:59:59', N'2011-08-31T23:59:59', N'2011-09-30T23:59:59', N'2011-10-31T23:59:59',
N'2011-11-30T23:59:59', N'2011-12-31T23:59:59', N'2012-01-31T23:59:59', N'2012-02-29T23:59:59', N'2012-03-31T23:59:59', N'2012-04-30T23:59:59', N'2012-05-31T23:59:59',
N'2012-06-30T23:59:59', N'2012-07-31T23:59:59', N'2012-08-31T23:59:59', N'2012-09-30T23:59:59', N'2012-10-31T23:59:59', N'2012-11-30T23:59:59', N'2012-12-31T23:59:59',
N'2013-01-31T23:59:59', N'2013-02-28T23:59:59', N'2013-03-31T23:59:59')
April 30, 2012 at 4:09 pm
Is it because the columns for the table and index are using ID and the GPS_RECORD_DATE, and the function is only listing the date? If so - that is fine, but then what do I use to give ranges for the ID's in the Function. I'll have no idea what those ID's will be at those given date ranges.
:blink:
EDIT: Grrrrrr
April 30, 2012 at 4:21 pm
Hey Rich, how's it going? Graduated to partitions since I last saw you, congratz. 🙂
What Gail (and a few of us) really need is a consolidated script that you would run if you were going end to end with this in one shot. Scheme, index, function, etc. It's broken up right now and I'm not sure what's current and what isn't. This would help us tremendously.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 30, 2012 at 4:27 pm
I need the EXACT create index statement that you ran, the one that failed.
p.s. If you're partitioning on dates, partition RIGHT, not LEFT. Partitioning left with dates is horrid, and your partition function allows for some dates to slip into the wrong partition, because there are valid dates between 2011-05-31 23:59:59 and 2011-06-01 00:00:00.
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
April 30, 2012 at 4:34 pm
Hi Kraig! Yes - big ol db and partitioning for one table that has about 500 million rows in it. Here is a consolidated script, and my apologies for having broken it up to begin with...
FUNCTION, SCHEME, CURRENT INDEX, PROPOSED DDL TO RECREATE INDEX AND HOPEFULLY NOT BLOW EVERYTHING UP!!!
USE [V3_2]
GO
/****** Object: PartitionFunction [RecordsPartitionFunction] Script Date: 04/30/2012 14:23:56 ******/
CREATE PARTITION FUNCTION [RecordsPartitionFunction](datetime)
AS RANGE LEFT FOR
VALUES (N'2011-04-30T23:59:59', N'2011-05-31T23:59:59', N'2011-06-30T23:59:59', N'2011-07-31T23:59:59', N'2011-08-31T23:59:59', N'2011-09-30T23:59:59', N'2011-10-31T23:59:59',
N'2011-11-30T23:59:59', N'2011-12-31T23:59:59', N'2012-01-31T23:59:59', N'2012-02-29T23:59:59', N'2012-03-31T23:59:59', N'2012-04-30T23:59:59', N'2012-05-31T23:59:59',
N'2012-06-30T23:59:59', N'2012-07-31T23:59:59', N'2012-08-31T23:59:59', N'2012-09-30T23:59:59', N'2012-10-31T23:59:59', N'2012-11-30T23:59:59', N'2012-12-31T23:59:59',
N'2013-01-31T23:59:59', N'2013-02-28T23:59:59', N'2013-03-31T23:59:59')
USE [V3_2]
GO
/****** Object: PartitionScheme [RecordsPartitionScheme] Script Date: 04/30/2012 14:24:26 ******/
CREATE PARTITION SCHEME [RecordsPartitionScheme]
AS PARTITION [RecordsPartitionFunction]
TO ([FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG8], [FG9], [FG10], [FG11], [FG12], [FG13], [FG14], [FG15], [FG16], [FG17], [FG18], [FG19], [FG20], [FG21], [FG22],
[FG23], [FG24], [PRIMARY])
--THIS IS THE CURRENT INDEX ON THE PARTITIONED TABLE
USE [V3_2]
GO
/****** Object: Index [PK_BW_RECORD_EVENT_NEWFORMAT2] Script Date: 04/30/2012 15:26:37 ******/
ALTER TABLE [dbo].[BW_RECORD_EVENT_NEWFORMAT2] ADD CONSTRAINT [PK_BW_RECORD_EVENT_NEWFORMAT2] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[GPS_RECORD_DATE] 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 [NC1]
--PROPSED RECREATION SCRIPT FOR THE PARTITIONED TABLE...
USE V3_2;
GO
CREATE UNIQUE CLUSTERED INDEX PK_BW_RECORD_EVENT_NEWFORMAT2
ON dbo.BW_RECORD_EVENT_NEWFORMAT2 (ID, GPS_RECORD_DATE)
WITH (DROP_EXISTING = ON) ON [RecordsPartitionScheme]
GO
Some thoughts...
- The original function, and index were not done correctly - IMHO. You can see that by the way the Function only has the datetime type, and the index is pointing to a Filegroup that it never should have been put into.
- I am just needing to do 2 things
- 1, rebuild the FUNCTION and SCHEME to properly reflect the Filegroups our new PROD system will use (which previously the SCHEME did not), and...
- 2, Recreate the index to have it spread across the Filegroup range that has been defined in the SCHEME. Thereby not having it in the Filegroup called 'NC1" any longer.
I am thinking that since the FUNCTION is only showing the datetime values that this is what is causing the error?
Not sure, but that is why I turned to you guys. I need to do this quick though, as this table is huge, and I am running out of time! Thanks no matter what though - I really appreciate you guys always helping me out!
🙂
Rich
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply