Home The Wired CIO Keeping your SQL Server healthy

Author's Opinion

The views in this column are those of the author and do not necessarily reflect the views of iTWire.

Have your say and comment below.

Keeping your SQL Server healthy

These are the tools I depend on, both when establishing a new SQL Server or encountering one in the wild.

Any significant application needs to store data and, although the cloud is becoming more prevalent, the old-school on-premises SQL Server was the winner of DB-Engines' 2016 database of the year.

DB-Engines tracks database popularity through Google trends, job offers, professional profiles, and other mechanisms that combine to show the popularity of a database in actual use.

No doubt a contributing factor is Microsoft's work on releasing SQL Server for Linux, but it certainly must be recognised that SQL Server is a high-performance, high-quality database management system in its own right.

Yet, like any database, you cannot simply leave it to itself. You must manage disk storage, disk performance, index fragmentation and other factors to ensure the continuing excellent performance.

Here are the three free and publicly available tools I use whenever I set up SQL Server, or whenever I am assessing the health of somebody else's SQL Server.

dbWarden

dbWarden is a monitoring package which delivers a nightly report, along with alerting, giving many facts and figures about your database health, schema changes, file growth, disk I/O and a good many other items.

This script is freely available without cost. It was first published in SQL Server Central by Michael Rounds in April 2013 and can be downloaded from SourceForge. At the time of writing, it was last updated in May 2016 and is a mature and stable product.

I can testify to the value of dbWarden. At one company I consulted, I was assured by the development team their SQL Server was in RAID 10 configuration. Yet, I observed application timeouts and long-running queries and I sensed something was not right.

dbWarden confirmed disk I/O was a problem; disk reads and writes and waits were excessive.

I further tested this with sp_Blitz (below) and was confident disk activity was greatly degraded on this system.

I asked the IT manager to show me the disk configuration, and it is something I will never forget. The server had hardware RAID with two RAID controllers. One was not being used. The other had two RAID 5 volumes on it. Within Windows, one of these RAID volumes was allocated exclusively to drive E:. "What's that?" I asked, and was shown it was the page file. The other RAID volume was partitioned within Windows to two drive volumes, C: and D:.

C: of course, contained Windows and the SQL Server software. D: held database files and log files, together on the same disk.

Oh, and D: also held a file share for one of the busiest departments in the company.

It also held a file share where all the company applications ran from. They weren't installed locally. This very same IT manager insisted that you got the best database performance when apps ran from a file share on the same server as the database. I couldn't believe my ears when I was told that, but I digress.

For now, seeing this disk configuration, I couldn't help but wonder how anyone had tolerated such a setup for so long. I knew why disk I/O was so poor. I could only look at the IT manager in surprise.

He, in turn, said "The sysadmin must have changed this! These were RAID 10!" and went out and asked the systems administrator. He said, "No, that's how it has always been." The IT manager responded, with speed that was in deft contrast to his infrastructure, "Oh yes, that's right; we ordered this from the hardware vendor and said it would be a database server so this is their best practice."

Needless to say, after some discussion with management, a Nimble SAN was soon purchased.

Your experiences may not be as extreme, but this is a real-world example of a company labouring with suboptimal database performance, believing it was actually the best they could get. Yet, every operation was performing poorly. Every minute of every hour of every working day, many staff members were waiting for database operations to complete. The damaging effect on productivity was vast and, in my personal opinion, an unacceptable situation for any IT department that considers itself competent to preside over.

Brent Ozar 1st Responder

Consulting firm, Brent Ozar, makes available a free set of scripts, along with other resources, to analyse the health of a SQL Server environment.

I also used Ozar's sp_Blitz script in the situation described above, in conjunction with dbWarden. Both gave me vital information.

They differ largely in purpose; dbWarden is designed to be a continuing daily and nightly reporting tool on the ongoing health of a system, while the Ozar toolkit is largely to run once, or as-needed, and then to deal with the issues presented.

What I particularly like about this toolkit is the extensive detail it provides on indexing, and the script sp_BlitzIndex can be run over specific databases as well as the server as a whole.

Ola Hallengren's management and indexing scripts

Lastly, the third essential tool I believe any SQL Server needs is Ola Hallengren's scripts for ongoing maintenance and backups.

It's pretty easy to set up a maintenance plan in SQL Server, but is it truly effective? You can tweak it but Hallengren's scripts have been used, and tested, by many large organisations including banks, Universities, energy providers, governments and more. When you deploy these scripts for your ongoing database management and backups you are leveraging the practices and testing of many very large SQL Server environments.

Unlike dbWarden and Brent Ozar's scripts, Ola's require some configuration and consideration to set up how you would like, but once in place they will reduce your maintenance windows, ensure solid ongoing performance, and in turn, reduce your own management burdens.

LEARN NBN TRICKS AND TRAPS WITH FREE NBN SURVIVAL GUIDE

Did you know: Key business communication services may not work on the NBN?

Would your office survive without a phone, fax or email?

Avoid disruption and despair for your business.

Learn the NBN tricks and traps with your FREE 10-page NBN Business Survival Guide

The NBN Business Survival Guide answers your key questions:

· When can I get NBN?
· Will my business phones work?
· Will fax & EFTPOS be affected?
· How much will NBN cost?
· When should I start preparing?

DOWNLOAD NOW!

David M Williams

joomla site stats

David has been computing since 1984 where he instantly gravitated to the family Commodore 64. He completed a Bachelor of Computer Science degree from 1990 to 1992, commencing full-time employment as a systems analyst at the end of that year. Within two years, he returned to his alma mater, the University of Newcastle, as a UNIX systems manager. This was a crucial time for UNIX at the University with the advent of the World-Wide-Web and the decline of VMS. David moved on to a brief stint in consulting, before returning to the University as IT Manager in 1998. In 2001, he joined an international software company as Asia-Pacific troubleshooter, specialising in AIX, HP/UX, Solaris and database systems. Settling down in Newcastle, David then found niche roles delivering hard-core tech to the recruitment industry and presently is the Chief Information Officer for a national resources company where he particularly specialises in mergers and acquisitions and enterprise applications.