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:
https://docs.microsoft.com/ru-ru/archive/blogs/docast/sql-high-cpu-troubleshooting-checklist очень подробная статья.
Alternative documentation for searching for SQL Server CPU problems
Configuration of the server hardware.
Is there an antivirus on the server?
DB (operations) are not performed/are performed as often.
Restructuring the Indexes
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)
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.
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?
(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.
-- 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
sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_plan_attributes
Результат имеет вид.
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
! You need to set an external procedure. You can put it in any database.
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.
@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
! You need to set an external procedure. You can put it in any database.
Quick check of your SQL Server (sp_Blitz)
How it works:
- You set the procedure
- 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-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.
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)
Examples of how Falcon Space platform components workExamples of convenient tables and forms Examples of dashboards Examples of graphs and charts Example of a calendar Table with API data Example of displaying a map with points Types of form fields Example of a statistics widget Example of a hierarchy tree Example of the Time line component Example of a product card Example of resource load tracking Example of a Kanban board Example of a master form Example of uploading Excel/Word documents Example of filling in TIN data
- Management Introduction to Falcon Space. What's the first? Guidance on usability of solutions on the Falcon Space platform Example of creating the Personnel subsystem on the Falcon Space platform Management. How to create a table Management. How to create a form Management. How to create a dashboard Installing Falcon Space on a server / hosting How to localize a site for a non-technical specialist Common SQL errors in stored procedures and queries Problems with the processor SQL Server CPU. CPU issues. Processor query Optimization Creating pages with the table/form component with automatic generation of necessary SQL procedures SQL. Optimization of SQL queries. MS SQL queries are slow Using SQL Profiler Express to solve SQL query problems Setting up fulltext search in SQL Server SQL Management Studio is slow. How to solve the problem How to improve the performance of IIS (Internet Information Services web server)
- Falcon Space Foundation
- Basic components
- Falcon Space Features
- Additional component
- HOWTO Tables
- HOWTO Forms
- HOWTO JS
- HOWTO CSS
- HOWTO Layout
- Solve problems
Falcon Space Platform
This is a reduction in the cost of ownership
at the expense of fewer people to support
This is a quick change
while using the program
This is a modern interface
full adaptation for mobile devices