Performance Suffers after adding column
-
Hi All,
I'm new to SQL Server 2000 , and DB administration in general so forgive me if I don't provide all the info you need up front. Recently a customer requested the ability to associate keywords with each of their products so that a shopper searching for say GLUE, could easily find it by typing in 'glue'. We search this new field called SearchTerms (which is a varchar 512) and the results are great. Customer's happy. Problem now is that our average response time has gone from 2 seconds to over 4 seconds! I've noticed some other areas of the app (that don't use the Product table at all) timing out periodically. All of this started only after I added this column to the Product table so I am assuming it is related.
I used EM to place the column at the end of the table since the coders prior to me set up their code to require columns to be in a certain place. Other than that I didn't do any kind of fancy defragging or anything after putting this in place so maybe I'm missing some obvious rule like "Always reboot SQL Server 200 after adding a column to an existing table". That's the kind of stuff I've managed to miss for most of my life which is one of the reasons my math is so abysmal! Well, anyway...
I'm using SQL Server 2000 Enterprise edition with service pack 3 on its own Windows 2000 Advanced Server (web servers are on other machines).
Any ideas??
Thank you kindly in advance,
Rich
-
If the table is a heap (no indexes) then the new column will be stored separately from the rest of the row, resulting in extra IO.
If it has clustered index, then this blows that idea out of the water
Also, is the new column indexed? How many rows? Is the app using tables scans over millions of rows?
Just some thoughts, nothing definitive...
-
If the table is a heap (no indexes) then the new column will be stored separately from the rest of the row, resulting in extra IO.
Can you expand on this?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I was recently interviewed for a DBA job.
I was questioned technically by an MS consultant because the recruiting organisation currently has no SQL Server DBAs.
One of the questions posed was:
You have a heap with a varchar(1000) column. The column contains only 20 chars. You issue an update that adds some data to this column. Where is the data stored?
I said separately, apart from the rest of the row. I was right
He followed this question up by how would I defrag this heap (given DBREINDEX and INDEXDEFRAG won't work)
Answer (which I needed prompting with is SHRINKFILE)
Applying the same thinking, why would the new column be stored with the rest of the row? If an UPDATE fragments your data, so will adding a new column because there is no room for the update or new data.
Now, I've not had chance to verify this or I could have it all wrong. Also, I can't find anything in BOL.
And I got the job :-). Start later in June.
-
That's interesting. My inclination in adding a new column (non-text) is that the table is rebuilt with page splits, etc. moving the data from one page to another, making room so that the data for each row is stored on the same page. If you script the "add" column, there is a new table created, old one dropped.
-
I agree with Steve. But I would be very interested in a repro script.
Btw, did the "MS consultant" give an explanation?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Rereading your answer. You didn't say anything that a new column was added. You instead updated an already existing. Why do you think this will be stored separately?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Not all scripting (via EM) produces the "new table created, old one dropped" routine. Sometimes a plain old "ALTER TABLE tablename ADD newcolumnname datatype constrants" is done.
Especially if it was hand coded for change control etc
It is an interesting though.
There are cleverer people than me in these forums so can anybody shed light?
-
Wish I could.. but the only thing I know about heap is how to spell it so I'll be waiting for an answer myself.
-
I would definitely want to see some proof on that. It seems to me that he was talking about forward-pointers, which are used for heap tables when a row is moved to a new location (so that non-clustered indexes RIDs do not need updating). But a forward-pointer always points to an entire row, not just one column of a row.
-
Because I was told this was correct when I answered the MS consultant's question. I had to think about it.
Given there is no reason (such as an index) for SQL to keep the data together, then it almost becomes obvious.
Now if anyone can prove me wrong, so be it, I refer to my previous post about shedding light. That would then mean that
the MS consultant is wrong.
I'm not going to name him (except in private emails to Frank and Steve if they wish) but he is an author of at least one SQL server book.
I have neither proved it nor found anything in BOL, but it does absolutelty demand that every table has an index...
-
The script below doesn't prove anything, I think.
Surely some of these clever people than me can amend it to use the undocumented DBCC commands to get an answer one way or another.
SET NOCOUNT ON
GO
IF OBJECT_ID('dbo.tblFragTest') IS NOT NULL
DROP TABLE dbo.tblFragTest
GO
CREATE TABLE dbo.tblFragTest (
IdColumn int NOT NULL IDENTITY (1,1),
DataColumn varchar (1000) NOT NULL)
GO
INSERT dbo.tblFragTest (DataColumn) VALUES ('01234567890123456789')
GO
DECLARE @idx int
SET @idx = 14
WHILE @idx > 0
BEGIN
INSERT dbo.tblFragTest (DataColumn) SELECT DataColumn FROM dbo.tblFragTest
SET @idx = @idx - 1
END
GO
SELECT COUNT(*) FROM dbo.tblFragTest
GO
DBCC SHOWCONTIG ('dbo.tblFragTest')
GO
UPDATE dbo.tblFragTest SET DataColumn = DataColumn +
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' +
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' +
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' +
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' +
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' +
'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
GO
DBCC SHOWCONTIG ('dbo.tblFragTest')
GO
ALTER TABLE dbo.tblFragTest WITH CHECK ADD CONSTRAINT PK_tblFragTest PRIMARY KEY CLUSTERED (IDColumn)
GO
ALTER TABLE dbo.tblFragTest DROP CONSTRAINT PK_tblFragTest
GO
DBCC SHOWCONTIG ('dbo.tblFragTest')
GO
-
You were very lucky that you've got the job.
You have several more or less severe mistakes here.
First, a heap is a table without a clustered index. It is not necessarily a table without an index at all. There can be nonclustered indexes present, and the table would still be a heap.
Second, for fragmentation this is really a must-read:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Now, a bit more severe is your understanding of rows and the physical storage
Let's have a play with it. Consider the following table:
USE tempdb
CREATE TABLE foolish_consultant
(
myid INT PRIMARY KEY IDENTITY
, c1 VARCHAR(1000)
)
INSERT INTO foolish_consultant
SELECT 'abc1234567890'
UNION ALL
SELECT 'cde'
Obviously we enter 2 rows into that table. Now we will take a look at the storage situation after this action.
DECLARE @page_adress AS BINARY(6)
SELECT
@page_adress = [first]
FROM
sysindexes
WHERE
[id] = OBJECT_ID('foolish_consultant')
SELECT
@page_adress page_adress
yields in my case
page_adress
--------------
0x1E0000000100
(1 row(s) affected)
Decoding this leads to
SELECT
CAST
(
SUBSTRING(@page_adress, 6, 1) +
SUBSTRING(@page_adress, 5, 1)
AS INT
 
AS file#, CAST
(
SUBSTRING(@page_adress, 4, 1) +
SUBSTRING(@page_adress, 3, 1) +
SUBSTRING(@page_adress, 2, 1) +
SUBSTRING(@page_adress, 1, 1) AS INT
 
AS page#file# page#
----------- -----------
1 30
(1 row(s) affected)
Now we can examine the content of that page
DBCC TRACEON(3604)
DBCC PAGE('tempdb',1,30,3)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:30)
------------
BUFFER:
-------
BUF @0x00E08980
---------------
bpage = 0x1980C000 bhash = 0x00000000 bpageno = (1:30)
bdbid = 2 breferences = 4 bstat = 0xb
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x1980C000
----------------
m_pageId = (1:30) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId = 1886629764 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 8 m_slotCnt = 2
m_freeCnt = 8046 m_freeData = 142 m_reservedCnt = 0
m_lsn = (7:376:190) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1980C060: 00080030 00000001 01000002 61001c00 0..............a
1980C070: 32316362 36353433 30393837 bc1234567890
myid = 1
c1 = abc1234567890
Slot 1 Offset 0x7c
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1980C07C: 00080030 00000002 01000002 63001200 0..............c
1980C08C: 6564 de
myid = 2
c1 = cde
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
As you can see, each row is stored in its own slot with all its columns
Now we update some content.
UPDATE foolish_consultant SET c1 = '0987654321cba' WHERE myid=1
DBCC PAGE('tempdb',1,30,3)
PAGE: (1:30)
------------
BUFFER:
-------
BUF @0x00E08980
---------------
bpage = 0x1980C000 bhash = 0x00000000 bpageno = (1:30)
bdbid = 2 breferences = 2 bstat = 0xb
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x1980C000
----------------
m_pageId = (1:30) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId = 1886629764 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 8 m_slotCnt = 2
m_freeCnt = 8046 m_freeData = 142 m_reservedCnt = 0
m_lsn = (7:376:298) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1980C060: 00080030 00000001 01000002 30001c00 0..............0
1980C070: 36373839 32333435 61626331 987654321cba
myid = 1
c1 = 0987654321cba
Slot 1 Offset 0x7c
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1980C07C: 00080030 00000002 01000002 63001200 0..............c
1980C08C: 6564 de
myid = 2
c1 = cde
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Apparently, each row is still in its own slow with all its columns and still on the same page. We now add a column
ALTER TABLE foolish_consultant
ADD c2 VARCHAR(1000)
GO
DBCC PAGE('tempdb',1,30,3)
PAGE: (1:30)
------------
BUFFER:
-------
BUF @0x00E08980
---------------
bpage = 0x1980C000 bhash = 0x00000000 bpageno = (1:30)
bdbid = 2 breferences = 1 bstat = 0xb
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x1980C000
----------------
m_pageId = (1:30) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId = 1886629764 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 8 m_slotCnt = 2
m_freeCnt = 8046 m_freeData = 142 m_reservedCnt = 0
m_lsn = (7:376:298) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1980C060: 00080030 00000001 01000002 30001c00 0..............0
1980C070: 36373839 32333435 61626331 987654321cba
myid = 1
c1 = 0987654321cba
c2 = [NULL]
Slot 1 Offset 0x7c
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1980C07C: 00080030 00000002 01000002 63001200 0..............c
1980C08C: 6564 de
myid = 2
c1 = cde
c2 = [NULL]
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The newly created column gets created in each already existing slot, so that all columns of a row are still in one and the same slot.
Lastly, we update the newly create column
UPDATE foolish_consultant SET c2 = 'So what?' WHERE myid=1
DBCC PAGE('tempdb',1,30,3)
PAGE: (1:30)
------------
BUFFER:
-------
BUF @0x00E08980
---------------
bpage = 0x1980C000 bhash = 0x00000000 bpageno = (1:30)
bdbid = 2 breferences = 1 bstat = 0xb
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x1980C000
----------------
m_pageId = (1:30) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId = 1886629764 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 8 m_slotCnt = 2
m_freeCnt = 8036 m_freeData = 180 m_reservedCnt = 0
m_lsn = (7:376:679) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x8e
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1980C08E: 00080030 00000001 02000003 26001e00 0..............&
1980C09E: 38393000 34353637 63313233 6f536162 .0987654321cbaSo
1980C0AE: 61687720 3f74 what?
myid = 1
c1 = 0987654321cba
c2 = So what?
Slot 1 Offset 0x7c
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1980C07C: 00080030 00000002 01000002 63001200 0..............c
1980C08C: 6564 de
myid = 2
c1 = cde
c2 = [NULL]
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DROP TABLE foolish_consultant
As you can see, nothing unexpected has changed. Each row in its own slot with all corresponding columns.
HTH
P.S. After editing, I would me interested in the name. Not to cause trouble here for anyone, but rather than to know whose books to stay away from
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hence the term MVP used to qualify Frank
. -
I'll pass on the comments if I speak to the consultant again and explain that you know better. I'll also point this thread out and ask him to clarify my understanding.
The question was posed about a table with no indexes.
From http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_5b1v.asp
"If a table is created with no indexes, the data rows are not stored in any particular order. This structure is called a heap."
...
"If a table has no clustered index, its data rows are stored in a heap"
...
It does go on to say a heap can have non-clustered indexes but the first statement backs up my original post about a heap.
Regardless.
Your script above has a primary key defined which defaults to clustered so it is NOT a heap. Adding columns, updating columns, so what, the clustered IDX will ensure that the data stays together.
So you have not proved anything yet, MVP or not.
Which is why the question was asked - what happens if there are no indexes on the table. I'd already answered other question where the answer was to use INDEXDEFRAG or DBREINDEX.
Maybe I was lucky I got the job, maybe not.
Maybe my 7 years solid experience on 3 versions of SQL, multiple server, multiple instance, multi site and as a Senior DBA got me the job. It just may be that I am competent with SQL Server...
But hey, lets not get personal...
I'll try the script tomorrow in work.
With a table with no indexes
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply