How CM works

Customer Support

+421-2-5465-0242

send email >>

Online support - chat (bottom right)

MS SQL performance by general parameters

If you use dedicated server for SQL, the first look on the server's status can be found at Online information, which provide history for the past 48 hours, and the Watch from the template General Performance, which provides history for the past 60 days, but mailny informs about eventual overload. The Watch General performace consists of almost the same parameters as are displayed in Online information :
       CPU Usage System - it's necessary that there's always some reserve, i.e. that the CPU should rarely reach over 90%
         Used Physical Memory - total occupied physical RAM, which is often the maximum for SQL and therefore it's not the main relevant parameter
        Disk activity - best reflects overload of the SQL server from the general parameters, if the system is overloaded, this value reaches the upper percents
         Disk Avg Read/Write - if the system is overloaded, one of the values is higher than 2.  Thanks to this parameter, you can determine if the overload is on the side of reading or writing.

There's a template prepared for the configuration, whose activation is very simple thanks to the wizard in C-Monitor Console in Watches. The procedure is illustrated on the following images.

Below you can find illustration images from Online informations for an overloaded state of the SQL server and the state before.

Entry to watches and addition of conditions for monitoring of SQL server's General performance

Image: Vstup do watchov a pridanie podmienok pre sledovanie SQL serverov General performance


Overview of parameters that are included in the template, to which you can adjust limit values for occurence of error, afterwards continue according to the wizard (set or skip the setup of actions)

Image: Prehľad aké parametre sú zahrnuté v šablóne a môžete upraviť limity k vzniku chyby, ďalej pokračujte podľa sprievodcu (nastavte alebo preskočte nastavenie akcií)


Example of state of watches from the template  General Performance

Image: Ukážka stavu watchov zo šablóny General Performance


Illustration from Online information from the SQL server, when the server isn't overloaded. Despite the high disk activity, the value of Disk queue is not increased (it's always less than 1)

Image: Ilustračný obrázok Online informácií z SQL servera, keď server nie je preťažený. Aj napriek vysokej aktivite disku nedochádza k zvyšovaniu hodnoty Disk queue (stále je max 1)


Illustration from Online information from the SQL server, when the server IS overloaded. Disk activity appears lower in time, but the value of Disk queue Write reaches up to 4. In practice, this was reflected as slowdown

Image: Ilustračný obrázok Online informácií z SQL servera, keď server JE preťažený. Aktivita disku je síce v čase nižšia, ale hodnoty Disk queue Write nadobúda až hodnotu 4. Toto sa aj v praxi prejavilo spomalením.



Illustration of Online information with transfer on the network card, when the SQL server wasn't overloaded

Image: Ilustrácia Online informácií s prenosom na sieťovej karte, keď SQL server preťažený nebol


Illustration of Online information with transfer on the network card, when the SQL server WAS overloaded

Image: Ilustrácia Online informácií s prenosom na sieťovej karte, keď SQL server BOL preťažený.


 

Images: 
Vstup do watchov a pridanie podmienok pre sledovanie SQL serverov General performancePrehľad aké parametre sú zahrnuté v šablóne a môžete upraviť limity k vzniku chyby, ďalej pokračujte podľa sprievodcu (nastavte alebo preskočte nastavenie akcií)Ukážka stavu watchov zo šablóny General PerformanceIlustračný obrázok Online informácií z SQL servera, keď server nie je preťažený. Aj napriek vysokej aktivite disku nedochádza k zvyšovaniu hodnoty Disk queue (stále je max 1)Ilustračný obrázok Online informácií z SQL servera, keď server JE preťažený. Aktivita disku je síce v čase nižšia, ale hodnoty Disk queue Write nadobúda až hodnotu 4. Toto sa aj v praxi prejavilo spomalením.Ilustrácia Online informácií s prenosom na sieťovej karte, keď SQL server preťažený nebolIlustrácia Online informácií s prenosom na sieťovej karte, keď SQL server BOL preťažený.