Search results matching tags 'Best Practices' and 'Automation'

New White Paper: SQL Server Extended Events and Notifications

KKline — Wed, 25 Apr 2012 13:13:00 GMT

SQL Server comes with a wide array of tools for monitoring your environment. There are logs and traces that provide information when errors occur, but these are often used passively to react to events that have already occurred. There's PerfMon, and Profiler, and loads of Dynamic Management Views to check. But where to look?

As database administrators (DBA), we need to monitor our environments proactively and create solutions as issues arise. In this white paper, we will look at a couple technologies – event notifications and extended events – that can help you achieve these goals. With these two features, we’ll look at the error log and deadlocks, and demonstrate how you can get relevant information delivered as it occurs. We’ll also look at ways that run-time errors can be captured and used to help reduce the amount of time required to investigate issues.

This white paper, written by SQL Server MVP Jason Strate (blog | twitter), is a free download but requires a registration. Download the Extended Events white paper here.

And, as always, I enjoy your feedback. Thanks!

-Kev

Follow me on Twitter!

Pain of the Week/Expert's Perspective: Performance Tuning for Backups and Restores

KKline — Mon, 27 Jun 2011 14:39:00 GMT

First off - the Pain of the Week webcast series has been renamed. It's now known as The Expert's Perspective. Please join us for future webcasts and, if you're interested in speaking, drop me a note to see if we can get you on the roster!

The bigger your databases get, the longer backups take. That doesn't really seem like a huge problem — until disaster strikes and you need to restore your databases as fast as possible.

Join my buddy Brent Ozar (blog | twitter), a Microsoft Certified Master of SQL Server and good friend, as he reveals ways to make these critically important maintenance tasks run faster.

You'll discover:

Why Instant File Initialization is so important for restores
How to use DMVs to check restore progress
How to find the bottleneck while you're backing up or restoring data

Watch the recorded presentation at http://www.quest.com/events/ListDetails.aspx?ContentID=13358!

Enjoy!

-Kev

Follow me on Twitter at kekline

Database Maintenance Scripting Done Right

KKline — Fri, 18 Feb 2011 13:16:00 GMT

I first wrote about useful database maintenance scripts on my SQLBlog account way back in 2008. Hmmm - now that I think about it, I first wrote about my own useful database maintenance scripts in a journal called SQL Server Professional back in the mid-1990's on SQL Server v6.5 or some such. But I digress... Anyway, I pointed out a couple useful sites where you could get some good scripts that would take care of preventative maintenance on your SQL Server, such as index defragmentation, updating statistics, and so forth. One of the script kits came directly from Microsoft's internal database management team. But, alas, they haven't published any updates in quite a while. On the other hand, the other set of scripts came from Ola Hallengren, who has done a great job keeping his scripts up to date. Recently, Ola added support for updating column statistics, both in a generalized update and also updates for only those columns whose statistics have been modified. He's added some other goodies to the latest release, which you can read about here, http://ola.hallengren.com/Versions.html for all details. In addition, I'd like to remind you of the white paper I wrote a while back called Automating DBA Processes, which covers many aspects of database automation and cherry picks some of the best practices from many different thought leaders, such as Ola, Michelle Ufford (blog | @sqlfool) who will be speaking at the 24HOP soon, and Allen White (blog | twitter).

Scripting for the out-of-band attention

Linchi Shea — Sun, 21 Nov 2010 02:53:00 GMT

I have written a lot of throw-away scripts, some of which were written to control the execution of a large number of SQL scripts. The logic of these control scripts is often simple, but their execution can last for a long time (e.g. hours). When writing this type of control scripts, people would generally give some thoughts to improve robustness in case things crap out unexpectedly in the middle of their running.

When a script is long running, chances are people may change their minds after the script has started running, and want to abort it. I might be wrong, but it seems that not much explicit consideration is given to this change-of-mind scenario in scripting-related discussions.

True, if you have spent a lot of time making the control script and the controlled SQL scripts super robust to the extent that you can just Ctrl-C to abort their execution and expect things to be in a good enough state, good for you! But we are talking about throw-away scripts. As much as we want to make them super robust, it may not be cost effective to actually do so. Typically, a throw-away script is written to deal with a scenario that is sufficiently narrow and we know what craps we may have to deal with and/or how we can handle them if the script indeed aborts unexpectedly. However, that does not mean that we should just go ahead with Ctrl-C, well, unless you enjoy dealing with craps that may come out of Ctrl-C. It can be done better.

Let me give a specific example.

Assume that you have data on some gadget items, orders, item stocks, fulfillments, and item returns, and for each month, you have a separate table for items, a separate table for orders, and so on. Since you need to convert all these tables to different schema for an upgrade, you have prepared T-SQL conversion scripts, one for each table. If your data go back for many years, you have a lot of migration scripts to execute for the upgrade. Being a lazy database professional, you write a control script to loop through all the months, and then another loop to go through the tables for that month, executing their respective conversion scripts in these two nested loops.

Now, just when you have started running this control script, you may suddenly realize that maybe you should have divided the tables into several groups, and execute the conversion scripts for each group in parallel. So you want to stop the current execution, and re-arrange your script. Or you just want to stop the execution of the control script for whatever reason that you may fancy.

As said, you can hit Ctrl-C and hope for the best. Or you can give some thought to this requirement when writing your control script, and build the logic into your script so that it responds to external events and exits the script gracefully at a certain desirable point. This can be something extremely simple. For instance, in the control script for the above-mentioned conversion, I may add a simple check upon the entry into each loop to see whether a dummy file can be opened. If yes, the script continues; if not, the script exits. This gives me a means to tell the script, out of band, to abort by simply creating or deleting that dummy file. Alternatively, if you want to be fancier, you can have the script re-check a config file repeatedly at certain points of its execution. By changing the config value, you can alter the script behavior out of band dynamically. The point is that there are many simple ways to signal the control script out of band if you give this potential requirement a little bit of thought in advance.

If you are writing a serious piece of software, what I'm rambling about here goes without saying. Scripting is a different beast; you often have to trade off robustness for 'time-to-market'. I just wouldn't want to see you going overboard in the process, forgetting that a long-running script may behave much more gracefully if it's prepared to respond to the attention out-of-band.