Solved

Indexing in SQL Server 2012

Posted on 2013-06-05
6
220 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
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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 48

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:ScottPletcher
ScottPletcher 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now