Indexing in SQL Server 2012

Posted on 2013-06-05
Last Modified: 2016-02-11
I have created an ssis package that creates and populates tables in a DB every night
These tables have alot of data so naturally they need to be indexed. Because I do not yet know who or what queries will be ran on this database, I do not know what indexes to create. I set up SQL Server profiler to capture all queries ran against the database and mark the time  the queries take to execute so that anything that takes longer than 10 seconds to load, I will run and grab the missing indexes code to create new indexes to improve load speeds on those queries.

The problem is, I am speaking to an audience that thinks you can index a table based on uniqueness of the columns alone and that is all there is to it. My experience, that is not possible, it would only be a guess. I even recall thinking a few years ago if I index all the columns, then the indexing problems are solved. The question is, is my approach the correct one to optimize the database based on queries executed against the database by users or is there a better approach?  

Also, how do I explain how indexing works to a non technical audience? I tried but it seems to go over their heads, keeping in mind these are people that have been hooked on MS access and are clinging on to it for dear life.
Question by:DB-aha
LVL 16

Accepted Solution

Surendra Nath earned 100 total points
ID: 39223342
how do I explain how indexing works to a non technical audience?

Take a book at tell them all the data in the database is the chapters they have (or the entire database) and the appendix / index/ catalogue in that book is the index for it (actual SQL server index), This is how I explain to the non-technical folk about the index and tell them it will help in looking into data as the index of a book help us get to the right chapter (with the help of page number).

I am speaking to an audience that thinks you can index a table based on uniqueness of the columns alone and that is all there is to it.

Yes, you can, as long as the index is covered index it should do good.

if I index all the columns, then the indexing problems are solved
Have ever seen a book with 500 pages of index and 10 pages  of content...
if you index every column in the table, it might fit to the above scenario....

an index will increase the read performance and reduces the write performance.. so if you create many indexes on a table without much thought then it might bring down the performance as well...

Author Comment

ID: 39223367
I am not sure what this means:

Yes, you can, as long as the index is covered index it should do good.
 If it implies a solution contrary to what I posted, please illustrate how it can be done and the steps to take to complete.
LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 100 total points
ID: 39224196
<<how do I explain how indexing works to a non technical audience?>>
OMG.  Don't even try.

Tell them it is the *stuff that needs to be included in the application design to optimize  speeding up queries* and ask them to call an MS consultant to ask him the following question: What is the consequence of having a database design without indexes ?.  

Seriously, I don't think it is a good idea at all to try to explain how indexes work on SQL to non technical since you will loose your audiences in few minutes.  The real message you need to get a across is why is indexing vital for SQL Server to function properly and you don't have to go into detail for this.  Another suggestion is: a design without indexes is like a bird without wings. Use analogy instead of lengthy explanations.

Hope this helps.
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  


Author Comment

ID: 39224481
I thank both of you for your input, however the answer still remains if my approach on how to build indexes is appropriate or if there is an out of the box solution for indexes on tables not knowing what queries will be ran.
LVL 48

Assisted Solution

PortletPaul earned 100 total points
ID: 39224544
I think you will find the discussion here very relevant on rules-of-thumb for indexing.

& I also think this small list is practical and realistic, & might be useful for your presentation

the book analogy (particularly if a big book) sounds good - hold up the book and, just say: 'Indexes improve the speed of queries"

going beyond that; as Racimo points out, may be just be a waste really.
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 200 total points
ID: 39226883
I use the phone book as the analogy (sadly now becoming anachronistic).

The phone book is ("cluster" -- specific level of audience indicates whether you introduce this concept or not) indexed by (last name, first name).  Therefore, a search by (last name) or (last name and first name) is very fast.

But what if you needed to find everyone with a first name of "John"?  The actual phone book doesn't help at all with that search.  But in a database, you could build a separate ("nonclustered") index that sorted the data by first name, so that a first name lookup is also fast.

Finally, what if you needed to find everyone whose number started with "283"?  In a database, you can create an index on number too, allowing those to be found quickly also.  A database allows you to create any number of indexes [not technically, but in practical terms that's true].

Hmm, great, let's index everything, right!?  BUT wait: what has to happen when we add a name to the phone book?  This new data must be added separately in ALL indexes -- thus, each index we create adds overhead when making additions, changes or deletions to/on the table.

It would be vastly harder to explain included columns and/or a "covering index" to a non-tech audience, and I don't think I'd even try to go that far.

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question