Browse by Tags

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

I was speaking with some folks yesterday about tuning a system, and they asked me about my methodology. As time goes on, I find myself doing things differently based on the situation, but there is a general approach I use that I'll explain here. This isn't a hard-and-fast rule; it's just a guideline...

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

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

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

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

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

I show this little-known feature in just about every perf tuning class I give, and it’s often a surprise to most of the folks there. It’s not earth-shattering or anything, but I do find it helpful. In SQL Server Management Studio 2008, start the new Activity Monitor. Open one of the “Bands”, such as...

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

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

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

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

I got a question yesterday in the mail that I thought I would just answer here in a broad context. While I can’t troubleshoot or do performance tuning from a distance, there are some interesting concepts and suggestions this e-mail brings up: “I have recently seen a change from SQL Server from 2005 to...

Yesterday I posted a header from a script that shows you some links for how to use “Logman”, a Windows command-line utility that can automate your collection of Windows Performance Monitor counters. Towards the bottom, you can see the logman command I use, and you can see that it points to a counter...

I held a workshop on performance tuning in Portland, Oregon yesterday, and as part of that I mentioned a process I follow for automatically collecting Performance Monitor Counters for a system. I use this data to store a baseline and then subsequently to have a reference set of data to compare with....

I’m leading a Performance Tuning workshop here in Portland, Oregon today, and I normally start this kind of workshop with a segment on “pre-tuning” – or, the steps you take so that you don’t have to tune as much later. When we set up a system is the greatest opportunity for optimal performance. From...

This morning I showed how I find DMVs, and find out how to use them. First, I just run this query:   USE MASTER; GO SELECT ‘sys.’ + name FROM sys.sysobjects WHERE name LIKE ‘dm%’   Then I copy and paste a result into the “Index” panel of Books Online. I also use a web search to learn more....

I do a lot of performance analysis on SQL Server Instances, and I normally start with a series of Performance Counters from both Windows and SQL Server. This gives me the ability to limit what I need to look at by seeing which Hardware and Windows components (and SQL Server-specific  features, such...

I’ll be speaking at two of the “SQL Saturday” Events in the next month. The first is here in my own back-yard, in Redmond, Washington. I’ll be demonstrating the Resource Governor with simple, easy-to-follow examples, and I’ll even show you how to monitor its activity. That one is on the 3rd of October...

In a previous blog entry , I mentioned you could import a Windows Perfmon log into SQL Server Profiler. I received a note that someone was having a problem doing this, and when we looked over his situation, it turned out that he was using a Windows Perfmon binary log, a SQL Server 2008 trace file - and...

In SQL Server 2005 a great new feature was introduced into SQL Server Profiler – the ability to import Perfmon data. If you’re not familiar with one or both of those tools, SQL Server Profiler is a package included with SQL Server that can “watch” your system’s activities. You add these activities into...

SQL Server uses something called the "Query Optimizer" to find the data you're looking for. To do that, there is another mechanism, called "Statistics", which can be created and maintained on columns in a table - even tables that don't have an Index. You can read more about Statistics here , and in...

In a previous "SQL Server Best Practice" post , I made reference to file layouts and where various file types should go. I got a comment there that I felt deserved another post. The question was: "...7 individual drives? If this is correct do you also recommend drive redundancy like raid 1 or...

I had a great time in Portland, Oregon on Saturday, where I spoke on using the new features in SQL Server 2008 for Performance Tuning. We covered the new Performance Tracking and Tuning tools in the product, but I focused more on the process for performance tuning. It's fairly simple to locate books...

If you could only change one thing to make a database system faster, what would that be? More memory, faster drives? Better queries, more indexes? Less indexes? Well, I'd spend my one "database wish" here: Don't get what you don't need. I'm constantly amazed at the code I look at when someone brings...