Wayne Sheffield

My blog about SQL Server

Browsing Posts published by Wayne Sheffield

TCP Chimney Offload transfers network traffic workload processing from the CPU to a network adapter that supports TCP Chimney Offload. This feature was introduced with Windows Server 2003 SP2, and it was called the Microsoft Scalable Networking Pack (SNP). Since Windows Server 2008, these features are a base part of these operating systems, so they no longer go by this name. To utilize this feature, the network adapter (and driver) must support this feature, and both the operating system and the network adapter must have this setting enabled.

This feature is not suitable for all applications. Microsoft says (at http://technet.microsoft.com/en-us/library/gg162709%28v=WS.10%29.aspx):

Because of the overhead associated with moving TCP/IP processing to the network adapter, TCP Chimney Offload offers the most benefit to applications that have long-lived connections and transfer large amounts of data. Servers that perform database replication, function as file servers, or perform backup functions are examples of computers that may benefit when you enable TCP Chimney Offload.

Default state by Windows Version

With the different operating systems versions, this feature is by default in different states:

Windows OS Default
Windows Server 2003 enabled
Windows Server 2008 disabled
Windows Server 2008 R2 automatic
Windows Server 2012 disabled

With all of these changes to the OS, which setting should we use for SQL Server? In general, for all of these operating systems, I recommend that TCP Chimney Offload be disabled – because you can see odd connectivity problems in any other state. Notice in the above quote that Microsoft says that this feature is best used for applications with long-lived connections that transfer large amounts of data – hopefully your OLTP database is performing lots of short-lived connections and they are not transferring large amounts of data (if they are, I can help you with that!). Some of the error messages that you can encounter are:

[Microsoft][ODBC SQL Server Driver][DBNETLIB] General Network error. Check your network documentation

ERROR [08S01] [Microsoft][SQL Native Client]Communication link failure

System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)

These errors are not exclusive to having problems with the TCP Chimney Offload. Additionally, they may only occur during times of high network load on the server.

To determine the Current TCP Chimney Offload Setting and to Disable it

In typical Microsoft format, this also varies between different operating system versions.

For Windows Server 2003, you need to check the registry. From a DOS command prompt, run:

If disabled, this will have a value of 0x0; if enabled, it will have a value of 0x1.

To disable, from an elevated DOS command prompt, run:

From Windows Server 2008 on, you can check the setting with this DOS command:

To disable it run the following from an elevated DOS command prompt:

 

References:

http://technet.microsoft.com/en-us/library/gg162709%28v=WS.10%29.aspx

http://support.microsoft.com/default.aspx?scid=kb;EN-US;942861

http://technet.microsoft.com/en-us/library/gg162709%28v=WS.10%29.aspx

http://blogs.msdn.com/b/cindygross/archive/2009/10/22/sql-server-and-tcp-chimney.aspx

This post is re-published from my original post on SQL Solutions Group.
Sure wish we could search this site

Click image to find an event near you!

SQL Saturdays are a neat idea – they’re a day-long event of free training, encompassing several one-hour sessions. These events will normally have 5 or 6 time slots during the entire day. Therefore, if the event has 6 concurrent sessions, that is 36 hours of training material. There is usually more than one event going on most weekends. If we assume 100 events at 36 sessions each, then there are 3600 sessions in a year. Since the speakers normally post their presentation materials and demo scripts on the site, the site itself has become a resource for additional training material. While the program is fantastic, the problem is that there isn’t a way to search the site for sessions.

Search the SQL Saturday sessions

5315.powershell-logo.gif-550x0Therefore, I’ve developed a PowerShell script that will search the SQL Saturday site. It searchs for sessions where the search phase is in either the session title or abstract. Without further ado, I introduce Get-SQLSaturdaySessionTopicSearch (which is available in my Code Library).

Input:

Name Required? What it does
-SearchTopic Required The phrase that you are searching for.
-StartDate Optional The first event date that you want to search. This defaults to 30 days before the current date.
-EndDate Optional The last event date that you want to search. This defaults to 30 days after the current date.
-EventNumber Optional The first event number where you want to start searching. Defaults to 500.
-ExportFile Optional The path and filename of the file to export the results to.
-DebugLevel Optional Controls the display of debugging and progress messages. Defaults to 1.

-DebugLevel values:

1. Displays the SQL Saturday URL as it is being processed.
2. Displays the event name and date if the feed for that SQL Saturday could be opened.
3. Displays session title for matched sessions.
4. Displays all session titles.

Examples:

.\Get-SQLSaturdaySessionTopicSearch.ps1 -SearchTopic ‘Query Store’

.\Get-SQLSaturdaySessionTopicSearch.ps1 -SearchTopic ‘Query Store’ -ExportFile ‘C:\Temp\SQLSatSearchResults.csv’

Output:

The output to the screen is the Event #, Speaker, Session Title and URL for the presentation.

The generated export file will also include the event name and session abstract. Additionally, the URL will be encased with the Excel HYPERLINK() function. When the export file is opened up with Excel, clicking the URL will open your browser to the session information, where the presentation material can be downloaded.

I hope that you get a lot of use out of this script.

Free SQL TrainingAre you going to the PASS Summit this year? (and if not… why not?)

Are you coming in to Seattle on or before Monday?

Are you not attending any of the wonderful precons at the PASS Summit on Monday?

Then I have a great deal for you.

Free SQL Training!

Several friends have teamed up to run a day of SQL training (while the training is free, the hotel we are using requires us to use their catering for lunch, so we are charging for lunch). Join us on Monday, October 24th, 2016 at the SpringHill Suites located at 1800 Yale Avenue, Seattle, WA 98101. This is about 4-5 blocks (just a bit over a half mile) from the Convention Center, so it’s just a short walk for anyone in the area. The only catch is that it starts promptly at 9am, so no late-night rebel-rousing if you want to spend your day learning!

From the speakers, we have 4 Microsoft Certified Masters (MCMs), 4 MVPs, 4 Friends of RedGate and 5 Threadizens. These SQL experts are:
Chad Crawford
Gail Shaw
Grant Fritchey
Jason Brimhall
TJay Belt
Wayne Sheffield

Just take a look at these speakers! Wow, what a great list! However, the speakers won’t make it a great day by themselves. While they do come close, this day is really all about the training. Topics for the day include:
An Introduction to SQL 2016’s Temporal Tables
From on-prem to Azure Sql Data Warehouse using Redgate Data Platform Studio
Impact Analysis – Discovering the true impact of database changes
PowerBI, from a DBA
Waits Analysis Deep Dive
The Adhoc XE Showcase: How XEs can help troubleshoot specific things from these other sessions

Are you really eager to go to this now? Well, just jump over to our registration page, and sign on up! We look forward to seeing you there!

Rename SQL Server

Rename SQL ServerSometimes you make a mistake, and forget to rename a syspred’d server before installing SQL Server. Or perhaps your corporate naming standard has changed, and you need to rename a server. Maybe you like to waste the time involved in troubleshooting connection issues after a server rename. In any case, you now find yourself where the name of the SQL Server is different than the physical name of the server itself, and you need to rename SQL Server to match the server’s physical name.

You could always rerun the setup program to rename the server. Fortunately, SQL Server provides an easier way to do this. You just need to run two stored procedures: sp_dropserver and sp_addserver. The following script demonstrates this concept. First, it will get the current name of the SQL Server name, the name of the computer, and the name of the SQL Server instance. Next, if the computer name plus the instance name is not the same as the SQL Server name, then it runs the sp_dropserver and sp_addserver stored procedures to rename SQL Server. The “LOCAL” parameter of sp_addserver denotes that this is the name of the local server. Consequently, you will need to restart the instance for the name change to take effect.

Before you just run this script, there are a few things to take into consideration:

  • If this is part of a SQL Server failover cluster, then a different process is needed. See this link to rename the cluster’s virtual SQL Server name. To rename the individual nodes, each node must be evicted from the cluster, the instance renamed (per this script), and then the node added back to the cluster.
  • SQL Server does not support renaming a server involved in replication.
  • Renaming a server that runs Reporting Services (SSRS) may result in SSRS not being available after the rename. If this happens, see this link.
  • When using database mirroring, you need to stop the mirroring before the rename, and reestablish it when finished.
  • If Remote Logins, Linked Servers, or Client Alias Names are used, see the “Other Considerations” section in this link.

 

This post is re-published from my original post on SQL Solutions Group. I hope that you enjoy it.

In my last article, I started off talking about checking various settings that make a performance difference prior to jumping into query tuning. This article continues the investigation into performance affecting settings, by jumping straight into the storage subsystem. How would you like to make a single change to your drives and have the IO operations improve by up to 40%?

Just reformat the drives that contain your database data and log files.

If you are running on a version of Windows prior to Windows Server 2008, or if the drives were migrated to a new system without reformatting them, then you really could improve IO performance simply by reformatting the drives. Older versions of Windows will create disk partitions that default to having the boundaries based upon the ancient Cylinder/Head/Sector addressing scheme that was common in disk controllers of that era. Once these boundaries are set, the only way to change them is to reformat the drive, so migrating this drive to a Windows Server 2008 or newer server alone does not fix the issue.

How reformatting the drive affects IO performance

File Allocation Size

On the hard drive, the minimum amount of data that can be read or written is called a sector, which historically was a mere 512 bytes (newer drives may have sector sizes up to 4KB). A group of sectors is a cluster (no not that type of cluster). A drive with a 64KB cluster size (at 512 bytes per cluster) will have 128 sectors, which is also known as the file allocation size. The first 63 sectors of a drive are reserved, or hidden, and include the master boot record (MBR). Simply multiplying the 63 sectors by the sector size of 512 bytes shows that this hidden area occupies 32,256 bytes, or 31.5KB. The drive dutifully starts storing data immediately after this.

SQL Server IO operations

Now, consider how SQL Server performs disk IO operations – an extent at a time (an extent is 8 pages, each of which are 8KB, for a total of 64KB). To store this information on disk, with the default settings, this 64KB will start immediately after the 31.5KB and continue for 64KB… and it will span two clusters. When the drive goes to read this data, it will have to read the first cluster, and then the second cluster, in order to get all of the data needed for the extent being read from disk. When the next extent is read, it will re-read the second cluster, and read the third cluster. The wasted IO operation should be obvious.

How to have SQL Server work better with disk

What we need to do is to offset the beginning of the data being stored on disk to a location more conducive to how the program is operating. This offset is known as the “Partition Alignment Offset”. To be in tune with SQL Server, this value should be an increment of 64KB. However, you also need to consider the entire storage subsystem – the disks, controllers and memory. Starting with Windows Server 2008, this offset is at 1024KB – a nice increment of 64KB that also works very nicely with most RAID disks/controllers. Prior to Windows Server 2008, partition alignment offset was not explicitly performed, so this will need to be performed.

Determining a drive’s partition alignment offset

To determine what the partition alignment offset is for a windows basic disk, there is a very simple wmic command to run:

The Index value is the same as the disk number reported in the Disk Management tool, and also from the wmic command wmic Volume. However, we tend to think in terms of drive letters, not drive indexes, so the following PowerShell script will return Partition Alignment Offset for all drives on the system that it is run on and perform the mapping from index to drive letter:

If you want to open the results in Notepad, pass in the parameter $True.

You may note that I said that using these wmic commands is for a Windows basic disk. If you are using a dynamic disk, you need to use alternative methods. With Windows Server 2008, you would use the tool diskdiag.exe (in earlier versions of Windows, this was called dmdiag). For either, you need to use the –V flag: dmdiag.exe –V or diskdiag.exe –V.

