SQL Audit (SQL-A)
We provide comprehensive one-time auditing services for your SQL Server Instances. This service includes structured documentation in Power BI report format, including findings with prioritization and estimated duration required for resolution of the issue(s) in man-days (MDs). An output document can be used as the basis for a performance tuning effort and is also helpful in setting up tuning priorities and tasks.
You will not be alone with the results! We provide a 2-hour presentation of results, at which time you may ask us anything regarding the audit findings.
Where does this help?
- Point out the most critical performance issues and propose solutions.
- Help to understand how your server is processing data and which databases have the main impact on your server.
- Uncover the most common configuration and security mistakes.
- Thoroughly examine the most influential database on the SQL Server instance
Where does the report data come from?
- We use only the data that your server has already collected!
- Internal in-memory statistics
- No external tool is installed
Every audit consists of several stages.
- Creation of a new Audit database on the monitored SQL Instance and deployment of monitoring jobs
- Execution of 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 following few working days, Audit database collection (uploading to the Woodler analysis server) and preparation of audit results
- Up to 2-hour presentation workshop of Audit results with company specialists, where we present audit details and agree on next steps based on audit findings
In addition to the presentation, a document in Power BI format (URL) with Audit results is provided, summarizing issues found on the server along with our recommendations, as well as a description of the important parts of the SQL Server. It is designed to be a ‘simple read’ even if you are missing key knowledge about SQL servers and / 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.
All activities are performed remotely via VPN
The SQL code in user DBs should be decrypted (in case of encryption)
SQL Instance Analysis from Woodler is divided into four audit categories described below.
- Defining who has strong rights in the instance and can do anything with the instance
- Report of non-existing users who own SQL agent jobs
- List of active built-in audits on the instance
- Summary of audit triggers running after users log into the instance
- List of databases already using Transparent Data Encryption
- Check of system database 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 owners in the database properties
- List of instance service accounts
- And more...
- TempDB configuration check
- Check for databases that are using the most I/O operations on the instance
- Defining bottleneck of instances, usage report of hardware components (CPU, memory, storage) from the Instance perspective (by databases)
- Check if memory & task handling is used efficiently
- List of ad-hoc queries mostly clogging the system
- Report of database file usage by instance
- Identification of (plan guides) that are used on the 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 ( a tool for advanced sharing of instance resources between users)
- Check for automatic notification and monitoring usage on the instance (free of charge)
- Inspection of page auto-repair for database mirroring
- List of jobs that are running automatically after the startup of SQL Agent
- Check for cleanup of the 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 of unused indexes that 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 the 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 decreases the performance of selecting data from tables)
- Definition of tables without clustered indexes
- Report of hypothetical or disabled indexes and statistics that the server does not use, but they clog database space
- Definition of non-trusted objects in the database
- Check for usage of query hints
- A report describing the 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...
