SQL 2014 Disk Read/Write Latency datasource

Microsoft.SQLServer.2014.DBEngine.DiskLatency.DataSource (DataSourceModuleType)

Disk Read/Write Latency datasource

Element properties:

TypeDataSourceModuleType
IsolationAny
AccessibilityInternal
RunAsDefault
OutputTypeSystem.PropertyBagData

Member Modules:

ID Module Type TypeId RunAs 
Scheduler DataSource System.Scheduler Default
PS ProbeAction Microsoft.Windows.PowerShellPropertyBagProbe Default

Overrideable Parameters:

IDParameterTypeSelectorDisplay NameDescription
IntervalSecondsint$Config/IntervalSeconds$Interval (seconds)The recurring interval of time in seconds in which to run the workflow.
TimeoutSecondsint$Config/TimeoutSeconds$Timeout (seconds)Specifies the time the workflow is allowed to run before being closed and marked as failed.
SyncTimestring$Config/SyncTime$Synchronization TimeThe synchronization time specified by using a 24-hour format. May be omitted.

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2014.DBEngine.DiskLatency.DataSource" Accessibility="Internal">
<Configuration>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="IntervalSeconds" type="xsd:integer"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="TimeoutSeconds" type="xsd:integer"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="SyncTime" type="xsd:string"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="ComputerName" type="xsd:string"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="SqlInstanceName" type="xsd:string"/>
</Configuration>
<OverrideableParameters>
<OverrideableParameter ID="IntervalSeconds" Selector="$Config/IntervalSeconds$" ParameterType="int"/>
<OverrideableParameter ID="TimeoutSeconds" Selector="$Config/TimeoutSeconds$" ParameterType="int"/>
<OverrideableParameter ID="SyncTime" Selector="$Config/SyncTime$" ParameterType="string"/>
</OverrideableParameters>
<ModuleImplementation>
<Composite>
<MemberModules>
<DataSource ID="Scheduler" TypeID="System!System.Scheduler">
<Scheduler>
<SimpleReccuringSchedule>
<Interval>$Config/IntervalSeconds$</Interval>
<SyncTime>$Config/SyncTime$</SyncTime>
</SimpleReccuringSchedule>
<ExcludeDates/>
</Scheduler>
</DataSource>
<ProbeAction ID="PS" TypeID="Windows!Microsoft.Windows.PowerShellPropertyBagProbe">
<ScriptName>DBDiskLatencyDataSource.ps1</ScriptName>
<ScriptBody><Script>#DBDiskLatencyDataSource.ps1
param($computerName, $sqlInstanceName, $connectionString, $tcpPort, $serviceName)

$SCRIPT_EVENT_ID = 4201
#debug
$DEBUG_MODE = 0
$DEBUG_MODULE = "DBDiskLatencyDataSource.ps1"
$DEBUG_MSG = ""
$DEBUG_SA = $null
$DEBUG_PWD = $null

#Event Severity values
$INFORMATION_EVENT_TYPE = 0
$ERROR_EVENT_TYPE = 1

function BuildConnectionString(
[String] $pHostName,
[String] $pInstanceName,
[String] $databaseName,
[String] $connectionString = $null,
[String] $user = $null,
[String] $password = $null)
{
if (($connectionString.Length -ne 0)) {
return $connectionString
} else {
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$serverName = ""
$namespace = "root\Microsoft\SqlServer\ComputerManagement12"
$class = "ServerNetworkProtocolProperty"
$serverName = $pHostName + "\" + $pInstanceName
$listenAll = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.PropertyName -eq "ListenOnAllIPs")}
if($listenAll.PropertyNumVal -eq 1) {
$tcpipAll = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {
($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq "IPAll")-and($_.PropertyName -eq "TcpPort")}
if($tcpipAll.PropertyStrVal -eq '') {
$tcpipAll = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {
($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq "IPAll")-and($_.PropertyName -eq "TcpDynamicPorts")}
}
if($tcpipAll.PropertyStrVal -ne '') {
$serverName = $serverName + "," + $tcpipAll.PropertyStrVal
}
}
else {
$ipAddressName = (Get-WmiObject -Namespace $namespace -Class $class | Where-Object {
($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -ne "")-and($_.PropertyName -eq "Enabled") -and ($_.PropertyNumVal -eq 1)}) | select -first 1 | select -ExpandProperty IPAddressName
if($ipAddressName -ne $null) {
$tcp = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {
($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq $ipAddressName)-and( ($_.PropertyName -eq "TcpPort") -or ($_.PropertyName -eq "TcpDynamicPorts")) -and ($_.PropertyStrVal -ne '')} | select -ExpandProperty PropertyStrVal
$ip = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {
($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq $ipAddressName)-and( $_.PropertyName -eq "IpAddress") -and ($_.PropertyStrVal -ne '')} | select -ExpandProperty PropertyStrVal
if($ip -ne $null) {
$serverName = $ip + "," + $tcp
}
else {
$serverName = $serverName + "," + $tcp
}
}
}

