Home » Archives for December 2012

December 2012

sql server DBA 999

Temporary Variables vs Temporary Tables SQL Server 2008/2005

Myth: A table variable is a memory-only structure.
This is not True. A Temp table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables.

Note: If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

Normally whether to go with Temp Variables or Temp Tables depends on:
•The number of rows that are inserted to the table.
•The number of recompilations the query is saved from.
•The type of queries and their dependency on indexes and statistics … Read the rest

sql server DBA 999

SSRS Queries for a DBA or Developers to monitor Reporting Server

The Script to Check Reports and Subcriptions with Sorted results per last Run Date

SELECT c.Name AS ReportName
, rs.ScheduleID AS JOB_NAME
, s.[Description]
, s.LastStatus
, s.LastRunTime
FROM ReportServer..[Catalog] c
JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID
jOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID
AND rs.SubscriptionID = s.SubscriptionID
order by 5

To Check Latest Running reports with Full details of who is using reports sevices at what time and which report,…. etc..

SELECT [InstanceName]
,b.name
,[UserName]
,[RequestType]
,[Format]
,[Parameters]
,convert(varchar(10),[TimeStart],103)+' '+convert(varchar(8),[TimeStart],108) as TimeStart
,convert(varchar(10),[TimeEnd],103) +' '+convert(varchar(8),[TimeEnd],108) as TimeEnd
,[TimeDataRetrieval]
,[TimeProcessing]
,[TimeRendering]
,[Source]
,[Status]
,[ByteCount]
,[RowCount]
FROM [ReportServer].[dbo].[ExecutionLog] a
inner join [ReportServer].[dbo].[Catalog] b
on a.reportid=b.itemid
Read the rest