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.

Friday, 06 January 2017 21:37

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 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.

WEBINAR event: IT Alerting Best Practices 27 MAY 2PM AEST

LogicMonitor, the cloud-based IT infrastructure monitoring and intelligence platform, is hosting an online event at 2PM on May 27th aimed at educating IT administrators, managers and leaders about IT and network alerts.

This free webinar will share best practices for setting network alerts, negating alert fatigue, optimising an alerting strategy and proactive monitoring.

The event will start at 2pm AEST. Topics will include:

- Setting alert routing and thresholds

- Avoiding alert and email overload

- Learning from missed alerts

- Managing downtime effectively

The webinar will run for approximately one hour. Recordings will be made available to anyone who registers but cannot make the live event.



Security requirements such as confidentiality, integrity and authentication have become mandatory in most industries.

Data encryption methods previously used only by military and intelligence services have become common practice in all data transfer networks across all platforms, in all industries where information is sensitive and vital (financial and government institutions, critical infrastructure, data centres, and service providers).

Get the full details on Layer-1 encryption solutions straight from PacketLight’s optical networks experts.

This white paper titled, “When 1% of the Light Equals 100% of the Information” is a must read for anyone within the fiber optics, cybersecurity or related industry sectors.

To access click Download here.


David M Williams

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. David subsequently worked as a UNIX Systems Manager, Asia-Pacific technical specialist for an international software company, Business Analyst, IT Manager, and other roles. David has been the Chief Information Officer for national public companies since 2007, delivering IT knowledge and business acumen, seeking to transform the industries within which he works. David is also involved in the user group community, the Australian Computer Society technical advisory boards, and education.



Recent Comments