Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Использовать ли partitioning?  [new]
Jevgenij Olechnovic
Member

Откуда: UK
Сообщений: 19
Привет, друзья!
Я новичек с MS SQL Server. Недавно получил такое задание:

Есть таблица для аудитирования данных в которую пишутся все изменения - insert, update, delete + дата действия.
Нужно разбить таблицу на 2 таблицы. в одной будут данные об изменениях за последние 3 месяца, а в другой все остальные.
Т.е после того как данные уже устарели, они должны переноситься в таблицу где > 3мес, считай архивирование.
Я в начале думал реализовать при помощи views, но есть требование, чтобы данные были физически разделенны, т.е. находились в разных файлах/таблицах. Можно ли такое задание сделать при помощи Table Partitioning или нужно писать job, который каждый день архивировал бы устаревшие данные?

Помогите советом.

Большое спасибо!
19 июл 11, 20:06    [10997068]     Ответить | Цитировать Сообщить модератору
 Re: Использовать ли partitioning?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
так в разных файлах / таблицах / базах?? это как бы важно и механизмы надо использовать разные и условности разные будут
19 июл 11, 20:14    [10997086]     Ответить | Цитировать Сообщить модератору
 Re: Использовать ли partitioning?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
При партицировании таблица будет одна. Но секции могут храниться в разных файловых группах этой же бд.
19 июл 11, 20:15    [10997089]     Ответить | Цитировать Сообщить модератору
 Re: Использовать ли partitioning?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Jevgenij Olechnovic
Можно ли такое задание сделать при помощи Table Partitioning


вполне, а ещё "старые" данные в секциях можно хранить в сжатом виде
19 июл 11, 20:17    [10997094]     Ответить | Цитировать Сообщить модератору
 Re: Использовать ли partitioning?  [new]
Jevgenij Olechnovic
Member

Откуда: UK
Сообщений: 19
Crimean,

Спасибо за ответ.
Таблица должна быть одна и она будет храниться в той же базе. Но необходимо, чтобы партиции хранились в разных файлах (как понимаю это положительно влияет на скорость).

Как я понимаю, данные назначаются партиции при insert'e? А как быть с данными которые уже введены?

я нашел интересный пример, я думаю написать Job, который раз в сутки будет выставлять статус Archived для всех данных, дата создания которых > 3 m. может знаете более элегантное решение?

Спасибо!

Вот собственно сам пример:

USE master;
GO
--- Step 1 : Create New Test Database with two different filegroups.
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'TestDB')
DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON PRIMARY
(NAME='TestDB_Part1',
FILENAME=
'c:\sqldata\TestDB_Part1.mdf',
SIZE=3,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = 'TestDB_Part2',
FILENAME =
'c:\sqldata\TestDB_Part2.ndf',
SIZE =3,
MAXSIZE=100,
FILEGROWTH=1 );
GO



USE TestDB;
GO
--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION TestDB_PartitionRange (Bit)
AS RANGE right FOR
VALUES (1);
GO

CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2);
GO


CREATE TABLE TestTable
(Archived Bit NOT NULL,
Date DATETIME)
ON TestDB_PartitionScheme (Archived);
GO


INSERT INTO TestTable (Archived, Date)
VALUES (0,'2010-01-01');
INSERT INTO TestTable (Archived, Date)
VALUES (0,'2010-02-01');
INSERT INTO TestTable (Archived, Date)
VALUES (0,'2010-03-01');
GO

SELECT * FROM TestTable;


SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';

update TestTable
set Archived = 1 where Date = '2010-03-01'

SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';


use master
go
drop database testdb
20 июл 11, 15:01    [11000697]     Ответить | Цитировать Сообщить модератору
 Re: Использовать ли partitioning?  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Jevgenij Olechnovic
Crimean,
А как быть с данными которые уже введены?


Таблицу можно секционировать двумя способами, а точнее 3-мя.
1. При создании таблицы указывается в какой парт схеме она будет находиться. В этом случае данных нет.
2. При изменении таблицы, удалении кластерного индекса (как правило PK) указываем в какую парт схему переместить.
3. При изменении таблицы, создании кластерного индекса (как правило PK) указываем в какую парт схему переместить.
Во всех трех случаях в схеме указывается поле по которому будет происходить партирование. И при изменении таблицы Скуль автоматически раскидает данные по нужны секциям.
20 июл 11, 15:23    [11000885]     Ответить | Цитировать Сообщить модератору
 Re: Использовать ли partitioning?  [new]
Maks Bragar
Member

Откуда: UA->AT
Сообщений: 165
Jevgenij Olechnovic,

Прозвучало 3 месяца. может так попробовать ?
USE MASTER;
GO
--- Step 1 : Create New Test Database with two different filegroups.
IF EXISTS (
       SELECT NAME
       FROM   sys.databases
       WHERE  NAME = N'TestDB'
   )
    DROP DATABASE TestDB;
GO

CREATE DATABASE TestDB
ON PRIMARY(
              NAME = 'TestDB_Part1',
              FILENAME =
              'D:\TestDB_Part1.mdf',
              SIZE = 3,
              MAXSIZE = 100,
              FILEGROWTH = 1
          ),
