Facing Performance problem after upgrading from SQL 2008 R2 to SQL 2012
- updated statistics with full scan against all tables at db.
- rebuild all indexes (online and offline both) at db.
- DBCC CHECKDB WITH DATA_PURITY;
- DBCC UPDATEUSAGE(db_name);
- Try to give a different maxdop options to execute that SP but didn't observe an improvement in SP execution timing.
- Passed that SP to DTA but didn't receive any suggestions.
Even we didn't change database compatibility level in new environment but still it's taking time to complete. I'm attaching here SP plan's at PHY and VM machines both, please open it in SSMS since in plan explorer can cut some portion of plan and advise on it. One more observation here I saw in these 2 execution plans is , there are 8 queries executing in bad plan wherever in good plan its executing only 2 queries for a same SP. In bad plan CTE is executing multiple TVF's which is causing more cost, not sure about the behavior of CTE's in SQL 2012.
link textAny help will be greatly appreciated.
Thanks.
Run the following query to determine the setting of this option for all T-SQL procedures and non-inline functions:
SELECT name = S.name + N'.' + O.name, O.type_desc, IsAnsiNullsOn = OBJECTPROPERTYEX(O.[object_id], 'ExecIsAnsiNullsOn'), IsQuotedIdentOn = OBJECTPROPERTYEX(O.[object_id], 'ExecIsQuotedIdentOn') FROM sys.objects AS O JOIN sys.schemas AS S ON S.[schema_id] = O.[schema_id] WHERE O.type_desc IN ( N'SQL_STORED_PROCEDURE', N'SQL_TABLE_VALUED_FUNCTION', N'SQL_SCALAR_FUNCTION' ) AND O.is_ms_shipped = CONVERT(bit, 'false');
Systems should not be designed to depend on the behaviour with ANSI_NULLS set OFF, as this option will be removed in a future version of the product.
- EXEC [dbo].ReportingServices_PBM_TrendFor_ContactReason] ? taking 45 mins to complete at VM, focus on this SP execution will resolve our problem. Further inside that SP, an attached query name as Query calling underSP [ReportingServices_PBM_TrendFor_ContactReason].sql causing delay, ultimately we need to fine tune it.
- EXEC [dbo].[ReportingServices_PBM_TrendFor_TotalSummation_ContactReason] ? Taking 1:12 mins to complete, we can ignore it for now.
I'm attaching all code here.
Other observation I had, If you see in new environment execution plan (SQL 2012) its trying to execute CTE with TVF's (table valued function) and there only its taking most of the time, not sure if CTE with TVF's has some known problem in SQL 2012. Please let me know your thought on this, if any. Thanks again !