SQL Server Performance

Best Options for my Server

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by SQLDBAS, Mar 26, 2007.

  1. SQLDBAS New Member

    Hello,
    Here are my server options. I am using linked servers. Basically Server (B) gets the data from Primary server using synonms and stored into local perm temp tables ( they are not # tables). I have 4 physical processor box ( hyper threaded ). 10 GB ram, SQL 2005 64 bit. If I run a report on Primart server (A) it is completing in 3 min . Same exact report run from Server B 27 min. I know there will be some delay in data transfer not that much. Is there any parameters I can check? Both server parameters are same. only thing is they communicate thru linked server ( SQLCLNI-OLEDB) .

    Your help would be really appreciated .
  2. MohammedU New Member

    Are you creating neccessary indexes on temp tables?

    What is data size you are copying?

    I read some where synonym copy the data row by row for linked server calls...
    Let me check it out...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. SQLDBAS New Member

    Thanks for the reply . Priamry server having all tables with proper indexes. Server B only uses synonym name to Primary server to get the data into temp tables.
    Where is the article synonym copies the data row by row. Please send the link once you find it. Thanks in advance
  4. MohammedU New Member

    I am able to find the info...
    But check the Graphycal execution plan on ServerB....

    You are copying the data from ServerA to ServerB using synonyms into temp tables? Don't you think you need to create indexes on temp tables?

    Check the execution time without copying the tables by commenting the copy part and see how much it is taking...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  5. SQLDBAS New Member

    We have dynamic sql SPs which will create the indexes on those temp tables. So, that is not a problems. It was working fine before somehow since last 2 days it is behaving pretty odd. I did dbcc freeprocchache also on both servers.
    Any other ideas?
  6. MohammedU New Member

    Are there any recent updates to OS or SQL?

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  7. SQLDBAS New Member

    NOPE! eXACT SAME VERSIONS.
  8. SQLDBAS New Member

    Folks ,
    Any ideas? We need to release this weekend we are having some perforamnce problems running reports thru linked servers. Please help !!!!
  9. Adriaan New Member

    Does your temp table have any indexes?
  10. SQLDBAS New Member

    Yes! I found that on the source server it is doing query processsing different if it is issued from Remote server.
    also it is using named pipes protocal. ( I talk to the other group the dont allow named pipes not sure how this is using)

    Index as accesspath do i need to set this optin on linked server? I did not setup When I set to 1 it was having some issues while accessing remote tables .

    Please give some thouts
    SQL 2K5 SP2 on both servers
  11. joechang New Member

    what exactly is the rational for having server B?

    am i to understand that you insert required data from primary into B temp tables for each query as needed?
  12. SQLDBAS New Member

    we do batch processing if opriamry server busy we would like to run more mbatches on server B. Server B having synonms for all tables pointing to Primary( SP,Views ,functions are local to remote srever)
  13. SQLDBAS New Member

    Joe,
    Do I need to set as Index as access path on the remote server ?
  14. joechang New Member

    i am thinking your are actually incurring penalties by doing this
    remote execution plans are not nearly as efficient as a local,
    you are also probably sending more data from the Primary,
    than if you just executed locally
    so you may not actually be offloading any work from Primary

    did you ever do a performance test with this model?
    or is this the test?
  15. SQLDBAS New Member

    When we test smaller data sets test was good so, I thought it will not affect anything.
    We need to go into prodction on Friday we try to run bigger reports it is screwing up. However, for smaller reports also it is taking little longer.
    Is there any options which I need to double check?
  16. joechang New Member

    what do you mean by good?

    did you compare cpu averaged over many calls
    between a local execute and a remote

    like i have said in many posts,
    disregard duration time for calls,
    focus on the cpu

    i think the results you are seeing is typical for remote execution
    ie, don't do it, except in very infrequeny cases
  17. SQLDBAS New Member

    Joe,
    I mean "good" =I got results from Primary Serve r/ Remote server running same job got expected results.

    Can You shed some knowledge what are all options I can check on both servers. ( Iknow there are too many factors to consider but something general ...)
  18. joechang New Member

  19. SQLDBAS New Member

    Joe thanks for the link.
    I tried all different options. The remote query execution plans are entirely differnt than local plans. for each record it doing a rpc call.. something like that..
    sp_execute 'Paramtervalue'
    SELECT "Tbl1008"."ID" "Col1961","Tbl1008"."Module_Code" "Col1970","Tbl1008"."Week" "Col1972","Tbl1008"."Qty" "Col1973","Tbl1008"."Value" "Col1978","Tbl1008"."Total_Price" "Col1979","Tbl1008"."UPC" "Col1980" FROM "<DBNAME>"."dbo"."<TNAME>" "Tbl1008" WITH (NOLOCK) WHERE "Tbl1008"."UPC"=@P1

    Thanks
  20. joechang New Member

    and that is why the performance characteristics of local and remote execution are so drastically different

    repent from remote execute
    return to local execute if only to save your (dba) soul

    (I am not license to practice religion)
  21. SQLDBAS New Member

    OK We are going into PLAN B. --Replication. (
    Hello Team ,
    Please help me out my brain stromed..
    What is best approach , we get data monthly about 2Gb we load them into publisher and push it to subscriber. Do you guys think this will have problem. ?
    I hvae my current prod database 65 GB. I am going to restore on the subscribing server before repl setup. Once I stratring setting up replication does it complain about this?
    Also, I have lot of identity colmsn do you guys thinks this is a problem?

    Thanks
  22. MohammedU New Member

    It depends on what type of replication you are setting up?
    How you are getting the data? why can't load on both servers 2 GB data instead of loading and replicating it?


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  23. SQLDBAS New Member

    we are setting up Transactional Repl

    We get data files & we will stop the distribution and load it on Publisher. Once everything turn on the distrbution. this the plan.
    Why we are not loading on two servers folks here having concerns manually loading keeping them in sync. any in sights would be good .
  24. SQLDBAS New Member

    I have request this question in SQL 2005 Replication Topic. Please reply to that topic. so, that All the information will be avialble in proper threads.

Share This Page