Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Indexing in SQL Server 2012

Posted on 2013-06-05
6
Medium Priority
?
265 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 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...
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 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.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 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.
0
 
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.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

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 part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

715 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