Can we Identify the CPU resources used by each sql named instance on a single Server

Posted on 2011-05-13
Last Modified: 2012-06-21
Hello Experts,

I have 2 named instances (SQL Server 2008 SP2) on a single physical server.

Can you please guide me if there is a way to identify amount of CPU resources used by each instance.

I am trying to estimate the CPU usage of each instance to get a baseline for migrating one instance to different server.

Question by:dba2dba
    LVL 11

    Accepted Solution

    Sounds like a job for perfmon.

    I believe they counters you are looking for are in the resourse pool stats, cpu usage % or something similar.

    So for a default instance it would be SQL Server:Resource Pool Stats
    For a named instace it would be mssql$instancename:Resource Pool stats
    LVL 11

    Assisted Solution


    Here is a good link that shows the basics on how to find the high CPU culprit on a per instance basis

    However, you can also run the following command to pull up the intances in the command line below
    (assume the instances are runnning on port 1433:
    netstat -ano | findstr "1433"

    if you stop one of the SQL instances and run the command you can find the "known" spid (THe SPID is off the the far right) of the instance that is still running. Then start the other instance and run the command again. now you see two entries. You also have the SPID of the second instance.

    With this information you can use the task manager to see which instance is using the CPU.  In the task manager, go to the process tab and click the view -seclect colums option. select the PID (process identifuer), CPU usage, and any other counters you wish. -  you can get a rough and tough estimation of the CPU usage, isolated by the PID you have collected from the prevous steps.

    I hope this helps.


    THen start

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now