Powershel Connection Database monitoring database status concurrent alert messages

function Get-databasedata {
[Cmdletbinding ()]
Param (
[String] $connectionString,
[String] $query,
[Switch] $isSQLServer
)
if ($isSQLServer) {
Write-verbose ' in SQL Server mode '
$connection = New-object-typename System.Data.SqlClient.SqlConnection
} else {
Write-verbose ' in OLE DB mode '
$connection = New-object-typename System.Data.OleDb.OleDbConnection
}
$connection. ConnectionString = $connectionString
$command = $connection. CreateCommand ()
$command. CommandText = $query
if ($isSQLServer) {
$adapter = New-object-typename System.Data.SqlClient.SqlDataAdapter $command
} else {
$adapter = New-object-typename System.Data.OleDb.OleDbDataAdapter $command
}
$dataset = New-object-typename System.Data.DataSet
$adapter. Fill ($dataset)
$dataset. Tables[0]

Note: Get-databasedata This function returns a table, we want to return a row of data for this table, we need to filter, for example $tab = (get-databasedata-verbose-connectionstring ' Server = Localhost;database = Master; User Id = Maxiangqian; Password = qsmc+12345 '-issqlserver-query "SELECT count (*) as a, as b from sys.databases;" | Select-object A, B) #这里是返回第一个表的两列, these two columns are an object
$tab [1].a
$tab [1].b
? #这里分别取两列的值

function Invoke-databasequery {
[Cmdletbinding ()]
Param (
[String] $connectionString,
[String] $query,
[Switch] $isSQLServer
)
if ($isSQLServer) {
Write-verbose ' in SQL Server mode '
$connection = New-object-typename System.Data.SqlClient.SqlConnection
} else {
Write-verbose ' in OLE DB mode '
$connection = New-object-typename System.Data.OleDb.OleDbConnection
}
$connection. ConnectionString = $connectionString
$command = $connection. CreateCommand ()
$command. CommandText = $query
$connection. Open ()
$command. ExecuteNonQuery ()
$connection. Close ()
}
# ' Server=localhost;database=test; Trusted_connection=true; '
get-databasedata-verbose-connectionstring ' Server = localhost; Database = master; User Id = test; Password = Test '-issqlserver-query ' SET TRANSACTION isolation level READ uncommitted;
SELECT TOP 20
CAST (qs.total_elapsed_time/1000000.0 as DECIMAL (28, 2))
As [total run time (s)]
, CAST (Qs.total_worker_time * 100.0/qs.total_elapsed_time
As DECIMAL (2)) as [% CPU]
, CAST ((qs.total_elapsed_time-qs.total_worker_time) * 100.0/
Qs.total_elapsed_time as DECIMAL (2)) as [% waiting]
, Qs.execution_count as total number of executions
, CAST (Qs.total_elapsed_time/1000000.0/qs.execution_count
As DECIMAL (2)) as [average run time]
, SUBSTRING (Qt.text, (QS.STATEMENT_START_OFFSET/2) + 1,
(case when qs.statement_end_offset =-1
Then LEN (CONVERT (NVARCHAR (MAX), Qt.text)) * 2
ELSE Qs.statement_end_offset
End-qs.statement_start_offset)/2) + 1) As [SQL statement]

, Db_name (qt.dbid) as database name

From Sys.dm_exec_query_stats QS
Cross APPLY Sys.dm_exec_sql_text (qs.sql_handle) as Qt
Cross APPLY sys.dm_exec_query_plan (qs.plan_handle) QP
WHERE qs.total_elapsed_time > 0
ORDER by Qs.total_elapsed_time DESC "| Select-object "Average run time", "% CPU", "% waiting", "total executions", "average elapsed time (s)", "SQL statement", "Database name" | Export-csv C:\test\ the longest execution time statement. csv–encoding "UTF8"


get-databasedata-verbose-connectionstring ' Server = localhost; Database = master; User Id = test; Password = Test '-issqlserver-query ' SET TRANSACTION isolation level READ uncommitted;
SELECT t4.name,t1. [statement],t1.object_id, T2.user_seeks, T2.user_scans,
T1.equality_columns, T1.inequality_columns,t1.included_columns
From sys.dm_db_missing_index_groups as T3
Join Sys.dm_db_missing_index_details as T1
On t1.index_handle = T3.index_handle
Join Sys.dm_db_missing_index_group_stats as T2
On t2.group_handle = T3.index_group_handle
Join sys.databases as T4
On t1.database_id = t4.database_id
where--t4.name in (' Qzycenter ', ' pbccenter ', ' poscenter ') and
User_seeks > 1000
ORDER BY t2.user_seeks desc "| Select-object-property Name,statement,object_id,user_seeks,user_scans,equality_columns,inequality_columns, Included_columns | Export-csv the missing index in the C:\test\ database. csv–encoding "UTF8"

function send-mail{
Param ([string[]] $toAddress,
[String] $body,
[String] $filename,
[string] $filename 1)
for ($i =0; $i-lt$toaddress.count; $i + +)
{
$message = New-object system.net.mail.mailmessage-argumentlist ' [email protected] ', $toAddress [$i], ' Database performance report ', $body
$attachment = New-object system.net.mail.attachment-argumentlist $filename, ' Application/octet '
$message. Attachments.Add ($attachment)
$attachment 1 = new-object system.net.mail.attachment-argumentlist $filename 1, ' Application/octet '
$message. Attachments.Add ($attachment 1)
$smtpServer = "Smtp.gmail.com"
$smtpUser = "[Email protected]"
$smtpPassword = "time.work.9818"
$SMTP = New-object system.net.mail.smtpclient-argumentlist $smtpServer
$smtp. Credentials = New-object system.net.networkcredential-argumentlist $smtpUser, $smtpPassword
$smtp. Enablessl= $true
$smtp. Send ($message)
}
}
Send-mail ' [email protected] ', ' [email protected] ' weekly send Qzycenter database run ' c:\test\ The missing index in the database. csv ' c:\test\ the longest time to execute sentence. csv '

Powershel Connection Database monitoring database status concurrent alert messages