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.

Revisiting and Restarting

Today is a new day. I now have a domain (http://sqlpadre.com) and I now have an email address Robert@sqlpadre.com and I hope this is a new beginning. This morning I revisited my old blog posts and discovered that I am not very consistent at blogging. In fact I was pretty terrible. I hope to change that.

Many years ago, I tried to begin two different blogging series called “B2B: Back to Basics” and “8 Weeks of Indexes”. These series were stared over 6 years ago and completely lost steam…translation I dropped the ball. Couple that with the fact two new versions of SQL have been released plus SQL Database in Azure, my blog is way behind the time.

I hope this is truly the beginning of a new chapter for me. I hope to restart both these series, producing one post a week alternating the topics. I have even set reminders.

The first post will be an introduction to both topics and provide an outline of the up coming posts (hey, I’m trying to stay organized).