"Help people get complex data about SQL Server in easy and understandable way"

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 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 to use during the deep-dive tuning phase
  • Enables you to compare and measure impact of the future changes and optimizations to the system

Examples of SMT Reports

Tool currently hold 55 reports and further are in 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 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 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 it’s 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 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 it’s 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 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 over time statistics
  • Per database index usage metrics with over time 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 over time statistics
  • Memory allocation with per database granularity for read 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 over time 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 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 need of VM for application frontend or Azure Data Factory or SQL Web edition instance)

Will you need a SMT?

You may ask a set of questions regarding your current SQL Server environment and situation, if you are in doubts with 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 Severs, did your dba team described root cause of the issue ?
  • Is the root cause described on real monitored/collected data or it is just assumption from dba team?
  • Is there any recommendations set after root cause has been identified to prevent reoccurrence of the issue?
  • After it’s implementation, how the new state on the server is evaluated?
  • Any hard data comparison or A-B analysis?
  • If your team do 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 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 opportunity to add or update index on most important tables?
  • Do you now 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 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 day?
  • Are you somehow recording changes on your existing indexes?
  • Do you maintain records regarding your query/index tuning activities with impact on existing queries?

How SMT Works

1

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

(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 SMT tool dashboard for overview.

2

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

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

 
3

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

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