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.

Blog milestone – 25k

This blog had a nice, little milestone this week – 25K hits since October 2012. This is an average of 1200 hits/month since I first started blogging regularly with at least 4-5 blog posts each month.

This blog is still used as my personal “notebook” where collect tricks and tips on things I’m working on day-to-day basis. I hope you all enjoy reading, and I will try my hardest to make the blog post as interesting for everyone else 🙂

I will still write posts on Microsoft Technologies like Windows Server, Azure, SQL Server and other things I find interesting.


Prices for Azure Virtual Network, Web and SQL VMs

My last post on virtual networks and virtual machines had a good response, and here is the follow-up post with more prices and recommended setup for different.

There are two types of virtual machines in Windows Azure – Basic and Standard. The “Basic” virtual machines sizes don’t have the possibility to auto-scaling or load balancing. In addition, the “Basic” sizes are restricted up to 4 cores and 14GB memory.

Most of the cost for a virual machine is connected to licences for operating system and other Microsoft software such as SQL Server, Biztalk and Sharepoint. In this post I will focus on SQL Server since this is my primary working area 🙂

The prices for Windows Azure virtual machines are located in the “Price Page“.

The following basic virtual machine types and size are are available. The column “Price/month” is the licencing cost for the Window Server 2012 R2, and the column “Web” is the cost for the SQL Server Web Edition, and the “Standard” column is the price for SQL Server Standard Edition for the given virtual machine size. All prices are given in USD.

General purpose (BASIC), prices from May 1st 2014 SQL Server
WinSrv 2012 R2 data center CPUs Memory Price/month Web Standard
A0 (Extra Small, 1Ghz) Shared 768mb 14
A1 (Small, 1.6GHz) 1 1.75gb 56 90 466
A2 (Medium, 1.6Ghz) 2 3.5gb 112 146 522
A3 (Large, 1.6Ghz) 4 7.0gb 224 258 634
A4 (Extra Large, 1.6Gz) 8 14.0gb 447 514 1.266

The “Standard” virtual machine sizes have the following specifications and prices:

General purpose (STANDARD), prices from May 1st 2014 SQL Server
WinSrv 2012 R2 data center CPUs Memory Price/month Web Standard Enterprise
A0 (Extra Small, 1Ghz) Shared 768mb 15
A1 (Small, 1.6GHz) 1 1.75gb 67 101 477
A2 (Medium, 1.6Ghz) 2 3.5gb 134 168 544
A3 (Large, 1.6Ghz) 4 7.0gb 268 302 678 1.831
A4 (Extra Large, 1.6Gz) 8 14.0gb 536 603 1.355 3.661
A5 (1.6Ghz) 2 14.0gb 224 258 634 1.787
A6 (1.6Ghz) 4 28.0gb 447 481 857 2.010
A7 (1.6Ghz) 8 56.0gb 893 960 1.712 4.018
A8 (2.6Ghz) 8 56gb 1.823
A9 (2.6Ghz) 16 112gb 3.646

I hope the prices are correctly converted into the table. Please check the price page on the azure portal if you suspect incorrect price. Orignally, I had these prices in “norwegian kroner”. It might be some errors in this post.