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

Read the New TPC Database Benchmarking Series

KKline — Mon, 22 Apr 2013 18:17:00 GMT

Let's talk about database application benchmarking.

This is a skill set which, in my opinion, is one of the major differentiators between a journeyman-level DBA and a true master of the trade. In this article published in my monthly column at Database Trends & Applications magazine, I'll give you a brief introduction to TPC benchmarks and, in future articles, I'll be telling you how to extract specific pieces of valuable information from the published benchmark results.

But let's get started with an overview … read more.

Many thanks,

-Kevin

- Follow me on Twitter!
- Google Author

Quick Tip - Speed a Slow Restore from the Transaction Log

KKline — Wed, 14 Nov 2012 15:59:00 GMT

Here's a quick tip for you:

During some restore operations on Microsoft SQL Server, the transaction log redo step might be taking an unusually long time. Depending somewhat on the version and edition of SQL Server you've installed, you may be able to increase performance by tinkering with the readahead performance for the redo operations. To do this, you should use the MAXTRANSFERSIZE parameter of the RESTORE statement. For example, if you set MAXTRANSFERSIZE=1048576, it'll use 1MB buffers.

If you change the MAXTRANSFERSIZE, keep an eye on the PerfMon objects for Buffer Manager and Readahead IO. You may also wish to keep an eye on LOGBUFFER wait stats.

I'd love to hear your feedback. Have you tried this technique? Did it work as advertised? Did it require some changes to work on a specific version or edition?

Many thanks,

-Kev

-Follow me on Twitter!

The Zombie PerfMon Counter That Never Dies! Quick Tip

KKline — Mon, 08 Oct 2012 11:55:00 GMT

The PerfMon Counters That Just Won't Die

One of the things that's simultaneously great and horrible about the Internet is that once something gets posted out in the ether, it basically never goes away. (Some day, politicians will realize this. We can easily fact check their consistency). Because of longevity of content posted to the Internet, a lot of performance tuning topics become "zombies". We shoot 'em in dead, but they keep coming back!

In other words, those old recommendations were a suggested best practices for long ago, for a specific version of SQL Server, but are now inappropriately for the newer version. It's not uncommon for me, when speaking at a conference, to encounter someone who's still clinging to settings and techniques which haven't been good practice since the days of SQL Server 2000. Here's an example of Microsoft SQL Server 2000 Best Practices that are very version-specific.

So here's an example. The %Disk Time counter and the Disk Queue Length were heavily recommended as a key performance indicator for IO performance. SQL Server throws a lot of IO at the disks using scatter/gather to maximize the utilization of the disk-based IO subsystem. This approach leads to short bursts of long queue depths during checkpoints and readaheads for an instance of SQL Server. Sometimes the server workload is such that your disk can't keep up with the IO shoved at it and when that happens, you'll see long queue lengths too. The short burst scenario isn't a problem. The lengthening queue length scenario usually is a problem. So is that a good practice?

In a word, not-so-much.

Those counters can still be of some use on an instance of SQL Server which only has one hard disk drive. But that's exceedingly rare these days. Why?

The PerfMon counter %Disk time is a bogus performance metric for several reasons. It does not take into account asynchronous I/O requests. It can't tell what the real performance profile is for an underlying RAID set may be, since they contain multiple disk drives. The PerfMon counter Disk Queue Length is also mostly useless, except on SQL Server's with a single physical disk, because the hard disk controller cache obfuscates how many IO operations are actually pending on the queue or not. In fact, some hard disks even have tiny write caches as well, which further muddies the water was to whether the IO is truly queued, in a cache somewhere between the OS and the disk, or has finally made it all the way to the CMOS on the disk.

Better IO PerfMon Counters

Instead of using those PerfMon counters, use the Ave Disk Reads /sec, Avg Disk Write /sec, and Avg Disk Transfers/sec to track the performance of disk subsystems. These counters track the average number of read IOs, write IOs, and combined read and write IOs to occured in the last second. Occassionally, I like to track the same metrics by volume of data rather than the rate of IO operations. So, to get that data, you may wish to give these volume-specific PerfMon counters a try: Avg Disk Transfer Bytes/sec, Ave Disk Read Bytes /sec, and Avg Disk Write Bytes/sec

For SQL Server IO Performance, Use Dynamic Management Views (DMV)

And unless you've been living in a cave, you should make sure to use SQL Server's Dynamic Management Views (DMVs) to check on IO performance for recent versions of SQL Server. Some of my favorite DMV's for IO include:

Sys.dm_os_wait_stats
Sys.dm_os_waiting_tasks
Sys.dm_os_performance_counters
Sys.dm_io_virtual_file_stats
Sys.dm_io_pending_io_requests
Sys.dm_db_index_operational_stats
Sys.dm_db_index_usage_stats

Many of these DMVs are fully document in this Books Online article here at Microsoft SQL Server 2012 Index Related Dynamic Management Views and Functions.

So how are you tracking IO performance metrics? Which ones are you using?

I look forward to hearing back from you!

Enjoy,

-Kev

-Follow me on Twitter!

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!

SQL Server Performance Tuning and Optimization in Jacksonville, FL on April 27th

KKline — Mon, 23 Apr 2012 13:15:00 GMT

One last reminder - If you're interested in learning more about troubleshooting and optimizing SQL Server performance, please consider coming to the full-day seminar I'll be giving this Friday. Full details are here: http://sqlperftuning.eventbrite.com/. We'll be working some exercises, so bring your laptop with an instance of SQL Server 2008 (or later) running on it. It'll be fun!

I also encourage you to come on down to SQL Saturday 130 the following day - whether there's an official registration for you or not. (But don't tell 'em I said that). :^)

Come by the booth for a visit. I'll be hanging out most of the day and have some nice swag to give away.

Enjoy,

-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

Available Online - SQL Server Training Videos

KKline — Thu, 02 Jun 2011 03:37:00 GMT

Interested in my various performance tuning and troubleshooting videos about SQL Server? These videos feature my good buddies (and uber-SQL Server experts) Brent Ozar (blog | twitter) and Buck Woody (blog | twitter). View these insight-packed training videos from our SQL Server training events originally presented on March 3 and July 21 of 2010:HERE.

You can watch the videos now at the URL above or order a DVD, shipped free of charge in the USA and Canada, HERE.

Enjoy!

-Kev

~~~

Twitter at kekline

New on SQLMag.Com: Update to SP_WHOISACTIVE

KKline — Thu, 21 Apr 2011 14:12:00 GMT

I profiled Adam Machanic's (blog | twitter) excellent stored procedure, SP_WHOISACTIVE, back in August of 2010 in my monthly SQLMag column, Tool Time. Adam has been diligent about maintaining the tool and adding new features. Read the details on my SQLMag Tool Time column (here - http://www.sqlmag.com/blogs/tool-time/entryid/76341/new-release-of-sp_whoisactive).

Enjoy!

-Kev

Twitter at kekline

Reminder: Totally Awesome and Totally Free Training SQL Server Training

KKline — Mon, 21 Feb 2011 13:45:00 GMT

One of the things that I enjoy about working for Quest Software is that we give back copiously to the community. From activities and offerings like SQLServerPedia, to our free posters mailed anywhere in North America (and don't forget the free hi-res PDFs for the rest of the world),

Don't forget that free DVDs of our virtual conferences featuring me, along with Buck Woody (blog | twitter) and Brent Ozar (blog | twitter) will be mailed anywhere in North America free of charge, now available at this link.

Enjoy!

-Kevin

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