March 24, 2003 at 6:05 am
I recently had a problem where stored procs that reference a linked SQL server were timing out.
After running a profile trace it became apparent that SQL statements (using 4 part naming convention) were in fact creating fat server side cursors on the target server and chewing up way too many CPU cycles and too much bandwidth.
Nowhere in the trace is any statement even resembling my code.
By comparison, an ADO recordset (SQLOLEDB,server side cursor) using the same query used 1/4 of the resources (rough guess based on perfmon spikes) and actually executed my carefully crafted SQL statement.
I don't know which provider the linked server is using, how can I check?
Can anyone explain how distributed querys really work and how I can execute T-SQL on a linked server?
Both machines are SQL2k SP3
Thanks
March 24, 2003 at 6:22 am
But in your sp are you using a cursor to execute any SQL command in the linked server?
I'm using linked servers all the time, and I didn't have any problem.
March 24, 2003 at 2:36 pm
There are no cursors declared in the sp or any of the test queries. Only T-SQL
April 3, 2003 at 11:03 am
Linked servers often will create cursors. Look through the documentation for Linked servers carefully and you will see statements such as:
"SQL Server uses IOpenRowset::OpenRowset on the base table and calls IRowsetChange::InsertRow to Insert new rows into the base rowset."
"IOpenRowset::OpenRowset - Opens and returns a rowset that includes all rows from a single base table or index". What this means is that a simple insert into will cause a select * on the remote table. Watch profiler, or the equivalent in db2 / oracle and you can clearly see the the select * being issued, even though you are only inserting 1 row. I try and never use linked server, but if i must do an insert I'll create a bogus view that contains no records.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply