{"id":104500,"date":"2024-11-07T23:37:57","date_gmt":"2024-11-07T23:37:57","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=104500"},"modified":"2024-11-07T23:37:58","modified_gmt":"2024-11-07T23:37:58","slug":"analyzing-kusto-query-history-in-fabric","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/analyzing-kusto-query-history-in-fabric\/","title":{"rendered":"Analyzing Kusto Query History in Fabric"},"content":{"rendered":"

We can consume a Kusto database in Fabric from many different places: Notebooks, semantic models, real time dashboards and more. Kusto register all queries sent by the consumers in the query history.<\/p>\n

Sometimes, either for logging purpose or to analyze and fix some bug, we need to identify the queries the database is receiving and executing.<\/p>\n

Checking the Query History in Kusto Database<\/h2>\n

The statement is simple:<\/p>\n

.show query<\/pre>\n
\u00a0<\/p>\n
However, a simple execution may generate an error. The result is too huge, and it will exceed the capacity.<\/p>\n
A simple solution is to filter by the query history by execution time:<\/p>\n
.show queries\n| where StartedOn > ago(2h)<\/pre>\n
\u00a0<\/p>\n
\u00a0<\/p>\n
\"A<\/p>\n
The Field you are Looking for<\/h2>\n
Usually what we are looking for is the query text. Most of the times we want to locate the precise text generated by Power BI visuals or applications to confirm if the query is being generated correctly or how to optimize it.<\/p>\n
This result comes in the field called Text<\/strong>.<\/p>\n
The differences are what queries you would like to find and how do you plan to do it.<\/p>\n
    \n
  • You may be looking for a query which caused an error message<\/li>\n
  • You may be looking for the most recent queries, after you simulated some test<\/li>\n
  • You may be looking for the most expensive queries to optimize<\/li>\n<\/ul>\n
    Looking for the cause of an error in Query History<\/h2>\n
    We can use the unique Ids in error messages to locate the precise query which caused the error message.<\/p>\n
    This is the ClientActivityId<\/strong>, which in the error messages appear as ClientRequestId<\/strong>. Filtering by this value we will locate the specific query causing the error.<\/p>\n
    .show queries\n| where ClientActivityId == \"KPBI;668bbe4d-1c57-4bd6-9e22-558f77c6eb3c;99a8b6e7-d544-421b-9299-6aa3b3c7abba;761ff106-9d7e-43fe-8d70-cfc8ea5a09cb\"<\/pre>\n
    \u00a0<\/p>\n
    This field can also be used as a filtering method to discover the queries we are looking for. The starting of the value tells us how the query was generated.<\/p>\n
    I couldn’t find a full documentation, but this is what I discovered about the ClientActivityId in the query history:<\/p>\n
      \n
    • KPBI: A query generated in Power BI<\/li>\n
    • KD2RunQuery: I believe this one is from Data Activator<\/li>\n
    • Kusto.Web.RTA.Dashboards: Real-Time Dashboards<\/li>\n
    • kustoSynapseSpark: Queries executed from a notebook<\/li>\n<\/ul>\n
      You can find queries related to these sources using the expresion startswith<\/strong> or !startswith<\/strong><\/p>\n
      .show queries\n| where ClientActivityId startswith \"Kusto.Web.RTA.Dashboards\"<\/pre>\n
      \u00a0<\/p>\n
      \u00a0<\/p>\n
      \"A<\/p>\n
      Looking for recent Queries<\/h2>\n
      Sometimes we make a test, a simulation from some source, and we want to retrieve the query which arrived in the server.<\/p>\n
      There are many fields we can use to filter and get the queries generated by our source. Check some interesting ones:<\/p>\n
      User:<\/strong> The username which sent the query. It’s the user used to authenticate in Kusto. This can vary a lot. Was the query executed interactively or through some resource?<\/p>\n
      For example, if the query was executed from a report, then it is sent by the semantic model. If it was from Power BI desktop, it may send the authentication of the logged in user. However, from the portal, it will send the authentication specified in the Cloud Connection.<\/p>\n
      Application:<\/strong> The identification of the application sending the query. The identification may not be so obvious, but it may help.<\/p>\n
      You can easily check the distinct values existing in these columns and identify which one corresponds to the source of your tests.<\/p>\n
      .show queries\n| distinct Application<\/pre>\n
      \u00a0<\/p>\n
      Looking for Failed Queries<\/h2>\n
      Sometimes our purpose is to locate the queries causing errors or a specific error<\/p>\n
      We can use the field State<\/strong> to filter queries according to the final result.<\/p>\n
      These are possible values for the field State<\/strong>:<\/p>\n
        \n
      • Completed:<\/strong> The query executed successfully<\/li>\n
      • Failed:<\/strong> The query failed<\/li>\n
      • Cancelled:<\/strong> The execution was cancelled in the middle<\/li>\n<\/ul>\n
        We can filter the queries by the Failed state and use the field FailureReason<\/strong> to identify what was the error.<\/p>\n
        FailureReason<\/strong> contains a message which already tells us some details about the error, but it’s also a link. If we use CTRL+Click on the link, we will see a popup message about the error.<\/p>\n
        High Performance Consumption<\/h2>\n
        A common need we may have about query history is to look for queries with high performance consumption.<\/p>\n
        We can search the queries and order the result in descending orders by one of the fields below and this will give a list of the highest consumption queries being executed.<\/p>\n
          \n
        • Duration<\/li>\n
        • TotalCPU<\/li>\n
        • MemoryPeak<\/li>\n<\/ul>\n
          The name of the fields already explains what they contain.<\/p>\n
          We can use Order By<\/strong> or Top<\/strong>, the result is similar:<\/p>\n
          .show queries\n| where StartedOn > ago(3d)\n| top 10 by Duration desc<\/pre>\n
          \u00a0<\/p>\n
          Deeper Query History Analysis<\/h2>\n
          There are other fields such as CacheStatistics, ScannedExtentsStatistics and ResultSetStatistics which allow us to analyze the query in much deeper detail. I will write more about these in another newsletter.<\/p>\n
          Summary<\/h2>\n
          Searching Kusto query history is an important step to debug problems and optimize queries<\/p>\n\n\n
          <\/p>\n","protected":false},"excerpt":{"rendered":"
          We can consume a Kusto database in Fabric from many different places: Notebooks, semantic models, real time dashboards and more. Kusto register all queries sent by the consumers in the query history. Sometimes, either for logging purpose or to analyze and fix some bug, we need to identify the queries the database is receiving and……<\/p>\n","protected":false},"author":50808,"featured_media":104503,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,159164],"tags":[126249,158997,101611,159057],"coauthors":[6810],"class_list":["post-104500","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blogs","category-microsoft-fabric","tag-kusto","tag-microsoft-fabric","tag-power-bi","tag-real-time"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104500","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=104500"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104500\/revisions"}],"predecessor-version":[{"id":104504,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/104500\/revisions\/104504"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/104503"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=104500"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=104500"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=104500"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=104500"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}