December 28, 2022 at 8:53 am
Standard answer for slowness -> what are your waits. Have you already activated query store to find the culprits? Is it patched up
Hyper-V:
Is it someway throttled: powersaving , resource limits, low priority ...
December 28, 2022 at 10:03 am
Hi,
and what kind of workload do you use?
OLTP or OLAP?
Check your configuration for parallelism and so on.
There are some nice free tools to check, what kind of problem your server got.
sp_blitz from Brent Ozar may be a good and easy choise.
Good luck
Andreas
December 28, 2022 at 3:23 pm
Hi,
I'm not a SQL expert, i'm the infrastructure guy :-). So i'm looking at the more infrastructure-side of things.
Users complain that 'sometimes' the third party sql-application works slow, slow to realy slow they say. So the guys of the SQL-aplication say (ofcourse) probably this has something to do with the server. So laying the ball in my camp. While i'm saying everything you asked for is there, and is even oversized. So you get the point.
in attachment are some screenshots of the SQL server Perfomance Dashboard. Maybe the numbers will say something to you guys, because for me its still struggeling to understand. (Altough the last few days i'm learning sql :-))
Thanks for any feedback.
December 28, 2022 at 4:09 pm
It's nice to have the dashboard.
You can use it when it's slow ("user request") to make an inventory of troublesome queries / blocking
Most expensive queries by cpu / reads / ... give you an insight since last restart of the database
Your screenshots shows there is very little going on (only one session).
There are other applications beside sql server, but the cpu impact is minimal ( less than 5%?)
Your database is patched up to the latest CU
December 28, 2022 at 4:12 pm
As Andreas wrote, if you have access to the database you can use sp_Blitz, sp_humanevents, sp_WhoIsActive for a quick triage.
For administration of SQL DB's you might also look at dbatools
December 28, 2022 at 4:16 pm
Yes, i did all updates and pathches on server OS and SQL server.
Indeed, the company is in holiday now, so thats why there's not much going on. And thats why i may update, restart, upgrade, test all the servers :-).
Glad to hear that the numbers are not showing anything out of the ordinary.
Once the company re-starts, more usage will be generated, and maybe i can post back some real-life usage. If thats okay?
December 28, 2022 at 5:48 pm
Best practice question.
We have configured everything as best practice.
What "best practices"? Can you elaborate on these, and where you learned these?
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/
December 28, 2022 at 9:03 pm
External company makes remarks that the server is the issue or could be the issue.
This is easy then... have a video meeting where you drive and they tell you what to look for/try/etc. HAVE THEM PROVE what they say. They should be happy to do it so that they can "prove" it's not actually their database design/software.
Almost every 3rd party database/software company I've complained to about performance have always blamed the server/hardware/index maintenance/blah/blah/blah. They also say, "Well, you're the first customer to complain". I so much want to tell them that we're also the first customer they've had that can actually tell. 😀
I've recently had one 3rd party database/software company explain that they need to archive rows from the biggest table to avoid documented major slow downs once it goes over [insert drum roll here] 40,000 rows. It's got like 15 columns of data in it and none of it has large character based columns.
There's another company that can't understand why them doing 149,000 reads on the same table for the same rows every time someone tabs to a new filed (close to 200 active sessions) is a bad idea.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2022 at 10:12 pm
regardless of other items, there is one thing you do need to confirm - is the HOST set to have cpu at full power all the time (or not) and is the VM also need to have power at full all the time.
if either has power saving mode on, that can slow down even the best SQL you have with a perfectly good db/tables/indexes.
and is it NUMA aware and are the cores/memory NUMA aligned.
December 29, 2022 at 7:57 am
Hi Frederico,
Thanks for the tips. I did the following adjustements.
Server 2016 OS
Power settings -> all on max.power -> was OK
Smart array raid controller
Power settings -> Max performance -> was OK
HP proliant BIOS settings
Power settings -> Dynamic Power Savings Mode -> Adjusted to "Static High Performance Mode"
HP Static High Performance Mode—Processors run in the maximum power and performance state, regardless of the OS power management policy. This mode is useful in environments where performance is critical and power consumption is less important.
December 29, 2022 at 8:57 am
In the meanwhile i had the possibility to run the sp_blitz. (not under real-life-load)
The result are in attachment. Curious if that output will show something to you SQL-guys.
Thanks for the feedback.
December 29, 2022 at 9:28 am
Haven't opened the attachment yet.
One of the result columns of sp_Blitz is URL which has more details what the check is about
Like "Active Tables Without Clustered Indexes" -> https://BrentOzar.com/go/heaps
December 29, 2022 at 10:41 am
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
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply