Hot answers tagged

43

The general rule of thumb is keep the core count as low as possible, and the processor speed as high as possible. The licensing math on that proves the point at ~$7,500 USD per core for Enterprise Edition. Buying the correct hardware can pay for itself in reduced licensing costs. See Processor Selection for SQL Server by Glenn Berry. It's a great resource ...


18

If your primary wait is ASYNC_NETWORK_IO, then the issue is not with SQL Server. It is almost always due to an application bottleneck. I don't mean a bottleneck on the application server, but rather a bottleneck in the application. The application bottleneck is usually because of row-by-row processing while SQL Server is sending the data: The application is ...


18

As discussed the last time you asked this question, your top wait is ASYNC_NETWORK_IO. SQL Server is sitting around waiting for the machine on the other end of the pipe to digest the next row of query results. I got this info from the waits stats results of sp_Blitz (thanks for pasting that in): 1 - ASYNC_NETWORK_IO - 225.9 hours of waits, 143.5 minutes ...


16

I'll answer in two parts: first "why the traditional answer about separating sequential and random often doesn't apply." Then I'll discuss the potential benefits of separating files at the Windows physicaldisk, and to adding additional vHBAs and distributing the physicaldisks among them. Expecting benefit from separating random and sequential disk IO at ...


15

What you're asking is, "Is it okay if we overcommit CPUs?" It's okay until you start experiencing performance bottlenecks that are related to CPU consumption. The same answer holds true with network overcommitment as well - it's not like you're putting 5 separate 10Gb Ethernet cards in the host, and dedicating one card per VM. Virtualization is all about ...


14

Hold on hold on hold on While the performance and licensing aspects are interesting, they're not the only aspect of a workload to consider. One thing that can have an impact on processor choice is worker threads. Worker Threads? Yeah buddy! They're the things that your SQL Server will use to run your queries and do all the background stuff that it needs ...


13

The problem I see with having dynamic allocation of some resources is that it leads to unpredictable performance. Reporting query x had 32 CPUs available yesterday, and ran in 4 minutes, today it only has 24 and it took considerably longer. You can also see latency as the guest waits for other cores to become available. Jonathan Kehayias gives some ...


11

My understanding is that some level of CPU over-commitment may be perfectly OK, but that totally depends on the workload requirements of all the guests. Check out David Klee's blog for a lot of good VM information. Specifically http://www.davidklee.net/articles/sql-server-articles/cpu-overcommitment-and-its-impact-on-sql-server-performance-on-vmware/ for CPU ...


11

Any non-SQL Server-process that consumes CPU may cause the entire SQL Server to be completely unusable. As an example, Windows Update will bring SQL Server to a stop while it is unpacking .Net assemblies. Any Anti-Virus process, even if it is configured to exclude SQL Server data files and folders may cause SQL Server to become unresponsive while it ...


9

Things I see wrong right away Way to many instances Not enough vCPUs vNUMA probably doesn't match NUMA I'm guessing there's a ton of missing indexes MAXDOP of 1 or 2 Cost threshold of 50 Optomize for adhoc workloads = on (it's probably off) Questions What do the waits look like? What are all the memory configs setup for? Fixing this is going to take an ...


7

What I am seeing is that in Task Manager, sqlserver.exe is using about 163 Mb of RAM, if I use procexp, the same service shows just under 500 MB of RAM used. You should never look at task manager to check SQL Server memory utilization. It would never show you correct result. You should use a DMV sys.dm_os_process_memory, if you are using SQL Server 2008 ...


6

VMWare VMotion isn't going to reboot your server, restart any services or drop caches. The VM stays live during the VMotion, so you shouldn't lose cache or plans, unless the host you are moving to is under severe memory pressure and ballooning is active. What does happen during VMotion is increased network latency and maybe a dropped ping while migrating, ...


6

MySQL Server will only execute each query in a single foreground thread, there is no support for multi-thread execution at SQL side. It also executes maintenance operations like ALTER TABLEs that can rebuild a whole table in a single thread. However, engines like InnoDB, specially in recent versions, are able to perform its background threads concurrently, ...


6

To answer my own question: The main reason for ASYNC_NETWORK_IO appearing on our SQL Server as the top wait type, was that the energy saving setting of the windows server was set to 'balanced' instead of 'high performance'. We talked to some vm ware admins afterwards, and they all said, that this setting kills performance. Solutions for this are either: ...


6

I've had to prove this same thing before. What finally got through to our capacity planning team is that our databases were nearly unusable during a backup. As a DBA, your biggest fear should be data loss. If you can't confidently run backups without taking or causing an outage, that's a big issue. Run some tests to make sure performance is impacted while a ...


5

The following two articles make it clear that VMware will present a virtual NUMA configuration to the guest OS that is optimal. Accordingly, the number of sockets that are configured on the VM are more or less irrelevant as to the number of NUMA nodes. If the actual hardware has at least 8 cores in a NUMA node, then VMware is only going to present one NUMA ...


4

I am here answering my own question to help those who are experiencing the same behavior. After many observations and experiments with searching over the internet, I found the solution to this issue following the next steps. Make sure you test everything before going to production. Disable TCP Chimney features on the both the SQL Server & Client ...


4

I would prefer not to use this backup solution but I have no choice but to cope with it. You are thinking absolutely correct. Please note that VMware backups take backup using snapshot mechanism its not like backup you take using TSQL Command. Its not a good idea to rely on VM backups I strongly suggest you to start takign TSQL backup specially if ...


4

Ensure you read the VMware / SQL Server best practices guidelines. This is taken from that document: Utilize the VMware Paravirtualized SCSI (PVSCSI) Controller as the virtual SCSI Controller for data and log VMDKs. The PVSCSI Controller is the optimal SCSI controller for an I/O-intensive application on vSphere allowing not only a higher I/O ...


4

For most workloads, once a processor, the number of vcpus, and the amount of vRAM has been chosen for the vm, the best choice will vcpus and vRAM spread evenly across the smallest number of vNUMA nodes which allow the vcpus and vRAM to align with physical resources on the same number of physical NUMA nodes underneath. This blog post from Mark Achtemichuk ...


3

There is no clear best practice on this. It all depends on your VMWare configuration and failover requirements. The idea behind this is the fact that it should be preventing SQL Server from releasing memory when the VMWare balloon driver thinks it needs this. There are several possible problems with that. One of them being the fact that the balloon driver ...


3

So you've got 5 'stacked' instances on a single Windows server. You haven't said exactly how many sockets/CPUs are available and how much memory though. I like setting affinity for each instance in such cases, even if I decide to have CPUs overlapping in the struggle to balance the overall CPU load (depends on each instance's load). Any instance with more ...


3

Wes. Those Perfmon counters are a good start to make sure that the VM is configured correctly (meaning, the host isn't wildly overcommitted on RAM.) However, they don't help size the VM. If you're having performance problems, you'll want to identify two things: The SQL Server's top wait during that time, and The queries causing that wait To get the top ...


3

The three NICs won't help with redundancy, because the VM layer should have already handled this configuration under the hood in the virtualization layer itself. However, three NICs would allow for segregation of particular types of server traffic (backups, AG replication, etc.) away from the primary NIC, which allows you to monitor and profile the usage ...


3

This was eventually identified as a side-effect of VMWare LRO. Disabling host-based LRO resolved the issue. See Enable or Disable LRO on a VMXNET3 Adapter on a Windows Virtual Machine Large Receive Offload Poor network performance or high network latency on Windows virtual machines (2008925) vmxnet3 adapter on windows server 2012 with MSSQL server ...


3

Is it normal to share CPUs on a VM of SQL Server? Yeah, it's pretty common. A lot of times VMs are used to consolidate lots of SQL Servers (especially ones that don't have extreme performance requirements) onto one host. This can save on licensing costs, since SQL Server can be licensed at the host level. Is it a good idea? I mean, it depends a lot on ...


3

Follow the process here: Move Database Files to move the databases to the new storage. You can leave the insance online, but each database will need to be OFFLINE while its files are copied to the new location. Alternatively you can present the new drives, shut down SQL Server, copy all the files to the new drives, unmount the old drives, mount the new ...


3

Are all of my assumptions above correct? Yeah, you're correct on all counts as far as I can tell. If so, does that mean there is no way to boost the CPU available to these queries? Given the constraints you've described, the only way to "boost the CPU" for those queries would be to move the VM to a host with a CPU that has a faster clock speed. ...


3

> is this the best approach for performance? That is at the same time too broad a question (i.e. will be very opinion based) and to narrow (other people looking for advice are unlikely to have that same config) > Please remember downtime is acceptable for some servers at a time in the case of a drive failure because this is not production workloads Are ...


2

If the "SQL Server VSS Writer" service is running IO on the database files will be "frozen" while a snapshot is made of the databases and the database state (full database backup) will be included with the VM backup. This will not lock down the SQL server but will in some cases make it slower. If the service is not running the database files will not be ...


Only top voted, non community-wiki answers of a minimum length are eligible