Pythian Group

Have you ever used Perfmon to collect performance statistics about a particular Windows Server? Do you hate manually picking the counters out of the list? Do you trace basically the same counters every time?

If you answered “yes” to any of these questions, here’s a tip that can save you some time. Once you’ve created and saved a trace, you can save/export it as an HTML document. When you need to trace a new server, with a few quick Find/Replaces from your favorite text editor, you can be up and tracing in minutes on the new host, without using the interface to pick and choose each of your counter objects.

Here’s how to do create a Perfmon trace template:

1. If you already have a saved trace somewhere, open perfmon on that server, expand Performance Logs and Alerts and click on Counter Logs. If you don’t already have a saved trace, you’ll have to create one before getting started.
2. From the Performance Logs and Alerts screen, right-click on your trace and click on Save Settings As…
3. Save the file somewhere and give it a meaningful name. (The default location is My Documents.)
4. Copy the file somewhere onto your machine for later use.

Congratulations. You’ve created and saved a template. It’s that easy. Now let’s say you’re ready to use it somewhere else.

Read the rest of this entry . . .

I got the idea for this article when one of our clients complained that their server’s performance was degrading during business hours. They thought it was weird that at the same time, SQL Server would list more than 1200 connections on SQL server Activity Monitor.

The server hosts more than 50 databases that serve an ASP.NET application hosted on some servers in a web farm. These servers issue connections to the databases in a distributed manner to balance the web application load. I tried to discover what these connections were doing and to what databases they were connected. Connections grouped by database:

select  db_name(dbid) , count(*) 'connections count'
from master..sysprocesses
where spid > 50 and spid  @@spid
group by  db_name(dbid)
order by count(*) desc

This showed some databases having more than 300 connections associated with them.

What about logins used?

select  loginame , nt_username, count(*) 'Connections count'
from master..sysprocesses
where spid > 50 and spid  @@spid
group by  loginame , nt_username
order by count(*) desc

This showed a mix of windows domain accounts (those with values in column nt_username, e.g: domain\user) beside SQL authentication accounts (those with column nt_username empty, e.g: “sa”).

In order to reduce the number of times that new connections must be opened, applications may use connection pooling. This was clearly not the case here, and all these connections resulted in what is known as “pool fragmentation”.

So what’s connection pooling? how does it work, what can cause pool fragmentation and how can we avoid/reduce it?

Read the rest of this entry . . .