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.