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

 

MS Windows – Storage – Reading IOPS Using DiskSpd

Background

Wanted to capture the IOPs that our current Hardware is capable of offering us.

Googled for tools and found this nice post by Glen Berry.

Using Microsoft DiskSpd to Test Your Storage Subsystem
Posted by Glenn Berry on August 4, 2015
http://sqlperformance.com/2015/08/io-subsystem/diskspd-test-storage

Referenced Blogs

Here are the blogs that pre-meditated this post.

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

 

Tool

Microsoft DiskSpd

The tool he talks up is DiskSpd.

Let us see if it is easy to use and addresses our need.

Tool Options

Option Meaning SQL Server Used
-b Block size of the I/O, specified as (K/M/G) 8 KB is relevant for SQL Server Data file -b 8k
-d Test duration in seconds N/A -d 300
-o Outstanding I/Os (meaning queue depth) per target, per worker thread N/A -o 4
-t Number of threads Number of threads -t 8
-h Disable software caching at the operating system level and hardware write caching
  • SQL Server uses its reads caching and so disabling is good
  • On capable hardware it is OK to enable caching
  • Remember for hardware this is only a request and Hardware will respond as it sees fit
-h
 -r  Options are -r for random, s for Sequential, and si -r for random  -r
 -w  % of Writes  25% writes is viable for DB  -w25
 -L  captures latency information  Latency is important for DB and so please capture & review -L
 -c  Size of workload file  Size of the workload file  -c 50 GB

 

 

 

Code

runtestOnDrive_Base.cmd

@ECHO ON

set "_dataFileFolder=%driveLetter%:\Microsoft\SQLServer\Diskspd\datafile"

set _logApp="D:\Program Files (x86)\GnuWin32\bin\tee.exe"

set "_APPFLD=D:\Downloads\Microsoft\Diskspd\v2.0.15\Extract\Diskspd-v2.0.15\amd64fre\"
set "_APPPRG=diskspd.exe"
set "_APPFULL=%_APPFLD%\%_APPPRG%"

REM http://snipplr.com/view/21573/print-datetime-in-dos-batch-file/
set _LOGFILE_DATE=%DATE:~10,4%_%DATE:~4,2%_%DATE:~7,2%
set _LOGFILE_TIME=%TIME:~0,2%_%TIME:~3,2%_%TIME:~6,2%
set _LOGFILE=log-%_LOGFILE_DATE%-%_LOGFILE_TIME%_OnDrive%driveLetter%.log
 
set _currentFolder=%cd%
set _logFolder=%_currentFolder%\log
set _logFileFull="%_logFolder%\%_LOGFILE%"

 
if not exist %_logFolder%   (
    mkdir %_logFolder%
)

if not exist %_dataFileFolder%   (
    mkdir %_dataFileFolder%
)

set "dataFile=emptyfile.txt"
set _datafilename=%_dataFileFolder%\%dataFile%

set "option=-b8K -d300 -o4 -t8 -h -r -w25 -L -c50G"

if exist %_datafilename% del %_datafilename%

%_APPFULL% %option% %_datafilename% | %_logApp% %_logFileFull%

if exist %_datafilename% del %_datafilename%

runtestagaintDriveF.cmd


@ECHO ON

set "driveLetter=F"

call runtestOnDrive_Base.cmd

Output

Images

Drive C:

DriveC

 

Drive D:

DriveD

 

Drive E:

DriveE

 

Drive F:

DriveF

 

Drive G:

DriveG

 

Tabulated

 

Product Version Total Reads Writes
 Drive C: 1217.39  914.86  302.53
 Drive D:  828.49  621.53  206.96
 Drive E:  1363.00  1022.90  340.10
 Drive F:  1278.83  959.72  319.10
 Drive G:  871.09  652.34  218.75

 

 

Explanation

  1. Depending on the drive, we get different numbers
  2. Read and Writes
    • We asked for mixed bag of reads and writes, specifically 25% writes
    • We attained about 30% writes

 

Follow-up

  1. Review the drives your OS Swap file sits on
  2. Review your disk configuration

 

Functionality

There is a wealth of functionalities that comes with this lithe product.

Stable

  1. Allows one to mix and match reads and writes during the same run
    • Option
      • -w
      • Represented in percentile of writes
    • Some of the competing products such as SQLIO does not offer this functionality
  2. Able to test both random and sequential workloads; albeit in different runs
    • Option
      • -s
        • Sequential
      • -si
        • More Sequential pattern
      • -r
        • Random

 

Stress

  1. Outstanding I/Os
    • Option
      • -o
        • The more this value is, the more I/Os you will be driving
  2. Thread Count
    • Option
      • -t
        • Concurrent Operations
  3. Disable both software caching and hardware write caching
    • Option
      • -h
        • Database engines perform their own caching and do not rely on the OS caching and so to properly replay SQL Server usage one should disable caching
        • Hardware

Validity

  1. Duration
    • Option
      • -d
        • The longer you run the test for the more reliable your numbers will be

 

Real Stress

Please be  careful before running this tool against your Production DB System.

We ran against our DR DB Server.

And, here are the metrics collected when we used Microsoft’s Resource Monitor to gauge overall system performance.

You want to zero in on System and diskspd Images.

High Reads

ResourceMonitor-20160504-0148PM

Concurrent Reads and Writes

Writes is 25% of Reads

ResourceMonitor-20160504-0158PM

 

Again, your system may get trashed and monitoring alarms might go off!

 

System Configuration

Anti-Virus

Make sure that the File’s Folder or Extension is excluded from your AV View.

McAfee McShield.exe

Here we are bumping against McAfee’s mcshield.exe

AntiVirus-20160504-0234PM

 

Other Tools

There are other tools that one can use to get IOPs readings.

This is obviously useful to validate what one is seeing.

MS Windows Performance

 

In Perfmon, read …

  1. PhysicalDisk
    • Disk Reads/sec
    • Disk Writes/sec
    • Disk Writes/sec

Perfmon

20160504-0617PM

DiskSPD

20160504-0617PM [Diskspd]

References

Tool

  1. Diskspd Utility: A Robust Storage Testing Tool (superseding SQLIO)
    https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223

 

  1. Paul Culmsee – More on SQL and SQLIO – Part 8 of the Demystifying SharePoint Performance Management series
    https://www.itunity.com/article/sql-sqlio-1144

 

Blogs about DiskSpd

  1. Glenn Berry – Using Microsoft DiskSpd to Test Your Storage Subsystem
    http://sqlperformance.com/2015/08/io-subsystem/diskspd-test-storage
  2. Jose Barreto’s Blog – DiskSpd, PowerShell and storage performance: measuring IOPs, throughput and latency for both local disks and SMB file shares
    https://blogs.technet.microsoft.com/josebda/2014/10/13/diskspd-powershell-and-storage-performance-measuring-iops-throughput-and-latency-for-both-local-disks-and-smb-file-shares/
  3. Argenis Fernandez – So Long, SQLIO. Hello, DiskSpd
    http://blog.purestorage.com/so-long-sqlio-hello-diskspd/

Storage White Papers

  1. Virtualizing SQL Server with VMware: Architecting for Performance: Storage
    By Michael Corey, Jeff Szastak, Michael Webster
    http://www.pearsonitcertification.com/articles/article.aspx?p=2240989&seqNum=4
  2. Suman Pinnamaneni – How to collect storage performance statistics using Windows Perfmon
    https://community.emc.com/docs/DOC-28792

 

Microsoft Sharepoint White Papers 

  1. Storage and SQL Server capacity planning and configuration (SharePoint Server 2013)
    https://technet.microsoft.com/en-us/library/cc298801.aspx

 

Centrino

Here are a couple of very informative postings by Anthony Nocentino,  an Enterprise Architect with Centino Systems.

  1. Load Testing Your Storage Subsystem with Diskspd
    http://www.centinosystems.com/blog/sql/load-testing-your-storage-subsystem-with-diskspd/
  2. Load Testing Your Storage Subsystem with Diskspd – Part II
    http://www.centinosystems.com/blog/sql/load-testing-your-storage-subsystem-with-diskspd-part-ii/
  3. Load Testing Your Storage Subsystem with Diskspd – Part III
    http://www.centinosystems.com/blog/sql/load-testing-your-storage-subsystem-with-diskspd-part-iii/

 

Disk Controllers

  1. Description of caching disk controllers in SQL Server
    https://support.microsoft.com/en-us/kb/86903

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”
    • https://support.microsoft.com/en-us/kb/945119
    • 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
    • https://support.microsoft.com/en-us/kb/940467
    • Date :- Aug 16, 2007
    • Version :-
      • Service Pack 1 – 5.2.3790.2992
      • Service Pack 2 – 5.2.3790.2992
  3. Hotfix Kb/ 932755

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

3rd Party Vendor – Netapp

File System Filter Drivers

Installed Drivers