$builder["Data Source"] = $serverName
$builder["Initial Catalog"] = $databaseName

if (($user.Length -ne 0) -and ($password.Length -ne 0)) {
$builder["User ID"] = $user
$builder["Password"] = $password
}
else
{
$builder["Integrated Security"] = 'SSPI'
}

return $builder.ConnectionString
}
}

function SqlTestDestination($connection, $serverName, $instanceName) {
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$DataSet = New-Object System.Data.DataSet
$SqlCmd.CommandText = "select SERVERPROPERTY('MachineName') as ServerName, @@servicename as InstanceName"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)|out-null
$res = $DataSet.Tables | select -first 1
if ($res -ne $null) {
$queryServerName = ($res | Select -ExpandProperty ServerName).ToUpperInvariant()
$queryInstanceName = ($res | Select -ExpandProperty InstanceName).ToUpperInvariant()
$serverNameWithoutDomain = $serverName
$dotPosition = $serverName.IndexOf(".")
if ($dotPosition -gt -1) {
$serverNameWithoutDomain = $serverName.Substring(0, $dotPosition)
}
if (($serverNameWithoutDomain.ToUpperInvariant() -eq $queryServerName) -and ($instanceName.ToUpperInvariant() -eq $queryInstanceName)) {
return;
}
}
throw "Connection target check failed: connected to " + $serverName + "\\" + $instanceName + ", but got " + $queryServerName + "\\" + $queryInstanceName + "."
}

function BuildConnectionStringWithPort(
[String] $connectionString,
[int] $port,
[String] $user = $null,
[String] $password = $null) {
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
if ($port -eq 0){
$builder["Data Source"] = $connectionString
} else {
$builder["Data Source"] = $connectionString + "," + $port
}
if (($user.Length -ne 0) -and ($password.Length -ne 0)) {
$builder["User ID"] = $user
$builder["Password"] = $password
} else {
$builder["Integrated Security"] = 'SSPI'
}
return $builder.ConnectionString
}

function ValidateConnectionString(
[String] $connectionString,
[int] $port,
[String] $serverName,
[String] $instanceName,
[String] $user = $null,
[String] $password = $null)
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
try {
$SqlConnection.ConnectionString = BuildConnectionStringWithPort $connectionString 0 $user $password
$SqlConnection.Open()
SqlTestDestination $SqlConnection $serverName $instanceName
$SqlConnection.Close()
return $SqlConnection.ConnectionString
} catch {
try {
if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {
$SqlConnection.Close()
}
$SqlConnection.ConnectionString = BuildConnectionStringWithPort $connectionString $port $user $password
$SqlConnection.Open()
SqlTestDestination $SqlConnection $serverName $instanceName
$SqlConnection.Close()
return $SqlConnection.ConnectionString
} catch {
if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {
$SqlConnection.Close()
}
}
}
return $null
}

function SqlQueryRows($connectionString, $query) {

$res = "";

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
try
{
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$DataSet = New-Object System.Data.DataSet

$SqlConnection.ConnectionString = $connectionString

$SqlCmd.CommandText = $query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)|out-null

$res = ($DataSet.Tables[0])
}
finally
{
$SqlConnection.Close()
}
return $res

}

#-------------------------------------------------------------------------------
# localized performance counters
#-------------------------------------------------------------------------------

