Script to find out the statistics update date for all the indexes in the current database
sp_MSforeachtable 'sp_autostats "?"'
Script to update the statistics of all the indexes
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'
Problem
When connecting to SQL Server using Management Studio (SSMS), with the limited permissions, you do not see any user databases or receive Error 916 when expanding the database list from Object Explorer. The error message is “The server principal “Login Name” is not able to access the database “database name” under the current security context. (Microsoft SQL Server, Error: 916).” In this tip I will explain the root cause of the issue and how to fix it.
Solution
http://www.mssqltips.com/sqlservertip/2761/sql-server-management-studio-error-916/
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.
You can use below simple technique to check whether lock pages in memory is enabled or not. If lock pages in memory is enabled you can see the “Using locked pages for buffer pool” message in the SQL Server error log.
exec xp_readerrorlog 0, 1, 'locked pages'
To check if it is disabled. You have to check for the “Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.” message.
exec xp_readerrorlog 0, 1, 'lock pages in memory'
You have often seen the above default service accounts while configuring the SQL Services. Let’s see what the difference between these accounts is.
Local System: Completely trusted account, more than the Administrator account. There is nothing on a single box that this account cannot do and it has the right to access the network as the machine. However local system account might restrict the SQL Server interaction with the other server. The actual name of the account is “NT AUTHORITY\SYSTEM”
Take example of “Lock Pages in Memory“, it will default granted to this account. No need to explicitly specify it.
Network Service: has more access to resources and objects than members of the Users group. Services that run as the Network Service account access network resources by using the credentials of the computer account. The actual name of the account is “NT AUTHORITY\NETWORK SERVICE“. This account is far more limited than the Limited than Local System account.
Local Service: A limited service account that is very similar to Network Service and meant to run standard least-privileged services. However unlike Network Service it has no ability to access the network as the machine.
This limited access helps safeguard the system if individual services or processes are compromised.
Services that run as the Local Service account access network resources as a null session without credentials.
The actual name of the account is “NT AUTHORITY\LOCAL SERVICE“. Local Service account is not supported for the SQL Server or SQL Server Agent services.
aiDeeva.com 