September 25, 2008 at 1:31 pm
Nicholas Cain (9/25/2008)
Have you monitored the locks and lock escalations while the query is running?Have you performed a reindex recently and are the stats up to date?
How does the execution plan look?
Sorry, just throwing things out there that I can think of that might make a difference.
I can see the locks happening, by using that graphical utility monitor in SSMS, as well as using sp_lock and sp_who. I don't really know how to tell why it thinks it needs the full table lock, but I can see it happening.
Yes, the indexes/stats are up to date.
For the execution plan, I don't know what to make of it. The final result set is a combination of data from the remote source, and there are some sub-queries that get local data. For the local data, it's using all indexes just fine, but for the remote query all it says is "remote query - 9% cost relative to batch"
As far as the execution time goes, last week our network guru was able to use some packet monitor tools and determine that all but a few of seconds of the execution is because of the network latency our WAN has.
The Redneck DBA
September 25, 2008 at 1:43 pm
How current does the data need to be?
Could you create a denomarlized table containing the resultset that you would need for the query?
Would you be able to post an xml query plan?
September 25, 2008 at 2:32 pm
That query is basically creating a denormalized table on the branch servers from corporate data to be consumed. It needs to be current, as it's information about people making online payments.
Attached is the XML query plan. It looks yuckier than the query!
The Redneck DBA
September 26, 2008 at 2:44 am
i have same query which runs on other server...that also includes join.
So to make query faster put "REMOTE" word in between the INNER JOIN
for example :
select t.name,t1.cal from table as t inner REMOTE join table_1 as t1
on t.id = t1.id
i m sure your query will result faster ..
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 26, 2008 at 3:16 am
I had a similair problem a while ago. Some background first. We had a stored proc that joined two tables. One table on the local server another table on the linked server. This query would run for a couple of hours sometime(database handled about 250+ logins per second so it was busy)
Anyway what I did to resolve the issue was quiet simple. I placed the results of the select on the linked server table into a temp table then proceeded to join the temp table with the local table. Query dropped to about 15 second during a peek period.
So if you are joining tables and they are physically located on different server try get that information into a local temp table and see.
Hope that helps...
September 26, 2008 at 7:02 am
Hi
As Every one suggested, there were Quite a lot of solutions for this, i had the same problems, what i did is that run the Query on the server which has many tables to deal with( eg, say you are using ServerA with one table and ServerB with 5 tables, the run the Query on server B) then copy the one table from server A to a temp table on serverB index it if needed and then process and then do a update or insert into the servers that are required,
This will Clear the problem.
If you say that this SQL has been running for many years, then check the amount of data that the table has, it must have been increased , if not there might be some problems with the connection between the servers, if this has been changed then the performance of the Query will be changed as well.
Cheers
🙂
September 26, 2008 at 7:05 am
I really am not interested in improving the speed of the query...it can take 20 min. to run for all I care. I'm just trying to figure out why all of a sudden it is doing full table locks.
That remote join hint won't work...apparently that only applies to inner joins, and I have outer joins.
The Redneck DBA
September 26, 2008 at 8:23 am
Jason Shadonix (9/26/2008)
I really am not interested in improving the speed of the query...it can take 20 min. to run for all I care. I'm just trying to figure out why all of a sudden it is doing full table locks.That remote join hint won't work...apparently that only applies to inner joins, and I have outer joins.
How frequently does the data change? Could you set the isolation level to something different while the query runs?
August 6, 2013 at 3:02 pm
Sadly, SQL Server scan the table from linked server, thats why you cant do it quickly. I am looking for the answer too and when I find it, I will share it.
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply