iLearn

When you undertake some performance improvement task, you think it is frustrating however at the same time you tend to learn more. So the other day we had to brain storm on one of the similar issues where the database stored procedure was returning data quickly as compared to the SSRS report (using same stored procedure) which seemed to be taking ages to run and display data.  The initial investigation and their results were:- Finding out from excutionlog table where the issue is. Turned out that 'TimeDataRetrieval' was considerably high for previous runs of the report. This proved that the real cause was indeed the slow performance of the stored procedure. Checked to see whether it could be related to temp database. On connecting with the database admin this was ruled out. Checked to see the cpu utilization at the report server. Nothing significant was found. After doing all these steps,  I thought that this issue might be related to the parameter sniffing. Paramet

Hi Everyone In the daily team meeting today, we had a requirement to find out the stored procedure used/referenced by SSRS reports. My Team Lead suggest why cant we write a sql to search the xml of the reports. Upon googling I came across this beautiful article by :- https://www.mssqltips.com/sqlservertip/3693/find-tables-columns-or-stored-procedures-used-in-a-sql-server-reporting-services-report/ Suggest everyone to go through this to understand the code. Here is the snipped which would give list of sp's that are used inside a report :- Declare @ReportName VARCHAR ( 100 ) Set @ReportName = 'QuarterlySalesPerformance' ; WITH XMLNAMESPACES ( DEFAULT    ' http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition ' , 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS ReportDefinition ) SELECT   distinct xmlcolumn . value ( '(Query/CommandText)[1]' , 'VARCHAR(2500)'

Hi Guys I know sql 2005 is getting outdated and there would be no support from Microsoft from this month. But lets not  go on that road, because client is ready to take the risk. So as a result we are still working on sql 2005. However by working with so called soon to be a outdated version gives you a chance to come  across  various bugs with the technology. I am listing few of them which I encountered last week or so:- 1.      I have a stored proc like this :- create proc reporting.sp_test as select * from test exec reporting.sp_test As you can see this is a stored proc belonging to a schema 'reporting' and referring to a table with default schema 'dbo'. On executing it I get a error message which says that - test does not exist. However when I tested this in sql 2008, it works fine. It seems the behaviour changed in 2008. It looks like in 2005 the default schema is the schema of the caller (in this case the SP) and in 2008 it's defau