Category: Query

TOP CPU queries in SQL Server database

SQLAutomationGuru2 Comments

In the previous article we have seen how to identify databases CPU resource usages. Please check Databases CPU resources usage article for more details.
Now if you want to check what all top CPU queries in SQL Server database that are consuming more resources, you can run the following query.

SELECT TOP 10
    ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
    ,TextData           = qt.text
    ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads
    ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads
    ,Executions         = qs.execution_count
    ,TotalCPUTime       = qs.total_worker_time
    ,AverageCPUTime     = qs.total_worker_time/qs.execution_count
    ,DiskWaitAndCPUTime = qs.total_elapsed_time
    ,MemoryWrites       = qs.max_logical_writes
    ,DateCached         = qs.creation_time
    ,DatabaseName       = DB_Name(qt.dbid)
    ,LastExecutionTime  = qs.last_execution_time
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 ORDER BY qs.total_worker_time DESC;

You will get the output like this.

There is one more query to get the data. 

select top 10
query_stats.query_hash,
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) as avgCPU_USAGE,
min(query_stats.statement_text) as QUERY
from (
select qs.*,
SUBSTRING(st.text,(qs.statement_start_offset/2)+1,
((case statement_end_offset
when -1 then DATALENGTH(st.text)
else qs.statement_end_offset end
- qs.statement_start_offset)/2) +1) as statement_text
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st 
) as query_stats
group by query_stats.query_hash
order by 2 desc;

The output will like this.

I hope you find it useful. Please share your views in the comment section.
In the next article we will how to get top 10 IO queries in SQL Server database.

Happy Learning!!

Databases CPU resources usage

SQLAutomationGuru1 Comment

Hello and welcome! SQL server performance management is an important task in the life of a DBA.
You just cant get away with it. There are some queries you can run to check the SQL server health.
First in the series is to check the databases CPU resources usage.

Here is your query:

WITH DB_CPU_STATS_ON_INSTANCE
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName, [CPU_Time_Ms], 
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_STATS_ON_INSTANCE
WHERE DatabaseID > 4 
AND DatabaseID <> 32767 
ORDER BY row_num OPTION (RECOMPILE);

It will provide you database wise CPU usage. The output will look like this.

Hope you find it useful.

In the next article we will see how to find top CPU queries in SQL Server database that are consuming more CPU.
TOP CPU queries in SQL Server database

Happy Learning!!

SQL query to get Linked Server details

SQLAutomationGuruLeave a comment

Hello and welcome! I recently received a request to retrieve linked server details from a SQL server. DBAs were performing a migration activity, and clearly linked servers had to be moved to the new version of SQL server as well.

The first step in any migration is to collect data from your old server and discuss it with the appropriate parties.
Going to SSMS and noting down each and every linked server with its properties is a time-consuming task.
This is when SQL queries come in handy.

I have given them below query which can be very helpful to you.

    SELECT @@servername as [Server Name]
    ,ss.server_id
    ,ss.name
    ,'Server' = case ss.server_id
                     when 0 then 'Current Server'
                     else 'Remote Server'
                     end
    ,ss.product
    ,ss.provider
    ,ss.catalog
    ,'Local Login' = case sl.uses_self_credential
                             when 1 then 'Uses Self Credential'
                             else ssp.name
                             end
    ,'Remote Login Name' = sl.remote_name
    ,'RPC Out Enabled' = case ss.is_rpc_out_enabled
                              when 1 then 'True'
                              else 'False'
                              end
    ,'Data Access Enabled' = case ss.is_data_access_enabled
                                  when 1 then 'True'
                                  else 'False'
                                  end
    ,ss.modify_date
     FROM sys.servers ss
     LEFT JOIN sys.linked_logins sl
     ON ss.server_id = sl.server_id
     LEFT JOIN sys.server_principals ssp
     ON ssp.principal_id = sl.local_principal_id

Below is the example of output of this command.

Let me know in comments if it is helpful to you.
Happy Learning!!