How CM works

Customer Support

+421-2-5465-0242

send email >>

Online support - chat (bottom right)

MS SQL performance by Performance counters

Basic monitoring of MS SQL servers' performance on Windows OS is realized by performance counters. There's a large number of them, we've selected a few of the most important ones and we also add configuration procedure for monitoring of any custom performance counter.

1. SQL Memory, Page splits, users v1.1 (template)
   SQL Total Memory  - currently used memory by the SQL server (from dedicated RAM)
   SQL Target memory - dedicated max. RAM for the SQL server, this value can be found in the Task                                              Manager
   SQL Memory Usage %  - percentage of the currently used RAM from a target value (if it's near 100%,                                              it means you need to add RAM to the server)
   SQL Page splits / sec - it's a specific amount of operations per second at memory management by the                                       SQL server. If the values in cells exceed a certain size, the SQL server must split                                        them among several memory pages - hence the name "Page split". If it happens                                       too often, it slows down the SQL server's operation. (indicatively, even a few                                             dozens for a few GB database is a bad number). This can be removed by                                                  maintenance of the SQL server by an SQL specialist

   SQL User connections - number of connections to the SQL server, for you to be able to tell if the                                                     performance parameters are adequate to number of users

    Setup can be found be below in the article 

 

2. SQL Transactions and Deadlocks v1.1. (template)
    SQL Active Transactions - one or more changes actively executed in the database at the time of                                            value reading. It's a field, for which you should choose limit values empirically,                                      since it depends on the application.

    SQL Deadlocks -  deadlock occurs, when two processes try to update the same record or                                                a record file
    Setup can be found be below in the article 

 

3. Custom performance counter
        It's possible to monitor any other performance counter using the Watch condition Performance            counter.
     An example of setup is given below in the article

 

 

Monitoring setup through templates

Both Watches from the above stated templates are configured directly on the WIN server with SQL server. You must enter its name or instance into the wizard. It's the only parameter that you will enter. Reading of the very performance counters happens directly on OS with the SQL server.

The following images capture the most important setup steps. The others are very similar to other Watches.

1. Setup of Watch through template SQL Memory, Page splits, users

The template is desctribed in introduction of the article, and we only further describe important points of setup .
Selection of template for monitoring of RAM, number of Page split operations /sec, number of active SQL connections.

Image: Výber šablóny k meraniu obsadenia RAM, počet operácií Page splits /sec, počet aktívnych SQL spojení.


Field to enter name of the SQL server or instance in the wizard  (to reopen this dialog, double click on the first condition in CHAT)

Image: Políčko pre zadanie názvu SQL servera alebo inštancie v rámci sprievodcu (pre znovu otvorenie tohto dialógu urobte dvojité kliknutie na prvú podmienku v CHATe)


Name selection for the SQL instance, it's usually SQLSERVER for the first one, and for the other it's usually MSSQL$instance_name.

You may finish the Watch's setup by defining actions or you can just skip them

Image: Dokončenie nastavenia Watchu môžete spraviť s definovaním akcií alebo ich preskočiť.


Illustration of a Watch from the template SQL Memory, Page splits, users

Image: Ukážka z Watchu zo šablóny SQL Memory, Page splits, users

 

2. Setup of Watch through template SQL Transactions and Deadlocks

The template is desctribed in introduction of the article, and we only further describe important points of setup .
Selection of template for measuring of the amount of SQL actions, deadlocks  and check of services

Image: Výber šablóny k meraniu počtu SQL transakcií, deadlockov a kontrola služieb


Field to enter name of the SQL server or instance in the wizard  (to reopen this dialog, double click on the first condition in CHAT)

Image: Políčko pre zadanie názvu SQL servera alebo inštancie v rámci sprievodcu (pre znovu otvorenie tohto dialógu urobte dvojité kliknutie na prvú podmienku v CHATe)


Name selection for the SQL instance, it's usually SQLSERVER for the first one, and for the other it's usually MSSQL$instance_name.

You may finish the Watch's setup by defining actions or you can just skip them

Image: Dokončenie nastavenia Watchu môžete spraviť s definovaním akcií alebo ich preskočiť.


Illustration of a Watch from the template SQL transactions and Deadlocks

Image: Ukážka z Watchu zo šablóny SQL transactions and Deadlocks

3. Setup of performance counters for monitoring of SQL of custom choice

Setup is realized by direct configuration of a Watch without the wizard. After opening the Watch and dialog for adding condition, choose the condition Performance counter. Then find the required Watch. To hepl you find it, we give you a hint that counters for SQL are among other counters, in alphabetical order, starting with name of the SQL instance. For a single or the first instance, it's SQLSERVER, for other instances it's MSSQL$instance_name.

After selection of the counter, you must choose a unit of the expected value. Unfortunately, you must choose it manually, because it cannot be read from the system, it's usually just a part of the counter's description in different forms. On the other hand, you get an option to convert the value from the counter to a more acceptable number (e.g. from high, difficultly read numbers in Bytes, to smaller, easily read MB or GB). Then enter a Friendly name, which will appear on CM Portal, and which you should choose to know what you're monitoring.
Procedure to create a Watch for monitoring of a selected counter without the wizard

Image: Postup k vytvoreniu Watchu na sledovanie zvoleného countra bez sprievodcu


Selection of the performance counter in system dialog

Image: Výber performance countra v systémovom dialógu


Selection of the performance counter's unit and units to be displayed in CM (with automatic conversion of units)

Image: Voľba jednotky performance countra a jednotky na zobrazenie v CM (s automatickým prepočtom jednotiek)

Example : Configured counter, whose unit is B/s, but the view on CM Portal will be in MB/sec under a more comprehensible Friendly name. The value will only be sent to the CM Server if it's changed for at least 10kB/sec, so that small changes don't unnecessarily fill the database on CM Server

Image: Príklad : Nastavený counter, ktorý má jednotku B/s, ale zobrazenie v CM Portáli bude v MB/sec pod zrozumiteľnejším Friendly názvom a do CM Servera bude posielaná hodnota len, keď sa zmení aspoň o 10kB/sec a malé zmeny zbytočne neplnia databázu v CM Server


Illustration in CM Portal of custom selection of performance counters for monitoring of SQL performance

Image: Ukážka v CM Portáli vlastného výberu performance countrov pre sledovanie SQL výkonu

 

Images: 
Výber šablóny k meraniu obsadenia RAM, počet operácií Page splits /sec, počet aktívnych SQL spojení.Políčko pre zadanie názvu SQL servera alebo inštancie v rámci sprievodcu (pre znovu otvorenie tohto dialógu urobte dvojité kliknutie na prvú podmienku v CHATe)Dokončenie nastavenia Watchu môžete spraviť s definovaním akcií alebo ich preskočiť.Ukážka z Watchu zo šablóny SQL Memory, Page splits, usersVýber šablóny k meraniu počtu SQL transakcií, deadlockov a kontrola služiebPolíčko pre zadanie názvu SQL servera alebo inštancie v rámci sprievodcu (pre znovu otvorenie tohto dialógu urobte dvojité kliknutie na prvú podmienku v CHATe)Dokončenie nastavenia Watchu môžete spraviť s definovaním akcií alebo ich preskočiť.Ukážka z Watchu zo šablóny SQL transactions and DeadlocksPostup k vytvoreniu Watchu na sledovanie zvoleného countra bez sprievodcuVýber performance countra v systémovom dialógu Voľba jednotky performance countra a jednotky na zobrazenie v CM (s automatickým prepočtom jednotiek)Príklad : Nastavený counter, ktorý má jednotku B/s, ale zobrazenie v CM Portáli bude v MB/sec pod zrozumiteľnejším Friendly názvom a do CM Servera bude posielaná hodnota len, keď sa zmení aspoň o 10kB/sec a malé zmeny zbytočne neplnia databázu v CM ServerUkážka v CM Portáli  vlastného výberu performance countrov pre sledovanie SQL výkonu