Thursday, 31 March 2016

Troubleshooting High-CPU Utilization for SQL Server

The first and the most common step if you suspect high CPU utilization (or are alerted for it) is to login to the physical server and check the Windows Task Manager. The Performance tab will show the high utilization as shown below: 

Next, we need to determine which process is responsible for the high CPU consumption. The Processes tab of the Task Manager will show this information:

For this types of situation we have to remember that CPU consume time in two modes as

1) Kernal Mode
2) User Mode

These two mode can be seen by "Performance Monitor" by monitoring "%Privilege Time" and "%User Time" counter.

Remember that "%Privileged time" is not based on 100%.It is based on number of processors.If you see 200 for sqlserver.exe and the system has 8 CPU then CPU consumed by sqlserver.exe is 200 out of 800 (only 25%).

If "% Privileged Time" value is more than 30% then it's generally caused by faulty drivers or anti-virus software. In such situations make sure the BIOS and filter drives are up to date and then try disabling the anti-virus software temporarily to see the change.

If "% User Time" is high then there is something consuming of SQL Server.
There are several known patterns which can be caused high CPU for processes running in SQL Server including

 Some of the most common causes for High CPU in SQL Server are

1 . Query executing causing CPU spike ( In general caused by optimizer picking bad plan)

2. High Compiles and Re-compiles ( In general stats change , schema change , temp tables , recompiled all the user defined SP's etc)

3. Running many traces.

Note: If your server is under severe stress and you are unable to login to SSMS, you can use another machine’s SSMS to login to the server through DAC – Dedicated Administrator Connection.

SELECT  scheduler_id
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

See below for the BOL definitions for the above columns:

scheduler_id – ID of the scheduler. All schedulers that are used to run regular queries have ID numbers less than 1048576. Those schedulers that have IDs greater than or equal to 1048576 are used internally by SQL Server, such as the dedicated administrator connection scheduler.
cpu_id – ID of the CPU with which this scheduler is associated.
status – Indicates the status of the scheduler.
runnable_tasks_count – Number of workers, with tasks assigned to them that are waiting to be scheduled on the runnable queue.
active_workers_count – Number of workers that are active. An active worker is never preemptive, must have an associated task, and is either running, runnable, or suspended.
current_tasks_count - Number of current tasks that are associated with this scheduler.
load_factor – Internal value that indicates the perceived load on this scheduler.
yield_count – Internal value that is used to indicate progress on this scheduler.

The next step is to identify which queries are demanding a lot of CPU time. The below query is useful for this purpose (note, in its current form, it only shows the top 10 records).

SELECT TOP 10 st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC

This query has total_worker_time as the measure of CPU load and is in descending order of the total_worker_time to show the most expensive queries and their plans at the top:
Note the BOL definitions for the important columns:
total_worker_time - Total amount of CPU time, in microseconds, that was consumed by executions of this plan since it was compiled.
last_worker_time - CPU time, in microseconds, that was consumed the last time the plan was executed.

I re-ran the same query again after a few seconds and was returned the below output.

Now figure out whether it is singe query or stored procedure causing CPU spike.

1. If the stats are up to date then estimated rows and estimated execution will be approximately same in the execution plan. If there is huge difference then stats are outdated and required update.

2. Rebuild or re-organize the indexes and also create if the indexes are not available.

3. If update statistics or rebuilding the indexes doesn't help you bringing down the CPU then tune the query one by one.

3. If the procedure is causing the CPU spike then

a. Use SET NOCOUNT ON to disable no of effected rows message. It is required only to test or debug the code.

b. Use schema name with the object name if multiple schemas exist in the database. This will helpful in directly finding the compiled plan instead of searching for the object in other schema. This process of searching schema for an object leads to COMPILE lock on SP and decreases the SP's performance. So always its better to refer the objects with the qualified name in the SP.

c. Do not use the prefix "sp_" in the stored procedure name . If you use then it will search in the master database. Searching in the master database causes extra over head and also there are changes to get wrong resulyt if the same SP found in the master database.

d. Use IF EXISTS (SELECT 1) instead of (SELECT * ) to check the existence of a record in another table. Hence EXIST will use True or False.

e. If the query which is spiking linked server query try changing the security of linked server to ensure liked server user has ddl_admin or dba/sysadmin on the remote server.

f. Try to avoid using the SQL Server cursors when ever possible and use while loop to process the records one by one.

g. Keep the transaction as short as possible - The length of transaction affects blocking and deadlocking.Exclusive lock is not released until the end of transaction. For faster execution and less blocking the transaction should be kept as short as possible.

h. Use Try-Catch for error handling it will help full to easily debug and fix  the issues in case of big portion of code.

2. If the system thread is consuming most of the CPU

If none of the SQL queries are consuming majority of the cpu then we can identify if the back ground threads is consuming the majority of CPU by looking at sysprocesses output for background threads.

Select * from sys.sysprocesses where spid<51

Check if you are hitting any of the known issues such as resource monitor may consume high CPU (hot fixes available ) or ghost clean up task uses 100% of the CPU on the idle system in SQL Server 2008 or SQL Server 2005.

Wednesday, 30 March 2016

SOL Server Login and Authentication and how it works ?

There are two types of authentication in SQL server as
  1. Windows Authentication
  2. SQL Authentication
Windows Authentication :
  1. These are created for Active Directory User.
  2. By the use of Active Directory user both windows and SQL server login are possible.
  3. This option is selected at the time of installation.
  4. It follow the windows password policy.
How It works :
When the user is connected by the Active Directory user, then windows verify the username and password and allocate a unique that is called TGT (Ticket Granting Ticket). Now when the user is going to login with the TGT, it will be allowed by the SQL server.

SQL Authentication :
  1. For non active directory we create the SQL Authentication.
  2. By default for every instance there is a user as "sa".
  3. The information about SQL usernames and passwords are mentions in SQL server.
How it works :
At the time of login SQL server check and verify the give credentials.

SQL Server users creations :

Create active directory user from windows Control Panel and add the user at the time of installation or use the following query as
use master
Create Login <Login Name> from windows.

In Object Explorer expand the security -> select login folder -> Add new login and follow the password policy.


Orphan Login:
When active user is deleted, the mapping login in SQL server is called Orphan Login.
To check the orphan login, use the following query as


The solution of the problem is, create the active directory user.  

Tuesday, 29 March 2016

About DDL, DML, DCL, TCL, DQL in SQL Server

DDL- it is data definition language, this is used to define the database structure or schema (create, alter, drop, Rename, truncate and Comment.
  • Create: it is used to create database and database Objects like tables, index, stored procedure, views, triggers, functions and etc.
Example: To create Customer table.
create table Customer(
Id int primary key identity(1,1) not null,
Name nvarchar(50) ,
HomeAddress nvarchar(50)
  • Alter: it is used to create database and its Objects.
  • Drop: it is used to delete objects from database.
  • Truncate: it is used to remove all records from a table, including all spaces allocated for records are removed.
  • Rename: It is used to rename the objects.
  • Comment: // Single line Comments, /* --Multi Line Comments-- */ used to comment the SQL statements. CL in SQL
DML –it is Data Manipulation Language, this commands is used for managing data within schema objects (Select, Insert, Update and Delete).
  • Select: To retrieve data from the database table.
  • Insert: To insert date into a table.
  • Update: To update the existing data in a table.
  • Delete: delete all records from a table.
DCL-it is Data Control Language; this command is for control the access (Grant, Revoke) to data stored in the database.
  • Grant: All users access privileges to database.
  • Revoke: Withdraw user’s access privileges given by using the Grant command.
TCL-it is Transaction Control Language, this commands is used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. (Commit, Rollback and Savepoint).
  • Commit: Commit is used for the permanent changes. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit.
  • Rollback: Rollback is used to undo the changes made by any command but only before a commit is done. We can't Rollback data which has been committed in the database with the help of the commit keyword.
  • Save point: creates points within groups of transactions in which to ROLLBACK.
  • SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
DQL-it is Data Query Language, this commands is used to retrieve(select) data from the database .

  • Select: To retrieve data from the database table.

What is SQL Server (Definition)

SQL Server is a relational database management system (RDBMS) from Microsoft that's designed for the enterprise environment. SQL Server runs on T-SQL (Transact -SQL), a set of programming extensions from Sybase and Microsoft that add several features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables. SQL server works on computer paging. Like all major RBDMS, SQL Server supports ANSI SQL, the standard SQL language

SQL Server History:

Code named Yukon in development, SQL Server 2005 was released in November 2005. The 2005 product is said to provide enhanced flexibility, scalability, reliability, and security to database applications, and to make them easier to create and deploy, thus reducing the complexity and tedium involved in database management. SQL Server 2005 also includes more administrative support.

The original SQL Server code was developed by Sybase; in the late 1980s, Microsoft, Sybase and Ashton-Tate collaborated to produce the first version of the product, SQL Server 4.2 for OS/2. Subsequently, both Sybase and Microsoft offered SQL Server products. Sybase has since renamed their product Adaptive Server Enterprise.