Search This Blog

Monday, October 8, 2018

Powershell One Liners: Get current cpu utilization on all your Servers

Powershell One Liner to Get current cpu utilization on all Servers

There are couple pre-requisites to get the CPU usage on servers.

  • You need to have the administrative rights on the servers
  • PowerShell must be launched in elevated privileges mode


# Assuming you have list of servers in servers.txt, each server name in its own line

$ComputerNames = get-content servers.txt


# Method 1 - Using the Get-Counter cmdlet
# Notice that here you can also easily add the interval and max sample sizes, with the WMI  method shown next, you cannot


Get-Counter -Counter "\Processor(_Total)\% Processor Time" `
            -SampleInterval 1 `
            -MaxSamples 2 `
            -ComputerName $ComputerNames | 
          
            Export-Counter -path PercentageProcessorTime.csv `
                           -fileformat csv -force


# Method 2 - Using WMI Object

Get-WmiObject -Query "Select * from Win32_PerfFormattedData_PerfOS_Processor where name = '_Total'" `
              -ComputerName $ComputerNames | 
              sort PercentProcessorTime -descending | 
              ft -Property PSComputerName, name, PercentProcessorTime -autosize








Powershell One Liners: Get status of SQL Instances on All SQL Servers

Powershell One Liners: Get status of SQL Instances on All SQL Servers
To be more precise, I am using the WMI to check the status of the SQL Server services i.e. the windows service for the database instances. I am not though testing whether a successful connection could be made to the SQL instances.

If you have a single server that you want to check status of the SQL Server database services:

$ComputerName = "Server1"
Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -ComputerName $ComputerName | ft -Property PSComputerName, @{Name = "ServiceName"; Expression = 'Name'}, PathName, ExitCode, ProcessID, StartMode, State, Status







# If you have list of servers in servers.txt, each sever name in its own line

$ComputerNames = get-content servers.txt
$ComputerNames = $ComputerNames | sort -Unique | Where-Object {$_ -ne ""}

Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -ComputerName $ComputerNames | ft -Property PSComputerName, @{Name = "ServiceName"; Expression = 'Name'}, PathName, ExitCode, ProcessID, StartMode, State, Status








# To export the output into an excel/csv, just add "export-csv <filename.csv>" at the end..

Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -ComputerName $ComputerNames | ft -Property PSComputerName, @{Name = "ServiceName"; Expression = 'Name'}, PathName, ExitCode, ProcessID, StartMode, State, Status | export-csv SQLInstancesStatus.csv


Sunday, October 7, 2018

SQL Server monitoring with built-in features

SQL Server monitoring with built-in features

Do you think it's imperative to use commercial tools like SQL Diagnostic Manager, SQL Sentry, Spotlight, SolarWind etc.? Or are the built-in features sufficient? While I think there are compelling arguments for both sides, if you can afford it, I suggest go with a third party tool. There are also free of cost third party tools out there.

In this blog, I plan to explore every built-in feature used to gather performance information. Some provide real-time overviews of your databases, while others offer detailed, on-demand data on SQL Server health and performance. Broadly speaking, the methods discussed in this article falls into 3 categories 1) T-SQL based 2) SSMS based and 3) Windows Performance Monitor.

I hope you'll learn something new.

Using T-SQL

There are several ways to use T-SQL queries to monitor SQL Server, including dynamic management views, built-in functions, stored procedures, and system data collections.

Dynamic management views (DMVs)

DMVs are virtual tables that you can query on an ad-hoc basis or as part of your custom, automated monitoring. Some display the SQL Server state at a particular instant, while others, especially those dealing with performance counters, measure values at regular intervals and show you the difference between two samples.

sys.dm_os_performance_counters DMV is of particular interest to view every performance object and counter of interest.

For example, to view the buffer manager performance object values:

SELECT object_name,
       counter_name,
       cntr_value
FROM   sys.dm_os_performance_counters
WHERE  object_name = 'SQLServer:Buffer Manager'
       AND cntr_value != 0;

You’ll get a result similar to this:



My goal here is not to teach you about each DMV; instead, I aim to teach you how to teach yourself! You can find all such views in the system view sys.system_objects.


select top 10000 * from sys.system_objects where type_desc = 'view'  order by name;








For example, Filter the results on specific terms liKE hadr for the always on views.

select top 10000 * from sys.system_objects
where type_desc = 'view' 
and name like '%hadr%'
order by name;




HA Clusters:

select top 10000 * from sys.system_objects where type_desc = 'view' 
and name like '%cluster%'
order by name












To get a better understanding or each DMV, you should check out the specific documentation.

Built-in functions

Unlike dynamic management views, which return data in the form of virtual tables, built-in functions return system data as single numerical values, calculated since the server was last started. You can use each built-in function as an argument in a SELECT statement. For example, the built-in function @@connections returns the sum of successful and unsuccessful connections over time:

SELECT @@connections AS "Total Connections";
GO

Total Connections
-----------------
             121

Note: @@connections increments every time a user attempts to log in (even if the attempt is unsuccessful).

The only built-in system statistics function that doesn’t return a single numerical value is sys.fn_virtualfilestats.

System stored procedures

Most stored procedures assist with administrative tasks, such as attaching a database or adding a login. However, some report metrics, while others return stored metadata or configuration information.

EXEC sp_spaceused;






Management Data Warehouse

Simply put, the Management Data Warehouse (MDW) in SQL Server stores state and performance data collected from one or more SQL Servers. From this data, reports are generated, and you have the option to create your own custom reports.

This feature significantly aligns with the functionality of commercial monitoring tools and may eventually render paid tools completely redundant.

MDW utilizes SQL Server Integration Services to automate the querying of databases and the recording of results in the Management Data Warehouse.

To read more about it:



SQL Server Management Studio: Activity Monitor

The Activity Monitor makes it possible to view SQL Server metrics in real time. To use the Activity Monitor, type “Ctrl-Alt-A” or click the icon within the SSMS toolbar.






















To view execution plan for an expensive query, right click on the query and select Show Execution Plan:
















Note: You can’t adjust the sizes of the graphs or the metrics they show. Nor can you change the way the Activity Monitor calculates the statistics for query performance


SQL Server Management Studio: Dashboard Reports

There are about 20 standard/default dashboards reports in SSMS.




























You can create a custom report.  And if you are keen to develop more reports, check out the Power BI , SQL Server Reporting Services (SSRS) etc. that provides more features PDF-ready reports, reports for the mobile devices etc..


Windows Performance Monitor

Windows Performance Monitor helps you visualize system-level resource usage from your Windows hosts, and enables you to correlate these metrics with SQL Server performance counters in time series graphs.

You can launch it from the Administrative Tools menu group or run the following from the Run window:

%windir%\system32\perfmon.msc /s




















Once in there, click on the Performance Monitor on the left and it will automatically start showing the percentage processor time. Click on the + to add more performance counters, including SQL Server counters.

 















I believe the third party tools still have a place in enterprise database monitoring and tuning. They offer ease of setup and use—often right out of the box—and may be more valuable if you have already made significant investments in them.

Additionally, I think these tools are supplementary rather than complementary to each other, and you should consider using both.

Tuesday, September 18, 2018

Check if any database has auditing configured


Here is a DMV script to check whether a or any database has the auditing configured and running, along with few other useful attributes if it does.


Few things to keep in mind:

  • Auditing is available from SQL version 2008 and up....
  • Until very recently (SQL version 2016 and up), granual (object level) auditing at the database level is available only on Enterprise edition. 
  • Although you could still use server level auditing to setup auditing at the database level


So keeping that in mind, I wrote this in a way to be able to execute as a multi-server query either using CMS or just a bunch of locally registered servers.

If you are running this as a multi-server script, you should turn on the "Merge Results" setting in the SSMS.

Finally, if you feel that this script is unncessarily complicated, its because it is written in a way so that it can be executed against any version and editon of sql server, whether it supports auditing or not!

Hope you find this use and please feel free to customize it anyway you would like...



SET nocount ON

USE [master]

go


IF Object_id('tempdb..##t1_db_audit_specs') IS NOT NULL
  DROP TABLE ##t1_db_audit_specs

go

DECLARE @sql_version_number NVARCHAR(100)
DECLARE @edition NVARCHAR(100)
DECLARE @SQL NVARCHAR(2000)

create table ##t1_db_audit_specs
(
             comment nvarchar(4000),
             dbname nvarchar(256),
             audit__target_name  nvarchar(256),
             db_audit_name nvarchar(256),
             is_server_audit_enabled   BIT,
             is_db_audit_enabled   BIT,


             on_failure_desc    NVARCHAR(256),
             max_file_size      BIGINT,
             max_rollover_files INT,
             queue_delay        INT,
             log_file_path      NVARCHAR(520),
             log_file_name      NVARCHAR(520),

             create_date  datetime,
             modify_date  datetime,
             sql_version_number NVARCHAR(256),
             edition            NVARCHAR(100)

)


SET @sql_version_number = Cast(Serverproperty('ProductVersion') AS NVARCHAR(100))
SET @edition = Cast(Serverproperty('Edition') AS NVARCHAR(100))

SET @SQL =
'

if ''?'' not in  (''master'', ''model'', ''msdb'', ''tempdb'')
and CAST(DATABASEPROPERTYEX(''?'', ''status'') as varchar(50)) = ''ONLINE''
and CAST(DATABASEPROPERTYEX(''?'', ''Updateability'') as varchar(50)) IN (''READ_WRITE'', ''READ_ONLY'')
      INSERT INTO ##t1_db_audit_specs
      SELECT
                    null,
                    ''?'' dbname,
                sa.name audit_name,
                     das.name db_audit_name,
             sa.is_state_enabled,
             das.is_state_enabled,

             sa.on_failure_desc,
             sa.max_file_size,
             sa.queue_delay,
             sa.max_rollover_files,
             sa.log_file_path,
             sa.log_file_name,

             das.create_date,
             das.modify_date,
             ''' + @sql_version_number + ''' sql_version_number,
             ''' + @edition + ''' sql_edition

      FROM   [?].sys.database_audit_specifications das
         inner join sys.server_file_audits sa on sa.audit_id = das.database_specification_id
  '
print @sql

IF LEFT(@sql_version_number, Charindex('.', @sql_version_number) - 1) < 10              -- SQL version check
               INSERT INTO ##t1_db_audit_specs (sql_version_number, edition, comment)
               VALUES     (@sql_version_number, @edition, '<<Auditing is not supported due to Older version>>')
ELSE
  BEGIN
  IF not exists (select * from sys.server_file_audits)                                  -- Is Auditing supported?
               INSERT INTO ##t1_db_audit_specs (sql_version_number, edition, comment)
               VALUES     (@sql_version_number, @edition, '<<There is no audit target is configured and therefore database audit could not have been created.>>')

  ELSE IF (CAST(SERVERPROPERTY('Edition') AS VARCHAR(100)) NOT LIKE 'Enterprise%')      -- Is database level auditing supported?
               INSERT INTO ##t1_db_audit_specs (sql_version_number, edition, comment)
               VALUES     (@sql_version_number, @edition, '<<Granular auditing is not available in non-Enterprise editions of SQL Server.>>')

  ELSE
                    EXEC master..sp_MSforeachdb @sql
  END

if not exists (SELECT * FROM   ##t1_db_audit_specs)
               INSERT INTO ##t1_db_audit_specs (sql_version_number, edition, comment)
               VALUES     (@sql_version_number, @edition, '<<No database has auditing configured.')

SELECT * FROM   ##t1_db_audit_specs

--
IF Object_id('tempdb..##t1_db_audit_specs') IS NOT NULL
  DROP TABLE ##t1_db_audit_specs


go