To-do lists

Something on your “to-do list” that never gets done.

Well, this WordPress prompt is really quite easy to answer: writing blog posts!! Ha ha

I always have great intentions of writing, but I always fall short. I truly thought being a new “empty nester” I would have more time on my hands. Well, that didn’t go as planned.

And the embarrassing thing is I absolutely love training, learning new things and imparting that knowledge. For the past 15+ years, I have been an adult volunteer for the Boy Scouts of America. In that time, I have participated in local, regional and national training courses. I was instrumental in beginning out council’s University of Scouting. I have staffed multiple local, regional, and national training courses for both youth and adults. I have presented multiple times at various SQL Saturdays. I saw all that not to brag, but to show I truly do love training and imparting knowledge. I guess I am more of an in person trainer, not a writer.

One of the hardest part of writing is determining the subject matter. I believe if given a topic, I could figure it all out. Another issue with flogging is the fear that I am writing about things that have been done to death. No one wants to read duplicate topics. It is hard to be original.

I hope to do better. I will set new goals and task myself with doing one blog post a month to start. If I can accomplish that, maybe increase frequency of blog posts. Maybe, haha!

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,, 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.