Search results matching tags 'SQL Server' and 'Performance Tuning'

Performance Monitor and Wait Events Presentation with Quest

BuckWoody — Mon, 19 Jul 2010 13:41:53 GMT

Quest Software is a great partner with Microsoft, and I’m honored to be able to present with Brent Ozar, Kevin Kline and Ari Weil at a day-long event that you can attend for free. We’re going to cover many aspects of performance tuning, from waits and queues to the Windows System Monitor, which you might also know as PerfMon or Performance Monitor.

It’s expensive to go to conferences, so when your boss complains that you’ll be gone all day, remind him/her that it’s *free*. (Plus, you can tell them I said it’s OK. )

The Windows Page File and SQL Server

BuckWoody — Tue, 29 Jun 2010 13:48:00 GMT

What is the best “Page File” size for a Windows system running SQL Server? I see this question over and over – and I see people answering it incorrectly all the time.

Note: I’m talking specifically about 64-bit architectures here. The information is different for 32-bit architectures, but I’ll blog about that at another time.

First, let’s start out with defining a few terms. A “Page File” is simply a file on the hard drive that handles situations where the system wants to move (or “page out”) sections of memory. There are several situations that cause this, but the one you’re most concerned about is when the system is out of physical memory. If the system runs out of memory it can “borrow” some storage from the hard drive to release some memory until it needs that data again.

And that’s exceptionally bad. The reason is that hard drives are amazingly slow in comparison with solid-state memory access. So you REALLY slow down a SQL Server when this happens. In fact, if it happens a lot, that’s a sure sign you need to add more physical RAM.

When you install Windows in the server flavors, it takes a couple of defaults that aren’t always best. As a rule, it will try to allocate 1.5 times the amount of physical RAM to the pagefile. If you have a lot of RAM, that really isn’t necessary. In some Windows versions, it also has a “system managed page file size”, meaning it will grow the file as it needs to. I normally choose a different size for the page file, and set both the “top” and “bottom” values to be the same. So what is that size?

Ah – if only it were that simple. As always, the answer is – wait for it - “it depends”. But this time you’re not left in the dark (completely) about how to find that out. It’s a straightforward matter of monitoring a few counters and adjusting the file as needed. Here are the pertinent counters, from a KB article we have for Windows 2003:

Counter threshold	Suggested value
Memory\\Available Bytes	No less than 4 MB
Memory\\Pages Input/sec	No more than 10 pages
Paging File\\% Usage	No more than 70 percent
Paging File\\% Usage Peak	No more than 70 percent
Process\\Page File Bytes Peak	Not applicable

DO NOT take my word for this. Read the full article here to understand what you are looking at - http://support.microsoft.com/kb/889654/en-us.

There’s one more thing to keep in mind. The pagefile can also be used for troubleshooting. If you have the “Full Dump” option set on the system settings in Windows, you will need a higher value – the full 1.5 times in some cases. As always, your mileage may vary, so spend some time in that KB and understand what you’re looking at.

And if you see anyone giving out a hard number for that Page File value, point them here.

Performance-Driven Development

BuckWoody — Tue, 11 May 2010 13:36:00 GMT

I was reading a blog yesterday about the evils of SELECT *. The author pointed out that it's almost always a bad idea to use SELECT * for a query, but in the case of SQL Azure (or any cloud database, for that matter) it's especially bad, since you're paying for each transmission that comes down the line. A very good point indeed.

This got me to thinking - shouldn't we treat ALL programming that way? In other words, wouldn't it make sense to pretend that we are paying for every chunk of data - a little less for a bit, a lot more for a BLOB or VARCHAR(MAX), that sort of thing? In effect, we really are paying for that. Which led me to the thought of Performance-Driven Development, or the act of programming with the goal of having the fastest code from the very outset. This isn't an original title, since a quick Bing-search shows me a couple of offerings from Forrester and a professional in Israel who already used that title, but the general idea I'm thinking of is assigning a "cost" to each code round-trip, be it network, storage, trip time and other variables, and then rewarding the developers that come up with the fastest code.

I wonder what kind of throughput and round-trip times you could get if your developers were paid on a scale of how fast the application performed...

PowerShell Version Two – Get Continuous Perf Counters

BuckWoody — Thu, 25 Feb 2010 14:32:52 GMT

In version 2.0 of PowerShell, you can now use a direct command-let (get-Counter) to get at the Performance Monitor counters. For instance, to show the current value of the Processor Percent Time, use this command:

Get-Counter '\Processor(*)\% Processor Time'

The interesting part of get-Counter is that you can add a parameter at the end to keep sampling:

Get-Counter '\Processor(*)\% Processor Time' - Continuous

Which is very useful if you’re doing performance troubleshooting like I am this morning. And yes, you can use this to get at SQL Server counters as well – to see the entire set of counters, use this command:

Get-Counter –listSet * | Select-Object -ExpandProperty Paths

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

The SQL Server Health Check

BuckWoody — Thu, 11 Feb 2010 14:16:05 GMT

My friend Brent Ozar, who is a top-notch SQL Server Professional, mentioned on his blog (http://www.brentozar.com/archive/2007/04/sql-server-health-check/comment-page-1/#comment-17520) that he brought in Microsoft Support to do a “Health Check”. There were some questions about what this actually entails – so I thought I would post that description here.

A SQL Server Health Check from Microsoft is an offering provided through our support and consulting branches. If you’ve purchased a Premier support agreement, you can use the hours you pay for to have someone come out and check out your systems using two basic vectors: standard best practices, and the best practices for your environment. Let me explain that a little further.

Microsoft Consulting Services (MCS) has an advanced set of tools and techniques to reach deep into your systems to evaluate them against a set of criteria that we establish for the best performance, safety and reliability for SQL Server. But your SQL Server Instances will be different than someone else’s, so the consultant will also spend some time in an interview with various teams to find out how you’re using the system. They’ll consult with experts in that area back at Redmond, and at the end of the engagement you get what I feel is the most valuable part of the exercise – the report. The report shows what was done, how it was done, what the findings are and what recommendations the consultant makes. It’s a thing of beauty.

You don’t have to have a Premier agreement to get a SQL Server Health Check, and companies other than Microsoft have versions of this as well. The prices vary, so if you want a Microsoft person then contact your local Microsoft office and ask for the Consulting Manager for your area. They will get you to the right person and you can work out the costs from there. If you take a hard look, most of the time the cost is well justified.

On a related note, I think it’s fantastic that Brent has the foresight to bring in “another pair of eyes” on his systems. Brent is actually in the program to gain a SQL Server “Master” certification – something only a few people in the world have. He works for a software development firm that creates fantastic products for – wait for it – SQL Server! He could evaluate his own systems, and of course he does. But he realizes that there’s always something more to learn, and someone out there may know one more little detail that he doesn’t. This is the mark of a very bright person.

Tracking SQL Server Time

BuckWoody — Mon, 25 Jan 2010 14:10:28 GMT

In the past few blog posts I’ve showed you how to use several methods to track things in SQL Server. You can use the “tags” to the right of this post here at this site to list things like PowerShell, Performance Tuning and so on. Now that you’re armed with these tools, what should you track?

Well, one of the items I track is time. I track the time it takes for lots of things, but they fall into three general buckets:

Queries – Normally I track the five longest running queries with their query plans.
Maintenance – From how long each backup takes to index reorgs and rebuilds, I want to know how long these things take.
Jobs – Most all of us have SQL Server Agent Jobs, and developing a schedule of how long they are running is very useful.

For each of these, I track the minimum, maximum and average times. I look for outliers – things that suddenly change and so on.

There are a lot of uses for this information. From performance tuning to developing a recovery plan, all of these actions need to be taken into account.

I think I’ll write up an article sometime on how I do this – it’s a little long for a blog post.

Performance Tuning Methodology Preferences

BuckWoody — Wed, 20 Jan 2010 12:22:00 GMT

I’m holding Performance Tuning Workshops this week for my clients in Washington and Idaho. During that workshop, I explain that there are several ways to examine the SQL Server component of an application. There’s “Hardware Component Pressure Measurement”, “Code Optimization” and “Index Analysis” as starting points, and most folks start there and then move on to use all of them in a mix. But a newer methodology called ‘Wait State Analysis” helps you to evaluate what the system is waiting on, so that you can knock those down one at a time.

I mention in the presentations that there is a new whitepaper out on that, and I promised to share that link here. So without further ado, here it is: http://msdn.microsoft.com/en-us/library/cc966413.aspx (link is at the bottom).

Performance Counters? There’s a script for that.

BuckWoody — Tue, 05 Jan 2010 14:02:15 GMT

It’s not hard to get Performance Monitor counters in PowerShell 1.0, and it’s REALLY easy in 2.0. This is one I used yesterday to monitor network bytes in and out on 2.0 (keep all of the green lines in this post on the same line) :

get-counter -Counter "\Network Interface(*)\Bytes Total/sec" -SampleInterval 1 -MaxSamples 10

The –SampleInterval is in seconds, and the –MaxSamples sets how many you want. The –Counter parameter is the object and counter you want to monitor. Don’t know what they are for your system? No problem:

get-counter -ListSet *

Need to hit another computer? Easy Peasy – doesn’t even require remoting to be turned on (but you do need to have the rights to hit the perf counters):

get-counter -ComputerName COLLOSSUS -Counter "\Network Interface(*)\Bytes Total/sec" -SampleInterval 1 -MaxSamples 10

If your machine happens not to be named “COLLOSSUS”, just change that part J. And yes, you can use whatever counters you like, including SQL Server locks, blocks, etc. The full reference for get-counter is here: http://technet.microsoft.com/en-us/library/dd367892.aspx

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or heartburn during the operation of this script, see a physician.

Measure Statement Performance with PowerShell

BuckWoody — Wed, 30 Dec 2009 14:31:37 GMT

When you monitor for performance, you might start in the database server itself. But a true test really has more to do with the “round trip” of a data request from a client to a server and then the return of data back to the client.

So I wired up this little test to simulate that process, and now I can run that from anywhere. The trick is the measure-Command { } part, which brings back the time display. In my example, I’m testing the backup process, but your command can be anything you like. I’m also using the ExecuteNonQuery call, since I don’t want any data back. You might want to use the ExecuteReader call if you do want data.

If you want to try this (and remember, test system only, read and understand, etc.), you need to change the SERVERNAME\INSTANCENAME to your system and instance names, and change the:

$sqlCommand.CommandText="BACKUP DATABASE TESTDATABASE TO DISK='c:\TEMP\TESTDATABASE.bak' WITH INIT" line to some command you want to run.

I’ve alternated the colors – keep all of one color on one line:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = "server=SERVERNAME\INSTANCENAME;integrated security=true;database=master"

$sqlConnection.Open()

$sqlCommand = new-object System.Data.SqlClient.SqlCommand

$sqlCommand.CommandText="BACKUP DATABASE TESTDATABASE TO DISK='c:\TEMP\TESTDATABASE.bak' WITH INIT"

$sqlCommand.Connection=$sqlConnection

Measure-command {$sqlCommand.ExecuteNonQuery()}

$sqlConnection.Close()

Monitoring SQL Azure For Performance

BuckWoody — Tue, 22 Dec 2009 14:33:20 GMT

In SQL Server Azure, there are no Dynamic Management Views (DMVs) or Performance Monitor Objects and Counters that you can access, so you can’t run your standard performance monitoring that way. I suspect that as time goes on, SQL Azure will have some instrumentation, but for the time being, you’ll have to go with a different metric – round trip throughput.

What I mean by that is you’ll need to measure the start of a transaction and it’s completion. In the end, this is the only metric that matters anyway, but it is helpful to know what to fix – and this metric doesn’t help you with that.

What’s been working for me is to develop my queries locally using all my tricks and tools, and then post those up to SQL Azure. While it’s not a one-to-one map, it does seem to fit the bill for now. I’ll keep you posted.