Before telling my problem, let you know that I am migrating my database to Microsoft SQL Server 2014 (for the moment CTP2).
In my database, I have a table with 50K records. My Software used to execute following query to get some contacts after first 20000 contacts. This query will get records from a single table which has clustered index for primary key, table name is Contact_Header. Following is the query.
select Contact_Unique_Ref=RTrim(Contact_Unique_Ref), Contact_Type=RTrim(Contact_Type), User_Unique_Ref=RTrim(User_Unique_Ref), Source_Unique_Ref=RTrim(Source_Unique_Ref), Contact_Category=RTrim(Contact_Category), Rule_Unique_Ref=RTrim(Rule_Unique_Ref) FROM Contact_Header where Contact_Unique_Ref NOT IN (select top 20000 Contact_Unique_Ref=RTrim(Contact_Unique_Ref) from Contact_Header order by Contact_Unique_Ref) order by Contact_Unique_Ref desc
This query takes no time in execution at SQL Server 2008 R2 Enterprise Edition. But when I tried it at SQL Server 2014 Enterprise Evaluation 64 bit installed at Windows 2012 R2, it takes 47 minutes approximately.
Can somebody help me that why this query takes that much time while executing at SQL Server 2014?
There are a standard set of steps to follow when migrating a database from one version to another, including:
- DBCC CHECKDB
- DBCC UPDATEUSAGE
- Update statistics on all tables
Have you completed these steps? Here's a guide:
Thank you for the reply.
Actually this is not the migration such that I detach the database from old version and attach to new version. From migration word, I mean that I have made the database at SQL Server 2014 with same script as I used to do at SQL Server 2008 R2 Enterprise Edition. Database creation is part of my product installer and now I am installing at the machine with SQL Server 2014.