Grumpy Old DBA - Beware the darkside !

I sometimes discover that the built in reports for SQL Server within SSMS are an unknown, sometimes this is because not all the right components were installed during the server build, other times is because generally there’s never been great reporting for the DBA from the SQL Team so no-one expects to find anything useful for free!

We did get the performance dashboard with SQL 2005 and this can be made to work with SQL 2008 with just a few code changes.

We also have the MDW database/management reports, but although I have this running on a couple of servers I’ve never really fallen in love with it.

( Once upon a time you could download the rdls for these reports but as far as I can tell the links are no longer available, I don’t know if this is deliberate. )

I use a few of these reports now and again, I have my own dashboards, reports and queries which are customised to particular environments, but if I’m faced with an “unknown” server then these reports will often be my first port of call.

Like anything using the dmvs remember that under pressure dmvs may shed or stop collecting data. Data is usually cumulative since last service restart, is cleared on a service restart and certain data may be cleared under specific circumstances.

The only way to collect dmv data for analysis is to take regular snapshots of the data into permanent tables of your own and work from there. At best some dmv data can lead you into believing you have problems where you don’t.

The relationships between dmvs can sometimes be confusing; obtaining information about partitioned tables is a good example of where it’s not as easy as you might wish.

Anyway Reports are available by context when you right click in SSMS object explorer; here I’m clicking on the actual instance/server.

( Note that to protect the innocent names and data have been changed/blanked where appropriate )

1. Reports by Server/Instance

To start off open SSMS, register your Server and right click on the instance/server

Choose Reports – Standard Reports - to view available reports for that context.

Here’s the list of Standard Reports for the instance, 23 reports in all and appearing to cover a number of useful areas.

Click on the individual report you wish to run to bring it to screen.

• I’m not going to screenshot every report but I will include a few examples.
• The main problem is that some of what might appear useful take too long to drill down and your actual chance of finding what you’re looking for is very slim, unless you only have a few connections.
• Some reports I’ve never been able to entice into producing an output.
• I’ll summarize this briefly below.

Starting with Server Dashboard which sounds better than it is:

• There are 24 databases on this server and as you can see the two charts do not display correctly, in fact I am totally unable to make them display correctly and neither the export or print option will produce a correctly formatted report.
• Right click to export to pdf or excel, pdf never formats correctly
• I personally would have like web archive as an option here.
• There’s no drill down and to be honest not a great deal of much use.

Non Default Configuration Options expand to show server options – I’m not sure I quite agree with “ Non Default” this sort of infers you’re not running a standard box, anyway this is from a production box showing what you might see. ( these are my “non default” settings! )

You might like to compare to the front screen of the Performance Dashboard which drills down on just about everything and also auto refreshes.

( Note that this report has been visually formatted to give a look and feel which matches other reports I use )

Back to SSMS Reports - Here’s the memory report, there’s drill down by object type too, not shown here.

Here’s one of my own reports: ( sorry can’t make it smaller and readable ! )

The Processes Report – generally far too unwieldy. ( drill down  just too many clicks  shown after )

Top Queries ( statements ) by cpu:

• · Can’t click on the chart to select a statement
• · Statements are abridged
• · Drill down does give object ID so you can track back to the procedure.

    2. Reports by Database – right click on a user database for these

The basic disk usage report is useful, especially as it shows autogrow activity – which should never be like this for a production database – but I don’t fully administer this system. ( as usual names changed to protect the innocent! )

Backup information.

Successful operations list each backup with file details etc. If your logs back up every 5 mins then this is a lengthy report!

  3. Reports for SQL Agent

Right clicking on the SQLAgent will show the following two reports.

Top Jobs report is interesting, I’m not sure about the Job Steps !

   4. Reports for Management

There are also two reports available from Management, but I am unable to see what value they have.

To summarise; If you have no reporting and you didn’t know these reports were available then you will probably find these better than nothing.

The main advantage these have over a query is that most are graphical and that well known phrase “ A picture paints a hundred words ” still holds true.

Users :

· How you deal with user activity on a server may depend upon your application(s), there’s always been sp_who and sp_who2 although I rarely ever use them.

· So why don’t I use these handy procedures and do I have my very own sp_who3 for example? Well the short answer is no because I tend to have worked with web facing applications, this doesn’t mean these are public applications but corporate applications that use a typical n-tier architecture.

· For this reason I’ve rarely been interested in who is connected to my database but more a case of what is connected.

· I’m also usually concerned with performance, monitoring and trending in an attempt to be pro-active rather than re-active, to this end I still have my eye on real time data mining as a way to predict potential issues with the production platform(s).

· So if you have 1,000 users and they all connect as “sa” or “AppUser” and/or you have a number of databases on your instance then sp_who and derivitives are probably not going to be too much use to you.

· I mentioned being pro-active and by this I mean I have processes to alert me if I think there’s going to be a problem, these are based upon thresholds of “normal” use and I have a dashboard which updates every 10 seconds to give me a visual heads up.

· Anyway, these are some of the queries concerned with user connections that I have to hand. I store useful scripts in a common location on all servers and my workstations, mostly I work in rdp sessions on the servers themselves, the reasons for this are out of scope here.

First up there’s a query which relies upon sys.sysprocesses, but if you’re still working with SQL Server 2000 this query will work if you change the table name from sys.sysprocesses to dbo.sysprocesses ( I don’t have a copy of SQL 2000 to test this on so apologies if I am incorrect )

--
select Count(*) from master.sys.sysprocesses where status='runnable' and dbid=7  and spid>50 ;
go
select right(convert(varchar,last_batch),7) as [Time],rtrim(loginame) as [LogonName],spid,open_tran as [Tran],kpid,blocked as [blocker],waittype,waittime
,lastwaittype,rtrim(waitresource) as [Resource],db_name(dbid) as DB,cpu,physical_io,[memusage],rtrim(status) as [Status]
,hostname,program_name,cmd,left(nt_username,20) as [NTuser] from master.sys.sysprocesses with (nolock) where 
--dbid=7 and 
spid > 50
order by --status asc, 
blocked desc,waittime desc,[tran] desc
go

Why is a query better than a procedure? Because I can customise it to whatever situation I’m looking at, I can also change column order and add/remove columns depending upon my requirements.

My first query ( above ) just returns the number of active processes, in certain circumstances spid 50 can actually be a system process so you may wish to change this to spid 51, you may of course wish to include system processes anyway.

The following queries use dmvs only:

--
-- get what's running
--
select 
dess.last_request_start_time as 'Last Batch Start', 
dess.last_request_end_time as 'Last Batch End',
dess.login_name, 
dess.session_id as 'SPID',
der.open_transaction_count as 'Open Tran',
der.blocking_session_id as 'Blocker',
isnull(der.wait_type,'') as 'Current Wait',
der.wait_time as 'Wait Time',
isnull(der.last_wait_type,'') as 'Last Wait',
isnull(der.wait_resource,'') as 'Waiting ON',
ISNULL(db_name(der.database_id),'') as 'Database',
dess.cpu_time as 'CPU Time',
dess.logical_reads as 'Reads',
dess.writes as 'Writes',
dess.[status],
dess.[host_name],
dess.[program_name],
der.command as 'Command',
eqp.query_plan
from sys.dm_exec_sessions dess 
join sys.dm_exec_connections decc on decc.session_id = dess.session_id
join sys.dm_exec_requests der on dess.session_id = der.session_id
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) eqp
where dess.session_id>50
order by Blocker desc;

Like the previous example you can filter or sort for specifics, again I’m usually troubleshooting here. This query ( above ) eliminates the clutter of non running connections and also presents the option of examining the query plan of what is running.

This query also works on SQL 2005 but you will need to save the xml from the queryplan with a file extension of .sqlplan then open it in SSMS to see the graphical plan, in SQL 2008 you see the graphical plan.

