This post is primary base on the Bob ward’s “Inside tempdb” session in the SQL SUMMIT 2011 .
- Round robin and proportional fill .
- Proportion calculation is base on
- After recovery
- When adding or removing files
- Every 8192 extent allocations
For extend grow ( Both uniform extend and mixed extend)
- Find the free extend in the current file. ( for uniform extend, looks for GAM, for mix extend, look for SCAM)
- If no space is the current file, find extend in other file.
- If no free space on all files, grow the file
De allocation
- Both uniform extend and mix extend will need to update PFS.
- Mix extend needs to update SCAM, uniform extend needs to update GAM.
- For tables less than 8 MB, sql server keep one pages and one IAM page.
- For tables larger than 8MB, sql server keeps the system table object.
Tempdb contention
A lot of allocation and de allocation will cause the temp db contention on GAM, SCAM ,PFS and internal objects.
The symptom is to find the PAGELATCH wait in tempdb files
Solution
- Temp Table caching
- Trace flag 1118; Server wide setting. Except for IAM page, all the pages allocation would use uniform extend.
- Multiple data files. so the GAM,SCAM can be spread out to multiple files.
- Prevent autogrow. –T 1117 would cause all the file grow the same. It is server wide setting.
- Evaluate temp table usage
- Find out what is consuming internal objects
Trace flag 1106
Trace flag 1106 can use to see the file switch in the sys.dm_os_ring_buffers
Create 2 tempdb file and restart the SQL server
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 100Mb)
GO
ALTER DATABASE [tempdb]
ADD FILE ( NAME = N'tempdev2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdev2.ndf' , SIZE = 100Mb , FILEGROWTH = 10%)
GO
Create the store procedure to add one page for every iteration
use master
go
drop proc sunny_proc
go
create proc sunny_proc @pagecount int
as
-- This create one page for every execution
create table #sunny (c1 int, c2 char(7000) not null)
declare @x int
set @x = 0
while (@x < @pagecount)
begin
insert into #sunny values (@x, 'sunny')
set @x = @x + 1
end
go
Enable trace flag
dbcc traceon(1106, -1)
Execute the sp to add 8 pages
exec sunny_proc 8
Query the dm_os_ring_bugger
declare @ts_now bigint
select @ts_now=cpu_ticks/(cpu_ticks/ms_ticks) from sys.dm_os_sys_info
select record_id,
dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
CASE
WHEN event = 0 THEN 'Allocation Cache Init'
WHEN event = 1 THEN 'Allocation Cache Add Entry'
WHEN event = 2 THEN 'Allocation Cache RMV Entry'
WHEN event = 3 THEN 'Allocation Cache Reinit'
WHEN event = 4 THEN 'Allocation Cache Free'
WHEN event = 5 THEN 'Truncate Allocation Unit'
WHEN event = 10 THEN 'PFS Alloc Page'
WHEN event = 11 THEN 'PFS Dealloc Page'
WHEN event = 20 THEN 'IAM Set Bit'
WHEN event = 21 THEN 'IAM Clear Bit'
WHEN event = 22 THEN 'GAM Set Bit'
WHEN event = 23 THEN 'GAM Clear Bit'
WHEN event = 24 THEN 'SGAM Set Bit'
WHEN event = 25 THEN 'SGAM Clear Bit'
WHEN event = 26 THEN 'SGAM Set Bit NX'
WHEN event = 27 THEN 'SGAM Clear Bit NX'
WHEN event = 28 THEN 'GAM_ZAP_EXTENT'
WHEN event = 40 THEN 'FORMAT IAM PAGE'
WHEN event = 41 THEN 'FORMAT PAGE'
WHEN event = 42 THEN 'REASSIGN IAM PAGE'
WHEN event = 50 THEN 'Worktable Cache Add IAM'
WHEN event = 51 THEN 'Worktable Cache Add Page'
WHEN event = 52 THEN 'Worktable Cache RMV IAM'
WHEN event = 53 THEN 'Worktable Cache RMV Page'
WHEN event = 61 THEN 'IAM Cache Destroy'
WHEN event = 62 THEN 'IAM Cache Add Page'
WHEN event = 63 THEN 'IAM Cache Refresh Requested'
ELSE 'Unknown Event'
END,
session_id,
page_id,
allocation_unit_id
from
(
select
xml_record.value('(./Record/@id)[1]', 'int') as record_id,
xml_record.value('(./Record/ALLOC/Event)[1]', 'int') as event,
xml_record.value('(./Record/ALLOC/SpId)[1]', 'int') as session_id,
xml_record.value('(./Record/ALLOC/PageId)[1]', 'varchar(100)') as page_id,
xml_record.value('(./Record/ALLOC/AuId)[1]', 'varchar(100)') as allocation_unit_id,
timestamp
from
(
select timestamp, convert(xml, record) as xml_record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_ALLOC_TRACE'
) as the_record
) as ring_buffer_record
-- where ring_buffer_record.event = 10
--order by EventTime
order by record_id
go
Now, we can see the page allocation is switch between file ID 1 and 3. ( FIle ID 2 is the log file so it wont go there).
Trace flag 1118
Before we start the testing :
- Bounce SQL server and turn on the 1106
- Add 8 Pages and you can see the page is spreading between file id 1 and 3
Turn on the trace flag 1118 and add another 8 pages.
dbcc traceon(1118,-1)
we will get consecutive pages for the uniform extend. Except IAM page which is always will need to use Mixed extend.
0 comments:
Post a Comment