cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 13

Question regarding Always on SQL. Performance issues.

We have  a DR environment setup with HA pools for the primary and for our additional servers. For SQL we setup a cluster with always on. Because of how Microsoft creates the cluster use of Full recovery was necessary. However, We have many errors that still point towards SQL contributing in a big way to the problems. You send a query and it's simply taking too long to respond. I am a new SolarWinds Admin to my company so I don't have yet have the full specifics of the deployment. But all things considered for out build it is using SolarWinds recommendations. Yet, I can't get snappy quick responses from sql. It's delayed more than what would be expected. Than when database maintenance runs and really loads SQL that's when we notice things slow down enough to alert us that SolarWinds is not syncing with sql. And usually this clears when db maintenance clears.

Few Pointers.

1. SQL is built on a VM. I know that's against recommendation. But these days I can count more companies with VM sql's and less companies with physical. SQL on VM is fine, It just requires additional settings to ensure that it's running correctly. 

2. The disk is on a SAN system. Again, another thing SolarWinds typically complains about. Thing is that these are to my knowledge 15k SAS drives. And they are setup for max IOP's. My storage guys say the setting has similar performance to RAID-10 If not better.

So build wise I don't believe it's a problem. I think it's just a matter of tweaking parameters. For example. We changed parallelism settings according to some suggestions on sw kb's and also active diagnostics and it helped. But wasn't nearly enough to get performance to acceptable levels.

Is there any queries I can run to determine if performance is where it's supposed to be? Are there things I can do to optimize SQL. I know this starts to stray from SolarWinds, But I could use a little help to determine more accurately my sql performance and what I may need to fix.

I think our LUN for that database is being shared for all drives and I think this could be a huge cost against performance to have one lun handle everything for SolarWinds. I plan to split the Drives in different LUNs. Our storage is a EMC Unity storage infrastructure. If anyone is familiar with this storage and can give me suggestions how to setup SolarWinds storage within it please offer any suggestions you may have.

 

At the end, the focus is SQL health. We are trying to baseline the SQL so to speak to get performance metrics and then optimize as needed.

 

Any help would be appreciated. Thanks.

Labels (6)
0 Kudos
2 Replies
Level 12

We are also using a SQL Cluster with always listening.  I have many many issues with this and have an on going case.  One thing that my DBA did do was to point the always listening to only one of the two cluster members.  This helped big time with "unable to connect to the data base" events in the event logs and response seems to be better. 

There are others issues and support is saying that is the database and my DBAs are saying it is the application. I am also getting a lot of index fragmentation, database maintenance takes 4 hours. And so it goes.

I'll stop my rant here.

I hope this little bit is of help.

0 Kudos

For cases like this I always recommend spinning up a VM and running a DPA eval if you don't already have the product. It lets you know what resources are causing the worst of your delays so you can focus your efforts on the biggest contributors. With the SQL AG across separate Datacenters (pretty common for DR) I do find that it's not uncommon to see maybe 10-20% of your waits just being related to HADR_SYNC_COMMIT (main host is waiting for secondary to write the data and report back that it is done) but as you said, nothing you can do to mitigate that if SQL AG is in use.
- Marc Netterfield, Github
0 Kudos