Solved

Indexing in SQL Server 2012

Posted on 2013-06-05
6
253 Views
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.
0
Comment
Question by:DB-aha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 16

Accepted Solution

by:
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...
0
 
LVL 1

Author Comment

by:DB-aha
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.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
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.
0
Industry Leaders: 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!

 
LVL 1

Author Comment

by:DB-aha
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.
0
 
LVL 49

Assisted Solution

by:PortletPaul
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.
0
 
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.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Viewers will learn how the fundamental information of how to create a table.

734 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