"Reprint" SQL Server queries for high CPU-intensive SQL statements

Source: Internet
Author: User
Tags cpu usage high cpu usage

This article is guided by: The trigger causes deadlock, the job is many and frequent, the use of the middle table, the large number of cursors, the design of the index is unreasonable, the transaction operation is frequent, the SQL statement design is unreasonable, will cause the query inefficient, affect the performance of the server. We can use SQL Server's own profiling Trace tool SQL Profiler to analyze the source of problems caused by database design and to do targeted processing; here's how SQL Server queries for high CPU-intensive SQL statements

SQL Server queries for high CPU usage, sys.sysprocesses, dm_exec_sessions, dm_exec_requests

First, to see how many current database user connections are

Use master

GO

SELECT * FROM Sys. [sysprocesses] WHERE [spid]>50--and db_name ([dbid]) = ' Gposdb '

SELECT COUNT (*) from [SYS]. [Dm_exec_sessions] WHERE [session_id]>50

Second, select the first 10 most CPU-consuming sessions

SQL CodeCopy

Iii. Query the top 10 CPU-consuming SQL statements

SQL CodeCopy

Iv. How many workers are waiting in a query session

SQL CodeCopy

V. Querying high CPU-intensive statements

SQL CodeCopy

"Reprint" SQL Server queries for high CPU-intensive SQL statements

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.