This is a first blog for this month, yes I was busy with Powershell quiz and some other personal stuff.
Okay, continue with Miscellaneous Info
here is some more important stuff/ information about sql server.
>>Ghost cleanup task runs every 5minuts (in 2005) and runs every 10minuts in (2008 onwards)
>> trace-T661 to turn off ghest cleanup tasks
>>Checkpoints runs every minutes by default(can change using sp_configure)
>> latches clears every 5minutes , so maximum wait time for a latch would be 5miuntes
>> For sql long wait (PAGEIOLATCH) to IO is taking more than 15seconds – no response from IO (returns message on error log) checks every 5minutes and maintain the counter in a message.
>> disk read timeout errors after 15seconds – sends error in errorlog
>> Disk at a batch of 512bits
>> the normal page size for Windows memory is 4Kb on x64 systems. But with large pages, the size is 2Mb
>> Sql server works with batch of 8k (pages)
>> versioning: 14bytes tag added to records… can make fragmentation if we enable on page which is already full.
>> for row-overflow 16bytes pointers will add for off-row column
>> “Copy “option is no available for -differential backup
>> non-index index either of 16 columns or 900bytes
>> MAxTrasferSize” defult is 960kb , due to memory pressure/VAS fragmentation and not having continues free space of 960kb than it will reduce the size of MAxTrasferSize” to 64kb
>> Every connection requires memory
>> Every lock requires 96k
>> Every lock requires memory
>> on PAGE offset array size of page is 2 Bytes
2 bytes of a status bytes
2 bytes of offset
2 bytes of column count
1 bytes null bitmap mask
So record size area (payload would 9060 -7 = 9053
RID/Bookmark for uniqueness on heap for non-cluster reference – 8bytes
Non-unique cluster index adds extra uniquefier of 4bytes
Version stored in tempdb is not Logged.
———Best way to count the rows
SELECT SUM (row_count) FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(‘sales.SalesOrderDetail’) AND (index_id=0 or index_id=1);
*Log files do not contain pages; they contain a series of log records.
*This information may change depending upon the version of sql used.
Hope this helps….