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

Record structure

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….