B2B: SQL Checklist

Working with a SQL Server Checklist

This is the 2nd post in my “Back 2 Basics” series following the introduction of topics.

Part of a DBAs role is to ensure SQL Server is installed and configured properly for optimal performance. Just about every DBA that has any experience validates their SQL installations with a checklist.  For years, my “go-to” checklist was Brent Ozar’s SQL Server Installation Checklist.  It has been a few years since it has been updated; but it still is the standard.  Based off his and many other I wrote my own check list of things to do on installation of a SQL Server.

These are the important things you need to remember when it comes to an installation checklist or a checklist for optimal performance.

For me, if I am given a new SQL instance to manage or installing a new SQL instance these are my personal checklist items:

  1. Server Level Configurations
    1. Enable DAC
    1. Configure TempDB
    1. Max Degree of Parallelism
    1. Cost Threshold for Parallelism
    1. SQL Server MAX Memory
      1. Memory Management
    1. Ad Hoc Workloads
  2. Database Configurations
    1. Individual Volumes for Data (mdf) and Transaction Log (ldf) files
    1. Auto Growth Settings
    1. Auto-Shrink
    1. Auto-Close
    1. Auto-Update Stats
    1. Compatibility Level
  3. Setup Maintenance – I recommend Ola Hallengren Scripts for this.  Ola’s scripts are world famous script used to manage all your databases.  On smaller setups, you can install and configure with a single script and never have to worry again.  However, I strongly recommend you learn the ins & outs of his scripts to customize them for your needs.  You can even extend its functionality with Configuration Tables.
    1. Backups
    1. Integrity Check
    1. Index
  4. SQL Agent Alerting
    1. Database Mail
    1. Configure Operator
    1. SQL Agent Failsafe
    1. SQL Agent Alerts

What is an Index?

This is my 2nd post in a series called “8 Weeks of Indexes”. 

Merriam-Webster defines index as:

a list … arranged usually in alphabetical order of some specified datum.

One of the most common real-world examples of an Index is your telephone book. The book stores information (Name, address and phone number) sorted alphabetically by last name. The pages are written in such a way that A comes before B and B comes before C, etc. If one knows their alphabet, then any name can be easily looked up.  Typically, the first “key” to finding a name is at the top of the page, which tells you what section of the book you are in.  If you were to locate my entry in the phonebook, you would quickly scan through the key until you found the letter B at the top of the page.  Then you would continue to scan until you find the group of entries for BISHOP.  And of course, then locate which entry matched my name: BISHOP, ROBERT. If there were no key at the top of the page, you would have to seek through all the pages one at a time until you got to the B section.  Another excellent real-world example of an index system is the Dewey Decimal System.  Libraries have been indexing their books with a numbering system for years. 

So, how does this all relate to SQL Server?  There are several bold print words above that translate to SQL Server terms and how SQL works the same way as a phone book.  To fully understand how SQL Indexes work one really needs to know how SQL stores data. We know SQL has the .mdf files that actually stores all your data.  However, the data file is made of pages that are 8 KB in size.  At the top of each page is a “page header” used to store system information about that page.  There are many different types of pages that store different things, but two specific types of pages I want to talk about are “data” pages and “index” pages.

A “data page” is where your actual data (based on data types) is stored and as you guessed it, the index page stores index information. The “key” to proper storage of data is a Clustered Index.  A Clustered Index, physically writes and stores a row of data in a page by selected column(s) and by sort order selected.  So, a Clustered Index on a user table could be by the column [Last Name], just like a phone book. This will ensure that the data rows are written in alphabetical order on each page and in turn each page will be in alphabetical order as well, very efficient.  SQL Engine “scans” the index to determine exactly what page the “B” last names are located.  If a table did not have a clustered index, the data would be stored in a “first come-first served” fashion.  In this scenario, the SQL Engine would have to scan then entire page or multiple pages to find your entry, very inefficient.  Imagine how inefficient a phone book would be if the publisher just kept adding rows to the end of the book every year without being sorted by name.  How long would it take you to find my name then?

So, the key to storing data in SQL, is to have a pre-determined way you want the data rows saved to the page. Ideally this would be the most used method of finding a row, i.e. by “Last Name”. 

Next week…  I will start a discussion on Types of Indexes in SQL Server.  There are too many to include all of them, however I will introduce and discuss in detail some of the more commonly used indexes.

B2B: Back 2 Basics Introduction

Learn to FlyOn occasion, an IT personnel (non-DBA) is tasked with providing SQL support.  Either with installation, upgrading, backups, restores, moving databases or other tasks that normally would not fall under their job description, you know the “and other assigned duties”.  Trust me I have been there.  We call these “Accidental DBAs” and sometimes a strange thing happens, the person actually ENJOYS working with MS SQL.  At least that is what happened to me, I was a .NET developer in a corporation with no DBA and over 20 instances of MS SQL in our network.  As the developer, I did primarily did developer tasks: create and/or modify tables, views, stored procedures, etc.; but I did not learn SQL Administration or management until much later.

So, if you are one of those accidental DBAs, where do you find more information on how to do DBA work.  I have always found www.sqlservercentral.com, www.mssqltips.com, and MSFT Documentation  to be invaluable resources to find answers to questions.  I follow some “gurus” of the industry on twitter and their blogs are never boring and always informative.  Some of my personal favorites, to just name a few:

  1. Brent Ozar
  2. Penal Dave
  3. Thomas LaRock
  4. Paul Randal

During this series I will hope to provide the following information:

  1. Working with a SQL Server checklist.
  2. Tools to Manage SQL:  SSMS vs Azure Data Studio vs Visual Code vs PowerShell
  3. Document, Document, Document
  4. Maintenance plans alternatives because MPs really suck.
  5. Secrets to keeping your DBA Job:  Disaster Recovery plan

As of now these are the topics I plan to cover in my Back to Basics series.  Although I reserve the right to edit this, I believe if an “accidental” DBA would learn these, then they could probably drop the “accidental” from their title.

8 Weeks of Inexes: Intro

This is the first post in a series of 8 posts about indexes. I hope to introduce you to the different types of indexes, different way to determine index effectiveness and overall hopefully learn something myself.

I have outlined the next few weeks of the topics of my posts:

  1. Introduction (this post)
  2. What is an Index?
  3. Types of Indexes?
  4. Structure of an Index
  5. Determining what indexes your database has right now!
  6. Are current index effective?  What makes effective indexes?
  7. Management of Indexes: They are not “set it and forget it!”
  8. What I’ve learned

Indexes are vital to SQL Engine efficency. Proper indexes are essential to SQL Engine efficiency. In posts 2-4 I will explain all I know about what an index is supposed to and how to create them.

Incorrect indexes or too many indexes can have a negative affect on performance as well. In posts 5 and 6 I hope to show you how to determined your database index needs to help your queries run as efficient as possible.

In the 7th post, I hope to show you quick and easy ways to manage your indexes, because proper index management is as essential as creating the correct indexes.

and of course, I will wrap the series up with the 8th posts. If I stay true to my word, the 2nd post of this series will be in 2 weeks. I am alternating one post a week with my “Back-2-Basics” series as well.