Miscellaneous Info

Some undocumented and important stuff  

DMV: dbcc showcontig – index_physical_stats will scan complete index pages even if you use where clause

Backup : uses 960k speed can reduce to 64k.

Virtual File Space … for 32 bit system. Default big pages when use PAE /awe /3G?

IAM –bit interval 4GB, GAM(Uniform Extents) -bit interval 4GB, SGAM(Mixed Extents) –bit interval 4GB, PFS(Page Free Space) –bytes interval 64MB.

Spinlock: mutual exclusion and never locked user data.


Windbg & sqlservr.pdb(Symbol File) KB311503

DBCC LOGINFO –too much VLFs can cause performance problem (more then 100/200)

DBCC  DBInfo(‘dbname’)

DBCC Page(‘’) – detailed page information.

DBCC IND() –index page numbers

DBCC  FileHeader(‘dbname’,filenumber#)

DBCC openTrans(‘dbnm’)

–DBCC Debugger

DBCC updateusage



DBCC SQLPERF(LRUSTATS) — Note this is undocumented command

measure Min Free Buffers on SQL server using: –


This entry was posted in DMV, Experts, General, Others, Tool and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.