Problems with the processor SQL Server CPU. CPU issues. Processor query Optimization

How to understand that the problem is in SQL Server - Go to Task Manager, on the Details tab, find sqlserver and look at the CPU column.

If this value is consistently high, it means that there is a CPU leak somewhere. 

In this guide, we have collected various tips on how to solve this problem. 

1. First of all, we determine the problem in Kernel or User queries.

In perfmon, look at the following parameters: 

  • Processor: % Privileged Time – Percentage of time processor spends on execution of Microsoft Windows kernel commands such as OS activity. (If more than 30% involve Windows Admins)
  • Process (sqlservr): % Privileged Time – the sum of processor time on each processor for all threads of the process (SQL Kernel)
  •  Processor: % User Time – percentage of time the processor spends on executing user processes such as SQL Server. This includes I/O requests from SQL Server

If this value % Privileged Time/No of logical cpus is more than 30%, it is most likely due to system settings, possibly antivirus. 

2. Looking for problematic queries:

SELECT * FROM sys.sysprocesses
WHERE cmd like 'LAZY WRITER' or cmd like '%Ghost%' or cmd like 'RESOURCE MONITOR'

You can find this query by spid: 

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 78
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

3. In the network, we are looking for sp_who1, sp_who2, and sp_who3-they allow us to find problematic queries

Also try running the following queries to find problem areas on the CPU

SELECT GETDATE() AS "RunTime", st.text AS batch, SUBSTRING(st.text,statement_start_offset/2+1
,((CASE WHEN a.statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),st.text)) * 2)
ELSE a.statement_end_offset END)  - a.statement_start_offset)/2+1)  AS current_statement
, qp.query_plan, a.*
FROM sys.dm_exec_requests a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) AS qp
ORDER BY CPU_time DESC

Another script for searching for problematic queries on the CPU:

/*
Disclaimer: I am not sure for the origin of this script/query.
This query is used in our team to identify and resolve high CPU issue

*/
--define the temptables that will hold intermediary results
IF OBJECT_ID('tempdb..#dbcc') IS NOT NULL
    DROP TABLE #dbcc

create table #dbcc(c1 varchar(15), c2 int, c3 varchar(255),spid int default 0)

IF OBJECT_ID('tempdb..#cpugroups') IS NOT NULL
    DROP TABLE #cpugroups

create table #cpugroups (sql_handle binary(20), sql_text nvarchar(50),total_cpu bigint,total_io bigint,total_sessions int, total_threads int)


--take the SPID groups that are running same code (NOT statement)
insert into #cpugroups
select top 10 sql_handle,substring((select text from fn_get_sql(sql_handle)),1,50), SUM(CPU) TotalCPUForGroup, SUM(physical_io) TotalIOForGroup, COUNT(distinct spid) TotalNoOfSessions,COUNT(*) TotalNoOfThreads
from master..sysprocesses (nolock)
where spid>50 and status<>'sleeping'
and sql_handle<>0x0 and spid<>@@spid
group by sql_handle
order by TotalCPUForGroup desc


declare @sql nvarchar(max)
declare @t table (spid int)

