Monitoring SQL  «Prev  Next»

Lesson 1

Monitoring SQL Server with SQL and SQL-EM

Monitoring SQL Server

As a system administrator, one of your tasks is to monitor SQL Server. While there are many tools that can be used to provide different information, using SQL to monitor SQL Server provides consistent information no matter what client platform you are using.

SQL Server database systems are more than ever before being chosen as the preferred backend database solution for large business's critical systems.
And SQL Server richly deserves this status. As a result of this, more users than ever before are blocked in their daily activity when the database is not available, or in a bad shape. It is your responsibility as a DBA to know the health of your SQL Server so you can take proactive action to minimize chances anything bad happens to your system. But do not panic. If you have the right tools in place, your system will tell you how it feels and will warn you well before it reaches the alarm phase. Through its performance behavior, you can spot potential problems not yet visible to the end-user. If, for example, for no reason response times of a certain query slow down from 100 to 500 ms, the end-user will not be alarmed, but you should.
I am not only addressing those DBA's among you who are responsible for SQL Server systems with thousands of users connected. If your system goes down with only 10 users connected, they will haunt you for not delivering what they expect you to. I don't want to frighten you or put unnecessary pressure on you, because I consider being a SQL Server DBA is probably the greatest jobs there is, you should feel comfortable with the system you are responsible for. This can only be achieved if your system tells you what is going on inside. That is why you should have reliable monitoring tools in place, starting even before going live with a new piece of software.
You will have to convince your manager that investing in monitoring this early in a project’s lifecycle is no luxury.
Solar Winds
Solar Winds

As a SQL Server system administrator, you need to verify the health of your SQL Server and troubleshoot problems. SQL Server has multiple tools to aid you in these tasks. This module discusses how to use SQL and SQL-EM to monitor SQL Server.

Learning objectives

By the time you complete this module, you should be able to Monitor SQL Server connections
  1. Monitor SQL Server connections
  2. Use SQL-EM to montior connections
  3. Monitor SQL Server locks
  4. Use SQL-EM to monitor locks
  5. Monitor overall system performance
  6. Monitor SQL Server with database consistency checker
  7. Use dbcc buffer to monitor the data cache
  8. Use dbcc to monitor overall performance
  9. Monitor SQL Server with trace flags
  10. Monitor SQL Server with built-in functions
The next lesson will cover how to monitor connections.

SQLEM: Fast Clustering in SQL using the EM Algorithm (2000)

Clustering is one of the most important tasks performed in Data Mining applications. This paper presents an efficient SQL implementation of the EM algorithm to perform clustering in very large databases. Our version can effectively handle high dimensional data, a high number of clusters and more importantly, a very large number of data records. We present three strategies to implement EM in SQL: horizontal, vertical and a hybrid one. We expect this work to be useful for data mining programmers and users who want to cluster large data sets inside a relational DBMS.