sqlschool.gr logo

articles

Articles of SQLschool.gr Team

Πώς θα βρω τα queries που κάνουν υψηλή χρήση της CPU;

Antonios Chatzipavlis
Sunday 28 June 2009

Λοιπόν μιας και απέκτησα και εδω ένα blog είπα να κάνω σεφτέ με κάτι που λίγο ή πολύ  όσοι ασχολούμαστε με SQL Server αντιμετωπίζουμε.

Πως θα βρω τα queries που κάνουν υψηλή χρήση της CPU;

H απάντηση στο ερώτημα αυτό είναι η παρακάτω custom stored procedure η οποία δουλεύει σε SQL Server 2005 & 2008

 

SQL Script

create procedure spFindQueriesThatUseHighCPU 
as 
    Set NOCOUNT ON 

    SELECT TOP 100 
            (a.total_worker_time/a.execution_count) as [Avg_CPU_Time], -- the time is in ms 
            Convert(Varchar,Last_Execution_Time) as 'Last_execution_Time', 
            Total_Physical_Reads, 
            SUBSTRING(b.text,a.statement_start_offset/2, 
            (case 
                when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2 
                else 
                a.statement_end_offset end - a.statement_start_offset)/2) as Query_Text, 
            dbname=Upper(db_name(b.dbid)), 
            b.objectid as 'Object_ID' 
    FROM sys.dm_exec_query_stats a 
        cross apply sys.dm_exec_sql_text(a.sql_handle) as b 
    ORDER BY [Avg_CPU_Time] DESC 
go

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn

sqlschool.gr © 2010-2025 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.