Using SSIS to monitor SQL Server Databases

Analyzing SQL Server Connections Over Time Using SSIS :
=======================================================
http://www.simple-talk.com/sql/database-administration/using-ssis-to-monitor-sql-server-databases-/


I wanted to know if there were any long-running jobs or processes in any of the eighty-odd SQL Servers that I manage. These are a sign of a problem that has to be investigated and put right. I’d developed a simple query to get the process Information from each database, but I wasn’t sure if this would interfere with its performance if it ran once every hour in a SQL Agent job: if so, was it worth it?

I soon realized that this could do much more than to isolate long running processes: I found that I could now deep dive into the data for trend analysis, capacity planning, concurrency issues, and even potential security breaches. In this article I will demonstrate the simple SSIS package that I use to collect and gather all the process information for these servers. I’ll also show you the queries that I use to analyze the hundreds of thousands of records I collected in just the first week .

Connection Collection
I’ve just said that the query to return the results that I needed was simple. Listing 1 should back me up on that. Notice, though, that even though it is simple, almost everything that you could want is returned. Sure, there are other fields that I could have used, like a “blocked_by” column, the same as the sp_who2 “blkby” column would provide. Also, it would have been useful to know the actual query that was being executed at the time, but this is not a server trace after all. To use a drummer’s cliché, “Less is more.” What I needed was a way to identify potential problems and then use other tools to get the details.

Listing 1: Simple Connections Query

SELECT GetDate() as Run_Date,

rtrim(Cast(@@ServerName as varchar(100))) as Server,

spid,

blocked,

waittime,

sb.name,

lastwaittype,

sp.cpu,

sp.login_time,

sp.last_batch,

sp.status,

sp.hostname,

sp.program_name,

sp.cmd,

sp.loginame,

getdate() - last_batch as duration

FROM master..sysprocesses sp

INNER JOIN master..sysdatabases sb ON sp.dbid = sb.dbid

More Information
http://msdn.microsoft.com/en-us/library/ms139805.aspx