A Well, it depends. If there isn’t any resource contention and there is enough of each resource to go around, then there is usually no reason to set any caps. The OS and SQL Server will share the available resources without any problems. However, if there is any resource contention, then depending on the resource and what you want to happen, the way that SQL Server and the OS manage the contention might not give you the result you want. In this situation you should consider setting caps on the resources in dispute.
With memory, each SQL Server instance will take what memory it thinks it needs, up to the available physical memory. When there is memory contention, then sometimes one SQL Server instance won’t release memory as quickly as you might like. If you are in this situation you may need to configure maximum and minimum server memory settings to manually control this. As these are dynamic settings in SQL Server 2005, you can change them without a reboot and see an immediate effect.
The OS and each SQL Server instance does a great job of sharing the CPU between all threads. Sometimes this is what you want, but sometimes you actually want to allow all instances to have an equal share of the CPU, not a share based on how much work they have to get done. Think about a server with one instance running a decision support system (DSS) with a few very complex long-running queries, and another instance running an online transaction processing (OLTP) system.
The OLTP system needs an unfair share of the CPU to allow it to guarantee response times to its users. You have to make sure the DSS instance doesn’t consume all the resources and starve the other instances while executing its complex queries. To fix each instance’s share of resources, you can use CPU Affinity and affinitize SQL Server instances to specific CPUs, or use
Windows System Resource Manager
to manage the share of total CPU resources for each SQL Server instance.
With I/O the OS does a great job of managing contention. You can often help to avoid contention in the design you choose for the I/O system. You do this by laying out your data and log files onto onto different physical disks, each on a different I/O path.
In a clustered environment, there may be additional factors concerning what happens when an instance fails over. In a multiple instance failover cluster configuration you have to take into account not just the resource needs of the instances running on this node, but also the resource requirements of the instances that may fail over to this node. This is a more complex topic and is discussed at length in the white papers located at "
SQL Server Consolidation on the 32-Bit Platform using a Clustered Environment
" and "
SQL Server Consolidation on the 64-Bit Platform
Default and Named Instances
Q I understand that a default instance can run SQL Server 2000 and a named instance can run SQL Server 2005, but are there any issues in doing this? Can a default instance run SQL Server 2005 and a named instance run SQL Server 2000?
A You can mix versions of SQL Server (both SQL Server 2000 and SQL Server 2005) on the same machine running Windows®. The combination can be from the following: one default instance (either SQL Server 2000 or SQL Server 2005) plus everything else as a named instance (SQL Server 2000 or SQL Server 2005) up to the number of supported instances for that version of SQL Server, or all named instances for everything. In a clustered configuration, it is exactly the same—you can have only one default instance plus the supported number of named instances for that version of SQL Server on a cluster, or all named instances.
Antivirus Software and SQL Server 2000
Q What are the best practices for using antivirus software on servers running SQL Server 2000?
A If you’re using Windows Server System™ Reference Architecture (WSSRA), then SQL Server will be behind a firewall in a data center, so it’s protected by virtue of its location. Beyond that, to some extent, the usual recommendation is to set exclusions for the SQL data and log files, and any backups. Otherwise the antivirus software goes mad trying to scan the data and log files every time SQL Server does anything.
Knowledge Base article 309422
also has some antivirus information. However, many times DBAs don’t set exclusions properly and their CPU and I/O utilization goes through the roof. Since this is such a common mistake, it’s the first thing you should check when encountering a SQL Server performance issue.
If you have a dedicated machine for SQL Server which is not being used as a file server, has no shares, and so forth, adding antivirus software with no exclusions poses a greater risk. And on a cluster, besides excluding all SQL Server and Analysis Services file types such as .mdf, .nfg, and .ldf, you must exclude the MSCS directory on the quorum drive as well. It is especially important in a clustered configuration to exclude the SQL Server files. This is because if there is a failure and failover to another node in the cluster, it can take a long time to scan that node, thus preventing SQL Server from coming back online in a timely manner.
Sometimes antivirus software, especially if it’s not cluster-aware, can have strange effects and interact in a negative way with clusters. Be sure to check with your antivirus vendor to see if it is cluster-aware or not.
Additionally, complete understanding of when a full and incremental antivirus scan is performed during the maintenance window for a database server is essential. Typically these scans run in the same timeslot as the maintenance window. To avoid resource contention, you might work with the infrastructure support teams to lock down backups, antivirus scans, software patches, and other schedules. Then you could look at server activity over a 24-hour period to see user time, processing times, and maintenance window times.
Then you can see overlaps of processes and use this for planning. Be aware of floating antivirus scan schedules due to antivirus management server issues and backlogging of tasks. Audit the antivirus scan window periodically to ensure it is occurring when you think it is.
Exporting Data from Oracle
Q I want to export data from an Oracle 9i database and import it into SQL Server 2005. I’m considering SQL Server Migration Assistant (SSMA) for Oracle version 2.0. I want to export the data once a week and then import it into the SQL Server database at a later time.
Otherwise the easiest way to go is to create a SQL Server Integration Services (SSIS) package, and to run it on a scheduled basis. SSMA moves the data from Oracle to SQL Server directly, without writing the data into a text file. You have to be actually connected to both Oracle and SQL Server during the data move, and you can’t export the data into a text file. You can select all or some of the tables to be migrated. Keep in mind that SSMA uses an INSERT statement to move the data, and so it’s usually slower than SSIS.
Thanks to the following Microsoft IT pros for their technical expertise: Dmitry Balin, Shaun Cox, Ernie DeVore, Tim Getsch, Allan Hirt, Ernest Ho, Matt Hostetler, Dimitris Kalyvas, Igor Leybovich, Kristal Lindstrom, Mauricio Lorenzon, John McClelland, Kevin Mineweaser, Maxwell Myrick, Matt Neerincx, Michael Nelson, Al Noel, Vijay Sirohi, Chris Skorlinski, Carl Speshock, Bryan Von Axelson, Frank Wiemer, Heiko Wiesner, Roger Wolter, and Steven Wort.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.