April 20, 2011 at 1:17 am
Hi.
I have a query that takes a bout 2 seconds when I run it on server A locally, but when I run this query through server B with a linked server to server A, it takes more than 20 seconds and also CPU usage raises from 2% to 50% . sys.dm_os_waiting_tasks tells SPID is waiting on OLE DB wait type.I don't think anything is wrong with my Network.any help? thanks in advance
Pooyan
April 21, 2011 at 9:57 am
OLEDB wait's are the appropriate wait for linked activity; basically, SQL Server is waiting on the remote server to return the values. Could be a network bottleneck (or remote bottleneck), but likely it is the query.
A linked query will 'drag' large portions of the table over the link, depending on how the query is written. For example, if you join a local table with a remote table, SQL Server may need to drag the entire remote table over the network in order to apply the join on the local server to discard the non matching rows. This can be expensive.
Perhaps you can make a view on the remote server which applies much of the WHERE criteria so that a lot of row-discarding will occur ON the remote server (the view executing on the remote server with the remote servers command parser running and query execution engine, etc. reducing the number of rows involved before the data traverses the network) with the smaller result set being dragged over the network and processed by the local query execution engine.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 21, 2011 at 12:33 pm
I forgot to tell this query was working fine until 2 days ago but all of the sudden it takes 20 second to be executed.
Two tables are being join together both on the remote server.both tables have some indexes on them which are normally being used by Optimizer At least I'm sure when the query is running locally on remote server.the Statistics are automatically updated on the remote database and there is no Insert or Update on remote server except at midnight and Indexes are rebuilt after that.
The thing is ,is it possible that Optimizer decides not to use the indexes when I run the query through the Linked Server but use them when I run the query locally on the remote server. I can't test it until 2 days later. thanks
Pooyan
April 21, 2011 at 1:37 pm
Ah, that's a bit different then. if it's been working, then I would next check perfmon and calculate the network IO on BOTH boxes. It is possible that the local NIC is hardly used, but the remote NIC is full due to moving off some backups or something.
Also, make sure you check the estimated and actual execution plans, compare row counts between estimated and actual row counts, etc. Statistics are tricky. Having auto update stats doesn't cure everything. In fact, the stats don't get automatically updated until 20% of the rows have bee modified IF the table has 500 or more rows in it. So small tables may have stale stats, as can large tables if only 19% of the rows were changed or less.
Therefore, the execution plan is definitely the next step after checking the NIC throughput.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 22, 2011 at 8:01 am
1) as someone else said, update stats on all tables and try again
2) you may have reached what is known as a "tipping point", wherein the number of rows in a table causes the cost estimating section of the query optimizer to determine that a different query plan should be more efficient, leading to something that can actually take longer to run than the query plan with 1 less row in a particular table.
3) speaking of query plan - what does the plan look like?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 23, 2011 at 6:14 am
I updated the statistics on all related tables but nothing changed. The main query is like this: select feild1,field2,... from T1 inner join T2 on T1.key=T2.key inner join T3 on T2.key2=T3.key
I also checked the execution plan it's like before!
I don't think it's about Tipping point because there is only one NoneClustered index on T3 witch is covering the query and is being used by Optimizer.The query is also very highly selective:) T1 contains 152 million records and T3 about 15 millions but the result is about 60 records.
I also restored the database on another server but nothing changed 🙁 I really don't know what to do.
Pooyan
April 23, 2011 at 4:11 pm
T1 is quite large to drag over the network. In fact T2 is also, and you never mentioned how large T3 is. What is more, you didn't indicate which of these tables are remote. If either T1 or T2 is remote, then I can see your problem. Use a view on the remote system. GOTO 10.
We really can't help much more when we are in theory land. If you need more assistance, please post the actual table definitions for all tables involved, the index definitions for all indexes on all tables involved, and the query execution plan that you are seeing. We also need the actual query performing these joins so we can see the facts.
This will remove our guess work.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 25, 2011 at 3:40 am
Provide query Display estimated Plan , it will show where is the cost of query
Are you receiving ASYNC_NETWORK_IO wait type ?
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 25, 2011 at 5:33 am
There was a Network related problem.It's ok right now.thank u all especially you jim
pouyan
Pooyan
April 25, 2011 at 7:53 am
pooyan_pdm (4/25/2011)
There was a Network related problem.
You are welcome. I'm glad you found it.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply