Skip to main content

SQL Server Performance Monitoring, Tuning, and Load Balancing

Published: May 13, 2003

Please note: Portions of this transcript have been edited for clarity

Hosts:

  • Sada Krishnamurthy, Performance Lead in SQL Server group
  • Stephen Wynkoop, Founder of SSWUG.ORG and e-editor of the daily SQL Server newsletter
  • Bill Carroll, SQL Server support engineer for Microsoft Product Support and Service
  • Mark Allison, SQL Server MVP
  • Prabhaker Potharaju, Technical Lead, Microsoft SQL Server Support
  • Stephen Dybing, SQL Server Communities Program Manager

Moderator: Stephen (Microsoft)

Welcome to today's SQL Server Performance Monitoring, Tuning, and Load Balancing chat. I will ask the hosts to introduce themselves.

Host: Sada (Microsoft)

I am Sada Krishnamurthy. I am a Performance Lead in SQL Server group.

Host: StephenW (SSWUG)

I'm Stephen Wynkoop, founder of SSWUG.ORG and e-editor of the daily SQL Server newsletter.

Host: Bill (Microsoft)

My name is Bill Carroll. I am a SQL Server support engineer for Microsoft Product Support and Service. I have been working with SQL Server since version 4.2.

Host: Mark (MVP)

Mark Allison, SQL Server MVP currently a freelancer in the City of London, UK, also working with SQL Server since 4.2

Host: Prabhaker (Microsoft)

My name is Prabhaker Potharaju, Technical Lead, Microsoft SQL Server Support.

Moderator: Stephen (Microsoft)

And... I am Stephen Dybing, SQL Server MVP Lead. Glad you all could make it today!

Let's get started! Please fire away with your questions for our hosts.

Host: Bill (Microsoft)

Q: Is there any specific thing you have to do to configure SQL Server to use dual processors? I checked this option under the Server Group properties, but don't see any difference when I use one or two.

A: SQL Server will automatically use all CPU available. On the server properties page make sure that all CPU's are checked.

Host: Bill (Microsoft)

Q: We're in an ASP (application service provider not Application Server Pages) environment where each customer has their own database. Each of these databases are identical in structure and design (tables, views, stored procs, etc.) are their either any "gotchas" to look out for, or any specific enhancements we can make in this environment over the normal tuning enhancements?

A: If your application has one database per customer be careful with your MemToLeave area of memory. For example if you get over 700 databases. Add the -g switch. For each database the MemToLeave needs 64K to manage the transaction log.

Host: Prabhaker (Microsoft)

Q: When I am doing SQL profile trace, the CPU numbers don't sound correct when I enable connection pooling. Does Microsoft know this?

A: The CPU values of a Batch or RPC events is not related to the connection pooling. Can you let us for what event you see the cpu values been not reported correctly. What is the SQL server version? What event in the profiler you see the cpu values been displayed incorrectly? RAJ: There is a know issue with RPC:Completed event reporting CPU value zero, you can contact Microsoft Product support to pursue.

Host: Mark (MVP)

Q: Have a RAID 5 and RAID 1 on Server. Data on Raid5, Logs RAID1. Is it best to have my tempdb and it logs on the RAID 1?

A: Yes it is better to have your logs on Raid 1 as the transaction log is written sequentially. You may want to test tempdb performance on either array, but I would suspect it would be better on RAID5 as that has higher write performance.

Host: Bill (Microsoft)

Q: Windows XP provides Perfmon logging to a SQL database. Is there documentation to understand and analyze these counters? I am particularly concerned about understanding the disk counters.

A: The book "SQL Server 2000 Performance tuning" from MS Press has a good discussion on SQL Server system monitor counters and performance tuning. Books Online has some good discussions on memory counters.

Host: StephenW (SSWUG)

Q: BOL states when using FT index config VM to 3 times physical, why so?

A: While we don't have ft expertise here, my understanding is that since FT indexer runs outside SQL Server, it requires the additional footprint for the index management and processes that run the FT capabilities. (no expertise here == no ft experts on this end of the chat :))

Host: Mark (MVP)

Q: Is there any major performance gain by having multiple files for the database's data file? (ndf) Secondly multiple logs if on the same drive?

A: For small databases (less than 50 gig) there is no performance gain to be made if the data/log files are on the same drive/raid array. However, Microsoft have done some tests with the Human Genome project that suggests that some gains can be made for VLDBs > 1Tb. See http://www.microsoft.com/sql/techinfo/administration/2000/rosetta.doc

Host: Bill (Microsoft)

Q: Has anyone developed a how-to for Monitoring SQL Server? Like a checklist approach that can be used?

A: There are many 3rd party documents that discuss Monitoring SQL Server and approaches. The main tools that we use are the SQL Server blocker script, profiler, and system monitor. There are also many 3rd party applications for monitoring SQL

Host: Sada (Microsoft)

Q: I realize that a simple SELECT statement probably shouldn't notice a difference with dual processor...is there an example query you can provide that I can run with a single process configuration against the dual to tell that it is running correctly?

