Does anyone run SW on a shared SQL server?

mesverrum adatole aLTeReGo cahunt ,

Hi all,

I am calling directly to you all because you guys have all greatly helped me in the past and provided me guidance when I couldn't find it anywhere through my own research.

Happy to provide any additional information needed. But as you can see from the available I have a very interesting and at the same time weird problem happening. And I can't figure out if it comes down to how our environment and SQL was deployed and setup or if this is something in the configuration that needs to be change? Confused is an understatement at this point. I've been spinning wheels for a full month now trying to figure this out and haven't gained my traction. 

We are hitting up against several constraints due to the licenses we have for SQL and hardware wise. And proving my point to get more resources etc is an uphill battle that's been difficult to fight. I say this because all possible metrics, charts, etc simply do not show critical numbers to warrant any approvals beyond what we already have. 

But before I get there I need to build this picture out a little more. In my research it appears to be deployment and design that's working against us even though the numbers look good. We've made many SQL enhancements within the last month and those took an act of congress to get the approvals. 

Can any of you guys give me your opinion on what we can do? Should we separate drives into individual LUN's instead of housing it all under one roof? Should we use HBA fiber connections? Should we be on a fast policy setup on the SAN? Are there any SQL configurations to look at and configure? Any SQL settings to setup to allow SQL to use the resources available more efficiently?    

Again. I can provide more information if need be. I just really need help on this front so I can try to find Root cause on my problem and work from the root up. 

Thanks in advance. 

I ran ours on a shared instance for years and it ran fine. The only time I ever ran into issues is when doing upgrades. In a shared instance its probably not a good idea to run any type of installer with SQL SA, so on a couple occasions I ran into rights issues during an install. Other than that it ran fine. 

Most customers run Orion on a shared SQL instance, though it is recommended to separate Orion out to its own LUN for the database and transaction logs to minimize any impact it might have on other applications sharing the SQL server. Your TempDB would also, ideally be on SSD for maximum performance. Virtually any query in Orion is processed inside the TempDB, so if you're suffering performance issues this is a good place to start. 

Without knowing exactly what the long-running queries are, or those queries that are deadlocked it's difficult to speculate as to the cause. I would recommend that you run Orion on SQL 2016 SP1 or later if you're not already, to take full advantage of column store indexes. Also, ensure that you're running the 2020.2 release or later, as there were enormous performance improvements made throughout various different areas of the product in this release. 

My sql instance right now is 16 vcpu, 600 gb ram and runs all of the applications associated with the monitoring platforms.  I imagine that my predecessors wanted to avoid any kind of ram bottleneck when they moved to GCP so they just went hard with it.  SQL drives are all split out per database plus tempdb and logson SSD, only the backup drive is bulk storage.  Down to 10 pollers, with about 50k elements but they had significantly more in the past so I think that contributes to the monstrous DB.  Shared isn't inherently a deal breaker but you want to do what you can to slice out resources as best you can, and the ultimate bottleneck I usually ran into with SQL was almost always going to be disk latency. 

Storage teams often struggled to explain why the infrastructure they put together was giving my clients intermittent massive bursts of latency.  I have to say that's one of the nice things in GCP, I monitor for the performance data but ultimately we don't have to be responsible for it and more often than not it performs exactly as advertised with no back and forth haggling with someone trying to convince me that i shouldn't be bothering them about being told I'm on the fastest available SSD storage and then measuring latencies that would have been considered a red flag on spinning disks.

mesverrum 

Very interesting! But I have to ask what is GCP? Not sure I follow. Thanks!

bobmarley 

What you have is what I'm trying to accompish haha. I know it's possible. But proving and getting approvals etc becomes an act of congress when the metrics do not clearly spell out trouble. More often I get the numbers thrown at me and people saying you see there is no problems. Not approved. 

Typically these are also the people that do not understand how solarwinds work and it's requirements. haha

aLTeReGo 

You Sir are the man! Much appreciated. And basically what you stated falls in line with what I had in mind. Essentially it points to design and deployment that could be hampering our performance. Feels good to know that I wasn't wrong in my assessment. 

We are planning to switch over to 2020.2 version. We are just trying to schedule it out to get the right approvals etc. Then we plan to deploy to our test environment before promoting to prod. We want to see if we can actually pin point the improvements so we can do kind of like a proof of concept type demonstration to management. And show case the improvements we should have with the update. 

To give you a little better picture. Our current setup basically has storage sliced up between ESX hosts and then the hosts slice that up for the VM's. I'm thinking overhead in this setup could be causing us potentially performance hits. This alone is very useful information for me to put a report together of sorts. And constrained resources are also another potential performance hits. All together these little things add up to a large performance problem. 

They had switched us recently to higher storage adapters on our VM's, wintel stated that these adapters offer higher throughput then the ones we were setup with. 

One question I have is running an entire deployment off of one esx host is against best practices right? Not recommended? Currently we have pollers, aditional web server, all sw databases, and practically everything sw running out of the same LUN assigned to that one esx host. I'm sure that Lun is also divided into many other vm's running other software meaning it's a pretty loaded lun I would assume. 

The uphill battle for me is that wintel has ran several reports showing averages that are well below any threshold and they push back hard saying the numbers do not support any further upgrades and they deny the request. Typically these same people don't understand solarwinds or it's requirments and how something that seems good could actually be causing problems. 

Thanks so much in advance for all your help.  

Marc,

The help from everyone here including yourself has been very appreciated. And has helped me gain progress. But if you don't mind I needed a little more help to get me to the end goal.

Paging is something that happens constantly in our SQL. From my understanding paging happens due to lack of RAM where it has to page over to hard drive because there isn't enough ram to have information stored on ram. Correct?

While technically Paging at a low level wouldn't really suggest a performance issue. High paging becomes a performance concern. Page faults are also high. I was reading somewhere that this is when SQL goes to ram thinking the data is there than has to go searching for the data because it's not where the data was expected. Correct? 

I was looking at our paging metrics and statistics and I see that after database maintenance page life expectancy goes from 15 days to 4 days and through out the day increases fast. Best practices stats that it shouldn't even be as high as a day. So the fact that we are going two to nearly three weeks out indicates possible paging problems. Page hard faults are also very high. 

The Memory and CPU i/o metric are also high. Being that this is a VM I'm assuming that this happens due to ESX policing who gets access to the cpu correct? 

Network is the only brights spot as network status do not show overhead. Do not show delays. ever. 

Disk performance has improved ever since we made enhancements. We are connects to partitions on the ESX. ESX has 1 LUN per host from the SAN. Fast cach policies are enabled to provide the best possible storage performance to all hosts. 

Because we have always on Active/Active cluster on our sql tempdb's are cycled every 6 hours and I believe replication also happens at predetermined times. I see that when replications happen large delays are seen. It's going from one DB in one datacenter to another in a different datacenter and our interconnect link isn't the most reliable. It's always prone to errors such as CRC errors and outages. It's something we believe could cause delays in replication ever. 

Are there any best practices and high level birds eye view advice you can provide given the additional information above? This way I can add on to the information already here to have a productive discussion with my DBA's. 

Because right now they are reluctant and hesitant to make changes as they aren't seeing problems from their perspectives. 

thanks!