{"id":549,"date":"2013-09-12T17:17:44","date_gmt":"2013-09-12T22:17:44","guid":{"rendered":"http:\/\/www.poweradmin.com\/blog\/?p=549"},"modified":"2015-04-27T08:37:33","modified_gmt":"2015-04-27T13:37:33","slug":"how-to-monitor-ms-sql-server","status":"publish","type":"post","link":"https:\/\/www.poweradmin.com\/blog\/how-to-monitor-ms-sql-server\/","title":{"rendered":"How to Monitor MS SQL Server"},"content":{"rendered":"<p>\n\t<a href=\"\/blog\/wp-content\/uploads\/2013\/09\/Monitoring-SQL-Server2.png\" rel=\"\" style=\"\" target=\"\" title=\"\"><img loading=\"lazy\" decoding=\"async\" alt=\"Monitoring SQL Server\" class=\"alignleft size-medium wp-image-553\" height=\"257\" src=\"\/blog\/wp-content\/uploads\/2013\/09\/Monitoring-SQL-Server2-300x300.png\" style=\"margin-left: 0px; margin-right: 15px;\" title=\"\" width=\"257\" srcset=\"https:\/\/www.poweradmin.com\/blog\/wp-content\/uploads\/2013\/09\/Monitoring-SQL-Server2-300x300.png 300w, https:\/\/www.poweradmin.com\/blog\/wp-content\/uploads\/2013\/09\/Monitoring-SQL-Server2-150x150.png 150w, https:\/\/www.poweradmin.com\/blog\/wp-content\/uploads\/2013\/09\/Monitoring-SQL-Server2.png 463w\" sizes=\"auto, (max-width: 257px) 100vw, 257px\"><\/a>\n<\/p>\n<p>\n\tThis will be the third in our blog series about monitoring. The first covered <a href=\"\/blog\/how-to-monitor-iis\/\">monitoring IIS<\/a> and the second, <a href=\"\/blog\/monitoring-asp-net\/\">monitoring ASP.NET<\/a>. Please take a look at these first to get some basic information about how to monitor different Windows products. In today\u2019s article we will focus on how to use monitoring software to visualize, troubleshoot and check <a href=\"\/help\/sm_5_2\/config_database_settings.aspx\">Microsoft SQL Server<\/a> performance counters. We will start talking a little about MS SQL in general and then we\u2019ll dig more into describing performance counters, services and ideal values that are needed to consider when monitoring Microsoft\u2019s SQL Server.\n<\/p>\n<p>\n\t\u00a0\n<\/p>\n<h3>\n\t<span style=\"color:#0066ff;\"><strong>What is MS SQL Server?<\/strong><\/span><br>\n<\/h3>\n<p>\n\t<a href=\"http:\/\/www.microsoft.com\/en-us\/sqlserver\/product-info.aspx\" rel=\"nofollow\" style=\"\" target=\"_blank\" title=\"\"><img loading=\"lazy\" decoding=\"async\" alt=\"MS SQL Server 2012\" class=\"alignright size-medium wp-image-559\" height=\"230\" src=\"\/blog\/wp-content\/uploads\/2013\/09\/ms-sql-server-box-273x300.png\" style=\"margin-left: 15px; margin-right: 0px;\" title=\"\" width=\"210\" srcset=\"https:\/\/www.poweradmin.com\/blog\/wp-content\/uploads\/2013\/09\/ms-sql-server-box-273x300.png 273w, https:\/\/www.poweradmin.com\/blog\/wp-content\/uploads\/2013\/09\/ms-sql-server-box.png 300w\" sizes=\"auto, (max-width: 210px) 100vw, 210px\"><img class=\"extlink-icon\" src=\"https:\/\/www.poweradmin.com\/blog\/wp-content\/plugins\/external-links-nofollow-open-in-new-tab-favicon\/images\/extlink.png\"><\/a>MS SQL Server is a product offered by Microsoft that is used in database environments. Unlike MySQL, which is the Linux version of a database system, MSSQL comes only with a licensed version. This means that you will have to pay for each of your MSSQL products. MSSQL is known as a relational database management system (RDMS) whose main responsibility is to store and retrieve data that are requested by different software applications. You\u2019ve probably heard a lot about database requests or queries that are intended to retrieve information hosted in databases. The main query languages used with MSSQL are <a href=\"http:\/\/en.wikipedia.org\/wiki\/Transact-SQL\" title=\"Transact-SQL\" rel=\"nofollow\" target=\"_blank\">T-SQL<img class=\"extlink-icon\" src=\"https:\/\/www.poweradmin.com\/blog\/wp-content\/plugins\/external-links-nofollow-open-in-new-tab-favicon\/images\/extlink.png\"><\/a>\u00a0and\u00a0<a href=\"http:\/\/en.wikipedia.org\/wiki\/SQL\" title=\"SQL\" rel=\"nofollow\" target=\"_blank\">ANSI SQL<img class=\"extlink-icon\" src=\"https:\/\/www.poweradmin.com\/blog\/wp-content\/plugins\/external-links-nofollow-open-in-new-tab-favicon\/images\/extlink.png\"><\/a>. Almost all products ranging from websites, software applications, games, etc. are using some sort of relational database.\n<\/p>\n<h3>\n\t<span style=\"color:#0066ff;\"><strong>Monitoring Disk Usage of an SQL Server<\/strong><\/span><br>\n<\/h3>\n<p>\n\tWe will start discussing about monitoring disk usage of an SQL server. MSSQL uses Microsoft Windows operating system input\/output (I\/O) calls to write and read data on disks. The Server\u2019s disks can be either local attached storage or shared storage from a NAS device. The SQL server is responsible for how and when I\/O operations are made. With that being said, Disk I\/O operations and Excess Paging must be monitored. It\u2019s best to isolate disk operations caused by the SQL Server and monitor them carefully. Disk performance counters that can be monitored include:\n<\/p>\n<ul>\n<li>\n\t\tPhysical Disk \u2013 Avg. Disk sec\/Read\n\t<\/li>\n<li>\n\t\tPhysical Disk \u2013 Avg. Disk sec\/Write\n\t<\/li>\n<li>\n\t\tPhysical Disk \u2013 Disk Reads\/sec\n\t<\/li>\n<li>\n\t\tPhysical Disk \u2013 Disk Writes\/sec\n\t<\/li>\n<\/ul>\n<h3>\n\t<span style=\"color:#0066ff;\"><strong>Monitoring Memory and CPU Usage<\/strong><\/span><br>\n<\/h3>\n<p>\n\tTwo of the most important elements that must be <a href=\"\/servermonitor\/\">monitored on a SQL server are memory and CPU usage<\/a>. This means that activity made by the MSSQL server that has direct impact on the hardware devices should be isolated from the rest. Microsoft uses a dynamic memory allocation system meaning that the SQL server will request extra memory from the system if needed or will release it if there are no requirements. This allocation is made by querying the OS for the available physical memory. Memory allocation can be configured manually so that a certain amount of memory is permanently reserved for the SQL server.\u00a0\n<\/p>\n<p>\n\tIn order to analyze and troubleshoot memory activity on your Microsoft SQL Server, make sure the following performance counters are added to the monitoring tool:\n<\/p>\n<ul>\n<li>\n\t\tProcess: Working Set \u2013 memory used by a single process\n\t<\/li>\n<li>\n\t\tSQL Server: Memory Manager: Total Server Memory (KB)\n\t<\/li>\n<li>\n\t\tSQL Server: Buffer Manager: Database Pages\n\t<\/li>\n<li>\n\t\tSQL Server: Buffer Manager: Buffer Cache Hit Ratio \u2013 percentage of requests resolved by the information stored in the cache.\n\t<\/li>\n<\/ul>\n<p>\n\tIn terms of CPU usage, it all comes down to purpose of the SQL Server. A CPU is an important hardware component that has to be monitored permanently. With that being said, consider the number of CPUs and cores that will be used on the SQL server. Any high usage can indicate that the hardware equipment is not appropriate for the Server\u2019s operation. In terms of CPU usage, the following performance counters should be monitored:<a href=\"\/blog\/wp-content\/uploads\/2013\/09\/performance-counters-speed.jpg\" rel=\"\" style=\"\" target=\"\" title=\"\"><img loading=\"lazy\" decoding=\"async\" alt=\"Performance Counters\" class=\"alignleft size-medium wp-image-563\" height=\"249\" src=\"\/blog\/wp-content\/uploads\/2013\/09\/performance-counters-speed-300x300.jpg\" style=\"margin-left: 0px; margin-right: 30px;\" title=\"\" width=\"249\" srcset=\"https:\/\/www.poweradmin.com\/blog\/wp-content\/uploads\/2013\/09\/performance-counters-speed-300x300.jpg 300w, https:\/\/www.poweradmin.com\/blog\/wp-content\/uploads\/2013\/09\/performance-counters-speed-150x150.jpg 150w, https:\/\/www.poweradmin.com\/blog\/wp-content\/uploads\/2013\/09\/performance-counters-speed-1024x1024.jpg 1024w, https:\/\/www.poweradmin.com\/blog\/wp-content\/uploads\/2013\/09\/performance-counters-speed.jpg 1200w\" sizes=\"auto, (max-width: 249px) 100vw, 249px\"><\/a>\n<\/p>\n<ul>\n<li>\n\t\tProcessor: % Privileged Time \u2013 percentage of time CPU takes to execute kernel commands. This can also refer to the time spend to execute commands that are related to the SQL Server such as I\/O requests.\n\t<\/li>\n<li>\n\t\tProcessor: %User Time \u2013 percentage of time CPU takes to execute user processes such as SQL Server\n\t<\/li>\n<li>\n\t\tProcessor: % Processor Time \u2013 percentage of time CPU requires to execute threads. You can also add \u00a0%Total Processor Time to check the performance of all processors\n\t<\/li>\n<\/ul>\n<p>\n\tSystem: Processor Queue Length \u2013 this performance counter measures the number of threads that are waiting in the processor\u2019s queue to be executed. A process can have one or multiple threads and if the CPU cannot execute requests as they are sent, they will be stacked in the queue. If threads require more CPU cycles than are available it could end in a processor bottleneck.\n<\/p>\n<p>\n\tIt is recommended that the server run a dedicated instance of the server if your SQL Server processes many calculations. By configuring MS SQL Server as such, you can rest assured that the hardware resources will be used only by the SQL instance and not shared with other intense usage processes. Bottlenecks can also indicate that the CPU is not fast enough and requests are stacked because the processor cannot execute them in the allocated time. In this case it\u2019s best to change the hardware or add additional CPUs.\n<\/p>\n<h3>\n\t<span style=\"color:#0066ff;\"><strong>Tools to Troubleshoot MS SQL Performance<\/strong><\/span><br>\n<\/h3>\n<p>\n\t<a href=\"\/blog\/wp-content\/uploads\/2013\/09\/tools.jpg\"><img loading=\"lazy\" decoding=\"async\" alt=\"Tools to Troubleshoot MS SQL Performance\" class=\"alignright size-medium wp-image-565\" height=\"252\" src=\"\/blog\/wp-content\/uploads\/2013\/09\/tools-300x252.jpg\" width=\"300\" srcset=\"https:\/\/www.poweradmin.com\/blog\/wp-content\/uploads\/2013\/09\/tools-300x252.jpg 300w, https:\/\/www.poweradmin.com\/blog\/wp-content\/uploads\/2013\/09\/tools.jpg 540w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\"><\/a>There are three main tools that can help you troubleshoot MS SQL performance issues:\n<\/p>\n<p>\n\t<strong><span style=\"color:#009900;\"><em>Using the SQL Profiler trace log<\/em><\/span><\/strong> \u2013 this powerful utility is one of the best to use for troubleshooting MS SQL Server\u2019s performance. SQL Profiler is used to monitor all activity running on the Server. With this tool you can analyze data before bottlenecks are made, making it easier to determine the root cause. It is also useful for troubleshooting slow queries and responses and to determine bad application configurations. If your server handles many calculations, it is suggested that the Index Tuning Wizard tool be used. The Index Tuning tools optimizes SQL queries, determines if indexes are used properly, and can also be used to increase the overall performance of the SQL server.\n<\/p>\n<p>\n\t<strong><span style=\"color:#009900;\"><em>Blocker script output<\/em><\/span><\/strong> \u2013 a mechanism that once configured properly can be used to easily determine system blockings or troubleshoot performance problems. Data gathered from blocker scripts can be incorporated into monitoring software for better analysis and troubleshooting. We won\u2019t touch on how to implement blocker scripts because this can be quite complicated and could require an entirely separate article.\n<\/p>\n<p>\n\t<strong><span style=\"color:#009900;\"><em>SQL Server Performance Monitor log<\/em><\/span><\/strong> \u2013 the performance tool available with MS SQL. Using it you can monitor performance counters that are responsible for bottlenecks like memory and CPU usage, system blocking, etc. For troubleshooting the server\u2019s performance we have to enable the performance counters. Gathering data can be done on the local SQL Server or it can be sent to the monitoring tool on a remote machine. Make sure that the following data is included in the analysis (information taken from <a href=\"http:\/\/support.microsoft.com\/kb\/298475\" rel=\"nofollow\" target=\"_blank\">Microsoft\u2019s website<img class=\"extlink-icon\" src=\"https:\/\/www.poweradmin.com\/blog\/wp-content\/plugins\/external-links-nofollow-open-in-new-tab-favicon\/images\/extlink.png\"><\/a>): Paging file, Process, Processor, All SQL Server counters, Memory, Threads, Logical disk, Physical disk and System.\n<\/p>\n<p>\n\tThe perfect combination for monitoring an MS SQL server depends a lot in the purpose of the server, the hardware configuration, the number of computations and many other factors. At minimum, the following combination should be used for monitoring an SQL Server:\n<\/p>\n<ul>\n<li>\n\t\tProcessor \u2013 % Processor Time\n\t<\/li>\n<li>\n\t\tPaging File \u2013 % Usage\n\t<\/li>\n<li>\n\t\tMemory \u2013 Available Mbytes\n\t<\/li>\n<li>\n\t\tSystem \u2013 Processor Queue Length\n\t<\/li>\n<li>\n\t\tSQL Server: Buffer Manager \u2013 Page life expectancy\n\t<\/li>\n<li>\n\t\tSQL Server: General Statistics \u2013 User Connections\n\t<\/li>\n<li>\n\t\tSQL Server: Memory Manager \u2013 Memory Grants Pending\n\t<\/li>\n<li>\n\t\tSQL Server: SQL Statistics \u2013 Batch Requests\/sec\n\t<\/li>\n<li>\n\t\tSQL Server: SQL Statistics \u2013 Compilations\/sec\n\t<\/li>\n<li>\n\t\tSQL Server: SQL Statistics \u2013 Recompilations\/sec\n\t<\/li>\n<li>\n\t\tPhysical Disk \u2013 Avg. Disk sec\/Read\n\t<\/li>\n<li>\n\t\tPhysical Disk \u2013 Avg. Disk sec\/Write\n\t<\/li>\n<li>\n\t\tPhysical Disk \u2013 Disk Reads\/sec\n\t<\/li>\n<li>\n\t\tPhysical Disk \u2013 Disk Writes\/sec\n\t<\/li>\n<\/ul>\n<p>\n\tOptimal values that should alert system administrators are hard to be determined. If the system is running a dedicated instance of the MS SQL Server, the overall usage can rise up to 80% because in case of bottlenecks only the SQL instance is affected. If you are using a shared system then it is recommended that the SQL processes should not consume more than 30-40% of system resources. It all comes down to the System Administrators decisions on what values are optimal, when alerts should be raised and what steps are taken to troubleshoot and fix the Server\u2019s performance.\n<\/p>\n<p>\n\tWe hope this article will you understand more about monitoring MS SQL Server, please share your thoughts about this topic. Have a great day and stay tuned for following articles.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This will be the third in our blog series about monitoring. The first covered monitoring IIS and the second, monitoring ASP.NET. Please take a look at these first to get some basic information about how to monitor different Windows products. In today\u2019s article we will focus on how to use monitoring software to visualize, troubleshoot [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":553,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,5,9],"tags":[],"class_list":["post-549","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-general-it","category-how-to","category-technical"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/posts\/549","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/comments?post=549"}],"version-history":[{"count":5,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/posts\/549\/revisions"}],"predecessor-version":[{"id":3581,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/posts\/549\/revisions\/3581"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/media\/553"}],"wp:attachment":[{"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/media?parent=549"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/categories?post=549"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/tags?post=549"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}