Collect performance statistics and output results to SSMS as custom reports
- Tutorial
So, we had a task to collect SQl server performance statistics and further analyze the results. What is this for? For example, you want to transfer a certain database from one server to another, and you need to calculate the performance before the transfer and after.
A little bit more. There is a server, let's call it server-sql-001, on which the company's CRM and ERP databases are spinning. And there is also a server, let's call it server-sql-1c, on which the 1C database is spinning. Server-sql-001 is a new branded server with modern features, etc. And server-sql-1c is a fairly average server by modern standards. And now, all the accounts department is eager to move to server-sql-001, so that they can be happy, the productivity and speed of the excellent yellow program will increase. So the task arose to check whether the truth will come to all happiness? Or will the performance of both CRM and ERP and 1C be affected after the move? Here is one example for which we need to collect and analyze statistics.
Yes, you can use the system monitor to collect all the necessary statistics in an excel file, then build graphs, etc. But what if the collection of statistics continues for a week, a month? And with an interval of 10 seconds? Will it be convenient to work with such a file? Well, see for yourself.
The first thing we need to do is create a database. We start SQL Server Management Studio, we are connected to the server necessary to us.
After connecting to the instance, click File - Create - Create a request in the current connection (or just Ctrl + N). In the window that appears, write the following code: If you do not like creating databases through queries, then do the following. After connecting to the instance, right-click on Databases - Create Database. Select the location of the database and click OK. For our purposes, all other parameters can be left by default. The goal is to create a database. And in what way - this is a matter of taste, as you like.
So, the base has been created, now it's time to create a source where the System Monitor will write its statistics. Choose Start - Administrative Tools - ODBC Data Sources (for Windows Server 2012 it is easier to press Win + q, type odbc and press enter).
We are interested in the System DSN tab. Click Add, select SQL Server and click Finish. In the wizard that appears, create a new source, write a name, for example, statistic, we can specify a description if we wish and indicate which sql server we want to connect to, in this case server-sql-001. (I advise you to write the name of the sql server manually, instead of choosing from the list. The reason is simple, if there are many SQL instances in the environment, then it will take some time to list the available SQL servers). After entering the data, click Next. Here you can choose which authentication method to choose. We leave the rest as is. Click Next and here we need to select the connection in the created by us. Check the box Use the default database and select our s_statistic. Click Next, in the next window make changes as desired.
The third part of our ballet begins with the launch of Computer Management - the Performance section - Data Collector Set, right-click on User Defined (New) - New Data Collector Set. Give a name to the new group of data collectors, select Create manually - Next - Create data logs (Performance counter) - Next - Add. And here micro hell begins! The number of all kinds of counters just rolls over. I chose a few:
• \ Memory \% allocated memory usage
• \ Process (sqlserv) \% CPU load
• \ CPU (_Total) \% CPU load
• \ Physical disk (_Total) \% disk activity while reading
• \ Physical disk (_Total) \% disk activity during recording
You can choose anything you want or everything you need at the moment. I have an interval of 10 seconds. Click Next - if necessary, change the root folder - Next - Default user - Done. So, the group was created. We see that the DataCollector01 counter has already been created and go to its properties in order to select the SQL log format. Below, it becomes possible to select a data source, where we select the statistic we created. Click OK, then right-click on the group of data collectors statistic - Start.
Great, statistics collection has begun! By the way, if the error ServelAllConnect% 1 (or something like that) crashes, then look in the event log. There, oddly enough, the error is described in great detail. Most often, it is associated with the rights of the user from whom statistics are collected.
And now we proceed to the most interesting! Create a custom report for SSMS.
First, let's check whether the report is being recorded. Run SSMS and create the following query: As a result, we get this: Run SQL Server Business Intelligence Development Studio (in SQL 2012 it is called SQL Server Data Tools). Choose File - New Project - Report Server Project. In the right part of the program, right-click General data sources and select Add a new data source. We call it what we like best, and write in the connection string . Or we click edit and select in the properties the connection the server name, the entrance to the server and the database. Check the connection and click OK. Go to the Credentials tab and specify the necessary ones there. Click OK.
Below we right-click Reports - Add a new report. In this case, we skip the new page, and on the second page we write this code, which is already familiar to us: Click Next, select the report type Matrix - Next. Put CounterName in the Columns field, CounterDateTime in the Rows field, CounterValue in the details field and you can click Finish. Or click further and select the matrix style. Great, the report is created. Now we can only bring it to the appearance that we like. Because we don’t really need a table, I delete it. Instead, we add a chart (graph). We indicate in the chart data: • Groups nearby - CounterName • Groups category - CounterDateTime • Values - CounterValue
We save our project and it is ready. Now run SSMS, right-click on our database s_statistic - Reports - User reports - find our report - Open.
And here is our result:
I hope for criticism and advice. For there is a feeling that I missed something. But the topic is very interesting for me, I plan to study further.
A little bit more. There is a server, let's call it server-sql-001, on which the company's CRM and ERP databases are spinning. And there is also a server, let's call it server-sql-1c, on which the 1C database is spinning. Server-sql-001 is a new branded server with modern features, etc. And server-sql-1c is a fairly average server by modern standards. And now, all the accounts department is eager to move to server-sql-001, so that they can be happy, the productivity and speed of the excellent yellow program will increase. So the task arose to check whether the truth will come to all happiness? Or will the performance of both CRM and ERP and 1C be affected after the move? Here is one example for which we need to collect and analyze statistics.
Yes, you can use the system monitor to collect all the necessary statistics in an excel file, then build graphs, etc. But what if the collection of statistics continues for a week, a month? And with an interval of 10 seconds? Will it be convenient to work with such a file? Well, see for yourself.
1. Creating a DB.
The first thing we need to do is create a database. We start SQL Server Management Studio, we are connected to the server necessary to us.
After connecting to the instance, click File - Create - Create a request in the current connection (or just Ctrl + N). In the window that appears, write the following code: If you do not like creating databases through queries, then do the following. After connecting to the instance, right-click on Databases - Create Database. Select the location of the database and click OK. For our purposes, all other parameters can be left by default. The goal is to create a database. And in what way - this is a matter of taste, as you like.
CREATE DATABASE [s_statistic]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N's_statistic', FILENAME = N'D:\data\s_statistic.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N's_statistic_log', FILENAME = N'L:\log\s_statistic_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO2. Create an ODBC source.
So, the base has been created, now it's time to create a source where the System Monitor will write its statistics. Choose Start - Administrative Tools - ODBC Data Sources (for Windows Server 2012 it is easier to press Win + q, type odbc and press enter).
We are interested in the System DSN tab. Click Add, select SQL Server and click Finish. In the wizard that appears, create a new source, write a name, for example, statistic, we can specify a description if we wish and indicate which sql server we want to connect to, in this case server-sql-001. (I advise you to write the name of the sql server manually, instead of choosing from the list. The reason is simple, if there are many SQL instances in the environment, then it will take some time to list the available SQL servers). After entering the data, click Next. Here you can choose which authentication method to choose. We leave the rest as is. Click Next and here we need to select the connection in the created by us. Check the box Use the default database and select our s_statistic. Click Next, in the next window make changes as desired.
3. Create and configure a group of data collectors.
The third part of our ballet begins with the launch of Computer Management - the Performance section - Data Collector Set, right-click on User Defined (New) - New Data Collector Set. Give a name to the new group of data collectors, select Create manually - Next - Create data logs (Performance counter) - Next - Add. And here micro hell begins! The number of all kinds of counters just rolls over. I chose a few:
• \ Memory \% allocated memory usage
• \ Process (sqlserv) \% CPU load
• \ CPU (_Total) \% CPU load
• \ Physical disk (_Total) \% disk activity while reading
• \ Physical disk (_Total) \% disk activity during recording
You can choose anything you want or everything you need at the moment. I have an interval of 10 seconds. Click Next - if necessary, change the root folder - Next - Default user - Done. So, the group was created. We see that the DataCollector01 counter has already been created and go to its properties in order to select the SQL log format. Below, it becomes possible to select a data source, where we select the statistic we created. Click OK, then right-click on the group of data collectors statistic - Start.
Great, statistics collection has begun! By the way, if the error ServelAllConnect% 1 (or something like that) crashes, then look in the event log. There, oddly enough, the error is described in great detail. Most often, it is associated with the rights of the user from whom statistics are collected.
4. Creating a custom report.
And now we proceed to the most interesting! Create a custom report for SSMS.
First, let's check whether the report is being recorded. Run SSMS and create the following query: As a result, we get this: Run SQL Server Business Intelligence Development Studio (in SQL 2012 it is called SQL Server Data Tools). Choose File - New Project - Report Server Project. In the right part of the program, right-click General data sources and select Add a new data source. We call it what we like best, and write in the connection string . Or we click edit and select in the properties the connection the server name, the entrance to the server and the database. Check the connection and click OK. Go to the Credentials tab and specify the necessary ones there. Click OK.
use [s_statistic]
go
select CounterName, CounterDateTime, CounterValue
from dbo.CounterData CDT
join dbo.CounterDetails CD on CD.CounterID=CDT.CounterIDData Source=server-sql-001;Initial Catalog=s_statisticBelow we right-click Reports - Add a new report. In this case, we skip the new page, and on the second page we write this code, which is already familiar to us: Click Next, select the report type Matrix - Next. Put CounterName in the Columns field, CounterDateTime in the Rows field, CounterValue in the details field and you can click Finish. Or click further and select the matrix style. Great, the report is created. Now we can only bring it to the appearance that we like. Because we don’t really need a table, I delete it. Instead, we add a chart (graph). We indicate in the chart data: • Groups nearby - CounterName • Groups category - CounterDateTime • Values - CounterValue
select CounterName, CounterDateTime, CounterValue
from dbo.CounterData CDT
join dbo.CounterDetails CD on CD.CounterID=CDT.CounterIDWe save our project and it is ready. Now run SSMS, right-click on our database s_statistic - Reports - User reports - find our report - Open.
And here is our result:
I hope for criticism and advice. For there is a feeling that I missed something. But the topic is very interesting for me, I plan to study further.