Search results matching tags 'Administration', 'Performance', 'DBA', 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!