What is this query, involving system views & index properties

Question

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?

Answer 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.

Answer 2

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!