# Retrieve the default language identifier of the current user. For most languages,
# you use the primary language identifier only to retrieve the text. In Windows XP and
# Windows Server 2003, you use the complete language identifier to retrieve Chinese
# text. In Windows Vista, you use the complete language identifier to retrieve Portuguese text.
function Get-GetLanguageId([String[]] $osVersion)
{
$LANG_PORTUGUESE = 0x16
$LANG_CHINESE = 0x04

$cul = Get-UICulture
$lcid = $cul.LCID

$lcidPrimary = $($lcid -band 0xff)

if (
($LANG_PORTUGUESE -eq $lcidPrimary -and $osVersion[0] -gt 5) -or #Windows Vista and later
($LANG_CHINESE -eq $lcidPrimary -and ($osVersion[0] -eq 5 -and $osVersion[1] -ge 1)) #XP and Windows Server 2003
)
{
return $lcid
}

return $lcidPrimary
}

# Build a hash array of offsets into the counter buffer. Use the index
# values from the performance data queries to access the offsets.
function Get-RegLocalizedPerfCounterNames($computerName = $env:COMPUTERNAME)
{
$path = ""
#get OS version
$osvi = Get-WmiObject -class Win32_OperatingSystem -computerName $computerName
if(($osvi -eq $null) -or ($osvi.Version -eq $null)) {
return $null
}
$osVersion = $osvi.Version.split(".")
#get language ID
$language = "{0:X3}" -f $(Get-GetLanguageId($osVersion))
#if OS windows 7, 8, 2008R2 and 2012
if( $osVersion[0] -ge 6 -and $osVersion[1] -ge 1 )
{
$path = "hklm:SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\CurrentLanguage"
}
#if OS Windows XP, 2003, Vista, 2008
if( ($osVersion[0] -eq 6 -and $osVersion[1] -eq 0 ) -or ($osVersion[0] -eq 5 -and $osVersion[1] -ge 1 ) )
{
$path = "hklm:SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\{0}" -f $language
}
#if no data in regestry when return null
$val = Get-ItemProperty -Path $path -Name "Counter" -ErrorAction SilentlyContinue
if (($val -eq $null) -or ($val.Length -eq 0))
{
return $null
}
#return registry values
$hashArray = @{}
for($i=0; $i -lt $val.Counter.Count; $i=$i+2)
{
[UInt32] $key = [UInt32]$val.Counter[$i]
if ($hashArray.ContainsKey($key) -ne $true)
{
$hashArray.Add($key, $val.Counter[$i+1])
}
}
return $hashArray
}

#Returns the performance object name or counter name corresponding to the specified index.
Function Get-PdhLookupPerfNameByIndex([UInt32] $ID, $computerName = $env:COMPUTERNAME)
{
$code = '[DllImport("pdh.dll", SetLastError=true, CharSet=CharSet.Unicode)] public static extern UInt32 PdhLookupPerfNameByIndex(string szMachineName, uint dwNameIndex, System.Text.StringBuilder szNameBuffer, ref uint pcchNameBufferSize);'

$Buffer = New-Object System.Text.StringBuilder(1024)
[UInt32]$BufferSize = $Buffer.Capacity

$t = Add-Type -MemberDefinition $code -PassThru -Name PerfCounter -Namespace Utility
$rv = $t::PdhLookupPerfNameByIndex($computerName, $id, $Buffer, [Ref]$BufferSize)

if ($rv -eq 0)
{
return $Buffer.ToString().Substring(0, $BufferSize-1)
}
else
{
Throw 'Get-PdhLookupPerfNameByIndex : Unable to retrieve localized name for performance counter ID ({0}) on computer "{1}".' -f $ID, $ComputerName
}
}

function Get-LocalizedPerfCounter($arrayCounters, [String] $format, $paramters)
{
$cul = Get-UICulture
#English language
if( $($cul.LCID -band 0xff) -ne 0x09)
{
#try to get locolized values
if ( $arrayCounters -ne $null)
{
#from regestry
for($i=0; $i -lt $paramters.Count; $i++)
{
[UInt32] $id = [UInt32]$paramters[$i][0]
if ($arrayCounters.ContainsKey($id) -eq $true)
{
$paramters[$i][1] = $arrayCounters[$id]
}
else
{
Throw 'Get-PerfCounter : Unable to retrieve localized name. (Index : {0} Name : {1})' -f $id,$paramters[$i][1]
}
}
}
else
{
#win32 api
for($i=0; $i -lt $paramters.Count; $i++)
{
[UInt32] $id = [UInt32]$paramters[$i][0]
$paramters[$i][1] = Get-PdhLookupPerfNameByIndex $id
}
}
}
#get counter
$counter = $format -f $paramters[0][1], $paramters[1][1]
return (Get-Counter ($counter))
}
#-------------------------------------------------------------------------------


function GetMountPoints($ComputerName) {
$separator = [System.IO.Path]::DirectorySeparatorChar
$extractTextInQuotes = "(?" + [char]60 + "=\"").*?(?=\"")"

$mountPoints = @(,$null)
$rawMountainPoints = Get-WmiObject Win32_MountPoint -ComputerName $ComputerName


$perfCounterNames = Get-RegLocalizedPerfCounterNames

$writeCounters = (Get-LocalizedPerfCounter $perfCounterNames $("\{0}(*)\{1}") $(@( 236 , "LogicalDisk" ), @( 210 , "Avg. Disk sec/Write" ))).CounterSamples
$readCounters = (Get-LocalizedPerfCounter $perfCounterNames $("\{0}(*)\{1}") $(@( 236 , "LogicalDisk" ), @( 208 , "Avg. Disk sec/Read" ))).CounterSamples


$rawMountainPoints | foreach {
if ($_.Directory -match $extractTextInQuotes) {

$directory = $matches[0].Replace("\\", "\")

if ($_.volume -match $extractTextInQuotes) {
$volume = $matches[0].Replace("\\", "\")

$diskInstanceName = $directory
if ($diskInstanceName.EndsWith($separator) ) {
$diskInstanceName = $directory.Substring(0, $directory.Length - 1)
}

$writeCounter = $writeCounters | where { $_.InstanceName -eq $diskInstanceName }
$readCounter = $readCounters | where { $_.InstanceName -eq $diskInstanceName }

if ($writeCounter -ne $null -and $readCounter -ne $null) {
$name = $volume + $directory
$mountPoints += , @( $directory, $volume, $name, [double]$readCounter.CookedValue, [double]$writeCounter.CookedValue )
}
}
}
}
return $mountPoints
}

function MatchMountPoint($mountPoints, $fileName) {

return $mountPoints | where {($_ -ne $null) -and ($fileName.StartsWith($_[0], [System.StringComparison]::InvariantCultureIgnoreCase)) } | select -First 1
}

#The function returns service or "Unknown" state
#Input:
# server - compute name
# service - system service name
# InstanceName - sql server instance name
#Output:
# service state or "Unknown" state
function GetServiceState($server, $service, $InstanceName)
{
try {
if ($service -eq "MSSQL") {
$service = "MSSQL`${0}" -f $InstanceName
}
$namespace = "root/cimv2"
$obje = Get-WmiObject -Namespace $namespace -ComputerName $server -Class "win32_service" -ErrorAction SilentlyContinue | where {$_.name -like $service }
if ($obje -ne $null) {
return $obje.State
}
}
catch {
}
return "Unknown"
}

function main($ComputerName, $InstanceName, $connectionString, $tcpPort, $serviceName) {

$msg = [Environment]::NewLine
$err = [Environment]::NewLine

#
# Prepare MOM API
#
$api = New-Object -comObject "MOM.ScriptAPI"

#if service is not in running state when exit without any error
$state = GetServiceState $computerName $serviceName $sqlInstanceName
if(($state -ne "Running") -and ($state -ne "Unknown"))
{
return
}

$sqlConnectionString = $connectionString
[int]$sqlTcpPort = $tcpPort

$sqlConnectionString = ValidateConnectionString $sqlConnectionString $sqlTcpPort $ComputerName $InstanceName $DEBUG_SA $DEBUG_PWD

try {
$query = "select sys.databases.name, sys.databases.database_id,sys.master_files.physical_name from sys.databases
join sys.master_files on sys.databases.database_id = sys.master_files.database_id
where sys.databases.source_database_id is null"

$connectionString = BuildConnectionString $ComputerName $InstanceName 'master' $sqlConnectionString

$dbFiles = SqlQueryRows $connectionString $query
$mountPoints = GetMountPoints $ComputerName

$groupedDBFiles = $dbFiles | group name

if($groupedDBFiles -eq $null){
return
}

$groupedDBFiles | foreach {
$dbName = $_.name

[double]$maxRead = 0.0;
[double]$maxWrite = 0.0;

$maxReadFile = ""
$maxWriteFile = ""
$maxReadMountPoint = ""
$maxWriteMountPoint = ""

$is_mount = $false

$_.group | foreach {
$dbFileName = $_.physical_name
$mountPoint = MatchMountPoint $mountPoints $dbFileName

if ($mountPoint -ne $null) {

if ($mountPoint[3] -gt $maxRead) {
$maxRead = $mountPoint[3]
$maxReadFile = $dbFileName
}

if ($mountPoint[4] -gt $maxWrite) {
$maxWrite = $mountPoint[4]
$maxWriteFile = $dbFileName
}
$maxReadMountPoint = $mountPoint[0]
$maxWriteMountPoint = $mountPoint[0]
$is_mount = $true
} else {
$err += "Can't find mount point for DB '$dbName' file '$dbFileName'"
$err += [Environment]::NewLine
}
}

if ($is_mount -eq $true)
{
[double]$maxReadWrite = 0.0
[string]$maxReadWriteFile = ""
[string]$maxReadWriteMountPoint = ""

if ($maxRead -gt $maxWrite) {
$maxReadWrite = $maxRead
$maxReadWriteFile = $maxReadFile
$maxReadWriteMountPoint = $maxReadMountPoint
} else {
$maxReadWrite = $maxWrite
$maxReadWriteFile = $maxWriteFile
$maxReadWriteMountPoint = $maxWriteMountPoint
}

$maxRead *= 1000
$maxWrite *= 1000
$maxReadWrite *= 1000

$bag = $api.CreatePropertyBag()

$bag.AddValue("Name", $dbName)
$bag.AddValue("MaxRead", $maxRead)
$bag.AddValue("MaxReadFile", $maxReadFile)
$bag.AddValue("MaxReadMountPoint", $maxReadMountPoint)

$bag.AddValue("MaxWrite", $maxWrite)
$bag.AddValue("MaxWriteFile", $maxWriteFile)
$bag.AddValue("MaxWriteMountPoint", $maxWriteMountPoint)

$bag.AddValue("MaxReadWrite", $maxReadWrite)
$bag.AddValue("MaxReadWriteFile", $maxReadWriteFile)
$bag.AddValue("MaxReadWriteMountPoint", $maxReadWriteMountPoint)

$msg += "DB=$dbName MaxRead=$maxRead MaxWrite=$maxWrite MaxReadFile=$maxReadFile MaxWriteFile=$maxWriteFile "
$msg += [Environment]::NewLine

$bag
}
}
}
catch {
$header = "Management Group: $Target/ManagementGroup/Name$. Script: {0} Module: {1}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE
$msg += "Error occurred during DB Disk Latency data source executing.{0}Computer:{1} {0}Reason: {2} {0}Position:{3} {0}Offset:{4} {0}Instance:{5}" -f
[Environment]::NewLine, $ComputerName, $_.Exception.Message, $_.InvocationInfo.ScriptLineNumber, $_.InvocationInfo.OffsetInLine, $InstanceName
$api.LogScriptEvent($header, $SCRIPT_EVENT_ID, $ERROR_EVENT_TYPE, $msg + $err)
}
if($DEBUG_MODE -eq 1) {
$msg += "Debug occurred during DB Disk Latency data source executing.{0}Computer:{1} {0}Instance:{2} {0}Connection:{3} {0}Port:{4} {0}Service:{5} {0}{6}" -f
[Environment]::NewLine, $ComputerName, $InstanceName, $connectionString, $tcpPort, $serviceName, $err
$api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg)
}
}

main $computerName $sqlInstanceName $connectionString $tcpPort $serviceName</Script></ScriptBody>
<SnapIns/>
<Parameters>
<Parameter>
<Name>computerName</Name>
<Value>$Config/ComputerName$</Value>
</Parameter>
<Parameter>
<Name>sqlInstanceName</Name>
<Value>$Config/SqlInstanceName$</Value>
</Parameter>
<Parameter>
<Name>connectionString</Name>
<Value>$Target/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/ConnectionString$</Value>
</Parameter>
<Parameter>
<Name>tcpPort</Name>
<Value>$Target/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/TcpPort$</Value>
</Parameter>
<Parameter>
<Name>serviceName</Name>
<Value>$Target/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/ServiceName$</Value>
</Parameter>
</Parameters>
<TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>
<StrictErrorHandling>true</StrictErrorHandling>
</ProbeAction>
</MemberModules>
<Composition>
<Node ID="PS">
<Node ID="Scheduler"/>
</Node>
</Composition>
</Composite>
</ModuleImplementation>
<OutputType>System!System.PropertyBagData</OutputType>
</DataSourceModuleType>