FILEGROUP TestDB_Part2(
                          NAME = 'TestDB_Part2',
                          FILENAME =
                          'D:\TestDB_Part2.ndf',
                          SIZE = 3,
                          MAXSIZE = 100,
                          FILEGROWTH = 1
                      );
GO



USE TestDB;
GO
--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION TestDB_PartitionRange(int)
AS RANGE RIGHT FOR
VALUES(90);
GO

CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2);
GO


CREATE TABLE TestTable
(
	Date      DATETIME,
	DayDate int
)
ON TestDB_PartitionScheme(DayDate);
GO


INSERT INTO TestTable (    Date,    DayDate  )
SELECT  '2011-01-01' , DATEDIFF(dd,'20110101',getdate())

INSERT INTO TestTable (    Date,    DayDate  )
SELECT  '2011-03-01' , DATEDIFF(dd,'20110301',getdate())

INSERT INTO TestTable (    Date,    DayDate  )
SELECT  '2011-04-01' , DATEDIFF(dd,'20110401',getdate())

INSERT INTO TestTable (    Date,    DayDate  )
SELECT  '2011-05-01' , DATEDIFF(dd,'20110501',getdate())

INSERT INTO TestTable (    Date,    DayDate  )
SELECT  '2011-06-01' , DATEDIFF(dd,'20110601',getdate())

SELECT *
FROM   TestTable;


SELECT *
FROM   sys.partitions
WHERE  OBJECT_NAME(OBJECT_ID) = 'TestTable';

GO

USE MASTER
GO

DROP DATABASE testdb


20 июл 11, 16:38    [11001519]     Ответить | Цитировать Сообщить модератору
 Re: Использовать ли partitioning?  [new]
Jevgenij Olechnovic
Member

Откуда: UK
Сообщений: 19
Большое спасибо за советы. Делюсь скриптом, который я использовал.
Сделал Job, который в 12.00АМ запускает
UPDATE tblAuditEvent SET Archived = 1 WHERE MONTH(UpdatedOn)> MONTH( DATEADD(MONTH,-3, GETDATE())
и все что > 3 мес получает статус Archived и благополучно переходит в другую партицию.


ALTER TABLE tblAuditEvent ADD Archived bit
GO
UPDATE tblAuditEvent SET Archived = 0 WHERE Archived IS NULL
GO
ALTER TABLE tblAuditEvent  ADD CONSTRAINT DF_ARCHIVED DEFAULT 0 FOR Archived	
GO

ALTER DATABASE rdsdb
ADD FILEGROUP AuditFG
GO

ALTER DATABASE RDSDB
ADD FILE
( NAME  = 'ArchivedAuditData'
, FILENAME = 'C:\Test\MS Sql\ArchivedAuditData.ndf'
, SIZE = 500MB
, MAXSIZE= UNLIMITED
, FILEGROWTH = 20%)
TO FILEGROUP AuditFG
GO

CREATE PARTITION FUNCTION ArchiveFunction(bit)
AS RANGE RIGHT FOR VALUES (1);
GO
CREATE PARTITION SCHEME AuditScheme
AS PARTITION ArchiveFunction
TO (AuditFG,[PRIMARY]);
GO


ALTER TABLE dbo.tblAuditEventParameter DROP CONSTRAINT fk_tblAuditEventParameter_tblAuditEvent;
GO
ALTER TABLE dbo.tblAuditEvent
DROP CONSTRAINT pk_tblAuditEvent
WITH (MOVE TO AuditScheme(Archived));

GO

ALTER TABLE dbo.tblAuditEvent
ADD CONSTRAINT pk_tblAuditEvent PRIMARY KEY NONCLUSTERED (AuditEventID)
ON [Primary]
GO

SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='tblAuditEvent';
GO

ALTER TABLE tblAuditEventParameter
	ADD constraint fk_tblAuditEventParameter_tblAuditEvent foreign key(AuditEventID)
	references dbo.tblAuditEvent(AuditEventID);
	
--UPDATE tblAuditEvent SET Archived = 1 WHERE MONTH(UpdatedOn)> MONTH( DATEADD(MONTH,-3, GETDATE()) 	)
	
SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='tblAuditEvent';

GO
SET STATISTICS IO ON

SELECT  * --MONTH(UpdatedOn), count( [AuditEventID] )      
  FROM [RDSDB].[dbo].[tblAuditEvent]
  where 
	UpdatedOn BETWEEN DATEADD(MONTH,-1, GETDATE()) AND GETDATE()
  GROUP BY MONTH(UpdatedOn)
  ORDER BY MONTH(UpdatedOn)
  
  DECLARE @Today DATETIME 
	SET @Today = DATEDIFF(dd, 0, GETDATE())   
	--RESET archived status
	UPDATE tblAuditEvent SET Archived = 1 WHERE UpdatedOn<  DATEADD(MONTH,-3, @Today) 	
8 авг 11, 20:03    [11088110]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить