Sql server 2017 performance

I have a query which works well in sql server 2014 filetables , after sql server 2017 upgrade it performs worse.

Query Prototype:

select cached_file_size from filetable where streamid='88656899'

Union

Select cached_file_size from filetablehistory where streamid='88656899'

Below Observations working fine:

select cached_file_size from filetable where streamid='88656899'

Union all

Select cached_file_size from filetablehistory where streamid='88656899'

select cached_file_size from filetable where streamid='88656899'

Select cached_file_size from filetablehistory where streamid='88656899'

4. There are no locks held on those tables.

Please help!!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ commented · Dec 06, 2018 at 11:47 PM

Is this on the same server that was upgraded in place or was the database restored to a different server that is sql 2017?

Is the database compatibility set for 2014 or 2017?

Did you run dbcc updateusage on the database after the upgrade?

Can you post the query plan for one of these queries?

0 ·

Mahesh120718 KenJ commented · Dec 07, 2018 at 02:38 AM

hello kenj

The database is restored from 2014 to 2017.

This is working fine some times and sometimes its taking time. Tried changing database properties of legacy cardinality to on also but was vain.

Once i run dbcc update statistics it works fine without issue and problem repeats again after a couple of days

Also found some procedures are running too slow but underlying query is executing fast

0 ·

KenJ Mahesh120718 commented · Dec 07, 2018 at 03:15 AM

Any chance the old server had an index or statistics job that kept the stats up to date but the new server doesn’t ? It’s common practice to create jobs to automate the maintenance of indexes and statistics.

0 ·

Show more comments

question

0 Answers

Write an Answer

question details

question