1

We are using Microsoft SQL Server Agent Jobs to execute stored procedures on the other database systems (like MySQL or Oracle).

Usually we run procedures via linked servers (over OLEDB and ODBC drivers) and queries like:

EXECUTE ('CALL DB.sp_MyProcedure') AT OTHER_DBS;

(Not very good method, but I don't have access to schedule other DBS.)

Well, I observe queries with sp_whoisactive and have seen high summary CPU consumption for these jobs — i.e. 2,000,000 in 30m query duration as OLEDB waiting.

What is the reason of high CPU consumption?

  • What is the SP doing . pulling data or pushing data to other RDBMS ? – Kin Shah Mar 22 '16 at 13:11
  • Linked server queries are not good at most optimizations,they only try by best basis,simple select which works on local server using index may work differently on remote – TheGameiswar Mar 22 '16 at 15:51
  • To trouble shoot CPU issues,here is whitepaper which lists down all possible causes and resolutions..msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx – TheGameiswar Mar 22 '16 at 15:52
  • @Kin The SPs doing... nothing! Or nothing connected with a calling server — I just execute remote SP, that have no result set or returning value or select from host (calling) server. – DmitryLogvinenko Mar 23 '16 at 5:17
  • @TheGameiswar See my answer above (SP has no returning result sets) – DmitryLogvinenko Mar 23 '16 at 5:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.