locked
Performance when Migrating from 2000 to 2005 RRS feed

  • Question

  • Hi guys
    We are in the process of moving from SQL Server 2000 to 2005. In this process in general I have noticed that performance is better as a result of the move but in a couple of specific cases performance is about 10 time worse as a result of the move and i am wondering if anyone can tell me why.

    1) Should I be noticing that calling functions from within a where clause are slower in 2005.

    2) Has the and/or logic processing been changed between the different versions.

    3) Why does this segment of code run really slow in 2005 but really fast in 2000 (note, i know that its not nice looking but it is pre-existing code from before we came on board and there are more examples of these so its a bit of a change to go through and fix it all up to what it should be but i need to know why before i can move on and as i said i know its not nice and one should expect it to be slow but i specially need to know why it would run fine in 2000 and not on 2005):

     

    .....
    AND (Deleted = 0)
    AND (DATEDIFF(d, dbo.GetStartOfDate(ReviewedDate), dbo.GetStartOfDate(GETDATE())) = 3)
    OR (ProgressPointId = 32)
    AND (Deleted = 0)
    AND (DATEDIFF(d, dbo.GetStartOfDate(ReviewedDate), dbo.GetStartOfDate(GETDATE())) = 3)
    OR (ProgressPointId = 30)
    AND (Deleted = 0)
    AND (DATEDIFF(d, dbo.GetStartOfDate(ReviewedDate), dbo.GetStartOfDate(GETDATE())) = 3)

    ....

    Thanks for your help.
    Anthony

    Sunday, February 10, 2008 11:55 PM

Answers

  • Often, when databases are migrated, one forgets to rebuild the indexes -SQL 2005 uses different Query Optimizer processes and it is essential to rebuild the indexes and update the statistics.

     

    1. It depends, some querys will be slower. If your query operates slower, it may be worthwhile to explore the Query Plan to determine optimization efficiencies.

     

    2. That is mostly related to indexes and statistics, see above.

     

    3. Again, indexing and statistics. And since any indexing on ReviewedDate will most likely NOT be used, it may be avantageous to explore materializing the DATEDIFF() value and building an index on it. However, not knowing what dbo.GetStartOfDate is accomplishing, this is just a shot in the dark.

     

    If you wish to post the code in dbo.GetStartofDate, we 'may' be better able to help you.

     

     

    Monday, February 11, 2008 4:23 AM
  • There is quite a bit of 'noise' on the internet about the slower queries issue. You may wish to search with the following terms: migrating "sql server 2005" slower queries.

     

    This thread may help:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=850373&SiteID=1

     

    About #3, again, I don't know what you are doing with dbo.GetStartOfDate(). It may be that creating a 'computed' column, and building an index on that computed column would be more efficient than two calls to the same function on each row.
    Monday, February 11, 2008 4:50 AM

All replies

  • Often, when databases are migrated, one forgets to rebuild the indexes -SQL 2005 uses different Query Optimizer processes and it is essential to rebuild the indexes and update the statistics.

     

    1. It depends, some querys will be slower. If your query operates slower, it may be worthwhile to explore the Query Plan to determine optimization efficiencies.

     

    2. That is mostly related to indexes and statistics, see above.

     

    3. Again, indexing and statistics. And since any indexing on ReviewedDate will most likely NOT be used, it may be avantageous to explore materializing the DATEDIFF() value and building an index on it. However, not knowing what dbo.GetStartOfDate is accomplishing, this is just a shot in the dark.

     

    If you wish to post the code in dbo.GetStartofDate, we 'may' be better able to help you.

     

     

    Monday, February 11, 2008 4:23 AM
  • Cool thanks for the reply.
    Firstly to make sure that I have go the indexes rebuilt right what is the recommended/bet way of approaching this. As far as i can tell i have rebuilt all the indexes but what steps should i be going through?

     

    On item 1, is there any documentation that says "this is why your queries could be slower", also if I rebuild my indexes what in particular should I be looking for to know what the difference between 2000 and 2005 is?

     

    Lastly on item 3, what do you mean by the "materializing" of the DATEDIFF() value. Guessing you must be saying that there is a way to put an index on this value?? Is that correct?

    Thanks

    Anthony

    Monday, February 11, 2008 4:36 AM
  • There is quite a bit of 'noise' on the internet about the slower queries issue. You may wish to search with the following terms: migrating "sql server 2005" slower queries.

     

    This thread may help:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=850373&SiteID=1

     

    About #3, again, I don't know what you are doing with dbo.GetStartOfDate(). It may be that creating a 'computed' column, and building an index on that computed column would be more efficient than two calls to the same function on each row.
    Monday, February 11, 2008 4:50 AM