Follow sqlserver-dba.com

Daily & Exclusive Content

Delivered by FeedBurner

SQLServer-DBA.com Links

Dba_db2_button

Powered by TypePad
SQL Server , Meltdown and Spectre FAQ

16 February,2018 by Jack Vamvas

I'm getting various questions about the Spectre and Meltdown impact on SQL Server. I'll start listing the most popular questions and the response. There is a lot of activity in progress across all the communities about mitigating the impact of Meltdown and  Spectre . Typically there will be patching by different vendors and this needs to be managed properly.

Where is the Microsoft SQL Server guideance documentation  for Meltdown and Spectre ?

The main link I reference is Protect SQL Server from attacks on Spectre and Meltdown side-channel vulnerabilities .   

The main advice is start planning for patching. There is some guidance about how to manage open extensibility interfaces , e/g CLR. Also , there may be requirements to patch different layers of the systems. These are some examples : Operating System patches,Registry adjustments (which are a requirement for the Windows OS patching),Firmware patching,SQL Server patches - patches have been released for most versions). There is also some consideration as to how you'll deal with the open extensibility parts - CLR, Linked Servers

 

How can I check the status of the Windows OS patching for Meltdown & Spectre?

Microsoft have written a Powershell Script to assist you in checking  the patch status. Find the Powershell script at this location: Windows Client Guidance for IT Pros to protect against speculative execution side-channel vulnerabilities

 

How can I check SQL Server performance impact of the Meltdown\Spectre patching ?

There is going to be quite a bit of different patching over the next couple of months - impacting different parts of the systems. My approach is to develop a benchmark testing which runs repeatable SQL Server tasks on the same data sets. It's a combination of different queue depth queries, large sequential reads, BACKUP,REINDEXING.   It's a repetable test where I can quickly report on  the impact as different patches are applied.

I also gather wait statistics on SQL Server database servers which I extract pre and post patching for other insights into SQL Server as a result of Meltdown\Spectre patching

To correlate details from collecting wait stats , I collect Perfmon Statistics for the same timeframe . BTW - I'm not manually cranking every server to collect Perfmon scripts - it's all scripted from a central location .

The great thing about collecting these snapshots is they can be repeatedly checked as every other layer of Meltdown\Patching is being applied.

 

 

How can I check which SQL Server extensibility features  require reviewing?

In the Microsoft SQL Server guidance , there is advice on which extensibility features to review. These 2 queries will summarise the current value set for the configurables. All changes requires analysis for impact on application availability and performance

 

--To list all linked servers on a SQL Server Instance 
EXEC sp_linkedservers;

--To list items in sys.configurations. Use the guidance to decide on which items to focus

SELECT [name] as [Config Name] , [value],[value_in_use], [description]
FROM sys.configurations
WHERE name IN ('clr enabled', 'clr strict security', 'external scripts enabled', 'Ole Automation Procedures','xp_cmdshell')
ORDER BY name;

 

 

Some useful links

Update adds the "CLR strict security" feature to SQL Server 2016

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server , Meltdown and Spectre FAQ


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer