May 30, 2012 at 4:22 pm
ScottPletcher (5/30/2012)
Personally I don't like SQL's mixed extents, but I can't really prevent it. The relevant trace flag helps some, but it's not 100%.
TF 1118 removes almost all allocations from mixed extents, the only exception I can think of is IAM pages.
May 30, 2012 at 4:40 pm
It claims it does, but seems to work only under certain conditions.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 30, 2012 at 4:46 pm
ScottPletcher (5/30/2012)
It claims it does, but seems to work only under certain conditions.
Sounds interesting, do you have a repro?
edit: On reflection, I wonder if you saw this behaviour with TF 1118 specified as a start-up flag, or by using DBCC TRACEON?
May 31, 2012 at 8:04 am
DBCC TRACEON
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 31, 2012 at 9:05 am
ScottPletcher (5/31/2012)
DBCC TRACEON
Ah well that might explain some of what you saw then. Using it as a start-up flag might give you more complete coverage.
May 31, 2012 at 9:34 am
Interesting. I used a start-up proc to set the flag, so it was almost immediately on.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 31, 2012 at 9:44 am
ScottPletcher (5/31/2012)
Interesting. I used a start-up proc to set the flag, so it was almost immediately on.
Ok that cuts the scope a bit. I wonder how many allocations might still occur before that point. Also, I suppose it is possible that not every system thread re-checks the flag after start-up, or there might be a delay....or, well, who knows 🙂
May 31, 2012 at 5:30 pm
I have to rescind my statement about possibly increasing fragmentation after rebuilding a heap on SQL 2008 using ALTER TABLE...REBUILD. I revisited my project notes and noted increased fragmentation in some cases on SQL 2005 when adding then dropping a clustered index from a heap. Not only that, in cases when fragmentation was reduced the technique was not always able to do a very good job of removing extent fragmentation. I was seeing heaps with ~90% only drop to ~70%. I was eventually able to get the vendor who wrote the database to approve the conversion of unique non-clustered indexes to unique clustered indexes on the problematic heaps, which for the particular heaps (lots of insert/update/delete activity that produced forward pointers on a regular basis) was my preferred option. I was not able to dig up evidence showing a fragmentation increase when using ALTER TABLE...REBUILD, save for the 8-page-or-less-table-example Scott provided, which is hopefully not a practical concern for anyone.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply