SQL Server History

How SQL Server writes data to disk ?

The transaction log is a serial record of all modifications that have occurred in the database. SQL Server writes changes to the log before it writes changes to the actual data file.

SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification must be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.

 At the time a modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.

When users change data, SQL Server doesn't write that change directly to the data. Rather, SQL Server locates the appropriate data and then loads it into a special area of RAM called the data cache. Changes are made in RAM. Then, SQL Server copies changes waiting in RAM to the transaction log. Only then does SQL Server write changes to the actual data file.

This is called a write-ahead log because SQL Server writes changes to the log before it writes changes to the actual data file. This approach is quite a bit faster than writing directly to the data file.

Checkpoint in SQL Server

What is checkpoint?

Checkpoint is a process to write drity pages to disk. Dirty pages are pages which are in buffer pool and modified but not yet written to disk.

Syntax :

Checkpoint [ checkpoint duration ]

Check point duration is advanced option. Checkpoint duration is time is seconds and it's value should be interger and greater than zero. If you mention any value in checkpoint duration then SQL will ensure to maintain the amount of dirty pages in disk.
If SQL services restart then all the databases should recover with in the time which is mentioned in the checkpoint duration. If you write nothing in checkpoint duration then SQL Server will issue automatic checkpoint. What does automatic means ? If no value is mentioned in checkpoint duration then SQL Server will issue the checkpoint when data in dirty pages is greater than what SQL can recover in the recovery interval. SQL will ensure that databases should be online after restart in specific amount of time which is  mentioned in recovery interval in sp_configure.

What check point do ?

Batch up write processes to improve performance.
Reduces time required for crash recovery.

How checkpoint improves performance?

If data is written to disk each time pages get's modified hence write I\Os will increase on the server which in result will bring down performance on whole. Checkpoint batches up write processes and writes them to disk at certain duration.

How checkpoint reduces crash recovery ?

SQL keep monitoring the pages in buffer pool and checkpoint is issues as soon as number of dirty pages increases beyond what can be covered in specified recovery interval.

SPID & KPID

SPID is the SQL Server Process ID number and is assigned by SQL Server to each new connection. It starts with one and is globally unique. SPID 1 through 50 are reserved for system uses and are not used for any user connections.

KPID is the kernel-process ID. Under SQL Server for Windows this is the thread ID number, also known as "ID Thread," and is assigned by Windows when the thread is created. The Thread ID number is a system-wide identifier that uniquely identifies the thread. KPID is visible by querying the KPID column of master..sysprocesses. It is only filled in for spid numbers four and higher. You can also get KPID/ID Thread from Windows Perfmon using the "Thread" object and the "ID Thread" counter.

Query to find how many threads and open transactions SPID is running we can run this query.

SELECT spid, kpid, status, cpu, memusage, open_tran, dbid FROM sysprocesses WHERE spid= 'enter spid value'

Tools for troubleshooting

In today's post I will walk you through some of the tools you can use to troubleshoot.

SQLDiag.exe is a valuable troubleshooting  tool for any SQL Server instance. It gathers all teh SQL Server error logs and configuration settings. You can use SQLDiag.exe to gather information such as event logs, a profiler trace and performance monitor log for SQL Server.

The Cluster log is not specific to SQL Server , it contains information about when any resource fails the LooksAlive or IsAlive check and tracks when resources are brought online and offline. All the times in cluster.log is GMT in Windows2000 and Windows2003. Cluster.log is present in the cluster directory under the systems folder.

The Event logs can be good source of information. The event logs are collected by SQLdiag in SQL Server 2005 and PSSDiag in SQL Server 2000.

If you are getting some sort of "file not found" error, it could indicate that a file or registry key no longer exists or the process does not have sufficient permissions for the file or key. You can doenload toos such as Filemon or RegMon from http://www.sysinternals to help troubleshoot these types of issues.

Sometimes you need to check the settings of various registry keys such as location of the master
data and log files. Regedit.exe is a useful tool in these situations. However keep in mind that in a cluster , extrasteps are necessary to change the checkpointed keys.

Sometimes the SQL Server error logs give you a clue as to why you are seeing problems.Always check SQL error logs.

SQL Server Profiler is a great tool for seeing exactly what queries are being sent to SQL Server and how long they take to execute.

System Monitor ( also called performance monitor , Perfmon, or sysMon ) is often used to monitor general performance as well as SQL Server specific components.

Cluster Adminstrator is used for operations such as manually failing over to another node , reviewing and changing configuration settings , taking resources or resource group offline or bringing them online and changing cluster resource dependencies. There is also the command line cluster.exe to perform these operations.

Which TCP/IP port does the SQL Server run on? How can it be Changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number, both on client and the server.

Cluster - Basic Terms

 In this post I want to describe basic terms used in windows cluster. Let's have a look :

Cluster Resource is anything that you want clustered or virtualized.

 SQL Server Cluster includes :

•  The SQL Server virtual name
•  One or more SQL Server IP addresses
•  The SQL Server Service
•  The SQL Server Agent Service
•  The Full Text resource.

 Cluster group is a group of related cluster resources. You can only fail over the entire group not the  individual resources.

In the context of cluster a shared disk is a disk that can be owned over time by any configured node in the cluster but it can be owned by one node at a time.

Each resource has a LooksAlive lightweight check to see whether it appears to be working. For the SQL Server Service resource , this is a query to the operating system to see whether it looks like the service is up.
Each resource also has IsAlive check that is bit more through.  For thacte SQL Server Service resource , the IsAlive check is a simple query against the master database.This actually verifies that the cluster service can connect to SQL Server and retrieve a small result set within the configured time limit.
There are time when looksalive check succeeds but IsAlive check fails. When the IsAlive check fails the configured number of times, it causes the group to fail.

Quorum is the agreed-upon place for storing information common to the windows cluster. Microsoft Windows clustering stores this information on shared disk called Quorum disk. The Quorum disk is disk resource in the same resource group that also has the windows cluster virtual name and the Windows cluster IP address. This group can reside on any node in the cluster and can be failed between nodes without disrupting the activity of the other clustered groups. The is often called the windows cluster group or the quorum group.
Along with the data stored on the quorum, registry keys exist that need to be kept in the sync. The quorum stored information about what ' checkpointed' registry keys have changed. A checkpointed key can be identified by it's existence in the cluster registry key hive. SQL Server set up adds several of it's keys to the checkpoint list , including the keys that store the location of the master database data and log and the location of the SQL server error log. The propagation of changes to these keys to other nodes in the cluster is called registry cloning.