July 24, 2008 at 3:34 am
We have our Production database on SQL Server 2000, we are facing some performance issue. we have only one application that is using this database.
I ran SQL Server 2000 Profiler and logged Trace in SQL Server 2000 Table, on another server (SQL Server 2000).
I have locally installed SQL Server 2005, Database Engine Tunning Advisor and run that workload to see recommendations on indexes. I liked the interface. One guy is saying that these recommendations are based on SQL Server 2005 manner.
These recommendation can't be applied and there would be different out put when running SQL Server 2000's Index Tunning Wizard
So I want to make sure that there is no any difference in SQL Server 2000's Index Tunning Wizard and SQL Server 2005's Database Engine Tunning Advisor based on above scenario I mentioned. because the Database Source is SQL Server 2000 and the workload table is also SQL Server 2000.
1- Shall I apply recommended (Create indexes, Create statistics) changes on Production
2- Are these recommendations are 100% correct? or always i have to care and check/verfiy recommendations manually?
3- How much percent they are usually correct and we may trusted and apply?
Please reply with your suggestions aswell.
Shamshad Ali.
July 24, 2008 at 3:26 pm
The suggestions are NOT 100%. They are suggestions. Quite often, they are dead wrong. Sometimes they are quite good.
You'll need to review the suggestions and make sure they actually make sense, then test them on a dev/test/QA server, before you take them to production.
- 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
July 28, 2008 at 1:11 pm
Compare the results of running it under Database Engine Tuning Advisor(2005) and Index Tuning Wizard(2000). It's been a while since I tried that but it seems that I came back with different results. Like was said earlier, you know(or will come to know) your system better than the wizard through trial and error.
There are things I don't like about DETA but we are still on 2000 so that's really a non-issue.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
July 29, 2008 at 6:37 am
Good afternoon, I found your query on the Performance Tuner interesting as I have recently been trying to get the Index tuning Wizard in SQL 2000 to work. I was hoping it would suggest inprovements to the indexes I have on my tables as I purposely removed a couple of key indexes and change one from a clustered index to a non clustered and then selected a script which accessed these tables and ran through the Index Tuning Wizard. Having checked the report there does not seem to be anything in there to suggest that I need to re-instate these indexes and I think there should be.
Any one any suggestions for tuning performance on tables ort scripts in SQL 2000.
Regards JP
July 29, 2008 at 6:51 am
When you run the Index Tuning Wizard are you running it against just one piece of code in query analyzer?
Did you tell it to do a thorough check? Keep the current indexes or add indexed views?
All of that can play into what it suggests. If you think that the indexes should be there and it doesn't suggest them then I'd ask myself if the code I'm running Index Tuning Wizard includes the queries that made think the indexes should be there initially.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
July 29, 2008 at 7:01 am
Hi
Thank you for getting back.
I did have it set to thorough check, I tried both options on the keep or drop indexes.
One of the indexes I dropped was a cluster index on the primary id field on that tables and I used a join in the query using that primary id field so I would of thought that this should have been highlighted.
Regards
JP
July 29, 2008 at 7:11 am
I would do a little research and see if the selectivity of that column has anything to do with why it wasn't suggested.
Also, you could always put the index back and then look at the execution plan to see if it even uses it. If it doesn't, then that would be my guess as to why it didn't suggest it.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
July 29, 2008 at 7:38 am
You are a star, thank you
Regards
JP
July 30, 2008 at 7:23 am
I recommend you be VERY careful about using either. They both will do HORRIBLE things to your database performance, size and concurrency by creating too many indexes and adding WAY too many (especially included) columns to the indexes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 19, 2008 at 4:13 pm
I digress on what I'm reading about DETA on SQL Server 2005 pro doing horrible things to your database.
For the situation that I am currently in; a legacy SQL2K to 2K5+SAP (lite on the SAP) move, the DETA has been a total blessing in disguise. All of the 269 tables in the old 2K db were 100% pure character data (fields); even numbers were converted to char() with virtually no key or primary key structure in place. We've single- and multiple column indexed for efficiency -- for genuine uniqueness now, added integer auto-incr's for tons of primary grid- and list-builder calls (in ASP.NET code) and I very rarely get less than a 1400% improvement in raw performance; raw perf meaning query"X" ran @ 2:19 before tuning, same query"X" now runs at 0:09 (9 seconds!) after simply running the tool and implementing the recommendations.
Mind you, most of the core table are millions of records deep and we typically join 3 (up to 7) tables at a time.
Realtime indexing via the tool and contemporary normalization technique have turned a GIGO db into one smokin fast operator!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply