SQL Audit (SQLA)

We are providing comprehensive one-time auditing services for your SQL Server Instances. This service includes structured documentation in PowerBI report format including findings with prioritization and estimated duration required for resolution of the issue (in man-days). An output document can be used as a base for performance tuning effort and is also helpful to set up tuning priorities and tasks. 

You will not be alone with the results! We deliver within this service also 2-hour presentation of results, where you may ask us anything regarding audit findings. 

Where does it help?

  • Point out the most critical performance issues and propose solutions.
  • Helps to understand how your server is processing data and which database s have the main impact on your server.
  • Uncover the most common configuration and security mistakes.
  • Detailly examine most influential database on SQL Server instance

SQL-A Overview page

Where the report data come from?

  • We are using only the data which your server had already collected!
  • Internal in-memory statistics
  • No external tools are installed

Every audit consists of several stages.

  • Creation of the new Audit database on monitored SQL Instance and deployment of the monitoring jobs
  • Execution of the monitoring jobs – runtime of 24 hours
  • After a 24-hour run, we stop the scheduled jobs and execute a set of additional audit scripts
  • During the next few working days –a collection of the Audit database (uploading it into Woodlers analysis server) and preparation of audit results
  • 1.5-2 hours presentation workshop of the Audit results with company’s specialists -where we present audit details and agree on next steps based on audit findings

Besides the presentation a document in PowerBI format (URL) with Audit results will be provided, that will summarize issues found on the server with our recommendations as well as state description of the important parts of SQL Server. It is designed to be a simple read even if you are missing some key knowledge about SQL servers or databases. The document also contains a high-level overview for non-technical personnel, to help evaluate the most critical issues found on the server and the cost (calculation in MDs) required to fix them.

SQL-A findings page

All activities will be realized remotely via VPN

SQL code in user DBs should be decrypted (in case it is encrypted)

 

SQL-A waits analysis detail page

 

SQL Instance Analysis from Woodler is divided into four audit categories described below.

  • Definition who have strong rights in the instance (can do everything with instance )
  • Report of non-existing users who own SQL agent jobs
  • List of active build-in audits on the instance
  • Summary of audit triggers running after users log into the instance
  • List of databases which already using Transparent Data Encryption
  • Check of system databases master, msdb a model for user-created objects (those objects will not be created after complete disaster recovery)
  • A report describing users who are marked as database owner in database properties
  • List of instance service accounts
  • And more...
  • Tempdb configuration check
  • Check for databases that are using most I/O operations on the instance
  • Defining bottleneck of instance, usage report of hardware components (CPU, memory, storage) from Instance perspective (by databases)
  • Check if memory & task handling is used efficiently
  • List of ad-hoc queries which mostly bloating the system
  • Report of database file usage by instance
  • Identification of (plan guides) are used on instance
  • Transaction log fragmentation check
  • Instance waits for analysis
  • SQL server query workload overview
  • And more...
  • Instance configuration check
  • User database configuration & design check
  • Database backups strategy check (Disaster recovery)
  • Check for the resource governor usage (tool for advanced sharing of instance resources between users)
  • Check for automatic notification and monitoring usage on the instance (it’s for free why not using it?)
  • Inspection of page auto repair for database mirroring
  • List of jobs that are running automatically after startup of SQL agent
  • Check for cleanup of msdb database
  • Inspection of actual maintenance plans
  • Report of available SQL agent alerts for monitoring critical server events
  • Enterprise features check used by databases
  • And more...

 

Database analysis for one database chosen by the customer.

  • Database file specification & I/O access characteristics for them
  • List of most used and longest-running stored procedures in the database (one of the best beds for further query tuning) 
  • Screening of stored procedures & queries for usage of system resources (CPU, Memory, etc.) 
  • Summary for unused indexes which dropdown insert/update operations on tables in the database. (potential candidates for further deletion) 
  • Definition of indexes that are potential candidates for creation based on SQL server needs 
  • Report of most used (cached in memory) tables and indexes on the instance. 
  • List of out-of-date column statistics (they can be a cause of query performance degradation) 
  • Status of index fragmentation (also decrease the performance of selecting data from tables) 
  • Definition of tables without clustered indexes 
  • Report of hypothetical or disabled indexes and statistics which server do not use but they bloat database space. 
  • Definition of non-trusted objects in the database. 
  • Check for usage of query hints 
  • A report describing table size and row count in the database 
  • Execution plan check for errors & warnings 
  • Database usage characteristics report (database growth rate and amount of transactions generated per week)
  • And more...