Troubleshoot SQL Server high CPU issue

We can use SQL query or task manager or perfmon to determine the issue.
Once we have identified that SQL Server process is consuming CPU then we have to next find which inside SQL Server process is consuming this CPU.
For this we need to remember that CPU consumes time in 2 modes.

                         1. Kernal Mode

                         2. User Mode

These 2 modes can be seen using performance monitor by monitoring "%Privileged time"  and "% User Time" counters under the "Process" node.
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 them 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 Recompiles ( In general stats change , schema change , temp tables , recompiled all the user defined SP's etc)


3. System threads spiking CPU ( In general ghost clean up , lazy writer and resouce monitor will be caused).

4. Running many traces.

1 . Query executing causing CPU spike :

In general query execution takes long time due to outdated statistics , lack of indexes, server configurations , distributed queries etc .

Run the below query to find the list of currently running queries order by CPU.

SELECT r.session_id
 ,st.TEXT AS batch_text
 ,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, (
   (
    CASE 
     WHEN r.statement_end_offset = - 1
      THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2)
     ELSE r.statement_end_offset
     END
    ) - r.statement_start_offset
   ) / 2 + 1) AS statement_text
 ,qp.query_plan AS 'XML Plan'
 ,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY cpu_time DESC

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.


Ghost Clean up - Assume you remove some records from a database table which is running in SQL server 2005 or 2008 then the database engine marks these records as ghost records. Then SQL server service starts a Ghost clean up task to complete the removal of the ghost records from the table. In this scenario SQL server uses 100 % of the CPU.


Use the below query to get historical data to find out the query which was cause CPU issue earlier. I have specified top 50 queries which you can also increase it.

SELECT TOP 50 sum(qs.total_worker_time) AS total_cpu_time,
 sum(qs.execution_count) AS total_execution_count,
 count(*) AS number_of_statements,
 qs.plan_handle
FROM sys.dm_exec_query_stats qs
GROUP BY qs.plan_handle
ORDER BY sum(qs.total_worker_time) DESC

High Compiles and recompiles will also cause high CPU.

Apart from this SQL Server that uses .NET framework can cause high.

SQL Server DBA Interview question and answers Part 7

                                              Performance Tuning

1. Which Tools are used for Performance Tuning?

There are many tools are used for Performance Tuning.

a. Windows Tools for monitoring applications :

Performance monitor - Performance counters and Logs , Task Manager and Network Manager.

b. SQL Server Tolls for monitoring components :

SQL Trace , SQL Profiler , DMV's, System Stored procedures , Graphical Show Plan , Activity Monitor , DBCC , Built-in Functions , Trance Flags.

2. How to identify longest running queries ?

There are three ways to identify slow running queries.

1. Profiler ( By using duration of the query )
2. Sys.dm_exec_query_stats and sys.dm_exeec_requests DMV's
3. DBCC OPENTRAN

3. How to analyse query performance ?

We can analyze query performance in three ways.

T-SQL : SET SHOWPLAN_ALL ON/OFF , SET SHOWPLAN_TEXT ON/OFF.

SSMS : Estimated execution plan & Actual execution plan.

4. Reasons for Slow Running Queries ?

There are a number of common reasons fo slow-running queries.

1. Lack of useful indexes , Lack of useful data striping (RAID).
2. Blockings , Table Scans , Lack of useful partitioning.
3. Missing or out of date statistics .
4. Slow network communication.
5. Insufficient memory available for SQL Server.
6. Insufficient disk space.
7. Excess recompilation of stored procedures.
8. Procedures and Triggers without SET NOCOUNT ON.

5. How to increase query performance ?

We can improve the query performance in the following ways.

1. Add indexes if required.
2. Run update statistics for out of date statistics.
3. Resolving blocking issues.
4. Add space to DB files or TempDB , if that are not having enough space.
5. Reduce the too much normalization.
6. Using Temporary tables instead of cursors.
7. SPs and Triggers are with SET NOCOUNT ON.
8. Unnecessarly complicated joins.

6. Explain about profiler / What are the users of Profiler?

SQL Profiler can capture SQL Server events from the server to analyse or troubleshoot performance problems such as finding slow-running queries , monitoring and optimization.

Advantages :

* Find the worst performing queries.

* Identify the cause of a deadlock.

* Monitoring stored procedure performance.

* Audit SQL Server activity.

* Monitoring T-SQL activity per user.

7. Event captured in SQL Profiler ?

1. Sessions , Transactions and Tuning.

2. Security Audits.

3. Table Scans.

4. T-SQL statements , stored procedures.

5. Cursors , Locks , dead locks etc.

8. What are the common failures in SQL Server ?

There are three common failures occurs in SQL Server.

Database Failures
Physical Server Failures
SQL Server service failures

Causes of Database Failures :

There are three common issues will cause database failures. Log filer viewr is very useful to diagnose these problems that will occurs in SQL Server 2005.

1. Database has run out of Disk Space :

If database is on line and running out of disk space the data cannot be inserted into the database.
If the data file becomes full during recovery then database engine marks the database as "Resource Pending".

2. T. Log Full :

If the database is online and the T.Log becomes full then the database engine issues 9002 error and it is read-only state and will not allow updates.

If the T.Log becomes full during recovery then the database engine marks the database as "Resource Pending"

3. TempDB has run out of Disk space :

Temp db stores temporary objects such as stored procedures , cursors, tables ,table variables , the results of join. SQL Server creates temp db every time while restarting the SQL Server . We cannot take backup of temp db . Rebuilding indexes consumes more space in temp db . Its recovery model is simple and database id is 2 . If tempdb runs out of disk space then it causes significant problems and the errors are written to SQL server log. These errors 1101,1105,3959,3967,3958,3966 indicates Temdb has insufficient space.

Cause of Physical Server failures :

There are five common issues will causes physical server failures.

* Disk Failures.

* Memory Failures.

* Processor Failures.

* Network Card Failures.

* RAID Failures

9. If data file is full what you will do ?

If the primary data file is full we can add secondary data file.

10. If Log file is full what you will do ?

If the T.Log file is full then the database issues 9002 error and it is in read-only state and will not allow updates.

* Backing up the Log.

* Adding one or more Log Files.

* Moving the Log to another disk.

* Increasing Log file size or enabling auto growth.

* Terminating long-running transactions.

11. If the server has completely failed then how you will restore onto a new server ?

Build the windows server and restore the domain logins to supports windows authentication
Install SQL Server in single-user mode and restore the master database.
Put SQL Server in single-user mode and restore the master database.
Restore the MSDB database.
If the Model database was modified restore it.
Restore the user databases.

12. How to recover crashed SQL Server with existing data files and logs?

* Before installing of SQL Server take the backup of existing datafiles and transaction logs.

* Rename the data files and transaction logs for all system stored  databases master , model,msdb and user databases to file_name_old.mdf and file_name_old.ldf.

* Install the SQL Server.

* Stop the services for SQL Server

* Rename the new datafiles and transaction logs for all systeam databasels to file_name_new.mdf and file_name_new.ldf.

* Rename the old data files and transaction logs for all system databases to original name i.e file_name.mdf,file_name.ldf.

* Start the services for SQL Server.

* Check all user and system databases exists on the server and nothing is off line.

* Verify the SQL Server and Windows Logins.

13. How to understand the database engine errors ?

If any error occurs the server writes error messages (database mail, sql agent , sql server and windows NT) to logs . These logs having error number , error message , servity , state , procedure name , line number. We can easy to understand errors by viewing log file viewer.

14. What is  Severity level in event log ? at what Severity level are written to the SQL Server log ?

The severity level tells how bad the error is . Above 19 severity level errors are written to the SQL Server log.

15. If a server performance is slow ? How you can trouble shoot problem ?

16. If a database perfromance is slow ? How can you do performance tuning?
What are the performance issues ?  Explain them ?

SQL Server DBA Interview question and answers Part 6

1. What is blocking ? How to identify and resolve the blocking ?

Blocking happens when one user holds an exclusive lock on an object and a second user requires an exclusive lock on the same object. This forces the second user to wait , block on the first.

Determining blocking sessions :

We can determine blockings using Activity Monitor , sp_who2 , sp_lock,sys.sysprocesses,sys.dm_exec_requests,sys.dm_os_waiting_tasks.

Resolving blocking session :

Right click on session and kill in Activity Monitor
Kill Session_ID
SQL Server DBA Interview question and answers Part 5

2. What is Deadlock ?

A deadlock occurs when users try to place exclusive locks on each other's object.

Ex:

User1 places an exclusive lock on Table1 and then tries to place an exclusive lock on Table2. User2 already has an exclusive lock on Table2 and user tries to put an exclusive lock on Table1. This condition causes endless loop of waiting for the locks to be released.

The database engine picks one of the victim (users) and kills their query and send a error message to users "You are the victim of a deadlock and try again later"

Deadlock Information Tools :

1. TraceFlags :

DBCC TRACEON(1204) & DBCC TRACEON(1222). When these trace flags are enabled the dead lock information is captured by the SQL Server errorlog.

2. Deadlock graph event in SQL Profiler :

SQL Server profiler graphically representation of tasks and resources involved in a deadlock.

3. System View :

 We can find the blocking sessions by writing the following query.

Select session_id,status,blocking_session_id from sys.dm_exec_requests where blocking_session_id>0

Resolving Deadlock :

