Slow SQL Server performance

[EasyUp Web Hosting]

I do not know SQL that well and do not have certification, but I have worked with it and can do basic things.

So, my question, we have 3x Dell R520 servers set up in HA with VMware Sphere 5.x Server specs are decent, but I know they are populated with 2TB 7200rpm drives and I do not know what RAID controllers are being used and what RAID setup. IT is outsourced and they won't give me the information. Actual servers are connected at 1GB, but not sure if they are aggregated.

The Virtual server has 8 cores, 32GB RAM and 3 volumes, C - Boot, D - Default databases and E - for our software databases. Windows Server 2012 R2 is installed and SQL 2014 Std + SP2 has been set to use max 24GB RAM. There are 59 Databases, excluding the default ones, but I can't say how many of them are being used. This SQL Server is mainly for Acctec software. For some reason all our servers are VMs, even the DCs and there are about 19 Servers. I hope this is enough info for now.

There are 2 other entities involved with this server, namely IT Support company and Acctec. Our software is slow and we have excel files pulling data from the server as well, which takes a long time. As per normal, these 2 companies blame each other for the slowness.

I want to do some troubleshooting from my side to see if it's the software that is slow, e.q. queries taking to long or holding things back or if the hardware can't cope anymore. Looking at the basics, the hardware is doing fine, except, I don't like those normal sata drives, which I think is the main problem.

All this said, we will be moving over to MS Dynamics 365 end of this year, so buying new hardware is out of the question, since we will be hosting it in the cloud.

Average CPU usage is about 54% and mem is at 29GB. Where do I start the troubleshooting?

 

[prod]

This is always a fun exercise. But from my experience...what's the information on statistics and index maintenance? These were our biggest factors contributing to sluggish performance.

 

[EasyUp Web Hosting]

How can I get that information for you?

 

[quovadis]

Before even looking at the SQL instance:
1) Check the IO of the disk arrays to see if it is sufficient to handle the load of the VM's - this is usually the greatest bottleneck.
2) Check the general health of the underlying hardware and arrays etc.

Once this is done move on to the SQL servers' databases and general health/maintenance/indexes etc as suggested by prOd.

 

[Traq]

At 8 cores allocated to the VM, you will need to launch Resource Monitor (Task Manager -> Performance link in the bottom left).
Go to the CPU tab and see if any of the Cores/CPU's is sleeping (Right side)

Fix for this is to turn off CPU sleeping.

Another common issue for SQL 2014 is the change in cardinality estimator, no indexes or statistics will save you from this if the queries are bad.

Fix for this is to change your database compatibility level to 110 (right click database, properties, options).

For a very high level overview, you can also right click the database(s) in question, right click, Reports, Standard reports, and there will be some information.

This should also give you an idea on what needs doing, based on prOd's question regarding index and statistics maintenance.

 

[quovadis]

Fix for this is to change your database compatibility level to 110 (right click database, properties, options).

Foolish advice for not knowing anything about the OP's environment.

 

[Traq]

Foolish advice for not knowing anything about the OP's environment.

Assumption made is that the databases where created as SQL Server 2014 (Compatibility Level 120), the fix for this in SQL 2014 was that you reverted back down one level. (SQL 2016 and higher have a setting for legacy cardinality estimation, unless this was introduced via a feature upgrade for SQL Server 2014).

Additional known fact from the OP is 3rd party companies, where in my experience it is a safe to say that they have not yet adjusted any queries for the change made in SQL Server 2014.

Other things which is an issue, which can likely not be solved, is the 7.2k RPM Drives, the RAID setup is unknown, it sounds like these are local storage. But because we do not know the storage setup, we cannot make a recommendation on what is best to do with the storage (data & log on separate drives, etc)

 

[EasyUp Web Hosting]

Thanx for all the feedback so far.

@quovadis - vSphere isn't reporting performance data on the arrays. Still trying to figure out why.

@Traq - Doesn't seem like any of the cores are sleeping:


Not going to check all the DBs, but the main one is showing compatibility level 100 SQL 2008. I am going to assume these databases were created on SQL 2008 and then the "server" was upgraded, but this was never changed...

Currently the DB and log is on the same drive, for above DB.

 

[poffle]

I think you are spot on with hard drive side.
Can try a few of the following:

1. TempDB, DB data files and DB Logs on the same drives - this is going to cause big slow downs, nothing will fix this unless they are separated.
2. Check SQL Activity monitor, do you have any locks or waits?
3. Performance monitor in windows, check the I/O on the disk.
4. Ram usage looks high, is your DB server used as an application server as well? Might have to do a bit of memory configuration if either the OS or SQL is being starved of ram.
5. Set up an extended events trace on the server and look for long running queries and what queries they are, are they ad hoc, are queries that get run multiple times?
6. DB Maintenance - Indexes, statistics - are the indexes fragmented? Might have to rebuild/reorganize/update those. Careful in a prod environment though. use the [select * sys.dm_db_index_physical_stats] DMV to see fragmentation. > 30% needs a rebuild.

 

[quovadis]

Thanx for all the feedback so far.

@quovadis - vSphere isn't reporting performance data on the arrays. Still trying to figure out why.

@Traq - Doesn't seem like any of the cores are sleeping:
View attachment 447566

Not going to check all the DBs, but the main one is showing compatibility level 100 SQL 2008. I am going to assume these databases were created on SQL 2008 and then the "server" was upgraded, but this was never changed...

Currently the DB and log is on the same drive, for above DB.

The best advice I can give you is to rather refer this to a professional or the company who initially configured the environment for you as usually there is an initial specification which the implementation will need to be measured against. It's ideally something which needs to be properly diagnosed and troubleshooted with all available data at hand.

 

[TheGuy]

Can you find out how the storage is setup? What you can maybe do is to isolate the most critical servers to one host and let stuff like domain controllers share a host. This all depends on how the storage is setup.

 

[EasyUp Web Hosting]

I think you are spot on with hard drive side.
Can try a few of the following:

1. TempDB, DB data files and DB Logs on the same drives - this is going to cause big slow downs, nothing will fix this unless they are separated.

Temp DB is on another volume.

2. Check SQL Activity monitor, do you have any locks or waits?

3. Performance monitor in windows, check the I/O on the disk.

Sorry for large image.

4. Ram usage looks high, is your DB server used as an application server as well? Might have to do a bit of memory configuration if either the OS or SQL is being starved of ram.

No, App server is on it's own. SQL is using 27GB. I can always get them to increase the ram on the VM to 64GB?

5. Set up an extended events trace on the server and look for long running queries and what queries they are, are they ad hoc, are queries that get run multiple times?

I will have to research how to do this, so reply on this will be later.

6. DB Maintenance - Indexes, statistics - are the indexes fragmented? Might have to rebuild/reorganize/update those. Careful in a prod environment though. use the [select * sys.dm_db_index_physical_stats] DMV to see fragmentation. > 30% needs a rebuild.

I get this error. My user is a domain admin and I log onto SQL SMS with the SA account, but read somewhere that this might be why I am getting this error.

 

[poffle]

1. Ok if other volume is a separate physical disk then thats fine.
2. Waits look fine, could you perhaps expand the Processes box in activity monitor and see what is waiting and the wait types.

3. Not sure on the baseline for your I/O, does it look high to you? Can you bench it against anything else? This you'll have to make a call
4. More ram always helps if its available.
6. Sorry the command is : select * from sys.dm_db_index_physical_stats (db_ID(),null,null,null,null) -- the db_id will only give you the indexes on the current database, change it to null for all of them. On your error, you shouldn't have debugging enabled, you should just run this like a normal SQL Query.

 

[prod]

4. Ram usage looks high, is your DB server used as an application server as well? Might have to do a bit of memory configuration if either the OS or SQL is being starved of ram.

