SQL Server Monitor
SQL server is a database system which is the most popular and preferred database for backend solutions for critical business systems. With its so many features, it deserves such a superlative status. As a matter of fact, there have been cases where users stopped working due to the unavailability of the database or it was in a very bad shape. If you are a SQL DBA user, then it is important that you know in what shape your SQL server is in, so that it becomes easier to take necessary steps to minimize the problems pertaining to the system. But, nothing to get afraid of, if the tools to counter the problem is already in place. These tools will warn you beforehand, which will help you to prevent any technical issue. The performance behavior of the server will let you know the problem it is facing, but the same will not be visible to the system's end-users.
It is always necessary for any SQL user to know the system well because without proper knowledge about the system, you will not be comfortable working on it. To know what the system has been going through, a reliable and effective monitoring tool must be in place. It should be noted that a SQL monitoring tool is extremely crucial especially in a project lifecycle.
In order to have perfect knowledge about the system, you must be thorough with stress testing, monitoring, base lining and performance tuning. Here, you will get an insight on how to put up a monitoring system right from retrieving important information about the SQL system to scenarios that are way more advance and ideal for long term system monitoring. Moreover, to monitor SQL server, you don't need third party fancy tools, but simple tools like "Performance Monitor (PerfMon), Excel and SQL Profiler" will do the job.
Techniques for SQL server monitoring
1. Fast and effective
All the internal SQL performance counters are available in the form of tables in the master.dbo.sysperfinfo and this is displayed through PerfMon. One can easily retrieve all the information like the "Log Bytes Flushed/sec" directly from the sysperfinfo.
2. Setting up PerfMon
There are certain steps that you can follow for setting up PerfMon. There are certain settings that are not related to BOL. Here, you will have to create DSN, which should be on the production server that is connected directly with the SQL server monitor system. Next, you will have to change the Log on as "Performance Logs and Alerts" for user on the production server.
3. Monitoring database setup
After, setting up the PerfMon, you will witness that a base table set has been created. For using these set of tables in queries, creating table indexes is necessary.
Collecting profiler data is difficult and to retrieve the data, you will require a filter process to get useful data from the column of text data. Here, you will have to use ADO recordSet. Update Batch feature but customizing the script is required.
5. Visualization setup for Excel
The data extracted will be stored in the form of tables in SQL server. You can see the data in the form of graphs with the help of Excel. You can also use Reporting Services, if you are comfortable with that.
You can use PerfMon alert mechanism to get detailed log events and to generate alerts in the event log application. This is a tool from where you can not only capture but also transfer the events through beepers and control room.
7. Digital dashboard
Here, you will require a graphical user interface to store values and handle alerts from the database table. This is a batch process through which you can compare stored values and generate alerts.
8. Data consolidation
Creating tables will help you in data consolidation for PerfMon results and all of these data will appear in the form of a star schema. You can also build a OLAP cube based on this.
These are some of the unique and best techniques to monitor SQL server without the assistance of third party application tools.