خانه SQL Server موضوعی نهفته در Data Partitioning – بخش اول SQL Server افزایش سرعت SQL Server نوشته شده توسط: مهدی قپانوری تاریخ انتشار: ۱۲ آبان ۱۴۰۰ آخرین بروزرسانی: ۲۱ شهریور ۱۴۰۲ زمان مطالعه: 20 دقیقه ۱ (۱) مقدمه پارتیشن نمودن جداول مزایای خاصی را به دنبال دارد مانند Partition Switch. همان طور که می دانیم Rebuild کلاستر ایندکس یک جدول بزرگ عملیات بسیار سنگینی می باشد، اما با استفاده از امکان Data Partitioning می توانیم فقط یک یا چند پارتیشن خاص را در مدت زمان کوتاهی Rebuild نمائیم. اگر Partition Key در شرط کوئری یا شرط Join مورد استفاده قرار گیرد، سرعت اجرای کوئری می تواند افزایش یابد. با استفاده از Data Partitioning بحث فشرده سازی را به ازای پارتیشن های جاری می توان انجام نداد و فقط پارتیشن های قدیمی را فشرده نمود. Data Partitioning مزایای دیگری نیز دارد. با همه این مزایا، پارتیشن نمودن جداول معایبی نیز دارد که در این مقاله قصد داریم به بررسی مهمترین این موارد بپردازیم. برای نشان دادن این موضوع جدول زیر را ایجاد و با داده های فرضی پر می نمائیم: Use Test GO CREATE TABLE dbo.DATA ( ID INT NOT NULL, DATECREATED DATETIME NOT NULL CONSTRAINT DEF_DATA_DATECREATED DEFAULT GETUTCDATE(), DATEMODIFIED DATETIME NOT NULL CONSTRAINT DEF_DATA_DATEMODIFIED DEFAULT GETUTCDATE(), PLACEHOLDER CHAR(500) NULL ); DECLARE @STARTDATE DATETIME = '2016-01-01'; ;WITH N1(C) AS (SELECT 0 UNION ALL SELECT 0) -- 2 ROWS ,N2(C) AS (SELECT 0 FROM N1 AS T1 CROSS JOIN N1 AS T2) -- 4 ROWS ,N3(C) AS (SELECT 0 FROM N2 AS T1 CROSS JOIN N2 AS T2) -- 16 ROWS ,N4(C) AS (SELECT 0 FROM N3 AS T1 CROSS JOIN N3 AS T2) -- 256 ROWS ,N5(C) AS (SELECT 0 FROM N4 AS T1 CROSS JOIN N4 AS T2) -- 65,536 ROWS ,N6(C) AS (SELECT 0 FROM N5 AS T1 CROSS JOIN N2 AS T2 CROSS JOIN N1 AS T3) -- 524,288 ,IDS(ID) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N6) INSERT INTO dbo.DATA WITH (TABLOCK)(ID, DATECREATED, DATEMODIFIED) SELECT ID, DATEADD(SECOND,35 * ID,@STARTDATE), CASE WHEN ID % 10 = 0 THEN DATEADD(SECOND, 24 * 60 * 60 * (ID % 31) + 11200 + ID % 59 + 35 * ID, @STARTDATE) ELSE DATEADD(SECOND,35 * ID,@STARTDATE) END FROM IDS; GO دستور زیر یک Unique Clustered Index و همچنین یک NonClustered Index غیر یکتا را بر روی جدول Data ایجاد می نماید: CREATE UNIQUE CLUSTERED INDEX IDXDATAID ON dbo.DATA(DATECREATED, ID) WITH (DATA_COMPRESSION = PAGE); CREATE NONCLUSTERED INDEX IDXDATADATEMODIFIED ON dbo.DATA(DATEMODIFIED) WITH (DATA_COMPRESSION = PAGE); اکنون پروسیجر زیر را ایجاد و اجرا می نمائیم : CREATE PROCEDURE USP_TEST (@TOP TINYINT, @LASTDATEMODIFIED DATETIME) AS SELECT TOP (@TOP) ID, DATECREATED, DATEMODIFIED, PLACEHOLDER FROM dbo.DATA WHERE DATEMODIFIED > @LASTDATEMODIFIED ORDER BY DATEMODIFIED GO EXEC USP_TEST 100, '2016-08-25 00:00:00.000' تصویر زیر Actual Execution Plan مربوط به اجرای پروسیجر را نمایش می دهد:همان طور که در تصویر مشاهده می نمائید SQL Server از NonClustered Index ایجاد شده بر روی جدول به خوبی استفاده نموده است. می خواهیم جدول را پارتیشن نموده و مجدد پروسیجر را اجرا نمائیم. دستورات زیر یک FileGroup به دیتابیس تست اضافه نموده و سپس Partition Function و Partition Scheme را ایجاد می نمایند: ALTER DATABASE TEST ADD FILEGROUP FG1 ALTER DATABASE TEST ADD FILE (NAME = F1, FILENAME = N'D:\DATABASES\DATA\TESTF1.NDF', SIZE = 1024 MB, FILEGROWTH = 256 MB) TO FILEGROUP FG1 CREATE PARTITION FUNCTION PFDATA(DATETIME) AS RANGE RIGHT FOR VALUES ('۲۰۱۶-۰۲-۰۱', '۲۰۱۶-۰۳-۰۱','۲۰۱۶-۰۴-۰۱','۲۰۱۶-۰۵-۰۱','۲۰۱۶-۰۶-۰۱','۲۰۱۶-۰۷-۰۱','۲۰۱۶-۰۸-۰۱'); CREATE PARTITION SCHEME PSDATA AS PARTITION PFDATA ALL TO ([FG1]); دستورات زیر نیز جدول را پارتیشن و NonClustered Index آن را اصطلاحا Aligned می نمایند: CREATE UNIQUE CLUSTERED INDEX IDXDATAID ON DBO.DATA(DATECREATED, ID) WITH (DATA_COMPRESSION = PAGE, DROP_EXISTING = ON) ON PSDATA(DATECREATED); CREATE NONCLUSTERED INDEX IDXDATADATEMODIFIED ON DBO.DATA(DATEMODIFIED) WITH (DATA_COMPRESSION = PAGE, DROP_EXISTING = ON) ON PSDATA(DATECREATED); GO مجدد پروسیجر را اجرا می نمائیم: EXEC USP_TEST 100, '2016-08-25 00:00:00.000' تصویر زیر پلن اجرایی کوئری را نشان می دهد:همان طور که مشاهده می نمائید پلن اجرایی پروسیجر تغییر یافته و Clustered Index Scan رخ داده است. راه حل ها حل مسئله مطرح شده می تواند یک Trade Off باشد. به عنوان یک راه حل می توان NonClustered Index را به صورت Aligned شده ایجاد نمود و پروسیجر را به صورت زیر بازنویسی کرد: CREATE OR ALTER PROCEDURE USP_TEST (@TOP TINYINT, @LASTDATEMODIFIED DATETIME) AS DECLARE @BOUNDARYVALUESCOUNT INT CREATE TABLE #PARTITIONS(PARTITIONNUM SMALLINT NOT NULL PRIMARY KEY); -- GETTING NUMBER OF BOUNDARY VALUES IN PARTITION FUNCTION SELECT @BOUNDARYVALUESCOUNT = MAX(BOUNDARY_ID) FROM SYS.PARTITION_FUNCTIONS PF JOIN SYS.PARTITION_RANGE_VALUES PRF ON PF.FUNCTION_ID = PRF.FUNCTION_ID WHERE PF.NAME = 'PFDATA'; ;WITH PARTITIONS(PARTITIONNUM) AS ( SELECT 1 UNION ALL SELECT PARTITIONNUM + 1 FROM PARTITIONS WHERE PARTITIONNUM <= @BOUNDARYVALUESCOUNT ) INSERT INTO #PARTITIONS(PARTITIONNUM) SELECT PARTITIONNUM FROM PARTITIONS; ;WITH STEPS1AND2(ID, DATECREATED, DATEMODIFIED) AS ( SELECT TOP 100 PARTDATA.ID, PARTDATA.DATECREATED, PARTDATA.DATEMODIFIED FROM #PARTITIONS P CROSS APPLY ( SELECT TOP 100 ID, DATECREATED, DATEMODIFIED FROM dbo.DATA WHERE DATEMODIFIED > @LASTDATEMODIFIED AND $PARTITION.PFDATA(DATECREATED) = P.PARTITIONNUM ORDER BY DATEMODIFIED ) PARTDATA ORDER BY PARTDATA.DATEMODIFIED ) -- STEP 3 - CI SEEK AS KEY LOOKUP OPERATION SELECT S.ID, S.DATECREATED, S.DATEMODIFIED, D.PLACEHOLDER FROM STEPS1AND2 S JOIN DBO.DATA D ON D.ID = S.ID AND S.DATECREATED = D.DATECREATED ORDER BY S.DATEMODIFIED بخشی از پلن اجرایی پروسیجر بعد از بازنویسی:هدف مقاله این است که، صرفا ایجاد ایندکس بر روی شرط کوئری باعث بهبود Performance نمی شود، همواره و با توجه به شرایط باید Plan اجرای کوئری مورد بررسی قرار گیرد که آیا SQL Server از ایندکس ایجاد شده به شکل مطلوبی استفاده می نماید یا خیر. ضمن اینکه بسته به شرایط سیستم و نیازها می توان از یکسری امکانات ارائه شده در SQL Server استفاده نمود. این گونه نیست که همه Solution ها برای همه سیستم ها مناسب باشند. چه رتبه ای میدهید؟ میانگین ۱ / ۵. از مجموع ۱ اولین نفر باش دانلود مقاله موضوعی نهفته در Data Partitioning – بخش اول فرمت PDF 6 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده مقالات 15 مقاله توسط این نویسنده محصولات 1 دوره توسط این نویسنده مهدی قپانوری مهدی قپانوری بیش از 6 سال است که در زمینههای نرم افزار و بانک اطلاعاتی فعالیت مینماید. تخصص اصلی او در بانک اطلاعاتی SQL Server بوده و دارای تجربه در حوزههایPerformance Tuning، Database Administration، Database Development و طراحی سیستمهای OLTP میباشد. مهدی علاقهمند به R&D در حوزههای نوین SQL Server است. معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ Stranger ۱۶ / ۰۸ / ۰۰ - ۱۱:۵۶ سپاس از شما اما مشکل همچنان وجود دارد. پاسخ به دیدگاه آرزو محمدزاده ۱۶ / ۰۸ / ۰۰ - ۱۲:۰۶ درود بر شما مجددا بررسی شد لطفا کش مرورگر خود را خالی نمایید و مجدد تلاش نمایید. تشکر از همراهی شما پاسخ به دیدگاه Stranger ۱۳ / ۰۸ / ۰۰ - ۰۵:۱۶ سلام، با تشکر از مطالب مفیدتان فایل PDF را دانلود کردم کامل است، اما روی صفحه وب خیر، بخش هایی را نسبت به فایل پی دی اف کم دارد. پاسخ به دیدگاه آرزو محمدزاده ۱۵ / ۰۸ / ۰۰ - ۱۱:۴۸ درود بر شما سپاس اط اطلاع رسانی شما این مورد بررسی شد. با تشکر پاسخ به دیدگاه Stranger ۱۳ / ۰۸ / ۰۰ - ۰۵:۱۶ سلام، با تشکر از مطالب مفیدتان فایل PDF را دانلود کردم کامل است، اما روی صفحه وب خیر، بخش هایی را نسبت به فایل پی دی اف کم دارد. پاسخ به دیدگاه آرزو محمدزاده ۱۶ / ۰۸ / ۰۰ - ۱۲:۰۶ درود بر شما مجددا بررسی شد لطفا کش مرورگر خود را خالی نمایید و مجدد تلاش نمایید. تشکر از همراهی شما پاسخ به دیدگاه