SSRS Report taking long time to load

If stored procedure is working fine in SQL Server but taking long time in SSRS, use the command "With recompile" in stored procedure like:

-----------------------------------------------
Create PROCEDURE [dbo].[spGBRGetAllCallRecords]
@pMSISDN varchar(50),
@pStartDate datetime,
@pEndDate datetime

with recompile
AS
SET FMTONLY OFF

---------------------------------------------

Get Missing Indexes from database on SQL Server

Get Missing indexes from a Database in SQL Server:


Create Proc p_getMissingIndexes
(@Databasename varchar(50)='GBRView')

as

--Declare @cmdstr as varchar(100)
--SELECT @cmdstr = 'use ' + @Databasename
--EXEC(@cmdstr)


SELECT --DB.name [Database],
D.statement
+ ' (' + ISNULL (D.equality_columns,'')
+ CASE WHEN D.equality_columns IS NOT NULL AND D.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (D.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + D.included_columns + ')', '') AS [Index Detail],

column_name, unique_compiles, avg_user_impact, last_user_seek, user_seeks, column_usage
FROM sys.dm_db_missing_index_groups G
JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle
JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle
Inner join master.dbo.sysdatabases(nolock) DB on D.Database_id = DB.[DBID]
CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC
where lower(DB.name) = lower(@Databasename)
ORDER BY D.index_handle, [statement];

Get Used indexes from SQL Server

use this Stored Procedure to get Used Indexes from a Database on SQL Server:


Create proc P_GetUsedIndexes (@Databasename as varchar(50))

As

DECLARE @cmdstr varchar(100)

SELECT DATABASE_ID,
DB_NAME(DATABASE_ID) AS DATABASENAME,
OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,
INDEX_NAME = (SELECT NAME
FROM SYS.INDEXES A
WHERE A.OBJECT_ID = B.OBJECT_ID
AND A.INDEX_ID = B.INDEX_ID),
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS B
INNER JOIN SYS.OBJECTS C
ON B.OBJECT_ID = C.OBJECT_ID
WHERE --DATABASE_ID = DB_ID(DB_NAME()) AND
DATABASE_ID = (select [dbid] from master.dbo.sysdatabases(nolock) where name=lower(@DatabaseName)) AND
C.TYPE <> 'S'