A note about supportability and tuning of databases 

 

Introduction: This is a continuation of the article titled tuning of databases and linked here. 

   

There is a lot of documentation on performance tuning of a relational database, but this article continues to cover some of those and supportability for a multitenant application.  

 

Monitoring is a critical aspect for determining how a server is performing. Effective monitoring involves taking periodic snapshots to isolate runaway processes. Optimal performance can be achieved by minimizing response times and maximizing throughput. Efficient network traffic, disk I/O, and CPU usage are key to peak performance. 

   

There is a performance dashboard available for whether a database is experiencing a performance bottleneck. If the bottleneck is found, additional diagnostic data can be easily captured to resolve the problem. The dashboard can help with common problems pertaining to  

• CPU bottlenecks,  

• I/O bottlenecks  

• Missing indexes 

• Blocking 

• Resource contention 

The performance dashboard also helps to identify expensive queries that may have been executed before, and several metrics that include CPU, Logical writes, Logical Reads, Duration, Physical reads, and CLR time. 

The dashboard is divided into the following sections and sub-reports: System CPU utilization, Current waiting requests, Current activity, Historical information, and miscellaneous information. 

The database also provides dynamic management views and functions. 

There are server scoped dynamic management views and functions which require “VIEW SERVER STATE” permission on the server and then there are database scoped dynamic management views and functions which require view database state permission on the database. 

These dynamic management views include Always On availability groups, Change Data Capture, Change Tracking Related, Common Language Runtime, Database Mirroring, Database related, Execution related,   Extended events, Filestream and Filetable, Full-text search and semantic search, Geo replication, Index related, I/O related, PolyBase, Memory-Optimized, Object Related, Query Notifications related, Replication related, Resource Governor related, Security-related, Server-Related, Service Broker, Spatial Data, Azure Synapse analytics and Parallel data warehouse, SQL Server operating system related, stretch database and transaction related.