Monitoring and Baselining Database Activity

Have you ever wondered how much a SQL Server database is used the last day, week, month, year, or if it is used at all? Two central question is – (1) Does users write/update data in the database? (2) Is the database read-only for historical usage?

This is two key questions for most migration projects and important if you want to reduce the number of databases in your migration plans. The amount of INSERT/UPDATE (write) and SELECT (read) statement is vital when determining the database importance in the migration. If the database is used only in normal work hours, we can request a long downtime window during migration. If the database is used during both day and night, you might want to check if the weekend migration is more suitable. If the database is just part of a larger application eco system with loads of integration, you might want to choose a longer downtime period or change the migration strategy.

I have always been interested in performance and database optimization to be able to detect current bottlenecks or predict future performance issues. Last year I came across a very interesting blog post written by Erin Stellato on the baselining topic. In addition, Glenn Berry, a colleague of Erin at sqlskills has interesting blog series on SQL Server diagnosing information. This information can be used to gather, aggregate and store information for how the SQL Server is performing over time. This enables us to establishing scenarios (read: baselines) for different period, such as normal working hours, mornings, evening, weekends, peak periods and so on. These baselines can be uses to measure if a server is performing as expected or not.

There exists several DMV (Dynamic Management Views) with information that can be used for monitor the Activity.

  • sys.dm_db_index_usage_stats: contains counts for different types of index operations and time when they were last performed. The most interesting columns are:
    • user_seeks – number of index seeks
    • user_scans- number of index scans
    • user_lookups – number of index lookups
    • user_updates – number of insert, update or delete operations
  • sys.dm_db_index_operational_stats: contains current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database. The most interesting columns for reading data is the following:
    • range_scan_count – cumulative count of range and table scans started on the index or heap.
    • singleton_lookup_count – cumulative count of single row retrievals from the index or heap.
  • sys.dm_os_performance_counters: contains cumulative and current values for different SQL server performance counters. The most interesting counter to monitor number of transactions (INSERT/UPDATE operations) against user defined tables are the following:
    • “Write Transactions/sec” (SQL 2008 and newer)
    • “Transaction/sec” (SQL 2005)
    • “User Connections”
    • Based on the post from Erin I have created a solution where I’m able to keep track of INSERT/UPDATE and different SELECT operations for databases on a SQL Server. I have defined two tables and one view:
  • Counters (table): contains definition of all counters and related information of performance counters and custom “counters” that is used on the solution. The column Name is a constructed name based on the “object_name” and “counter_name” from the DMV sys.dm_os_performance_counters. A counter might be deactivated by setting the Active column to 0 (zero).
  • PerfmonData (table) – all data based on DMVs or custom calculations. This store either calculated delta values based cumulative counter values or snapshot values for specified counter. The interval between calculations is decided by the schedules used by the agent job that is responsible to collect data.
  • v_read_count (view) – contains aggregation of read access for all databases on the server. The data is based on sys.dm_db_index_operational_stats.
  • Do the following steps to build Your own baseline database:
  1. Create a database named “BaselineLight”. Add the schema by running this script.
  2. Next step is to create configuration for the initial counter values  for databases. Download and run “Create Counters” script.
  3. Create a SQL Agent job for collecting data. Download and run “Create Collector job” script.
  4. Create test database – mine is called Roar2. Download full script.
  5. Add test data with the following command
    INSERT INTO [Roar2].[dbo].[RoarTest]([CreatedAt]) VALUES (getdate());
  6. Run some single lookups and range reads against the RoarTest table with following commands:
    SELECT * FROM [Roar2].[dbo].[RoarTest] where Id >= 5 AND Id < 10 
    SELECT * FROM [Roar2].[dbo].[RoarTest] where Id = 5 ;

Based on the read/write data in PerfMonData table we can create a forecast on how much a database is used every day all day long. The schedule for the CollectPerfmonData job can be adjusted to for example 5min or 15 min for a finer granularity of the calculated data.

Now we have to data in the DMVs, described above for the Roar2 database. Whent the job “CollectPerfmonData” runs next time the table “PerfMonData” will be populated with the delta values between “run time” of the CollectPermonData and (2) when the initial counter values was recorded.


2 thoughts on “Monitoring and Baselining Database Activity

  1. Pingback: GetDatabaseVersion – Stringify Compatibility Level | Sveroa's Developer Blog

  2. Pingback: When is SQL Server restarted? | Sveroa's Developer Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s