SQL Server Performance Forum – Threads Archive
Best Options for my Server
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 .
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
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
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
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?
Are there any recent updates to OS or SQL? MohammedU.
Moderator
SQL-Server-Performance.com
NOPE! eXACT SAME VERSIONS.
Folks ,
Any ideas? We need to release this weekend we are having some perforamnce problems running reports thru linked servers. Please help !!!!
Does your temp table have any indexes?
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
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?
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)
Joe,
Do I need to set as Index as access path on the remote server ?
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?
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?
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
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 …)
i suggest you follow the steps in:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19595 for local execute and remote execute
before looking for magic settings
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"[email protected] Thanks
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)
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
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
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 .
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.
]]>