question

MichaelMacGregor-8223 avatar image
0 Votes"
MichaelMacGregor-8223 asked ErlandSommarskog answered

Object ID in query_store_query DMV doesn't exist? Where'd it go?

I'm querying the query store DMVs directly but the object_id in the query_store_query DMV is returning a value that can't be found. It isn't 0 so why does it return a value that doesn't seem to exist? Could this be due to the fact some queries are coming from Entity Framework and is creating temporary objects? Clutching at straws here. Any help is appreciated.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

An obvious reason is that the object in question have been dropped. But I don't think Entity Framework creates temporary objects.

Something I haven't tested, but what if there is a query that calls a function in a different database? How is this recorded? (If you make a call to a stored procedure in a different database, I would expect that call to be recorded in Query Store for that database.)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichaelMacGregor-8223 avatar image
0 Votes"
MichaelMacGregor-8223 answered

You'd think it was obvious, eh, but this was after load testing on the database in question, with no changes to either tables, stored procs, UDFs, etc. No cross-database functionality at all.

It's left me scratching my head as to what's going on.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Maybe you should re-run the load test, but now with a DDL triggers that logs eventdata()? to a table?

When you look up a plan in sys.dm_exec_plan_attributes, you will find an objectid also for ad-hoc queries, which is the internal hash of the query text. However, I don't think think this hash is saved to Query Store, and nor does the documentation suggest that this is the case.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.