SQL Server Performance

slow sql server 2005 performance vs. 2000

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Lana, Oct 19, 2006.

  1. Lana New Member

    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
  2. joechang New Member

    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
  3. Lana New Member

    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
    CPU time = 5266 ms, elapsed time = 6032 ms on sql server 2000
  4. joechang New Member

    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
  5. jezemine New Member

    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 />
  6. vsmurali New Member

    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.

  7. Lana New Member

    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
  8. Lana New Member

    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)
    set @count = @count + 1
    set @unions = @unions + ',null'
    if @count = 99
    set @count1 = 0
    while (@count1 < 100)
    set @count1 = @count1 + 1
    print 'select ' + @unions
    print 'union'
    print 'select ' + @unions

    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

  9. MichaelB Member

    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
  10. MichaelB Member

    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.


    -- 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]

    -- Step 1. Initalize


    -- 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

    set @totalCurrent = 0
    set @totalCheckDB = 0
    set @totalUpdateUsage = 0

    declare curs3 cursor local fast_forward
    select distinct
    name not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW', 'northwind', 'pubs')
    open curs3

    fetch next from curs3 into @databasename

    while @@fetch_status = 0

    print 'Starting Processes for :' + @databasename + '**********************************'

    PRINT 'Started CHECKDB checks for '+ @databasename + ''
    set @start = GETDATE()
    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

    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
    Select table_name FROM information_schema.tables where table_type = ''BASE TABLE''

    open curs4

    fetch next from curs4 into @tablename

    while @@fetch_status = 0

    --******************* Reindex Tables *******************
    PRINT ''Reindexing Started For:'' + @TableName
    set @start = GETDATE()
    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
    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()
    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
    close curs3
    deallocate curs3

    Print 'CheckDB Time : ' + cast(@totalCheckDB as varchar(20))
    Print 'UpdateUsage Time : ' + cast(@totalUpdateUsage as varchar(20))

    -- Step 3. Clean up


    Michael B
    Sr. DBA

    "The fear of the Lord is the beginning of knowledge,
    but fools despise wisdom and instruction." Proverbs 1:7

Share This Page