aiDeeva.com

Tag: DBCC

  • Important Trace Flags for DBA

    A trace flag is used to set specific server characteristics or to switch off a particular behavior of the SQL Server.

    You can enable the trace flag from Startup Parameters, Session and Global Session level.

    DBCC TraceON(Flag Number) – at session level
DBCC TraceON(Flag Number, -1) -- Global

    Trace Flag 610 : Minimally logged DML operations into Indexed tables, transaction log and allow bulk loading
    Trace Flag 4199 : enable query optimizer fixes
    Trace Flag 1222 : writes the deadlock information in XML format into error log
    Trace flag 835 : Enable the Lock Pages in memory for standard edition
    Trace flag 1118 : Directs SQL Server to allocate full extents to each tempDB objects
    Trace flag 1204 : Write information about the deadlock in text format
    Trace flag 1211 : Disable the lock escalation based on the memory pressure
    Trace flag 3226 : Prevents successful backup operation being logged
    Trace flag 3014 : Returns more information to error log about backup operation
    Trace flag 3502 : Write information about the checkpoint in the error log
    Trace flag 3505 : Disables automatic checkpoint
    Trace flag 3004 : Returns more information about the instant file initialization
    Trace flag 1806 : Disables the instant file initialization

  • DBCC Config

    Dbcc Config is an undocumented DBCC command which returns SQL server configuration. Configuration details are stored in Master database file 1 and on page 10.You have to enable the trace flag 3604 to get the result.

    dbcc traceon(3604)
dbcc config
dbcc page(master,1,10, 3) 

    CONFIG:


DS_CONFIG @0x032D6060

cconfsz = 8                          cmajor = 8                           cminor = 0
crevision = 10                       cbootsource = 2                      crecinterval = 0
ccatalogupdates = 0                  cmbSrvMemMin = 8                     cmbSrvMemMax = 2147483647
cusrconnections = 0                  cnetworkpcksize = 4096               ckbIndexMem = 0
cfillfactor = 0                      ctapreten = 0                        cwritestatus = 0
cfgpriorityboost = 0x0               cfgexposeadvparm = 0x1               cfglogintime = 20
cfgpss = 0                           cfgpad = 4096                        cfgxdes = 16
cfgaffinitymask = 0                  cfgIOAffinityMask = 0                cfgbuf = 4362
cfgdes = 0                           cfglocks = 0                         cfgquerytime = 600
cfgcursorthrsh = -1                  cfgrmttimeout = 10                   cfg_dtc_rpcs = 0
cclkrate = 31250                     cfg_max_text_repl_size = 65536       cfgupddate = 40947
cfgupdtime = 22332689                fRemoteAccess = 1                    cfgbufminfree = 331
cnestedtriggers = 0x1                cdeflang = 0                         cfgTransformNoiseWords = 0x0
cfgPrecomputeRank = 0x0              crossdbownchain = 0                  cidCfg = 0x3400d008
cidCfgOld = 0x3400d008               cfgCutoffYear = 2049                 cfgLangNeutralFT = 1033
maxworkthreads = 0                   minworkthreads = 32                  minnetworkthreads = 32
threadtimeout = 15                   connectsperthread = 0                cusroptions = 0
exchcostthreshold = 5                maxdop = 0                           cpwdpolicyupgrade = 0x1
cfServerTriggerRecursion = 1         cfDisableResultsetsFromTriggers = 0  cfgPHConnectTimeout = 60
CLREnabled = 0                       cfgmaxcrawlrange = 4                 ftSmallBufMin = 0
ftSmallBufMax = 100                  ftLargeBufMin = 0                    ftLargeBufMax = 100
RemoteDacEnabled = 0                 CommCritComplianceEnabled = 0        EkmEnabled = 0
cUserInstanceTimeout = 0x3c          cfgEnableUserInstances = 0x1         m_BackupCompressionDefault = 0x0
FilestreamAccessLevel = 0            OptimizeForAdhocWorkloads = 0        cchecksum = 1787
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



