SQL Server Performance

SQL 2000 with linked 2005 server

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by MichaelB, Jul 6, 2007.

  1. MichaelB Member

    I know I have brought this up before, but I want to bring it up again.
    I have noticed a very obvious performance loss when we converted over a 2000 box to a 005 box. There were no real performance issues on the 2005 box, but more with other 2000 boxes that reference it in queries. This seems to be a known issue by the responses I got, but my question is if anyone has noticed any issues with 2005 to 2005? if we convert these boxes over to 2005 does the linked server performance improve? I can test this, but I am looking to the community to see if anyone has done this already?

    Thanks in advance!

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  2. satya Moderator

    What sort of performance hit you have observed between linked servers 2000-2005-2005?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. MichaelB Member

    Increased CPU on the 2000 side and response time has increased 2-4 times worse. These jobs run in the evening so it doesnt impact users.

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  4. satya Moderator

    Have you collected the stats using PERFMOn during that query execution, how about execution plan for those queries on both 2000 & 2005 servers?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. MichaelB Member

    no. sorry, I have not collected perfmon info. I do not have any of the 2005 dbs running on any 2000 servers at this time (minor compatibility issues - but still can, but havent). The way I noticed this is I am looking at a CPU monitoring tool we have that lets me see CPU usage change over time (over a year) and it definately changed at the conversion. I monitor procs running on the system (traces) and keep the info and found the procs that cross servers are the ones with the losses in performance (duration) and the ones that do not cross servers have better performance than with SQL 2000. Hope this is clear.

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  6. satya Moderator

    I feel like unless we have statistics to consider it is hard to say this is affected due to linked server or based on version. I would suggest to collect them as and when they are feasible within yoru environment, good ones to take around.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page