Search results matching tags 'Performance', 'Troubleshooting', 'Automation', and 'Administration'

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!

Everybody Needs a Test Harness

KKline — Mon, 31 Oct 2011 14:17:00 GMT

When you're developing new Transact-SQL code or modifying some existing code, do you just launch directly into programming?

I know that I did just that, for years. It wasn't until I was trying to performance tune some existing code that I realized I hadn't actually taken caching of data and execution plans into account. So all those modified stored procedures that I was so proud of might not actually be faster than the first generation of procedures because I hadn't checked to ensure that I was testing cached programs against uncached programs (and, by extension, the data used by those programs). That's easy enough to fix with a test harness. Test harness were originally an actual, physical harness used by engineers to clamp down parts of an electrical or mechanical device they were prototyping. Ours is no different. It locks down all of the assumptions about our code (like my early, false assumption that I didn't need to clear the caches) and adds a metric or two for good measure - literally - so we can better measure what's happening in that code.

Here's what my test harness looks like:

/* Transact-SQL test harness by Kevin Kline, http://KevinEKline.com, Twitter at kekline */

/* Flush dirty pages from the buffer to the database files. */

CHECKPOINT;

/* Flush the data cache and procedure cache, respectively. For DEV environments only! */

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE;

/* Enable statistics tracking for IO and timings. Remember, SET commands remain enabled during a session until disabled. */

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

-- Whatever SQL code you'd like to process goes below.

SELECT SalesOrderID

FROM Sales.SalesOrderHeader H

WHERE CustomerID = 344

GO

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

/* Textual Execution Plans, if desired.

SET SHOWPLAN_TEXT ON;

SET SHOWPLAN_TEXT OFF;

*/

I also like to include the execution plans a lot of the time. You might wonder why I don't save the execution plans for the GUI in SSMS? Well, I'm a big advocate of scripting in general because I like to automate activities. By pulling the execution plans using scripts, I can use SQLCMD to schedule a large number of query executions during the evening and have the results ready for analysis when I come back into the office in the morning. Workin' smarter, not harder, Baby!

So how does this test harness work for you? Do you use other elements in yours? If so, share your experiences here!

Thanks,

-Kevin

-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