SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

T-SQL Tuesday #68 - Just Say No to Defaults & Stay Healthy

TSQLTuesday68Happy T-SQL Tuesday, July 14, 2015! (I have been time traveling, and now back on this continuum ;-)  @SQLBek, Andy Yun, has launched his own “Just Say No” campaign, and that is in the form of this month’s terrific T-SQL Tuesday topic, #68, “Just Say No To Defaults (To view the original invite, click on the previous hyperlink) So, what does he mean? Perhaps he had Greece’s economic chaos and vote in mind, featured in the news, Greece is about to default on its debt?  Nah, that’s a serious crisis brewing over there, but not as much a crisis for a DBA taking on a SQL Server installed, with all default settings!

Click, Next, Next, Finish, might be ok for some 3rd party vendors looking to install SQL Server, as the app’s backend, and get out quickly.  This is known as “Set It, and Forget It”, but it’s something the DBA will remember quite well when performance begins to suffer. As discussed in my new book Healthy SQL – A Comprehensive Guide to Healthy SQL Server Performance, in a situation of “Set it and forget it,” as the application usage increases, as new records are continuously inserted, updated, and deleted, and as users place demand on the application, response times will eventually slow down, as will the overall performance of the SQL Server database back end. Indexes become fragmented, excessive page splitting occurs, and backups are not properly configured.  This is almost always due to accepting installation defaults.

Key to making sure your SQL Servers are properly configured from the outset is creating and using an installation checklist. Not only will this checklist be used for your own DBA deployment endeavors, but one that should be completed by the application owner as well, so you know which components to install, what the instance name should be, special collation requirements, ports, etc.

 In fact, Andy asks us the following,

<< Do you have a checklist of “always change” settings?
     Do you have a preferred method for mass-deploying your changes?
    Do you have a specific default setting you ALWAYS change?

 And the answer to all the above should be, and for me, is YES! All DBAs should have a checklist of “always change” settings, and key default settings changed.

For example, don’t accept the default installation paths for SQL Server data, log and temp files! The Disk Layout for SQL Server should ideally separate Data, Log, tempdb, Binaries, and SQL Backups to separate physical disks. We don’t want to C: this drive for anything but the binaries. (play on words intended ;-). Please create additional tempdb files accordingly, that are sized way larger than the 8mb default. In most cases, enable the SQL Server configuration setting ‘Optimize For AdHoc Workloads’ for better query performance – it’s the closest SQL Server comes to having a magic turbo button.  :-) Always set the SQL Server configuration options Maximum Server Memory and Minimum Server Memory. Even consider when creating indexes to modify the default fill factor (0 or 100) according to whether your SQL Server is read or write performance heavy. There are certainly many more, and my book discusses them. Simple best practices will keep our SQL Servers healthy!

Furthermore, DBAs that will be deploying multiple SQL Servers throughout your company, should consider a way to deploy changes to multiple SQL Servers. In fact, you should have a standard build that you can deploy as well. You can use a silent unattended installations (using the /Q or /QS parameters) with the /IAcceptSQLServerLicenseTerms parameter to accept and get past the licensing terms. Better yet you can create a silent unintended install using the configuration file and a batch file. You would step through the SQL Server installation program, which will gather your preferred configuration options, and stop short of the final installation step, to capture the configuration.ini file and path.  For a complete step-by-step demo on how to do this, click on the aforementioned hyperlink.

 There are many ways to rapidly deploy code to multiple databases and multiple SQL Servers.  Natively, as mentioned in HealthySQL the book, you can also use central management server (CMS), on versions SQL Server 2008 and newer, to run T-SQL scripts and queries against multiple instances of SQL Server simultaneously by creating server groups that contain the connection information for one or more instances of SQL Server. You designate one instance to manage all the others and serve as the CMS. You can create a CMS in SSMS by selecting View > Registered Servers > Central Management Servers. Right-click and select Register Central Management Server to register an instance of the CMS.

Or you can use 3rd party software solutions, such as Red Gate’s Multi-Script, or ApexSQL Build. Apex also writes about deploying multiple sql scripts to multiple SQL Servers using a batch file, as well as their ApexSQL build product.

So I heartily agree with Mr. Yun, just say no to defaults!  Thank you, Andy, for hosting this month’s INSTALLment of T-SQL Tuesday! If you want to participate, use these DEAFAULT settings:

  1. Write a blog post about the topic. Don’t have a blog? Start one!
  2. Include the T-SQL Tuesday Logo and link it back to the original invitation post.
  3. Publish your blog post Tuesday, July 14, between 00:00 GMT & 23:59 GMT.
  4. Leave a reply on the original invite with a link to your blog post (for the round-up).
  5. Share you post with the community! Tweet it out using the #tsql2sday hashtag!

Oh, and ping Adam Mechanic (b|t), the creator of this booming blog party, if you too would like to host your own T-SQL Tuesday!


HealthySQL  For all things SQL, news, events, jobs, info, and other fun tweets, follow me on twitter @Pearlknows

  And for a comprehensive guide to healthy SQL Server performance, how to conduct a health check, and efficiently manage your SQL Server environment, pick up a copy of my    

  book on Amazon: http://bit.ly/HealthySQLonAmazon or direct from Apress: http://bit.ly/orderHealthySQLnow and join the #HealthySQL campaign to keep your SQL Servers 



No comments.

Leave a Comment

Please register or log in to leave a comment.