Witam, od jakiegoś czasu zagłębiam się w administrowanie MSSQL i miałbym prośbę o sprawdzenie, czy dobrze rozumiem aspekt partycjonowania tabel. Gdy uczyłem się partycjonować korzystałem z różnych książek, tutoriali, czy informacji w internecie, gdzie zazwyczaj partycjonowanie odbywało się za pomocą lat, czy miesięcy. W Tabeli, którą chce partycjonować nie mam takiego odniesienia. Tabela składa się z trzech kolumn tj. id (liczby od 1 i dalej), relation_id (liczby od 1779698 do 682498137) i position (zamienne znaki liter i liczb, np. R3535, które mogą sie powtarzac). Uznałem, że najlepsza do partycjonowania byłaby kolumna relation_id. Poniżej mój kod, którym to zrobiłem:
Stworzyłem pliki i grupy plików względem kolumny relation_id
-- Dodanie nowych grup plików
ALTER DATABASE mip1
ADD FILEGROUP relation_id_100kk;
ALTER DATABASE mip1
ADD FILEGROUP relation_id_200kk;
ALTER DATABASE mip1
ADD FILEGROUP relation_id_300kk;
ALTER DATABASE mip1
ADD FILEGROUP relation_id_400kk;
ALTER DATABASE mip1
ADD FILEGROUP relation_id_500kk;
ALTER DATABASE mip1
ADD FILEGROUP relation_id_600kk;
ALTER DATABASE mip1
ADD FILEGROUP relation_id_700kk;
-- Dodanie nowych plików do nowych grup plików
ALTER DATABASE mip1
ADD FILE
(
NAME = 'relation_id_100kk',
FILENAME = 'P:\relation_id_100kk.ndf',
SIZE = 50GB,
MAXSIZE = 70GB,
FILEGROWTH = 1024MB
)
TO FILEGROUP relation_id_100kk;
ALTER DATABASE mip1
ADD FILE
(
NAME = 'relation_id_200kk',
FILENAME = 'P:\relation_id_200kk.ndf',
SIZE = 50GB,
MAXSIZE = 70GB,
FILEGROWTH = 1024MB
)
TO FILEGROUP relation_id_200kk;
ALTER DATABASE mip1
ADD FILE
(
NAME = 'relation_id_300kk',
FILENAME = 'P:\relation_id_300kk.ndf',
SIZE = 50GB,
MAXSIZE = 70GB,
FILEGROWTH = 1024MB
)
TO FILEGROUP relation_id_300kk;
ALTER DATABASE mip1
ADD FILE
(
NAME = 'relation_id_400kk',
FILENAME = 'P:\relation_id_400kk.ndf',
SIZE = 50GB,
MAXSIZE = 70GB,
FILEGROWTH = 1024MB
)
TO FILEGROUP relation_id_400kk;
ALTER DATABASE mip1
ADD FILE
(
NAME = 'relation_id_500kk',
FILENAME = 'P:\relation_id_500kk.ndf',
SIZE = 50GB,
MAXSIZE = 70GB,
FILEGROWTH = 1024MB
)
TO FILEGROUP relation_id_500kk;
ALTER DATABASE mip1
ADD FILE
(
NAME = 'relation_id_600kk',
FILENAME = 'P:\relation_id_600kk.ndf',
SIZE = 50GB,
MAXSIZE = 70GB,
FILEGROWTH = 1024MB
)
TO FILEGROUP relation_id_600kk;
ALTER DATABASE mip1
ADD FILE
(
NAME = 'relation_id_700kk',
FILENAME = 'P:\relation_id_700kk.ndf',
SIZE = 50GB,
MAXSIZE = 70GB,
FILEGROWTH = 1024MB
)
TO FILEGROUP relation_id_700kk;
Stworzyłem funkcje partycji:
CREATE PARTITION FUNCTION PartitionFunction_Relation_Id (bigint)
AS RANGE LEFT FOR VALUES (
100000000,
200000000,
300000000,
400000000,
500000000,
600000000
);
Schemat partycjonowania
CREATE PARTITION SCHEME PartitionScheme_Relation_Id
AS PARTITION PartitionFunction_Relation_Id
TO (
relation_id_100kk,
relation_id_200kk,
relation_id_300kk,
relation_id_400kk,
relation_id_500kk,
relation_id_600kk,
relation_id_700kk
);
Stworzyłem nową tabelę taką jak tą, którą chce partycjonowac:
CREATE TABLE [mipadm].[u_tt_batch_relation_mounting_positions_partitioned](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[tt_relation_id] [bigint] NOT NULL,
[position] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_u_tt_batch_relation_mounting_positions_partitioned] PRIMARY KEY CLUSTERED
(
[id] ASC, [tt_relation_id]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
) ON PartitionScheme_Relation_Id(tt_relation_id);
Przeniesienie danych ze starej tabeli:
INSERT INTO [mipadm].[u_tt_batch_relation_mounting_positions_partitioned] ([tt_relation_id], [position])
SELECT [tt_relation_id], [position]
FROM [mipadm].[u_tt_batch_relation_mounting_positions];
Usuniecie starej tabeli:
DROP TABLE [mipadm].[u_tt_batch_relation_mounting_positions];
Zmiana nazwy:
EXEC sp_rename 'mipadm.u_tt_batch_relation_mounting_positions_partitioned', 'u_tt_batch_relation_mounting_positions';
Dodatkowo zrobiłem indeksy tak samo jak na starej tabeli:
USE [mip1]
GO
CREATE NONCLUSTERED INDEX [idx_batch_relation_id] ON [mipadm].[u_tt_batch_relation_mounting_positions_partitioned]
(
[tt_relation_id] ASC
)
INCLUDE([position])
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
)
ON PartitionScheme_Relation_Id([tt_relation_id]);
GO
Mam kilka pytań odnośnie tego co zrobiłem:
- Czy całość partycjonowania jest dobrze wykonana? Czy powinienem coś poprawić i ewentalnie co konkretnie?
- Jak się ma sprawa z tworzeniem plików, a konkretnie z przydzielaniem dla nich miejsca? Nie do końca rozumiem na jakiej zasadzie sie przypisuje określoną ilość miejsca dla pliku.
- Czy można bezpośrednio partycjonować stara tabele, bez tworzenia nowej? Czy wiąże się to z czymś konkretnym? Czy lepiej pozostać, przy tworzeniu nowej tabeli i przenoszeniu danych do niej ze starej tabeli?
- Czy można po skonczonym partycjonowaniu, jakiś czas pozniej "dorobić" kolejną partycje, np. dla 800k? Czy lepiej zrobic partycję "do przodu"?
- Czy jest możliwosć automatyzacji partycjonowania i w jaki sposób to zrobić? Jakieś zródło najlepiej, zebym sie zapoznał :)
Bardzo dziękuję każdemu za udzieloną odpowiedz.
SQL 
A chcąc mieć dane w tym samym bloku dyskowym- nie do końca rozumiem, chodzi ci o to że masz macierz dyskową, operującą na nim bazę, jedno nie wie o drugim i chcesz mieć możliwość kontroli gdzie baza składuje swoje dane? Tak, wtedy to ma sens - ale to ułomność DB że nie operuje na poziomie bloków tylko na abstrakcji systemu plików.Jeśli możesz to rozwiń temat kosztu utrzymania spójności danych przy partycjonowaniu.- chodzi głównie o składowane procedury i/o klucze. W przypadku jednej tabeli po prostu nakładasz constraint i elo. W przypadku wielu rozproszonych - nie wiem, może widoki też dają takie możliwości choć wątpię.