November 26, 2008 at 10:33 am
I am (for the first time) designing my own database, and I've got a few tables that by necessity have millions of rows in them.
I've worked with other databases with similar scenarios whose queries ran significantly faster when they had one or more indexes associated with each large table.
My question is - since I've never designed my own indexes - is there a good resource out there that describes the theory behind creating a good index? Maybe a book or a website? With some fabulous examples?
Hopefully this isn't asking for too much.
Thanks for all your help!
November 26, 2008 at 11:05 am
Bottom line, a good index is one that queries use.
Take a look at the queries that run against that table. Look at what's in the where clause, what the join columns are. See if you can create indexes for those queries. Fewer wide indexes are far better than lots of narrow indexes.
If you want, post the table structure and a couple queries here and we can give you suggestions to get you started.
Edit: If you want a book, Inside SQL Server 2005: Query Tuning and Optimisation is excellent.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 11:16 am
Another vote for that book. Great read and good information.
Just remember that getting the clustered index right should be task #1. All other indexing flows from there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 2, 2008 at 3:16 pm
another vote for the book.
You could use DTA , but just be carefull to get to familiar with DTA , some people use it because they are to lazy to understand index architectures ect ..so follow Gails advise and teach yourself all the goodies. Takes time but worth it at the end.
December 2, 2008 at 3:27 pm
Just be careful with DTA, it might not give you what you are looking for either. During a performance issue on our Finance system, it never determined that an index was missing on a table heavily used when opening PO's.
December 3, 2008 at 5:28 am
Another word of caution for the DTA. I recently ran a series of experiments using the DTA against AdventureWorks2008. It wasn't capturing obvious indexing issues. I don't trust it at all. Unfortunately, if you do end up on a call with premier support, they may ask you to run it and implement it's suggestions, however silly they seem, prior to moving forward with the support. It's happened to me.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply