Partitioning of tables in SQL Server

Partition means splitting into pieces. When table grows huge and it becomes difficult to manage it, even if indexes on it are getting tampered we can opt for partitioning such huge tables.

We can split huge tables into small tables based on ‘Range’ of values. By specifying upper and lower limit values we can split tables into small pieces. This helps in increasing performance and even index maintenance also becomes easier. We can partition indexes too, only Clustered index can’t be partitioned.

Partitioned tables can be kept in different file groups. So same table can be partitioned and kept in different file groups and they can be maintained in different locations. By partitioning we can deviate set of values to store in a particular part of table

Links …

How many?

How many instances per computer?

50 instances on stand-alone server.

25 instances on failover cluster.

How many Non-Clustered indexes per table?

999

How many Parameters per stored procedure?

2100

How many Parameters per user-defined function?

2100

How many Nested triggers/stored procedures levels?

32

How many columns per SELECT/INSERT statement?

4096

How many columns per PRIMARY/Foreign KEY?

16

Links …

What is a trigger?

It is also a type of Stored Procedure but it will get executed automatically based on a event. Execution of a trigger is termed as firing technically.

A trigger will fire based on DML operations like INSERT, UPDATE and DELETE. Triggers can be created on tables and views.

A trigger created for UPDATE operation on a table will get executed/fired automatically whenever any UPDATE command is used on the table.

How many triggers can be created on a table?

A table can have any number of triggers on it. Too many triggers on a table also lead to performance issues.

In SQL Server 2008 there is no particular restriction for number of triggers on a table but the total number of objects a table can have is 2,147,483,647. Objects include everything like tables, views, SP’s, triggers, rules, constraints and user defined functions.

What is Nested trigger?

A trigger can call another trigger and in turn that can call another trigger. These are called nested triggers.

Links …

Checkpoint

Checkpoint:

It is an event that occurs regularly in SQL Server depending either on setting of ‘recovery interval’ option or depending on occurrence of some particular events.

Checkpoint can be termed as a standard reference point for SQL Server. SQL Server commits all the transactions once checkpoint occurs also writes all dirty pages to disk.

When Checkpoint occurs?

·         If log file is 70% full. (Incase of SIMPLE recovery model).

·         If DB engine determines the time required to write dirty pages to disk is crossing the value set for ‘recovery interval’ option. (Incase of FULL/BULK-Logged recovery model).

·         While DB backup.

·         When following commands are executed:

·         Commit.

·         SHUTDOWN

·         All DDL Commands.

Command:

CHECKPOINT [duration (in seconds)]

This command can be used to trigger checkpoint manually. Duration can be specified if we wish to complete the event in some particular time if not specified by user SQL Server automatically determines time considering performance and the amount of dirty pages to be written.

Links …

Pagefile in Windows

What is Pagefile?

It is a virtual memory similar to RAM. Pagefile is used when all the applications/processes running on server have consumed the total amount of RAM allotted to them and need more space for other operations.

If RAM is full and needed more space then RAM transfers few of pages (4KB size) to hard disk, this releases space in RAM for new operations. These paged out pages are stored in pagefile.sys. It is also called as swap file.

As RAM is a limited resource which can’t grow beyond the configured value we have to maintain pagefile properly which grow unlimitedly as it is virtual memory.

Size of pagefile:

There is no limit or standard configuration for pagefile. It can grow upto unlimited size. The standard configuration is, it should be 1.5 times RAM size.

Can we delete?

Yes, pagefile can be deleted but it is always recommended not to delete it as to reduce performance issues. If we have huge RAM then we don’t need pagefile but microsoft strongly recommends to maintain pagefile.

Can we move pagefile to other location?

Yes, pagefile can be moved to other drives and it is not mandatory that it should be a single file. Pagefile can be partionined and split between different drives.

Links …