بهینه شدن Caching جداول Temp در SQL Server 2014

نوشته شده توسط: تورج عزیزی

تاریخ انتشار: ۱۶ آبان ۱۳۹۴

آخرین بروزرسانی: 28 بهمن 1403

زمان مطالعه: 4 دقیقه

Caching جداول Temp در SQL Server، در این مقاله با قابلیتی جدید در SQL Server آشنا می شوید که تاثیر بسزایی در Performance دیتابیس tempdb دارد. جداول Temp جداولی هستند که در دیتابیس tempdb ساخته می شوند و نام آنها با # شروع می شود.

مفهوم Caching جداول Temp در SQL Server

در صورت برقراری شرایطی خاص SQL Server می تواند جداول Temp را Cache کند. Cache کردن جداول Temp بدان معنی است که پایگاه داده SQL Server مجبور نخواهد بود جداول Temp را هنگامی که به دفعات نیاز به ایجاد آنها دارد، بسازد. این امر کارایی workload (منظور کوئری هایی که نیاز به ساخت این جداول دارند) را به طرز فوق العاده ای بهبود می بخشد، چون SQL Server نیازی ندارد تا به Page های ویژه ای در حافظه (PFS, GAM, SGAM) دست پیدا کند که می تواند منجر به بروز درگیری در زمان peak کاری شود.

یکی از نیازمندی های Cache جداول Temp این است که شما نباید دستورات DML و DDL را در یک مفهوم Stored Procedure با هم تلفیق کنید. کد زیر را تصور کنید:

 -- Create a new stored procedure
CREATE PROCEDURE PopulateTempTable
AS
BEGIN
-- Create a new temp table
CREATE TABLE #TempTable
(
Col1 INT IDENTITY(1, 1),
Col2 CHAR(4000),
Col3 CHAR(4000)
)
-- Create a unique clustered index on the previous created temp table
CREATE UNIQUE CLUSTERED INDEX idx_c1 ON #TempTable(Col1)
-- Insert 10 dummy records
DECLARE @i INT = 0
WHILE (@i < 10)
BEGIN
INSERT INTO #TempTable VALUES ('Klaus', 'Aschenbrenner')
SET @i += 1
END
END
GO

در کدهای بالا با استفاده از یک دستور (CREATE UNIQUE CLUSTERED INDEX) که یک دستور از نوع DDL است یک ایندکس ایجاد می کنید و بدین معنی است که دستورات DDL و DML را با هم تلفیق کرده اید. به این دلیل SQL Server نمی تواند جداول شما را Cache کند. شما می‌توانید کوئری نویسی را به صورت گام‌به‌گام از نیک آموز فرا بگیرید. شما می توانید از وجود چنین رفتاری مطمئن شوید. کافیست یک Perfrormance Counter به نام Temp Tables Creation Rate در یک dmv به نام sys.dm_os_performance_counters مانند مثال زیر بررسی کنید:

DECLARE @table_counter_before_test BIGINT;
SELECT @table_counter_before_test = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Temp Tables Creation Rate'
DECLARE @i INT = 0
WHILE (@i < 1000)
BEGIN
EXEC PopulateTempTable
SET @i += 1
END
DECLARE @table_counter_after_test BIGINT;
SELECT @table_counter_after_test = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Temp Tables Creation Rate'
PRINT 'Temp tables created during the test: ' + CONVERT(VARCHAR(100), @table_counter_after_test - @table_counter_before_test)
GO

وقتی شما کدهای بالا را اجرا می کنید SQL Server باید ۱۰۰۰ جدول temp بسازد. این موضوع در خروجی SSMS قابل مشاهده است:

برای رفع این مشکل می توانید با استفاده از قید PRIMARY KEY یک Clustered Unique Index بسازید. با این روش شما بدون استفاده از دستور DDL به هدف خود رسیده اید و در نهایت SQL Server می تواند جداول شما را Cache کند.

ALTER PROCEDURE PopulateTempTable
AS
BEGIN
-- Create a new temp table
CREATE TABLE #TempTable
(
Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index
Col2 CHAR(4000),
Col3 CHAR(4000)
)
-- Insert 10 dummy records
DECLARE @i INT = 0
WHILE (@i < 10)
BEGIN
INSERT INTO #TempTable VALUES ('Klaus', 'Aschenbrenner')
SET @i += 1
END
END
GO

اگر یک بار دیگر کد آزمایش Performance Counter را اجرا کنید خواهید دید که SQL Server جدول temp را یکبار ساخته و به دفعات از آن استفاده کرده است. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

اگر با استفاده از دستورات DDL دیگر ایندکس های Nonclustered دیگری روی جدول temp بسازید، باز هم جدول temp قادر به Cache شدن نخواهد بود. در SQL Server 2014 برای رفع این مشکل می توانید اینگونه ایندکس ها را به روش Inline تعریف کنید:

ALTER PROCEDURE PopulateTempTable
AS
BEGIN
-- Create a new temp table
CREATE TABLE #TempTable
(
Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index
Col2 CHAR(100) INDEX idx_Col2,
Col3 CHAR(100) INDEX idx_Col3
)
-- Insert 10 dummy records
DECLARE @i INT = 0
WHILE (@i < 10)
BEGIN
INSERT INTO #TempTable VALUES ('Klaus', 'Aschenbrenner')
SET @i += 1
END
END
GO

سخن پایانی

Caching جداول Temp در SQL Server، همانطوری که می بینید من دو ایندکس Nonclustered روی جدول temp مستقیماً هنگام ساخت جدول ایجاد کرده ام و مجدداً این بدان معنی است که ما دستورات DDL و DML را با هم تلفیق نکرده ایم. امکان تعریف ایندکس روی جداول temp هنگام تعریف جدول یک قابلیت بسیار عالی در SQL Server محسوب می شود. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.

مشاهده کامل‌ترین و بروزترین آموزش sql server در نیک آموز