INSERT INTO @t
SELECT DISTINCT spid FROM master..sysprocesses WHERE spid>50 and sql_handle in (select sql_handle from #cpugroups)


declare @spid int


WHILE EXISTS(select * from @t)
BEGIN
  select top 1 @spid=spid from @t
  set @sql='dbcc inputbuffer('+LTRIM(STR(@spid))+')'

  --try to retrieve the original command for all SPIDs
  BEGIN TRY
    INSERT INTO #dbcc(c1, c2, c3)
    EXEC (@sql)

    update #dbcc
    set spid=@spid
    where spid=0

  END TRY
  BEGIN CATCH
  END CATCH


  delete from @t where spid=@spid

END


select * from #cpugroups
select c3 [sql_text], count(*) NoOfSessionsRunning from #dbcc group by c3 order by 2 desc
select * from #dbcc

For found elements, you can delete the plan in the cache (substituting sql_handle):

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

One more request to search for problems on the 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

And one more: 

SELECT s.session_id,

r.status,

r.blocking_session_id 'Blk by',

r.wait_type,

wait_resource,

r.wait_time/(1000 * 60) 'Wait M',

r.cpu_time,

r.logical_reads,

r.reads,

r.writes,

r.total_elapsed_time/(1000 * 60) 'Elaps M',

Substring(st.TEXT,(r.statement_start_offset/2) + 1,

((CASE r.statement_end_offset

WHEN -1

THEN Datalength(st.TEXT)

ELSE r.statement_end_offset

END - r.statement_start_offset)/2) + 1) AS statement_text,

Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,
r.command,

s.login_name,

s.host_name,

s.program_name,

s.last_request_end_time,

s.login_time,

r.open_transaction_count

FROM sys.dm_exec_sessions AS s

JOIN sys.dm_exec_requests AS r

ON r.session_id = s.session_id

CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st

WHERE r.session_id != @@SPID

ORDER BY r.cpu_time desc

Also right-click on the Server reports > Standard reports > Top CPU queries.

4. In the found queries, look at the execution plan and see where the highest cost is.

5. Additional recommendation: 

  • check that all the necessary indexes are on tables that participate in problematic queries. 
  • try to trim the data as quickly as possible in where to reduce the selection for further processing. in Other words, try not to scan a very large table without using any filtering (or this filtering contains expensive logic for checking conditions)
  • make sure that you don't have costly type conversions
  • if you use multiple functions, make sure that they use a minimum of extractions inside. 

Sources and what to read on the topic: 

http://dba-datascience.com/high-cpu-usage-sql-server/

https://support.microsoft.com/ru-ru/help/2009160/high-cpu-use-occurs-in-your-queries-on-sql-server

https://blog.sqlauthority.com/2018/03/23/sql-server-how-to-fix-high-cpu-consumption-on-sql-server-2017-and-2016/

https://logicalread.com/2014/12/04/troubleshoot-high-cpu-sql-server-pd01/#.XqLoTmYzaM8

https://www.sql.ru/forum/96127/sp-who3

https://www.c-sharpcorner.com/blogs/difference-between-spwho-spwho2

https://docs.microsoft.com/ru-ru/archive/blogs/docast/sql-high-cpu-troubleshooting-checklist очень подробная статья.

https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/

https://www.youtube.com/watch?v=98c8spD5k5s

-----------------------------------------------------------------------------------------------------------------------------

Alternative documentation for searching for SQL Server CPU problems

Security questions.

Configuration of the server hardware.

Disk subsystem.

Free space.

Is there an antivirus on the server?

DB (operations) are not performed/are performed as often.

Integrity check

Restructuring the Indexes

Updating statistics

Compression (Shrink)

General recommendations

http://sqlcom.ru/optimization_query/sql-server-performance-problems-after-moved-to-new-server/

Power supply - use « High performance »

to configure write caching on the disk

antivirus, - add SQL Server folder and DB files to exceptions

(?) setting up parallelism parameters are configured (cost threshold for parallelism, max degree of parallelism)

(?)configuring Hyper-Threading.

SSMS monitoring-Standard reports

! the disadvantage of – is to run on   each database.

Standard reports in the Management Studio user interface

SQL Server Management Studio provides the minimum required set of standard reports for getting information in the user interface mode.

These reports can be accessed through the object explorer (Object explorer) → Right-click on the database→ reports » (Reports) → «Standard report » (Standard reports)

The list of "Standard reports":

List of Standard reports;:

  • disk space is Used
  • Disk memory usage by top tables
  • Disk memory usage by the table
  • Disk memory usage by the
  • section
  • backup and restore Events
  • All transactions
  • All blocking transactions
  • Longest transactions
  • Transactions that block the largest number of transactions when executing
  • Transactions with the highest number of locks
  • Resource blocking statistics for objects
  • Object execution statistics
  • Database consistency Log
  • Index usage Statistics
  • Physical statistics of the index
  • Schema change Log
  • User Statistics
  • List of « Custom reports »  

Activity Monitor-activity Monitor

Open the activity monitor CTRL+ALT+A or SSMS standard. icon toolbar.

The SQL Server 2008 activity Monitor combines process data to provide visual information about current and recently executed processes.

The activity Monitor offers the administrator an overview section that looks similar to the Windows task Manager, as well as components for detailed viewing of individual processes, waiting for resources, I/o to data files, and recent resource-intensive requests.

Monitoring  Reporting Services - Performance Dashboard Reports

To monitor SQL Server, there is an interesting report package Reporting Services, called SQL Server Performance Dashboard Reports.

http://sqlcom.ru/dba-tools/sql-server-dashboard-reports/

You can Download

The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio.

Q :is Reporting Services used?

 

Monitoring(paid)

http://sqlcom.ru/monitor/microsoft-sql-server-monitoring-solution/

(paid) from different companies:

    Idera — SQL Diagnostic Manager

    Red-Gate — SQL Monitor

    ApexSQL — ApexSQL Monitor

    Quest — Spotlight on SQL Server Enterprise

    SentryOne — SQL centry

also-option for monitoring SQL Server on Zabbix.

 

SCRIPTS-system

-- 010_sp_who and sp_who2

-- 010_sp_who and sp_who2

-- -- stored procedures sp_who and sp_who2, legacy of SQL Server 7/2000.

find blocking and waiting requests

1 -- 100_1_sys.dm_exec_query_stats - most time cpu

-- 100_1_sys.dm_exec_query_stats_Which Queries are taking the most time cpu to execute

sys.dm_exec_query_stats     OUTER APPLY sys.dm_exec_query_pl

3 - 100_3_sys.dm_exec_query_stats cpu-utilization

100_3_sys.dm_exec_query_stats_sql-server-cpu-utilization-io-usage-and-memory-usage.sql

sys.dm_exec_query_stats  CROSS APPLY  sys.dm_exec_plan_attributes

Результат имеет вид.

row_num

DatabaseName

CPU_Time(Ms)

CPUPercent

1

master

6355553

88.11

2

АutoParts_shop_v2

357018

4.95

3

testDB_1

255776

3.55

4

tempdb

244863

3.39

5

msdb

142

0

4 - 100_4_sys.dm_exec_query_stats_Heavy queries

-- 100_4_sys.dm_exec_query_stats_Heavy queries

-- the Script is based on the sys.dm_exec_query_stats view:

the

5 - 100_5_sys.dm_tran_locks_заблокированные requests

Quick way to find blocked requests

 

-- 100_5_sys.dm_tran_locks_blocked requests

 

-- we have to kill blocking_session_id.

-- Examine the BlockingText column

--We can kill a session with KILL 52

6 - 100_6_Which processor is loaded with what

-- 100_6_Which processor is loaded with what

7-100_7_information about users and connections. sql

-- 100_7_information about users and connections. sql

-- views: sys. dm_exec_connections, master.sys.sysprocesses, sys. dm_exec_sessions

SCRIPTS-sp_WhoIsActive

! You need to set an external procedure. You can put it in any database.

Call:

200_2_sp_WhoIsActive_Parameters. sql

 

Installation:

\_SCRIPTS_2_sp_whoisactive\who_is_active_v11_32.sql

 

A source:

http://whoisactive.com/

sp_whoisactive is a comprehensive activity monitoring stored procedure that works for all versions of SQL Server from 2005 through 2017.

«Who Is Active » shows only the current server activity ( who and what is causing the load at this time.). You can use it for data collection and subsequent analysis.

15 DMV is used Inside.

 

Many parameters.

                               @show_sleeping_spids = 2,  -- Show sleeping sessions

                               @show_system_spids = 1, -- Show system sessions

                               @show_own_spid = 1  --  Show your own session

                               , @get_full_inner_text = 1 -- To see all of the activity

                               , @get_outer_command = 1 -- What caused this [sql_text]

                               , @get_task_info = 1 -- Output the [wait_info] column not only the most important wait, but also all the others:

                               , @get_transaction_info = 1 --9. You can also remove aggregation of transactions from a single session and output them separately:

                               , @get_additional_info = 1 --10. Output more detailed information. The [additional_info] column with information in XML format will be added

                               -- , @find_block_leaders = 1 -- Number of blocked processes per session:

                               , @sort_order = '[CPU] DESC' -- Sorting output

 

SCRIPTS-sp_Blitz

! You need to set an external procedure. You can put it in any database.

Call:

300_1_sp_blitz_starting.

Installation:

SCRIPTS_3_sp_BlitzFirst_\...sql

 

A source:

https://www.brentozar.com/askbrent/

https://www.brentozar.com/first-aid/

 

Quick check of your SQL Server (sp_Blitz)

How it works:

  1. You set the procedure
  2. Launch when it's convenient for you

The procedure is Usually performed for 5 to 15 seconds. The script is fully developed so as not to interfere with the work of other users, but you should still check it out on the test server

--dbo.sp_Blitz -- General parameters
--sp_BlitzFirst - "Why is my SQL Server slow right now?"
--sp_BlitzCache - "What are the most resource-intensive queries on this server?"
--sp_BlitzIndex - "How could I tune indexes to make this database faster?"
--sp_BlitzQueryStore - "How has this query performed over time?"
--Sorry, sp_BlitzQueryStore doesn't work on versions of SQL prior to 2016, or Azure Database compatibility < 130.
--sp_BlitzWho --Who’s running what queries right now?

 

SQL Profiler

SQL Profiler-use it where you need to quickly see what the query is.

 

SQL Profiler - remains one of the most used tools for diagnosing SQL Server performance, even though it is considered outdated. Can be removed in future versions of the DBMS, is a graphical add-on for SQL Trace . Like SQL Profiler, SQL Trace is considered an outdated tool and may be removed in the future.  SQL Trace is in support mode and is not updated with new functionality.

number of counters (events) = 180.

Extended Events

In SSMS, under "Manage-> Extended events-> Sessions", you can find a list of all extended event sessions.

creating extended event sessions using T-SQL. – returns an error

Not considered- creating a session using the GUI

Not considered - Windows performance counters (hardware Load/internal SQL Server metrics)

 

 

 

 

Note