Author: SQLAutomationGuru

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!!

Powershell script for multiple servers

SQLAutomationGuru1 Comment

In the previous article, we saw how to connect to a SQL server and retrieve information using PowerShell.
Consider the case when you have hundreds of servers and wish to retrieve data from all of them. Will you manually perform the query against each server? Nope! All you have to do is create a basic loop for each server and run your query. It will connect to each server and collect data automatically.

Let’s start.
First, let’s have a look how a loop works in the Powershell. Here we will use ‘foreach’ loop.

Foreach (placeholder variable IN the collection)
{
Code for what you want to do in the collection
}

Now, if you have hundred servers, put them into a text file and read into a powershell variable.
$servers = get-content -Path “C:\temp\Serverlist.txt”

Now you have to put a foreach loop to access each server you stored in $servers

foreach ($server in $servers)
{
Your code
}

Here is your code:

<#
   Powershell script to connect multiple servers
#>

#Read the server list
$servers = get-content -Path "c:\temp\Serverlist.txt"

foreach($server in $servers)
{
   #Create a connection to the server
   $connectionstring = "Data Source"="+"$server"+";Initial Catalog=master;Integrated Security=SSPI;"
   $connection = New-Object System.Data.SqlClient.SqlConnection
   $connection.ConnectionString = $connectionstring

   #Create a command
   $command = New-Object System.Data.SqlClient.SqlCommand
   $command.CommandText = "Select * FROM sys.databases"

   #Assign the command to the connection
   $command.Connection = #Connection

   #Create an adapter
   $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
   $SqlAdapter.SelectCommand = $command

   #Create your dataset and fill the result
   $DataSet = New-Object System.Data.DataSet
   $SqlAdapter.Fill($DataSet)
   $DataSet.Tables[0] | Export-csv "C:\temp\output.csv" -append -Force -NoTypeInformation #Output your 
   data to a csv file
}

You have observed in the Export-csv command we have added -append in the end. This will append your data into the csv file retrieved from each server.

-NoTypeInformation property will not put default csv information at the top of your csv file.

Try this script and see if it’s helpful to you. Happy Learning!

My First Powershell Script

SQLAutomationGuruLeave a comment

Hello there, As a DBA, you must do numerous checks on your servers on a daily basis. It could involve things like monitoring disk space, running a health check, checking the status of services, and other minor details.
Instead of logging into each server to acquire the details, don’t you think you should have some type of automation script to get those details for you, and if you can execute them on a regular basis via task scheduler or a job on a daily basis, it will help you a lot?
And this is where Powershell automation comes in helpful.

So here is your first Powershell script to connect to your server and get the data.

<#
   My First Powershell script
#>

#Assign a server name to the $server variable
$server = 'servername'

#Create a connection to the server
$connectionstring = "Data Source"="+"$server"+";Initial Catalog=master;Integrated Security=SSPI;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionstring

#Create a command
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = "Select * FROM sys.databases"

#Assign the command to the connection
$command.Connection = #Connection

#Create an adapter
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command

#Create your dataset and fill the result
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | Export-csv "C:\temp\output.csv"  #Output your data to a csv file
#or
$DataSet.Tables[0] | Out-GridView   #another way to see the output

This script will return the database names from the SQL server. Just change the select query and you will get the desired output from the server.

This is how the script looks in the editor.

Hope you find it useful. Please provide your feedback in the comments.
Thank You! Happy Learning!

In the next post we will see how to get data from multiple servers.
Powershell script for multiple servers