بهبود Tempdb در SQL Server

در این مقاله قصد دارم به بهبودهایی که در نسخه SQL Server 2019 برای Tempdb انجام شده است، بپردازم.

راه اندازی و نصب

پس از نصب SQL Server 2019 CTP 3.2  را روی ماشین مجازی خود که دارای ۸ گیگابایت حافظه (حداکثر حافظه سرور روی ۶ گیگابایت) و ۴ تا vCPU است. چهار  فایل داده tempdb ایجاد می کنیم که هر کدام به اندازه ۱ گیگابایت می‌باشد.

در مرحله بعد یک کپی از WideWorldImporters را بازیابی کرده و سپس سه stored procedure را ایجاد می‌کنیم که در کد زیر سینتکس آنها نشان داده شده است. هر stored procedure یک ورودی date را می‌پذیرد و همه ردیف‌های Sales.Order و Sales.OrderLines برای آن date در شی temporary وارد می‌شود.

Sales.usp_OrderInfoTV، شی یک متغیر جدولی است، Sales.usp_OrderInfoTT شی یک جدول temporary است که از طریق SELECT … INTO تعریف می‌شود و در ادامه یک nonclustered اضافه می‌شود. در نهایت Sales.usp_OrderInfoTTALT، شی یک جدول temporary از پیش تعریف شده است که برای داشتن یک ستون اضافی، ویرایش می‌شود. پس از اضافه شدن داده‌ها به شی Temporary، یک دستور SELECT وجود دارد که با جدول Sales.Customers جوین (join) می‌شود.

/*
    Create the stored procedures
  */
  USE [WideWorldImporters];
  GO
 
  DROP PROCEDURE IF EXISTS Sales.usp_OrderInfoTV
  GO
 
  CREATE PROCEDURE Sales.usp_OrderInfoTV @OrderDate DATE
  AS
  BEGIN
    DECLARE @OrdersInfo TABLE (
      OrderID INT,
      OrderLineID INT,
      CustomerID INT,
      StockItemID INT,
      Quantity INT,
      UnitPrice DECIMAL(18,2),
      OrderDate DATE);
 
    INSERT INTO @OrdersInfo (
      OrderID,
      OrderLineID,
      CustomerID,
      StockItemID,
      Quantity,
      UnitPrice,
      OrderDate)
    SELECT 
      o.OrderID,
      ol.OrderLineID,
      o.CustomerID,
      ol.StockItemID,
      ol.Quantity,
      ol.UnitPrice,
      OrderDate
    FROM Sales.Orders o
    INNER JOIN Sales.OrderLines ol
      ON o.OrderID = ol.OrderID
    WHERE o.OrderDate = @OrderDate;
 
    SELECT o.OrderID,
      c.CustomerName,
      SUM (o.Quantity),
      SUM (o.UnitPrice)
    FROM @OrdersInfo o
    JOIN Sales.Customers c
      ON o.CustomerID = c.CustomerID
    GROUP BY o.OrderID, c.CustomerName;
  END
  GO
 
  DROP PROCEDURE IF EXISTS  Sales.usp_OrderInfoTT
  GO
 
  CREATE PROCEDURE Sales.usp_OrderInfoTT @OrderDate DATE
  AS
  BEGIN
    SELECT 
      o.OrderID,
      ol.OrderLineID,
      o.CustomerID,
      ol.StockItemID,
      ol.Quantity,
      ol.UnitPrice,
      OrderDate
    INTO #temporderinfo 
    FROM Sales.Orders o
    INNER JOIN Sales.OrderLines ol
      ON o.OrderID = ol.OrderID
    WHERE o.OrderDate = @OrderDate;
 
    SELECT o.OrderID,
      c.CustomerName,
      SUM (o.Quantity),
      SUM (o.UnitPrice)
    FROM #temporderinfo o
    JOIN Sales.Customers c
      ON o.CustomerID = c.CustomerID
    GROUP BY o.OrderID, c.CustomerName
  END
  GO
 
  DROP PROCEDURE IF EXISTS  Sales.usp_OrderInfoTTALT
  GO
 
  CREATE PROCEDURE Sales.usp_OrderInfoTTALT @OrderDate DATE
  AS
  BEGIN
    CREATE TABLE #temporderinfo (
      OrderID INT,
      OrderLineID INT,
      CustomerID INT,
      StockItemID INT,
      Quantity INT,
      UnitPrice DECIMAL(18,2));
 
    INSERT INTO #temporderinfo (
      OrderID,
      OrderLineID,
      CustomerID,
      StockItemID,
      Quantity,
      UnitPrice)
    SELECT 
      o.OrderID,
      ol.OrderLineID,
      o.CustomerID,
      ol.StockItemID,
      ol.Quantity,
      ol.UnitPrice
    FROM Sales.Orders o
    INNER JOIN Sales.OrderLines ol
      ON o.OrderID = ol.OrderID
    WHERE o.OrderDate = @OrderDate;
 
    SELECT o.OrderID,
      c.CustomerName,
      SUM (o.Quantity),
      SUM (o.UnitPrice)
    FROM #temporderinfo o
    JOIN Sales.Customers c
      ON o.CustomerID  c.CustomerID
    GROUP BY o.OrderID, c.CustomerName
  END
  GO
 
  /*
    Create tables to hold testing data
  */
 
  USE [WideWorldImporters];
  GO
 
  CREATE TABLE [dbo].[PerfTesting_Tests] (
    [TestID] INT IDENTITY(1,1), 
    [TestName] VARCHAR (200),
    [TestStartTime] DATETIME2,
    [TestEndTime] DATETIME2
  ) ON [PRIMARY];
  GO
 
  CREATE TABLE [dbo].[PerfTesting_WaitStats]   (
    [TestID] [int] NOT NULL,
    [CaptureDate] [datetime] NOT NULL DEFAULT (sysdatetime()),
    [WaitType] [nvarchar](60) NOT NULL,
    [Wait_S] [decimal](16, 2) NULL,
    [Resource_S] [decimal](16, 2) NULL,
    [Signal_S] [decimal](16, 2) NULL,
    [WaitCount] [bigint] NULL,
    [Percentage] [decimal](5, 2) NULL,
    [AvgWait_S] [decimal](16, 4) NULL,
    [AvgRes_S] [decimal](16, 4) NULL,
    [AvgSig_S] [decimal](16, 4) NULL
  ) ON [PRIMARY];
  GO
 
  /*
    Enable Query Store
    (testing settings, not exactly what 
    I would recommend for production)
  */
 
  USE [master];
  GO
 
  ALTER DATABASE [WideWorldImporters] SET QUERY_STORE = ON;
  GO
 
  ALTER DATABASE [WideWorldImporters] SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE, 
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), 
    DATA_FLUSH_INTERVAL_SECONDS = 600, 
    INTERVAL_LENGTH_MINUTES = 10, 
    MAX_STORAGE_SIZE_MB = 1024, 
    QUERY_CAPTURE_MODE = AUTO, 
    SIZE_BASED_CLEANUP_MODE = AUTO);
  GO

تست

رفتار پیش‌ فرض برای SQL Server 2019 این است که متادیتای Tempdb برای حافظه بهینه‌سازی نشده است و ما می‌توانیم این موضوع را با بررسی sys.configurations متوجه شویم.

SELECT *
FROM sys.configurations
WHERE configuration_id = 1589;

برای هر سه Stored Procedure، ما از sqlcmd برای تولید ۲۰ نخ (thread) همزمان که یکی از دو فایل .sql  متفاوت را اجرا می‌کنند، استفاده خواهیم کرد. اولین فایل .sql که توسط ۱۹ نخ (thread) استفاده می‌شود، این پروسیجر را در یک حلقه، ۱۰۰۰ بار اجرا می‌کند. فایل .sql دوم که فقط ۱ نخ (thread) دارد، این پروسیجر را در یک حلقه، ۳۰۰۰ بار اجرا می‌کند. این فایل همچنین شامل TSQL برای ثبت دو معیار مورد نظر است، یعنی Total Duration  و wait statistics. ما از Query Store برای گرفتن میانگین مدت زمان پروسیجر استفاده خواهیم کرد.

