Help with Performance on select count(*) SQL Server 2014

Question

Help with Performance on select count(*) SQL Server 2014

zouzou

SSCrazy

Points: 2292
More actions
May 23, 2017 at 9:25 am

#321621

Have 2 same databases one on test and one on dev in different servers. Whilst trying to run the same query is select * from a view i get 17 minutes on test to run and 6 seconds dev. I have restored the dev db to test and that db was running faster than my test one taking 35 seconds.
I have ran the reindexing update stats and looked through all the settings od the database with no luck.
See attached the anonymised plans from the running of the query
can you please help what can it be? I can see that the plan has scans that normally takes more time on test.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

zouzou - Tuesday, May 23, 2017 9:25 AM
Have 2 same databases one on test and one on dev in different servers. Whilst trying to run the same query is select * from a view i get 17 minutes on test to run and 6 seconds dev. I have restored the dev db to test and that db was running faster than my test one taking 35 seconds.
I have ran the reindexing update stats and looked through all the settings od the database with no luck.
See attached the anonymised plans from the running of the query
can you please help what can it be? I can see that the plan has scans that normally takes more time on test.

Running both databases on similar hardware?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

zouzou SSCrazy Points: 2292 More actions · Answer 2

Similar servers and memory is more on Test which doesnt make sense either

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

zouzou - Tuesday, May 23, 2017 9:30 AM
Similar servers and memory is more on Test which doesnt make sense either

Are both SQL Instances configured 100% the same as well?
Is the loads similar on both servers?
Are both virtual/physcial, with same disk architecture?

Your exec plans were not attached in original post.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

RandomEvent SSCrazy Points: 2963 More actions · Answer 4

Have you checked the wait stats on both boxes ? might give you an idea of whether there is something connected to the hardware or the processing methods used.

zouzou SSCrazy Points: 2292 More actions · Answer 5

they are virtual and i think its not environment related since i have restore the db from DEV to test and it was running as fast as in DEV.

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 6

zouzou - Tuesday, May 23, 2017 9:52 AM
they are virtual and i think its not environment related since i have restore the db from DEV to test and it was running as fast as in DEV.

Couldn't view your plans, root elements missing and XML can't be parsed.

This part confuses me a bit.

Have 2 same databases one on test and one on dev in different servers. Whilst trying to run the same query is select * from a view i get 17 minutes on test to run and 6 seconds dev. I have restored the dev db to test and that db was running faster than my test one taking 35 seconds.

Did you backup db X on DEV Server and restore it as DB y on TEST Server, and now the same SELECT * FROM view is slower on TEST server than what it was on DEV Server?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

zouzou SSCrazy Points: 2292 More actions · Answer 7

I have backed up DEV db and restored it on TEST server and then i have run on that DEV db on Test server the same query i was running on the test db that is hosted on the same server and on DEV db the query ran very fast. sorry about the plans uploaded them again now.

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 8

zouzou - Tuesday, May 23, 2017 10:17 AM
I have backed up DEV db and restored it on TEST server and then i have run on that DEV db on Test server the same query i was running on the test db that is hosted on the same server and on DEV db the query ran very fast. sorry about the plans uploaded them again now.

Have you looked at the differences in execution plans by comparing these 2 show-plans in SSMS?

Top = DEV, Bottom = TEST

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

zouzou SSCrazy Points: 2292 More actions · Answer 9

I m not that good in reading execution plans but I did see the different between the scans and the seeks. Is there anything you think I should look out for?