Forum Discussion

MulgraveTester's avatar
MulgraveTester
Frequent Contributor
8 years ago

Get SQL memory usage

Hi, I have been using code to get the memory and CPU usage of processes for some time.

 

VBScript

      set objMyProcess= Sys.WaitProcess("My Process Name",0)

      log.message ("CPU usage = " & objMyProcess.CPUusage)

      log.message("Memory usage = " & objMyProcess.memUsage)

 

Now I want to be able to get the same for SQL but SQL does not appear in the Object Browser. It appears in the Task Manager under Background Processes. I believe the reason why SQL does not appear in the Object Browser is because it is actually a service (not a process).

 

I either need the process handle for SQL Server (SQLEXPRESS) so that I can use the same method as above or I need a similar method to get the resources used by services. Any ideas?

 

Thanks.

  • HKosova's avatar
    HKosova
    8 years ago
    MulgraveTester wrote:

     

     

    Now I want to be able to get the same for SQL but SQL does not appear in the Object Browser. It appears in the Task Manager under Background Processes. I believe the reason why SQL does not appear in the Object Browser is because it is actually a service (not a process).

     

    If it's in Task Manager, it's in Object Browser too. Make sure the buttons to show the system processes are pressed. If the SQL Server is running as admin, make sure to run TestComplete as admin as well.

     

  • HKosova's avatar
    HKosova
    SmartBear Alumni (Retired)
    8 years ago
    MulgraveTester wrote:

     

     

    Now I want to be able to get the same for SQL but SQL does not appear in the Object Browser. It appears in the Task Manager under Background Processes. I believe the reason why SQL does not appear in the Object Browser is because it is actually a service (not a process).

     

    If it's in Task Manager, it's in Object Browser too. Make sure the buttons to show the system processes are pressed. If the SQL Server is running as admin, make sure to run TestComplete as admin as well.

     

    • Colin_McCrae's avatar
      Colin_McCrae
      Community Hero
      8 years ago

      HKosova - it's possible it could be embedded in a SVCHost process. Which can make it hard to spot. Not sure if the TC Object Browser can show you that?

       

      MulgraveTester - in task manager, if you right click on the service, it should give you an option to go to the associated process. (The option will be greyed out if the service is not running) That should tell you if it's a SVCHost process or it's own one. If it is a SVCHost one, I don't know if they always get assigned the same one. If they don't, you'd need to get the PID and use that as there are often multiple SVCHost processes running.

      • HKosova's avatar
        HKosova
        SmartBear Alumni (Retired)
        8 years ago

        Colin_McCrae, SQL Server has its own process. You can add the "Process name" column in the Task Manager to see the process names. Services that live in svchost are labelled "Service Host: <something>".

    • MulgraveTester's avatar
      MulgraveTester
      Frequent Contributor
      8 years ago

      Thanks HKosova, That was it. I had to run TestComplete as administrator to be able to see the process "sqlsevr" in the object browser.

      • MulgraveTester's avatar
        MulgraveTester
        Frequent Contributor
        8 years ago

        I found a way to get the SQL memory usage without having to run TC as an administrator.

         

        VBScript&colon;

         

        function getProcessMB(processName)
        'Returns the number of MB the specified process is consuming in memory
        'Processname: String : e.g. "SQLServr.exe"
        'This method is required instead of TestComplete's built in process.memUsage because TC would have to run as an administrator
          getProcessMB = 0
          Set objWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\localhost\root\cimv2")
          Set objProc = objWMI.ExecQuery("SELECT * FROM Win32_Process WHERE name=""" & processName & """")
          For Each Process In objProc 
           getProcessMB = formatnumber(Process.WorkingSetSize / 1024 / 1024, 0)
          Next
        end function

  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    8 years ago

    You should be able to get the PID back from the Service. Which would allow you to query it's process.

     

    https://msdn.microsoft.com/en-us/library/aa394418(v=vs.85).aspx#properties

     

    Although, SQL processes should be pretty obvious in task manager? Certainly are on my install. But mine isn't SQL Express, which may be different. Never used it. But certainly sqlsrvr (SQL Server) is an obvious process for me. Along with it's gigantic memory footprint! Guess Express must be embedded in a SVCHost process?

     

    And if you want to use a Win32_Service object to query it, here's some info on that: http://www.computerperformance.co.uk/vbscript/wmi_services.htm

     

    I use the above to check, start and stop services during runs. But you need to get from the service to it's process in order to get memory footprint info as far as I can see. The PID should do that. I think. :)

    • MulgraveTester's avatar
      MulgraveTester
      Frequent Contributor
      8 years ago

      Thanks for all your help on this Colin. The screenshot that I attached showed the process in the Task Manager. I wasn't able to see it in the TestComplete Object Browser because I wasn't running TestComplete as an administrator. Once I did that I was able to get the process and its memory usage as before.

       

      Thanks.