Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @T TABLE(X INT)
- INSERT INTO @T VALUES(1),(2)
- SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
- FROM @T
- File:Page:Slot
- ----------------
- (1:148:0)
- (1:148:1)
- IF (1 = 0)
- BEGIN
- DECLARE @T TABLE(X INT)
- END
- --Works fine
- SELECT *
- FROM @T
- /*Declare a table variable with some unusual options.*/
- DECLARE @T TABLE
- (
- [dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED,
- A INT CHECK (A > 0),
- B INT DEFAULT 1,
- InRowFiller char(1000) DEFAULT REPLICATE('A',1000),
- OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000),
- LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000),
- UNIQUE CLUSTERED (A,B)
- WITH (FILLFACTOR = 80,
- IGNORE_DUP_KEY = ON,
- DATA_COMPRESSION = PAGE,
- ALLOW_ROW_LOCKS=ON,
- ALLOW_PAGE_LOCKS=ON)
- )
- INSERT INTO @T (A)
- VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)
- SELECT t.object_id,
- t.name,
- p.rows,
- a.type_desc,
- a.total_pages,
- a.used_pages,
- a.data_pages,
- p.data_compression_desc
- FROM tempdb.sys.partitions AS p
- INNER JOIN tempdb.sys.system_internals_allocation_units AS a
- ON p.hobt_id = a.container_id
- INNER JOIN tempdb.sys.tables AS t
- ON t.object_id = p.object_id
- INNER JOIN tempdb.sys.columns AS c
- ON c.object_id = p.object_id
- WHERE c.name = 'dba.se'
- Duplicate key was ignored.
- +-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
- | object_id | name | rows | type_desc | total_pages | used_pages | data_pages | data_compression_desc |
- +-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
- | 574625090 | #22401542 | 13 | IN_ROW_DATA | 2 | 2 | 1 | PAGE |
- | 574625090 | #22401542 | 13 | LOB_DATA | 24 | 19 | 0 | PAGE |
- | 574625090 | #22401542 | 13 | ROW_OVERFLOW_DATA | 16 | 14 | 0 | PAGE |
- | 574625090 | #22401542 | 13 | IN_ROW_DATA | 2 | 2 | 1 | NONE |
- +-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
- DECLARE @T TABLE(X INT)
- CREATE TABLE #T(X INT)
- BEGIN TRAN
- INSERT #T
- OUTPUT INSERTED.X INTO @T
- VALUES(1),(2),(3)
- /*Both have 3 rows*/
- SELECT * FROM #T
- SELECT * FROM @T
- ROLLBACK
- /*Only table variable now has rows*/
- SELECT * FROM #T
- SELECT * FROM @T
- DROP TABLE #T
- USE tempdb;
- /*
- Don't run this on a busy server.
- Ideally should be no concurrent activity at all
- */
- CHECKPOINT;
- GO
- /*
- The 2nd column is binary to allow easier correlation with log output shown later*/
- DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, B BINARY(10))
- INSERT INTO @T
- VALUES (1, 0x41414141414141414141),
- (2, 0x41414141414141414141)
- UPDATE @T
- SET B = 0x42424242424242424242
- DELETE FROM @T
- /*Put allocation_unit_id into CONTEXT_INFO to access in next batch*/
- DECLARE @allocId BIGINT, @Context_Info VARBINARY(128)
- SELECT @Context_Info = allocation_unit_id,
- @allocId = a.allocation_unit_id
- FROM sys.system_internals_allocation_units a
- INNER JOIN sys.partitions p
- ON p.hobt_id = a.container_id
- INNER JOIN sys.columns c
- ON c.object_id = p.object_id
- WHERE ( c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3' )
- SET CONTEXT_INFO @Context_Info
- /*Check log for records related to modifications of table variable itself*/
- SELECT Operation,
- Context,
- AllocUnitName,
- [RowLog Contents 0],
- [Log Record Length]
- FROM fn_dblog(NULL, NULL)
- WHERE AllocUnitId = @allocId
- GO
- /*Check total log usage including updates against system tables*/
- DECLARE @allocId BIGINT = CAST(CONTEXT_INFO() AS BINARY(8));
- WITH T
- AS (SELECT Operation,
- Context,
- CASE
- WHEN AllocUnitId = @allocId THEN 'Table Variable'
- WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table'
- ELSE AllocUnitName
- END AS AllocUnitName,
- [Log Record Length]
- FROM fn_dblog(NULL, NULL) AS D)
- SELECT Operation = CASE
- WHEN GROUPING(Operation) = 1 THEN 'Total'
- ELSE Operation
- END,
- Context,
- AllocUnitName,
- [Size in Bytes] = COALESCE(SUM([Log Record Length]), 0),
- Cnt = COUNT(*)
- FROM T
- GROUP BY GROUPING SETS( ( Operation, Context, AllocUnitName ), ( ) )
- ORDER BY GROUPING(Operation),
- AllocUnitName
- DECLARE @T TABLE(I INT);
- INSERT INTO @T VALUES(1),(2),(3),(4),(5)
- CREATE TABLE #T(I INT)
- /*Reference to #T means this statement is subject to deferred compile*/
- SELECT * FROM @T WHERE NOT EXISTS(SELECT * FROM #T)
- DROP TABLE #T
- DECLARE @tv_target TABLE (c11 int, c22 char(100))
- DBCC TRACEON(1200,-1,3604)
- INSERT INTO @tv_target (c11, c22)
- VALUES (1, REPLICATE('A',100)), (2, REPLICATE('A',100))
- DBCC TRACEOFF(1200,-1,3604)
- DECLARE @T TABLE(X INT);
- SELECT X
- FROM @T
- OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8607)
- *** Output Tree: (trivial plan) ***
- PhyOp_TableScan TBL: @T Bmk ( Bmk1000) IsRow: COL: IsBaseRow1002 Hints( NOLOCK )
- SET NOCOUNT ON;
- CREATE TABLE #T( [ID] [int] IDENTITY NOT NULL,
- [Filler] [char](8000) NULL,
- PRIMARY KEY CLUSTERED ([ID] DESC))
- DECLARE @T TABLE ( [ID] [int] IDENTITY NOT NULL,
- [Filler] [char](8000) NULL,
- PRIMARY KEY CLUSTERED ([ID] DESC))
- DECLARE @I INT = 0
- WHILE (@I < 10000)
- BEGIN
- INSERT INTO #T DEFAULT VALUES
- INSERT INTO @T DEFAULT VALUES
- SET @I += 1
- END
- /*Run once so compilation output doesn't appear in lock output*/
- EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')
- DBCC TRACEON(1200,3604,-1)
- SELECT *, sys.fn_PhysLocFormatter(%%physloc%%)
- FROM @T
- PRINT '--*--'
- EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')
- DBCC TRACEOFF(1200,3604,-1)
- DROP TABLE #T
- Process 58 acquiring Sch-S lock on OBJECT: 2:-1325894110:0 (class bit0 ref1) result: OK
- --*--
- Process 58 acquiring IS lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK
- Process 58 acquiring S lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK
- Process 58 releasing lock on OBJECT: 2:-1293893996:0
- --BEGIN TRAN;
- CREATE TABLE #T (X INT,Y CHAR(4000) NULL);
- INSERT INTO #T (X) VALUES(1)
- SELECT CASE resource_type
- WHEN 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id, 2)
- WHEN 'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME(object_id, 2)
- FROM tempdb.sys.allocation_units a
- JOIN tempdb.sys.partitions p ON a.container_id = p.hobt_id
- WHERE a.allocation_unit_id = resource_associated_entity_id)
- WHEN 'DATABASE' THEN DB_NAME(resource_database_id)
- ELSE (SELECT OBJECT_NAME(object_id, 2)
- FROM tempdb.sys.partitions
- WHERE partition_id = resource_associated_entity_id)
- END AS object_name,
- *
- FROM sys.dm_tran_locks
- WHERE request_session_id = @@SPID
- DROP TABLE #T
- -- ROLLBACK
- DECLARE @T TABLE (
- C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/
- C2 INT INDEX IX2 NONCLUSTERED,
- INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/
- );
- DECLARE @T TABLE
- (
- c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/
- )
- INSERT INTO @DATA ( ... )
- EXEC('SELECT .. FROM ...')
- CREATE TABLE #T(X INT, Filler char(8000) NULL)
- INSERT INTO #T(X)
- SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY @@SPID)
- FROM master..spt_values
- DROP TABLE #T
- DECLARE @T TABLE (
- X INT,
- [dba.se] CHAR(8000) NULL)
- INSERT INTO @T
- (X)
- SELECT TOP 251 Row_number() OVER (ORDER BY (SELECT 0))
- FROM master..spt_values
- SELECT is_modified,
- Count(*) AS page_count
- FROM sys.dm_os_buffer_descriptors
- WHERE database_id = 2
- AND allocation_unit_id = (SELECT a.allocation_unit_id
- FROM tempdb.sys.partitions AS p
- INNER JOIN tempdb.sys.system_internals_allocation_units AS a
- ON p.hobt_id = a.container_id
- INNER JOIN tempdb.sys.columns AS c
- ON c.object_id = p.object_id
- WHERE c.name = 'dba.se')
- GROUP BY is_modified
- is_modified page_count
- ----------- -----------
- 0 192
- 1 61
- SELECT Count(*)
- FROM sys.dm_os_buffer_descriptors
- WHERE database_id = 2
- AND allocation_unit_id = @allocId
- AND page_type = 'DATA_PAGE'
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement