January 28, 2016 at 8:12 am
I'm working as a consultant at a company which is running their production SQL Server (2012) on a VM. I don't know which VM platform is being used.
The server has been running very slowly and they are discussing setting up the main databases to use multiple data and/or log files.
Any thoughts on this? Could it improve performance?
January 28, 2016 at 8:20 am
Probably not.
Unless they have:
- Done sufficient analysis to determine that the workload is IO-bound
- Are planning to put the additional data files onto a separate physical IO path/device
- Have carefully designed the split of tables across the filegroups
If they have done all of those, then there's a good chance of some improvement.
If they haven't, then there's a high chance that it will be a waste of time with no gain.
Since you mentioned multiple log files (which are not useful, SQL uses them sequentially), I suspect someone's grasping at straws and trying stuff randomly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2016 at 8:45 am
i_am_moose (1/28/2016)
I'm working as a consultant at a company which is running their production SQL Server (2012) on a VM. I don't know which VM platform is being used.The server has been running very slowly and they are discussing setting up the main databases to use multiple data and/or log files.
Any thoughts on this? Could it improve performance?
Are you a SQL Server consultant or some other kind that got roped into doing SQL-centric work for the client? If the former then, putting it as gently as I can, you are asking questions you should REALLY already know the answers to (and as Gail mentioned especially that multiple tlog file one).
There are very simple methodologies to follow to determine root causes of SQL Server slowness, especially generic performance issues. Differential IO stall analysis, Differential wait stats analysis, sp_whoisactive, and aggregate profiler trace analysis. Any SQL Server consultant should have those as their bread-and-butter too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 28, 2016 at 9:05 am
Kevin,
Your reply is intriguing. I will have to look into some of those things.
What I can tell you is that I have access to certain databases to write and run queries. I cannot run any sproc in master that drops a temp table, I have no access to Profiler.
I would never build a production server on a VM, unless that was my only option. This is just how it was done here. I just wanted to get some feedback from others on this.
The biggest wait issues I've found are CXPACKET, PAGEIOLATCH_SH AND PAGEIOLATCH_EX, in that order. We have a lot of indexes, but they are often CHAR types or similar.
January 28, 2016 at 9:12 am
There's nothing wrong with a production server on a VM. I have very few clients still using bare metal servers. Configured correctly, VMs are fine. Badly configured, VMs can be terrible, but so can a bare metal server.
If your client doesn't have anyone who knows their way around SQL tuning (and from the sounds of things they don't), they should get someone in. Or they can flail around and guess for weeks and get nowhere (seen that too often).
P.S. CXPacket is not a problem by itself. It's an indication that SQL is running queries in parallel. The others sound like either memory pressure or slow IO subsystem or inefficient queries (or all three)
If they're based in South Africa or don't mind working with an overseas country and they want help, drop me a PM. I have space for another client.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2016 at 9:24 am
Thanks for the info, Gail. I haven't installed on VMs for some years, so I didn't realize they were considered a good option for SQL Server.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply