Showing results for 
Search instead for 
Did you mean: 
Create Post

Webinar Q&A: Stop Throwing Hardware at SQL Server Performance Issues

Q: Does the power saver mode also apply to VMs?

A: Sort of. The servers that the hypervisors run on have powersaver modes but the virtual OS the SQL is running in doesn't

Q: Can you please provide a clickable link to the kb article regarding power saver mode?


Q: What performance counters would you look at to determine that was the issue if it wasn't something you knew about?

A:To our recollection, this question was about HBA queue depth. Traditionally, people have looked for a disk queue length that is high - 5 to 10 maybe. With most SANs, this number can be a red herring since you don't know the underlying spindle count. Mostly you want to identify a disk performance issue by looking at the Physical Disk counter. When the latency is high HBA queue depth could be the issue.

Q: How does this work with VMware?

A: Same counters to look at, but you want to review both host and machine counters. A machine might not show disk issues based on its activity, but the aggregate from all machines on the host may identify an issue.

Q: What about hyperthreading on or off in a VMWare based environment?

A: Usually off in VMWare or Hyper-V because they hand out hyperthread processors as if they were real processors and that can cause inconsistent performance issues.

Most people don't use hyperthreading with VMWare but I have seen it used successfully. I've heard you should always assign an even number of processors to the hosted OS in this case but I haven't experimented with that myself.

Q: Is there anything in your software that can help me detect the problem with NUMA or make a decision that I shall restrict the SQL TO ONE NUMA NODE ONLY?

A: The point with NUMA nodes is that if you keep MAXDOP less than or equal to the number of physical processors in a NUMA node, SQL Server can keep most of the memory access local to a NUMA node and avoid the overhead of cross-node memory access. This doesn't mean that all memory access is contained within the NUMA node. For example, if a page the query needs is already in the buffer in another NUMA node, it is much faster to access it there than to load another copy from disk into the local node.

Q: Isn't the maximum number of cores limited by the edition of SQL? I think standard has quite a low limit.

A: Yes it is. For example, SQL 2014 standard edition is limited to 16 cores.

Q: That is not what NUMA is. NUMA is because modern processors each have their own isolated memory. And getting at the memory that is on another CPU is expensive. So, the NUMA node size is simply the number of cores on your CPU (or twice that with Hyper-Threading enabled).

A: This is a good explanation of NUMA nodes. While in practice a NUMA node contains the number of cores that a processor socket contains, this is an implementation convenience and there's nothing to say that a NUMA node couldn't contain two sockets worth of cores for example. SQL Server also supports soft NUMA which can define different NUMA node sizes.

Q: On my system I have sudden drops of PLE and I cannot correlate it with anything?

A: Sudden drops in PLE are usually caused by SQL Server running low on buffer memory and throwing a bunch of pages out to make room. This can often be correlated with a large table scan, a large sort, rebuilding a large index, etc. but in some cases it is just a combination of smaller things using up all the memory. The things we talked about to reduce table scans - better indexing, query rewrites, etc. still apply in this case but it may not be possible to find a single query that is causing it - the may be multiple queries that need to be addressed.

Q: the Cost Threshold of Parallelism be changed on the LIVE system without starting SQL Services?

A: Yes, this is a dynamic threshold that takes effect as soon as it is changed. Note that it doesn't change currently running queries, only queries that run after the threshold is changed.

Q: would forced parameterization affect the utilization of filtered indexes?

A: By and large, forced parameterization prevents the use of filtered indexes. Since the queries are parameterized there is no mechanism to identify if the filtered index is valid for the query.

Q: We user DPA 9, and we have a strange scenario: I have information where Signal Waits percents are up 25%, sometimes 30%, 40%, 70%! But the CPU utilization is low than 15%.

A: Signal Waits are SQL Server's way of controlling processes running on the CPU. If you have high Signal Waits and low CPU utilization, review the other waits that are happening on the system. For example, if you have a lot of lock or LOCK waits that are all waiting on a resource, and the resource suddenly frees up, all of the processes that waited for the resource suddenly request CPU. If you don't have enough CPU threads, the process goes into a queue (i.e. signal wait). Lots of recompilations and unnecessary sorting (missing index) can cause this as well. The other thing to keep in mind is that processor utilization is an average of all the processors so it's possible to have a processor running with 100% utilization on a query and still have the average utilization be 10%. The signal waits you are seeing might be from queries running on a single processor waiting on that processor.

Labels (1)
Version history
Revision #:
1 of 1
Last update:
‎01-29-2015 08:42 AM
Updated by: