June 17, 2008 at 1:34 am
Hi there, I have been searching for solution for this problem but unable to find one. Very much appreciate if some pro from here can give me some tips on the mentioned.
I am able to run Database Tunning Engine and able to login to pick a saved query. But after click Start Analysis, it stops at Submitting configuration Information (first out of 5 processes). Sometime, after a few minutes it will return a message say query timeout or sometime it just hang it there forever...
Please help and thanks is advance.
June 17, 2008 at 8:38 am
Is the server seriously overloaded?
- 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
June 17, 2008 at 11:14 pm
You may not have the privs to run it.
--Jeff Moden
June 18, 2008 at 4:06 am
The SQL server is installed at my note book and virtually no load because I am the only one using it at all time for development purpose.
Also, I login as 'sa' to run the Database Tunning Engine and I guess it should have correct privilage.
Any idea? TIA
June 18, 2008 at 7:16 am
Unfortunately... no... I don't. Sounds like you have everything you need to run it correctly.
--Jeff Moden
June 19, 2008 at 7:55 am
Any pro out there has a solution for me? TIA
June 19, 2008 at 9:20 am
My advice is to never use DTA at all!! I have seen it do AWFUL things to databases. Well, actually that works in my favor since I get called in to clean up the mess. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 22, 2008 at 8:23 am
hi ,
did you try to rebuild indexes for the database
sometime when the indexes are fragmented , SQL try to get data directly from the table and you will get Time out expired error
HTH
Yaron .
June 23, 2008 at 10:12 pm
Hi Yaron,
I was trying to use DTA to check and get some suggestion of which table to be indexed.
I found it is usefull for SQL2000 and I have no problem with its DTA however, DTA for SQL2005 just drives me nut.
I still couldn't found soultion for that.
June 23, 2008 at 10:28 pm
TheSQLGuru (6/19/2008)
My advice is to never use DTA at all!! I have seen it do AWFUL things to databases. Well, actually that works in my favor since I get called in to clean up the mess. 😎
Can't help with the OP's issue as I haven't had any problems running DTA for SQL Server 2005. As far as not using it, I take what it offers with a grain of salt. Some of its recommendations are helpful, and at the least helps identify a starting point when trying to identify potential indexing issues or new indexes that are beneficial.
😎
June 23, 2008 at 10:59 pm
TheSQLGuru (6/19/2008)
My advice is to never use DTA at all!! I have seen it do AWFUL things to databases. Well, actually that works in my favor since I get called in to clean up the mess. 😎
Just curious - but have you ever spoken with Kimberly Tripp about DTA? She has a completely different opinion about using the tool :w00t:
Actually, I agree with you for the most part. But, Kimberly is very persuasive...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 24, 2008 at 5:37 am
I've been having good success with DTA on set based code. Sometimes it's recommendations don't actually speed anything up but, as some have said about other tools, it's a good starting point for me. Additional analysis on my part is sometimes required, but not very often.
On hidden RBAR like recurrsion... it doesn't do so good.
--Jeff Moden
June 24, 2008 at 7:24 am
DTA is like any other tool: if you KNOW how it works and how to use it it can be an effective device. But I have had two clients now who have simply thrown profile runs at it and implemented everything they got back and it was a disaster both times. And it is simply astounding how many indexes it will create (very similar ones in numerous cases) and an AMAZING amount of INCLUDEd columns. Multiple indexes with over 70% of table's columns included, >10X index size on a table compared to data size, etc, etc. Other clients have done smaller cycles of the same thing.
One client's performance is so bad now for DML actions that we are actually going to drop every index on every table (except for PK, unique, FK and some obvious single column ones) and basically start the index analysis process from scratch. Unfortunately they didn't accept the default names that DTA provided with so I can't use that as a mechanism to pick the ones to drop. What a fun week that is gonna be! 😎
For me, DTA in the hands of the average user is like Russian Roulette with 4.73 bullets in the gun. 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 24, 2008 at 10:13 am
TheSQLGuru (6/24/2008)
DTA is like any other tool: if you KNOW how it works and how to use it it can be an effective device. But I have had two clients now who have simply thrown profile runs at it and implemented everything they got back and it was a disaster both times. And it is simply astounding how many indexes it will create (very similar ones in numerous cases) and an AMAZING amount of INCLUDEd columns. Multiple indexes with over 70% of table's columns included, >10X index size on a table compared to data size, etc, etc. Other clients have done smaller cycles of the same thing.One client's performance is so bad now for DML actions that we are actually going to drop every index on every table (except for PK, unique, FK and some obvious single column ones) and basically start the index analysis process from scratch. Unfortunately they didn't accept the default names that DTA provided with so I can't use that as a mechanism to pick the ones to drop. What a fun week that is gonna be! 😎
For me, DTA in the hands of the average user is like Russian Roulette with 4.73 bullets in the gun. 😀
Yeah, gotta agree with you on that one and I have seen the same thing. It's the same thing I have seen with using the missing index dmv's in SQL Server 2008. Performance Dashboard will give you a very nice report - but, do you really need to implement a non clustered index that includes almost every column in the table?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 24, 2008 at 12:47 pm
TheSQLGuru (6/24/2008)
DTA is like any other tool: if you KNOW how it works and how to use it it can be an effective device. But I have had two clients now who have simply thrown profile runs at it and implemented everything they got back and it was a disaster both times. And it is simply astounding how many indexes it will create (very similar ones in numerous cases) and an AMAZING amount of INCLUDEd columns. Multiple indexes with over 70% of table's columns included, >10X index size on a table compared to data size, etc, etc. Other clients have done smaller cycles of the same thing.One client's performance is so bad now for DML actions that we are actually going to drop every index on every table (except for PK, unique, FK and some obvious single column ones) and basically start the index analysis process from scratch. Unfortunately they didn't accept the default names that DTA provided with so I can't use that as a mechanism to pick the ones to drop. What a fun week that is gonna be! 😎
For me, DTA in the hands of the average user is like Russian Roulette with 4.73 bullets in the gun. 😀
Oh yeah.... I absolutely forgot about DBA's that aren't and casual users that have heard indexes = speed/performance...
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply