Stored Proc not Working after migration - CONTAINSTABLE

Question

Stored Proc not Working after migration - CONTAINSTABLE

swoozie

SSCrazy

Points: 2770
More actions
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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

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)