Optimizing SQL Server Operations - first steps

Post Featured Image

Optimizing SQL Server Operations - first steps

When the number of Enterprise SQL Servers begins to grow along with your business, it is often difficult to keep up. A lack of coordination or database expertise can exacerbate problems associated with this growth. Consolidating and optimizing your servers will go a long way toward reducing the cost and complexity of your database environment. This brief is the first in a series of articles on optimizing SQL Server environments, describing the initial steps to take toward consolidation. 


This is not about virtualisation of database servers. This is about the steps you should take before you consider if and how best to virtualise servers. When you are done with consolidation you have trimmed down your SQL Server farm to the optimum configuration and have the best possible starting point for virtualisation. If you have already virtualised your environment you can then consolidate your SQL Server instances.


The number of databases using MS SQL Server has exploded since its launch of version 2005 . Its increased capability and reliability have made it the platform of choice for more critical enterprise applications, especially given the high fees and complex licensing of its Oracle competitor. Vendors typically recommend a dedicated SQL Server for their applications – often accompanied by excessive recommendations for size, functionality and performance, along with unique versioning and patching requirements. The unchecked growth resulting from these separate requirements, along with the associated need for additional test, development, and training servers results in a large, fragmented, and often unmanageable database environment.


This wasteful situation obviously leads to unnecessary hardware, licensing, and administrative costs. Without in-house database expertise you are stuck following application vendor recommendations. With the help of an experienced and competent SQL Server database administrator (DBA), however, you can take back control of your server environment through a series of specific tasks.


The first step, and probably the most crucial, is to establish all of the routines and processes necessary for collecting the relevant information and statistics about your systems. This information will serve as a basis for simulation, followed by the complete restructuring of the database environment. Restructuring consists of both consolidating necessary systems as well as phasing out redundant or unused servers. This step will have the largest impact on the final structure and function of your server environment, so it is critical that all relevant information be collected as precisely and accurately as possible.


To achieve an optimal database environment, we recommend the following data collection procedures for each MS SQL Server instance. Collect statistics for:

  • the amount of data being accessed (logical reads) on each entire instance
  • the transaction volume of the 10 most active databases per instance, and the totals on each entire instance
  • memory usage (data cache) per database on the top 10 databases per instance
  • databases with the least number (or zero) transactions per instance
  • the number of connection per instance (and for 10 databases with most sessions)
  • the filling ratio on the 10 largest file groups per instance, and the total size of all database files

In the next briefs I will discuss:

  • Implementing monitoring procedures.
  • Important factors when consolidating databases
  • Extracting relevant information for consolidation from activity- statistics
  • I have 150 SQL Servers, how many do I really need? How to simulate a new, optimal database environment

You can read more about how dbWatch can help you optimize your SQL Server environment here.


Marek Jablonski is the CTO of dbWatch AS

No comments (Add your own)

Add a New Comment


code
 

Comment Guidelines: No HTML is allowed. Off-topic or inappropriate comments will be edited or deleted. Thanks.