Search results for 'app:weblogs' matching tags 'SQL Server' and 'operating system'

Capture Performance Counter Data and Write to SQL Server

Anonymous — Wed, 27 Jul 2011 05:00:00 GMT

Summary: Learn how to use Windows PowerShell to capture performance counter information and write the saved data to a SQL Server database.

Hey, Scripting Guy! I have been enjoying your PoshMon articles this week, but I have found them to be of limited value. I would love to see how I could store this performance data in a SQL database. I could then use SQL Reporting Services to parse the data and create reports.

—RS

Hello RS,

Microsoft Scripting Guy Ed Wilson here. I am in the middle of a two-week road trip. The engagement with the Central Ohio Windows PowerShell Users group was great, and so was SQLSaturday in Wheeling, West Virginia. I had never been to Wheeling—it is actually a pretty cool place and is relatively near both Columbus, Ohio, and Pittsburgh, Pennsylvania. Now I am in Seattle, Washington, for the internal-to-Microsoft TechReady conference, and it has been a great experience. I have gotten to see many of my friends from other locations, so it has been like a reunion in one respect. I have also learned a lot both in terms of perceived customer needs and technical aspects.

RS, I am going to use the technique from yesterday’s blog post wherein I automatically glean the performance counters from a binary performance trace file. The reason for doing this is that it greatly simplifies the task of specifying the performance counters. For that post, I carefully selected a nice collection of counters by using the graphical selector tool in the Performance Monitor tool, and I do not want to repeat that task. The original code is shown here:

$counters = Import-Counter -Path "C:\Users\edwils\Desktop\HSG-New\System Monitor Log.blg"

$paths = $counters[0].countersamples | % {$_.path}

Get-Counter -Counter $paths

For a more permanent solution, it would be trivial to export the counter paths to a text file. You could then use the Get-Content cmdlet to read the text file and populate the $counterPath variable when needed. This would save needing to parse the .blg file on a repetitive basis. The code to do this is shown here:

$paths | Out-File -FilePath c:\fso\ExportPaths.txt -Encoding ascii -Append

In fact, I like the idea of writing the counters to a data file. I decided to modify the original code to use the Tee-Object to send the output to both a variable and a text file at the same time. This is not something that Tee-Object is normally used for, but because Tee-Object will output to the screen if it is the last command in the pipeline, I save the output to a variable. This gives me both a file and a variable at the same time. The code to write to both a variable and a file is shown here (the % symbol is an alias for the ForEach-Object cmdlet):

$paths = $counters[0].countersamples | % {$_.path} | Tee-Object -FilePath c:\fso\testpaths.txt

The advantage of using Tee-Object is the code is still three lines long, but I now get both the variable populated and the text file created all at once. The modified code is shown here:

$counters = Import-Counter -Path "C:\Users\edwils\Desktop\HSG-New\System Monitor Log.blg"

$paths = $counters[0].countersamples | % {$_.path} | Tee-Object -FilePath c:\fso\counterpaths.txt

Get-Counter -Counter $paths

The text file that contains the counter paths is shown here.

After I have my counters in a text file, I can use the Get-Content cmdlet to read the counter text file for the counter paths. I specify 20 samples and a sample interval of 6 seconds. This will give me 20 readings over a two-minute period. When I have completed gathering my data, I use the Export-Counter cmdlet to export my performance data to a CSV file. It is much better to use this cmdlet than to attempt manually creating the CSV data.

These four lines of code are really two logical lines of code. I use the line continuation character at the end of the first and third lines to break the code to the next lines to make the code easier to read on the blog. The code to read counter paths from a text file, pass the paths to the Get-Counter cmdlet to retrieve 20 samples at 6-second intervals, store the results in a variable, and export the data to a CSV file is shown here:

$counterData = Get-Counter -Counter (Get-Content C:\fso\counterPaths.txt) `

-MaxSamples 20 -SampleInterval 6

Export-Counter -Path c:\fso\counterData.csv -FileFormat csv `

-InputObject $counterData

The script and associated output (there is no output) are shown here in a screen shot of the Windows PowerShell ISE.

The data that is collected outputs to a CSV file. By default, CSV files associate with Microsoft Excel (if installed), and the CSV file created by running the script and exporting the counter information are shown in the following figure.

After I have verified that I have captured the data and have it in an acceptable format, I am ready to import it into a database. One note: if anything changes from the .blg file to the counter path file to the time when you run your script to capture data, you might receive an error while running the script. The error might be associated with an “invalid” counter path. This might happen, for example, if you were running this on a laptop and the counter is supposed to use a wired network connection, and later you were on a wireless network connection and disabled the wired network adapter. All the other data would be captured; only the invalid Perfmon counters would be mentioned in the error message and no data would be captured for those instances. A sampling of such errors is shown in the following figure.

To import my newly created CSV file into my database, I am going to use the SQL Server Import and Export Wizard. I created my database earlier by using Microsoft SQL Server Management Studio, but I could have used the SQLPSX cmdlets from CodePlex. I am using SQL Server 2008 R2 Express Edition, which is a free download. One disadvantage of using the Express Edition is that I cannot save my import package.

After I have chosen my data source, selected the file, and specified that the column names are in the first data row, I go to the next page where I can view the way the data will be imported. If it does not look acceptable, I can go back and make advanced changes. In this case, however, everything looks fine, as is shown in the following figure.

Now I need to choose which database I am going to use. I am going to use my PoshMon database on my SQL Server Express Edition, and connect via Windows Authentication. This is shown in the following figure.

Now I need to map the data to a particular table. I am going to wimp out and allow the wizard to automatically create a new table for me. The table will be called counterData, which was the name from the spreadsheet. The table mapping is shown in the following figure.

It is time to click a few more times and allow the import to run. The results appear on the final screen. If a problem occurs, a report will be available under the Message column. The successful conclusion page is shown here.

After the import has completed, I like to run a query from inside the SQL Server Management Studio. I can right-click the newly created table and choose Select Top 1000 Rows from the shortcut menu. The generated query and associated data are shown in the following figure.

That is it for querying performance counters and writing them to a SQL database. It can be lots of fun. Download SQL Express today, and give it a whirl.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

Use PowerShell to Monitor Your SQL Server Performance

Anonymous — Mon, 25 Jul 2011 05:00:00 GMT

Summary: Guest Blogger Kevin Kline talks about using Windows PowerShell to monitor SQL Server performance.

Hey, Scripting Guy! I am wondering about using Windows PowerShell to work with SQL performance counters. Is this something that can be done?

—SH

Hello SH,

Microsoft Scripting Guy Ed Wilson here. We have a new guest blogger today. Kevin Kline is the technical strategy manager for SQL Server Solutions at Quest Software, a leading provider of award-winning tools for database management and application monitoring.

Kevin is a founding board member and former president of the international Professional Association for SQL Server (PASS) and frequently contributes to database technology magazines, websites, and discussion forums. Kevin was the recipient of the PASS 2009 Lifetime Achievement Award.

Kevin’s most popular book is SQL in a Nutshell (now in its third edition) published by O’Reilly Media. Kevin is also author or co-author of seven other IT books, including Transact-SQL Programming.

Tuning SQL Server: Windows PowerShell + PerfMon <> 2P. It’s = P²!

Performance tuning is one of the most rewarding and interesting parts of the IT pro’s job. Don’t like performance tuning? Then you’re missing out on an activity that provides endless variety, opportunity for innovation, and a chance to show the boss that you rock.

So what’s keeping you from jumping into performance tuning? Whenever I talk to customers—in every environment from small four-person IT shops to huge Fortune 50 enterprises—about why they don’t spend more time doing performance tuning, it usually comes down to two obstacles:

The tyranny of the urgent: Boiled down to its essence, IT pros have too many fires to put out to actually get on top of performance. The analogy I like to use is, “When you barely have enough time to bail the water out of your sail boat to keep it from sinking, you certainly won’t have time to adjust the sails for better speed.”
Skills: Or as my buddy Jermaine says “mad skilz.” Simply put, IT pros have so much to do and so little time to do it that they don’t have time to learn how to do one more thing. Our brains are full and learning something new not only requires a big investment in brain power, it will very likely lead to more responsibility without removing any of our other responsibilities. So not only do we not have the time to learn performance tuning, we don’t have the time for all of the follow-up responsibilities we will have to commit to in the future. The analogy I like to use is, “Ignorance is bliss, or at least less painful than not being ignorant.”

But there’s a tradeoff that PowerShell plus Performance Monitor (PerfMon, also known as System Monitor and, most recently, Windows Reliability and Performance Monitor) offers you to mitigate the obstacles mentioned above.

First, and in answer to the tyranny of the urgent, when you get a handle on performance tuning, urgent issues crop up less often—a lot less often. When you don’t know how to collect and interpret performance issues, every snag seems like an emergency. But many times, those red flags turn out to be red herrings instead. Instead of going into emergency firefighter mode every now and then, you’re forced to put on your fire hat every time the phone rings. Too much stress! Second, and in response to acquiring mad skilz, Scripting Guy Ed Wilson and I have teamed up to make performance tuning a “no-skilz required” activity. Why learn a whole new set of skills when you can learn just 20 percent of the content but get 80 percent of the productivity boost of being an expert? Invoke the power of the 80/20 rule by reusing these lessons freely.

What’s our objective?

We’re going to teach you how to:

Perform a quick health check of SQL Server.
Find the best performance counters for SQL Server performance tuning.
Develop robust monitoring solutions.

Once you’ve followed our guidance here, performance tuning on SQL Server is no longer a black art. That’s because you’ll be able to shine the bright light of good instrumentation on your Windows and SQL Server instances.

Step 1: Perform a quick health check of SQL Server

Knowing which PerfMon counters to keep an eye on is half the battle. Maybe even more than half the battle. I’m going to start off by teaching you the handful of PerfMon counters, as well as their usage and values, that enable you to perform a quick health check of Windows and SQL Server.

A little later in the article, I’ll also show you where you can get a huge best practices collection for SQL Server PerfMon counters if you’re interested in looking at more than a handful of counters or are ready to go to the next step of hardcore performance tuning.

As a practical recommendation from a long-time SQL Server expert, SQL Server usually encounters problems in IO, memory, and then CPU (in order of commonality). The reason that SQL Server consumes an inordinate amount of IO, memory, and CPU is usually because of poor-quality SQL code, poor database design, or inadequate hardware (in order of commonality). Notice that poor code and poor design are usually the problem. So that means that if you usually fix performance issues by throwing more hardware at the problem, you’ll eventually end where you started—with an underperforming application. This is because poor code and poor design will always use more resources than they have available.

So here’s your quick list of PerfMon counters to determine SQL Server health relating to IO, memory, and CPU. These three sections are analogous to every visit to the medical clinic that starts with pulse, blood pressure, and temperature checks. If one of these is out of whack, something is definitely wrong. We haven’t necessarily diagnosed an illness, but we know there’s definitely a problem.

When assessing the top level of IO health, latency is your best quick health check. Latency means the amount of time measured between the initiation of an IO operation and its completion. Though these numbers can vary widely due to all sorts of variations in the underlying storage systems, here’s a good place to start:

Physical Disk: Average Disk/sec Reads

Physical Disk: Average Disk/sec Write

The lower these values, the better. Microsoft recommends that a well-tuned IO subsystem should deliver IOs at 5 milliseconds or below on the disks holding the transaction log files and at 20 milliseconds or less on the disks holding the data files. (The transaction logs and database files are on separate disks, right?) In real world systems, I’ve seen applications perform well when reads and writes average a bit higher than their white paper recommended values, but these are great rules of thumb. Numbers a lot higher than the recommendations means that the IO subsystem is under stress.

Memory

A lot of IT pros new to SQL Server get quite alarmed when they see that SQL Server is gobbling up all of a server’s available memory. Not to worry—that’s by design. By default, SQL Server is configured to grab all of a server’s available physical memory (even if it doesn’t immediately use it all), and then give it back to other Windows processes whenever they ask for it. That way, SQL Server can optimize large blocks of memory for queries and major processes, such as big reporting jobs and backup processes.

When doing a quick check of SQL Server memory, I like to corroborate the findings by checking more than one indicator. In this case, these two counters provide an excellent quick indicator of memory pressure inside SQL Server:

SQL Server: Memory Manager >> Free List Stalls/sec

This counter monitors the number of requests per second where data requests stall because no buffers are available. Any value greater than 2 indicates that SQL Server needs more memory:

SQL Server: Memory Manager >> Memory Grants Pending

This counter shows the total number of processes per second waiting for a workspace memory grant. Numbers greater than 0 indicate a lack of memory.

CPU

It’s not very common for SQL Server, when running OLTP workloads, to use up major amounts of CPU. Even a busy SQL Server will usually use between 25-45 percent of CPU when responding to a transaction heavy workload. SQL Server will use more CPU for BI applications, but even then it usually consumes an added 20-30 percent of CPU. To find out the total amount of CPU being used and the proportion of that being used by SQL Server, check these counters:

Processor(_total): % Processor Time

This is the percentage of elapsed time the processor spends executing work (in other words, nonidle threads). On a box dedicated to SQL Server, I raise a red flag if this is frequently above 80 percent:

Process (sqlservr): % Processor Time

This shows the percentage of processor time spent exclusively on SQL Server process threads. Combining this value with the Processor:% Processor Time value will show you conclusively the overall CPU utilization of the server overall compared to how much SQL Server is using.

As I pointed out earlier in the memory section, it’s sometimes very useful to corroborate a finding with overmeasures to ensure you have a clear picture of the issue. Any quick and easy check of CPU pressure to add to the mix follows:

System: Processor Queue Length

It’s sometimes a bit of hassle trying to figure out CPU utilization based on percentages when you factor in multiple cores, hyperthreading, and virtualization. So it’s often valuable not to check a metric based on percentages, but on the raw number of threads waiting for access to that resource. In this case, the processor queue length is a great resource because it represents the number of threads waiting for CPU: anything about 12 is a red flag; values between 9 to 12 per CPU are good or fair; 5–8 is better; and 4 or less is best.

Step 2: Find the best performance counters for SQL Server performance tuning

In the previous section, I’ve pointed out a couple PerfMon counters that quickly assess the health of your SQL Server’s IO, memory, and CPU. These quick checks basically show whether your SQL Server is overextended or not. But they don’t reveal the root cause that is contributing to overconsumption of IO, memory, or CPU. For example, you might have memory issues on a SQL Server because stored procedures running on the server are constantly recompiling and are never able to stay in the cache for long. Similarly, a SQL Server might by showing high IO consumption when, at the root of the problem, the transactions running on the server are constantly blocking each other and preventing each other from completing quickly. There are PerfMon counters for that, too.

I’ve spent a lot of time building the ultimate list of SQL Server PerfMon counters and the troubleshooting scenarios when they’re most useful. Rather than run through all of them here, please take a look at my website to take a deeper dive.

Step 3: Develop robust monitoring solutions

Now that you know what to look for, it’s time to use Windows PowerShell to regularly poll your SQL Servers for these PerfMon values. I strongly encourage you to use Windows PowerShell not just for an occasional spot check of these values but to build a regular monitoring solution that runs at a rather frequent polling interval—say, 5 or 15 minutes, saving the data as you go along. That way you can save long-term performance information and look for trends and problem areas in your system.

To get PerfMon information about using Windows PowerShell, we’ll use the Get-Counter cmdlet. Without parameters, this cmdlet retrieves a handy set of summary information about the server. However, we want to retrieve performance information from specific computers. We do that by providing the name of the server along with the path of the counter, as I’ve shown earlier, using the –Computer parameter:

PS > $computer = $ENV:Computername

PS > Get-Counter "\\$computer\processor(_total)\% processor time"

Timestamp CounterSamples

--------- --------------

6/11/2011 11:16:44 AM \\...\processor(_total)\% processor time :

25.4520932356424

Don’t know the path to the performance counter you want? Then use the -ListSet parameter to search for just the right counter or set of counters. To see all counter sets, use an asterisk as the parameter value.

When building your database of monitored values, export the retrieved counter information using the Export-Counter cmdlet to save your data in a format that other tools can read, such as the .blg format used by Windows Performance Monitor. That way you can use the nice graphic tools in Windows Performance Monitor for easy graphic analysis of performance and problems.

I hope you find this quick introduction to SQL Server performance monitor counters and Windows PowerShell to be useful. Please look me up on Twitter and on my blog at http://KevinEKline.com.

Thanks, Kevin, that was exactly what I was looking for.

Ed Wilson, Microsoft Scripting Guy