April 8, 2010 at 1:22 pm
I agree you point there a logic for it but actually my query is running faster for bigger database(remote) when compared to smaller one. Both databases are on different server what would be the reason behind.
April 8, 2010 at 1:36 pm
Tara-1044200 (4/8/2010)
I agree you point there a logic for it but actually my query is running faster for bigger database(remote) when compared to smaller one. Both databases are on different server what would be the reason behind.
I completely understand, and I realize that my suggestion is not necessarily logical given that point. But I have definitely seen the optimizer do some strange this with remote queries, to the point that I would not be surprised if somehow on the smaller dataset the optimizer thinks that doing a table scan (or scans) against the remote database is more viable than using indexes, where as on the much larger dataset the optimizer uses the indexes correctly.
Part of the problem with remote queries is that you do not get a lot of insight into exactly what the Optimizer is doing. Your plan is a good example of that, since it just says "Remote Query", such that you have absolutely no insight as to what is happening there at all.
If the optimizer happens to be doing table scans against the remote table on the smaller dataset, and that table is joined to thousands or millions of times, where on the larger dataset it is doing seeks, you could definitely see an increase of hours on the smaller dataset.
It may not be the case, but it certainly would be worth a shot.
April 8, 2010 at 2:19 pm
Lets see we go with your theory, as thayt is unseen at remote side how actually execution plan is used, what would be the best bet for me to fix on the smaller database.
can i just rebuild and update stats on that, do you think it works ok, just a thought ?
April 8, 2010 at 2:28 pm
from the query plan only difference i see is a Table Spool(Lazy Spool) in the slow query which i dont see in fast query, could some interpret that for me?
April 9, 2010 at 6:48 am
I assume Table Spool is used to improve qry perofrmance, but looks like it is reverse in this scenarioa as it is used only in the slow running query.
April 9, 2010 at 8:10 am
I have also seen remote queries do wierd things in the optimiser. In addition multiple joins into a remote table will be relatively slow. How about this:
1. Create a temp table on the server where most of the work happens for each remote table you need data from.
2. Populate the temp table(s) with the datasets from the remote server.
3. Modify the query(ies) to use the temp tables instead of the remote server.
4. Run profiler while doing all of this, filtering by the SPID if running from Management Studio, and find out how long each part takes and compare the execution plans for each.
April 9, 2010 at 9:53 am
I am looking in a simple way... what ever may be the exceptions for remote queries when it is running faster for all other databases why only this database. Do i really have to troubleshoot from the query or look into that particular database which is in quetion, if so what settings i have to look into that database.
April 12, 2010 at 8:13 pm
Here is another thing i observed the slow query has a table spool which shows actual rows=1510403515 actual rewinds=46 but i dont see any table spool for fast query. how do i eliminate this part in slow qry ?
April 13, 2010 at 7:07 am
Tara-1044200 (4/12/2010)
Here is another thing i observed the slow query has a table spool which shows actual rows=1510403515 actual rewinds=46 but i dont see any table spool for fast query. how do i eliminate this part in slow qry ?
This again goes back to the remote query, since the table spool and the scalar computation you are talking about both come from that part of the query. The kinds of things I would try would be to just before this query runs, move the data (or at least some of the data) from Obbeaver.State_FL.dbo.HomeLook_USA over to the server that this is running on in a temp table. This is the optimizer doing strange things that a few of us noted earlier. The optimizer sees that remote query and it is not always completely sure how many rows it is dealing with. If you pull over that dataset or at least just a small amount of that data set into a temp table just before this query runs and then change this select to use that temp table, there is a very good chance it will remove that and things will run much faster.
You are connecting to this remote server in two places for this query so it is possible you may need to pull the data over from both tables ahead of time on OBbeaver in order to get this to perform better. But at first I would just try it from HomeLook_USA.
Just something as simple as
select column1, column2.....
into #HomeLook_USA
from Obbeaver.State_FL.dbo.HomeLook_USA
where ......--some stuff so you are not having to pull over the whole table
that way when you run your query you change it to
select distinct
a.Clmn10,a.Clmn9, EmpID, EmpName, Grp,
Title, b.LastName as provider_lastname, b.FirstName as provider_firstname, MI, Address1, Address2,
City, ST, ZipCode, PhoneNum, FaxNum, EmpType,
a.EmpID, EmpCode, Clmn6, Clmn7, Clmn8, Clmn11, Clmn12,
Clmn13, Clmn14, Clmn15, Clmn16, Clmn17,
Clmn18, Clmn19, Clmn20, Clmn21, Clmn22,FName,LName,
Employee,StateRevCode,[Description]
into #SameState
from #FL as a inner join #HomeLook_USA as b on a.Clmn10 = b.Clmn10
left join Obbeaver.State_FL.dbo.VwOffLook_USA as c on b.StateeRevCode = c.SCode
and a.EmpID = c.EmpID
where EmpType IN ('P','G') and a.Clmn10 > '000000006' and a.EmpID = 'FL'
Odds are good that will get rid of the table spool. No guarantees that will resolve the issue, but that is the sort of thing I would try in an attempt to resolve it.
April 13, 2010 at 8:27 am
From the types of questions you are asking Tara, and the amount of data you have and the complex environment, I strongly recommend you get a professional SQL Server tuning consultant in house for a week or perhaps two to review your apps and infrastructure and give you some mentoring on how to answer a lot of the questions you have yourself. There are MANY things that can cause your observed scenarios and you could go back and forth for weeks on a forum and not really find out what/why. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply