Top 5 Reasons to Assess Your SQL Servers Quarterly

Assessment Overview

A SQL Server assessment also called a SQL Server Health Check, is a review of one or all of the SQL Servers to provide the management team and the DBAs a holistic view of the SQL Server environment. The primary objective of the SQL Server Assessment is to review the current state configuration, health, and performance of the environment, and provide recommendations that focus on increasing the scalability, availability, and performance. If you decide to engage a database consulting company or a database managed service provider (MSP), an added benefit of performing a SQL Server Assessment up front is that the assessment is a baseline for the systems, a level set on what the environment was like on Day 1, and it provides the MSP or consulting company with the information they need to plan the next 90 days with.

Reasons to Perform a SQL Server Assessment Quarterly

1.  Data Growth – The first reason to perform a quarterly SQL Server Assessment is that each SQL Server system is processing large amounts of data or records every day. Ask yourself this question, “What is our quarterly or annual data growth percentage and how is this impacting the performance of the SQL Server workloads?”.  The SQL Assessment will identify the impact of the data growth on the server capacity, performance, data model, and ultimately the SQL statements. For example, it is important to review critical SQL statements that have an increased risk of hitting the tipping point in the execution plan which is when an INDEX SEEK switches to a TABLE SCAN due to data growth.
2. Code Changes – In many situations, we know that code is changing, but do we know how the changes are impacting the SQL Server workload?  One critical part of the SQL Server Assessment is the review the plan cache for non-optimal code so they can be optimized by the development team before the bad code causes a performance issue. This occurs because many of the developers change tables or code but often overlook updating the indexing strategy which can impact the performance and concurrency of the system.
3. Changes in Workload – Another main reason that you need to have a quarterly SQL Server Assessment performed is to gather information on the changes in workload as it applies to applications and users. You may have a situation where you have recently launched a new product or recently ran a marketing campaign that brought more customers or users to your database. With that increase in users, your SQL Server may have an increased workload that you should know about to avoid any downtime or disruption in service. In another situation, your servers may be running the order functions 50 times more an hour which is a change in workload that you will want to address, be aware of, and have certain protocols in place for proactive maintenance for your SQL Servers.
4. Infrastructure Changes – The fourth reason that you should have SQL Servers assessed on a quarterly basis is to gain an understanding of the infrastructure changes going on behind the scenes. In many cases, the changes in infrastructure related to resource pressure with other systems growing in usage often cause a resource issue on SQL Server. A classic example is slow response times for storage. Last year, the storage response times were healthy but last month, we started seeing higher disk response times and Wait Statistics related to IO. Often the Storage Area Network (SAN) which is a shared resource hits a tipping point with capacity with the number of IOPS. This results in slow response times that impact the SQL Server workload.
5. New Applications – Lastly, with a quarterly SQL Server Assessment, you will have valuable data on new applications and workloads. When you ask yourself, ‘How many new features, reports, applications, or integrations were launched in the last 90 days?’, your answer may vary. Businesses are launching new applications and services monthly and each of these applications may need a new database to be deployed to an existing SQL Server. When the database is created, what is the net impact to compute, memory, and IO within the SQL Server instance? How will the other database workloads be impacted? Do we need to adjust and resource or re-configure to optimize the new workload?

All these events impact the health and workload of the SQL Server. It is pertinent that SQL Server assessments are a top-down analysis to identify issues around configuration management, capacity planning, workload health, and other systemic issues that might be hidden if you were to only a local SQL assessment procedure. It also allows you to see resources, volume metrics, and other interesting data at the enterprise level versus the server level. Questions such as ‘What are the top transactional systems in your environment?’, ‘How much did our data grow last year?’ and ‘What systems have the most resource (Waits) pressure?’ are clearly answered and management can now plan at the enterprise level and DBAs can plan at the SQL Server level.

SQL Server Assessments The Fortified Way

Many SQL Server Assessments or health checks provide you with 100 findings but do not show you the risk, effort and the priority or sequence for what to do first, second and third. Fortified gives you recommendations for each finding and then works with you to identify priority changes based on the risk, effort, and established priority to deploy the change.

Fortified uses our expertise from scaling systems and designing large, consolidated SQL Server implementations in various industries to advise and consult with clients about what items should be prioritized to give their business a competitive edge. We focus on what the results from the changes are versus believing that the following key tenets are critical to the success of any data platform that supports mission-critical systems:

• Stability, Predictability, and Reliability
• High Availability (HA) and Disaster Recovery (DR)
• Scalability, Capacity, and Performance
• Data Integrity and Protection

When engaging with Fortified as a managed service client, our Core Health^® database managed services include an initial SQL Server Assessment and health check so that you can gain insight into your SQL Servers and leverage the vast knowledge of the Fortified team to act on the findings and recommended changes. Even more, if your internal team is just looking for a little bit of help or you are not ready to engage a next-generation managed service provider, we also offer standalone SQL Server Assessments so that you can gain access to this pertinent information.