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
Abonneren op:
Reacties posten (Atom)
Geen opmerkingen:
Een reactie posten