خانه SQL Server Caching جداول Temp در SQL Server 2014 بهینه شده است! SQL Server نوشته شده توسط: تورج عزیزی ۱۶ آبان ۱۳۹۴ زمان مطالعه: 14 دقیقه ۳ (۲) مقدمه در این مقاله با قابلیتی جدید در SQL Server آشنا می شوید که تاثیر بسزایی در Performance دیتابیس tempdb دارد. جداول Temp جداولی هستند که در دیتابیس tempdb ساخته می شوند و نام آنها با # شروع می شود. در صورت برقراری شرایطی خاص 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 اگر یک بار دیگر کد آزمایش Perfrormance Counter را اجرا کنید خواهید دید که SQL Server جدول temp را یکبار ساخته و به دفعات از آن استفاده کرده است. اگر با استفاده از دستورات 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 همانطوری که می بینید من دو ایندکس Nonclustered روی جدول temp مستقیماً هنگام ساخت جدول ایجاد کرده ام و مجدداً این بدان معنی است که ما دستورات DDL و DML را با هم تلفیق نکرده ایم. امکان تعریف ایندکس روی جداول temp هنگام تعریف جدول یک قابلیت بسیار عالی در SQL Server 2014 محسوب می شود. در مورد این قابلیت چطور فکر میکنید؟ چه رتبه ای می‌دهید؟ میانگین ۳ / ۵. از مجموع ۲ اولین نفر باش برچسب ها # Performance# SQL Server# TempDB# آموزش SQL Server# جداول Temp معرفی نویسنده مقالات 18 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تورج عزیزی پروفایل نویسنده معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۶ اردیبهشت SQL Server پایگاه داده برداری چیست؟ بررسی کاربردها، نحوه کار و آینده Vector Database تیم فنی نیک آموز ۰۲ اردیبهشت SQL Server تاثیر ایندکس های Computed-Column روی دستور DBCC CHECKDB تورج عزیزی ۲۶ فروردین SQL Server دستور SELECT TOP در SQL Server تیم فنی نیک آموز ۱۹ فروردین SQL Server راهنمای پیکربندی Authentication و Authorization در SQL Server به بهترین روش تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ ha_zarabi_vb6@outlook.com ۰۷ / ۱۱ / ۹۴ - ۱۲:۱۰ با سلام من تست کردم بر روی سرعت خیلی تاثیر گذار است واقعا از مقاله خوبتون ممنونم با تشکر از شما پاسخ به دیدگاه میلاد فیروزی ۱۷ / ۱۰ / ۹۴ - ۰۸:۴۴ بسیار عالی پاسخ به دیدگاه تورج عزیزی ۲۸ / ۰۹ / ۹۴ - ۰۱:۰۷ فرشید علی اکبری: با ۸ فایل شروع کنید و مانیتور کنید، در صورت وجود Bottleneck ، ۴ فایل اضافه کنید و دوباره بررسی کنید وجود Bottleneck را چک کنید و این روند را تا زمانی که Bottleneck از بین برود ادامه دهید. پاسخ به دیدگاه مهران رحمتی ۲۶ / ۰۸ / ۹۴ - ۱۲:۰۷ سلام مقاله بسیار عالی و کاربردی بود سپاسگذار پاسخ به دیدگاه مسعود طاهری ۱۸ / ۰۸ / ۹۴ - ۰۸:۵۹ دوستان عزیز برای مشاهده پیج های allocated شده و deallocated شده به ازای هر Session در بانک اطلاعاتی TempDB می توانید از ویو dm_db_session_space_usage استفاده کنید به مثال زیر توجه کنید USE master GO —ایجاد جدول موقت CREATE TABLE #TempTable ( ID INT ,Name char(8000)) ; GO —درج رکورد تستی INSERT INTO #TempTable ( ID,Name ) VALUES ( 1,’AAAAAA’ ) ; INSERT INTO #TempTable ( ID,Name ) VALUES ( 2,’BBBBBB’ ) ; INSERT INTO #TempTable ( ID,Name ) VALUES ( 3,’CCCCCC’ ) ; GO —تعداد پیج های تخصیص یافته SELECT session_id, database_id, DB_NAME(database_id), user_objects_alloc_page_count FROM sys.dm_db_session_space_usage WHERE session_id =@@SPID ; GO —مشاهده پیج ها مربوط به جدول DBCC IND(‘TEMPDB’,’#TempTable’,-1) GO —مشاهده محتوای پیج DBCC TRACEON(3604) DBCC PAGE(‘TEMPDB’,1,341,3) GO –مشاهده لاگ های درج شده در لاگ فایل USE tempdb GO SELECT [Current LSN],[Transaction ID],Operation FROM SYS.fn_dblog(NULL,NULL) ضمنا یکی از مهمترین دلایل افزایش دیتا فایل های TempDB کاهش Latch Contention می باشد. پاسخ به دیدگاه Hamid J. Fard ۱۷ / ۰۸ / ۹۴ - ۰۴:۴۰ با سلام مقاله خوبی است. البته این نکته رو بگم که در مورد استفاده نکردن از Clustere Index ها – داده ها Cache نمی شوند بلکه Stabe Plan ساخته شده دوباره استفاده میشود. و اینکه دادهای کمتر از ۸ مگابایت داخل حافظه هستند و در tempdb نوشته نمی شوند به غیر از قسمت لاگ تراکنش و Structure جدول. فرشید علی اکبری: برای هر پردازنده یک دیتا فایل و حداکثر ۸. این به دلیل وجود Parallel Execution Plan ها است. پاسخ به دیدگاه فرشید علی اکبری ۱۸ / ۰۸ / ۹۴ - ۰۲:۵۹ مرسی از راهنمایی تون. تشکر. پاسخ به دیدگاه فرشید علی اکبری ۱۶ / ۰۸ / ۹۴ - ۱۱:۲۷ سلام آقای عزیزی تشکر از مقاله بسیار کارآمد و خوبی که ارائه دادید و به نکته خوبی اشاره کرده بودید. من یک سئوال از خدمت شما دارم و اون اینکه: با توجه به اینکه نصب اسکوئل سرور ۲۰۱۶ با نسخه های قبلی کمی متفاوت تر شده، اگه نصبش کرده باشید حتماً متوجه شدید که مسیر Tempdb را به همراه تعداد Data File هایی که می تونه باعث تاثیر بسزایی در Performance باشه ( و اگه درست خاطرم باشه حداکثر تا ۸ دیتافایل) می تونید براش ایجاد کنید. شما فکر میکنید با چه معیاری باید دقیقاً تعیین کنیم که آیا بانک اطلاعاتی ما، با توجه به اینکه مسیر Tempdb را تغییر داده ایم ولی نیاز به تعیین دیتا فایل در نسخه ۲۰۱۶ داره یا نه؟ و اگه داره تعداد اون رو برچه مبنایی باید تعیین کنیم؟ پاسخ به دیدگاه مسعود طاهری ۱۷ / ۰۸ / ۹۴ - ۰۷:۱۰ سلام یکی از مقاله های خوب برای تنظیم TempDB http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/ پاسخ به دیدگاه محمّدامین مزیدی ۱۶ / ۰۸ / ۹۴ - ۱۱:۴۲ با سلام مقاله خوبی بود . این مطالب می تونه تکمیل ترش کنه . اگه مثال تون رو با خواندن محتوای لاگ فایل می زدید کمی قشنگ تر بود . در هر صورت مطلب خوبی بود خسته نباشید . There are two kinds of the temporary tables: local and global. Local temporary tables are named starting with the # symbol, and they are visible only in the session in which they were created and in the modules called from that session Global temporary tables are created with the names starting with ## symbols, and they are visible to all sessions In order to improve performance, SQL Server introduces the concept of temporary table caching. This term is a bit confusing. It relates to temporary table allocation rather than data pages, which are cached in a buffer pool, similar to regular tables In a nutshell, with temporary table caching, instead of dropping the table, SQL Server truncates it, keeping two pages per index pre-allocated: one IAM and one data page. The next time the table is created, SQL Server will reuse these pages, which helps reduce the number of modifications required in the allocation map page SQL Server does not cache IAM and data pages for global temporary tables nor does it cache local temporary tables created in the session scope. Only the temporary tables created within the stored procedures and triggers are cached There are also a few requirements of the table and code, including The table needs to be smaller than eight megabytes. Large tables are not cached There are no DDL statements that change the table structure. Any schema modification statements in the code, with exception of DROP TABLE, will prevent a temporary table caching .However, you can create indexes on the table and, as mentioned previously, SQL Server will cache themThere are no named constraints defined in the table. Unnamed constraints will not prevent the caching پاسخ به دیدگاه مسعود طاهری ۱۷ / ۰۸ / ۹۴ - ۰۷:۱۰ سلام یکی از مقاله های خوب برای تنظیم TempDB http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/ پاسخ به دیدگاه
