December 2008

You are currently browsing the monthly archive for December 2008.

Since SP2 for SQL Server 2005 we have some Standard Reports available when right-clicking either server or database in the Object Explorer in Management Studio.  One of my favorites is the Index Usage Statistics report.

If you have the task to optimize performance of your server or database, Microsoft has provided an interesting additional download called the Performance Dashboard.  When you’ve installed the package, there will be some .RDL files and an interesting .CHM file in our SQL Server folder, for instance C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\.

Among this bunch of .RDLs there is one main report called performance_dashboard_main.rdl.  All other reports are used in drillthrough scenarios from the main Performance Dashboard report.  The main report can be opened by right-clicking your server in SSMS, then choose Reports > Custom Reports… and browse to the RDL.

For a more detailed explanation on the installation procedure, have a look at this Database Journal article.  And for an explanation of each report plus some screenshots, part two of that article is certainly useful as well.

Additional info is also available in the PerfDash.chm file located in the same folder as the RDLs.

A bug

Earlier this week I came across a bug in one of the stored procedures that the main Performance Dashboard report is using.  When I tried executing the report I got the following error:

Msg 535, Level 16, State 0, Line 1
Difference of two datetime columns caused overflow at runtime.

This issue manifests itself when there are open connections which have been open for a period longer than approx. 24.85 days.

You can simulate the same error by executing the following select:

select datediff(ms, '2007-12-27 08:49:50.853', GetDate())

The bug is known by Microsoft, see this Microsoft Connect page, but will not be fixed as these reports are not supported.  In the feedback you can get enough info on how to fix the issue.  I also got the above query from there although I added an m to select milliseconds instead of seconds (which would not generate the error). :-)

I’ve solved it by modifying the usp_Main_GetSessionInfo stored procedure like this:

CREATE PROCEDURE MS_PerfDashboard.usp_Main_GetSessionInfoas
select count(*) as num_sessions,
sum(convert(bigint, s.total_elapsed_time)) as total_elapsed_time,
sum(convert(bigint, s.cpu_time)) as cpu_time,
sum(convert(bigint, s.total_elapsed_time))
    - sum(convert(bigint, s.cpu_time)) as wait_time,
 following line gives an error when login_time > 24 days
 the line: sum(convert(bigint, datediff(ms, login_time, getdate())))
    - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
sum(convert(bigint, datediff(s, login_time, getdate()))) * 1000
    - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
case when sum(s.logical_reads) > 0 then (sum(s.logical_reads)
    - isnull(sum(s.reads), 0)) / convert(float, sum(s.logical_reads))
else NULL
end as cache_hit_ratio
from sys.dm_exec_sessions s
where s.is_user_process = 0x1


Tags: , , ,

Microsoft has released an early Christmas present in the form of Service Pack 3 for SQL Server 2005.

The SP can be downloaded from the Download Center.  Same for the updated Books Online.

As I’m a fan of version numbers, SP3 upgrades your SQL Server to version 9.00.4035.

Besides all the fixes from the cumulative updates, the service pack also contains some enhancements.  The one that caught my interest is the following:

In SharePoint integrated mode, reports typically run slower than the same reports run in native mode. The main cause of this latency can be attributed to SharePoint object model calls that are made. In SQL Server 2005 SP3, the number of SharePoint object model calls that SQL Server 2005 Reporting Services (SSRS) makes are optimized. This optimization reduces some of the latency when you compare report performance with native mode.

Now if you’ll excuse me, I’ve got some downloads to do :-)


Tags: , ,

© 2008-2019 BI: Beer Intelligence? All Rights Reserved