-2

Some queries are hitting my CPU extremely hard. sp_WhoIsActive reports that sp_OAMethod is the cause (the sql_text column points to it) and that it has of huge waits of the PREEMPTIVE_OS_GETPROCADDRESS wait type. Given that sp_OAMethod is a built-in stored procedure, how can I debug this?

I am on a 2019 version of SQL server, 15.0.something.

J. Mini
  • 1,161
  • 8
  • 32

1 Answers1

6

I would first stop using OLE automation entirely as, to put it bluntly, it's rife with issues. There are many better alternatives. Allowing someone to host a web server inside the SQL Server process isn't helping anyone.

GetProcAddress is a Windows API used to locate the entry point of a function inside a module. Since you're using OLE automation the "things" you've asked automation to do requires that it find and execute those "things", enter in this Windows API which is used everywhere in the Windows C/C++ community. The wait type is saying it's waiting for that.

To debug it further you'll want to enable and capture ETW tracing for SQL Server (such a wpr, xperf, logman) as a start to see what that may show. Checking the stacks for interlopers in kernel mode or long disk read times would be a start. I'm sure the usual suspects will rear their head.

From the bounty text:

The huge number of comments on the current answer suggest that there is much to learn about sp_OAMethod and its OLE Automation companions. I would like to see a detailed answer regarding precisely what issues they can cause. Attempts to find this on Stack Exchange have failed.

There is not enough space in the stack exchange database to exhaustively list all of the issues they could cause. I'm being somewhat hyperbolic, but also it's quite accurate. It's the same as asking, "Precisely what issues would an unknown bug in code cause?". It could be anywhere from "nothing" because that code path isn't exercised, to crashing, memory corruption, etc., it's just too wide of a question.

We've covered the major points so creating a summary list would be:

  • All of the issues that OLE and COM bring
  • All of the issues with allowing users to dynamically work with other dynamic objects not owned or known by the process
  • All of the issues around not having enough memory, scheduling, blocking, and anything else that running code not tested to run in tandem with would do, including any bugs in the OLE/COM code, Windows, and SQL Server, including any 3rd party items which already modify behavior (such as anti* software), drivers, etc.

The answer I have will answer your question. You actually now have TWO separate questions, one of which (the bounty text) I would call "tip of the iceberg" and most likely VTC as "Needs detail or clarity".

Sean Gallardy
  • 38,135
  • 3
  • 49
  • 91