April 12, 2006 at 9:39 am
In performing a test this morning, I noticed that my logical counts on a heap that started out with NULLS is very high. I'm hoping others can chime in on what is causing the high logical I/O's
TEST SQL:
--Create tables and populate
DROP TABLE dbo.TableVarWithNulls
DROP TABLE dbo.TableVar
GO
CREATE TABLE dbo.TableVarWithNulls (
[ID] int NOT NULL,
COL1 VARCHAR(50) NULL)
GO
CREATE TABLE dbo.TableVar (
[ID] int NOT NULL,
COL1 VARCHAR(50) NOT NULL)
GO
SET NOCOUNT ON
DECLARE @intCounter int
SET @intCounter = 1
WHILE @intCounter < 10001
BEGIN
INSERT INTO dbo.TableVarWithNulls ([ID]) VALUES (@intCounter)
SET @intCounter=@intCounter+1
END
GO
DECLARE @intCounter int
SET @intCounter = 1
WHILE @intCounter < 10001
BEGIN
INSERT INTO dbo.TableVar ([ID], COL1) VALUES (@intCounter, ' ')
SET @intCounter=@intCounter+1
END
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
--NOTE: tables are both showing up as 200K even though one has NULLS
DBCC SHOWCONTIG scanning 'TableVarWithNulls' table...
Table: 'TableVarWithNulls' (1428968217); index ID: 0, database ID: 25
TABLE level scan performed.
- Pages Scanned................................: 17
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 2.4
- Scan Density [Best Count:Actual Count].......: 42.86% [3:7]
- Extent Scan Fragmentation ...................: 42.86%
- Avg. Bytes Free per Page.....................: 448.9
- Avg. Page Density (full).....................: 94.45%
DBCC SHOWCONTIG scanning 'TableVar' table...
Table: 'TableVar' (1444968274); index ID: 0, database ID: 25
TABLE level scan performed.
- Pages Scanned................................: 24
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 3.4
- Scan Density [Best Count:Actual Count].......: 42.86% [3:7]
- Extent Scan Fragmentation ...................: 71.43%
- Avg. Bytes Free per Page.....................: 596.0
- Avg. Page Density (full).....................: 92.64%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--now update the tables to see heap behavior
UPDATE dbo.TableVarWithNulls SET COL1='X'
-----------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'TableVarWithNulls'. Scan count 1, logical reads 16197, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1165 ms, elapsed time = 1165 ms.
-----------------
UPDATE dbo.TableVar SET COL1='Y'
-----------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'TableVar'. Scan count 1, logical reads 10024, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 51 ms.
------------------------------------------
If I run the statement:
UPDATE dbo.TableVar SET COL1='Y'
...again, the statistics go down to 24 logical I/O's
If I run the statement:
UPDATE dbo.TableVarWithNulls SET COL1='X'
...again, the statistics will repeatedly show 18575 logical I/O's
Here are the new stats on the tables:
DBCC SHOWCONTIG scanning 'TableVarWithNulls' table...
Table: 'TableVarWithNulls' (1428968217); index ID: 0, database ID: 25
TABLE level scan performed.
- Pages Scanned................................: 41
- Extents Scanned..............................: 10
- Extent Switches..............................: 9
- Avg. Pages per Extent........................: 4.1
- Scan Density [Best Count:Actual Count].......: 60.00% [6:10]
- Extent Scan Fragmentation ...................: 40.00%
- Avg. Bytes Free per Page.....................: 240.0
- Avg. Page Density (full).....................: 97.03%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'TableVar' table...
Table: 'TableVar' (1444968274); index ID: 0, database ID: 25
TABLE level scan performed.
- Pages Scanned................................: 24
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 3.4
- Scan Density [Best Count:Actual Count].......: 42.86% [3:7]
- Extent Scan Fragmentation ...................: 71.43%
- Avg. Bytes Free per Page.....................: 596.0
- Avg. Page Density (full).....................: 92.64%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
----------
What exactly is going on with the I/O here. Since this is a heap and a UPDATE without a where clause - wouldn't it just grab the entire table in one physical operation? The "logical I/O" counts here are throwing me.
Thanks
Ryan
April 12, 2006 at 12:56 pm
As a note...if I had any indexes whatsoever on the table I would assume the issue was with forward-pointers. Without indexes, I assumed that forward pointers wouldn't be neccessary and the row would simply be moved to a new rowid without a "forwarding address". Maybe that's a bad assumption.
Are forward-pointers simply default behavior for heaps period?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply