SQL Server Performance

SQL Server multiple instances and resource utilization - best practices

Discussion in 'SQL Server Knowledge Sharing Network (SqlServer-qa' started by satya, Mar 14, 2008.

  1. satya Moderator

    A newbie or FAQ within Forums and Newsgroups that having multiple instances and how best to set the resources on the server such as memory, CPU etc.
    Should you configure each of the instances to use specific portions of the available Operating System resources then best to leave the default settings, for instance dynamic memory settings on SQL Server. As per the configuration of SQL Server you can easily mix versions of SQL Server (both 2000 and 2005) on the same machine running Windows server, such as 1 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. On the other hand for the Clustering basis configuration this 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.
    Then coming to the Resource Utilization the answer is it depends. Say if you have 4 multiple instances and 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 or limits. The Operating System and SQL Server will share the available resources without any problems. But when you find out that there is a 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.
    The default setting of SQL Server dynamic memory settings when 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.
    Also having the Anti Virus software installed on the servers is a common placement and in this case make sure that 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, refer to AntiVirus-SQLServer post as well. Ensure to have a 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.
    So keeping this in mind whenever required the operating system and SQL Server instance will have a great job of sharing the CPU between all threads. Better to collect the statistics during busy times & less busy times using SYSMON tool, this will get you much information. 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. 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".
  2. Anonymous New Member

    A newbie or FAQ within Forums and Newsgroups that having multiple instances and how best to set the resources
  3. Anonymous New Member

    Pingback from in which business situations might you use a multi instance server
  4. Anonymous New Member

    Pingback from Kingswood Price, Chevrolet Kingswood Replacement 8cyl L 5.7l Brand
  5. Anonymous New Member

    Pingback from Transporter Airways, Sale Transporter 3
  6. Anonymous New Member

    Pingback from 528e Used List, Aftermarket Prices Bmw 528e
  7. Anonymous New Member

    Pingback from Rush In Rio Live Version Geddy Lee, Lw300 Auction Saturn L Series Keyless Entry - 109.codebluehacks.org
  8. Anonymous New Member

    Pingback from C15c1500 Silverado Cooling System, 2000 Chevrolet Malibu Parts Silverado - 31.computeronlinebingo.com
  9. Anonymous New Member

    Pingback from V5100n Discount Being, Apple Ipad Lenovo Thinkpad Sl510 - 352.jordanbrandallamerican.com
  10. Anonymous New Member

    Pingback from 1987 - 1994 @ Wrecked Toyota Prius Hybrid Sale, Toyota Prius Sale Search Tire - 189.ja3ra.com
  11. Anonymous New Member

    Pingback from 1980 - 1991 @ Cheap Cts For Sale In Chicago Cadillac Catera, Cadillac Aftermarket Catera Parts - 107.jeepsunlimted.com
  12. Anonymous New Member

    Pingback from 1989 - 1980 @ Arrow T Shirts, Arrow Best - 161.dlmreza.net
  13. Anonymous New Member

    Pingback from 1990 - 1985 @ K10 Airport, 735i Marketing Bmw K100 - 226.animejin.com

Share This Page