Winter is coming! Is your SQL Server ready?
There you sit, watching TV, when you hear it: “Analysts are predicting massive cold fronts this winter! Learn 12 tips to prepare for winter and the storm of the century tonight at 11!”
“Oh my gosh!” you think, “I’d better pay attention and record that for future reference”
Yeah. You are probably like me and just get back to trying to find something not over propagandized to watch. Is there anything like that on TV anymore?
Reality is, winter is coming, at least for those of us in the northern hemisphere. That doesn’t seem to mean much to us when dealing with SQL Server databases, right?
Well, actually, it does.
For a lot of companies, the end of year is an exceptionally heavy workload month. There is a culmination of month end, quarter end and year end analysis and reports to be done. If the company happens to be part of the retail industry, there are sales and promotions that start to push websites, applications, and databases to an extreme they usually don’t experience any other time of the year. If a company’s fiscal year matches is based on the calendar year, there is a lot of other reporting and analysis to be done for that. It all adds up very quickly.
Let’s go back to the initial opening statement I had, about the news coverage of the upcoming winter. There will most likely be some story about how to prepare your car, your home, or even yourself to make it through the rough winter ahead.
My question is what are you doing to make sure your database is ready for the onslaught of winter.
Here are 12 things to check to make sure your system is prepared for this winter. There are lots of other things that can be checked as well. I invite you to share in the comments anything else you do to prepare SQL Server for this winter’s onslaught of transactions and analysis.
Have Space To Grow
check drive space and growth trends to make sure you aren’t going to run out of space. Here is a simple query (there are lots out there, find one you like best) to show you just how much space is used by each of the files of your database.
use <DatabaseName>;
go
SELECT sf.FILEID ,
CONVERT(DECIMAL(12, 2), ROUND(sf.size / 128.000, 2)) AS FileSizeMB ,
CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(sf.name, 'SpaceUsed')
/ 128.000, 2)) AS SpaceUsedMB ,
CONVERT(DECIMAL(12, 2), ROUND(( sf.size - FILEPROPERTY(sf.name,
'SpaceUsed') )
/ 128.000, 2)) AS FreeSpaceMB ,
LEFT(sf.NAME, 15) AS Name ,
LEFT(sf.FILENAME, 30) AS FileName
FROM sys.sysfiles sf
From here you can see if you may need to add more space to your data & log files, or even to your drives to ensure stability.
Have Enough Memory
Make sure you have enough memory in your server to handle an increased workload and larger buffers. If pressured, now is the time to increase your memory rather than in the middle of a crisis. If you are using SQL 2014 Enterprise Edition you can look into using SSDs to increase your buffer pool size using the new Buffer Pool Extension option.
Check CPU Usage
Check CPU pressure and waits. If you are constantly waiting for the CPU now, imagine what will happen if you have a spike in processing due to more demand on the server.
You can use the information that SQLPerformance.com has here to help identify if there are and CPU bottlenecks you should start looking into.
Identify Slow & Expensive Queries
This doesn’t mean just those big complex coded, clunky ones. It also means those simple ones that run all the time too. If you can improve the performance of the queries most often used then you can prepare for the onslaught of executions the holidays may demand. There is some good info here about how to identify the heavy queries so you can start to focus in on them. Glenn Berry (T|B) has a nice set of queries that will help identify potential bottleneck stored procedures. You can get them here.
Know Your SLAs
Knowing how quickly you need to respond to an emergency, and how much data you can lose are critical in being able to sleep during these heavy load times. If you don’ t have them established yet now is the time to get that together. Here are a couple of videos that outline the RTO and RPO, which when combined work together to define your Service Level Agreement for a database / system.
Ensure Availability
Are you using mirroring? AlwaysOn Availability Groups, log shipping, crossed fingers? Whatever it is you are using, hopefully not crossed fingers, you need to make sure it is all working before things get crazy. Schedule time to perform a test fail-over to make sure things are working. The worst time to find out things aren’t working as planned, is when they happen on their own.
Clean Up Junk Data
If you have a lot of excess junk, and bad data in your database, this is the time to start cleaning it up. It will allow end of year analysis to be more accurate, and also help ensure the proper performance of your system. Check for things like untrusted foreign keys which can lead to data integrity issues.
Check Indexes
I know you are already doing index maintenance, probably with something like Ola Halengren’s Index and Stats Maintenance scripts, or even the new Minion Reindex from MidnightDBA. Either way, make sure you go in and check how often the various indexes are being used. You may have excessive indexes taking up space and resources that you don’t need. You may also be missing some indexes. Now, don’t just go blindly on indexes that SSMS suggests either, think about what you are doing and test them out. Better to do it now than try to figure it all out under pressure of a slow system and the glare of management.
Be Up To Date With Patches
Before things go nuts this winter, check your patch level. Here is a link to a post I try to keep up to date with the various version numbers by path level of SQL Server. If you are woefully out of date, it may be time to look into getting the patches in place. You can download the various patches and releases from the Update Center for Microsoft SQL Server. Keep in mind. Don’t blindly update your servers either. You will need to test first to make sure you don’t wind up breaking things. Breaking things right before heading into a heavy season of activity is not a very good thing to do. Get it done in a test environment where any damage that may occur doesn’t produce, as Grant Fritchey (T|B) says, an R.G.E. or Resume Generating Event.
Where Is TEMPDB?
We all know we shouldn’t have our TEMPDB sitting on the same drives as all our other data and log files. The reasoning is due to the high usage of TEMPDB by all databases on the instance of SQL Server. The performance can really be a problem when spinning drives are used because there is a physical delay when moving the HDD’s heads to the location of the data. If you have multiple databases trying to read from the same disk at the same time but from different locations, you can just imagine how crazy the physical movements get. The issues isn’t as prominent with Solid State Drives (SSDs) now, and I have personally witnessed some amazing performance from a SSD storage SAN from PureStorage. Whatever storage method you are using, be aware of where your TEMPDB is and make sure it can perform the best it can when you start to get excessive traffic to the database.
Check Security and Compliance
Make sure you have any required security measures in place. Be is IPSEC & SSL for connections, down to cell level encryption.
Configure Alerts
Alerts are things that will wake you up in the middle of the night, which isn’t pleasant. But it is far better to be woken up by alerted by your server than to come in the next day to the office being in chaos because your SQL Server is down and no one knew until they arrived. To really be able to use alerts you will need database mail configured first. Once you have that setup you will need operators and then you can get alerts setup. Below is a script that can be used to setup an operator if you don’t want to use the GUI (They are setup under the SQL Server Agent in SSMS). Just change the values below to those that apply for you.
USE [msdb] GO EXEC msdb.dbo.sp_add_operator @name=N'YourOperatorName', @enabled=1, @weekday_pager_start_time=90000, @weekday_pager_end_time=180000, @saturday_pager_start_time=90000, @saturday_pager_end_time=180000, @sunday_pager_start_time=90000, @sunday_pager_end_time=180000, @pager_days=0, @email_address=N'[email protected]', @category_name=N'[DBA Alerts]' GO
I highly recommend setting up your operator as a group of people. This way there is no single point of communication loss. Setup a mailing group, or use semi-colons to separate the various email addresses. You can even add your cellphone in as an operator to recive critical alerts.
Add your text receiving phone as an email contact as well for critical alerts.
You just have to separate the email addresses with a semicolon. You can use this little chart to determine how to email to your phone as a text alert:
| Provider | Format* |
| Sprint | phonenumber@messaging.sprintpcs.com |
| Verizon | phonenumber@vtext.com |
| T-Mobile | phonenumber@tmomail.net |
| AT&T | phonenumber@txt.att.net |
*this info is not guaranteed to work, but is easy enough to test
Once you have the operators all set, you can add the alerts and set them to send information to the operators when triggered.
The following script sets up the various alerts that are critical to the system operating. You can also create custom alerts. Just be sure to change the values to those for your environment.
USE MSDB;
GO
EXEC msdb.dbo.sp_add_alert @name = N'Sev19', @message_id = 0, @severity = 19,
@enabled = 1, @delay_between_responses = 600,
@include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Sev19',
@operator_name = N'YourOperatorName', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name = N'Sev20', @message_id = 0, @severity = 20,
@enabled = 1, @delay_between_responses = 600,
@include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Sev20',
@operator_name = N'YourOperatorName', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name = N'Sev21', @message_id = 0, @severity = 21,
@enabled = 1, @delay_between_responses = 600,
@include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Sev21',
@operator_name = N'YourOperatorName', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name = N'Sev22', @message_id = 0, @severity = 22,
@enabled = 1, @delay_between_responses = 600,
@include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Sev22',
@operator_name = N'YourOperatorName', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name = N'Sev23', @message_id = 0, @severity = 23,
@enabled = 1, @delay_between_responses = 600,
@include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Sev23',
@operator_name = N'YourOperatorName', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name = N'Sev24', @message_id = 0, @severity = 24,
@enabled = 1, @delay_between_responses = 600,
@include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Sev24',
@operator_name = N'YourOperatorName', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name = N'Sev25', @message_id = 0, @severity = 25,
@enabled = 1, @delay_between_responses = 600,
@include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Sev25',
@operator_name = N'YourOperatorName', @notification_method =
GO
EXEC msdb.dbo.sp_add_alert @name = N'Sev823', @message_id = 823, @severity = 0,
@enabled = 1, @delay_between_responses = 600,
@include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Sev823',
@operator_name = N'YourOperatorName', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name = N'Sev824', @message_id = 824, @severity = 0,
@enabled = 1, @delay_between_responses = 600,
@include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Sev824',
@operator_name = N'YourOperatorName', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name = N'Sev825', @message_id = 825, @severity = 0,
@enabled = 1, @delay_between_responses = 600,
@include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Sev825',
@operator_name = N'YourOperatorName', @notification_method = 1
GO
More information about most of these alert’s severity levels can be found here
There you have it. A few tips to get your SQL Server ready for this coming winter and the heavy workload expected with it. This doesn’t just apply to Winter as well. Use these checks and concepts anytime you are about to experience heavy workload. It is always better to be prepared than blind-sided by something that could have been easily avoided.
Be sure to share your comments below on what you do to prepare your systems for heavy workloads!
Avoid this look from management; Prepare for winter!
Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com.
Click here to contact Chris or to follow him on twitter.
Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services along with the destination location week-long training series: SQL Summer Camp. New in 2015 WaterOx Consulting is introducing the SQLPunch series of half day training events designed to help others learn to efficiently and effectively use SQL Server.
He is the founding president of PASSDC and organizes the annual SQLSaturday for the DC area. Chris frequently speaks at and attends SQL Server events, sharing his passion for all things SQL Server.
In 2012 Chris was a finalist in the worldwide Exceptional DBA competition and in 2014 he received the Microsoft MVP award in recognition of his open sharing of his knowledge with the technical community. His blog is currently syndicated to SQLServerCentral.com and ToadWorld.com