Lucene offers several search modes that require special characters.
To limit the size of the files and avoid duplicating information, we chose not to store the full text of the query in Lucene instead, we store just an ID that we can use to extract it, and any other information about the query, from our repository. Retrieving the sampled query is then fast.
Lucene splits the text of the query into individual words, and we index top queries as they’re sampled, so this relatively intensive operation is carried out up front. We also considered using ElasticSearch or Solr, but either of these would have required the installation of an external service. We considered using SQL Server’s inbuilt Full Text Search functionality but had concerns about performance, and the fact that data would be stored in the repository. Lucene indexes data in the file system and searches those files when you perform a search.
SQL Monitor uses Lucene.NET to index query text. It should be make it much easier to find a particular query, or to find all queries referencing a particular table or view or calling a particular function, for example. So even though our 2.12 pm query isn’t in the top queries list, SQL Monitor has sampled it and stored it, so a search will find it. It will return any matching query that was in the top 50 queries at any sampling point in the interval being examined, according to any of the available metrics. SQL Monitor v12 now allows you to search the text of top queries. This repopulates and reorders the list each time, according to the selected metric such as CPU time or Logical reads. Finally, you could try reordering the table by different columns, to find it. For example, a query that ranked 42nd by physical reads at 2:12 pm and was never seen again would be unlikely to make the top 50 when considered over a 24-hour interval. You might also have needed to restrict the time range to a narrow window around when the query ran. You often needed to expand the list to display the top 50 queries instead of just the top 10. If you’re trying to identify which queries are the biggest culprits in a system that’s experiencing generalized performance problems, then the top queries list is a good place to start, since it identifies all the most ‘expensive’ user and system queries that ran on the instance, over the period. However, without a search facility, it was often harder to locate a specific query in the list, if it was not one of the longest running queries during that period. SQL Monitor doesn’t store every single query that is run on a particular instance, only those that exceed certain minimum threshold levels. By default, the list is populated by Duration (ms), meaning that the query with the longest average duration per execution is listed first. It presents this information in the top 10 queries table.
SQL Monitor keeps track of what queries are being run by regularly sampling SQL Server’s “query summary” DMV, sys.dm_exec_query_stats, and persisting the query execution statistics to its repository.