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). Output document can be used as a base for performance tuning effort and is also helpful to setup 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 it helps?

  • Point out most critical performance issues and propose solutions.
  • Helps to understand how your server is processing data and which database s have 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 comes 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 of the scheduled jobs and execute a set of additional audit scripts
  • During next few working days –collection of the Audit database (uploading it into Woodler’s 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

Beside 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 simple read even if you are missing some key knowledge about SQL servers or databases. Document also contains high level overview for non-technical personnel, to help evaluate the most critical issues found on server and cost (calculation in MDs) required to fix it.

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 instance (can do everything with instance )
  • Report of non-existing users which own SQL agent jobs
  • List of active build-in audits on instance
  • Summary of audit triggers running after users log into 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)
  • Report describing users which are marked as database owner in database properties
  • List of instance service accounts
  • And more...
  • Tempdb configuration check
  • Check for databases which 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 system
  • Report of database file usage by instance
  • Identification if (plan guides) are used on instance
  • Transaction log fragmentation check
  • Instance waits 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 instance (it’s for free why not using it?)
  • Inspection of page auto repair for database mirroring
  • List of jobs which 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 customer.

  • Database file specification & I/O access characteristics for them
  • List of mostly used and longest running stored procedures in 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 database. (potential candidates for further delete) 
  • Definition of indexes which are potential candidates for creation based on SQL server needs 
  • Report of most used (cached in memory) tables and indexes on instance. 
  • List of out-of-date column statistics (they can be a cause of query performance degradation) 
  • Status of index fragmentation (also decrease 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 database. 
  • Check for usage of query hints 
  • Report describing table size and row count in database 
  • Execution plan check for errors & warnings 
  • Database usage characteristics report (database growth rate and amount of transactions generated per week)
  • And more...