Post reply

Stored Proc not Working after migration - CONTAINSTABLE

  • February 3, 2017 at 2:31 pm

    #340117

    I have a stored procedure that works when it is a small amount of data returned but times out when a large amount of data is returned.  It times our both in SSMS and via the application.

    We migrated from 2008 r2 to 2014 but was left it in compatibility mode. The code isn't the greatest but it should still be returning records as it does in current prod.  the number of records is over 135k

    The function CONTAINSTABLE  and the use of Full-Text  are the concerns I have.  in thins new environment.  I have tweeked configurations and such to deal with these 2 items and nothin has worked. 

    The new environment is went from 16 cpu to 40 cpus  for starters and ram is now at 128  from 64  so this should be doing better not worse.

    At ideas would be  appreciated.

    --Susan

  • February 3, 2017 at 3:45 pm

    #1926713

    post migration, it's mandatory to rebuild all your indexes ; the statistics are different than the new sql engine expects, andyou'll see poor performance until you rebuild your indexes and get your statistics updated to the new 2014 expectation.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 4, 2017 at 4:24 pm

    #1926742

    IIRC, you'll need to rebuld the full text indexes in their entirety.

    EDIT... Crud... didn't see Lowell's post, which says the same thing.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply