When you use the Query Store, you could miss load on your database if query’s are launched from another database. Query’s are stored in the database from where they are launched.
Let me show you this with an example. We have a query that is launched from the AdventureWorks2019 database. It doesn’t touch any resources in the AdventureWorks2019 database. It only queries tables in the AdventureWorks2017 database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/*Start from AdventureWorks2019*/ USE AdventureWorks2019 SELECT th.TransactionID ,th.ReferenceOrderID ,th.ReferenceOrderLineID ,th.TransactionDate ,th.TransactionType ,th.Quantity ,th.ActualCost ,po.ModifiedDate ,po.ProductID FROM AdventureWorks2017.Production.TransactionHistory AS th INNER JOIN AdventureWorks2017.Purchasing.PurchaseOrderDetail AS po ON th.TransactionDate = po.DueDate |
You’re gut-feeling my tell you that this query will be persisted in the Query Store on the AdventureWorks2017 database but it isn’t.
There’s no clear indication that warns your for cross database query’s. The only way (I know) is to look at the query itself or the execution plan.
Share via:
