slow sql server 2005 performance vs. 2000 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

slow sql server 2005 performance vs. 2000

We are planning to upgrade to sql server 2005 in the nearest future. Upgrade advisor did not give any errors or warnings. Then I ran the Upgrade assistant from Scalability Experts, and it reported a couple of differences in query behaviour between 2000 and 2005. The query is a big report that returns results in an FOR XML EXPLICIT format. It has around 40 nodes and takes around 8 – 12 seconds to execute in Query Analyzer(sql server 2000 SP 4 standard edition). If I run the same report (same input) in MSSMS on the same exact database and the same server upgraded to sql server 2005 sp1 standard edition it takes 2 minutes and 22 seconds to display the xml. Our customers were complainig the report took too long to print (since there is a transform and rendering part that runs on the application server), now it will take 2 more minutes – THIS time is unacceptable! I was trying reindexing all tables, force recompile on all procedures, nothing helped. It looks like to select the 1-st node
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Root!1!Report],
NULL AS [T1!2!Attr1],
NULL AS [T1!2!Att2],… etc.
……
NULL AS [T40!40!AttrN] …total of 510 attributes
takes 3 seconds to display
What query hints could I use, how do I make the optimizer choose the same plan?
Has anybody seen the same behaviour?
Thank you in advance for your replies

