Turn to Profiler First

How can I use T-SQL to programmatically determine the number of processors that my SQL Server machine has?

The following command will return the information you need:

EXEC master..xp_msver N'ProcessorCount',

However, I'm a big fan of teaching people how to fish instead of giving them a plate of frozen fish sticks, so I can't possibly stop with that simple answer. When a colleague recently asked me this question, I didn't remember the name of the appropriate command. But I know that Enterprise Manager displays a machine's number of processors on the Processor tab of the SQL Server Properties dialog box. I also know that Enterprise Manager gets nearly all of its information by running simple T-SQL commands against the server, and I know that, by running SQL Server Profiler, I can quickly view the commands that Enterprise Manager generates.

I've often used this column to elaborate on the benefits of using Profiler and of learning by watching what SQL Server does, and here's another example that puts that advice into practice. I isolated Enterprise Manager's calls to the xp_msver extended stored procedure in about 3 minutes by using Profiler—the tool I always look to first in situations such as this.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.