|
Describe in brief SQL Server monitoring ways.Execution Plan
SQL Server caches the plan of execution of query or stored procedure which it uses in subsequent call. This is a very important feature with regards to performance enhancement. You can view execution plan of data retrieval graphically or textually.
SQL Profiler
SQL Profiler is a tool that stores events from the server.
SQL Profiler saves the events in the trace file.
The trace file can be analyzed later to monitor the performance of the queries.
Trace file is useful in diagnosing slow running queries.
You can set SQL Profiler to trace only those events in which you are interested.
Monitoring too many events will end up in large trace file.What is an execution plan?An Execution plan is most useful when a SQL query runs slowly. An execution plan gives details of the SQL Server query optimizer. It consists of set of steps that describes the information of the relational database. Since one query can be executed in several ways, execution plan provides a mechanism to manually analyze the performance.What is SQL Profiler?SQL Profiler is used to monitor events of SQL server. The profiler has a user friendly GUI that allows selected events to be traced and monitored. It is most useful when queries are performing slowly, debugging stored procedures, reviewing of all activities that took place on the server.Describe in brief SQL Server monitoring ways.Ways to monitor SQL Server:
Applications manager provides a web based interface to visualize, manage and monitor SQL Server data farms. It provides granular information about performance enabling administrators to understand the usage and hence also plan the capacity of the database and the server. SQL Server monitoring can connect to the database source and monitor various system table column values and be aware of any alarms.
Some of the attributes to monitor are:
Memory usage, SQL Statistics, Buffer manager statistics, Latch details, Connection statistics, Access method details, Cache details, Database details, Lock details and Scheduled jobs. One can also use Database query monitoring to monitor bottleneck queries. Database administrators may also use Windows Performance monitor. This allows them to monitor the overall performance of the servers.
|