before using hints
you need to learn how to interpret the execution plan
compare the 2000 and 2005 plans
it is better to use the Show Plan, instead of the Display Estimated Execution Plan
exection plans (both estimated and actual are the same, if I turn on statistics the number of logical reads and scan counts, etc… are the same. the only difference is parse and compile time.
It’s 137875 ms (CPU) and 141815(elapsed) on 2005
and
CPU time = 5266 ms, elapsed time = 6032 ms on sql server 2000
i am not inclined to believe the 2000 and 2005 plans are the same the estimated and actual plans are the same. what you are looking for is significant differences in the estimated rows & executions vs actual
This doesn’t address directly the issue that’s causing your perf problem, but if you do switch to 2005, I STRONGLY suggest dumping your FOR XML EXPLICIT query altogether. FOR XML EXPLICIT is almost impossible to maintain, as you probably are aware. <br /><br />In 2005, there is a much much nicer way to generate xml, called FOR XML PATH. it’s infinitely more maintainable. I bet you could rewrite your explicit query in 30mins using path, and it would be 80% fewer lines of code (and more performant too!)<br /><br />I have always thought the genius that came up with FOR XML EXPLICIT ought to be punished somehow… [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><br /><br /><hr noshade size="1">SqlSpec – a fast, cheap, and comprehensive data dictionary generator <br />for SQL Server 2000 and 2005 and Analysis Server 2005 – www.elsasoft.org <br />
Have you ever used SQL Diagnostics Manager from Idera? I had a similar situation wherein the E-biz team having "find the doctor" was suddenly running slow at random days and random times. I had idera monitoring the server and the good thing is that you can look back at what happened in the last 30 days and you can find out what happened in a specific date and at specific time? You can check which SQL uses the most resources and at what time? Top 10 worse sql statements, top 10 sqls having hard parse are areas I would look if I have issues. Long story short, it turned out the issue was not the Query which was causing the issue rather we had "Sharepoint" having "microsoft outlook" configured on it and whenever a user checks a large email with attachments it slowed down the server. My suggestion would be to compartment your problems into cpu, memory, db and eliminate which is not the root cause. The final one is your issue. Thanks
Murali
thank you all for your replies. Unfortunately I cannot use something that is not supported in sql server 20000, as we have more than 150 customer base, and some of them were asking us whether they can upgrade to sql server 2005. So the only condition for our upgrade is: it cannot be slower than in sql server 2000. This particular report is used probably every 2 to 5 minutes. It is considered a long – running report as it takes around 12 seconds to display one main record and all it’s 0 to many dependencies (being a foreign key in more than 40 different tables). In sql server 2005 it takes roughly 2.5 minutes. Once it runs in mssms once all the subsequent executions of this report with the same parameter or different ones execute in no time. The actual execution plans are very similar – same index seeks are performed on same number of tables. But in sql server 2005 steps include filter icons and an exessive number of compute scalar icons.(cost is 0% though) when there was only one compute scalar per step in 2000. There is no good way to compare the plans as the scale when you print them out is different on 2000 and 2005. It’s also very hard to compare with the set_show_plan option on as it looks different. I think the slow performance is attributed to the "for xml explicit" (union all and universal table…) constract but our report software expects it in this particular format. We also cannot maintain 2 different code bases. Could somebody give me some advise – the planned sql server 2005 roll-out date is december and I still don’t have an answer.
thank you

this is just an update, so if anybody has problems with FOR XML EXPLICIT in SQL Server 2005 or any UNION operations please be informed that there is a discrepancy between how it is handled in sql server 2000 and sql server 2005.
Try to run the following code, copy the result string in query analyzer and query editor and see for yourselves. declare @count int, @count1 int, @unions varchar(8000) set @count = 0
set @unions = ‘null’
while (@count < 100)
begin
set @count = @count + 1
set @unions = @unions + ‘,null’
if @count = 99
begin
set @count1 = 0
while (@count1 < 100)
begin
set @count1 = @count1 + 1
print ‘select ‘ + @unions
print ‘union’
end
print ‘select ‘ + @unions
end
end as a workaround until there is a fix we are inserting all selects into a table variable and then selecting out of this table for xml explicit.
thank you Lana
One thing… as far as comparing plans.. I would use SQL2005 Management studio and point it to both databases (yes, it works for both) and then you can print them out the same way<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> <br /><br />As far as performance, stay away from hints if you can (I have only 1 hint in my db and it works well<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> but normally SQL picks the best index. Also, it sounds like you have a lot of FK’s so I assume we are talking about a lot of left outer joins. I am unsure how many selects you are doing, but I would recommend doing each outer join to the main select seperately (like temp table or table variable – try both but dont forget to index the temp tables!) I dont have experience with XML EXPLICIT …sorry<br /><br />Cheers!<br /><br />Michael B<br />Sr. DBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7
One other thing, have you refreshed statistics? I would do that first (at night when not busy). I would also would use the fullscan option on them. since it is not a prod box YET I would do the fullscan. Also, below is a nice little proc you can run to get it up to speed. It may take a couple hours depending on your db size.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO –*****************************************************************
— Proc Name:mt_Database_Maintenance_Weekly

— Purpose:To Reindex entire DB

— Called By:Reindex Nightly Job

— Created By:Michael Berry

— Created on:10/30/2006

— Modified By:

— Modified Dte:

— Modified Rsn:
–***************************************************************** CREATE Procedure [dbo].[mt_Database_Maintenance_Weekly]
as
–******************************************************************

— Step 1. Initalize

–****************************************************************** SET NOCOUNT ON
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
–******************************************************************

— Step 2.Run reindex for each table in DB

–****************************************************************** –select * from ebs_common.dbo.cleanuptext
declare @databasename as varchar(200) declare @start datetime
declare @totalCurrent as int
declare @totalCheckDB as int
declare @totalUpdateUsage as int –initalize
set @totalCurrent = 0
set @totalCheckDB = 0
set @totalUpdateUsage = 0
declare curs3 cursor local fast_forward
for
select distinct
name
from
master.dbo.sysdatabases
where
name not in (‘master’, ‘msdb’, ‘model’, ‘tempdb’,’AdventureWorks’,’AdventureWorksDW’, ‘northwind’, ‘pubs’)
open curs3
fetch next from curs3 into @databasename while @@fetch_status = 0
begin print ‘Starting Processes for :’ + @databasename + ‘**********************************’ PRINT ‘Started CHECKDB checks for ‘+ @databasename + ”
set @start = GETDATE()
DBCC CHECKDB (@databasename) WITH NO_INFOMSGS
set @totalCurrent = cast((DATEDIFF(MILLISECOND, @start, GETDATE())/1000) as int)
–Print ‘******************* Completed CHECKDB for DB:’+ @databasename + ‘ in ‘ + @totalCurrent + ‘ Seconds *******************’
–add the number to the toal
set @totalCheckDB = @totalCheckDB + @totalCurrent exec(‘
declare @totalReindex as int
declare @totalStats as int
declare @totalCurrent as int set @totalReindex = 0
set @totalStats = 0 use ‘ + @databasename + ‘ declare @start datetime
declare @tablename varchar(300)
declare curs4 cursor local fast_forward
for
Select table_name FROM information_schema.tables where table_type = ”BASE TABLE” open curs4 fetch next from curs4 into @tablename while @@fetch_status = 0
begin
–******************* Reindex Tables *******************
PRINT ”Reindexing Started For:” + @TableName
set @start = GETDATE()
DBCC DBREINDEX(@TableName, ” ”,0) WITH NO_INFOMSGS
set @totalCurrent = cast((DATEDIFF(MILLISECOND, @start, GETDATE())/1000) as int)
— Print ”******************* Completed Reindex Of: ” + @TableName +” in ” + cast((DATEDIFF(MILLISECOND, @start, GETDATE())/1000) as varchar(50)) + ” Seconds *******************”
set @totalReindex = @totalReindex + @totalCurrent PRINT ”Updating Stats Started For:” + @TableName
set @start = GETDATE()
exec (”UPDATE STATISTICS [” + @TableName + ”] WITH SAMPLE 50 PERCENT”)
set @totalCurrent = (DATEDIFF(MILLISECOND, @start, GETDATE())/1000)
— Print ”******************* Completed Update Stats Of: ” + @TableName +” in ” + cast((DATEDIFF(MILLISECOND, @start, GETDATE())/1000) as varchar(50)) + ” Seconds *******************”
set @totalStats = @totalStats + @totalCurrent fetch next from curs4 into @tablename
end
close curs4
deallocate curs4 Print ”*************** Reindex Time : ”+ cast((@totalReindex) as varchar(20))
Print ”*************** UpdateStats Time ” + cast((@totalStats) as varchar(20))
‘)
PRINT ‘UpdateUsage ‘ + @databasename
set @start = GETDATE()
DBCC UPDATEUSAGE (@databasename) WITH NO_INFOMSGS
set @totalCurrent = cast((DATEDIFF(MILLISECOND, @start, GETDATE())/1000) as int)
–Print ‘******************* Completed UPDATEUSAGE for DB :’+ @databasename + ‘ in ‘ + cast((DATEDIFF(MILLISECOND, @start, GETDATE())/1000) as varchar(50)) + ‘ Seconds *******************’
set @totalUpdateUsage = @totalUpdateUsage + @totalCurrent fetch next from curs3 into @databasename
end
close curs3
deallocate curs3 Print ‘CheckDB Time : ‘ + cast(@totalCheckDB as varchar(20))
Print ‘UpdateUsage Time : ‘ + cast(@totalUpdateUsage as varchar(20)) –******************************************************************

— Step 3. Clean up

–******************************************************************
SET NOCOUNT OFF
Michael B
Sr. DBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7
]]>