SSRS Report Performance & Optimization

You should determine what is taking a long time.  You can query the reportserver database to find out how much time the report is spending running the query and how much time is spent rendering.  To optimize the query time, you need to analyze the query and optimize it as you would any other SQL Query.  Many times the main reason a report will render slowly is because you are doing lots of aggregations inside of the report instead of pushing the aggregations down to the database in your query.

This query will show you the details for a specific report:

select
 CAST(convert(varchar(40),timestart,101) as datetime) dt,
 avg(DATEDIFF(second,timestart,timeend)) run_time_seconds,
 AVG(
  case TimeDataRetrieval
   when -1 then null
   else TimeDataRetrieval
  end
 ) / 1000 data_retrieval_seconds,
 AVG(
  case TimeProcessing
   when -1 then null
   else TimeProcessing
  end
 ) / 1000 processing_seconds,
 AVG(
  case TimeRendering
   when -1 then null
   else TimeRendering
  end
 ) / 1000 rendering_seconds,
 COUNT(*) num_runs
from ExecutionLog2 
where timestart > getdate()-30
and Reportpath=@path
group by CAST(convert(varchar(40),timestart,101) as datetime)

2005 Find Table without Clustered Index – Find Table with no Primary Key

USE AdventureWorks ----Replace AdventureWorks with your DBName
GO
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE]
GO

Get a List of Fixed Hard Drive and Free Space on Server

If I do not have access to OS I use the following Stored Procedure to figure out the number of fixed drives (hard drive) a system has along with free space on each of those drives. 

EXEC master..xp_fixeddrives

Get Directory Structure using Extended Stored Procedure xp_dirtree

Well, there is one undocumented stored procedure exists which can do the same. However, please be vary to use any undocumented procedures.

xp_dirtree 'C:\Windows'


Execution of the above stored procedure will give following result. If you prefer you can insert the data in the temptable and use the same for further use.

Here is the quick script which will insert the data into the temptable and retrieve from the same.

CREATE TABLE #TempTable (Subdirectory VARCHAR(512), Depth INT);
INSERT INTO #TempTable (Subdirectory, Depth)
EXEC xp_dirtree 'C:\Windows'
SELECT Subdirectory, Depth
FROM #TempTable;
DROP TABLE #TempTable;

Handling special characters with FOR XML PATH('')

Since SQL Server 2005, we’ve been able to use FOR XML PATH('') to do string concatenation. I’ve blogged about it before several times. But I don’t think I’ve blogged about the fact that it all goes a bit wrong if you have special characters in the strings you’re concatenating.
Generally, I don’t even worry about this. I should, but I don’t, particularly when the solution is so easy.
Suppose I want to concatenate the list of user databases on my system, in alphabetical order. Easy: Note the lack of

select
  stuff(
     (select ', ' + name -- Note the lack of column name
     from sys.databases
     where database_id > 4
     order by name
     for xml path('')
     )
   , 1, 2, '') as namelist;

This is easy, and it just works. The STUFF command lets me strip the leading comma and space, and my list of database names is done.
But if I wanted to list the names of database with triangular brackets around them… then that’s bit more complicated.

select
  stuff(
     (select ', <' + name + '>'
     from sys.databases
     where database_id > 4
     order by name
     for xml path('')
     )
   , 1, 2, '') as namelist;

It still runs, but I my results don’t show the triangular brackets, it shows <databasename>, <databasename2>. It’s not what I want to see. I effectively need to HTMLDecode the results – but T-SQL doesn’t have a function like that.
However, if I actually make my FOR XML call return actual well-formed XML, then I can extract the contents out, and it returns the data to me in its original (correct) form.

select
  stuff(
     (select ', <' + name + '>'
     from sys.databases
     where database_id > 4
     order by name
     for xml path(''), root('MyString'), type      ).value('/MyString[1]','varchar(max)')
   , 1, 2, '') as namelist;

 OR

select

 stuff(

    (select ', <' + name + '>'

    from sys.databases

    where database_id > 4

    order by name

    for xml path(''), type

    ).value('(./text())[1]','varchar(max)')

  , 1, 2, '') as namelist;

To be valid XML, it needs a ROOT element. I also need to tell it to be XML, using the TYPE directive.
But this lets me hook into the value of /MyString[1], and return that as varchar(max).
And it works, my data comes back as , , etc.
It’s a habit I need to use more often.

Edit: I can also skip the ROOT element (but keep TYPE) and use .value('(./text())[1]','varchar(max)') - but for some reason it's always felt strange to me to be returning something which I declare to be XML (using TYPE) without it being well-formed.

Source : http://sqlblog.com/blogs/rob_farley/archive/2010/04/15/handling-special-characters-with-for-xml-path.aspx