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.

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

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

Technical: Microsoft – Storage – Disk – Error Message – “The volume requires contiguous disk extents”

Technical: Microsoft – Storage – Disk – Error Message – “The volume requires contiguous disk extents” (Unresolved)

Another day, another problem.  Or more precisely, “Another day, another imprecise, unhelpful error”

Rumor has it that if you want to extend your drive\partition you can use the GUI or use the command line \ diskpart:



List disk

Select disk 0

List partition

Select partition 3

List Volume 0

Select Volume 0

extend size = <size> disk = <disk-to-steal-storage-from>

But, when I try to do same, I am getting errors (listed below):

  • The volume requires contiguos disk extents
  • The specified disks have less than 1 MB of free space available.  Please specify a disk with at least 1 MB available free space –> If the source disk is RAW or already formatted
  • The specified disks do not belong to the same pack
  • No extents were found for the plex

Btw, I also tried using “AOMEI Partition Assistant”, but got the error pasted below:

The lite edition can only work in the online state of networking.  Please detect your internet connection.

AOMEI Partition Assistance - Lite Edition

The AOEMI version we tried is available from:

References

Microsoft – SQL Server – Storage – Disk Alignment (via DiskPart Scripting)

Introduction:

As part of a Storage I/O review, discussions whether our disks are properly aligned came back up.

The specific Knowledge Base article that I was pointed to is :

How to diagnose misaligned I/O on Windows hosts
https://kb.netapp.com/support/index?page=content&id=1010803

It is a recently published NetApp article; as its publish date is 2013.02.27.

Though a short article, there is a lot in it.

There are two areas that I will like to cover in this posting; those areas are StartingOffset and Partition Style.

Starting Offset:

Depending on the Version of Windows (OS Version) , Partition Style, and your LUN size, your gold standard for “Starting offset” appears to vary a bit.

Here are the Numbers published by NetApp:

  • For Windows MBR, this number should be 32256, 31.5kb offset is used when the LUN is created with the Windows LUN type (31.5 * 1024 = 32256).
  • For Windows GPT, this number should be 65535 bytes for LUNs smaller than 4GB or 1048576 bytes for LUNs that are 4GB or larger.
  • For Windows 2008+, this number should be 65535 bytes for LUNs smaller than 4GB or 1048576 bytes for LUNs that are 4GB or larger

In summary:

  • Windows MBR, 32556
  • Windows GPT, disk size < 4 GB, then 65535 : else disk size >=  4GB, then 1048576

Detection:

There are a couple of procedures you can employ to determine your Starting Offset:

  • Windows Management Interface (WMI)

WMI

Syntax:


wmic partition get BlockSize, StartingOffset, Name

 

Interpretation:

OS version

Starting Offset

  • On the disk we are most interested in, Disk 4, our Starting Offset is 1048576.  That number matches up with NetApp guidance.

Partition Style:

Microsoft supports a couple of partition styles, MBR and GPT.

  • MBR — Legacy partition
  • GPT – new partition

There a couple of ways to determine your partition style:

  • GUI – Disk Management
  • DiskPart / list disk

Partition Style – Detect via Disk Management

Here are the steps:

  • Launch “Computer Management”
  • On the left panel, transverse to “Storage” \ “Disk Management”
  • The list of available disks are displayed on the right panel
  • On the right panel, select the disk you are interested in — make sure you select the physical disk, and not logical disk
  • Right click on your selected physical disk, and select “Properties” from the drop-down  menu
  • Notice that the name of the window that shows up will very based on whether this is a local disk, a SAN disk and the Disk Vendor
  • Proceed to the “Volumes” tab

Here is what shows up for us:

NetApp Device Properties

Partition Style – Via DiskPart / listdisk

Here are the steps:

  • Launch OS Shell
  • Start diskpart interactively (diskpart)
  • Issue “List Disk

DiskPart - List disk

 

To determine which disks are GPT, follow the GPT column.

If a disk has the asterisk symbol, then it is GPT. Else, it is not…

The two checks we performed conclusively affirm that our “Disk 4” is in fact MBR.  How could this be:

So went back and looked at our scripts:


select disk 4
create partition primary align=1024
assign letter=V
format fs=ntfs unit=64k label="Disk - Temp" quick

The script looks good:

 

Googled some more and found out what is wrong:

  • The Create partition syntax does not allow us to directly set the Partition Style

 

To correct your script for the future, have it resemble something along the likes of:


select disk 4
clean
convert gpt
offline disk
online disk
attribute disk clear readonly
create partition primary align = 1024
assign letter=V
format fs=ntfs unit=64k label="Disk - Temp" quick

Please show and demonstrate extraordinary care when preparing to issue the script above:

  • Make sure you have selected the right disk (Select disk 4)
  • Notice the use of “clean” — It destroys the disk
  • Convert gpt –> Converts the partition to gpt; the default is mbr
  • Notice the use of “attribute disk clear readonly”; It says to the disk remove the armor you place on formatted and in-use disk

Why GPT:

Performance:

Is there a performance penalty with choosing either MBR or GPT as your Partition Style. Googled for help, and there does not appear to be.

Please keep in mind that the partition style does not affect the way your data is written out, it has more to do with your partition table.

And, Microsoft will not let you get away with a wrong choice (ie MBR) for disks bigger than 2TB.

References

NetApp

GPT

References – GPT ( Processing)

References – Vendor

References – Vendor – NetApp:

Storage Professional Services and Guiding Angels

Introduction

Customer Testimonies are not easy to recant or write about.  It is either too ‘soapy’ or critical; a bit forced either way.

So let me try writing one without quite doing so.

A little over a year ago we were having big performance problems.  Everything was at a standstill. Nothing was moving either way.  Our databases were big, so big – About 5 to 6 TB.

And, so what to do, but query Microsoft SQL Server ‘wait states’.  What is causing the problem?  We settled on I/O bottlenecks.

So is it Write or Reads – Mostly reads, can’t recollect how many MB/sec,  but really quite big.

Met with a couple of gentlemen – Steve (Storage) and Chris & another engineer.

So they took time and explained the topology of our Storage Filers to me — Via Fiber Channel we are directly connected to the Filers.  No Storage Switches and so that couldn’t possible be the problem.  Almost, bare metals like you might say; none of these virtual stuff.

So I nodded with knowing head.  But, still did not know what to do to get better performance.

On our database side, we have an Active and a Passive database.  They are on two different machines and each machine is connected to its own Storage Filer.

So the more we thought, we said let us switch the Filer each database is connected to.  We made the change, nothing substantial in terms of performance throughout.

Storage – Flash Memory

So things were bad and stayed bad.  And, Steve went away for a while and came back to let us know that he might be able to help.

He said we should run perfstats and collect performance metrics.  And, so we did so.  Upon reviewing the numbers he said getting Flash Memory will help.  As we were ‘reads’ heavy, Flash Memory would serve to keep more of data in the Filer Memory and we would not have to go the actual Filer’s physical disk, as much.

So we took his advice and added the Flash Memory to the Filer.  Since we knew we were memory bound on the hosts as well, we took the time to add memory to our database hosts.

We were a bit constrained as to how much memory we could add to the Database Hosts; the reason being that we did want to throw away any of the existing memory Simms.  But, thankfully the manager was so committed and he ‘maxed out’ the boxes.

Storage – Additional LUNS

Things were good.  Performance was quite better.  But, nightly out of disk calls remained. We were running out of database log room.

The reasons were related to Database Mirroring problems, Device driver issues, etc.

So what to do: update device drivers, install database service packs, etc.

And, cup in hand, go begging for more storage.

Manager ponied up the money again.  And, we went to each host and added secondary local disks.

As I thought of the best way to divvy up the new storage, Steve was roaming around and I brought up the fact that we were going to add new Storage, but wanted to know if it will be better to use new disk or have him carve out new LUNs on the Filers.

And, Steve said let him go back and look at how much Storage we had available on each hosts.

A week or two later he came back with a nicely prepared document.  The document had charts and hard facts about our current setup in each environment – how much storage is available in whole, how much is already allotted, and how much more is available.

The document was so beautifully prepared I walked it in to my manager.  It is always easy to go to management with something you know they will get behind quickly.

Management said yes right away and since no new purchase we could start right away.

All I needed to do was write a deployment plan & schedule.

Did so and got on the Internet and researched each Storage Filer command that we will be running.  Easy; as Steve had each step written out already.

Our rollout of the new LUNS went very well.  We were able to align the new drives to ensure that they were properly aligned – This is an OS Requirement.

There was zero downtime.

But, then came some alarms.  Our support staff started getting these alarms that the Volume was almost full.  And, this was due to me not following our internal guidelines.  Steve was aware of that guideline and had taken it into consideration in his original proposal.

With the Log LUN provisioning having gone so well, we created new LUNS for TempDB, as well.

This meant that our 3 Database pillars – Data, Log, and TempDB each had their own LUN.

Slow Performance on a couple of hosts

With more tweaking here and there, things were generally well.  But, we continued to have noticeable problems on one or two hosts.

It was all consuming trying to determine why these hosts were slow.

Spent months on this holding pattern, nothing we tried budged our trouble.

Opened Ticket with Vendor

We opened a ticket with Vendor for this one host.  While ticket was opened, read and read some more on what we can possibly do to help Storage I/O.

Ideas came such as using compression, which is a bit of an Industry term for what marketing folks are now calling de-duplication.

We tried compressing some of our database files, and that helped.  But, still not nearly enough on these troublesome hosts.

Storage Vendor called meeting and we went to the meeting.  In a nutshell, they held on the fact that these Filers are configured just like others and there was nothing in their setup that might contribute to slowness.  They cited the problem was probably related more so to something going on with our database hosts and that they noticed that in some cases we were bumping into the upper limit of our Queue Length.

We all know that the only way to get more Queue Length is to drop in yet another LUN and split our database \ database files between the current Data and the new one(s).

Hearing this yet again, confirmed that there was nothing that we had missed while configuring the Database, nor the Storage hosts.

Nowhere else to go

A great leader said words that had stayed with me for a while here — “Having nowhere else to go, but on my knees”.

And, that was me — No where else to go, but to the database hosts.

I stopped a couple of database jobs so that I can better explain our system.  As we continued stopping more jobs and looking ever deeper, we noticed that our system was still chewing up a lot of I/O even though the jobs that were running were not the ones we once thought were the most expensive.

As we looked more closely at these newly found expensive queries, we noticed a best described anomalous behavior of otherwise simple queries.

We planned on calling our Database Vendor to possibly gain more clarity.

But, the helping hands of God, came sooner than we could have asked.  We were able to fix the SQL.

I am sure Steve and the other engineers helped focus and constrain our thoughts.

Spoilt

In the last few months, there were many things we considered doing. Some avenues were not fully tried, and others half-heartedly.  But, I think somehow God really wanted this problem to be one that he will solve with 5 loaves of bread and 2 fishes.

All along, I have held this little feeling that if we would have tried a few different things we would have gone further.  But, again nothing helped until we gave 5 loaves of bread and 2 fishes.

The whole silliness is that I can no longer hold the sad feeling of how we could have solved it through engineering might.

All I have to say is that God had his way, and I have to release the grudge to him and have him wash it away.  And, he is looking down and saying you silly blogger.