Go Premium for a chance to win a PS4. Enter to Win


Indexing in SQL Server 2012

Posted on 2013-06-05
Medium Priority
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 400 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 400 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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 49

Assisted Solution

PortletPaul earned 400 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

885 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