2

https://www.brentozar.com/pastetheplan/?id=Bkg8vBVzb

What in the heck is this query from? I thought it may have been sp_BlitzIndex or sp_BlitzCache but I searched thru them and didn't see anything like it (looked for keywords like IgnoreDuplicateKeys or spatial_indexes). When I searched for is_hypothetical I got a hit on both sp_BlitzIndex and Ola Hallengren's IndexOptimize, but further eyeballing tells me those aren't the culprits because they're missing so many other pieces that this rogue plan has (like the spatial and xml stuff).

I noticed it because it's executed over 20k times per day and shows a lot of wait time in my monitoring tool, but then again it typically only shows waits between 1am and 7am, which is kinda around our maintenance window... I'm just really confused at this point, though, because I'm using Ola's solution for indexing and the occasional ad-hoc alter index all on some_table rebuild.

Any ideas?

Improve this question
4
  • 4
    If you're using a monitoring tool, that's probably where I'd start with a support call. It looks like a monitoring tool. Commented Jun 6, 2017 at 15:52
  • How did you capture the plan? Did you capture the connection information as well? If not, get that and start looking into the client-side server/IP. Hopefully you identify that it's a monitoring solution, application, or something else non-alarming. Commented Jun 6, 2017 at 16:21
  • 1
    If it's running that often, run a trace for a few minutes and you should be able to find it and see the client application name... Commented Jun 6, 2017 at 19:38
  • Using @JohnEisbrener's suggestion, I got the user/DB/program/machine-name, and it's NOT a recognized monitoring tool. It's an internal process of some kind (whether it's homegrown or just a local implementation of a monitoring-tool-look-alike remains to be seen). Commented Jun 22, 2017 at 14:50

2 Answers 2

Reset to default
1

Now that you know where this query is coming from, the next step to take will be based on if you think this process is critical or not.

If I were in your shoes, I would setup a Login Trigger that won't allow this connection in and see who complains or what tool starts erring out. Just a word of warning, a poorly defined Logon trigger can make your life maddening, but hopefully this example will do the trick. I modified an example of a different restrictive trigger based from the one found in this post:

CREATE TRIGGER application_limit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF HOST_NAME() IN (N'ServerNameOfAnnoyingQuery', N'PCNameOfAnnoyingQuery')
AND ORIGINAL_LOGIN() NOT IN ('sa', 'DOMAIN\username')
          BEGIN
            RAISERROR('Connection Denied.  Please contact the Database Administrator Group at [email protected] to resolve this issue.', 16, 1)
            ROLLBACK;
          ENDEND;

If you suspect the query does support a critical process, I would start reaching out to users who have profiles on the originating server in question and start asking them about processes they have used from there.

Finally, if you can't identify any useful information from users and you don't want to go so far as to create the logon trigger, I would then track it down by process-of-elimination. Start this by logging into the Server where this query originates from and slowly stop any running services (i.e. services.msc) that look suspect as well as review/disable any suspect Windows Scheduler jobs. Eventually I would hope you turn off the proper process and eventually figure out what's going on.

Hopefully this gives you some ideas on routes to take. Good luck and hopefully you figure it out.

Improve this answer
1

Mystery solved! By checking the login, host-machine, and program-name properties of the session, we narrowed it down. And it was indeed a Windows Scheduled Task, calling a .Net app.

This particularly nasty looking query is courtesy of MS SMO (SQL Management Objects), which is used by ScriptDB (the .Net app) to read schema for export to an SVN repo. Specifically, this part seems to be a "get index" type of command, although I have no more patience/desire to dive into the code and explicitly draw it out. So anyway, now we know!

PS: Thanks John!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.