5

Problem:

We use entity framework (6.21) as our ORM manager. Our database is Azure Sql Database.

Because some of the parametrized queries (frequently used in our app) are slow on some of the inputs (on some input it runs 60 seconds on other input it runs 0.4 seconds)

We started investigate those queries using QueryStore and QueryStore explorer in MS SQL Management Studio (MSSMS -> Object Explorer -> Query Store).

We found out, that QueryStore stores two same (same sql query but different params - params are not even stored) queries as different queries (with different query_id). By different query I mean different row in table sys.query_store_query).

I checked this by looking into QueryStore tables:

    SELECT 
     qStore.query_id,
     qStore.query_text_id,
     queryTextStore.query_sql_text
     ROW_NUMBER() OVER(PARTITION BY query_sql_text ORDER BY query_sql_text ASC) AS rn 
    FROM 
    sys.query_store_query qStore
    INNER JOIN 
    sys.query_store_query_text queryTextStore 
    ON qStore.query_text_id = queryTextStore.query_text_id

Screenshot from MSSSM

I am not able to compare plans of those queries easily in MSSMS, because each query has its own associated plan.

Expected behaviour: I would assume that each subsequent run of same query with different parametres would result in either:

1/ re-use of existing plan or

2/ in creation of another plan based on passed params values...

Example:

The query would look like this (in reality queries are much more complex as they are generated by EntityFramework):

SELECT * FROM tbl WHERE a = @__plinq__

and it's two subsequent runs (with different params) would result in two rows in sys.query_store_query.

Question:

How can I make Azure to save queries with same text as same queries? Or am I missing something or is this expected behaviour?

Or more generally how to tune database queries if they are generated by Entity Framework?

How SQL Server Query Store considers two queries same or different?

Edit1: Update

Based on @PeterB comment (Adding a query hint when calling Table-Valued Function) we were able to solve our problem with slow queries on some params values (we added hint "recompile" on problematic queries).

Based on @GrantFritchey hint I checked context_settings, but there are still multiple rows in query_store table which have same query_sql_text and same context_settings_id but with different query_id .

So we still wonder how SQL Server Query Store consider two queries same or different?

videokojot
  • 499
  • 3
  • 12
  • 1
    This could be related to [SQL Server query hints](https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query). If so, then something like this might help: https://stackoverflow.com/q/26761827/1220550 – Peter B Jan 08 '18 at 16:34
  • Thank you very much - I will look into it. – videokojot Jan 08 '18 at 16:38
  • 1
    Check the sys.query_context_settings values to see if there are differences. This can result in the plan being in there twice. – Grant Fritchey Jan 08 '18 at 16:52
  • @PeterB Thank you - interception with recompile hint on problematic really helped (50 seconds -> 4 seconds) - so our problem is solved. Still do not understand why are same queries saved as different in QueryStore. Thank you. – videokojot Jan 09 '18 at 09:58
  • @GrantFritchey good idea, but there are still multiple rows in `query_store` which have same `query_sql_text` and same `context_settings_id` but different `query_id` . So i still wonder how SQL Server Query Store consider two queries same or different.... – videokojot Jan 09 '18 at 10:02
  • Without seeing the details, I'd be hard pressed to provide an answer. It will be a logical division though. I have yet to see, or hear about, simple arbitrary duplication. There are textual differences, something. – Grant Fritchey Jan 09 '18 at 12:44

1 Answers1

3

As for the different query entries, the key that Query Store uses for a query consists of:

  • query_text_id,
  • context_settings_id,
  • object_id,
  • batch_sql_handle,
  • query_parameterization_type

If any of these is different for a query it will generate a new entry in the query table. Note that batch_sql_handle is only populated for queries referencing temp tables. So you can check which of these values is different for the queries that you listed.

Currently there are no settings that control the way Query Store aggregates queries. The only way to make it treat them as same is to change your workload so that the fields listed above match. But alternatively probably better approach is that you write your own reporting queries that will aggregate queries and their statistics according to your needs.

MiodragRd
  • 31
  • 4