| Author |
Topic |
|
jonbprime
Starting Member
4 Posts |
 Posted - 2004-12-10 : 11:32:41
|
| I have 2 SQL Server 2000 servers with what I think are identical DB’s in them (I copied the DB from one to the other). When I run a particular query, the execution plans are different (one is fast, one is very slow). The query involves a remote linked SQL Server (SRV5). My question is why are the plans differing, and what’s a good way to track down the reason? I am not an experienced DBA and inherited this code from a developer who left our company.Here is the query:SELECT DISTINCT b.CITY, a.NPA, b.STATEFROM [SRV5].NPANXX.dbo.NumAvail a LEFT OUTER JOIN FONEDATA b ON a.NPA = b.NPA AND a.NXX = b.NXXWHERE (a.NXX <> '000') AND (b.STATE = 'AZ')On the 2 servers I run the query, the FONEDATA table is local and appears to be identical between the 2 machines.Here are the execution plans:Server 1(fast): Sort(DISTINCT ORDER BY:([b].[CITY] ASC, [SRV5].[NPANXX].[dbo].[NumAvail].[NPA] ASC)) |--Hash Match(Inner Join, HASH:([b].[NPA], [b].[NXX])=([SRV5].[NPANXX].[dbo].[NumAvail].[NPA], [SRV5].[NPANXX].[dbo].[NumAvail].[NXX]), RESIDUAL:([b].[NPA]=[SRV5].[NPANXX].[dbo].[NumAvail].[NPA] AND [b].[NXX]=[SRV5].[NPANXX].[dbo].[NumAvail].[NXX])) |--Table Scan(OBJECT:([GlobalNPANXX].[dbo].[FONEDATA] AS [b]), WHERE:([b].[STATE]='AZ')) |--Remote Query(SOURCE:(SRV5), QUERY:(SELECT a."NXX" Col1017,a."NPA" Col1016 FROM "NPANXX"."dbo"."NumAvail" a WHERE a."NXX"<>N'000'))Server 2(slow…):Hash Match(Aggregate, HASH:([b].[CITY], [SRV5].[NPANXX].[dbo].[NumAvail].[NPA]), RESIDUAL:([b].[CITY]=[b].[CITY] AND [SRV5].[NPANXX].[dbo].[NumAvail].[NPA]=[SRV5].[NPANXX].[dbo].[NumAvail].[NPA]) DEFINE:([b].[STATE]=ANY([b].[STATE]))) |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[NXX], [b].[NPA])) |--Sort(ORDER BY:([b].[NXX] ASC, [b].[NPA] ASC)) | |--Table Scan(OBJECT:([GlobalNPANXX].[dbo].[FONEDATA] AS [b]), WHERE:([b].[STATE]='AZ')) |--Table Spool |--Remote Query(SOURCE:(SRV5), QUERY:(SELECT a."NPA" Col1008 FROM "NPANXX"."dbo"."NumAvail" a WHERE a."NXX"<>N'000' AND ?=a."NPA" AND ?=a."NXX"))Thanks,-Jon |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-10 : 13:06:35
|
| Have you tried running an UPDATE STATISTICS...WITH FULLSCAN on all the affected tables on both servers? Try that and see if the plans change. |
 |
|
|
jonbprime
Starting Member
4 Posts |
Posted - 2004-12-10 : 15:16:54
|
quote:
Originally posted by robvolk
Have you tried running an UPDATE STATISTICS...WITH FULLSCAN on all the affected tables on both servers? Try that and see if the plans change.
It tried this on the "slow" server (FONEDATA table) and it did not help. I don't want the to do it on the "fast" server because I would be in trouble if that made my fast server slow. It seems like it should make no difference what I do on the remote server referenced in the query (but I could be wrong about that).Any other ideas?-Jon |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-12-10 : 16:01:51
|
| How fast can fast be if it's doing a scan in the first place?Brett8-) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-10 : 16:07:42
|
| I forgot to add, you should do a DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS after you update the stats. That way SQL Server will flush data and plans from their caches and recompile the plans.Unless you update the stats on both servers, there's no way to truly compare the two plans. Worst case, if the fast server gets slow, then the query probably needs to be rewritten. Join hints may be required. I would take another look at how the linked servers are set up on each machine, because the remote query plan should not differ at all but it does. There might be a setting there that needs to be changed.Another thing: is the hardware on the 2 servers identical? Are the server settings and database configs the same? If not, they may be the real reason the query is slow on one and not the other. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-12-10 : 16:08:56
|
| Maybe you could add this index and then see what happensCREATE INDEX FONEDATA_IX99 ON FONEDATA (NPA, NXX, CITY, STATE)Brett8-) |
|
|
|
jonbprime
Starting Member
4 Posts |
Posted - 2004-12-10 : 18:36:03
|
| Ok Thanks for the info. As far as the speed, one takes 3 seconds, the other takes 30. The funny thing is, if the state parameter is 'CA' instead 'AZ' the query is fast on both servers, event though there is more California data in our tables! If this does not work, I may just wait until we move all of our DB's to the same server and fix the bad SQL...Thanks,-Jon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-10 : 18:47:22
|
| How was the copying of the database done? With backup/restore or sp_detach_db/sp_attach_db? Or through the import/export wizard?Tara |
|
|
|
jonbprime
Starting Member
4 Posts |
Posted - 2004-12-10 : 19:18:52
|
| Originally the copy was done with export/import. Then as a test I did a backup and restore to another server, but it was just as slow as the server with export/import.-Jon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-13 : 12:23:36
|
| Import/export does not grab your indexes or FKs. I would redo the backup/restore and do what Rob suggested in his first post on this database.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-12-13 : 12:44:45
|
| What are stats going to do for a query that's scanning the table?You need the index I gave you.(Then you might want to consider stats and a reorg)Brett8-) |
|
|
|
|
Execution plans differ?