I use SQL Server 2014 and have a database with compatibility level 110 (SQL Server 2012). also I have a stored procedure on it that execute about 10 second. When I change compatibility level of my database to 120 (SQL Server 2014), execution time of my stored procedure became about 30 minute.
I use following steps but execution time of my stored procedure not changed.
Step 1: Use
Step 2: Execute
DBCC UPDATEUSAGEon my database.
step 3: Rebuild all indexes on my database.
step 4: Drop all index and all foreign key on my database and create all again.
After all of above step my stored procedure execution time not changed.
But when compatibility of my database returned to 110, my sp run about 10 second.
I check above step multiple and get a single result.
How must I do. and how can I change compatibility level of my database to 120 (SQL Server 2014).
Thanks in advance.