SQL Server - monitoring reports

I am manning the ask the experts SQL server booth at TechEd this week. One of the many interesting questions I was asked was how the reporting information from the standard reports in SQL server could be integrated into one set of data from multiple servers.

 

I couldn't think of anything immediately to help him, so I am posting my thoughts here.

The reports are using Reporting Services from a SQL source, but I couldn't find out what this was without running profiler - so that's what I had to do.  I setup a new trace to watch for  RPC complete events with a column filter on application name set to "Microsoft SQL Server Management Server".  I then ran the Activity - All Session report:

and then stopped the trace and looked at the relevant event:

as you can see the SQL looks long but it's mainly columns, there are only four tables involved:

• sys.dm_exec_sessions
• sys.dm_exec_connections
• sys.dm_exec_requests

Of course in some of the other reports there are multiple charts and data regions so the underlying query will be much more complex, but this can be easily broken down and will only hit four or five table for each sub-query.

The next step is how to combine and extract this.  Personally I would use Integration Services  to run this query for each server with the query as the source and put this out  to one table. On the way throw you would tag every row with the server it came in a new column. the final step would ne to fire this out to excel and schedule it to run as required.

Another approach would be to do the who;e thing in one stored procedure which would link to each server in turn and would run on demand.

Technorati Tags: SQL Server,SQL

Comments

• Anonymous
  November 15, 2007
  It's something which is always being asked on the forums and solutions usually involve complex solutions with linked servers which might not be what you want.Sounds fine for 6 servers but maybe not for 200. An oft found problem with the process of linking all the server data together is what happens when a server is not there - you have to make sure the error handing is really good. What I'd possibly say is that there are products out there, like Idera SQL Diagnostic Manager, sometimes it makes sense to buy a tool to handle the basic stuff rather than re-inventing the wheel and spending lots of time ( which has a cost too ) to develop your own. The best solution is to return data to a central server and work on it there - just be wary of the overhead you may apply to your system, network and servers.

• Anonymous
  November 16, 2007
  Blogging is pretty new for me and it's difficult to know how interesting or valuable my stuff is. So

• Anonymous
  November 16, 2007
  I spoke with Andrew Fryer yesterday via e-mail when I noticed he was also covering some of the same information