SQL Server 2016 Query Store with Linked Servers

On servers [WIN-TC4FTRLFICH\SQL2016X1] run

create database davwill;
use davwill;
create table dbo.justdave1(a int, b int);
insert into dbo.justdave1 values(1,1);
ALTER DATABASE CURRENT SET QUERY_STORE = ON;

On linked server  [WIN-TC4FTRLFICH\SQL2016X2] run

create database davwill;
use davwill;
create table dbo.justdave1(a int, b int);
insert into dbo.justdave1 values(1,2);
ALTER DATABASE CURRENT SET QUERY_STORE = ON;

Then on the first server run:

use davwill;

select top 1 a.b,b.b from davwill.dbo.justdave1 a,
[WIN-TC4FTRLFICH\SQL2016X2].davwill.dbo.justdave1 b
WHERE a.a=b.a

Then check the query store on the first server database davwill

select top 3 * from sys.query_store_query_text order by query_text_id desc

query_text_id    query_sql_text    statement_sql_handle    is_part_of_encrypted_module    has_restricted_text
3    select top 3 * from sys.query_store_query_text order by query_text_id desc    0x09006E854375827B18C9B88B2280364E194F0000000000000000000000000000000000000000000000000000    0    0
2    select top 1 a.b,b.b from davwill.dbo.justdave1 a,  [WIN-TC4FTRLFICH\SQL2016X2].davwill.dbo.justdave1 b  WHERE a.a=b.a    0x0900E5E3F6B306B5BF3E00E6F014C0384CED0000000000000000000000000000000000000000000000000000    0    0
1    SELECT * FROM sys.database_query_store_options    0x09003CD7308C321E569EFC0C639317339F8C0000000000000000000000000000000000000000000000000000    0    0

Check the query store on the 2nd server database davwill:

select top 3 * from sys.query_store_query_text order by query_text_id desc

query_text_id    query_sql_text    statement_sql_handle    is_part_of_encrypted_module    has_restricted_text
2    select top 3 * from sys.query_store_query_text order by query_text_id desc    0x09006E854375827B18C9B88B2280364E194F0000000000000000000000000000000000000000000000000000    0    0
1    SELECT * FROM sys.database_query_store_options    0x09003CD7308C321E569EFC0C639317339F8C0000000000000000000000000000000000000000000000000000    0    0

Nothing appears in the query store on the linked server!

Advertisement

---