In our DB world ,the most scary term we heard is performance issues. Most of the effective investigation mostly point  to no-usage or wrong usage of Indexes.I have  been working or learning  with a lot of good Data Gurus for a time now #SSGeeks.I surfed around the sea of unlimited knowledge available in the blog of DB professionals like me and jot down some important facts about Indexes 

The Index Breakdown Basics.

An index is an object in a database.

All items within a database are objects.

An indexes job is to speed up data retrieval for queries.

The are various kind of indexes but most of the time in OLTP environments we are taking about two.

The clustered index and the non-clustered index.

The clustered index is the data.

A non-clustered index is a separate object and it’s used to speed up data retrieval.

Most transactions in an OLTP environment fall into 4 categories when an application is in use.

Data is retrieved.

Data is updated.

Data is inserted.

Data is deleted.

Hopefully, most OLTP boxes will be heavy on insets and selects. (the select is how we retrieve data)

Indexes aren’t free. They take up space and we are penalized when data is inserted. (more reasons but that’s another story)

That’s because every non-clustered index points back to the clustered index and has to be updated.

If you have a table that’s heavily inserted into, the more indexes… the wider the index… the slower the performance of those inserts.

The goal of every OLTP app is balance.

You need the right combination of indexes to speed up data retrieval yet limit them so they don’t degrade performance on inserts.

That doesn’t sound so difficult so what’s the issue?

You are building an application and there are 10 developers adding indexes to their code on the same tables. Imagine the overlap on those tables.

Most companies put more than one database on each production instance.

These databases complete for the same critical resources. (mem, I/O, CPU)

If you have 10 databases on an instance and those 10 databases have 100 indexes and half of them are junk then the entire box is being affected by poor indexing.

The goal of performance tuning is two fold.

Number one, speed up queries for data retrieval.

Secondly, reduce resource consumption across the board on that box.

So, it  is  always good to remove the unnecessary indexes (especially those with the largest insert penalty) and to create the most impacting  indexes at the *INSTANCE* level.

If you wanna add something related to the post please feel free to ping it in comments section .  Mainly it is all about our ♥  for  Databases.

Signing off.

@DB#untuned