Changing the Partition Alignment Offset

To change the Partition Alignment Offset on Windows Server 2008 or higher, simply format the drive. On earlier systems, you will need to use the diskpart.exe tool to change the partition alignment offset, and then format the drive. In either case, this is a destructive operation – you will lose all data on the drive.

For more information on disk partition alignment, refer to this TechNet article: http://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx.

These steps should yield a noticeable IO performance improvement. Test it for yourself and let me know what you experience.

TSQL TuesdayIt’s that time of the month… the time when all of the T-SQL bloggers have a party and blog about a specific topic. This month, the host is my friend Jason Brimhall (b/t). The topic that he has selected is “Sharpen Something”. Specifically, he wants us to:

I am asking you to not only write a post but to do a little homework – first. In other words, plan to do something, carry out that plan, and then write about the experience.

Immediately, I’m thinking of something that I could write on. Then I read on and see this other part of his post where he has some examples:

You know you are extremely deficient at a certain SQL Skill. Tell me what that skill is and develop a plan to get better at that skill. Report on the implementation of this skill and how you are doing at improving. Maybe that skill is about Extended Events, PoSH or availability groups.

And now I’m thinking that Jason’s picking on me. He knows that there is one particular skill that I’m deficient on, and I’ve been meaning to work on it. So Jason, thank you for prodding me along.

To wrap up, Jason says:

In recap, this is an invite to make a short term goal covering the next two weeks. Tell everybody what that goal is (in your tsql tuesday post of course) and how you went about creating a plan for that goal and how you have progressed during the two week interval.

“Tell me what the skill is”

So what is the skill that I’m deficient on? It’s just the most popular feature released in SQL 2012: Availability Groups (did you notice how Jason even mentioned that above? See, he’s picking on me!) It’s not that I’m completely ignorant about Availability Groups – it’s just that I’ve never had the opportunity to work with this feature. I’ve even tried to work with it before, however higher priority things keep popping up, delaying my getting around to this.

“Develop a plan to get better”

The short and simple plan is to create an environment where I have Availability Groups installed, and utilize it on some databases.

The longer plan goes like this: Since Availability Groups is built on top of Windows Server Failover Clustering, I need to:

  1. Build an environment where I have a domain controller and two servers that have clustering implemented.
  2. Install SQL Server on the two servers.
  3. Create a few databases – with data.
  4. Create an Availability Group with these databases.
  5. Test the secondary replicas being read-only.
  6. Repeat for the following environments to assess differences:
    1. Windows Server 2008R2 with SQL 2012
    2. Windows Server 2012R2 with SQL 2012
    3. Windows Server 2012R2 with SQL 2014
    4. Windows Server 2012R2 with SQL 2016
    5. Windows Server 2016 with SQL 2016

“Report on the implementation of this skill”

This is the section where I humble myself. To date, I have created three virtual machines running Windows Server 2008R2. They aren’t even in a domain yet. A setback that I faced was that I already had a domain set up for this… but since I’m using evaluation edition of Windows, they had expired. Everything had to be rebuilt, and now I’m running into domain setup issues. It seems to me that the Windows setup is harder than the SQL setup will be. But I will persist, and this will be done.

Because I am extremely deficient in this skill, and I need to be working with it.

 

 

Service Packs and Updates

Upgrades and Service Packs

Source: http://www.digipas.com/files/photos-firmware/software-btn1.png

One of the things that I always check on client servers is that they are running the latest service packs, for both SQL Server and for the Windows OS itself. Now, sometimes I know what the latest service pack is – but other times I don’t. Therefore, I end up spending more time than I would like looking up the latest service pack. Therefore, I’m making a list of service packs here, along with download links (as I find them – navigating Microsoft’s Download Center isn’t exactly a piece of cake). I tried to get the download links for both 32-bit and 64-bit architectures, where applicable. Hopefully, you can use this chart also.

If you have a link to any that I have missed, please leave me a comment so that I can update this! I’d appreciate your help.


Windows Update Rollups

Windows Version(s) Date Update Prerequisites
8.1, 2012R2 2016-07-21 July 2016 Update Rollup April 2014 Update Rollup
Service Stack Update
8, 2012 2012-12 Dec 2014 Update Rollup none
7, 2008R2 2016-07-21 August 2016 Update Rollup SP1
Servicing Stack Update

Back to Contents

Windows Server Versions

Operating System RTM SP1 SP2 SP3 SP4
Windows 2016 CTP5
Windows 2012 R2 6.3.9200
Windows 2012 6.2.9200
Windows 2008 R2 6.1.7600.16385 6.1.7601
Windows 2008 6.0.6000 6.0.6001
32-bit, 64-bit
6.0.6002
32-bit, 64-bit
Windows 2003 R2 5.2.3790.1180 5.2.3790.?
32-bit, 64-bit
Windows 2003 5.2.3790 5.2.3790.1180
32-bit, 64-bit
5.2.3790.?
32-bit, 64-bit
Windows 2000 5.00.2195 32-bit

Back to Contents

Windows (non-server) Versions

Operating System RTM SP1 SP2 SP3 SP4
Windows 10 10.0.10240
Windows 8.1 6.3.9200 6.3.9600
32-bit, 64-bit
Windows 8 6.2.9200
Windows 7 6.1.7600 6.1.7601
Windows Vista 6.0.6000 6.0.6001
32-bit, 64-bit
6.0.6002
32-bit, 64-bit
Windows XP x64 5.2.2600 5.2.2600.1105 5.2.2600.2180
Windows XP 5.1.2600 5.1.2600.1105 5.1.2600.2180 5.1.2600.5512
Windows 2000 5.00.2195 32-bit

Back to Contents

SQL Server Versions

Version RTM SP1 SP2 SP3 SP4
SQL Server 2016
Latest CU
13.0.1601.5
CU2
13.00.2164.0
SQL Server 2014
Latest CU
12.0.2000.8
CU14
12.00.2569
12.0.4100.1
CU8
12.00.4468
12.0.5000.0
CU1
12.0.5511
SQL Server 2012 11.0.2100.60
CU11
11.00.2424
11.0.3000.0
CU16
11.00.3492
11.0.5058.0
CU14
11.00.5657
11.0.6020.0
CU5
11.00.6544
SQL Server 2008 R2 10.50.1600.1
CU14
10.50.1817
10.50.2500.0
CU13
10.50.2876
10.50.4000.0
CU13
10.50.4319
10.50.6000.34
SQL Server 2008 10.0.1600.22
CU10
10.00.1835
10.0.2531.0
CU16
10.00.2850
10.0.4000.0
CU11
10.00.4333
10.0.5500.0
CU17
10.00.5861
10.0.6000.29
SQL Server 2005 9.0.1399.06 9.0.2047 9.0.3042
CU17
9.00.3356
9.0.4035
CU15
9.00.4325
9.0.5000
CU3
9.00.5266
SQL Server 2000 8.0.194 8.0.384 8.0.532 8.0.760 8.0.2039
SQL Server 7 7.0.623 7.0.699 7.0.842 7.0.961 7.0.1063

Back to Contents

SQL Server Tools

Tool Latest Download
SQL Server Management Studio (SSMS) Latest SSMS Release
SQL Server Data Tools (SSDT) Latest SSDT Release

Back to Contents

Updates

Date Action
2016-08-05 SQL 2016 RTM, July update rollup for Win 7/2008R2
Added update rollups for Windows 8/2012 & 8.1/2012R2
Added SQL Server Cumulative Updates and SSMS
2016-08-22 Windows 7/2008R2, August update rollup
Fixed SQL 2005 CU links
2016-08-27 Added SQL 2014 CU1
2016-09-21 Changed SSMS to link to latest instead of specific version. Added SSDT, SQL 2016 CU2. Links for latest CUs for SQL 2014/2016.

I’m happy to announce that I’ll be going on a Central Texas SQL Tour in mid-August.

First up is the San Antonio SQL Saturday – I’ve been selected to be a speaker. This is really special because this is San Antonio’s first ever SQL Saturday!!! I’ll be presenting what is probably my favorite presentation – “Table Variables & Temp Tables – What You Need to Know!” This session usually runs about 75 minutes, so I’ve arranged to have the session immediately prior to lunch to help stagger the lunch flow. If you are anywhere in the area, come on out and see this presentation – I’ll guarantee that you will learn something, or double your registration fee back! You can register for this wonderful, free event at this link. From that link, you can also see the schedule and gaze in awe and wonder at all of the wonderful free sessions that you can attend all day. The SQL Saturday is on August 13th, 2016, and it is at Texas A&M University, One University Way, San Antonio, Texas, 78224 (map).

Image source: http://bit.ly/29R5yce

The following week, I’ll be at two user group meetings. The first is CACTUSS North on Tuesday, August 16th in Austin Texas. The meeting starts at 6pm, and I’ll be doing an encore presentation of “Table Variables & Temp Tables – What You Need to Know!”, so if you couldn’t see it at the SQL Saturday, be sure to come out to see it here. Sign up to attend this user group meeting here, and the meeting is at the Microsoft offices at 10900 Stonelake Blvd., Suite 225, Austin, TX, 78759 (map).

Image source: http://salssa.sqlpass.org/portals/404/salssa.gif

The next night, Wednesday, August 17th, I’ll be back in San Antonio, presenting at their SALSSA user group meeting. The meeting starts at 6pm, and I’ll be doing a different presentation – “SSMS Tips and Tricks – Using What You’ve Already Got”. They meet at the New Horizons Computer Learning Center, located at 2727 NW Loop 410, San Antonio, TX, 78230 (map).

Image source: http://www.thesanantonioriverwalk.com/images/site/home_night_banner.jpg

You can read the abstracts for both of these presentations here. And yes, I’m sure I’ll be at the Riverwalk in San Antonio sometime during my free time. I love the Riverwalk, and my favorite restaurant is nearby.

 

 

This post is re-published from my original post on SQL Solutions Group. I hope that you enjoy it.

When investigating a performance issue, the desired end result is already known… you need to make the queries run faster. It’s been my experience that most performance problems involve optimizing the query that is being run—sometimes the query needs a re-write to be more efficient, sometimes the tables being queried need a new (or modified) index, and sometimes even the underlying database schema might need modifying. Before starting down any of these routes though, the first thing that I do is to check the configuration settings that make a difference.

Enter SQL Server’s “Go Fast” button

turbobuttonIn a CPU universe far, far away, there existed a particular series known as the 80486 processor, commonly called just 486. One particular issue that this series of CPUs had was that they were too fast for many games of that era (games that were coded for running off of CPU ticks). If you can get hold of one of these games and try to run it on a modern system, I wish you luck in even being able to press a single key before the game ends! But I digress… in order to counteract this issue, the processors had a feature where they could be slowed down. There were different ways that the CPU could be slowed down, but to interact with the computer user, there was a button on the case, known as the Turbo button, that would cycle the system between the low and high speeds of the CPU. Accidentally leaving this button in the low speed would slow down everything else on that computer; merely putting the system into the high speed would fix your performance problem.

So what does this mean for SQL Server?

In a CPU universe very, very close to you, today CPUs have a similar feature known as CPU Throttling. This feature, otherwise known as Dynamic Frequency Scaling, dynamically adjusts the CPU frequency on the fly. There are several benefits from doing this: less heat is generated, less power is consumed, and it can even make the systems quieter by also reducing the fan speed necessary to cool the computer. In a large server farm, reducing the heat means that cooling costs are also reduced. By operating the systems in a reduced state, the lower cooling costs coupled with the lower power requirements of the computers themselves can mount up to quite a savings. The dynamic portion of this feature is based upon system monitoring of CPU usage, and when it is low the CPU frequency is reduced, typically by 50% or more.

For most systems, the computer’s BIOS will monitor certain communication from the operating system, so the operating system can also send instructions for setting the CPU Throttling. In the Windows operating system, this is performed through the “Power Plan” setting. Since Windows Server 2008, the default power plan is “Balanced”, which allows Windows to reduce the CPU speed in an effort to balance power and performance.

How does this affect SQL Server?

SQL Server is one application that doesn’t typically use CPU resources so much as to make the CPU Throttling disengage and the CPUs to run at full power. This manifests itself in SQL Server as queries just taking longer to run. You might even have a new server that the application runs slower on than the server it is replacing. The solution is to simply disengage the CPU Throttling feature so that the CPUs will run at full speed constantly.

Detecting if CPU Throttling is engaged

CPU Throttling is implemCPU-Z-CPU-Throttlingented at both the hardware and software level. The Windows operating system can control CPU Throttling, and this can also be controlled from the BIOS. On some systems, the BIOS will ignore signals sent from Windows, so we will cover how to test and set both methods.

To see if your CPUs are running at rated speed, there are two methods. The first is the third-party tool CPU-Z, available at http://www.cpuid.com/softwares/cpu-z.html. In this screen shot (from the above link), you can see that the CPU is rated to run at 3.2GHz, but is actually running at almost 1.2GHz. This system is having its CPU throttled.

Additionally, you can check out the WMI performance counters from the WIN32_Processor class. This DOS command returns the current and max clock speeds every second for 30 seconds; if the CurrentClockSpeed and the MaxClockSpeed differ significantly, then the CPU is being throttled.

In Windows, CPU Throttling is controlled from the Power Settings control panel applet. On a server running SQL Server, the desired setting is to be using the the “High Performance” power plan. You can do this from either the Power Settings control panel applet, or from the command line (starting with Windows Server 2008R2).

To start the Power Settings control panel applet, press the windows key + r key combination. In the run dialog box, enter powercfg.cpl. In the applet, you are looking for:

Power-Plan

From a command prompt, you can simply run this DOS command:

Disabling CPU Throttling

To change the power plan, in the control panel applet simply select the High Performance power plan (as shown above). From the DOS command prompt:

Changing the power plan takes effect immediately, so you should immediately see your CPUs running at full speed. This means that you can change the power plan on your SQL Server without requiring an outage. If, however, this does not return your CPUs to full speed, then the BIOS is overriding the Windows setting, and you will need to reboot to go into the BIOS to disable it, and once changed reboot again. Different BIOS manufacturers call CPU Throttling / Dynamic Frequency Scaling by different names, so you will need to investigate your server manufacturer’s web site to determine what it is called. This will obviously cause an outage during this time, so this needs to be a scheduled maintenance action.

What kind of difference does this make?

This isn’t the fix for all queries, but it should help out pretty dramatically. For a system replacing another, it should return the application to pre-update performance, if not surpassing it.

Recently, I had the opportunity to test out the performance impact of changing the power plan from Balanced to High Performance. Prior to changing the power plan, a set of queries was running in 7 hours, 45 minutes. After changing just the power plan to High Performance, this set of queries is now running in 3 hours, 55 minutes. That’s almost ½ the time, from this simple setting change.

Just remember… SQL Server is not “green”. It needs full power.

And now you know about SQL Server’s hidden “Go Fast” button.

Recently (okay, it went live a month ago, and I’m super-late in my blog post!), I was the guest on the podcast that Carlos Chacon of SQLDataPartners publishes.

In this podcast, we talk about Table Variables and Temporary Tables (if you’ve been to one of my presentations on this topic, then you know what’s coming!). We also talk about super-hero powers and other general chit-chat. You can hear the podcast at at this link (MP3). The “Show Notes” are available here.

I hope that you go out and listen to it, and that you enjoy it as much as I enjoyed harassing Carlos during the show!