SQL on Hyperv

Question

SQL on Hyperv

Viewing 15 posts - 16 through 30 (of 43 total)

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

Jo Pattyn SSC-Dedicated Points: 32341 More actions · Answer 1

Got it open

Full Recovery Model w/o Log Backups

The ... log file has not been backed up in the last week: Is there a transaction backup in place or change the recovery model to simple

User Databases on C Drive: generally C-drive is harder to expand, slower and it doesn't help restoretimes. Moving them off C can help you installing a fresh sql-server and attach the DB-volume to it

Page Verification Not Optimal: change it to checksum (corruption detection)

Max Memory Set Too High: the default consumes all server memory starving the OS. Set appropiately so that OS/other apps also have some ram to use

Shrink Database Job: generally shrinking is not done due fragementation. Only in emergency / one off operations. As the SSISDB is involved in this one, check the SSISDB-configuration (log 365 days -> smaller )

Foreign Keys Not Trusted: you're losing some performance . Execute the command in the url/documentation (WITH CHECK CHECK CONSTRAINT) to make them trusted again.

Objects created with dangerous SET Options: consult the application developer/support.

File growth set to 1MB: too many filegrowths cause the number of VLF's to grow slowing your restores. Set it at a relevant autogrowth

MSDB Backup History Not Purged: msdb might grow continuously

Backup Compression Default Off: set it to on

SQL Agent Job Alerts: no email set

cost threshold for parallelism 5: generally start at 50 and tune it further. With 5 many plans have a parellel overhead

Query Store Disabled: recommended to activate it. Especially when you get the complaints "app is slow". Low impact monitoring

Jobs Owned By Users: you have a maintenance plan that won't run if the user is no longer active/known

Agent is Currently Offline: Currently you're offloading regular maintance to another system

Michael L John One Orange Chip Points: 27053 More actions · Answer 2

The results of sp_blitz make me wonder what "best practices" you followed.

Also, you do realize that you have published data in a public forum that could be used to penetrate your systems? Knowing an admin level user name gives a bad actor a big head start. I now know three sysadmin level usernames.

Like Jo said, follow all the links provided by sp_blitz, they will give you more information on each point.

You should configure multiple SCSI controllers, and divide them as follows, one for the OS (C: Drive), one for SQL data files, one for SQL log files, and one for tempdb. Move the database files to the proper locations. There are a lot of instructions on doing that.
The fact that there log files larger than the data files, as well as no log backups, tell me that your backup and restore process is seriously flawed. I'm also guessing that you have no other maintenance in place, like re-indexing, updating stats, and consistency checking. Do yourself a favor and download Ola Hallengren's Maintenance solution. It's not perfect, but it's far better than what you probably have in place. https://ola.hallengren.com/
Your file growth settings need to be looked at. File growths are very costly when they occur. Adjust the growth settings so that they rarely have to occur. Set the file sizes large enough so that there does not need to be any growth. Monitor free space, and when it passes a threshold, you can add disk space.
Shrinking the SSISDB is kind of useless. There are built in procs that purge records in SSISDB. Without executing them, the database will grow out of control. Set up the purge, and stop shrinking. You are in a grow/shrink/grow/shrink endless loop. Free space is not a bad thing.
Why is SQL Agent stopped?
How many tempdb data files do you have? https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver16
You need to set the max memory. https://www.sqlskills.com/blogs/jonathan/wow-an-online-calculator-to-misconfigure-your-sql-server-memory/
You need to raise the cost threshold for parallelism. Start at 45
What is the max degree of parallelism (MAXDOP)? How many cores are allocated to this VM?
Run this, and post the results please.
```
sp_Configure 'show advanced options', 1
RECONFIGURE
```
Then run sp_configure again to show all of the settings.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Michael L John One Orange Chip Points: 27053 More actions · Answer 3

This is a good whitepaper, although it's geared toward Dell hardware.

https://cdn-prod.scdn6.secure.raxcdn.com/static/media/28398947-a676-42e5-bc42-c75f6eefff78.pdf

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jo SSC Enthusiast Points: 195 More actions · Answer 4

Hi Michael,

Thanks for the extensive feedback.

My fault, i should have cut the admin fields. Good remark. In the heat of the speed 🙂 My intention for next year take some more time, and do one case at a time, not 5 at the same time.

Some points that Jo mentioned i already 'fixed'. Nevertheless as i said i'm not an sql specialist. I feel like i'm doing the work for external party who wrote the sql application.

5. Why is SQL Agent stopped? -> i have red that installing this one can give some serious overhead, and slow your system? don't know if this is right?

7. You need to set the max memory. -> done that

8. Raise the cost threshold for parallelism. -> done that, is set to 50

9. Max degree of parallelism (MAXDOP). -> in the advanced report is can see its set to 32767

10. sp_configure advanced. -> did run that, results in attachment.

Attachments:

You must be logged in to view attached files.

Michael L John One Orange Chip Points: 27053 More actions · Answer 5

Jo wrote:

5. Why is SQL Agent stopped? -> i have red that installing this one can give some serious overhead, and slow your system? don't know if this is right?

WHO or WHAT are you paying attention to? Stop now. They have no clue what they are talking about.

SQL Agent is the job engine for SQL Server. It provides the ability to schedule things like backups and maintenance. It is an integral and important part of a SQL Server installation

Jo wrote:

9. Max degree of parallelism (MAXDOP). -> in the advanced report is can see its set to 32767
Simplified, this represents the number of cores SQL can use for parallel processing. 32767 is all available cores. There are tons of articles about how to set this. You may want to start with 8. 8 seems to be the "sweet spot" for most of the servers I have managed, although that varies significantly. There are some applications, such as SharePoint, that recommend setting this to 1, which removes any parallel processing.
You can change this, execute a query, change it again, execute, without re-booting. Keep in mind that one query may benefit from a certain setting, and others may negatively affected.

Jo wrote:

10. sp_configure advanced. -> did run that, results in attachment.

None of the settings are "wrong"

These are the key ones to pay attention to, again, there is a ton of resources about these settings.

Agent XPs

automatic soft-NUMA disabled

backup checksum default

backup compression default

cost threshold for parallelism

cross db ownership chaining

Database Mail XPs

fill factor (%)

max degree of parallelism

max server memory (MB)

min server memory (MB)

optimize for ad hoc workloads

remote access

remote admin connections

xp_cmdshell

There is likely not a "magic" setting that needs to be changed that will solve performance issues.

I think I would start with reconfiguring the drives and the locations of the data, log, and tempdb files. Create 3 new SCSI controllers, add a disk to each one. Format the disks in 64 KB block sizes. Make sure the SQL service account has permissions to these disks.

Second, I would get some backups and maintenance put in place.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jo SSC Enthusiast Points: 195 More actions · Answer 6

additional (usefull??):

we did some testing with the virtual machine where the sql resides.

First we had 4 vprocessors, If we run the sql app and start working with it, we noticed that the sql-service maxed out at 25%

If we double the to 8 vprocessors, we saw that the sql-service maxed out at 12.5%

info from within the virtual machine:

Maximum speed: 2.10Ghz

Sockets: 1

Virtual processors: 8

Virtual machine: Yes

L1 cache: N/A

Michael L John One Orange Chip Points: 27053 More actions · Answer 7

Jo wrote:

additional (usefull??):
we did some testing with the virtual machine where the sql resides.
First we had 4 vprocessors, If we run the sql app and start working with it, we noticed that the sql-service maxed out at 25%
If we double the to 8 vprocessors, we saw that the sql-service maxed out at 12.5%
info from within the virtual machine:
Maximum speed: 2.10Ghz
Sockets: 1
Virtual processors: 8
Virtual machine: Yes
L1 cache: N/A

Ok, that indicates that there is fairly low CPU usage. It looks like CPU may not be one of the performance bottlenecks. However, it depends! The type of testing you did is key.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

durai nagarajan SSCoach Points: 15778 More actions · Answer 8

Jo wrote:

Is there a way we can check if the SQL server is using the full power?
The hyperV layer is a server 2016. The SQL virtual machine is configured with 8 vprocessors and 250Gb of ram. If you run the taskmanager in the virtual machine you can see this. sockets:1 virtual processors:8 max.memory: 244Gb
Any help or suggestions would be fine.
Thanks.

For 8 Processors why RAM level is huge, do they have In memory or other high memory , Regarding actual performance tracking , Check all parameters during actual issue time.

Regards
Durai Nagarajan

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 9

You may be looking at : Memory pressure / CPU pressure / tempdb contention / missing indexes.

Jonathon K. could have good suggestions for you on performance issues.

Here are few things you can try :

Minimum memory to 8-16 GB / SQL Maximum memory 3/4 of the server physical memory.

Adjust the Auto-growth to be in MB increments.

Place tempdb files on fast raid. (Raid 10 for example)

Number of tempdbs = logical CPU count (sys.dm_io_sys_info) - with equal NDF file sizes for each tempdb file and growth.

Place the data and log files on separate disks. (disk striping)

I would not use database shrinking. Rather see Paul Randdals article.

DBASupport

EdVassie SSC Guru Points: 60469 More actions · Answer 10

Can I add a big thumbs up to this.

You obviously need to check your environment to eliminate obvious performance bottlenecks, but after that try to get the software vendor involved. I have lost count of the number of packages that perform badly due to their poor design.

Possibly the most effective thing you can do is look for improvements you can do with indexes. Work out a set of new indexes on your Dev system that make a significant performance improvement and show this working when the vendor examines your system. Get them to allow you to make any index changes you need - indexes are not DB structure changes so they should be allowed. If they really refuse to let you improve performance have your manager lined up to threaten to swap out their package for something that works - this has worked for me.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

EdVassie SSC Guru Points: 60469 More actions · Answer 11

BTW it is so long since I ran SQL Server on anything other than a guest machine I have forgotten what it looks like. Running under Hyper-v is fine.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Jo SSC Enthusiast Points: 195 More actions · Answer 12

Next week they are returning from holiday and everyone will start working again. The SQL app will go to full use again. So then we will see what the effect is of all the good tips i already received on this thread.

Jo SSC Enthusiast Points: 195 More actions · Answer 13

OK all users are back to work. And still complaining about "speed" or the lack of it. As in the previous comments we adjusted all the tips and tricks we recieved. I think that the "issue" layes within SQL or the SQL software.

The only thing we "struggle" with or doesn't fit in our mind is that cpu doesn't go above 12.5% or tops of at 12.5%. It is like something is blocking the use of cpu, or that SQL doens't know that there are 8 cpu's it can use.

Altough if i run different queries on the SQL it says 8 cores are assigned and visible?

We as infrastructure people if we see servers under stress or failing to perform, we see that cpu climbs to 95% or stays at 95%. By that we now that the server chockes in demand and slows down.

Jo Pattyn SSC-Dedicated Points: 32341 More actions · Answer 14

About limited to 12%. Looks like single-core processing. SQL Server is pretty good at detecting available cores.

MaxDop accidentally set to 1 at server/database/query level ?

VM limited to 12% : are there resource limits imposed on the vm?

Check sql server query store / waitstats

Application sequential processing?

Michael L John One Orange Chip Points: 27053 More actions · Answer 15

Because the users are complaining about slowness does not mean that you should expect to see high CPU usage. Or, that utilizing more CPU would make things faster.

You can experiment with your max degree of parallelism and cost threshold for parallelism without any disruption of the system

If you can enlist some users to execute the same process, and try setting MAXDOP differently.

Start with 4, increase up or down and execute the same process. Not very scientific, but it's a start

sp_configure 'max degree of parallelism', 4

RECONFIGURE

Also, none of us have really touched on maintenance. Have statistics been updated? How about indexes?

Really, it sounds like you need a DBA.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/