ivanmp's questions

I've got a couple of servers running SQL Server 2005 Enterprise and I'd like to monitor them remotely using Perfmon (Windows' Performance Monitor).

On my machine, I have an instance of SQL Server 2008 R2 Developer, and I noticed that there are a bunch of SQL Server specific performance counters, like MSSQL:Buffer Manager, MSSQL:Databases, MSSQL:Memory Manager, MSSQL:Transactions, MSSQL:Wait Statistics, etc.

If I connect to both servers using Remote Desktop and open Perfmon at the server, I can see all those counters. When I connect to the server from my machine directly via Perfmon (not using remote desktop) and select counters from the remote computer (\SERVERNAME), I can see all kinds of performance counters (like .NET CLR Jit, Networking, Processes, etc.), but not those from SQL Server.

Please note that I'm using a domain login that has admin permissions on the server.

Do I have to enable something on the server that allows me to monitor the SQL Server's specific performance counters? Am I missing something?

According to Mark Russinovich's Windows Internals 5th Edition, Windows uses (small) page sizes of 4 KB on both x86 and x64 architectures to manage memory. Even though I can't find a reason for this, intuitively I'd think SQL Server would use the same size for its own pages but instead it uses page sizes of 8 KB.

I've researched through the documentation and blog posts, but I was unable to find an explanation for this behavior. All I was able to find was that it is the way it is.

I'm definitely not arguing that this is good or bad (and I'm in no position to do so, anyway), I'm just curious and would like to learn the motivation that culminated in this decision.

What is the technical reason for choosing a page size of 8 KB for SQL Server? Why is it a good page size? Or maybe it's just an inheritance from its Sybase lineage?

How does this decision affect SQL Server's performance (meaning how does it interact with the system's memory and disk internally)?

My second thought would be something related to the storage subsystem, but I'm still not sure. (NTFS has a default cluster size of 4KB)

This week I've run into a problem with the MSSEARCH wait type, and I haven't been able to fully diagnose the problem.

The server had been running with no problem for several weeks until the other day, when it suddenly started taking too long for replying to users' requests.

My team and I quickly found the problem to be located at the Full-text Search component, but we had no idea what was causing it. (FTS is a feature heavily used in our workload and we've had no issues with it up until now.)

We tried restarting the MSFTE service, but it wouldn't respond.

As the screenshot above shows, the server was just below 400 Waiting Tasks (normal workload of under 10) and rising.

I didn't have too much time to try and diagnose it before restarting our server because we it was running in production, so I was left only with SQL Server's logs and a couple of MSFTE memory dumps after the full server restart.

I was expecting to be able to have a better understanding of the problem with those, but I wasn't able to acquire much information out of those, so I would be really glad if anyone would offer a pointer or shed some light into this.

All we were able to deduce was that the Full-text Search service had stopped working, but I found no evidence of such a bug on the web, and although it seems to be running ok now, I'd like to really understand what happened and prevent it from happening again.

Thank you.

I have been running a Data Collector Set for SQL Server for a week, and everything was going just fine until I tried clicking on Action -> Save Current View and now it always tries to add the following counters automatically to my reports:

\\MYSERVER\Provedor de Dados .NET para SqlServer(sqllogship[1296])\NumberOfInactiveConnectionPools
\\MYSERVER\Provedor de Dados .NET para SqlServer(sqllogship[1296])\NumberOfNonPooledConnections
\\MYSERVER\Provedor de Dados .NET para SqlServer(sqllogship[1296])\NumberOfPooledConnections
\\MYSERVER\Provedor de Dados .NET para SqlServer(sqllogship[1624])\NumberOfInactiveConnectionPools
\\MYSERVER\Provedor de Dados .NET para SqlServer(sqllogship[1624])\NumberOfNonPooledConnections
\\MYSERVER\Provedor de Dados .NET para SqlServer(sqllogship[1624])\NumberOfPooledConnections
\\MYSERVER\Provedor de Dados .NET para SqlServer(sqllogship[1744])\NumberOfInactiveConnectionPools
\\MYSERVER\Provedor de Dados .NET para SqlServer(sqllogship[1744])\NumberOfNonPooledConnections
\\MYSERVER\Provedor de Dados .NET para SqlServer(sqllogship[1744])\NumberOfPooledConnections
\\MYSERVER\Provedor de Dados .NET para SqlServer(sqllogship[2008])\NumberOfInactiveConnectionPools

And this causes a lot of annoying error messages (one for each counter listed above) saying:

One or more of the selected counters are already present and won't be added again.

How do I prevent those counters from being automatically (re-) added everytime I select a report?

Thanks.