PAGE: (1:10)


BUFFER:


BUF @0x043659F8

bpage = 0x069A4000                   bhash = 0x00000000                   bpageno = (1:10)
bdbid = 1                            breferences = 0                      bUse1 = 45452
bstat = 0x2c00009                    blog = 0x43212159                    bnext = 0x00000000

PAGE HEADER:


Page @0x069A4000

m_pageId = (1:10)                    m_headerVersion = 1                  m_type = 14
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99     m_indexId (AllocUnitId.idInd) = 0    Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0            Metadata: IndexId = 0                Metadata: ObjectId = 99
m_prevPage = (0:0)                   m_nextPage = (0:0)                   pminlen = 0
m_slotCnt = 1                        m_freeCnt = 0                        m_freeData = 8190
m_reservedCnt = 0                    m_lsn = (0:0:1)                      m_xactReserved = 0
m_xdesId = (0:0)                     m_ghostRecCnt = 0                    m_tornBits = -1715479820

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           
PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =                  Record Size = 9

Memory Dump @0x6300A060

00000000:   00000800 fb060000 08†††††††††††††††††....û....                

DS_CONFIG @0x6300A060

cconfsz = 8                          cmajor = 8                           cminor = 0
crevision = 10                       cbootsource = 2                      crecinterval = 0
ccatalogupdates = 0                  cmbSrvMemMin = 0                     cmbSrvMemMax = 2147483647
cusrconnections = 0                  cnetworkpcksize = 4096               ckbIndexMem = 0
cfillfactor = 0                      ctapreten = 0                        cwritestatus = 0
cfgpriorityboost = 0x0               cfgexposeadvparm = 0x1               cfglogintime = 20
cfgpss = 0                           cfgpad = 4096                        cfgxdes = 16
cfgaffinitymask = 0                  cfgIOAffinityMask = 0                cfgbuf = 4362
cfgdes = 0                           cfglocks = 0                         cfgquerytime = 600
cfgcursorthrsh = -1                  cfgrmttimeout = 10                   cfg_dtc_rpcs = 0
cclkrate = 31250                     cfg_max_text_repl_size = 65536       cfgupddate = 40947
cfgupdtime = 22332689                fRemoteAccess = 1                    cfgbufminfree = 331
cnestedtriggers = 0x1                cdeflang = 0                         cfgTransformNoiseWords = 0x0
cfgPrecomputeRank = 0x0              crossdbownchain = 0                  cidCfg = 0x3400d008
cidCfgOld = 0x3400d008               cfgCutoffYear = 2049                 cfgLangNeutralFT = 1033
maxworkthreads = 0                   minworkthreads = 32                  minnetworkthreads = 32
threadtimeout = 15                   connectsperthread = 0                cusroptions = 0
exchcostthreshold = 5                maxdop = 0                           cpwdpolicyupgrade = 0x1
cfServerTriggerRecursion = 1         cfDisableResultsetsFromTriggers = 0  cfgPHConnectTimeout = 60
CLREnabled = 0                       cfgmaxcrawlrange = 4                 ftSmallBufMin = 0
ftSmallBufMax = 100                  ftLargeBufMin = 0                    ftLargeBufMax = 100
RemoteDacEnabled = 0                 CommCritComplianceEnabled = 0        EkmEnabled = 0
cUserInstanceTimeout = 0x3c          cfgEnableUserInstances = 0x1         m_BackupCompressionDefault = 0x0
FilestreamAccessLevel = 0            OptimizeForAdhocWorkloads = 0        cchecksum = 1787


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  • How to check waits in SQL Server 2000?

    Today I got a comment, how to check the wait statistics in SQL Server 2000. You can query sysprocesses table and use the DBCC SQLPERF to get the wait statistics in SQL Server 2000.

    select top 5* from sysprocesses
    dbcc sqlperf(‘waitstats’)

    Wait Statistics Image