Find out who is viewing the reports on SQL server reporting services 2005

This SQL script should give you the list of users using the reports on the reporting server 2005 platform.It can be used for auditing platform as well.

SELECT s.ReportName,s.UserName,COUNT(*)
FROM
(
SELECT cat.Name ReportName,
ex.UserName,
TimeStart
FROM ReportServer.dbo.ExecutionLog AS ex
INNER JOIN ReportServer.dbo.Catalog AS cat ON ex.ReportID = cat.ItemID
WHERE ex.TimeStart >= GETDATE() - 1
--ORDER BY ReportName
) s
GROUP BY s.ReportName,s.username

Playing With SQL Server Cache(DMV)

Yesterday Afternoon me and my friend were doing some serious Sql manipulation on some heavy datasets that needs to go inside the Cube.We have written some dynamic SQL statement that is going to insert some 40000 rows into a Control table for a Dimension.
Weird Enough my friend accidently closed the Query window and for a second we thought we lost it.

But Thanks to Dynamic Management views(DMV) from Sql server 2005.we were able to get the query back from the Cache..The query we used is as follows


select qs.usecounts, cacheobjtype, objtype, qt.text
from sys.dm_exec_cached_plans qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as qt
--where qt.text like '%%'
order by qt.text

and thats it we were able to get back the query...quite cool way of doing things..isnt it?

Missing Indexes on a table for Sql server 2005

The following consolidated Query from all the DMV's should give the missing indexes in the table


SELECT
statement AS [database.scheme.table],
column_id , column_name, column_usage,
migs.user_seeks, migs.user_scans,
migs.last_user_seek, migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
ON mig.index_group_handle=migs.group_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id
GO


When you create indexes , Please note the following
a. The DML operations will become slow if there are many indexes
b. More diskspace is required if you create more indexes..Also depends on the column datatype.
c. Create 100% fill factor on the indexes if the table is used only for read purposes; For all other DML operations balance the fillfactor between 0 and 100 percent.