March 11, 2009 at 6:08 am
Hi everyone
I'm stuck with a database that was designed by someone else that has since left the company. It's been working fine for the past 2 years with the occasional slow day here and there.
I was asked to make changes to the application, and as a result had to change one of the field's lengths in the SQL 2000 database from NVARCHAR(9) to NVARCHAR(10). This table contains over 9 million records and the change of the field took quite a while. After the change, the database came to a grinding halt (and my life too - I haven't slept in days!).
To get to the point: I saw that the designer created a few initial indexes on a couple of tables - but I'm pretty much convinced that they're all wrong anyway. The server spec is good, there's lots of free memory, disk space and CPU available. I think the only reason the database was still performing ok without the proper indexes was because the server is so well spec'd.
So I was reading about using the SQL Profiler to get a report of usual queries performed during a day and then used Index Tuning Wizard to try and see if I could speed things up. In the end, Index Tuning Wizard had almost no suggestions and only created 1 index, which was a very silly index. But, it did seem to help a little bit and at least today the app is running, all be it slow.
So then I discovered that the guy didn't set any primary keys on the database and he didn't define any relationships between the primary key columns/tables and other table foreign keys. I always do it when I'm designing database systems but I'm not sure if it's absolutely neccesary to ensure a fast database system.
My Question: Will it help if I spent time on the database, clear all existing indexes, then start and create the correct primary key for each column. Then set the correct relationships between all relational fields. Then run SQL Profiler and get a good sample days work, then use Index Tuning Wizard, or will it be a waste of time?
Does the database actually use the "Relationships" that you define in Enterprise Manager? Will it speed things up?
Do you think that the reason why Index Tuning Wizard didn't really come up with anything was because it didn't "UNDERSTAND" what was going on as nothing was properly defined as it should be?
Any help or suggestions would be greatly appreciated!
March 11, 2009 at 6:37 am
barcode (3/11/2009)
My Question: Will it help if I spent time on the database, clear all existing indexes, then start and create the correct primary key for each column. Then set the correct relationships between all relational fields. Then run SQL Profiler and get a good sample days work, then use Index Tuning Wizard, or will it be a waste of time?
With the exception of the "use Index Tuning Wizard", that sounds like a very good idea. Once you have the trace, analyse it yourself and see what indexes are necessary.
If you need help with indexes for queries, post here and ask.
I believe there will be an article on doing exactly this in next week's Simple Talk newsletter (www.simple-talk.com)
Just be aware that you may encounter data integrity issues while adding primary and foreign keys. If there are none, it's possible that the data is bad and there are references to rows that don't exist.
Does the database actually use the "Relationships" that you define in Enterprise Manager? Will it speed things up?
Yes and maybe.
Do you think that the reason why Index Tuning Wizard didn't really come up with anything was because it didn't "UNDERSTAND" what was going on as nothing was properly defined as it should be?
No. Index tuning wizard's not that great. It sometimes misses obvious things, it often over-specifies indexes. 2005's Database Tuning Advisor's better, but not much better
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
March 11, 2009 at 8:39 am
Thanks Gail - it helps to get confirmation from an expert that I'm on the right path here!
I'm going to try it tonight when no-one's working on the DB and will maybe need help on 1 or 2 indexes - but I'll post here then we see if we can get this beast on the road! Everyone's been telling me that Index Tuning Wizard is the best thing since sliced bread but I've also been having my doubts lately!
March 11, 2009 at 9:08 am
barcode (3/11/2009)
Everyone's been telling me that Index Tuning Wizard is the best thing since sliced bread but I've also been having my doubts lately!
Personally, I don't use the ITW and I don't use DTA. I prefer to tune indexes by hand.
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
March 12, 2009 at 10:42 am
GilaMonster (3/11/2009)
barcode (3/11/2009)
Everyone's been telling me that Index Tuning Wizard is the best thing since sliced bread but I've also been having my doubts lately!Personally, I don't use the ITW and I don't use DTA. I prefer to tune indexes by hand.
Would you please provide some references where I could start on this?
Regards
March 12, 2009 at 11:01 am
J (3/12/2009)
Would you please provide some references where I could start on this?Regards
Check the Simple-Talk newsletter next week.
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
March 12, 2009 at 11:09 am
I've tuned a lot of databases, sped up a lot of queries for a lot of applications, over the last 9 years. I have yet to find the index wizards of any use to me at all. I've seen a lot of harmful suggestions from it, like a suggestion to add 5 indexes to a table, one with Col1, the next with Col1 and Col2, the third with Col1, Col2 and Col3, and so on. Would make sense (maybe) to have one with all five suggested columns, but definitely not to have five like that.
What I would suggest is creating a test copy of the database, and do your experiments in there. Don't mess with the production database till you know exactly what you want to do with it.
That means make a test copy, and play with removing indexes, building PKs and FKs, adding the indexes you think will help, then testing procs and see if it actually helped. Try a few combinations of things, see what gets you the best results, and then use that on the production database. It'll save you a lot of headaches in the future. I could be wrong, but it doesn't sound like you were working that way. If you are, great, just ignore this paragraph. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply