Audit Made Easy Without Audit Log - Part One

Introduction

In Microsoft SQL Server, the activity of each of the database tables is tracked in the other table. The phenomenon is called the Audit trail or Audit log of the database table.

In most of the cases, we don’t explicitly create the “Server Audit”. The reasons for not having a server audit for the database are listed below,

  • More memory space consumption
  • Each of the CRUD operations will trigger the Insert operation into Audit record which slows the process
  • Query execution time is increased [Not directly impacted]
  • Expensive

In this article, I will explain how to make use of the existing resources in the SQL Server which cannot directly replace the Audit trail whereas it can help to solve a few of the needs which an Audit trail can satisfy.

The Microsoft SQL Server itself has some of the features which were not used by the developers frequently and I am going to explore these options.

SQL Server Audit (Database Engine)

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server Audit lets you create server audits, which can contain server audit specifications for server level events and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.

There are several levels of auditing for SQL Server, depending on government or standard requirements for your installation. SQL Server Audit provides the tools and processes you need to enable, store, and view audits on various servers and database objects.

Reference 

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine

Scenario 1

I have to identify the last table updated with DateTime, however, in this scenario, the Audit trail is not available for the specific table. Also, there is no column in the database table which shows the Last updated date time.

We are going to use the system table of SQL Server where some database and data table information is stored. sys.dm_db_index_usage_stats is the table which is used to find the value of the table updated last time.

I will explain about the table “sys.dm_db_index_usage_stats” in later parts of this article but now, let’s know the usage of the table in different scenarios.

Query

  1. SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName,  
  2.     last_user_update  
  3. FROM sys.dm_db_index_usage_stats 

Output/Result

Output

Scenario 2

In this, we have to get the Date time when the table was last executed with SELECT query. This will be helpful to check if some are used in an application or Code or by its stored procedures for a long time, if not, we can drop the table which is not necessary.

To confirm the usage of the table, we can use the following query. In this sample, I have added the condition to check for a specific data table value.

Query

  1. SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_seekFROM sys.dm_db_index_usage_statsWHERE OBJECT_ID=OBJECT_ID('ApplicationConfiguration')  

Output/Result

Output

Here, the “ApplicationConfiguration” table name is specified in the query. So, the information is shown only of that table.

Scenario 3

In this scenario, we have to find the number of times a table is used for Read or Update operations.

This is very helpful for the analytics scenarios where a certain table may be used multiple times, whereas some other tables may not be used at all by the application. So, to identify the frequency of usage, we can use this query.

Query - Number_of_Select_Queries

  1. SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName,user_seeks AS Number_of_Select_QueriesFROM sys.dm_db_index_usage_stats  

Output/Result

Output

Query - Number_of_Update_Queries

  1. SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, user_updates AS Number_of_Update_QueriesFROM sys.dm_db_index_usage_statsWHERE OBJECT_ID=OBJECT_ID('Logs')  

Output/Result

Output

The usage of this system table is not just limited to the above scenarios. It has many columns and each column is helpful for the user to get the statistics and index based reporting the data base is not configured with action audit.

More information on “sys.dm_db_index_usage_stats”

sys.dm_db_index_usage_stats has a limitation that it does not return the details about memory-optimized indexes, however, we have other set of alternatives to achieve those tasks.

I have provided the information of each of the Columns.

Query

  1. SELECT *FROM sys.dm_db_index_usage_stats  

The below result value shows all the information for each table in the particular database.

Result

Output

Output

The identifier for the node that this distribution is ON.

Table Column nameData typeInformation
database_idsmallintDatabase ID on the table is defined.
object_idintID of the table the index is defined
index_idintID of the index.
user_seeksbigintNumber of seeks by user queries.
user_scansbigintNumber of scans by user queries. This represents scans that did not use 'seek' predicate.
user_lookupsbigintNumber of bookmark lookups by user queries.
user_updatesbigintNumber of updates by user queries. This includes Insert, Delete and Updates representing number of operations done not the actual rows affected. For example, if you delete 1000 rows in one statement, this count will increment by 1
last_user_seekdatetimeTime of last user seek
last_user_scandatetimeTime of last user scan.
last_user_lookupdatetimeTime of last user lookup.
last_user_updatedatetimeTime of last user update.
system_seeksbigintNumber of seeks by system queries.
system_scansbigintNumber of scans by system queries.
system_lookupsbigintNumber of lookups by system queries.
system_updatesbigintNumber of updates by system queries.
last_system_seekdatetimeTime of last system seek.
last_system_scandatetimeTime of last system scan.
last_system_lookupdatetimeTime of last system lookup.
last_system_updatedatetimeTime of last system update.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

Conclusion:

This set of tables is very useful. Post your queries in the comment box and I will be happy to help you with more details.