August 15, 2008 at 2:28 pm
I have a SQL 2000 db that I detached and reattached in SQL 2005. Now some of the more queries are painfully slow. I have rebuilt the indexes but does not seem to help.
Any ideas of where to start looking? Thanks for your help.
Bob
August 15, 2008 at 2:38 pm
Did you update statistics with full scan?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2008 at 1:38 am
Also run DBCC UPDATEUSAGE - see BOL ---> Upgrading the Database Engine.
Check for bloking using the "activity monitor".
If your machine has several processors you may have trouble with the "max degree of parallelism" option. You may have to change this - in SQL 2005 you can do this on a "per query" using the "MAXDOP" query hint.
August 17, 2008 at 11:01 am
Did you leave your database in 2k compatibility mode?
If yes, try switching to 90.
Best Regards,
Chris Büttner
August 18, 2008 at 7:44 am
I think Jeffery has the prime suggestion.
If that doesn't address the issue, take a look at the query plans of the affected queries and see if they can be improved. The optimizer received a MAJOR overhaul, and there were some regressions. Oh, speaking of which, exactly what version of 2005 are you using. There is SP2 and 8 Cumulative Updates for that release. I usually recommend to my clients to go to CU6 (higher if they are receiving a particular issue fixed in 7 or 8).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 19, 2008 at 2:51 am
Stage I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all objects
exec sp_refreshview for all views
Stage II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all objects
exec sp_refreshview for all views
Stage III
Profiler
Query Hints
http://www.ITjobfeed.com
August 19, 2008 at 7:02 am
jvamvas (8/19/2008)
Stage I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all objects
exec sp_refreshview for all views
Stage II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all objects
exec sp_refreshview for all views
Stage III
Profiler
Query Hints
I think the update stats in stage 2 is redundant. For the indexes, the dbcc dbreindex does stats that are full scan. And stage 1 covered full scan updates for non-index stats.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 19, 2008 at 7:12 am
EDIT: Damn it, SQLGuru beat me to the punch 😛
Hi there,
jvamvas suggests doing some statistics updates and then index rebuilds.
May I just point out that by doing an index rebuild with DBCC REINDEX you will be updating statistics anyway so the double update statistics is redundant.
You could save a lot of time and effort by doing the DBCC UPDATEUSAGE and if things are still too slow, then try for the DBCC REINDEX when your next maintenance window comes around.
Please be aware that DBCC REINDEX will be removed from SQL SERVER at some point in the future and that ALTER INDEX ... REBUILD is the preferred method. DBCC REINDEX just has the nice side-effect (in your case) that it updates column stats for non-indexed columns too.
Regards
GermanDBA
Regards,
WilliamD
August 19, 2008 at 8:10 am
When we moved our 2000 db to 2005 we had problem with queries that uses more complex views (not that complex but not the simple one). Maybe you could look into that.
August 19, 2008 at 9:53 am
Please post an example of the queries and query plans from SQL Server 2000 and 2005. Until we see what exactly the query is doing, its difficult to determine a course of action.
September 5, 2008 at 6:51 am
Our team has actually avoided using views altogether. Time and time again, you can take the same SQL statement from a view and use it in the SP (instead of referencing the view) and the SP executes faster. Views are evil! 😛
September 5, 2008 at 6:55 am
mmmm
Interesting , I'm pretty sure view have the same execution plan as the same query run outside the view.
Do you have test data for us to examine this?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 5, 2008 at 9:24 am
You can do this on any of your SPs that use views; just replace the view reference with the select statement from the view. I know it doesn't make sense but it's happened so often that we just figured SQL Server handles it differently and we've moved on.
September 5, 2008 at 9:38 am
thanks for that. very interesting I must say.
The only time I can think that the view would be slower would be if you call a view with loads of underlying tables and loads of columns, however you only return a few of the columns.
Other wise I would except the view to run the same as the query that it holds.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply