July 20, 2012 at 12:58 am
hello Everyone.
i am dealing with a table that contain around 60 M records and every day dump around 100K records.
currently i have break this table in two tables as active and passive . data move to passive table at every weekend.i have using cluster and non cluster index that make system better in displaying data on report. data comes historical data comes in around max 15 second in UI. Everything is fine in current system.
Now for future prospects data will increase so i have perches sql enterprise edition and want to use sql partition function with multiple file group.
i have some par tics on this maintain single table with four file group (Historical,6 month,current Month ,Last Seven days data).
Everything is fine data dump and retrieve from respective file group can be seen query execution plan.
But I am facing problem in archive data.same procedure i am following archive data at 7 days. 7 days data move from very file group its previous filegroup base on partition function and partition scheme and after moving data from all file group of last 7 days change partition function date range .
But this process taking huge time around 30 minute and can not insert data during archive process in current file group.
i have move first current seven days data make it free for current use so data can insert.
Please tell me how can solve this problem. that make my archive process and system fast . i am attaching my archive procedure below:
ALTER PROCEDURE [dbo].[ARCHIVESMS]
@RecevingDate varchar(1000) = null
AS
BEGIN
SET NOCOUNT ON;
IF((SELECT COUNT(1) FROM SERVICE_TASKSTATUS WHERE TASK='ARCHIVE' AND [STATUS]=0)>0)
RETURN
DECLARE @ARCHIVEID UNIQUEIDENTIFIER
SELECT @ARCHIVEID =NEWID()
BEGIN TRY
DECLARE @PARTITIONRANGETABLE TABLE(FUNCTIONID VARCHAR(100), BOUNDARYID VARCHAR(100), PARAMETERID VARCHAR(100), VALUE DATETIME,FILEGROUPNAME VARCHAR(500))
DECLARE @ARCHIVEDYS AS INT
DECLARE @LASTARCHIVEINGDATE DATETIME
DECLARE @DAYSMOVEUPTO INT
DECLARE @CURRENTDATE DATETIME
SET @ARCHIVEDYS=1;
INSERT INTO ARCHIVEHISTORY (ID,STARTTIME) VALUEs(@ARCHIVEID,GETUTCDATE())
SET @CURRENTDATE=DATEADD(DD ,-@ARCHIVEDYS,GETUTCDATE())
INSERT INTO @PARTITIONRANGETABLE (FUNCTIONID,BOUNDARYID,PARAMETERID,VALUE,FILEGROUPNAME)
SELECT CONVERT(VARCHAR(10),R.FUNCTION_ID),CONVERT(VARCHAR(10),R.BOUNDARY_ID),CONVERT(VARCHAR(10),R.PARAMETER_ID),Convert(varchar(100),R.VALUE,121),
P.FILEGROUPNAME FROM (select ps.name as PSName, (dds.destination_id-1) as PartitionNumber, fg.Name as FileGroupName from
sys.indexes i
inner join
sys.partition_schemes ps on ps.data_space_id = i.data_space_id
inner join
sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id
inner join
sys.filegroups fg on fg.data_space_id = dds.data_space_id
left outer join
sys.partition_range_values prv on prv.boundary_id = dds.destination_id
where
i.[object_id] = object_id('sms_mtmessages')
) P LEFT JOIN SYS.partition_range_values R ON P.PartitionNumber=R.boundary_id ORDER BY boundary_id DESC
SELECT @LASTARCHIVEINGDATE =MAX(VALUE) FROM @PARTITIONRANGETABLE
SET @DAYSMOVEUPTO=DATEDIFF(MINUTE,@LASTARCHIVEINGDATE,@CURRENTDATE)
DECLARE @PARTITIONRANGE AS DATETIME
DECLARE @FILEGROUPNAME AS VARCHAR(500)
DECLARE @NEXTUSEDSQL AS NVARCHAR(MAX)
DECLARE POINTER CURSOR FOR SELECT VALUE,FILEGROUPNAME FROM @PARTITIONRANGETABLE WHERE ISNULL(VALUE,'')!='' ORDER BY VALUE DESC
OPEN POINTER
FETCH NEXT FROM POINTER INTO @PARTITIONRANGE,@FILEGROUPNAME
WHILE @@FETCH_STATUS=0
BEGIN
ALTER PARTITION FUNCTION [pf_ACTUALTIME_DATE]()
MERGE RANGE (CONVERT(VARCHAR,@PARTITIONRANGE,121));
SET @NEXTUSEDSQL=''
SET @NEXTUSEDSQL= 'ALTER PARTITION SCHEME [ps_ACTUALTIME_DATE] '
SET @NEXTUSEDSQL=@NEXTUSEDSQL + 'NEXT USED '+ @FILEGROUPNAME +';'
EXEC(@NEXTUSEDSQL)
ALTER PARTITION FUNCTION [pf_ACTUALTIME_DATE]()
SPLIT RANGE (DATEADD(MINUTE,@DAYSMOVEUPTO,CONVERT(VARCHAR,@PARTITIONRANGE,121)));
FETCH NEXT FROM POINTER INTO @PARTITIONRANGE,@FILEGROUPNAME
END
CLOSE POINTER
DEALLOCATE POINTER
UPDATE ARCHIVEHISTORY SET ENDTIME=GETUTCDATE() WHERE [ID]=@ARCHIVEID
END TRY
BEGIN CATCH
DECLARE @ERRORMESSAGE NVARCHAR(4000), @ERRORSEVERITY INT, @ERRORSTATE INT
SELECT @ERRORMESSAGE = ERROR_MESSAGE(), @ERRORSEVERITY = ERROR_SEVERITY(), @ERRORSTATE = ERROR_STATE();
RAISERROR(@ERRORMESSAGE, @ERRORSEVERITY,@ERRORSTATE)
CLOSE POINTER
DEALLOCATE POINTER
END CATCH
ALTER DATABASE [testdb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE('SMS2.2_log', 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
Dbcc ShrinkDatabase(testdb, TruncateOnly)
UPDATE ARCHIVEHISTORY SET ENDSHRINKTIME=GETUTCDATE() WHERE [ID]=@ARCHIVEID
END
July 20, 2012 at 1:16 am
painulyanoop (7/20/2012)
ALTER DATABASE [testdb] SET RECOVERY SIMPLE WITH NO_WAITDBCC SHRINKFILE('SMS2.2_log', 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
Dbcc ShrinkDatabase(testdb, TruncateOnly)
You can start by removing this.
Breaks database recovery path, results in slow-downs next time data is inserted, probably file system fragmentation, etc. Very, very bad practice.
As for the rest, I didn't look too much, there's a lot of code. Where's the slow part?
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
July 20, 2012 at 1:52 am
Thanks Gail Shaw
I agree with you but after archive data size increase of all file group. i was these line only for shrinking yes i was wrong.
lets i am going with you and removed these line from code but still same time taken.
Have a look what i am doing in code.
step 1) : merging seven days file group data with current month data . after merging then change the boundary for last seven days file group so it will contain data equal and greater then current day.
step 2) : merging current month file group data with last 6 month and then again change current month boundary by adding 7 days.
step 3) Merging last 6 month to historical and and same thing change last 6 month file group boundary with adding 7 days.
step 2 & step 3 taking huge time in merging and changing boundary value.
below steps using in merging and splinting :
ALTER PARTITION FUNCTION [pf_ACTUALTIME_DATE]()
MERGE RANGE (N'2013-01-24 23:50:00.000');
ALTER PARTITION SCHEME [ps_ACTUALTIME_DATE]
NEXT USED [FACT_HISTORICALDATA];
ALTER PARTITION FUNCTION [pf_ACTUALTIME_DATE]()
SPLIT RANGE (N'2013-01-24 23:50:00.000');
Gail you are master but i am programmer in c# but trying to learn sql. so please tell what is wrong and how can i make it better . if any idea please provide me .
Thanks & Regards
Anoop
July 20, 2012 at 2:53 am
Merging and splitting the ranges shouldn't take any time.
Are the partitions on different filegroups?
What wait type are you seeing when running the merge and split?
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
July 20, 2012 at 2:59 am
yes 4 partition on different file group.
I have 50M records in Historical ,20M in 6 month file group and 3M in current month and .7 M in last seven days file group.
moving last 7 days data in all partition group taking time around 30 minute and main problem is during this time can insert data and view records in UI.
Regards
~Anoop
July 20, 2012 at 6:15 am
When you merge two partitions, is one always empty?
Are you trying to move lots of rows from a partition in one filegroup to a partition in another?
What wait type are you seeing when running the merge and split?
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply