Collect performance statistics and output results to SSMS as custom reports.

So, we have a task to collect SQl-server performance statistics and further analysis of the results. What is it for? For example, you want to transfer a certain database from one server to another, and you need to calculate performance before and after transfer.



A little more. There is a certain server, let's call it server-sql-001, on which the CRM and ERP databases of the company run. And there is another server, let's call it server-sql-1c, on which DB 1C is running. Server-sql-001 is a brand new server, with modern features, etc. And server-sql-1c is a fairly average server by modern standards. And so, all the bookkeeping is eager to move to server-sql-001 in order for them to be happy, to increase the productivity and speed of the excellent yellow program. So there was a task to check, but is it true that everyone will be happy? Or after the move will suffer performance and CRM and ERP and 1C? Here is one of the examples for which we need to collect and analyze statistics.
')
Yes, you can collect all the necessary statistics into an excel file using the system monitor, then build graphs, etc. But what if statistics collection continues for a week, a month? And at intervals 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. Run SQL Server Management Studio, connect to the server we need.



After connecting to the instance, click File - Create - Create a query in the current connection (or just Ctrl + N). In the window that appears we write the following code:

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%)
GO

If you do not like to create a database through requests, then do the following. After connecting to the instance, right-click on the Databases - Create Database.



Select the location of the database and click OK. For our purposes, all other parameters can be left as default.
The goal is to create a database. And in what way is a matter of taste, as you like.

2. Create an ODBC source.

So, the database was created, now it's time to create a source where the System Monitor will write its statistics. Select Start - Administration - ODBC data sources (for Windows Server 2012, it's 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 for creating a new source that appears, we write a name, for example, statistic, if we wish, we can specify a description and specify 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, the list of available SQL servers will take some time). After entering the data, click Next. Here you can choose which authentication method to choose. Everything else is left as is. Click Next and here you have to select the connection in the one created by us. Tick ​​Use default database and select our s_statistic. Click Next, in the next window to make changes as desired. Click Finish, check the data source, click OK and close the ODBC data source management console.

3. Creating and configuring a group of data collectors.

The third part of our ballet begins with the launch of Computer Management - the Performance section (Performance) - Data Collector Set (Data Collector Groups), right click on User Defined (Special) - New Data Collector Set (Create Group Data Collectors). We give the name of the new group of data collectors, choose Create manually - Next - Create data logs (Performance counter) - Next - Add. And here micro hell begins! The number of various counters just rolls over. I opted for several:

• \ Memory \% utilization of allocated memory
• \ Process (sqlserv) \% Processor Time
• \ Processor (_Total) \% CPU utilization
• \ Physical disk (_Total) \% disk activity while reading
• \ Physical disk (_Total) \% disk activity while burning

You can choose anything or whatever you need at the moment. My interval is set at 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 there is an opportunity 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, 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.
To begin, check if the report is being recorded. Run SSMS and create such a query:

use [s_statistic]
go
select CounterName, CounterDateTime, CounterValue
from dbo.CounterData CDT
join dbo.CounterDetails CD on CD.CounterID=CDT.CounterID

As a result, we get this:



We launch SQL Server Business Intelligence Development Studio (in SQL 2012 it is called SQL Server Data Tools). Select File - New project - Report server project. In the right part of the program, right-click the General data sources and select Add new data source.



We call as we like, and in the connection string we write Data Source=server-sql-001;Initial Catalog=s_statistic . Or we press the edit and select the connection name of the server, the entrance to the server and the database in the properties of the connection.



We check the connection and click OK. Go to the Accounting data tab and specify the necessary ones there. Click OK.
Below click the right button Reports - Add a new report. In this case, we skip the new page, and on the second page we write this, we already know, code:

select CounterName, CounterDateTime, CounterValue
from dbo.CounterData CDT
join dbo.CounterDetails CD on CD.CounterID=CDT.CounterID

Click Next, select the report type Matrix - Next. In the Columns field, puts the CounterName, in the Rows field of the CounterDateTime, in the Details field of the CounterValue, and you can click Finish. Or click next and choose the style of the matrix. Great, report created.
Now we can only bring it to the appearance that we like. Since we do not really need the table, I delete it. Instead, we add a chart (graph). In the given diagrams we indicate:

• Groups nearby - CounterName
• Group category - CounterDateTime
• Values ​​- CounterValue

Save our project and ready. Now we run SSMS, right-click on our database s_statistic - Reports - Custom reports - find our report - Open.

And here is our result:



I hope for critics and advice. For there is a feeling that I missed something. But the topic is very interesting for me, I plan to study further.