Pages

Tuesday, August 17, 2010

Pages

Page size: 8KB, Extent : 64KB. So when an insert to a table exceeds 8KB storage space, a page split occurs and SQl shifts half of data on prev page to new page causing page splits. Somtimes if extent is full it moves data onto a page on different extent.
There are two types of page faults: Soft and Hard. Soft are the best; thus making hard the worst (speaking in performance terms).

In paged memory systems there are three levels of memory pages: In memory, in cache, and on disk. Most people are familiar with the in memory / on disk concept. However, most paged memory implementations keep a few recently used memory blocks in a page pool that gets written to disk when they are untouched for a certain amount of time. Basically, it's a collection of pages that are on their way out. When requesting a bit of memory that is not in the main memory banks, it goes to the page pool to see if it is a recent main memory visitor. If so, a soft page fault occurs, and the page is placed back into memory from memory (a quick operation). Pages not even in the pool have to be fetched from disk, causing a hard fault. This is the slowest and most horrible of operations for a computer and should be avoided at all costs. Too many hard page faults are often referred to as "thrashing".

Max capacity specifications SQL2008

Clustered Index:1
Non clusetered index: 999
xml index: 249
Fk's: 253

columns per FK, PK, index: 16
Databases, Filegroups, Files per instance: 32,767
Max DB size: 524272 TB
Instances per computer: 50 on satnd alone, 25 on failover
Max number of objects in a DB(views, procs, index,keys, triggers): 2,147,483,647 (Triggers are limited only by max no of objects)

For more details: http://msdn.microsoft.com/en-us/library/ms143432.aspx

Wednesday, August 11, 2010

Transaction Logs

Transaction log full:

If you reach 100% of the log size and 100% of the disk size, you'll need to clear it and shrink it using DBCC SHRINKFILE

If you reach 100% of the log size but not 100% of the disk size, and Database is in simple mode, you either need to do a database checkpoint (CHECHPOINT) or increase log file size (though this is a temporary solution)

If you reach 100% of the log size but not 100% of the disk size, and Database is in Full/Bulk Logged recovery model, you (most likely) need to take a log backup (and do it regularly).