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.
CPU-Z
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 (LOGSPACE)
PERFMON (SYSMON)
DBCC SQLPERF(LRUSTATS) — Note this is undocumented command
measure Min Free Buffers on SQL server using: -
