Share via

Stored procedure taking a long time in standard SQLServer 2014

Avyayah 1,271 Reputation points
2022-08-18T18:39:26.66+00:00

Stored procedure taking a long time in Standard sqlserver 2014 :Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB5014164) - 12.0.6439.10 (X64)
Apr 20 2022 03:13:42
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

vs enterprise edition Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
Aug 15 2017 10:23:29
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Do you know if enterprise vs standard will cause differences in the performance of stored procedure

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,721 Reputation points
    2022-08-18T19:28:48.817+00:00

    There are many, many, many reasons for the difference in performance. Without a lot more information, there is no way to guess what is affecting your query.

    Do the actual query plans match?
    Assuming your proc takes parameters, did you try WITH RECOMPLILE on the proc?
    Did you install on the same server?
    If not, are the VM servers configured the same on the same hardware/disk drives?
    How did you get the database into SQL 2014?
    Assuming you did a restore, did you rebuild the indexes and stats on SQL 2014 after the restore?
    Enterprise edition uses all CPU/RAM, Standard edition is limited. Do you have >4 CPUs? Did you configure more than 1 core per socket in your VM?

  2. Erland Sommarskog 107.2K Reputation points
    2022-08-18T21:52:55.653+00:00

    As Tom said, there are many reasons why this can happen.

    Indeed, one possible reason for the difference is that the query plan in SQL 2012 is making use of a feature that is only available in Enterprise Edition.

    Another possible reason with this transition you are making is that SQL 2014 introduced a new Cardinality Estimator, which has a great impact on the optimizer's work. In many cases, it leads to better plans, but there are also cases where it backfires.

    What is the compatibility level for the databases? The new CE is only available in compat level 120 and higher. If the SQL 2014 database is in compat level 120, enable trace flag 4199, to enable optimizer fixes that were made after the release of SQL 2014; there were some bugs with the new CE.

    I like to point out that without seeing the code or the query plans, we can only offer speculations.

  3. YufeiShao-msft 7,091 Reputation points
    2022-08-19T09:01:23.53+00:00

    Hi @Avyayah ,

    You can try yo create an extended events session and collect data or use profiler to find long running stored procedure.
    There are some function differences between the Standard and enterprise edition, some function are not available in the standard.

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments