SQL Server Maintenance

Why an SQL Server Maintenance Plan is a Must

Often in the Information Technology world, there are phrases used to refer to servers, applications, or processes that just continue to run day after day with no administration or intervention required. “Set it and forget it”, “If it ain’t broke don’t fix it”, and others refer to this notion of setting up a server, installing an application or performing some configuration, and then never having to worry about it again.

I have seen IT departments that have phone servers sitting in the corner of a room that were set in place in the late 1990’s and have been running for 15 years or longer without an issue. Typically when they do have a problem, it’s a power supply, fan, or another moving component that wears out and has to be replaced. Then the box continues to run for another 10 years.

I did work one day within an IT environment and I remember asking the manager about one of the server’s that was running a part of the website. When I asked where it resided, he told me in complete honesty, that he wasn’t sure which rack of equipment it was running in. Jokingly, he told me it responds to pings, but he doesn’t know physically where it’s located! Stumped, I followed up by asking what the plan was in the event something happened to it. He responded that it had been running flawlessly for some 8 years and he saw no need to get involved for as long as it kept running.

While that may be a rather short sighted view of the issue I was trying to bring to his attention, the reality is that in many cases that’s the mentality of an IT organization. Sometimes the only time a server is called to mind is to ensure it’s part of a backup or disaster recovery strategy. While that is a critical exercise, the reality is there are some servers that require more than the “set and forget” type of deployment.

Don’t Settle for Slow SQL

SQL servers are one such instance. Without maintenance performed on a routine and regular basis, SQL servers will continue to get slower and slower. Sometimes the resolution is to perform a server restart. While that may clear up some of the problems temporarily, the reality is the server will continue to operate in a less than optimal manner.

This degraded performance, while seemingly minor, can have a compounding effect on the productivity of the end-user. For instance, consider an SQL server that is hosting a mission-critical ERP system used by roughly 75% of the company. This is not an unusual scenario, as often ERP software covers the entire order flow process, from the sales order to the invoice, and everything in between. A company would recognize that this server is critical to its operation and ensure it is part of a comprehensive backup and disaster recovery plan. That’s all well and good, but what is overlooked more often than not is that the performance of the server degrades over time. Thinking there is nothing that can be done to resolve the issue, or that there are no ‘hard costs’ associated with it, many companies don’t pay any more attention to the issue.

However, consider the impact SQL performance could have on a company:

I once performed an assessment for a company with several hundred employees, across multiple shifts, the majority of whom worked in the warehouse. Some time after the application was implemented, I was told that ‘clicks’ or actions within the warehouse management application took 3-4 seconds to respond. For instance, clicking a button to enter a work order number, the user would wait several seconds for the screen to pop-up with the prompt for the work order. After typing the number in and clicking ‘OK’, it would take several more seconds to confirm and retrieve the work order. A few seconds here and there was nothing anyone was concerned with, but working with the management team we attempted to identify the true cost of this delay across the organization. Because the degradation in performance had been gradual, users had just come to ‘expect’ the delays out of the application.

After shadowing several employees across the course of one hour of their typical job duties, we determined the average warehouse employee clicked around 70 buttons, and waited around 3 seconds for a response from the system each time. This correlated to around 3.5 minutes per hour of non-value-add time, waiting for the system to respond. Multiply that by 100 employees, and that’s just under 6 hours. Multiply that number by an 8-hour typical work shift, and a total of just under 47 hours of wasted time is recognized. In an 8 hour shift, across 100 employees, the equivalent of almost 6 employees is spent just waiting, for the entirety of the shift. With two 8 hour shifts per day, with shifts 6 days a week, we identified that some 23 days of employee time are being wasted, every week.

In the above example, we were able to reduce the wait time by optimizing the SQL server by around 1.5 seconds, roughly cutting it in half. The results of a change like this are phenomenal. Not only does management recognize a more efficient workforce, with less time spent “waiting on a system”, but the user perception of the system increased and we received positive comments all around, from warehouse employee to the management team.

This is a rather extreme example, but there are more of them out there. The direct correlation between SQL server performance (which is often at the heart of any application performance issue) and the performance of the end-user (employee), is very seldom recognized. Also note that in many cases the performance gradually degrades, which results in users not recognizing it.

What does SQL server require to keep it functioning at an optimal level?

  • Log file growth
  • Database integrity checks
  • Reorganizing of indexes
  • Updating statistics
  • Rebuilding of indexes
  • Optimization of database and log files
  • Optimization of memory, CPU, and storage utilization
  • Testing backup and restores

Depending on the applications or databases in use, additional tasks may need to be performed on a routine or recurring basis, as changes to database architecture are made.

Don’t Forget the Indexes

One of the most important issues that many run into when maintaining an SQL server is that of indexes. While indexing is a complex topic that many whitepapers have been written on, the concept is simple. For simplicity sake, think of an index in SQL as being like a telephone directory (do you still remember what those look like?). If you want to search for someone with the last name “Pierce”, you would know to turn to the ‘P’ section, then to the ‘Pi’ section, and so forth. The concept is such that rather than having to start on the first page of the phone book and flip through thousands of pages, you know exactly where the record you’re looking for resides because the data is arranged in an orderly manner.

Updating the printed telephone directory for a change is impossible since there is no space to add records between existing records. In printed form, changes to a phone directory can only be implemented at the next printing. SQL obviously needs a way to implement changes immediately, as large SQL server environments are making thousands, sometimes millions, of changes to records every second. When SQL needs to obtain a record (using our analogy, perhaps the ‘Pierce’ record), rather than having to search sequentially through countless records – which are in no particular order, to find the Pierce record, it can go directly to it because it’s been indexed. This may not seem like a big deal for a powerful SQL server, but there are often millions of records that have to be searched, and often hundreds or thousands of requests per second.

This is the importance of indexes in optimizing SQL server. The difference between well-executed indexes and no indexes or missing indexes can make the difference between a fast and well-performing server, and one that crawls. They can significantly impact the performance of the application.

Indexes are just one example of an aspect of SQL that requires monitoring. It’s typically not possible to simply implement an index and expect it to continue working. Since SQL is a living, breathing machine, it requires constant observation, tuning, and tweaking. It’s true a lot of this takes place automatically within the SQL server, but components such as indexes are always going to require some manual adjusting. The same concept applies to statistics, log files and the distribution of temp DB files, databases, and other components.

SQL Server Maintenance Is Key

The bottom line is that unlike web servers, phone servers, and other servers that typically get set up and never have to be managed or maintained outside of just making backups, SQL servers need ongoing maintenance to keep running in an optimal fashion, providing the best and most efficient access to and availability of data to your users and applications.

If you have concerns about the performance of your critical business applications, Rand Group has a team of highly motivated and exceptionally certified IT professionals that can assess your current set-up and offer recommendations for overall improvement.

If you’re running a business management application such as Microsoft Dynamics NAV, Microsoft Dynamics GP, Microsoft Dynamics AX or Microsoft Dynamics CRM or SharePoint, and notice the performance is lacking, let us help eliminate the frustration.

– Software Delivered as Promised. No Surprises.

Latest Posts