???????Щ????????￡?SQL Server?????????????cache Temp Tables???????????????ζ??????????????????????????SQL Server??????????????????????????????????????????????????SQL Server?????????????????????PFS??GAM??SGAM??????????????Щ??????????????????????????????Latch Contention????Paul White?????????????????????????????????Χ?????
??????????????????????????洢???????????DML??Data Manipulation Language ??????????????DDL??Data Definition Language ???????????????????????????????
1 -- Create a new stored procedure
2 CREATE PROCEDURE PopulateTempTable
3 AS
4 BEGIN
5     -- Create a new temp table
6     CREATE TABLE #TempTable
7     (
8         Col1 INT IDENTITY(1?? 1)??
9         Col2 CHAR(4000)??
10         Col3 CHAR(4000)
11     )
12
13     -- Create a unique clustered index on the previous created temp table
14     CREATE UNIQUE CLUSTERED INDEX idx_c1 ON #TempTable(Col1)
15
16     -- Insert 10 dummy records
17     DECLARE @i INT = 0
18     WHILE (@i < 10)
19     BEGIN
20         INSERT INTO #TempTable VALUES ('Woody'?? 'Tu')
21         SET @i += 1
22     END
23 END
24 GO
?????????????DDL???CREATE UNIQUE CLUSTERED INDEX ????????????????????????????DDL??DML??????SQL Server?????????????????????????????????DMV sys.dm_os_performance_counters ??????????????????Temp Tables Creation Rate ???????
1 DECLARE @table_counter_before_test BIGINT;
2 SELECT @table_counter_before_test = cntr_value FROM sys.dm_os_performance_counters
3 WHERE counter_name = 'Temp Tables Creation Rate'
4
5 DECLARE @i INT = 0
6 WHILE (@i < 1000)
7 BEGIN
8     EXEC PopulateTempTable
9     SET @i += 1
10 END
11
12 DECLARE @table_counter_after_test BIGINT;
13 SELECT @table_counter_after_test = cntr_value FROM sys.dm_os_performance_counters
14 WHERE counter_name = 'Temp Tables Creation Rate'
15
16 PRINT 'Temp tables created during the test: ' + CONVERT(VARCHAR(100)?? @table_counter_after_test - @table_counter_before_test)
17 GO