Bu yazımda Primary Key GUID olan büyük bir tabloyu PARTITION’lara nasıl bölebileceğinizi göstereceğim. Küçük tablolarda bu yöntem performansın kötüleşmesine neden olur fakat büyük tablolarda çok iyi sonuçlar alınabilir. Yine de her zaman sonucun test edilmesi gerekiyor ve sonuca göre production sistemlere uygulanmalı.
use master
alter database MyDB set RESTRICTED_USER
-- DB backup all !!!
--önce TPropertyValue tablonun full scriptini al (defaultlar dahil) indexler, FK, Constraint
use MyDB
--geciçi tablo yarat
select * into TPropertyValue_20100114 from TPropertyValue
--tabloyu hızlandırmak için PK ekle
ALTER TABLE TPropertyValue_20100114 add CONSTRAINT PK__TPropertyValue_20100114__33758E3C PRIMARY KEY NONCLUSTEReD (ID)
--geçici tablonun içini kontrol et
--kayıt sayısını kontrol et
select count(*) from TPropertyValue_20100114
select count(*) from TPropertyValue
-- partition yapılacak tabloyu TPropertyValue drop et. Daha
ALTER INDEX ALL ON TPropertyValue DISABLE
--tüm foreign keyleri drop et. us_fkeys kullanabilirsin
--ilişkili view var ise ÖNCE view indexlerin scriplerini all !!!
--var ise ilişkili viewlar'daki SHEMABINDING leri kaldır,
drop table TPropertyValue
--Partition Function yarat
--------drop partition function PFuncNurgun_TPropertyValueN
CREATE PARTITION FUNCTION PFuncNurgun_TPropertyValue (uniqueidentifier)
AS RANGE RIGHT FOR VALUES
('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-090000000000',
'00000000-0000-0000-0000-100000000000', '00000000-0000-0000-0000-190000000000',
'00000000-0000-0000-0000-200000000000', '00000000-0000-0000-0000-290000000000',
'00000000-0000-0000-0000-300000000000', '00000000-0000-0000-0000-390000000000',
'00000000-0000-0000-0000-400000000000', '00000000-0000-0000-0000-490000000000',
'00000000-0000-0000-0000-500000000000', '00000000-0000-0000-0000-590000000000',
'00000000-0000-0000-0000-600000000000', '00000000-0000-0000-0000-690000000000',
'00000000-0000-0000-0000-700000000000', '00000000-0000-0000-0000-790000000000',
'00000000-0000-0000-0000-800000000000', '00000000-0000-0000-0000-890000000000',
'00000000-0000-0000-0000-900000000000', '00000000-0000-0000-0000-9F0000000000',
'00000000-0000-0000-0000-A00000000000', '00000000-0000-0000-0000-AF0000000000',
'00000000-0000-0000-0000-B00000000000', '00000000-0000-0000-0000-BF0000000000',
'00000000-0000-0000-0000-C00000000000', '00000000-0000-0000-0000-CF0000000000',
'00000000-0000-0000-0000-D00000000000', '00000000-0000-0000-0000-DF0000000000',
'00000000-0000-0000-0000-E00000000000', '00000000-0000-0000-0000-EF0000000000',
'00000000-0000-0000-0000-F00000000000', 'FFFFFFFF-FFFF-FFFF-FFFF-FEFFFFFFFFFF')
--FileGroup ekle
Declare @i int, @s varchar(5)
Declare @stmt varchar(2000)
select @i =0
while @i < 33
begin
select @i = @i + 1;
select @s = convert(varchar,@i)
select @stmt = 'ALTER DATABASE [CardioReferans_Mirror] ADD FILEGROUP TPropertyValue_fg'+@s
-- select @stmt = 'ALTER DATABASE [CardioReferans_Mirror] REMOVE FILEGROUP TPropertyValue_fg'+@s
exec (@stmt)
end
--FILE ekle burada S:\MSSQLDATA\REFERANS\ path ve SIZE değiştirilebilir !
Declare @i int, @s varchar(5)
Declare @stmt varchar(2000)
select @i =0
while @i < 33
begin
select @i = @i + 1;
select @s = convert(varchar,@i)
select @stmt = 'ALTER DATABASE [CardioReferans_Mirror] '
select @stmt = @stmt + ' ADD FILE (NAME = TPropertyValue_file'+@s+', FILENAME = '
select @stmt = @stmt + '''S:\MSSQLDATA\REFERANS\TellcomCardioReferans_TPropertyValue_fg'+@s+'.ndf'''
select @stmt = @stmt + ', SIZE = 100MB,FILEGROWTH = 50MB ) TO FILEGROUP TPropertyValue_fg'+@s+';'
--select @stmt = 'ALTER DATABASE [CardioReferans_Mirror] REMOVE FILE TPropertyValue_file'+@s
exec (@stmt)
end
--sp_helpdb MyDB
--Partition şema ekle
CREATE PARTITION SCHEME PSchemeNurgun_TPropertyValue
AS PARTITION PFuncNurgun_TPropertyValue
TO ( TPropertyValue_fg1, TPropertyValue_fg2, TPropertyValue_fg3, TPropertyValue_fg4,
TPropertyValue_fg5, TPropertyValue_fg6, TPropertyValue_fg7, TPropertyValue_fg8,
TPropertyValue_fg9, TPropertyValue_fg10, TPropertyValue_fg11, TPropertyValue_fg12,
TPropertyValue_fg13, TPropertyValue_fg14, TPropertyValue_fg15, TPropertyValue_fg16,
TPropertyValue_fg17,TPropertyValue_fg18,TPropertyValue_fg19,TPropertyValue_fg20 ,
TPropertyValue_fg21,TPropertyValue_fg22,TPropertyValue_fg23,TPropertyValue_fg24 ,
TPropertyValue_fg25, TPropertyValue_fg26,TPropertyValue_fg27,TPropertyValue_fg28 ,
TPropertyValue_fg29,TPropertyValue_fg30,TPropertyValue_fg31,TPropertyValue_fg32 ,
TPropertyValue_fg33)
--Tabloyu şema içinde yarat !!!
CREATE TABLE [dbo].[TPropertyValue](
[ID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_TPropertyValue_ID] DEFAULT (newid()),
[PropertyId] [uniqueidentifier] NULL,
[PropertyLovId] [uniqueidentifier] NULL,
[ValueString] [nvarchar](800) NULL,
[ValueFloat] [float] NULL,
[ValueDatetime] [datetime] NULL,
[ValueMlstringTr] [nvarchar](200) NULL,
[ValueMlstringEn] [nvarchar](200) NULL,
[ValueMlstringDe] [nvarchar](200) NULL,
[ValueMlstringFr] [nvarchar](200) NULL,
[ValueMlstringRu] [nvarchar](200) NULL,
[ValuePassword] [nvarchar](800) NULL,
[CreateUserId] [uniqueidentifier] NULL,
[CreateUserPositionId] [uniqueidentifier] NULL,
[CreateUserTime] [datetime] NULL,
[UpdateUserId] [uniqueidentifier] NULL,
[UpdateUserPositionId] [uniqueidentifier] NULL,
[UpdateUserTime] [datetime] NULL,
[OrderBy] [int] NULL,
[EntityType] [uniqueidentifier] NULL,
[EditStatus] [int] NULL CONSTRAINT [TPropertyValue_EditStatus_Dflt] DEFAULT ((0)),
[AutoId] [int] IDENTITY(1,1) NOT NULL,
[GcRecordId] [uniqueidentifier] NULL,
[UpdateUserIP] [nvarchar](30) NULL,
[CreateUserIP] [nvarchar](30) NULL,
[BulkUpdateFlag] [bit] NULL
,PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PSchemeNurgun_TPropertyValue(ID)
) ON PSchemeNurgun_TPropertyValue(ID)
--TPropertyValue tabloyu yedekten geri al
BEGIN
SET IDENTITY_INSERT TPropertyValue ON
insert into TPropertyValue ( ID, PropertyId, PropertyLovId, ValueString, ValueFloat, ValueDatetime, ValueMlstringTr, ValueMlstringEn, ValueMlstringDe, ValueMlstringFr, ValueMlstringRu, ValuePassword, CreateUserId, CreateUserPositionId, CreateUserTime, UpdateUserId, UpdateUserPositionId, UpdateUserTime, OrderBy, EntityType, EditStatus, AutoId, GcRecordId, UpdateUserIP, CreateUserIP, BulkUpdateFlag)
select ID, PropertyId, PropertyLovId, ValueString, ValueFloat, ValueDatetime, ValueMlstringTr, ValueMlstringEn, ValueMlstringDe, ValueMlstringFr, ValueMlstringRu, ValuePassword, CreateUserId, CreateUserPositionId, CreateUserTime, UpdateUserId, UpdateUserPositionId, UpdateUserTime, OrderBy, EntityType, EditStatus, AutoId, GcRecordId, UpdateUserIP, CreateUserIP, BulkUpdateFlag
from TPropertyValue_20100114
SET IDENTITY_INSERT TPropertyValue OFF
END
--
--TPropertyValue tablonun Contrain ve FK 'lerini yeniden yarat
--TPropertyValue tablonun INDEX'lerini ŞEMA içinde yarat ve incele
CREATE NONCLUSTERED INDEX [IX_TPropertyValue_ValueString] ON [dbo].[TPropertyValue]
(
[PropertyId] ASC,
[ValueString] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
ON PSchemeNurgun_TPropertyValue(ID)
sp_helpindex TPropertyValue
update statistics [TPropertyValue]
--viewların SHEMABINDING lerini geri koy ve view'ların indexleri yarat
--Partition Row dağılımı incele
select partition_number, rows from sys.partitions where object_id =object_id('TPropertyValue') and index_id <=1
--sadece bir partition nın index'ini rebuild et
alter index [IX_TPropertyValue_ValueString] on TPropertyValue rebuild PARTITION =2
--herkese izin ver
alter database MyDB set MULTI_USER
--THE END
Subscribe