August 12, 2009 at 10:53 pm
mtassin (8/12/2009)
Lynn Pettis (8/11/2009)
Not all posts are related to the article itself. That is one of the benefits of this community and where you actually learn new things or ideas.And some of us discover new things, and find out that folks here have already written about them.
I will say that I for one wouldn't write 20 tips for better SQL without doing some research to make sure that all 20 were valid for all versions of SQL (or I'd comment them to state which versions each of them applied to).
Agreed that the author did not validate what he presented. It was a mistake. But the comments like the above does not suit for a guy who is 'ten centuries' old. Looks like he is saying -- I am superior. I never make mistakes.
I hate this when some one points a finger of accusation rather than saying OK kid, it happens. Better luck next time.
August 12, 2009 at 11:23 pm
GilaMonster (8/10/2009)
Jeff Moden (8/10/2009)
[font="Arial Black"]Absolutely not true. Please, folks... stop perpetuating this myth.[/font] That's a left over wives tale for the original version of 6.5. Don't take my word for it, though... that how myth's get started and perpetuated 😉 Instead, please read the following article especially the part about how the fix has been built in since version 7...To add to that....
In SQL 2000, any form of temp table creation, if frequent enough (several a second usually), could cause blocking on the allocation pages (most often seen as a PAGELATCH wait on resource 2:1:3). This happened for both CREATE TABLE and SELECT INTO and the workaround was to create more files for TempDB and optionally enable a traceflag. With several enhancements to tempDB and temp table creation in 2005 and above, it's a lot more difficult to run into this contention problem.
I meant to ask, Gail... which trace flag do you speak of?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2009 at 1:48 am
Jeff Moden (8/12/2009)
I meant to ask, Gail... which trace flag do you speak of?
Now you made me have to go and look it up.....
Traceflag 1118
It changes the behaviour of SQL with regards to allocation of pages in small tables. Normally SQL allocates the first few pages for a table (8 in 2000, 24 in 2005) from mixed extents before switching to uniform (dedicated) extents. In TempDB with high numbers of temp table creation, this could lead to contention on the first SGAM (the allocation page that tracked what mixed extents had free pages) as lots of processes were trying to allocate and deallocate pages
By switching 1118 on SQL will use uniform (dedicated) extents always, regardless of the size of the table. This means that small tables take a minimum of 64 kb instead of 8 (16 if you count the table's IAM), but it also means less allocations and less impact on the allocation pages.
It's not usually needed in 2005 and higher due to the enhancements around temp table creation and reuse.
Paul's got a good summary - http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2009 at 1:51 am
Jeff,
Probably 1118: see http://blogs.msdn.com/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx and http://support.microsoft.com/kb/328551. Not everyone knows that it can be used with SQL2K as well.
Paul
edit: Gail's reply wasn't there when I started writing this post! Darn it!!! And I agree - Paul R's blog entry is excellent.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 13, 2009 at 3:12 am
GilaMonster (8/13/2009)
Normally SQL allocates the first few pages for a table (8 in 2000, 24 in 2005) from mixed extents before switching to uniform (dedicated) extents.
No that is not correct: SQL Server (at least 2000, 2005, and 2008) always allocate the first 8 data pages from mixed extents, not 24.
An object must contain 25 (not 24) data pages before creating (or re-creating) a clustered index will coalesce the mixed extents into uniform extents. (Illustrative code follows.)
The same rule of 25 pages applies to index pages above the leaf level - so a table with 25+ data pages will usually be accompanied by at least one higher-level index page allocated from one or more mixed extents.
Paul
SET NOCOUNT ON;
CREATE TABLE dbo.AllocationTest (row_id INTEGER IDENTITY, col1 CHAR(8000) NOT NULL);
GO
-- Allocate 8 data pages
INSERT dbo.AllocationTest (col1) VALUES ('')
GO 8
-- ext_size is 1 for each of the first 8 pages,
-- these are allocations from mixed extents
DBCC EXTENTINFO ('Test', 'dbo.AllocationTest', -1);
GO
-- Allocate the 9th page
INSERT dbo.AllocationTest (col1) VALUES ('')
GO
-- The new page is allocated from an extent with ext_size of 8
-- This is a uniform extent
DBCC EXTENTINFO ('Test', 'dbo.AllocationTest', -1);
GO
-- Allocate 15 more pages for a total of 24 pages
INSERT dbo.AllocationTest (col1) VALUES ('')
GO 15
-- There are now 8 pages from mixed extents and
-- 16 pages from two uniform extents
DBCC EXTENTINFO ('Test', 'dbo.AllocationTest', -1);
-- Try to remove the mixed extents by creating a clustered index
CREATE UNIQUE CLUSTERED INDEX c ON dbo.AllocationTest (row_id);
-- Mixed extents remain
DBCC EXTENTINFO ('Test', 'dbo.AllocationTest', -1);
GO
-- Drop and recreate the table for the next test
DROP TABLE dbo.AllocationTest;
CREATE TABLE dbo.AllocationTest (row_id INTEGER IDENTITY, col1 CHAR(8000) NOT NULL);
GO
-- Allocate 25 pages (one more than last time)
INSERT dbo.AllocationTest (col1) VALUES ('')
GO 25
-- Try to remove the mixed extents by creating a clustered index
CREATE UNIQUE CLUSTERED INDEX c ON dbo.AllocationTest (row_id) WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF);
-- Data is now in uniform extents
DBCC EXTENTINFO ('Test', 'dbo.AllocationTest', -1);
-- Finished
DROP TABLE dbo.AllocationTest;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 13, 2009 at 3:47 am
Paul White (8/13/2009)
GilaMonster (8/13/2009)
Normally SQL allocates the first few pages for a table (8 in 2000, 24 in 2005) from mixed extents before switching to uniform (dedicated) extents.No that is not correct: SQL Server (at least 2000, 2005, and 2008) always allocate the first 8 data pages from mixed extents, not 24.
I remember reading somewhere, during the 2005 beta period, that the switch to uniform was changed to after 3 extents, not 1. Can't recall where but it was at a time I was working with TempDB contention and traceflag 1118 so it was of concern to me them. Maybe it changed back during the beta period.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2009 at 4:00 am
GilaMonster (8/13/2009)
Paul White (8/13/2009)
GilaMonster (8/13/2009)
Normally SQL allocates the first few pages for a table (8 in 2000, 24 in 2005) from mixed extents before switching to uniform (dedicated) extents.No that is not correct: SQL Server (at least 2000, 2005, and 2008) always allocate the first 8 data pages from mixed extents, not 24.
I remember reading somewhere, during the 2005 beta period, that the switch to uniform was changed to after 3 extents, not 1. Can't recall where but it was at a time I was working with TempDB contention and traceflag 1118 so it was of concern to me them. Maybe it changed back during the beta period.
Kalen made a similar error: http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/23/328.aspx. Perhaps you were thinking along the same lines.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 13, 2009 at 4:15 am
Paul White (8/13/2009)
Kalen made a similar error: http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/23/328.aspx. Perhaps you were thinking along the same lines.
Reading carefully, she hasn't made an error, but it's easy to draw an incorrect conclusion from there. She shows that rebuilds use only uniform after 24 whereas in 2000 they use only uniform after 8, but allocations switch to uniform after 8.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2009 at 4:22 am
August 13, 2009 at 4:27 am
Runs the batch 8 times
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2009 at 4:30 am
ta.bu.shi.da.yu (8/13/2009)
Hmmmm... Paul, what does the following do?GO 8
It executes the preceding batch 8 times, as if you had highlighted it in SSMS and hit Execute eight times.
I'm not sure that it is documented behaviour, but it is reasonably well-known and used in demo scripts.
edit: It is documented in Books Online: http://msdn.microsoft.com/en-us/library/ms188037(SQL.90).aspx
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 13, 2009 at 4:33 am
GilaMonster (8/13/2009)
Paul White (8/13/2009)
Kalen made a similar error: http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/23/328.aspx. Perhaps you were thinking along the same lines.Reading carefully, she hasn't made an error, but it's easy to draw an incorrect conclusion from there. She shows that rebuilds use only uniform after 24 whereas in 2000 they use only uniform after 8, but allocations switch to uniform after 8.
I was referring to the section:
In my class last week one of the students asked how we could get rid of the single page allocations. In SQL Server 2000, you could do this by just creating a clustered index on the table. I assumed this was the case, because if SQL Server knows right when it starts building an index that there will be more than 8 pages, there should be no need for single page allocations.
But when I tried to illustrate that behavior on SQL Server 2005, I was taken by surprise. The single page allocations remained, even though they now all had new pages numbers, as building a clustered index completely moves all the data.
As far as the only-using-uniform extents after 25 pages is concerned, that is exactly the point I illustrated in my script (I also wrote "An object must contain 25 (not 24) data pages before creating (or re-creating) a clustered index will coalesce the mixed extents into uniform extents"). I had assumed that you were confusing that behaviour with the initial allocation algorithm. No matter, it's cleared up now.
Paul
edit: added quote
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 13, 2009 at 4:44 am
Re this "traceflag" (I'm not familiar with using that term for "alter the data processing behaviour of the server", but clearly that's what you say): these posts and linked articles are talking about avoiding contention in tempdb, but - is this still correct? - not only tempdb but every database has handling altered by the traceflag.
http://support.microsoft.com/kb/813492/ describes a hotfix for SQL Server 7.0 so that "when you
turn on trace flag 1118, all allocations for tables in the tempdb database will allocate full extents to the table. However, allocations for small tables (less than 8 pages) in other databases will continue to allocate single pages from "mixed" extents exactly as they would if the trace flag were not turned on." But "If SQL Server trace flag 1118 is enabled on SQL Server 2000, mixed extent allocations are disabled in all databases."
I don't get why the traceflag creates a problem in user databases that needs a hotfix in 7.0 but apparently doesn't demand a hotfix in 2000 and later. But I do want to be clear on the "it affects tempdb" / "it affects all databases" distinction, just in case I'm the guy who does find a problem in our own database caused by the traceflag.
August 13, 2009 at 5:04 am
rja.carnegie (8/13/2009)
I don't get why the traceflag creates a problem in user databases that needs a hotfix in 7.0 but apparently doesn't demand a hotfix in 2000 and later. But I do want to be clear on the "it affects tempdb" / "it affects all databases" distinction, just in case I'm the guy who does find a problem in our own database caused by the traceflag.
It affects all databases. You can demonstrate this for yourself by enabling the trace flag on a test box and using the script I posted to write one page of data and check the extent allocated using DBCC EXTENTINFO.
There is a lot of confusion and debate over this trace flag. Some performance test results can be found on Linchi Shea's excellent blog: http://sqlblog.com/blogs/linchi_shea/archive/2007/08/07/reduce-the-contention-on-tempdb-with-trace-flag-1118-to-enable-or-not-to-enable.aspx
In the most recent build of 2005 and 2008, I would tend to allocate a number of equal-sized files to tempdb (maximum four), and leave the trace flag off. There is another solution: I work at a place with fairly extreme tempdb usage - we have found that using solid state storage for tempdb removes the need for the trace flag and the extra files.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 13, 2009 at 5:10 am
Here's a script you can use to experiment:
USE tempdb;
GO
SET NOCOUNT ON;
GO
-- Add one row (will fit on one page)
CREATE TABLE dbo.AllocationTest (row_id INTEGER IDENTITY, col1 CHAR(8000) NOT NULL);
DBCC TRACEON (1118, -1);
INSERT dbo.AllocationTest (col1) VALUES ('')
DBCC EXTENTINFO ('tempdb', 'dbo.AllocationTest', -1); -- ext_size = 8 (64KB uniform extent)
DROP TABLE dbo.AllocationTest;
GO
CREATE TABLE dbo.AllocationTest (row_id INTEGER IDENTITY, col1 CHAR(8000) NOT NULL);
DBCC TRACEOFF (1118, -1);
INSERT dbo.AllocationTest (col1) VALUES ('')
DBCC EXTENTINFO ('tempdb', 'dbo.AllocationTest', -1); -- ext_size = 1 (8KB page from a mixed extent)
DROP TABLE dbo.AllocationTest;
GO
Just change the database name in the USE statement at the top and in the DBCC EXTENTINFO statements to try it with different databases.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 166 through 180 (of 244 total)
You must be logged in to reply to this topic. Login to reply