Databases CPU resources usage

September 1, 2023September 3, 2023 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!!

Powershell script for multiple servers

August 29, 2023September 1, 2023 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

August 26, 2023September 1, 2023 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.