It is my understanding that SQL server will consume whatever you give it. So RAM usage will always appear to be high no?

@OP have you considering things like PLE etc? Good advice for RAM vs. db size here:

https://dba.stackexchange.com/a/144410

It's ideally something which needs to be properly diagnosed and troubleshooted with all available data at hand.

Tell that to my previous managers

 

[poffle]

It is my understanding that SQL server will use whatever you give it. So RAM usage will always appear to be high no?

Correct you are, its a tricky one to diagnose if ram is an issue but more is always welcome. If you configure your max memory it will tell SQL to not consume it all, or else it will and thus starving the OS and anything else needing it. SQL wont just automatically use 28gb, it grows till that amount, think this is also a call that someone with knowledge on the system & db will have to make. Many things to consider.

 

[PsYTraNc3]

It is my understanding that SQL server will use whatever you give it. So RAM usage will always appear to be high no?

Yes

And for such a little DBs, using 27Gb should be more than enough unless each of them are linked to a different app that does a lot of SQL queries?

I've got some servers running 3000-6000DBs on 64Gb RAM.
Having various maintenance plans in place does help though!

Since drive performance is in question, I would recommend checking the page file location and move to a different disk if you can.
Also check if the page file size is big enough.
The article below give calculations you could use for the size.
https://blogs.technet.microsoft.com/motiba/2015/10/15/page-file-the-definitive-guide/
I just stick to the old RAM*1.5

 

[EasyUp Web Hosting]

Can you find out how the storage is setup? What you can maybe do is to isolate the most critical servers to one host and let stuff like domain controllers share a host. This all depends on how the storage is setup.

The best info I can give you, all 3 servers have 8x2TB 7200rpm drives installed and I am going to guess it's in RAID 5, as the free space is being reported as 7.28TB.

1. Ok if other volume is a separate physical disk then thats fine.

All on the same RAID set.

2. Waits look fine, could you perhaps expand the Processes box in activity monitor and see what is waiting and the wait types.

3. Not sure on the baseline for your I/O, does it look high to you? Can you bench it against anything else? This you'll have to make a call

I saw an article somewhere saying that this is a bit high, but the solution would be to upgrade the drives.

4. More ram always helps if its available.

Then I will put the request through.

6. Sorry the command is : select * from sys.dm_db_index_physical_stats (db_ID(),null,null,null,null) -- the db_id will only give you the indexes on the current database, change it to null for all of them. On your error, you shouldn't have debugging enabled, you should just run this like a normal SQL Query.

Yeah, I go NEW QUERY in SSMS and paste your command and get the same error.

 

[EasyUp Web Hosting]

Yes

And for such a little DBs, using 27Gb should be more than enough unless each of them are linked to a different app that does a lot of SQL queries?

I've got some servers running 3000-6000DBs on 64Gb RAM.
Having various maintenance plans in place does help though!

Since drive performance is in question, I would recommend checking the page file location and move to a different disk if you can.
Also check if the page file size is big enough.
The article below give calculations you could use for the size.
https://blogs.technet.microsoft.com/motiba/2015/10/15/page-file-the-definitive-guide/
I just stick to the old RAM*1.5

The one database is 53GB in size. Page file is set to auto and located on the same drive as the database for some reason. Currently 32GB.

 

[poffle]

Okay CX_PACKET is a problem. -- well the wait type CX_PACKET isn't the ACTUAL problem but its a problem because this means all those cx_packets are waiting in line for another process to finish.
Generally its caused by I/O but you cannot be 100% sure until you investigate.

You can see what is blocking those transactions in the column Blocked By 180.
Its being blocked by itself. Whats the wait resource? exchangeevent something

CXPACKET does generally involve parallelism. check the instance settings.
https://www.sqlshack.com/troubleshooting-the-cxpacket-wait-type-in-sql-server/


Back to your SSMS problem. Its weird, sa should be able to execute these without a problem by just hitting f5.

 

[EasyUp Web Hosting]

Is this the one you wanted to see?