How to find maximum number query executed in sql server

I am logging all my SQL inline query in elastic search. Now I have the requirement to improve performance of my SQL query. How I will find which query I am executing most. So that I can improve my SQL query. Maybe my where condition varies in SQL query. My elastic index naming is abc-current-date.
It means each day I am creating a new index for that.

Suppose this query appears 100 times then it will show like below.

Query Count
first query 100
second query 50
third query 40

Welcome! :slight_smile:

This will depend on if you are structuring the data that you are putting into Elasticsearch. What does a sample document look like?

My whole document look like

  "_index": "abc-2019.06.20",
  "_type": "logevent",
  "_id": "0-oudGsBmkAMnCR7H87I",
  "_version": 1,
  "_score": null,
  "_source": {
    "@timestamp": "2019-06-20T14:48:25.6803265+05:30",
    "level": "Information",
    "messageTemplate": "{@sqlQuery}",
    "message": "DbExecutionLogContext { ContextType: \"GedisBuDataContext\", Command: \"SELECT \r\n\\\"Extent1\\\".\\\"OFFER_NO\\\" AS \\\"OFFER_NO\\\", \r\n\\\"Extent1\\\".\\\"BUILD_NO\\\" AS \\\"BUILD_NO\\\", \r\n\\\"Extent1\\\".\\\"LINE_NO\\\" AS \\\"LINE_NO\\\", \r\n\\\"Extent1\\\".\\\"MODEL\\\" AS \\\"MODEL\\\", \r\n\\\"Extent1\\\".\\\"CLASS\\\" AS \\\"CLASS\\\", \r\n\\\"Extent1\\\".\\\"VK_UNIT\\\" AS \\\"VK_UNIT\\\", \r\n\\\"Extent1\\\".\\\"DESCRIPTION\\\" AS \\\"DESCRIPTION\\\", \r\n\\\"Extent1\\\".\\\"QTY_ORDERED\\\" AS \\\"QTY_ORDERED\\\", \r\n\\\"Extent1\\\".\\\"SELLING_PRICE\\\" AS \\\"SELLING_PRICE\\\", \r\n\\\"Extent1\\\".\\\"TOTAL_TAX\\\" AS \\\"TOTAL_TAX\\\", \r\n\\\"Extent1\\\".\\\"ORIGINAL_TAG_NUMBER\\\" AS \\\"ORIGINAL_TAG_NUMBER\\\"\r\nFROM \\\"AP\\\".\\\"GEDIS_ORDER_LINES\\\" \\\"Extent1\\\"\r\nWHERE (\\\"Extent1\\\".\\\"OFFER_NO\\\" = :p__linq__0)\", Parameters: [OracleParameter { ArrayBindSize: null, ArrayBindStatus: null, CollectionType: None, DbType: Int64, Direction: Input, IsNullable: False, Offset: 0, OracleDbType: Int64, OracleDbTypeEx: Int64, ParameterName: \"p__linq__0\", Precision: 0, Scale: 0, Size: 0, SourceColumn: \"\", SourceColumnNullMapping: False, SourceVersion: Current, Status: Success, UdtTypeName: \"\", Value: 90235033 }], CommandType: Text, CommandException: null, ElapsedMilliseconds: 259 }",
    "sqlQuery": {
      "_typeTag": "DbExecutionLogContext",
      "ContextType": "GedisBuDataContext",
      "Command": "SELECT \r\n\"Extent1\".\"OFFER_NO\" AS \"OFFER_NO\", \r\n\"Extent1\".\"BUILD_NO\" AS \"BUILD_NO\", \r\n\"Extent1\".\"LINE_NO\" AS \"LINE_NO\", \r\n\"Extent1\".\"MODEL\" AS \"MODEL\", \r\n\"Extent1\".\"CLASS\" AS \"CLASS\", \r\n\"Extent1\".\"VK_UNIT\" AS \"VK_UNIT\", \r\n\"Extent1\".\"DESCRIPTION\" AS \"DESCRIPTION\", \r\n\"Extent1\".\"QTY_ORDERED\" AS \"QTY_ORDERED\", \r\n\"Extent1\".\"SELLING_PRICE\" AS \"SELLING_PRICE\", \r\n\"Extent1\".\"TOTAL_TAX\" AS \"TOTAL_TAX\", \r\n\"Extent1\".\"ORIGINAL_TAG_NUMBER\" AS \"ORIGINAL_TAG_NUMBER\"\r\nFROM \"AP\".\"GEDIS_ORDER_LINES\" \"Extent1\"\r\nWHERE (\"Extent1\".\"OFFER_NO\" = :p__linq__0)",
      "Parameters": [
          "_typeTag": "OracleParameter",
          "ArrayBindSize": null,
          "ArrayBindStatus": null,
          "CollectionType": "None",
          "DbType": "Int64",
          "Direction": "Input",
          "IsNullable": false,
          "Offset": 0,
          "OracleDbType": "Int64",
          "OracleDbTypeEx": "Int64",
          "ParameterName": "p__linq__0",
          "Precision": 0,
          "Scale": 0,
          "Size": 0,
          "SourceColumn": "",
          "SourceColumnNullMapping": false,
          "SourceVersion": "Current",
          "Status": "Success",
          "UdtTypeName": "",
          "Value": 90235033
      "CommandType": "Text",
      "CommandException": null,
      "ElapsedMilliseconds": 259
    "correlationId": "8a0fd14d-5799-4684-9c5a-59426c96a691",
    "ActionId": "05929118-3300-4092-975c-81bdc37da774",
    "ActionName": "Gii.TestHarness.QuoteSummary.Api.Controllers.BuildsController.Get (Gii.TestHarness.QuoteSummary.Api)",
    "RequestId": "0HLNL99531Q0H:00000001",
    "RequestPath": "/api/GB/offers/90235033/builds",
    "CorrelationId": null,
    "ConnectionId": "0HLNL99531Q0H",
    "MachineName": "W10H8JNPV2",
    "Application": "Product.Api",
    "Environment": "Development"
  "fields": {
    "@timestamp": [
  "highlight": {
    "sqlQuery.Command": [
      "@kibana-highlighted-field@SELECT@/kibana-highlighted-field@ \r\n\"Extent1\".\"OFFER_NO\" AS \"OFFER_NO\", \r\n\"Extent1\".\"BUILD_NO\" AS \"BUILD_NO\", \r\n\"Extent1\".\"LINE_NO\" AS \"LINE_NO\", \r\n\"Extent1\".\"MODEL\" AS \"MODEL\", \r\n\"Extent1\".\"CLASS\" AS \"CLASS\", \r\n\"Extent1\".\"VK_UNIT\" AS \"VK_UNIT\", \r\n\"Extent1\".\"DESCRIPTION\" AS \"DESCRIPTION\", \r\n\"Extent1\".\"QTY_ORDERED\" AS \"QTY_ORDERED\", \r\n\"Extent1\".\"SELLING_PRICE\" AS \"SELLING_PRICE\", \r\n\"Extent1\".\"TOTAL_TAX\" AS \"TOTAL_TAX\", \r\n\"Extent1\".\"ORIGINAL_TAG_NUMBER\" AS \"ORIGINAL_TAG_NUMBER\"\r\nFROM \"AP\".\"GEDIS_ORDER_LINES\" \"Extent1\"\r\nWHERE (\"Extent1\".\"OFFER_NO\" = :p__linq__0)"
  "sort": [

I've edited that for you to make it a bit easier to read. Please format your code/logs/config using the </> button, or markdown style back ticks. It helps to make things easy to read which helps us help you :slight_smile:

So are you looking to do a unique count on the Command field?

yes @warkolm. But my where clause will vary. i.e.

select * from users where id=1
select * from users where name ='pramod'

but when i see my report it should display like
select * from users - 2
because this query store 2 times in elastic search. Thanks.

They are two different requests though. They are the same verb against the same table, which might be more specifically what you are looking for.

If that is the case, then you will want to ideally extract that into another field to run a count against.

@warkolm how to extract into another field and run query for count.

How do you ingest data into Elasticsearch?

using entity framework,seriog and nest in .net

I'm not familiar with that sorry, but they should have the ability to parse out specific things and put them into their own field:value pairs/

But right now we are storing as key value pair as well in elastic search table.
Okay in other word in which format you want data. can you provide sample for that. Pls provide query as well so that if i will get data then i will perform that query for the expected result.

Can you elaborate on this? How are you looking to identify queries as being the same in order to group them? I guess you'd like to group by entire parameterized SQL query i.e. query body is exactly the same, except for the values supplied to parameters?

I just ran a filter { sqlQuery.Command:"exists"} and it throws 50 hits. I just want to write all 50 hits into a data table, and data table should display like
Query Count
query1 20
query2 15
query3 10
query4 5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.