September 1, 2010 at 7:38 am
Hi,
In SQL Server 2005 , i found the contention in 2:1:3 in wait resource. If i enabled T1118 does it only impact the allocation in TEMPDB or all the other databases will be impacted.
What will the negative impact if any in User databases if T1118 is enabled.
Should I enable it for sql 2008 environment as well?
If Paul or anyone who knows the answer or point me to the right direction, would certainly appreciate.
September 1, 2010 at 7:46 am
All databases.
Before enabling that, see if additional data files for TempDB helps. How many CPU cores and how many tempDB files do you have?
Are you seeing contention on 2:1:3 on 2008 as well?
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
September 1, 2010 at 7:59 am
Hi,
Check that you are running a build higher or equal to 9.00.3166 on your 2005 SQL Server if you intend to switch -T1118 on. cf http://support.microsoft.com/kb/936185
David B.
September 2, 2010 at 1:45 pm
The issue is only on SQL Server 2005. It is not on SQL 2008.
There are 4 tempdb files each 5 GB maxcapped to 10 Gb.
It is a 4 CPU server and the version is SP3 CU5
I couldn't see in any blogs or MSDN articles saying T1118 impacts all USER DB . it is always discussed on for TEMPDB.
Regarding SQL 2008, it was out of curiosity ...i know some changes has been done like temp db caching etc . but need to know some more info...
September 3, 2010 at 1:36 am
I would say like Gail, 1118 has an impact on all databases allocation:
On a SQL2005 SP3 instance started with -T1118:
[font="Courier New"]
create database uniformext
use uniformext
create table T1(a char(8000))
insert into T1 values (replicate('a',8000))
go 2
dbcc ind('uniformext','T1',-1)
PageFID PagePID IAMFID IAMPID ObjectID IndexID
1 40 NULL NULL 20730584210
1 184 1 40 20730584210
1 185 1 40 20730584210
-- 1:184 and 1:185 are data pages, 1:40 is the allocation page for T1. As they follow there are chances they are on the same extent.
-- Look at the IAM page contents:
dbcc traceon(3604)
dbcc page(18,1,40,3)
(...)
IAM: Extent Alloc Status Slot 1 @0x6303C0C2
(1:0) - (1:176) = NOT ALLOCATED
(1:184) - = ALLOCATED
(1:192) - (1:272) = NOT ALLOCATE
(...)[/font]
You don't see two single page allocations here, you see only one for 184. Proof that -T1118 also applies on user dbs.
I guess you've already read these but just in case:
- http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx
David B.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply