Vision: SQL Homeostasis

# 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 Get - Counter   - Counter  "\Processor(_Total)\% Processor Time"  - SampleInterval 10  - MaxSamples 5  - 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

# Assuming you have list of servers in  servers . txt, each sever name in its own line $ComputerNames = get - content servers . txt 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 PS:  Or, to make this a true one-liner, embed the...

Do you think you need to use commercial tools  like SQL Diagnostic Manager, SQL Sentry, Spotlight etc.. ?  Or do you think the built-in features are enough? There are complelling arguments in favor of each. In this blog I hope to touch on every built-in feature to gather the performance information, some give  real-time overviews of your databases whereas others provide detailed, on-demand data on SQL Server health and performance. I hope you learn something new. Using T-SQL There are several ways to use T-SQL queries to monitor SQL Server, using dynamic management views, built-in functions, stored procedures, or system data collections… Dynamic management views DMVs are virtual tables that you can query on adhoc or as part of your custom, automated monitoring.   Some shows sql server state at a particular instant whereas others, especially those that deal with performance counters, measures values on a regular internal and show you the difference between two samples...