How to find maximum number query executed in sql server

Pramod_Thakur · June 20, 2019, 9:35am

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

warkolm · June 20, 2019, 9:42am

Welcome!

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

Pramod_Thakur · June 20, 2019, 9:48am

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": [
      "2019-06-20T09:18:25.680Z"
    ]
  },
  "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": [
    1561022305680
  ]
}

warkolm · June 20, 2019, 9:53am

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

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

Pramod_Thakur · June 20, 2019, 10:00am

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.

warkolm · June 20, 2019, 10:01am

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.

Pramod_Thakur · June 20, 2019, 10:05am

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

warkolm · June 20, 2019, 10:06am

How do you ingest data into Elasticsearch?

Pramod_Thakur · June 20, 2019, 10:09am

using entity framework,seriog and nest in .net

warkolm · June 20, 2019, 10:10am

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/

Pramod_Thakur · June 20, 2019, 10:15am

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.

forloop · June 22, 2019, 3:31am

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?

Pramod_Thakur · June 24, 2019, 7:28am

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

system · July 22, 2019, 7:28am

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