# How to find maximum number query executed in sql server **URL:** \ **Category:** Kibana\ **Created:** [June 20, 2019, 9:35am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636 "2019-06-20T09:35:27Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Pramod\_Thakur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pramod_thakur/32/48428_2.png) [@Pramod\_Thakur](https://discuss.elastic.co/u/Pramod_Thakur)\ **Post date:** [June 20, 2019, 9:35am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/1 "2019-06-20T09:35:27Z")
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. ![image](https://us1.discourse-cdn.com/elastic/original/3X/0/c/0cd157d85f1bbe6fbacace9384aafaad188c0d9f.png) Suppose this query appears 100 times then it will show like below. Query Count first query 100 second query 50 third query 40 ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [June 20, 2019, 9:42am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/2 "2019-06-20T09:42:33Z")
Welcome! 🙂 This will depend on if you are structuring the data that you are putting into Elasticsearch. What does a sample document look like? ---
**Author:** ![Pramod\_Thakur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pramod_thakur/32/48428_2.png) [@Pramod\_Thakur](https://discuss.elastic.co/u/Pramod_Thakur)\ **Post date:** [June 20, 2019, 9:48am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/3 "2019-06-20T09:48:41Z")
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 ] } ``` ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [June 20, 2019, 9:53am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/4 "2019-06-20T09:53:28Z")
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? ---
**Author:** ![Pramod\_Thakur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pramod_thakur/32/48428_2.png) [@Pramod\_Thakur](https://discuss.elastic.co/u/Pramod_Thakur)\ **Post date:** [June 20, 2019, 10:00am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/5 "2019-06-20T10:00:20Z")
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. ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [June 20, 2019, 10:01am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/6 "2019-06-20T10:01:54Z")
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. ---
**Author:** ![Pramod\_Thakur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pramod_thakur/32/48428_2.png) [@Pramod\_Thakur](https://discuss.elastic.co/u/Pramod_Thakur)\ **Post date:** [June 20, 2019, 10:05am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/7 "2019-06-20T10:05:18Z")
@warkolm how to extract into another field and run query for count. ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [June 20, 2019, 10:06am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/8 "2019-06-20T10:06:38Z")
How do you ingest data into Elasticsearch? ---
**Author:** ![Pramod\_Thakur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pramod_thakur/32/48428_2.png) [@Pramod\_Thakur](https://discuss.elastic.co/u/Pramod_Thakur)\ **Post date:** [June 20, 2019, 10:09am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/9 "2019-06-20T10:09:32Z")
using entity framework,seriog and nest in .net ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [June 20, 2019, 10:10am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/10 "2019-06-20T10:10:08Z")
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/ ---
**Author:** ![Pramod\_Thakur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pramod_thakur/32/48428_2.png) [@Pramod\_Thakur](https://discuss.elastic.co/u/Pramod_Thakur)\ **Post date:** [June 20, 2019, 10:15am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/11 "2019-06-20T10:15:17Z")
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. ---
**Author:** ![forloop](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forloop/32/9021_2.png) [@forloop](https://discuss.elastic.co/u/forloop)\ **Post date:** [June 22, 2019, 3:31am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/12 "2019-06-22T03:31:57Z")
> [@Pramod\_Thakur](#): > > How I will find which query I am executing most. So that I can improve my SQL query. 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? ---
**Author:** ![Pramod\_Thakur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pramod_thakur/32/48428_2.png) [@Pramod\_Thakur](https://discuss.elastic.co/u/Pramod_Thakur)\ **Post date:** [June 24, 2019, 7:28am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/13 "2019-06-24T07:28:40Z")
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 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 22, 2019, 7:28am UTC](https://discuss.elastic.co/t/how-to-find-maximum-number-query-executed-in-sql-server/186636/14 "2019-07-22T07:28:49Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.