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.

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.

47 REASONS TO ATTEND YOW! 2018

With 4 keynotes + 33 talks + 10 in-depth workshops from world-class speakers, YOW! is your chance to learn more about the latest software trends, practices and technologies and interact with many of the people who created them.

Speakers this year include Anita Sengupta (Rocket Scientist and Sr. VP Engineering at Hyperloop One), Brendan Gregg (Sr. Performance Architect Netflix), Jessica Kerr (Developer, Speaker, Writer and Lead Engineer at Atomist) and Kent Beck (Author Extreme Programming, Test Driven Development).

YOW! 2018 is a great place to network with the best and brightest software developers in Australia. You’ll be amazed by the great ideas (and perhaps great talent) you’ll take back to the office!

Register now for YOW! Conference

· Sydney 29-30 November
· Brisbane 3-4 December
· Melbourne 6-7 December

Register now for YOW! Workshops

· Sydney 27-28 November
· Melbourne 4-5 December

REGISTER NOW!

LEARN HOW TO REDUCE YOUR RISK OF A CYBER ATTACK

Australia is a cyber espionage hot spot.

As we automate, script and move to the cloud, more and more businesses are reliant on infrastructure that has the high potential to be exposed to risk.

It only takes one awry email to expose an accounts’ payable process, and for cyber attackers to cost a business thousands of dollars.

In the free white paper ‘6 Steps to Improve your Business Cyber Security’ you’ll learn some simple steps you should be taking to prevent devastating and malicious cyber attacks from destroying your business.

Cyber security can no longer be ignored, in this white paper you’ll learn:

· How does business security get breached?
· What can it cost to get it wrong?
· 6 actionable tips

DOWNLOAD NOW!

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.

 

Popular News

 

Telecommunications

 

Sponsored News

 

 

 

 

Connect