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