A: Select count(*) from a large table should create a parallel plan.

Host: Bill (Microsoft)

Q: MemToLeave being the memory to leave for the OS, no? So the OS needs 64K per Log, right? Not SQLServer?

A: SQL Server memory is broken up into two parts, The BPool, which is what is dynamic and what BooksOnline talks about. There is also a MemToLeave area that for SQL 2K is set at 265MB. The -g switch will increase the MemToLeave area. See SP3 readme for more information. The MemToLeave is used for memory allocations over 8K.

Host: Sada (Microsoft)

Q: For a small # of users is it appropriate to decrease # of worker threads to improve/free resources?

A: Not really. The potential gains are very limited.

Host: Bill (Microsoft)

Q: What is this MemToLeave used for?

A: If a memory allocation is over 8K it will be from the MemToLeave area. Typically the MemToLeave area is used for things like extended stored procedures, OLEDB (linked server), sp_oa* create, PSS structure for logins, transaction log, etc.

Host: StephenW (SSWUG)

Q: Any whitepapers on scale up vs. scale out?

A: We're not aware of any, just because it's so solution-specific. That said, you should talk with your server vendor. They may also have studies (or formal whitepapers) that could help in documenting their experience.

Host: Bill (Microsoft)

Q: What is the performance implication of MemToLeave and can this be tuned?

A: On SQL Server 2000 the MemToLeave area is set at 256MB. You can use the startup parameter -g to increase at startup. So you can add the -g384 for example. See the service pack readme and search on -g. Usually, we see memory error if there are MemToLeave pressure.

Host: Prabhaker (Microsoft)

Q: How can I narrow down sqlserver.exe? It is paging.

A: Can you give more details, do you intend to know if SQL is doing lots of paging?

A high rate for the Pages/sec counter could indicate excessive paging. Monitor the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging.

To determine whether SQL Server rather than another process is causing excessive paging, monitor the Process: Page Faults/sec counter for the SQL Server process instance. If the target is less then the total that means other process/application is contending for memory hence SQL is dynamically releasing the memory to other process.

Host: Mark (MVP)

Q: Is there a way to reduce the large memory used by SQL Server after a big operation? Sometimes the server hangs due to this.

A: Have you allowed enough memory for the OS to operate in? For NT4 the minimum should be about 100Mb, for Win2000, a little more, perhaps 150Mb. You can set this in EM, by right-clicking your server and then selecting Properties, then select the Memory tab. and then selecting Properties, then select the Memory tab. and then select and then selecting Properties, then select the Memory tab. and then selecting Properties, then select the Memory tab.

Host: Bill (Microsoft)

Q: So are you saying we should leave it at the default?

A: Yes, I would leave it at the default. If you get memory errors like 17803, 17802 then you should investigate if you are using xp, sp_oa*, linked servers etc. Then maybe increase the -g. For most installation the default is appropriate.

Host: Mark (MVP)

Q: Mark, I am asking, even when the settings are dynamic, the server hangs after a big dts operation.

A: Even when using dynamic memory, you may want to limit the max memory SQL Server can take. This doesn't sound like a memory problem to me though - perhaps something in your DTS package is causing a problem? Have you checked your SQL Server error logs?

Host: StephenW (SSWUG)

Q: Is SQL NUMA aware?

A: Could you clarify: "SQL NUMA"

Host: Sada (Microsoft)

Q: Have a SQL 2k Win 2k server running multiple databases (1 db per application). How can I easily determine server resources (CPU) used by a specific database in real-time. Like System Monitor CPU counter, broken out by DB.

A: There is no easy way to appropriate the CPU cycles by DB.

Host: Bill (Microsoft)

Q: If your server is only used for a client/server application, should you Reserve physical memory for SQL Server? Also, what is the standard minimum amount of memory to allocate for queries?

A: For SQL Server 2000 the default configuration parameters are very good for most installations. Microsoft has done a lot of work to make as many parameters as possible "self" or "auto" tuning. For example memory will be taken and given back to the OS dynamically with the default parameters.

Host: Mark (MVP)

Q: Mark, the DTS package executes smoothly, but the huge amount of memory used by SQL Server is not released.

A: Does this cause a problem? SQL Server will not release the memory unless requested by the OS. At what point does the server hang (as you mentioned earlier)? I don't know of a tool to release memory.

Host: Sada (Microsoft)

Q: What are the counters generally tracked using the sysperfinfo system table?

A: All the perfmon counters tracked by SQLServer.

Moderator: Stephen (Microsoft)

Q: Still I am asking if there is some tool to release the memory immediately.

A: No, SQL Server is designed to release memory when the OS asks for it, as a result of memory pressure. There are no tools to free memory immediately.

Host: Mark (MVP)

Q: Mark, every time package is run, Additional 500 MB is taken by SQL

A: What is in that package? Do you have DTS Custom tasks implemented as COM objects that are misbehaving?

Host: StephenW (SSWUG)

Q: Question about individual processes in sysprocesses: I occasionally experience processes that will not complete (they sit there "awaiting command")

A: Awaiting command typically means that the process is complete, but the connection remains. SQL has done the work requested and is just waiting for your next statement.

Host: StephenW (SSWUG)

Q: Why would it then be holding resources such as locks?

A: Awaiting command means it doesn't have any work to do. It's possible that you started a transaction and never finished it?

Host: Sada (Microsoft)

Q: What's the performance hit with Torn Page Detection? Should it be off or on for an OLTP 70gig DB?

A: It is around 5% on some well known benchmarks. But the recommendation is to always turn ON.

Host: Prabhaker (Microsoft)

Q: SQL max memory setting can be changed to lower value to release the memory to OS for immediate effect.

A: Don't have to, these are dynamic unless its AWE enabled. Check BOL topic: Server Memory Options. Min server memory and max server memory are advanced options. These settings take effect immediately (without a server stop and restart).

Moderator: Stephen (Microsoft)

Q: We have seen results of huge servers being benchmarked on tpcc.org. OD we have the same kind of results for more practical servers (the ones we can afford)

A: The whole benchmark scene is about proving your database has the best performance. Perhaps nobody can find anyone in their marketing departments who is interested in providing non-optimal benchmark scores? :-)

Host: Bill (Microsoft)

Q: I'm just setting up a SQL 2K SP3 server with multiple instances. What do I need to look for in tuning this server? Should I limit how many processors and how much memory each instance has? 4 processors 3 GB memory.

A: With multiple instances we suggest to let the instances dynamically manage themselves. SQL Server will release resources back to the OS and then they will be absorbed by the other SQL Server instances. If you are not going to let the instances dynamically manage themselves you need to do some analysis on workloads for each instance then modify resources accordingly.

Moderator: Stephen (Microsoft)

Q: Stephen, in that case, there is no way to know which kind of hardware will be best for my application within my budget.

A: Unfortunately, that's correct. Nothing beats performing your own benchmarking using your own application load.

Host: Sada (Microsoft)

The TPCC numbers are published for most entry level servers.

Moderator: Stephen (Microsoft)

I'm not familiar with that site and what is available on it. However, Sada is saying that we are publishing benchmarks on 1 and 2 processor machines. Perhaps you and I should both take a close look at the site. :-)

Host: Sada (Microsoft)

SQLServer is the leader both in price & performance from 1 CPU on wards

Host: StephenW (SSWUG)

Q: Does it in anyway affect the performance when you have SQL server and IIS server in different domains

A: Adversely - the things to watch for are connection latency issues between the boxes (if you're jumping firewalls for example). Otherwise, it's typically better optimized. The processes don't fight each other as they would on a common box.

Host: Prabhaker (Microsoft)

Q: Any known gotchas using AWE?

A: No, works great with 4GB and greater RAMS.

Host: Bill (Microsoft)

Q: Thanks BillC_MS. Right now I have the only instance limited to just 2 processors since the server is not fully loaded and I don't want to hear complaints when response time slows a couple of seconds.

A: You can also use system monitor to gather some data to analyze what processors, memory and physical disk are doing.

Host: Prabhaker (Microsoft)

Q: In Oracle, the initial connection is with 1251 and then it assigns another port for future communication with the client. In SQL Server, once the client connects to 1433 at server all future result sets would be coming out of 1433. Is that a correct statement?

A: Dbnetlib.dll opens a connection to UDP port 1434 on the computer network name specified in the connection. When a client Dbnetlib.dll connects to this port, the server returns a packet listing all the instances running on the server. For each instance, the packet reports the server Net-Libraries and network addresses the instance is listening on.

Host: Mark (MVP)

By default, the default instance communicates to and from the client over port 1433. However, the first instance that starts on the computer also listens on UDP port 1434 and keeps a list of which instances are running on which ports on the computer. Again by default, when you attempt to connect to a named instance, the client connects to UDP port 1434, gets a list of the instance names and port each instance is listening on, and then uses that information to communicate with the named instance.

You can assign static TCP ports to be used for communication using the Server Network Utility. However, if you do that, you'll also need to modify the clients to connect to that specific port. One way to do that is using the Client Network Utility. The Client Network Utility can also be used on your client computers to specify which port to connect to when UDP port 1434 has been blocked as a result of the Slammer virus.

Moderator: Stephen (Microsoft)

Thanks for joining us today! You've asked some great questions, but unfortunately, it's time to go.

Host: StephenW (SSWUG)

Thanks everyone for coming by today - if you have suggestions for future SSWUG.ORG/Microsoft chats, please post them here, or feel free to email them to swynk@sswug.org  www.sswug.org when you get a minute...

Host: Sada (Microsoft)

Thank You

Host: Mark (MVP)

Thanks everyone, goodbye.

Host: Bill (Microsoft)

Thanks for using Microsoft SQL Server!!!!

For further information on this topic or about SQL Server, please visit the following:

Newsgroups: SQL Server Newsgroups

SQL Transcripts: Read the archive of past SQL chats.

Website: Visit the Microsoft SQL Website