Measuring IOPs on MS Windows – Day 01

Background

Wanted to cover a couple of options for measuring Storage IOPs on a MS Windows machine.

Outline

  1. SysGauge – System Monitors
  2. MS Windows Performance Monitor
    • Disk IO
      • Logical Disk
    • SQL Server ( v2014+ )

Choices

SysGuage

Download SysGuage from here.

Install it on targeted machine.

Run it while sufficient load is running.

Images

MS Windows

Performance Monitor

Counter :- Disk IO

Counter :- Disk IO \ Logical Disk
Image

Explanation
  1. You want to look under LogicalDisk
    • Disk Reads/sec
    • Disk Writes/sec
    • Disk Transfers/sec
      • Disk Reads/sec + Disk Writes/sec

Counter :- SQL Server

Object Name :- SQL Server \ Resource Pool Stats

Please keep in  mind that Resource Pool Stats is only available as of SQL Server v2014.

When not running default MS SQL Server Instance, please look for the named instance complement.

Image

Explanation
  1. SQL Server
    • Object Name :- Resource Pool Stats
      • Counter Name
        • Disk Read IO/sec
        • Disk Write IO/sec

Windows Update – Patching SQL Server 2005 Express

Background

This is the second in a series of posts in which I will try to journal some of the steps we took to address a busy hard drive.

In the previous post we spoke about how we configured svchost.exe to run on separate processes and thus we are better positioned to identify which specific services are driving I/O.

Inclusive in the services identified are Windows Management and Windows Updates.

 

Windows Update

Once we saw that Windows Update was one of the main drivers we tried to see which updates was causing the problem.

To launch the Automatic Updates we clicked on the icon on the right bottom panel.

 

Microsoft SQL Server 2005 Express Edition Service Pack 4 ( KB2463322 ) AND Microsoft SQL Server 2005 Express Edition Toolkit Service Pack 4 ( KB2463322 )

Screen Shot

Outline

The screens that we encountered are:

  1. How do you want to install updates
  2. Choose Updates to install
  3. The updates are being installed
  4. Some updates could not be installed

How do you want to install the updates

Image
Initial Screen

HowDoYouWantToInstallUpdates_20170319_0230AM

 

Post Selection Screen

HowDoYouWantToInstallUpdates_20170319_0231AM

Textual

We chose “Custom Install (Advanced )” to gain better insight as to what is being installed are have more control as to which ones we can opt out of.

Choose Updates to install

Image

ChooseUpdatesToInstall_20170318_0232AM

 

Textual

Here are the updates

  1. Microsoft SQL Server 2005 Express Edition Service Pack 4 (KB2463332)
  2. Microsoft SQL Server 2005 Express Edition Toolkit Service Pack 4 (KB2463332)

 

The updates are being installed

Image

TheUpdatesAreBeingInstalled_20170319_0235AM

 

Textual

Here are the first of two updates are being applied

 

The updates are being installed ( Post First Patch )

Image

TheUpdatesAreBeingInstalled_20170319_0237AM

 

Textual

Unfortunately, the first patch failed.

And, the second patch is now being applied.

 

Some updates could not be installed.

Image

SomeUpdatesCouldNotBeInstalled_20170319_0238AM

 

Textual

Here both updates failed.

 

TroubleShooting

The troubleshooting steps available to us are the following:

  1. Check Event Viewer
  2. Check Windows Update log files
    • c:\windows\WindowsUpdate.log

 

c:\windows\WindowsUpdate.log

Here are some sample entries from c:\windows\windowsUpdate.log

Entries – 2017-03-19 3:54

 

Follow Up

Opted Out

Opted out SP4

ScreenShot

Choose updates to install
Image

ChooseUpdatesToInstall

 

Textual

Unchecked both packages.

 

Hide Updates
Image

DontNotofyNeAboutTheseUpdatesAgain

 

Textual

Here we are opting out of this release not just for now, but for always.

 

Summary

A number of years ago all the cool kids wore a tee shirt that read “Got tired of patch Tuesday, went Googling”.

It is not that bad for us, we will just go the manual install path.

svchost – High IO on MS Windows 2003

 

What is svchost.exe?

Wikipedia
Link

svchost.exe (Service Host, or SvcHost) is a system process that hosts multiple Windows services in the Windows NT family of operating systems.
Svchost is essential in the implementation of so-called shared service processes, where a number of services can share a process in order to reduce resource consumption

 

Issue

Hard-drive stays busy.

Indicator

Task Manager

Image

Explanation

  1. Top IO Usage
    • svchost.exe
      • Process ID is 920
      • User name is System

SysInternals

Explanation

  1. Top IO Usage
    • svchost.exe
      • Process ID is 920
      • User name is System

TroubleShooting

Process Management

Tasklist

List all services running under svchost.exe

Tasklist – List all processes running under svchost.exe

Script

tasklist /svc /fi "imagename eq svchost.exe"

Output

Explanation
  1. We are focused on PID = 920

Process Explorer

Tasklist – Dig deeper into process svchost.exe = 920

Services

Own Process

In a nice Server Fault QA post, Peter Mortensen suggested that one could separate out the services into their own process and thus gain clearer understanding of each service resource uptake.

To do one will have to change the service configuration.

Here is the specific QA:

How to find memory usage of individual Windows services?
Link

Run as distinct Process

Syntax

SC Config Servicename Type= own

Run as shared Process

Syntax

SC Config Servicename Type= share;

Run as distinct Process

Sample Code

rem  1. "Automatic Updates"
SC Config wuauserv Type= own

rem  2. "COM+ Event System"
SC Config EventSystem Type= own

rem  3. "Computer Browser"
SC Config Browser Type= own

rem  4. "Cryptographic Services"
SC Config CryptSvc Type= own

rem  5. "Distributed Link Tracking"
SC Config TrkWks Type= own

rem  6. "Help and Support"
SC Config helpsvc Type= own

rem  7. "Logical Disk Manager"
SC Config dmserver Type= own

rem  8. "Network Connections"
SC Config Netman Type= own

rem  9. "Network Location Awareness"
SC Config NLA Type= own

rem 10. "Remote Access Connection Manager"
SC Config RasMan Type= own

rem 11. "Secondary Logon"
SC Config seclogon Type= own

rem 12. "Server"
SC Config lanmanserver Type= own

rem 13. "Shell Hardware Detection"
SC Config ShellHWDetection Type= own

rem 14. "System Event Notification"
SC Config SENS Type= own

rem 15. "System Restore Service"
SC Config srservice Type= own

rem 16. "Task Scheduler"
SC Config Schedule Type= own

rem 17. "Telephony"
SC Config TapiSrv Type= own

rem 18. "Terminal Services"
SC Config TermService Type= own

rem 19. "Themes"
SC Config Themes Type= own

rem 20. "Windows Audio"
SC Config AudioSrv Type= own

rem 21. "Windows Firewall/Internet Connection Sharing (ICS)"
SC Config SharedAccess Type= own

rem 22. "Windows Management Instrumentation"
SC Config winmgmt Type= own

rem 23. "Wireless Configuration"
SC Config WZCSVC Type= own

rem 24. "Workstation"
SC Config lanmanworkstation Type= own

rem End.

 

Remediation

Once we ran the code to start all the aforementioned svchost.exe services in their own process space, restarted the machine.

SysInternals – Process Explorer

Took the SysInternal’s Process Explorer, arranged based on IO, and noticed that WMI is the culprit.

Images

svchost.exe – Services

Here are the services that are using our cited svchost.exe process.

Services

Took to Control Panel, services applet to stop that service and see if it things slow down.

Dependent Services

Reviewed Dependent Services

And, I really will rather than not stop the local system firewall service.  And, start to wonder why so busy anyways.

But, all that will wait another post as it is Saturday and I have errands to run.

Dedicated

Dedicated to Peter as in Mortensen.

 

References

  1. How to find memory usage of individual Windows services?
    Link
  2. How do I discover which process is making my hard drive go crazy? (need disk io equivalent of task manager’s cpu % column)
    Link
  3. YongRhee ( MSFT )
    • How to troubleshoot Service Host (svchost.exe) related problems?
      Link

Are you running on SSD – Dell – Latitude E7270 ?

Background

Recently we ran into a performance issue and wanted to compare our in-house environment against the Vendor’s Original implementation environment.

 

Is it Storage?

From what we observed our query was eating up a lot of tempdb storage and so quickly zeroed in how tempdb is configured.

Configuration of Tempdb

  1. Does the number of tempdb files match the number of processor cores
  2. Are the tempdb files sized equally and do they have the same growth pattern
  3. Is Instant File Initialization ( IFI ) enabled for the service account that SQL is running under

 

Hardware Itself

The other concern is whether our hardware has the neccesary IOPS

What type of Storage

And, so bring ups the question what type of storage do we have?

  1. Is it Local Disk
    • Mechanic rotating disk
    • SSD
  2. Is it Network Storage
    • Are we running SAN
    • Do we have sufficient memory ( PAM )

 

Self

Wanted to document the steps that needs to be taken by the vendor to document their storage.

And, so tried to try things out on my laptop as I prepared those steps.

 

Device Manager

Ran “Device Manager

Image

devicemanager_20170302_0923am

Textual

NVMe CX2-8B256-Q11 NV SCSI Disk Device

 

Explanation

The Device name “NVMe CX2-8B256-Q11 NV SCSI Disk Device” does not tell me whether it is an SSD Drive or not.

 

Google

Let us google on the device name and we found matches

Image

google-nvme-cx2-8b256-q11-nv

Textual

  1. CX2 NVMe Series – Liteon SSD
    Link
  2. LITEONIT Solid State Drive Firmware Driver Details | Dell US
    Link

 

Explanation

From Google we can see that the top matches suggests that we have an SSD Drive.

 

Dell

The computer is a Dell, let us go to it’s support site and see if it helps us identify the Storage type.

Dell Support is available here.

Steps

  1. Launched Internet Explorer
  2. Enter the URL for Dell Support
  3. On Dell’s Support Site
    • Installed Dell System Detect
    • Dell’s tool detected that our system is Latitude E7270
    • Once our computer is detected access the “System configuration” Tab
    • The Part number is “SSDR, 256G, P34, 80S3, SAMSUNG, PM951

Image

Do you want to install this application?

applicationinstall

Accept Terms and Conditions

dellsystemdetect

Installation

dellsystemdetect-accepttermsandconditions

Support for Latitude E7270

supportforlatitudee7270

 

Part Number

solidstatedrive

 

Summary

From Dell’s support site, we detected that our storage is :

SSDR, 256G, P34, 80S3, SAMSUNG, PM951

Vendor :- Samsung
Size :- 256 GB

Windows – What is in your System Drive – C:\WINDOWS\SoftwareDistribution\Download Folder?

Background

Trying to see what is taking up space on an OS Drive.

Tools to Use

OS Drive View

SpaceSniffer

BTW, SpaceSniffer is available here.

Here is what SpaceSniffer came up with.

spacesniffer-brushedup

 

Which Applications is using our big folders?

One of the folders that is big is C:\WINDOWS\SoftwareDistribution\Download

As the C:\WINDOWS\SoftwareDistribution\Download folder is in use by many applications, let us see which Application is using the most storage.

 

Explorer View

In Explorer, select the Title Bar, and add the following columns:

  1. Company
  2. Description

 

Choose Details

choosedetails

 

Files

Here are the files ordered by Size in Descending Order:

mswindowsexplorer

Explanation:

  1. 1258935150ae2e1692ddee617d6fd408259eff42
    • 120 MB
    • Microsoft Antimalware Signature Package Redirector
  2. From Aug-11th to Aug-18
    • 19 MB
    • AntiMalware Definition Update

 

AntiMalware Definition Update

multiplefiles19mbeach

 

PowerShell

Script


<# .PARAMETER folder The name of the folder .PARAMETER maxFiles The maximum # of files #>
param
(
	 [Parameter(Mandatory=$true)]
	 [string]$folder="C:\WINDOWS\SoftwareDistribution\Download"
	
	,[Parameter(Mandatory=$false)]
	 [int]$maxFiles=15
	 
)
	
Set-StrictMode -Version 2

##
# start of script
##
#[System.Type]$Global:ConvertSize = $null;
#[String]$Global:ConvertSize = $null;

Function Convert-Size {
    <# .SYSNOPSIS Converts a size in bytes to its upper most value. .DESCRIPTION Converts a size in bytes to its upper most value. .PARAMETER Size The size in bytes to convert .NOTES Author: Boe Prox Date Created: 22AUG2012 .EXAMPLE Convert-Size -Size 568956 555 KB Description ----------- Converts the byte value 568956 to upper most value of 555 KB .EXAMPLE Get-ChildItem | ? {! $_.PSIsContainer} | Select -First 5 | Select Name, @{L='Size';E={$_ | Convert-Size}} Name Size ---- ---- Data1.cap 14.4 MB Data2.cap 12.5 MB Image.iso 5.72 GB Index.txt 23.9 KB SomeSite.lnk 1.52 KB SomeFile.ini 152 bytes Description ----------- Used with Get-ChildItem and custom formatting with Select-Object to list the uppermost size. #>
    [cmdletbinding()]
    Param (
        [parameter(ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
        [Alias("Length")]
        [int64]$Size
    )
    Begin 
	{
		if ($Global:ConvertSizeDefined -eq $false) 
		{
		
            Write-Verbose ("Creating signature from Win32API")
			
            $Signature =  @"
                 [DllImport("Shlwapi.dll", CharSet = CharSet.Auto)]
                 public static extern long StrFormatByteSize( long fileSize, System.Text.StringBuilder buffer, int bufferSize );
"@
			 
            $Global:ConvertSize = Add-Type -Name SizeConverter -MemberDefinition $Signature -PassThru
			
        }
		
        Write-Verbose ("Building buffer for string")
		
        $stringBuilder = New-Object Text.StringBuilder 1024
		
    }
	
    Process 
	{
        Write-Verbose ("Converting {0} to upper most size" -f $Size)
        $ConvertSize::StrFormatByteSize( $Size, $stringBuilder, $stringBuilder.Capacity ) | Out-Null
        $stringBuilder.ToString()
    }
	
}

if(-not($folder)) 
{ 
	Throw “You must supply a value for -folder” 
}

[bool]$Global:ConvertSizeDefined = $false;
$MaxFilesDefaut = 15

if(-not($maxFiles)) 
{
	$maxFiles = $MaxFilesDefaut
}


#Get Files in Folders Ordered By Size
$files = Get-ChildItem $folder | Sort-Object length  -Descending

#Get # of Folders & Files
$iFileCount = $files.Count

$iFileID = 0;
$iFilesProcessed=0

#Iterate Folders and Files
for ($iFileID=0; $iFileID -lt $iFileCount; $iFileID++) `
{

	$objFile = $files[$iFileID]
	$filename = $objFile.Name.Trim()	
	$filenameFull = $objFile.FullName
	$strFileSize= "";

	# Get File Item
	$objFileItem = Get-Item $filenameFull
	
	# Get File Type
	$objFileType = $objFileItem.GetType()
	
	# Make sure is File and not a Folder
	if ($objFileItem -is [system.io.fileinfo])
	{

		# Increment File Processed Counter
		$iFilesProcessed = $iFilesProcessed + 1;
		
		#Get File Size
		$iFileSize = $objFileItem.length
		
		#Covert File Size to Human Readable Fashion
		$strFileSize = Convert-Size($iFileSize)
		
		
		#Get File Info
		$info= [system.Diagnostics.FileVersionInfo]::GetVersionInfo($filenameFull)
		
		# If File Info is good
		if ($info)
		{
			"File ID			 : {0}" -f $iFilesProcessed 
			"File CompanyName 	 : {0}" -f  $info.CompanyName
			"File Name	         : {0,-40}" -f $filename.Trim()
			"File Description 	 : {0}" -f  $info.filedescription
			"File Size     		 : {0}" -f  $strFileSize.Trim()
			Write-Host ""
		}

		# Break if Max Files 
		if ($iFilesProcessed -ge $maxFiles) 
		{ 
			break; 
		}
	
	} # if ($objFileItem is [system.io.fileinfo])
	

	
}	
	 
	 

 

Sample Invocation


powershell ./Get-FilesInfo.ps1  -folder C:\WINDOWS\SoftwareDistribution\Download -maxFiles 15

Output

getfilesinfo

 

 

Source Code Version Control

Git Hub

Files

 

SQL Server – Storage – IO Pattern – Seeks versus Scans

Background

One of the areas that needs to be considered when preparing IOPs requirements, is the mix of Seeks versus Scans.

Let us see how we compare amount of seeks versus scans on an existing system.

 

Referenced Blogs

Here are the blogs that pre-meditated this post.

  1. SQL Server – Storage – IO Pattern – Seeks versus Scans
    https://danieladeniji.wordpress.com/2016/05/03/sql-server-storage-io-pattern-seeks-versus-scans/

Code

Guide

We query  the sys.dm_db_index_usage_stats dmv for index usage statistics.

The table exposes seeks and scans for each index.

By aggregating the data we can get an index of how much seeks and scans we are experiencing.

Code

set nocount on;

declare @tblDatabaseSkip TABLE
(
	  [name] sysname
	, [databaseID]
		as isNull
				(
					  db_id([name])
					, -1
				)
)


insert into @tblDatabaseSkip
([name])
select 'DBBackup'
union
select 'DBUtility'
union
select 'csSchoolContent'
union
select 'csLogins'
union
select 'nettraffic_db'
union
select 'repository'
union
select 'TestLogShipping'
union
select 'TSRDestribution'



 ; with cteObject
 (
	  [database_id]
	, [object]
	, [seek]
	, [scan]
 )
 as
 (

	 select 

			  [database_id]

			, [object]
				= object_schema_name
					(
						tblIUS.object_id
					)
					+ '.'
					+ object_name
					(
						tblIUS.object_id
					)

			, [random]
				= (
						   tblIUS.user_seeks
					--	+  tblIUS.user_lookups
				  )	

			, [sequential]
				= tblIUS.user_scans


	 from   sys.dm_db_index_usage_stats tblIUS

	 where  tblIUS.database_id not in
				(
					select [databaseID] from @tblDatabaseSkip
				)

)
select 
		  [database]
			= db_name([database_id])

	    , [seek] 
			= sum([seek])

		, [scan] 
			= sum([scan])

		, [%seek] 
			= sum([seek]) * 100
				/ sum
					(
						[seek] + [scan]
					)
		, [%scan] 
			= sum([scan]) * 100
				/ sum
					(
						[seek] + [scan]
					)

from   cteObject

group by
		[database_id]

order by
		(
			sum([seek])
		  + sum([scan])
		) desc


 

Output

SeeksVersusScans-20160503-0305PM

 

Explanation

  1. We can see that when we add user_seeks to user_lookups ( bookmark lookups )  and compare the summed total to user_scans, the seeks percentiles are high
    • We likely have high Index Utilizations, and very little Table Scans

 

SQL Server 2008 R2 – Best Practice Analyzer – StorPort Driver – Out of Date

Background

Troubleshooting performance issues on a MS SQL Server 2008/R2 instance running on a rebuilt MS Windows 2003 box.

And, thought of running Best Practice Analyzer against the instance.

Best Practice Analyzer

Installation

Installed the tool.

Identified Errors and Warnings

Storport driver fix from KBA 940467 missing

Textual


Category: Configuration

Source: localhost

Issue: The storport driver present on this system is below the recommended version 5.2.3790.4133

Impact: Using the existing version of the storport driver can lead to various server instability and integrity issues affecting SQL Server operations

Resolution: For compatibility information, see KB 940467 - http://support.microsoft.com/default.aspx?scid=kb;EN-US;940467 and then, install the new version of the driver

Visual

StorportDriverFixFromKBA940467Missing

Hotfix

Here is a short list of storport hotfixes released in 2007:

  1. Hotfix kb/945119
    • Stop error that is related to the Storport.sys driver on a Windows Server 2003-based computer: “0x000000D1 (parameter1, parameter2, parameter3, parameter4) DRIVER_IRQL_NOT_LESS_OR_EQUAL”
      Link
      Date :- Nov 14, 2007
      Version :-

      • Service Pack 1 – 5.2.3790.3044
      • Service Pack 2 – 5.2.3790.4189
  2. Hotfix Kb/940467
    • MMC stops responding, or you cannot access VDS-dependent tools after you install the Storport storage driver from Microsoft Knowledge Base article 932755 in Windows Server 2003
    • Link
    • Date :- Aug 16, 2007
    • Version :-
      • Service Pack 1 – 5.2.3790.2992
      • Service Pack 2 – 5.2.3790.2992
  3. Hotfix Kb/ 932755
    • An updated Storport storage driver is available for Windows Server 2003
    • Link
    • Date :- Feb 13, 2007
    • Version :-
      • Service Pack 1 – 5.2.3790.2880
      • Service Pack 2 – 5.2.3790.4021

Quick Explanation

  1. KB932755 was released in Feb 2007
  2. It caused some problems and so in Aug 2007, KB 940467 was released to fix those problems
  3. In Nov 2007, KB945119 was released

HotFix Install

Request Hotfix

Visit the listed Microsoft page, and request a download.

Microsoft will send you a link per the requested bitness (32, 64, Itanium) and Language.

Check your email for link and download the patches.

Install Hotfix

Install the downloaded hotfix.

Reboot

As this is heavily used file, you have to reboot your system

Validate Hotfix Install

Device Drivers

Normally, I will say access access “Control Panel\ System \ Device Drivers”, but as this a patch\hotfix, you will not be able to validate via “Device Drivers”.

File System

  1. Launch Windows Explorer
  2. Navigate to C:\Windows\System32\drivers
  3. Select storport.sys
  4. Access the Version tab
  5. In the list of items, choose “File Version”
  6. We have “5.2.3790.4189 ( srv03_sp2_qfe.071114-1205 )

storport

References

Microsoft

  1. Analyzing Storage Performance using the Windows Performance Analysis ToolKit (WPT)
    Link
  2. A hotfix is available that improves the logging capabilities of the Storport.sys driver to troubleshoot poor performance issues for the disk I/O in Windows Server 2008
    Link

3rd Party Vendor – Netapp

  1. List of required hotfixes for Windows Server
    Link

File System Filter Drivers

  1. Is there a quick way to see what File System Filter drivers are loaded on Windows
    Link

Installed Drivers

  1. Nirsort
    Link