Odd SQL server performance since install SP 2013 Foundation

[Homerboy]

I've had a SQL server running at our offices for years now. It does a lot of reporting, data mining etc etc. without much issue.

Earlier this week, I installed MS Sharepoint 2013 Foundation on a new server and pointed the database to my existing SQL server. Ever since that install, SQL queries that we have been running daily for months and months are taking longer than they ever have before. It seems like the CPU (8 core Xeon) never gets above 20% CPU utilization no matter what sort of query I run - that's out of the norm for sure.

Would the install of SP 2013 and whatever it setup on my SQL server affect it in such a way? I can not for the life of me figure this out, and it's REALLY affecting our day-to-day operations.

 

[Exterous]

Are these VMs by chance? When we installed SP2013 several years back we ran into issues with the VMNet virtual nics. Changing our SP servers from VMXNet3 to E1000E resolved the performance issues despite the throughput limitations (We might have changed the SQL servers too? I don't remember). There was no sign of CPU, RAM, disk, network pegging but SP websites were way slower than SP2010 and we had to failover other DBs off the server the SP DB was on due to performance issues before we made that change. I don't know if we ever found a reason as to why that was causing the issue to happen

 

[Homerboy]

Are these VMs by chance? When we installed SP2013 several years back we ran into issues with the VMNet virtual nics. Changing our servers from VMXNet3 to E1000E resolved the performance issues despite the throughput limitations. There was no sign of CPU, RAM, disk, network pegging but SP websites were way slower than SP2010 and we had to failover other DBs off the server the SP DB was on due to performance issues before we made that change. I don't know if we ever found a reason as to why that was causing the issue to happen

The Sharepoint server is in a VM. SQL server is bare-metal install.

I should have been more clear though - it's not SP that is slow. In fact that server (VM) is currently turned off entirely for right now. I'm referring to SQL queries we used to run on the SQL server (built in reports, ad-hoc queries through SSMS) that are now slow. Manual queries that used to take 10-15secs are taking 10-15mins. And that CPU does_not_budge. It seems that it's just not allowing the CPU to be hammered for a short window to provide the results or something.

 

[Exterous]

The Sharepoint server is in a VM. SQL server is bare-metal install.

I should have been more clear though - it's not SP that is slow. In fact that server (VM) is currently turned off entirely for right now. I'm referring to SQL queries we used to run on the SQL server (built in reports, ad-hoc queries through SSMS) that are now slow. Manual queries that used to take 10-15secs are taking 10-15mins. And that CPU does_not_budge. It seems that it's just not allowing the CPU to be hammered for a short window to provide the results or something.

Our issue did affect other DBs running on the same SQL server but it sounds like the issues aren't the same. I do know that we got to a point where we could run other DBs on the same server as the SharePoint. I'm not sure server capacity vs DB workload comparisons between our environments but it should be possible. For reference: We could run our several hundred site SP 2013 DB on a 4 core, 48GB SQL server along side our SCCM DB for 8k windows computers

 

[Homerboy]

Our issue did affect other DBs running on the same SQL server but it sounds like the issues aren't the same. I do know that we got to a point where we could run other DBs on the same server as the SharePoint. I'm not sure server capacity vs DB workload comparisons between our environments but it should be possible. For reference: We could run our several hundred site SP 2013 DB on a 4 core, 48GB SQL server along side our SCCM DB for 8k windows computers

I think I have it fixed. It seems the maxdop setting was now at "1". I set it to "0" to allow FULL hammering of the CPU while running queries and it seems back to it's normal self. Of course hard to directly compare, but I can at least see the CPU now spiking through the roof when I do some ugly subquery or something