/*
    Example of first .sql file
    which calls the SP 1000 times
  */
 
  SET NOCOUNT ON;
  GO
 
  USE [WideWorldImporters];
  GO
 
  DECLARE @StartDate DATE;
  DECLARE @MaxDate DATE;
  DECLARE @Date DATE;
  DECLARE @Counter INT = 1;
 
  SELECT @StartDATE = MIN(OrderDate) FROM [WideWorldImporters].[Sales].[Orders];
  SELECT @MaxDATE = MAX(OrderDate) FROM [WideWorldImporters].[Sales].[Orders];
 
  SET @Date = @StartDate;
 
  WHILE @Counter <= 1000
  BEGIN
    EXEC [Sales].[usp_OrderInfoTT] @Date;
 
    IF @Date <= @MaxDate
    BEGIN
      SET @Date = DATEADD(DAY, 1, @Date);
    END
    ELSE
    BEGIN
      SET @Date = @StartDate;
    END
 
    SET @Counter = @Counter + 1;
  END
  GO
 
  /*
    Example of second .sql file
    which calls the SP 3000 times
    and captures total duration and
    wait statisics
  */
 
  SET NOCOUNT ON;
  GO
 
  USE [WideWorldImporters];
  GO
 
  DECLARE @StartDate DATE;
  DECLARE @MaxDate DATE;
  DECLARE @DATE DATE;
  DECLARE @Counter INT = 1;
  DECLARE @TestID INT;
  DECLARE @TestName VARCHAR(200) = 'Execution of usp_OrderInfoTT - Disk Based System Tables';
 
  INSERT INTO [WideWorldImporters].[dbo].[PerfTesting_Tests] ([TestName]) VALUES (@TestName);
 
  SELECT @TestID = MAX(TestID) FROM [WideWorldImporters].[dbo].[PerfTesting_Tests];
 
  SELECT @StartDATE = MIN(OrderDate) FROM [WideWorldImporters].[Sales].[Orders];
 
  SELECT @MaxDATE = MAX(OrderDate) FROM [WideWorldImporters].[Sales].[Orders];
 
  SET @Date = @StartDate;
 
  IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
      WHERE [name] = N'##SQLskillsStats1')
      DROP TABLE [##SQLskillsStats1];
 
  IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
      WHERE [name] = N'##SQLskillsStats2')
      DROP TABLE [##SQLskillsStats2];
 
  SELECT [wait_type], [waiting_tasks_count], [wait_time_ms],
         [max_wait_time_ms], [signal_wait_time_ms]
  INTO ##SQLskillsStats1
  FROM sys.dm_os_wait_stats;
 
  /* 
    set start time 
  */
 
  UPDATE [WideWorldImporters].[dbo].[PerfTesting_Tests] 
  SET [TestStartTime] = SYSDATETIME()
  WHERE [TestID] = @TestID;
 
  WHILE @Counter <= 3000
  BEGIN
    EXEC [Sales].[usp_OrderInfoTT] @Date;
 
    IF @Date <= @MaxDate
    BEGIN
      SET @Date = DATEADD(DAY, 1, @Date);
    END
    ELSE
    BEGIN
      SET @Date = @StartDate;
    END
 
    SET @Counter = @Counter + 1
  END
 
  /* 
    set end time 
  */
 
  UPDATE [WideWorldImporters].[dbo].[PerfTesting_Tests] 
  SET [TestEndTime] = SYSDATETIME() 
  WHERE [TestID] = @TestID;
 
  SELECT [wait_type], [waiting_tasks_count], [wait_time_ms],
         [max_wait_time_ms], [signal_wait_time_ms]
  INTO ##SQLskillsStats2
  FROM sys.dm_os_wait_stats;
 
  WITH [DiffWaits] AS
  (SELECT
    -- Waits that weren't in the first snapshot
          [ts2].[wait_type],
          [ts2].[wait_time_ms],
          [ts2].[signal_wait_time_ms],
          [ts2].[waiting_tasks_count]
      FROM [##SQLskillsStats2] AS [ts2]
      LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]
          ON [ts2].[wait_type] = [ts1].[wait_type]
      WHERE [ts1].[wait_type] IS NULL
      AND [ts2].[wait_time_ms] > 0
  UNION
  SELECT
  -- Diff of waits in both snapshots
          [ts2].[wait_type],
          [ts2].[wait_time_ms] - [ts1].[wait_time_ms] AS [wait_time_ms],
          [ts2].[signal_wait_time_ms] - [ts1].[signal_wait_time_ms] AS [signal_wait_time_ms],
          [ts2].[waiting_tasks_count] - [ts1].[waiting_tasks_count] AS [waiting_tasks_count]
      FROM [##SQLskillsStats2] AS [ts2]
      LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]
          ON [ts2].[wait_type] = [ts1].[wait_type]
      WHERE [ts1].[wait_type] IS NOT NULL
      AND [ts2].[waiting_tasks_count] - [ts1].[waiting_tasks_count] > 0
      AND [ts2].[wait_time_ms] - [ts1].[wait_time_ms] &gt; 0),
  [Waits] AS
      (SELECT
          [wait_type],
          [wait_time_ms] / 1000.0 AS [WaitS],
          ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
          [signal_wait_time_ms] / 1000.0 AS [SignalS],
          [waiting_tasks_count] AS [WaitCount],
          ۱۰۰.۰ * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
          ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
      FROM [DiffWaits]
      WHERE [wait_type] NOT IN (
          -- These wait types are almost 100% never a problem and so they are
          -- filtered out to avoid them skewing the results.
          N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', 
          N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', 
          N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT',
          N'CLR_SEMAPHORE', N'CXCONSUMER', N'DBMIRROR_DBM_EVENT', 
          N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', 
          N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', 
          N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', 
          N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT',
          N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', 
          N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', 
          N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',  N'PARALLEL_REDO_DRAIN_WORKER', 
          N'PARALLEL_REDO_LOG_CACHE', N'PARALLEL_REDO_TRAN_LIST', N'PARALLEL_REDO_WORKER_SYNC', 
          N'PARALLEL_REDO_WORKER_WAIT_WORK', N'PREEMPTIVE_XE_GETTARGETSTATE', 
          N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', 
          N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE', 
          N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
          N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK', N'REQUEST_FOR_DEADLOCK_SEARCH', 
          N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', 
          N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY',
          N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', 
          N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', 
          N'SNI_HTTP_ACCEPT', N'SOS_WORK_DISPATCHER', N'SP_SERVER_DIAGNOSTICS_SLEEP',
          N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 
          N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', 
          N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_RECOVERY', N'WAIT_XTP_HOST_WAIT', 
          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE',
          N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT' 
      )
    )
  INSERT INTO [WideWorldImporters].[dbo].[PerfTesting_WaitStats] (
    [TestID],
    [WaitType] ,
    [Wait_S] ,
    [Resource_S] ,
    [Signal_S] ,
    [WaitCount] ,
    [Percentage] ,
    [AvgWait_S] ,
    [AvgRes_S] ,
    [AvgSig_S]
  )
  SELECT
    @TestID,
      [W1].[wait_type] AS [WaitType],
      CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
      CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],
      CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],
      [W1].[WaitCount] AS [WaitCount],
      CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
      CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
      CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
      CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
  FROM [Waits] AS [W1]
  INNER JOIN [Waits] AS [W2]
      ON [W2].[RowNum] <= [W1].[RowNum]
  GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
      [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
  HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
  GO
 
  -- Cleanup
  IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
      WHERE [name] = N'##SQLskillsStats1')
      DROP TABLE [##SQLskillsStats1];
 
  IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
      WHERE [name] = N'##SQLskillsStats2')
      DROP TABLE [##SQLskillsStats2];
  GO

نمونه‌ای از فایل خط فرمان به صورت زیر است:

SELECT *, DATEDIFF(SECOND, TestStartTime, TestEndTime) AS [TotalDuration]
FROM [dbo].[PerfTesting_Tests]
ORDER BY [TestID];

Stored Procedureها با جداول Temporary (usp_OrderInfoTT و usp_OrderInfoTTC) بیشتر طول کشید تا تکمیل شود. کارایی کوئری را بررسی می‌کنیم:

SELECT
    [qsq].[query_id], 
    [qsp].[plan_id],
    OBJECT_NAME([qsq].[object_id]) AS [ObjectName],
    [rs].[count_executions],
    [rs].[last_execution_time],
    [rs].[avg_duration],
    [rs].[avg_logical_io_reads],
    [qst].[query_sql_text]
  FROM [sys].[query_store_query] [qsq] 
  JOIN [sys].[query_store_query_text] [qst]
    ON [qsq].[query_text_id] = [qst].[query_text_id]
  JOIN [sys].[query_store_plan] [qsp] 
    ON [qsq].[query_id] = [qsp].[query_id]
  JOIN [sys].[query_store_runtime_stats] [rs] 
    ON [qsp].[plan_id] = [rs].[plan_id]
  WHERE ([qsq].[object_id] = OBJECT_ID('Sales.usp_OrderInfoTT'))
  OR ([qsq].[object_id] = OBJECT_ID('Sales.usp_OrderInfoTV'))
  OR ([qsq].[object_id] = OBJECT_ID('Sales.usp_OrderInfoTTALT'))
  ORDER BY [qsq].[query_id], [rs].[last_execution_time];

توجه داشته باشید که ما فقط منتظر PAGELATCH*  برای تست‌های ۱ و ۲ هستیم که پروسیجرهای جداول temporary بودند. برای usp_OrderInfoTV، که از یک متغیر جدول استفاده می‌کند، ما فقط زمان انتظار برای دیدن SOS_SCHEDULER_YIELD را داریم. توجه داشته باشید: این به مورد اصلا به این معنی نیست که شما باید از متغیرهای جدول به جای جداول temporary  استفاده کنید و همچنین به این معنی نیست که برای  متغیرهای جدول منتظر دیدن PAGELATCH نخواهیم بود. این یک سناریوی ساختگی است. من پیشنهاد می‌کنم با کد خود تست کنید تا ببینید چه نوع wait_type هایی ظاهر می‌شود.

اکنون نمونه را برای استفاده از جداول بهینه شده با حافظه (memory-optimized) برای متادیتا Tempdb تغییر می‌دهیم. دو راه برای این کار وجود دارد، از طریق دستور ALTER SERVER CONFIGURATION یا با استفاده از  sp_configureمی‌توانید انجام دهید. از آنجایی که این تنظیمات یک آپشن پیشرفته (Advanced) است، اگر از sp_configure استفاده می‌کنید، ابتدا باید آپشن های پیشرفته (advanced) را فعال کنید.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
GO

پس از این تغییر، لازم است راه اندازی مجدد انجام شود.پس از راه‌اندازی مجدد، اگر دوباره sys.configurations را بررسی کنیم، می‌توانیم جداول متادیتا را به صورت زیر ببینیم:

پس از اجرای مجدد فایل‌های خط فرمان، Total Duration برای ۲۱۰۰۰ اجرای هر پروسیجر، موارد زیر را نشان می‌دهد (توجه داشته باشید که نتایج برای مقایسه آسان‌تر بر اساس Stored Procedure مرتب شده‌اند):

قطعاً بهبودی در کارایی برای usp_OrderInfoTT و usp_OrderInfoTTC و افزایش جزئی در کارایی برای usp_OrderInfoTV وجود داشته است. در ادامه مدت زمان کوئری را بررسی می‌کنیم:

برای همه کوئری‌ها، مدت زمان اجرای کوئری تقریباً یکسان است، به غیر از افزایش مدت زمان اجرای دستور INSERT زمانی که جدول را به صورت pre-created داشته‌ایم، که کاملاً غیرمنتظره است. همچنین شاهد یک تغییر جالب در آمار انتظار (Wait Statistics) هستیم:

برای usp_OrderInfoTT، یک SELECT … INTO برای ایجاد جدول temporary  اجرا می‌شود. انتظارها از PAGELATCH_EX و PAGELATCH_SH به PAGELATCH_EX و SOS_SCHEDULER_YIELD تغییر می‌کند. همچنین دیگر PAGELATCH_SH ها را نمی‌بینیم.

جمع‌بندی

بر اساس این آزمایش، ما شاهد بهبود در همه موارد بودیم. به طور قابل توجهی برای stored procedure با جداول temporary شاهد بهبود کارایی بوده‌ایم. تغییر جزئی برای پروسیجر متغیر جدول وجود داشته است. بسیار مهم است که به یاد داشته باشید که این یک سناریو آزمایشی با حجم داده کم است. من بسیار علاقه‌مند بودم که این سه سناریو بسیار ساده را امتحان کنم تا متوجه شوم چه مواردی ممکن است از بهینه‌سازی حافظه متادیتا Tempdb بیشترین بهبود را داشته باشد.

در این آزمایش‌ها حجم کاری کم بود و برای مدت زمان بسیار محدودی اجرا شدند. در واقع من نتایج متنوع‌تری را با نخ‌های بیشتر می‌توانستم داشته باشم که ارزش بررسی در مقالات دیگری را دارد. مساله اصلی این است که برای همه ویژگی‌ها و عملکردهای جدید، آزمایش مهم است. برای این ویژگی، می‌توانید یک پایه از عملکرد فعلی را داشته باشید که با آن معیارهایی مانند درخواست‌های دسته‌ای (Batch Requests) و آمار انتظار (Wait Statistics) را پس از بهینه‌سازی حافظه متادیتا مقایسه کنید.

نکات بیشتر

استفاده از OLTP در حافظه به یک گروه فایل از نوع MEMORY OPTIMIZED DATA نیاز دارد. بااین‌حال، پس از فعال کردن MEMORY_OPTIMIZED TEMPDB_METADATA، هیچ گروه فایل دیگری برای Tempdb ایجاد نمی‌شود. علاوه بر این، مشخص نیست که آیا جداول بهینه‌سازی شده برای حافظه پایدار هستند (SCHEMA_AND_DATA) یا خیر (SCHEMA_ONLY). معمولاً این مورد را می‌توان از طریق sys.tables (durability_desc) تعیین کرد، اما هیچ موردی برای جداول سیستمی درگیر هنگام کوئری در Tempdb، حتی در هنگام استفاده از Dedicated Administrator Connection باز نمی‌گردد.

شما توانایی مشاهده nonclustered indexها برای جداول بهینه‌سازی شده برای حافظه را دارید. می‌توانید از کوئری زیر استفاده کنید تا ببینید کدام جداول در tempdb برای حافظه بهینه شده‌اند:

SELECT *
  FROM tempdb.sys.dm_db_xtp_object_stats x
  JOIN tempdb.sys.objects o
    ON x.object_id = o.object_id
  JOIN tempdb.sys.schemas s
    ON o.schema_id = s.schema_id;

سپس برای هر یک از جداول، sp_helpindex را اجرا کنید، برای مثال:

EXEC sys.sp_helpindex N'sys.sysobjvalues';

توجه داشته باشید که اگر این یک hash index باشد (که نیاز به سنجش BUCKET_COUNT به عنوان بخشی از روال ایجاد دارد)، توضیحات شامل «nonclustered hash» می‌شود.