A couple of days ago some of our users were complaining about the speed at witch the reports were running.

I suspected that they were running some huge reports to get some year end data, but of course they denide that. I had seen a couple of tables that reporting services uses, and started poking about in those.

After a bit i managed to come up with the following sql that gave me a list of what reports had been run, who ran them, what parameters they used and how long they took to run:

SELECT TOP (100) PERCENT dbo.Catalog.Name, dbo.ExecutionLog.UserName, DATEDIFF(mi, dbo.ExecutionLog.TimeStart, dbo.ExecutionLog.TimeEnd)
AS Proc_time, dbo.ExecutionLog.Parameters, dbo.ExecutionLog.TimeStart
FROM dbo.ExecutionLog RIGHT OUTER JOIN
dbo.Catalog ON dbo.ExecutionLog.ReportID = dbo.Catalog.ItemID
WHERE (dbo.ExecutionLog.TimeStart > CONVERT(DATETIME, ’2006-12-01 00:00:01′, 102))
ORDER BY Proc_time

Please note that this works in SQL 2005 reporting services, i’m not sure if it will in previous versions. To run for different dates, just change TimeStart’s parameter

Tried to setup SQL 2000 today so it would email me when certain events occurred, I have done it before but it took me a bit to work out why it wouldn’t work this time. Couple of things I had to do:

• I needed to install Outlook mail client on the pc. (I suppose any mail client will do as long as it allows you to setup a mail profile)
• The account that I was running the sql server agent service as was a domain account not the default system one. To get it to see a mail profile I had to login to the server as the user I was running the service as, and setup outlook so it had a mail profile.
• I was using a mailbox we setup to receive log files from, so it was created under a different user. I had to give the user the SQL agent service was running as rights into this mailbox.
• After a restart of the Agent service I could then send mail.

I also come across something else today witch look quite cool. Its called the SQL Server Health and History tool, and can be downloaded here
It will log various things about SQL installs to a database, and there is a report pack you can download so you can look at the info.
I’ve not managed to get the performance counters working yet, and its only logging from one server but when I get it all working ill write it up on here.

I found this function a couple of weeks ago and found it very usful. I had to add a filed to a report, that contained a list of other fileds from the report, each seperated by a comma. At first i thought i’d have to write some custom code, but then I found the COALESCE function.
You can use this in the following way, to create one output from x number of records, seperated by a comma.

DECLARE @aa varchar(100)

SELECT @aa = COALESCE (@aa + ‘, ‘, ”) + CAST(name AS varchar(5))
FROM tbl_Customer

WHERE (Customer_bal > 10)
SELECT @aa AS Names

I then added this sql to a sub report and passed the id of the line to it, so I could then have this showing for each record. The time it took to render the report increased, but the user got the information they needed.
I then had to write some custom code into the report so it would sort the results in a predictable way. You can read more about the function here