mixed_extent_page_count

Question

Post reply

mixed_extent_page_count

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
December 13, 2010 at 11:43 pm

#229579

Comments posted to this topic are about the item mixed_extent_page_count
Thanks.
December 14, 2010 at 2:32 am

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

vk-kirov SSCertifiable Points: 7686 More actions · Answer 1

The bad news is that, according to BOL, "this view is applicable only to the tempdb database".

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Very nice question, definately learned something.

And thanks to vk-kirov for pointing out an important remark. I hadn't even noticed it when I read the BOL page.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 3

vk-kirov (12/14/2010)
The bad news is that, according to BOL, "this view is applicable only to the tempdb database".

I would not expect much change in SGAM pages in a normal database. Table creation rates should be low compared to tempdb, so frequent changes in SGAM page allocation is unusual.

SanDroid SSChampion Points: 10068 More actions · Answer 4

Nils Gustav Stråbø (12/14/2010)
vk-kirov (12/14/2010)
The bad news is that, according to BOL, "this view is applicable only to the tempdb database".
I would not expect much change in SGAM pages in a normal database. Table creation rates should be low compared to tempdb, so frequent changes in SGAM page allocation is unusual.

I believe this is more important to monitor when you are using Mirroing or Publishing a VLDB.

Also it may be usefull you execute large querries (5K+ rows) that use TEMP tables or Table Parameters that have to create objects to store data in TEMPdB.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

Thnx to all for your valuable feedback !! .. 🙂

Thanks.

webrunner SSC-Dedicated Points: 31866 More actions · Answer 7

Thanks, good question. I knew I should have picked dm_db_file_space_usage!

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

UMG Developer SSChampion Points: 13482 More actions · Answer 8

Thanks for the question, I learned a couple things. 🙂

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Thanks for teh question. It made me look in BoL, which some say is cheating (and I say is learning).

Tom

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 10

I had to do the same (look i BOL), but I don't consider that cheating. "Cheating" in my opinion is when you copy/paste/run the T-SQL and post the answer without utilizing a single neuron in the central nervous system.

Paul White SSC Guru Points: 150467 More actions · Answer 11

SanDroid (12/14/2010)
Nils Gustav Stråbø (12/14/2010)
vk-kirov (12/14/2010)
The bad news is that, according to BOL, "this view is applicable only to the tempdb database".
I would not expect much change in SGAM pages in a normal database. Table creation rates should be low compared to tempdb, so frequent changes in SGAM page allocation is unusual.
I believe this is more important to monitor when you are using Mirroing or Publishing a VLDB.
Also it may be usefull you execute large querries (5K+ rows) that use TEMP tables or Table Parameters that have to create objects to store data in TEMPdB.

No, Nils is quite correct.

I have no idea why you think mirroring, or publishing a large database would be a prime factor in tempdb SGAM contention.

Where does your magic figure of 5K+ rows come from, or are you just guessing?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi