"Help people get complex data about SQL Server easily and understandably"

SMT Mission

Overview

  • SQL Monitoring & Performance Tuning tool designed by (in the field) performance tuners developed in-house for administrators, developers, and tuners
  • Running in 7x24 always-on mode
  • Accessible by Customer‘s internal team and Woodler team(optional)
  • Handy in the development/testing phase of the applications
  • Perfect for root cause analysis (RCA)
  • Automated alerting and notification
  • Heart of SQL Server administration
  • Custom reports connecting different metrics into singular reports for better troubleshooting overview
  • Strongly recommended using during the deep-dive tuning phase
  • Enables you to compare and measure the impact of the future changes and optimizations to the system

Examples of SMT Reports

The tool currently holds 55 reports and further are in the development pipeline. We have many ideas :-)

Do you want a free trial? Let´s have an online meeting

Online meeting

 

  • Proper SQL Server configuration
  • Index consolidation: to speedup database modification operations and maintenance
  • Reconfiguration of index objects: to provide a more concurrent environment and less fragmented databases
  • Optimize database transaction logs: to speed up transaction processing
  • Leveraging the baseline monitoring we could:
  • Recommend new indexes to improve the overall performance of queries executed on the server
  • Perform query tuning for top intensive/resource consuming operations on the server
  • Feature tuning for user most application exposed operations
  • Recommend database code refactor if possible (depend on database and application type)
  • Further tuning of database maintenance operations based on historic runtime data


Areas covered by our monitoring tool:

  • Memory allocation distribution, usage, and its KPI states.
  • CPU resource allocation per database or process and related counters.
  • Tempdb performance metrics and usage, together with contention points and costly operations.
  • Very detailed storage usage statistics collection up to specific database file on the volume.
  • Transaction log usage per-database and file with its contention queries for all databases.
  • Index usage of all indexes on the server and its design changes together with recommendations over time.
  • Query performance for most interesting operations on the server and databases.
  • Blocking and deadlocking operations on the server and its victims.
  • All resource governor usage and configuration statistics with up to workload group detail.
  • AlwaysOn events and configuration.
  • Server configuration changes and all its logs such as server, agent, SMT alerts, maintenance, and job automatization.
  • Performance tuning activities over time with its impact.
  • Everything was collected and stored up to 3 years in the past for good trend analysis.
  • With schedules configurable per monitoring item with server-specific presets.


What we monitor/feature above other industry-standard tools:

  • Full Resource Governor metrics
  • Per database file performance and capacity
  • Index recommendations with overtime statistics
  • Per database index usage metrics with overtime statistics
  • Tuning notes, (diary & impact analysis report/dashboard)
  • Job runtime volatility report
  • Query compare analyzer between two specified time windows
  • Collector for Waits, Spinlocks, Latches with overtime statistics
  • Memory allocation with per database granularity for reading and write pages
  • Per NUMA node metrics for memory
  • Tempdb allocation patterns (All type of stores, cleanup rates, and contention events)
  • Memory checkpoint and lazy writer processes with overtime statistics
  • Audit for changes of server config and database index definitions
  • Advanced CPU counters and waiting tasks
  • Automated memory management and cleanup
  • Automated & offloaded backup testing and consistency checks for all company SQL servers
  • Automated synchronization of logins between AlwaysOn Replicas
  • Build-in selectivity calculator for table columns with graphical histogram
  • Super-fast front end interaction with API connectivity at your disposal

Possible Monitoring Implementations

Graph showing sharp drop in user data cached in memory for specific databases, only most used database kept its data in buffer pool for reading. We also have graph for modified (dirty) data pages in SMT.
  • Standalone SQL Server installed on a physical host or virtual machine using one SMT License
  • Failover Cluster Instance of SQL Server using one SMT License
  • SQL Server (Active Node) connected through Availability Groups with other SQL Server (Passive Node) using one SMT License
  • SQL Server (Active Node) connected through Availability Groups with other SQL Server (Active Node) using two SMT Licenses
  • SQL Server Azure Managed Instance using one SMT License (Further costs may apply due to needing of VM for application frontend or Azure Data Factory or SQL Web edition instance)

Will you need an SMT?

You may ask a set of questions regarding your current SQL Server environment and situation, if you are in doubts about current answers, there is a high probability this tool helps.

  • How are you notified regarding critical issues on core company SQL Servers?
    • Which communication channel your team use to gets notified?
    • Is response time from your team good enough?
    • Any delay in case resolution?
  • If any issues arise on your SQL Servers, did your dba team described the root cause of the issue?
  • Is the root cause described on real monitored/collected data or it is just an assumption from a dba team?
  • Are there any recommendations set after the root cause has been identified to prevent the reoccurrence of the issue?
  • After its implementation, how the new state on the server is evaluated?
  • Any hard data comparison or A-B analysis?
  • If your team does the change on SQL Servers, how is it documented?
  • Do we have any server change notebook or tuning notebook to describe what has been updated?
  • Do you have any tool to measure trend change of your SQL Server wait statistics?
  • Are you monitoring the performance of your top queries running on the server over time?
  • How long you keep your collection history? Which query attributes are you collecting (Execution plans, Duration, physical reads)?
  • Are you monitoring and auditing server-wide configuration changes?
  • Are you monitoring database, table, and index usage?
  • Would you like to know where your SQL Server sees the opportunity to add or update the index on the most important tables?
  • Do you know which tables are mostly exposed to reads and writes?
  • If you find a slow performance of your storage, are you able to identify which queries used it during a poor performance state?
  • How you currently identify query performance regressions?
  • Are you able to describe if your application is bloated by parameter sniffing situations?
  • Do you have any deadlock monitoring in place?
  • Do you know which currently existing indexes are not needed on your tables?
  • Can you describe which indexes on your core database are mostly fragmented during the day?
  • Are you somehow recording changes on your existing indexes?
  • Do you maintain records regarding your query/index tuning activities with an impact on existing queries?

How SMT Works

1

Alert module of SMT monitoring together with standard SQL Server alerting will provide notification about upcoming issues.

(via mail and, incident ticket registered in our help desk tool to provide you with an overview of the situation and our future actions). Alerts are also available in the SMT tool dashboard for overview.

2

By the early notification, we could provide the necessary support in time.

Thanks to our hand-made monitoring tool we could easily identify the root cause and provide a proper recommendation or fix for the issue.

 
3

As a result, this service will save your costs on the business and support side.

(decreased out of business times for core company servers and applications) and support side (less time spent on troubleshooting and analysis of issues)