optimizing microsoft sql server 2005 environments...2007/01/03 · relation to specific...
TRANSCRIPT
DELL POWER SOLUTIONS Reprinted from Dell Power Solutions, November 2006. Copyright © 2006 Dell Inc. All rights reserved. November 200696
DATABASES: SQL SERVER
Many organizations today struggle with complex
administration, widely distributed data, system avail-
ability problems, and high overhead costs. The Microsoft
SQL Server 2005 database platform can help mitigate
or eliminate these problems, providing organizations of
all sizes with an enterprise-grade database platform for
critical online transaction processing and business appli-
cations that can help enhance availability, performance,
scalability, and security.
Planning a migration or upgrade to SQL Server 2005,
however, can be a challenging process. As organiza-
tions plan a SQL Server 2005 implementation, their
first step should be to assess their current environment.
EMC Corporation, a leader in information management
and storage, offers assessments for SQL Server to help
organizations understand their existing environment and
plan for their migration or upgrade.
Carrying out EMC assessments
for Microsoft SQL Server
An EMC assessment for Microsoft SQL Server evaluates
an organization’s existing SQL Server environment and
provides recommendations for a migration or upgrade.
EMC gathers information about the environment nonin-
trusively and then uses this information to provide guid-
ance based on actual environmental metrics, including
architectural insights and advice as well as evaluations of
performance, capacity needs, forecasting, and design.
The first stage of an EMC assessment involves data
collection and analysis. The process begins with the
BY CHAD SAKAC AND KEVIN KLINE
Optimizing Microsoft SQL Server 2005 Environments with EMC Assessments and Quest Software
Migrating to or upgrading a Microsoft® SQL Server™ environment can be a challenging
process. EMC provides assessments for SQL Server by using Spotlight® on SQL Server
Enterprise from Quest Software to evaluate database performance. These assess-
ments can help organizations understand their existing environment and plan their
new deployment to meet their performance and availability goals.
Related Categories:
EMC
Microsoft SQL Server 2005
Quest Software
Visit www.dell.com/powersolutions
for the complete category index.
DATABASES: SQL SERVER
www.dell.com/powersolutions Reprinted from Dell Power Solutions, November 2006. Copyright © 2006 Dell Inc. All rights reserved. DELL POWER SOLUTIONS 97
organization completing an in-depth questionnaire about its SQL
Server environment. EMC then uses a variety of data collection
and analysis tools to provide comprehensive information about the
environment. Data is collected for up to 72 hours, during periods of
both high and low activity, to help provide an accurate analysis.
Within days following an assessment, the organization receives
a final report that provides detailed information about the existing
SQL Server environment, recommendations, and an action list and
implementation plan for executing the recommendations. The recom-
mendations cover system and storage infrastruc-
ture, including particular areas where migrations to
SQL Server 2005 and a Microsoft Windows Server®
2003 x64 OS as well as SQL Server optimization
can provide benefits. The summary report also
includes recommendations to help maximize local
backup and recovery capabilities, create test and
development replicas, and protect the SQL Server
environment from a site disaster.
Enhancing the assessment process
with data collection and analysis tools
EMC uses multiple tools to help provide accurate
assessment information. The most basic of these
tools is Microsoft Windows® Performance Monitor ®
(perfmon). Perfmon collects and records performance
counters from the Windows OS and SQL Server data-
base, measuring the amount of activity within a dis-
crete area of the OS or database. However, perfmon
does not correlate these discrete areas of performance
with each other, so after this tool has collected up to
72 hours of activity on the database server, an EMC expert ana-
lyzes this data using internally developed tools and processes.
Figure 1 shows a screenshot of such an analysis.
The perfmon part of the analysis can provide excellent
insight into elements such as utilization, storage I/O profiling,
and SQL Server OS (SQLOS) memory management—all criti-
cal from a server, OS, and storage design standpoint. However,
EMC cannot rely on perfmon alone to provide a thorough
assessment, particularly in the areas of system impact cor-
relation to specific transactions, SQL Server query analysis,
and optimization. Fortunately, powerful tools from Quest
Software have allowed EMC to enhance the speed, accuracy,
and comprehensiveness of its assessment process.
Using Spotlight on SQL Server Enterprise from Quest SoftwareWhen searching for tools to enhance its assessment process,
EMC wanted to provide its field experts with immediate and
simple analysis of OS and database performance; real-time,
granular, root-cause analysis; correlation of performance
metrics with SQL Server user activity; and a detailed knowledge
base for understanding and diagnosing performance bottlenecks.
Spotlight on SQL Server Enterprise from Quest Software met
these requirements. By providing a visual representation of the
OS and database components through an intuitive graphical user
interface, Spotlight enables quick and easy identification of per-
formance bottlenecks and problem areas. The reports generated
by Spotlight further augment the ability of EMC field experts to
carry out a comprehensive assessment.
Figure 1. EMC analysis of data collected with Microsoft Windows Performance Monitor
Figure 2. Top-level dashboard window in Spotlight on SQL Server Enterprise from Quest Software
DATABASES: SQL SERVER
DELL POWER SOLUTIONS Reprinted from Dell Power Solutions, November 2006. Copyright © 2006 Dell Inc. All rights reserved. November 200698
Spotlight uses a dashboard approach to depict OS and database
performance. Figure 2 shows the top-level window in Spotlight. A
green gauge indicates that a given component of the database is
functioning properly, a yellow gauge indicates a potential prob-
lem, and a red gauge indicates an outright problem, an error, or
a serious bottleneck. When a dashboard gauge turns yellow or
red, an EMC expert can immediately view the low-level details to
perform a root-cause analysis.
The thresholds for each level (green, yellow, and red) are
dynamically determined by Spotlight using an internal calibra-
tion process. Spotlight first assesses the normal activity for a
particular server, then configures the thresholds that escalate a
gauge from green to yellow and from yellow to red. (Users can
also configure these settings manually.) For example, Figure 2
shows the CPU Usage gauge at 100 percent, so Spotlight has
turned this gauge red and raised an alert. The EMC expert can
then view the details of the CPU Usage gauge to see CPU activ-
ity, the processes using the CPUs, the processes waiting for the
CPUs, and so forth.
Figure 3 shows an example of a typical Spotlight details
window, in this case for SQL Server sessions. From the major
panels on this screen, users can also view additional session details
and other related information.
In addition to performing real-time monitoring and root-
cause analysis, EMC experts can also research issues within the
extensive Spotlight knowledge base. This knowledge base can
be helpful when an expert does not have personal experience
with a given problem.
Optimizing SQL Server with expert
analysis and tools
Organizations can face many challenges when
migrating to or upgrading Microsoft SQL Server
environments. EMC, which has more than a decade
of experience with SQL Server environments,
offers organizations its expertise and knowledge of
database and storage requirements through EMC
assessments for Microsoft SQL Server. In carry-
ing out these assessments, EMC employs industry
standards, best practices, and useful tools such
as Spotlight on SQL Server Enterprise from Quest
Software, which provides real-time database per-
formance information and helps EMC discover
the root causes of potential and existing database
problems and bottlenecks.
EMC assessments for Microsoft SQL Server
can help organizations understand their existing
environment before moving forward with a
migration or upgrade, by providing real perfor-
mance data, an analysis of the current environ-
ment, and recommendations for the future—with the ultimate
goal of helping organizations enhance SQL Server performance
and maintain their database investment.
Chad Sakac is a director for midsize enterprise solutions at EMC, where
he is responsible for solution validation—creating, testing, benchmarking,
and refining best practices for the primary applications deployed on EMC
platforms. He also manages a global field team of application experts
who work with customers to implement best practices for their critical
application environments.
Kevin Kline is the director of technology for SQL Server at Quest Software,
designing products for database administrators and developers. Kevin is the
author or coauthor of four books, including SQL in a Nutshell and Transact-SQL
Programming, and numerous magazine and online articles. Kevin also serves
as president of the Professional Association for SQL Server and has been a
Microsoft MVP for SQL Server since 2004.
FOR MORE INFORMATION
EMC services for midsize enterprises:
www.emc.com/stdforms/urlTrackServlet.jsp?evntCode=AMA00002357
Spotlight on SQL Server Enterprise from Quest Software:
www.quest.com/spotlight_on_sql_server_enterprise
Quest “Worth Upgrading to SQL Server 2005?” Webcast:
www.quest.com/upgrade
Figure 3. Details window in Spotlight on SQL Server Enterprise from Quest Software showing SQL Server sessions