SQL Server Automation Operations series-about data collection (multi-server data collection and performance monitoring)

Requirements Description

In a production environment, it is often necessary to collect data to locate a problem or to form a baseline.

There are many ways to solve the data acquisition in SQL Server, such as trace, Profile, SQLdiag, extended events and so on.

Several scenarios have pros and cons, with the beginning of the SQL Server2012 version, Microsoft's start to integrate these acquisition solutions, force expansion events.

For the above-mentioned data acquisition is only a means of implementation, for the storage of the collection is not uniform specification, and for multi-server data collection and aggregation did not form a unified standard.

This article realizes

1. Realize multi-server performance acquisition by SQL Server Data acquisition device

2. Using SQL Server Data Warehouse (DW) to form operation and maintenance report

3, through the configuration of flexibility, to achieve different data collection points of the server

<1> Basic Configuration

Previously, a colleague said that SQL Server automation is too weak, and the location of the problem is also more troublesome, need to remember the various systems of the DMV .... Various log lookups .... You look at them. MySQL's powerful graphical interface hints give you a glimpse of the current database's problems.

Indeed, take a look at the graphical operational interface provided by MySQL

Is his Niang handsome, the overall platform for the division is very detailed: network, instance status, storage state.

And there's a graphical display interface that looks elegant.

The content reflected in the above interface is very convenient for finding the problem, and the same module cannot be found in SQL Server. If an experienced DBA passes through Task Manager, Performance Monitor, and then the system comes with a few DMV ... For analysis .... It looks complicated and very advanced in appearance.

In fact, in SQL Server, there are similar functional modules, and more flexible implementation of multiple servers together to collect, below, we look at the detailed use and configuration process.

On data collection, right-click on "Configure Management Data Warehouse"

SQL Server has created a data warehouse (DW) for data analysis in order to support the data acquisition task of multiple servers, due to the large amount and importance of data analysis.

Choose a good example here, create a good data warehouse can.

Tip: In order to avoid affecting the performance of the production system, it is generally recommended to use another idle instance, dedicated to data acquisition and performance analysis.

I'll demonstrate here, configure it with a local instance and then the next step:

To this step is to manage the user rights of the data Warehouse, you can configure user rights, three levels of permission: Administrator, readable, writable;

Quite simply, the configuration is done directly to the next step, and then the Data Warehouse is completed.

<2> Basic Configuration

This step is to set up data collection, simply to configure what data items are collected.

Also, right-click on the data collection and select "Set Up data collection"

Then, the next step is to connect to the Data warehouse and select the cache directory

Then, the next step can be completed, here SQL Server also built a set of data collection template, will collect all the basic information for you, of course, can also be customized, the article is described later.

Look at the default collection of data collected items

The default template, which is divided into the query statistics, in fact, the corresponding instance state, disk storage, server activity, in addition to this gave a utility information, which is used to flexibly configure a few other collection items.

You can start and stop data collection actions according to your preferences at any time, as appropriate.

Also, you can configure the time interval or the status value of the collection action yourself.

Also, SQL Server has built-in a plan template that basically covers all of the application scenarios.

Then, you can rest assured that it will be collected on its own. You can stop when you feel uncomfortable.

The rest of the story is to look at the data collected, since MySQL provides such a beautiful way to visualize the image, SQL Server also has.

That's it.

Does that seem to mean something, including: CPU, memory, disk IO, network ...

With SQL Server wait, SQL statement execution, etc.

Then, some statements for performance tuning also give the sort including CPU, run time, total IO, physical read, logical read, etc.

Of course, my local machine itself is very small, and there are not many T-SQL statements running, so the chart tool shows a lot of emptiness.

Take a look at the disk storage

This is generally the above content, their own use of the time to dig it, this article provides ideas.

If the experienced DBA, I estimate that the above statements can be viewed through the system's DMV, but that is limited to experienced, the above scenario for small white reduces the cost of maintaining the database.

And can be collected in multiple servers, centralized processing problems.

Conclusion

This article describes the use of SQL Server's own data collection tool for database operations. For the custom data collection item settings, the next article describes it.

In addition, there are many useful contents of DW for data collection, if it is for large platform performance operation, it can be expanded to form its own operation and maintenance platform.

There is a wide range of SQL Server Automation operations and testing, many of which are based on daily experience, from manual to automated processes step-by-step.

If you read this blog, feel that you have something to gain, please do not skimp on your " recommendation ".

SQL Server Automation Operations series-about data collection (multi-server data collection and performance monitoring)