--
-- get what's connected
--
select 
dess.last_request_start_time as 'Last Batch Start', 
dess.last_request_end_time as 'Last Batch End',
dess.login_name, 
dess.session_id as 'SPID',
der.open_transaction_count as 'Open Tran',
der.blocking_session_id as 'Blocker',
isnull(der.wait_type,'') as 'Current Wait',
der.wait_time as 'Wait Time',
isnull(der.last_wait_type,'') as 'Last Wait',
isnull(der.wait_resource,'') as 'Waiting ON',
ISNULL(db_name(der.database_id),'') as 'Database',
dess.cpu_time as 'CPU Time',
dess.logical_reads as 'Reads',
dess.writes as 'Writes',
dess.[status],
dess.[host_name],
dess.[program_name],
der.command as 'Command',
eqp.query_plan
from sys.dm_exec_sessions dess 
join sys.dm_exec_connections decc on decc.session_id = dess.session_id
left outer join sys.dm_exec_requests der on dess.session_id = der.session_id
OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) eqp
where dess.session_id>50
order by last_request_start_time desc;

This query ( above ) is similar to the sysprocesses query and lists all user connections.

Also works on SQL 2005 but with the same conditions for viewing the query plans.

--
-- to get the last command run you need to pass the handle from dm_exec_connections to sys.dm_exec_sql_text
-- including the sql text can make for some big result sets
--
select 
dess.last_request_start_time as 'Last Batch Start', 
dess.last_request_end_time as 'Last Batch End',
est.[text],
eqp.query_plan,
dess.login_name, 
dess.session_id as 'SPID',
der.open_transaction_count as 'Open Tran',
der.blocking_session_id as 'Blocker',
isnull(der.wait_type,'') as 'Current Wait',
der.wait_time as 'Wait Time',
isnull(der.last_wait_type,'') as 'Last Wait',
isnull(der.wait_resource,'') as 'Waiting ON',
ISNULL(db_name(der.database_id),'') as 'Database',
dess.cpu_time as 'CPU Time',
dess.logical_reads as 'Reads',
dess.writes as 'Writes',
dess.[status],
dess.[host_name],
dess.[program_name],
der.command as 'Command'
from sys.dm_exec_sessions dess 
join sys.dm_exec_connections decc on decc.session_id = dess.session_id
join sys.dm_exec_requests der on dess.session_id = der.session_id
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) eqp
CROSS APPLY sys.dm_exec_sql_text(decc.most_recent_sql_handle) est
where dess.session_id>50
order by Blocker desc;

This query ( above ) lists out the last sql run along with the query plan, I use this mostly when there are blocking issues or I’m concerned something isn’t quite right, such as a serialised procedure plan.

Works with SQL 2005 as before.

--
-- connections by host
--
select  isnull(ses.[host_name],'TOTAL') as HostName,count(*) as Logins
from  master.sys.dm_exec_sessions ses with (nolock)
where ses.session_id > 50 
and ses.[host_name] not in('') -- you might want to add the actual server to eliminate system sessions
group by ses.[host_name]
with rollup;

A slightly different query ( above ) I use this within a dashboard and to monitor connections.

Typically you may have a number of n-tier servers, for instance a number of web servers , SSRS servers and application servers this query groups by host and uses the rollup to give a grand total.

I’ve been known to run this every minute as a scheduled job to put the output into a table, this allows me to then plot the use by host server; e.g. if you have four load balanced application servers did they maintain a similar number of connections all day?

The “ not in “ allows you to filter out say the cluster node; on the other hand you can quickly identify any workstations connecting to your server!

I’ve used this technique previously linked to an alert when I encountered issues with connection pooling not working correctly from a server; the number of connections kept increasing and essentially performed a denial of service attack on the server, by monitoring the count of connections I could alert when a threshold was reached.

If you saw my presentation on creating real time dashboards for production systems this was part of one of the dashboard reports.

--
-- open transactions
--
select count(*) as [Trans]
from sys.dm_tran_session_transactions tst
join sys.dm_tran_active_transactions tat on tat.transaction_id = tst.transaction_id
join sys.dm_tran_database_transactions tdt on tst.transaction_id = tdt.transaction_id
join sys.dm_exec_sessions dess on dess.session_id = tst.session_id
where tst.session_id>50;

The query above counts open transactions; too many open transactions can be an indication of an issue, I use this for monitoring against SSRS, these have users too but perhaps not in the same way as more conventional application databases.

Another proactive query, my experience has shown that for my SSRS Servers a value of 10 or more shows I have slow running reports, over 30 usually indicates timeouts and other user unfriendly effects.