December 18, 2007 at 8:58 am
yup agreed, although I admit to being biased as tuning is what I do most of. No offence to the posters in this thread but I meet too many people who think they know how to tune sql server and have some of the strangest ideas/beliefs often backed up with no documented hard evidence or testing. Sadly gold partners are not excluded from this - even some of the microsoft support people have some very strange ideas - but I'm not going to get dragged into that.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 18, 2007 at 9:23 am
colin Leversuch-Roberts (12/18/2007)
I meet too many people who think they know how to tune sql server and have some of the strangest ideas/beliefs often backed up with no documented hard evidence or testing. Sadly gold partners are not excluded from this - even some of the microsoft support people have some very strange ideas - but I'm not going to get dragged into that.
😀 Yes it is funny, how everyone that is into tunning has 10 years of experience in tunning SQL 2005 ... although it hasn't been around that long.
:w00t: I don't say I'm a guru in all of this. What I'm saying is that we should take into consideration ALL the facts, even the ones that remain hidden, or seem obvious... Did I told you about the time i thought my monitor was fried after an electric storm, and it turned out to be that the power cable was kind of loose?
I just assumed that lightning storm + monitor failure = fried failure, and didn't even thought about checking the cable. Same thing with DTA and migrations... sometimes we don´t realize a change we do.
I don't know if this is going to be a Bad word, if it is I apologize, but in MySQL I remember a programmer going crazy cause he was getting a date back but not a correct hour. He totally forgot he had changed the data type to DATE, instead of DATETIME. [Sorry mates you will have to wait to SQL 2008 for that data type 😉 ]
As I already said, I did it on win 98, so it never hurts to cover all basis, and that includes not believing everything your DTA tool tells you... it is a tool after all. I bet Arton Sena Dasilva in a Toyota Camri, will beat me everyday of the week, even if I race him in a ferrari... reason is tools are just as good as the hands / minds of those who uses them.
SQL Server 2008 has better new tools. Let's wait and see.
December 18, 2007 at 9:54 am
just an observation here that I often make - databases hould run on server o/s not desktop o/s. Sorry but if you're using w95/98, w2k prof, xp, vista you're not running on a server o/s and things are not the same. You don't need years of experience in tuning, but it helps, thing is back in sql 6.0 there was less to get to grips with - It must be daunting dealing with sql 2005 as your first database , I see lots of talk about sql 2008 as if it will be radically different to sql 2005 or it will magically fix all the ills of upgrading from 2000 - I doubt there's been that much significant changes to the bits that count - and so much tuning is common sense anyway - but as I said earlier, I'm not putting anyone down - I usually come in on the back of those who've tried to tune a database/server/application and I just find common trends running through what i have to do :-
incorrect server setup ( what this generally means is someone has changed the out of the box settings )
poor disk subsystem
poor raid choice
poorly performing san
poor data structures
lack of understanding of reporting functions
ad-hoc queries against prod systems
lack of training for those writing queries
appalling thrid party applications
inapproriate indexes
lack of service packs/patches
32 bit where it should be 64 bit
no controls or processes
no trending or performance monitoring
The main fault with performance tuning is not having a baseline to work form and not having a test system for proper testing.
OK so I've been tuning for 14 odd years ( not sql 2005 for all that time ) but I don't have all the answers and every client/server/application teaches me something new - AND - without this forum and the contributors my life would be less rich and much more dificult.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 18, 2007 at 10:19 am
I agree, most installations that I see are done in Windows 2003 server. But I have also seen products that uses MS SQL as the main engine, run in laptops, and desktops, different animal, same spots.
I don't want to say if SQL 2008 is going to be all that good or not. I just believe with all the new features / tools, this will be nice to try. It can end up being a total disapointment, but all in all I like vista far more than XP.
What I have used as a beta tester of SQL 2008 is that is more powerful. However a downside I might say is that some of the programs I've seen running perfectly in SQL 2000, doesn't work that well int SQL 2008. I don't think they are trying to be 100% compatible with that version any more; and I do think MS will try to get us all into his new application boat.
Just my 2 red cents.
Sven
December 18, 2007 at 10:30 am
I have been tuning almost as long as you have Colin (since 6.5 first came out) - it has actually become one of the primary areas of my consulting business - and I have to agree with you on all counts. I will add that there is simply NO subsitute for loads of experience coupled with constant study. Everyone seems to think that a few forum posts will always solve their problems and teach them all they need to know to be a perf guru. NOT!! 🙂
And despite thousands of hours of personal time studying and tens of thousands of manhours of work experience I too STILL learn something new at every client I go to!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 19, 2007 at 6:00 am
I agree with Colin, Sven and the GURU on this. Tuning is not simply a matter of running the DTA and taking its advice as gospel. Unfortunately, if you get caught on a support call with Microsoft Premier Support, they'll ask if you've run it. If not, they'll require it. Further, they'll require you take the suggestions it makes and apply them. They did this to me even though I told them that the index it was suggesting wouldn't have enough selectivity to be used by any query, let alone the query that we were experiencing problems with. No matter. Apply it or the support call can't continue.
So while we may not place much belief in the power of this three-legged dog (good one), Microsoft is preaching the gospel according to the DTA.
For what it's worth I've been screwing up... uh, I mean, tuning, yeah, that's the word, tuning databases in SQL Server since 6.0 and I still pick up a new wrinkle or twist almost daily. Every so often I even learn something that I absolutely KNEW to be true to be false. So, probably, someday, whatever tool inherits from the DTA will work well and I'll be screaming from the mountain tops how much it stinks and be totally wrong. But today, I'm still right. DTA stinks.
"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 19, 2007 at 8:05 am
I can't speak from personal experience w.r.t the DTA, as I've never used it outside a class room. I prefer to tune by hand.
What I have noticed with it is that it tends to go well overboard, suggesting massive covering indexes, secondary indexes and 3-4 column statistics for a single query.
If all there was to tuning was running the DTA, I'm sure a lot of us would be out of work. 😉 Long live crappy tuning advisers.
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
December 19, 2007 at 8:50 am
Agree Gila. I have seen it pick 75% of the columns on a table as either fields or includes in an index! One client implemented all of the DTA suggestions they got and more than QUADRUPLED the database size!! Oh, and no, performance was NOT improved. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply