Facing Performance problem after upgrading from SQL 2008 R2 to SQL 2012
2015-01-02 17:25:35
Hi Guys – Recently we migrated our environment from SQL 2008 R2 PHY machine to SQL Server 2012 VM environment and after it we started facing performance problem to execute a particular SP. That SP is executing in 31 seconds at PHY machine but same SP is taking 45 mins time to complete at VM environment. At new environment we did following things :
- 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.
2015-01-03 15:17:27
It appears the procedures or functions were created on the 2012 machine with ANSI_NULLS set differently from the 2008 R2 instance. Note that it is the setting of this option that was in effect when the procedure was created that is important, not the setting when the query is executed.
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.
2015-01-05 07:52:23
Did you run the query I posted or check the settings some other way? The settings are different in the execution plans you posted, and that difference is the main way I can think of for such extensive simplification (removal of whole queries/CTEs) to occur.
2015-01-06 14:18:34
Hi Paul – I used your query only to check the ANSI settings but didn't found any difference between PHY and VM's. Even I specifically put "SET ANSI_NULLS ON;" in that SP and executed it again at VM but still SP is taking same amount of time to complete. Another observation I had in bad XML plan that lots of implicit conversion (total 103) are happening which is not the case with good xml plan (only 3). Any further advise.
2015-01-06 16:35:48
Perhaps it would help if you added the complete definition of the stored procedure to the question. There will be a logical reason for the difference, but it's hard to diagnose without the details. Remember we do not have access to your system so you need to be explicit.
2015-01-06 17:54:06
Also, please confirm what the settings of ANSI_NULLS is for the procedure and every function referenced in the procedure, as returned by the code above.
2015-01-06 17:27:18
This problematic SP (name as ReportingServices_PBM_TrendFor_ContactReasonMonthWise) is calling 2 other SP's inside it :
- 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.
2015-01-06 18:56:08
Where are the definitions of dbo.GetRollingFiscalMonthEndDate and dbo.fn_ConvertStringToList? I still think the fundamental cause of this issue is mishandling of NULLs by the way.
2015-01-06 20:29:52
mailed you those queries at SQLkiwi@gmail.com since its not allowing me answer a question multiple times.
2015-01-07 19:07:39
Hi Paul – Did you had a chance to look in to this further.
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 !