SQL server integration provide the sql.query for debug

[lalit-satapathy]

The SQL server integration currently deletes the sql.query. This is largely to save space, since the query text can be large.

However, it may be required to keep this query for debug purposes in the generated events. We can enhance the package to not delete sql.query, as an optional scenario.

[harnish-elastic]

Observation

• MSSQL perfromance data stream uses the merge_results: true parameter. From this parameter, I observed that all the sql_queries results are being merged in single event and we are not receiving the sql.query field in the response. Refer

• If we need the sql.query field, we should have to do merge_results: false or remove merge_results: true from the handlebars. After that we need to make sql.query field as dimension: true as well we will receive single events from each query. Please refer the following screenshot that having 18 events from 18 queries in single interval.

• We need to remove the remove processor as it will never execute.

Question

• If we do merge_results: false or remove merge_results: true it will publish the 18 events in single interval which decrease the readability and increase the number of events respect to time interval. Please refer the attached file. Let me know if you still want sql.query field.
  without_merge_result.json

[lalit-satapathy]

If we do merge_results: false or remove merge_results: true it will publish the 18 events in single interval which decrease the readability and increase the number of events respect to time interval. Please refer the attached file. Let me know if you still want sql.query field.

CC: @shmsr to summarise the plan to change the SQL beats module, to preserve the query in merge results scenario.

[shmsr]

Apologies for not replying to this earlier.

The "query" field is of keyword type and natively supports arrays according to Elasticsearch. Creation of event and publishing is happening here (see query field).

For example, see the tags here. It is an array of tag i.e., []tag and we publish it to Elasticsearch:

Also, the change is just 3 lines from what I saw. As for when merge_results is true, we accept multiple queries. So, we should collect those queries and put them in query field as more than one values is supported in ES.

I will send a patch soon.

[shmsr]

Related PR is merged for 9.x and 8.x branch. The changes will be available in next 8.x minor release. Do we need to backport it; any specific requirements? For 9.x, it will be available starting from 9.0.

Next 8.x minor release is 8.18.

[harnish-elastic]

Test scenarios after beats PR merged. I have tested two scenarios for this PR, with preserve_sql_queries tag and without preserve_sql_queries tag. Everything working as expected.

With preserve_sql_queries tag

 "mssql": {
      "metrics": {
        "active_temp_tables": 0,
        "batch_requests_per_sec": 1435,
        "buffer_cache_hit_ratio": 58,
        "buffer_checkpoint_pages_per_sec": 70,
        "buffer_database_pages": 2894,
        "buffer_page_life_expectancy": 2885,
        "buffer_target_pages": 1196032,
        "compilations_per_sec": 516,
        "connection_reset_per_sec": 480,
        "instance_name": "MSSQLSERVER",
        "lock_waits_per_sec": 3,
        "logins_per_sec": 314,
        "logouts_per_sec": 311,
        "memory_grants_pending": 0,
        "page_splits_per_sec": 17,
        "re_compilations_per_sec": 2,
        "server_name": "a5b2fa4d6ecc",
        "transactions": 0,
        "user_connections": 2
      },
      "query": [
        "SELECT @@servername AS server_name, @@servicename AS instance_name;",
        "SELECT cntr_value As 'active_temp_tables' FROM sys.dm_os_performance_counters WHERE counter_name = 'Active Temp Tables' AND object_name like '%General Statistics%'",
        "SELECT cntr_value As 'batch_requests_per_sec' FROM sys.dm_os_performance_counters WHERE counter_name = 'Batch Requests/sec'",
        "SELECT cntr_value As 'buffer_cache_hit_ratio' FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio' AND object_name like '%Buffer Manager%'",
        "SELECT cntr_value As 'buffer_checkpoint_pages_per_sec' FROM sys.dm_os_performance_counters WHERE counter_name = 'Checkpoint pages/sec' AND object_name like '%Buffer Manager%'",
        "SELECT cntr_value As 'buffer_database_pages' FROM sys.dm_os_performance_counters WHERE counter_name = 'Database pages' AND object_name like '%Buffer Manager%'",
        "SELECT cntr_value As 'buffer_page_life_expectancy' FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy' AND  object_name like '%Buffer Manager%'",
        "SELECT cntr_value As 'buffer_target_pages' FROM sys.dm_os_performance_counters WHERE counter_name = 'Target pages' AND  object_name like '%Buffer Manager%'",
        "SELECT cntr_value As 'compilations_per_sec' FROM sys.dm_os_performance_counters WHERE counter_name = 'SQL Compilations/sec'",
        "SELECT cntr_value As 'connection_reset_per_sec' FROM sys.dm_os_performance_counters WHERE counter_name = 'Connection Reset/sec' AND object_name like '%General Statistics%'",
        "SELECT cntr_value As 'lock_waits_per_sec' FROM sys.dm_os_performance_counters WHERE counter_name = 'Lock Waits/sec' AND instance_name = '_Total'",
        "SELECT cntr_value As 'logins_per_sec' FROM sys.dm_os_performance_counters WHERE counter_name = 'Logins/sec' AND object_name like '%General Statistics%'",
        "SELECT cntr_value As 'logouts_per_sec' FROM sys.dm_os_performance_counters WHERE counter_name = 'Logouts/sec' AND object_name like '%General Statistics%'",
        "SELECT cntr_value As 'page_splits_per_sec' FROM sys.dm_os_performance_counters WHERE counter_name = 'Page splits/sec'",
        "SELECT cntr_value As 're_compilations_per_sec' FROM sys.dm_os_performance_counters WHERE counter_name = 'SQL Re-Compilations/sec'",
        "SELECT cntr_value As 'transactions' FROM sys.dm_os_performance_counters WHERE counter_name = 'Transactions' AND object_name like '%General Statistics%'",
        "SELECT cntr_value As 'user_connections' FROM sys.dm_os_performance_counters WHERE counter_name= 'User Connections'",
        "SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name like 'Memory Grants Pend%'"
      ]
    },
    "tags": "preserve_sql_queries"

Without preserve_sql_queries tag

"mssql": {
      "metrics": {
        "active_temp_tables": 0,
        "batch_requests_per_sec": 1876,
        "buffer_cache_hit_ratio": 62,
        "buffer_checkpoint_pages_per_sec": 70,
        "buffer_database_pages": 2918,
        "buffer_page_life_expectancy": 3382,
        "buffer_target_pages": 1196032,
        "compilations_per_sec": 607,
        "connection_reset_per_sec": 751,
        "instance_name": "MSSQLSERVER",
        "lock_waits_per_sec": 3,
        "logins_per_sec": 382,
        "logouts_per_sec": 380,
        "memory_grants_pending": 0,
        "page_splits_per_sec": 17,
        "re_compilations_per_sec": 2,
        "server_name": "a5b2fa4d6ecc",
        "transactions": 0,
        "user_connections": 2
      }
    }

[shmsr]

Thanks @harnish-elastic for validating this.

[harnish-elastic]

Both PRs are approved and merged. Closing as completed!
#12212
#12216