New answers tagged ssrs
Do you have documentation or change control regarding indexes? Perhaps someone that has admin access to the database deleted or disabled an index. Get the sp_askbrent stored procedure from brentozar.com installed and run it.
One good source for the data is plan cache, and you can find top cpu or logical reads by querying dm_exec_query_stats DMV, for example with something like this: select top 100 SUBSTRING(t.text, (s.statement_start_offset/2)+1, ((CASE s.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE s.statement_end_offset END - s.statement_start_offset)/2) + 1) as ...
You will want to use dynamic SQL to change the DB you are executing the query in: DECLARE @dbname AS VARCHAR(100) DECLARE @query AS VARCHAR(MAX) SELECT @dbname = name FROM sys.databases d WHERE /*enter criteria here to get DB*/ SET @query = ' use ' + @dbname + ' /*enter your query here*/ ' PRINT @query EXEC(@query)
You could use a RS.exe script. This would allow you to create a script, a batch file and a set of .rdl files. Then you just call rs.exe with the script you created to upload the files to the report server. You can find an example here
Top 50 recent answers are included