question

JohnVenables-6931 avatar image
0 Votes"
JohnVenables-6931 asked CarrinWu-MSFT commented

SQL Server 2016 Developer Edition Performance Issues Running a Script

Hi All,

I have a bit of a strange one...I have a Windows Server 2016 Standard Edition with SQL Server 2016 Developer Edtion running on an old HP Server as a Virtual Machine.. This is our test environment, we have a special script that was created by one of our Developers that sanitises a specific DB, this script on the old server takes about 1hr 30mins to complete.

However, we built a new server in our new environment, its effectively a private cloud using brand new servers, faster CPU's, storage, memory, network.....Pretty much everything is newer and faster....hehe...

So we built a new Virtual Server with 64GB RAM and 6 CPU's to run purely this script as we need to sanitise a lot due to Developers needing copies of a DB to test fixes..etc..

However the new server is taking 3 times longer to run this script, which is baffling. I compared TEMPDB config, the model DB config and made sure all the parameters were identical to the old Heritage Server with regards to autogrowth..etc.

I know i am asking a bit of sweeping question, but I am at my wits end...So looking to hopefully start a conversation with people dramatically more in the know than me. I am happy to supply any information that I can to get help.

Anyone up for a challenge?

Regards

John

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JohnVenables-6931, we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Did you have Query Stored turned on in the past? Query Store is great for these kind of things, because you have the execution data before the change and after the change, and you find out where the regression is.

Some of the hardware characteristics can affect the plan choice. The most important is the number of CPUs and the available RAM. On the latter, I recall a ware story where someone told that they had good performance at max server memory = 352 GB, and then it completely collapsed when this was increased to 356 GB, because SQL Server now thought the memory was good for a different plan, but because of of a poor estimate, a Sort operator spilled to disk, badly.

In any case, Query Store is your friend. Turn it on, and select the data for the period when the sanitising is running and locate the slow query/ies.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @JohnVenables-6931,

Welcome to Microsoft Q&A!

Although you have a better environment now, but the slow queries will be impact by some hardware configuration. This blog introduce two tools will help you to identify slow running queries. The first tool is a built-in tool for SQL Server Management Studio “Activity Monitor”. Activity monitor tells you what the current and recent activities are in your SQL Server Instance.
The next tool is the “Query Store”. This is helpful and could save your life in situation where you were called in the middle of the night to check why SQL Server was slow 1 hour earlier.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.