I’ve been doing SQL Critical Care® work with clients for over a year now. It’s absolutely true that every client’s situation is different and needs special attention. However, I’ve found that there’s a short list of issues that nearly all clients have in common. Drawing from that list, here are the top three high-impact, low-effort areas you can work on today to tune up your SQL Server.
Check power savings everywhere
I recently had a client whose CPU would peg at 100% for short stretches even though the server was under a light load. After some exploration, we found they had power savings turned on. In the BIOS. Because we couldn’t get to the BIOS remotely (and we didn’t want to reboot the server right then and there), we used a free tool called CPU-Z to watch core speeds. Can you spot the difference in speeds?
That’s a whole lot of CPU speed you paid for and aren’t getting! When checking power savings, make sure you check all of the following:
- Windows Power Options (under Control Panel)
- If a physical server, also check the BIOS at startup
- If a virtual server, check:
- hypervisor power settings/performance plan
- BIOS at startup of the hypervisor host server
One other thing: make sure you don’t have outdated BIOS firmware. That can have some nasty CPU consequences too.
Changes to the BIOS require a server restart, so plan accordingly.
How to tell if it worked: Using CPU-Z, watch the Core Speed box at lower left. If it deviates from the core speed in the Specification box by more than approximately 1%, there’s probably power savings turned on somewhere.
address hot missing index requests
Every time a query runs and wants an index that doesn’t exist, SQL Server files that missing index request away. You need to know which missing indexes are being requested and how helpful that index will be. There are DMVs you can query for this information, but my favorite method is sp_BlitzIndex®. It will tell you the missing index definition, as well as three numbers:
- Impact – the difference this index is expected to make in query cost.
- Avg. Query Cost – the price in magical Microsoft Query Bucks as determined by SQL Server.
- Uses – the number of times this index would have been used.
The product of these three numbers (Impact x Cost x Uses) equals the Estimated Benefit.
Because these numbers get reset with every service restart, we need to factor in uptime as well. If you’ve been up for seven days or more with your average user load during that time:
- Est. Benefit > 1,000,000: Keep an eye on this index.
- Est. Benefit > 10,000,000: Try it out in dev environment and see how it does.
- Est. Benefit > 100,000,000: Try it out in a dev environment — TODAY.
I can’t recommend outright that you deploy these missing indexes to production, just that you test them. One reason: it may be more efficient to alter a very similar existing index rather than add the new one. You’ll have to decide what’s best, but don’t let the 100M+ indexes go unnoticed.
And in case you’re wondering, the record high I’ve seen for the Estimated Benefit number is just over 14,000,000,000 (billion). Let’s hope you can’t beat that. (You don’t want to.)
How to tell if it worked: This missing index recommendation should go away in sp_BlitzIndex® results and the query running it should decrease in CPU time and cost.
Raise cost threshold for parallelism
SQL Server’s default Cost Threshold for Parallelism (CTP) is 5. Microsoft has left this setting untouched since the 20th century.
(Don’t laugh — that was at least sixteen years ago.)
Face it — pretty much everything that old needs an update. Your hairstyle. Your clothes. Your car. Your server’s CTP is no different. CPUs are way more powerful today than they were in the late 90’s. With CTP set to 5, chances are a lot of queries are going parallel when they don’t have to. Raise the bar to 25 or even 50 (as always, test this in dev first). Unlike licensing additional cores, changing CTP settings is absolutely free.
How to tell if it worked: You should see a drop in CXPACKET waits, along with some query plans no longer showing parallelism.
There you have it: three simple and quick checks to speed up your SQL Server. For more ideas, try Five SQL Server Settings to Change.
Brent says: Listen, people, this is free advice. Don’t pay us to tell you this. Just do it today. Then pay us anyway. Actually, ignore this post.
18 Comments. Leave new
With that last piece of advice, did the pilot kick the dog out of the cockpit? 🙂
Chris – HAHAHA, I see what you did there.
Thanks, Doug. I had the same issue last week on an Oracle server that had the power savings configured in the BIOS. Amazing the difference it makes when you’re firing on all cylinders. Now off to fire up sp_BlitzIndex on another project.
Was there a particular bug you were trying to highlight in the link to the BIOS update?
Yes. This one: “Updated Intel Management Engine (ME) binary to Rev 2.1.5. 0x8B, to address issues that resulted in unexpected temporary or permanent Processor frequency degradation.”
Note the last four words in that statement.
Does it matter if I the instance have been running for very long time (like a year) ?
Could the magic number be too high because of that?
I’m running more and more into that power setting issue, to the degree I’ve made a small app to help my fellow coworkers out. Very simply it does nothing but calculate numbers, over a number of configurable threads, over a period of time and shows the time taken.
If the number fluctuates significantly – the server most likely has power save mode set or there’s something wrong with the setup.
Very simple, yet incredible helpful to illustrate to non-techies that something is wrong.
The reason for I did this to begin with, was that I found settings were reported correctly on a virtual server in a load balanced setup, but we “had a feeling” that one server was slower than the other. And this little simple app helped identify issues in the underlying virtual environment where one entire cluster was setup wrong with power settings – thus actually affecting many other customers.
Power settings mean more than people think it does now – and it can be tricky to identify if dealing with out-out-house hosting companies, and in virtual setups.
So it’s definitely worth looking at that one if feeling a server might be under-performing.
Great article! Did you have any recommendations or considerations for COT with regards to SharePoint environments? I know SharePoint is very picky about MAXDOP, but wasn’t so sure about COT settings?
Thanks!
Ajay
Nice post Doug. Thanks.
Nice post Doug. I use Powershell and WMI to check the difference between CPU MAX and CURRENT clock speed. If they are different, it’s time to check the power plan. This can be executed quickly and remotely and can also be piped to SMTP mail for hands free alerting. I also included a link to using Powershell to check and change powerplans. Brent, I’m sure you will want to add this to your Powershell arsenal.
# powershell wmi script to check current CPU speed
gwmi win32_processor | select -First 1 -Property name, currentclockspeed, maxclockspeed, @{Label=”Status”;E={if($_.CurrentClockSpeed -ge $_.MaxClockSpeed ) {“OK. Brent, your CPU is running at Max Clock Speed.”} else {“OOPS! Brent, Your CPU is running slower than Max Clock Speed! Check your Windows Power Plan Settings.”}}} | Format-List
# How to use Powershell to read powerplan settings
# http://blogs.technet.com/b/heyscriptingguy/archive/2010/08/31/get-windows-power-plan-settings-on-your-computer-by-using-powershell.aspx
Enjoy!!!
Just curious if power saving has effect on VM instance?
It’s possible to have power savings enabled in the BIOS of the host, which would affect all the guests on that host.
Okay, I have ya beat on that 14 billion. I came across one today that is at 167,051,690,772 for the Magic Benefit Number. And right under it is a 1,240,025,903… Me thinks I better work that database over. Incidentally, three of the top six are load tables, the other three are a single table the others feed into.
That’s horrifying. How long was the server uptime?
About 2 weeks since the last restart of the server.
Wow. Just….wow.
I just found one while doing some performance analysis and could NOT believe my eyes: estimated benefit: 24,507,836,351 The server has been up over 100 days. WOW! sp_blitzindex is amazing. Thanks!!! I’ll try to let you know how the saga continues.
I beat you all. Just recently joined a production DBA team and am running some analysis on a poorly performing server. Got this magic benefit number on highest missing index: 244,957,517,484!!! It is failing over about once a week. 😉