Databases have to put up with a lot. I gotta say I have sympathy for them. We expect them to perform like racehorses but we treat them like mules!
One minute we overburden them with tons of data. The next minute we ask them to run as fast and furiously as they can. No sooner have they started than we bring them to a full stop. They have to react to the extremes of being hit with millions of transactions per second or churning through complex analytic questions, mining terabytes of data in milliseconds.
Apart from the odd upgrade we provide little or no care to our SQL Servers. And then we are surprised when they stumble, fall and eventually grind to a halt! If we had the time, we could pore over each database, grooming it to perfection and coaching it to success.
That’s why Dell has taken the diagnostic and monitoring capabilities of Spotlight on SQL Server and supercharged them with the data-crunching power of Microsoft Azure. The result is Spotlight Essentials – monitoring, diagnostics and cloud-driven analytics that give you the insight you need to turn your SQL Server investment into the racehorse we know it can be.
Before you read on, go to www.spotlightessentials.com and download Spotlight on SQL Server Freemium.
How to make a racehorse from a mule
Step One – Identify the Mules
To get started on SpotlightEssentials.com, create an account and download Spotlight on SQL Server Freemium edition. Freemium installs into SQL Server Management Studio and takes only a few minutes to connect your SQL Server instances.
Straight away, you can monitor the main elements of your SQL instance workload, along with wait statistics and blocked processes. And this is where the clever bit happens...
Spotlight uploads your performance data to your Spotlight Essentials account, which is hosted in Microsoft Azure. Now you can retain performance data for much longer periods and start monitoring performance trends, system health and more. The first things to look for are the health indicators.
Step Two – Conduct a fitness test
The Spotlight Essentials Performance Health Check is an excellent way of checking your instance health over time.
After a few uploads you will begin to see a performance pattern emerge. You will be able to see how steady the instance is and how it reacts to workload peaks and troughs.
You can view top SQL queries, drill into each time they ran, how they performed and which query plan they used.
You can also can look at the top three wait stats to tell you where your instance is waiting on resources and drill into each one to gain more insights.
Step Three – Compare to the rest of the mules
Use the Collective Intelligence feature of Spotlight Essentials to benchmark the performance of your instance against thousands of others. If your instance performs in a low percentile, then tuning is required. Collective Intelligence helps you focus your precious time and effort for maximum benefit.
Step Four – Take corrective actions
Look in several places for corrective actions you can take:
- In the performance section, analyze your top SQL and drill into your top waits.
- In the system health section you’ll find a range of checks and balances. Spotlight Essentials prioritizes the top three items to examine.
- See whether your databases are missing indexes, and add them with the provided script.
- Look at memory pressure, ad hoc query workload, and a series of non-performance checks such as security, recovery models and compatibility level settings.
Spotlight Essentials gives you clear explanations and any corrective scripts you may need.
How do we do it?
It’s not easy. Spotlight Essentials manages more than 200 TB of performance data and 7 billion wait statistics, then runs analytics and delivers a benchmark to you in seconds. It takes advantage of multiple Microsoft Azure services including worker roles, the scheduler, HD Insight, SQL Database, Queues, Blobs and Tables.
And, it protects your privacy. All performance data moves through an encrypted connection to your account inside Microsoft Azure, and it’s encrypted when at rest. (For a full rundown on security provisions, go to www.spotlightessentials.com/documentation/security.)
Creating the racehorse – Move up to the big time
Now that you can see the potential, it’s time to up the ante. No more mules – time to turn your databases into racehorses. Treat your SQL Server databases like the true athletes they are.
Spotlight Essentials monitors every movement, tunes your databases to perfection and conducts regular check-ups to spot symptoms early. It profiles and compares each instance against thousands of similar ones to establish health patterns within their demographic. Even when Spotlight Essentials is not there to watch over them, it knows exactly how they are and can react in a split second to figure out what is ailing.
Once you have seen how much insight you gain from Spotlight Freemium, you can download the 30-day trial of Spotlight Enterprise. Enterprise users also get the benefits of monitoring and diagnostics on the move by using Spotlight Essentials to deliver mobile apps for IOS, Android and Windows Mobile. Get a taste of just how far you can push your mule with the power and insight of Spotlight Enterprise.
Watch the webcast, “Adding New Dimensions to SQL Server Performance Monitoring with Microsoft Azure.” Debbi Lyons of Microsoft describes Azure data services, our Patrick O’Keeffe explains how Spotlight Mobile and Health Check work, and yours truly talks about how the combination of Spotlight and Azure makes life easier for DBAs. Spotlight Essentials is a solid use case for developing a data- and compute-intensive application in Azure, and it’s more fruit of the Dell-Microsoft relationship.
Visit SpotlightEssentials.com for more examples of UI, Collective Intelligence and free performance monitoring downloads from Spotlight.