After finding the session causing the problem we can use Kill process_id command.

3. How to find the locks on a resource?

We can use sys.dm_tran_locks system view or sp_lcok

Ex: Select resource_type,resource_mode,request_status,request_session_id from sys.dm_tran_locks.

4. What is Lock escalation ?

Lock escalation is the process of converting a lot of low level locks like ro locks,page locks into higher level locks like table locks.

5. What is virtual memory ? How to assign virtual memory ? How much space required for virtual memory ?

A reserved disk space to maintain transactions whenever memory (RAM) is full. Virtual memory size is at least 3 times of the physical memory installed in the computer.

To set Virtual Memory : Right click on System --> Select system properties --> Advanced --> Virtual Memory --> Change --> Select directory --> Provide Min and Max value.

6. What is check point and when it occurs ?

A check point is a SQL Server operation that synchronizes the physical data with the current state of the buffer cache by writing out all modified data pages in buffer cache to disk.

Check point occurs :

When SQL Server shutdown.
When Alter database command is used to add or remove data files.
When recovery model change from Full / Bulk-Logged to Simple.
Before a database backup is perfromed.
In Simple recovery model log is truncated after checkpoints occurs.
Manual CHECKPOINT command.

Ex: CHECKPOINT 60 (60 is duration in seconds)

Checkpoint runs periodically on Full / Bulk Logged recovery model databases as specified by the recovery interval setting.

7. What is the purpose of LSN Numbers?

Every record in the transaction log is uniquely identified by a log sequence number.LSNs are used internally during a restore sequence to track the point in time to which data has been restored.

8. What is Fill Factor ? How to assign FillFactor ?

A Fill factor is a reserved free space on each leaf level page which is used for future growth of data or index in a table and reduces the page splits.

Assign Fill Factor : Right click on Server --> Properties --> Database Settings --> Default Index Fill Facto --> Provide the Value.

9. Backup Strategy ?

Generally backups are done by client requirement. But most of the companies will follow the strategy like

Every Sunday night Full Backup
Every Day night Differential Backup
Every 15/30/45 Minutes Transactional Log Backup.

10. Restore Strategy ?

First restore recent last full backup WITH NORECOVERY.
Then restore last Diff.backup WITH NORECOVERY.
Then restore all transaction log backups since recent Full / Diff backups WITH RECOVERY.

11. What is Point-in-time restore and use of that ?

Point-in-time restore is used to restore a database to a particular time just before a failure has occurred or before corrupt data.

To Restore a database to a point-in-time :

STOPAT option is used to restore a database to specific time.

12. What is End Point ? 

An endpoint is a network protocol which is used to communicate Principal,Mirror & Witness Servers over the network.

13. What is Hardening ?

As quick as possible the log buffer is written to the transaction log on disk . This process is called as hardening.

14. What is Log Buffer ?

A Log buffer is a special location in memory (RAM) . SQL Server stores the changes in the databases log buffer.

15. What are the agents used for Transactional Replication ?

SLD i.e Snapshot Agent , Log Reader Agent & Distributor Agent.

16. What are the Agents used in Merge Replication ?

SM i.e Snapshot Agent & Merger Agent

17.  What is Database Mail ?

Database Mail is designed for reliability , scalability , security and supportability. DB Mail is an enterprise solution for sending e-mail messages from the Microsoft SQL Server.

Mirroring in SQL Server

Mirroring is introduced in 2005.Mirroring in one of the disaster recovery solution. Mirroring is an option to improve the availability of a databases which supports automatic failover with no loss of data.

Advantages :

* Protection against database failures.
* Automatic failure detection and failover.
* Automatic client redirection.
* Multiple operating modes.
* Improves the availability of the production database during upgrades.

Prerequisites :

* SQ Server 2005 with SP1 or above versions.
* Database recovery model should be FULL.
* Service Broker should be enabled on the databases.
* Both principle and Mirror servers should have same case sensitivity settings i.e either Enterprise or Standard editions.
* Witness server can have any edition i.e Enterprise / Express / Standard / Work Group .
* For Principal , Mirror and Witness servers TCP / IP should be enabled.
* All logins for connecting principal database should be reside on Mirror database.
* Like Log Shipping here also 3 servers are required.

Limitations :

* Maximum 10 databases per instance can support on a 32-bit system.
* Database Mirroring is not supported with either cross-database transactions or distributed transactions.

Principal Server :

One server serves the database to client is called Principal Server and is having original data. We can have only one Principal Server and it has to be on a separate server . Principal database recovery model should be FULL.

Mirror Server :

Mirror Server maintains copy of the principal database and database called as Mirror database and recovery model must be FULL and database state is WITH NO RECOVERY (Stand By).

Witness Server :

Mirror Server is an optional server used  for automatic failover to the Mirror Server if the Principal becomes unavailable .To support automatic failover witness server is mandatory.

Operations running in Operating Modes :

Synchronous :

 In Synchronous operation , The principal server sends the log buffer to the mirror server and then waits for a response from the mirror server.
 It supports High Availability & High Protection Modes.

Asynchronous :

In Asynchronous operation , The Principal server does not wait for a response from the mirror server after sending the log buffer.

Operating Modes :

High Availability :

High availability mode runs synchronously i.e Prinicpal server sends the log buffer to the mirror server and then waits for a response from the mirror server. High availability requires a witness server instance so that it supports automatic failover.

High Protection :

High protection mode runs synchronously . In high protection witness server is not required i.e no automatic fail over support. Always commit changes at both the principal and mirror.

High Performance :

High performance runs asynchronously i.e the prinicpal server doesn't wait for a response from the mirror server after sending the log buffer. In high performance also witness server is not required so no automatic fail over support.

Note:

Witness server is not required in high protection and high performance mode so automatic failover is not applicable for high protection and high performance modes.

Operating Modes in 2012 :

High Performance (Asynchronous ) : Commit the changes at the principle and transfer them to the mirror.

High Safety with out automatic fail over (Synchronous ) : Always commit the changes at both the principal and mirror.

High safety with automatic failover (Synchronous ) : Requires a witness server instance. Commit changes at both principal and mirror if both are available. The witness server controls automatic failover to the mirror if the principal becomes unavailable.

Terminologies in Mirroring :

Log Buffer : A log buffer is a special location in memory (RAM) . SQL Server stores the changes in the database log buffer.

Hardening : As quick as possible the log buffer is written to the transaction log on disk . This process is called as Hardening.

End Point : An endpoint is a network protocol which is used to communicate Prinicpal , Mirror and Witness servers over the network.

Creation of Endpoint :

Create endpoint <endpoint name> State= Started / Stopped / Disabled. as tcp (listner_port=5022/5023) for database_mirroring (role=partner/witness)

The default port numbers of principal , mirror and witness servers are 5022,5023 and 5024.

select name,port from sys.tcp_endpoints

Trace flags used in Mirroring :

Trace flags are used to temporary set specific server characteristics or to switch off or switch on a particular behavior .1400 trace flag is used in mirroring.

Role-Switching : Inter changing of roles like principal and mirror are called role switching.

How to failover the mirror database manually ?

Alter database <database name> set partner failover (do on principal server)

                                                         (or)
SSMS --> Click on principal database --> Task --> Mirror --> Fialover

How to stop the database mirroring ?

Alter database <database name> set partner off

SSMS --> Click on principal database --> Task --> Mirror --> Remove Mirroring.

How to set a witness server to database mirroring >

Right click on Principal database --> Tasks --> Mirror --> Click on Configure Security --> Provide the Endpoint for Witness Server --> Click OK

                                                         (or)
Alter database <Database Name > Set WITNESS= 'TCP://sai.local:5024' (Do this from the Principal Server)

How to remove a witness server from Database Mirroring ?

Right click on Prinicpal Database --> Tasks --> Mirror -->  Remove TCP address from the witness --> Click OK

                                                          (or)

Alter database <database name> set WITNESS OFF

How to set up fully qualified names for Database Mirroring ?

1. FQDN Error :

One or more of the server network addresses lacks a fully qualified domain name specify the FQDN for each server and click start mirroring again.

Syntax for a fully-qualified TCP address is:

TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>

2. Rectifying fully qualified names :

* To view end points : select * from sys.database_mirroring_endpoints;

* Remove existing all endpoints from Principal , Mirror and Witness Serves :

              Drop endpoint <end point name>

* Adding 'Local' as the primary DNS suffix as follows :

* Right-Click on My Computer and then click properties. The system properties dialogue box will appear.
* Click on computer name tab
* Click change. The computer name changes dialog box will appear.
* Click more. The DNS suffix and NetBIOS computer name dialog box will appear.
* Enter the appropriate DNS suffix for the domain.
* Enable "Change primary DNS suffix when domain membership changes" check box.
* Click OK to save the changes and then click OK to exit the Computer Name Changes dialog box.
* Click OK to close the System Properties dialog box and then restart the computer for the change to take effect.

Reconfigure the Database mirroring either GUI or T-SQL.

What are the factors that cause the Log File grow ?

The below are the root causes to grow the log file .

1. Checkpoint has not occurred since last log truncation.

2. Mirroring is paused or mode is in high performance.

3. Long running active transactions

4. In Replication publisher transactions are not yet delivered to distributor.

5.  Huge number of database snapshots is being created.

6. When database is in Full recovery model , no log backups are performed after Full backup.

7. An active Backup / Restore operation is running from long backup.