December 10, 2009 at 2:12 pm
I upgraded a server from 2000 to 2008. For the most part, everything has gone fine. There is one issue. We have a number of long running queries that are used in reports. These queries run overnight and select their results into tables that the reports feed off of. Most of these queries run fine on 2008. There are two in particular, though that are waaay slower - they went from 30 seconds or so on average to over an hour. Any idea why this would happen or how I can fix? I did rebuild all the indexes on the affected database and also updated statistics. Anything else I should do? Again, other queries are fine, including some that used to take longer than these two.
I did try the "Database Engine Tuning Advisor" - and that recommended a lot of indexes. I'm not familiar with the DETA - I'm used to the Index Tuning Wizard in SQL 2000. The DETA recommended a ton of indexes and I'm not sure how to tell which ones are most important. But I'm thinking this shouldn't even be neccessary since the queries had all the indexes they needed to run quickly on SQL 2000...
One thing that may be relevant - the "select into" selects from tables in one database into tables in another database. Did something change in SQL 2008 that would cause problems in the execution plans for this type of query?
Any advice?
- Dave
December 10, 2009 at 2:40 pm
You don't give very much information to work with. What do the queries look like? And how big are the data sources?
Give us some detail, and we might be able to help out.
Thanks . . .
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
December 10, 2009 at 2:58 pm
Where I'd start would be the execution plans on both servers. Compare those, see what's different.
If you can't get the old SQL 2000 execution plans, then look at the new ones and performance tune in the usual manner.
- 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
December 11, 2009 at 2:52 am
Hi,
Please read gail article added in my signature.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 11, 2009 at 3:38 am
Hi
If you haven't done so, you may need to update the statistics;
e.g
DBCC UPDATEUSAGE(<databasename>)
--and
exec sp_updatestats
-Matt
December 11, 2009 at 3:44 am
sp_updatestats and dbcc updateusage
They are completely unrelated and do totally different things.
sp_updatestats runs a statistics update (using UPDATE STATISTICS) across all the statistics in the database where the underlying table has had at least one row changed since the last statistics update.
DBCC UPDATEUSAGE fixes incorrect page-usage metadata in the storage engine. It was needed on SQL 2000 because there were bugs in the algorithms that were supposed to keep the page usage metadata correct. It should not be needed any longer on SQL 2005, as those bugs were supposedly fixed during the development of SQL 2005, though at least one persisted until SP3.
Gail Shaw
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 11, 2009 at 4:04 am
Hi
Appologies if my previous post was misleading. Yes, they are completely different, yet it seems it is still good practice to run these commands following and upgrade from sql2000 to sql2008. Check the Books online (sql2008) entry for DBCC UPDATEUSAGE :-
Best Practices
Always run DBCC UPDATEUSAGE after upgrading a database from SQL Server 2000. The page and row counts are corrected and are maintained thereafter.
December 11, 2009 at 7:20 am
I will tell share with you two issues from our recent migration from 2000 to 2008 enterprise.
1) Several of our 2000 tables were OVER-indexed or had the wrong indecis on them. Look carefully at the ones you REALLY need, drop the rest, and rebuild the ones you need.
2) THIS NEXT ONE IS A BIGGIE: Run your queries through SQL's database tuning advisor. It will blow your socks off. We got queries to go from 20 minutes to < 1 second, just by adding the stats and indices recommended by the advisor.
December 11, 2009 at 11:06 am
Hey, everyone. Thanks for all the replies!
Ok, first of all, sorry for the lack of details. I was hoping for some general known issues in upgrading from 00 to 08, and didn't want to burden anyone with having to read the queries that cause the problem.
I have already updated stats, as I said in the original post. I had not done an updateusage, so I just tried that and it didn't help.
I did start going on the route of using the Tuning Advisor, but stopped when the advisor recommended about 50 indexes. I'm not familiar with the tuning advisor, and need to get up to speed on it. But since the tables were already indexed using the advice of the SQL 2000 index tuning wizard, I think we have a reasonable amount of indexes. And I don't need these queries to scream - they run overnight.
So here are some details on one particular query that I have this problem with. It runs every night and selects into a temporary table on another database. It contains a whole pile of subqueries that are left outer joined together. In SQL 2000, it took about 9 minutes to run each night, and in 2008 it consistently takes about an hour and a half (!). I can attach the query if anyone wants to look at this, but it's pretty long and complex and again - it worked fine on 2000 and I'd rather not go down the road of trying to further optimize it, since there are several other queries that have this issue, and I think there is some general thing I need to do that would fix all of them. I'm attaching the 2000 and 2008 estimated execution plans.
One clue is that all these queries would sometimes bomb with the following error under 2000:
The fix Microsoft recommended is to do "option (maxdop 1)" in the query. I tried removing this from the query and it still is just as slow in 2008.
December 12, 2009 at 4:40 am
Hi,
Please can u send me the actual execution plan
the "select into" selects from tables in one database into tables in another database. Did something change in SQL 2008 that would cause problems in the execution plans for this type of query?
Nothing has changed in 2008,But the "select * into" wouldn't take whole object structure (Like index,constraints...)
Put the object name in the following query and check the indexes in both the DB's
select * from sys.indexes where OBJECT_id=object_id('objectname')
Server: Msg 8642, Level 17, State 1, Line 9 The query processor could not start the necessary thread resources for parallel query execution
-- Configuration's are same in both sql-2000 &2008 (like memory,disk...)
Finally I'd recommend run the perfmon and collect the counters & check it.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 14, 2009 at 11:02 am
Yeah had some similar issues ... ended up having to slap a bunch of index hints and join hints to resolve it. Updateusage all very well, but would knock my db offline for days... just not viable.
December 14, 2009 at 12:39 pm
The query plan generator in 2008 is very different from the one in 2000.
So the time difference is expected on big / complex queries.
I'd break the joined queries into individual queries and run them manually.
Use profiler and find the slow one(s).
Try using query hints to tell the optimizer to use different indexes.
Apparently there was an index that was being used by sql 2000 that sql 2008 doesn't like.
If you can find that index, you'll probably get back to the previous speed.
Sounds like the queries could use a general looking over - if the analyzer is suggesting 50 new indexes...
December 14, 2009 at 1:59 pm
Thanks for the replies!
Here are my responses:
RichB: I did try running UpdateUsage and it finished fairly quickly, but didn't solve my problem.
Muthukumaran: You asked that I attach the full execution plan, here it is.
John: I can't accept that this is due to any normal differences between 2000 and 2008. This particular query went from 9 minutes on 2000 to 90 minutes on 2008 - almost 10 times as slow and over an hour longer. If I break out the individual queries, the slowest takes 15 seconds and all but 3 or 4 of them are virtually instantaneous. I'm sure I can tune this specific query to run faster on 2008 but I'd ~much~ rather find the underlying cause for this dramatic slowdown. Again - this isn't the only query that has this issue and I'm not sure I can even find them all. My concern is that whatever is causing this problem is also causing other issues, perhaps more subtle ones, with other queries in our application. We have hundreds of queries we use, and I don't want to have to manually do comparisons pre and post 2008 on all of them.
I really appreciate all your help everyone, but I'm thinking I will have to open a case with Microsoft if nobody else has any ideas of where to look. Please let me know if you have any other suggestions, or if the execution plan I'm attaching here gives any hints.
Thanks!
- Dave
December 15, 2009 at 1:32 am
I apologize if I missed some of the information posted earlier, but as a general practice when upgrading, we do the following at my company:
1. DBCC CHECKDB([dbname]) with data_purity
2. DBCC UPDATEUSAGE([dbname]) -- I saw you ran this already, but this is usually required when there are data purity issues.
3. UPDATE STATISTICS [tableschema].[tablename] WITH FULLSCAN -- I saw you updated statistics, but if you used sp_updatestats, it would only use a sample of the data, thus not allowing SQL to build the most optimal query plan.
This query is database specific, but will generate all of the update statistics statements for the particular database:
select 'UPDATE STATISTICS ' + QUOTENAME(s.name) + '.' + quotename(t.name) + ' with fullscan'
from sys.tables t
inner join sys.schemas s
on t.schema_id = s.schema_id
4. Rebuild all indexes.
In my experience, the update statistics with fullscan has been the most effective when doing upgrades and seeing extreme slowness right after the upgrade.
Regards,
Steve
December 15, 2009 at 3:35 am
it's a petty the plan doesn't show all the batch SQL
Just for a starters ....
IMO the first query should have this alternative tested/compared with:
select submittal.recruiter_source as recr
, case opportunity.type
when 'Contract to Perm' then 'Contract'
else opportunity.type
end as [Type]
, dateadd(mm,datediff(mm, 0, activity_completed_date) ,0) m_y -- stick to datetime datatype as long as possible !!!
-- , datepart(month, activity_completed_date) as m
-- , datepart(year, activity_completed_date) as y
, count(*) as numinterviews
into utils.[dbo].[RecrMonthlyInterviews]
from productioned..rn_appointments rn_appointments
join productioned..submittal submittal
on submittal.submittal_id = rn_appointments.submittal_id
and rn_appointments_id = (
select min(rn_appointments_id)
from productioned..rn_appointments rn_appointments
where submittal_id = submittal.submittal_id
and rn_appointments.egi_activity_type in ( 'Phone Screen', 'Client Interview' )
)
join productioned..opportunity opportunity
on opportunity.opportunity_id = submittal.opportunity_id
-- NEVER USE a date THIS WAY !! USE CONVERT AND PROVIDE A FORMAT NUMBER !!!!
where activity_completed_date >= '7/1/2002'
and egi_activity_type in ( 'Phone Screen', 'Client Interview' )
group by submittal.recruiter_source
, case opportunity.type
when 'Contract to Perm' then 'Contract'
else opportunity.type
end
, dateadd(mm,datediff(mm, 0, activity_completed_date) ,0)
If you can, just post the full proc or sequence !
Your third query makes 83% of the batch ! Focus on that one !
- Avoid (runtime) conversions.
- stick to datetime datatype as long as you can
There is a condition "datepart(month,[ProductionED].[dbo].[Phone_Calls].[Call_Time] as [phone_calls].[Call_Time])=[Expr1012] AND datepart(year,[ProductionED].[dbo].[Phone_Calls].[Call_Time] as [phone_calls].[Call_Time])=[Expr1013]"
This consumes 7 % of your batch !!
Properly index you "[Utils].[dbo].[timesheetCommissions]" table ! It causes many FULL scans and may be optimized (+200000 rows)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply