Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Gama and Naughter

Implementation

In an ideal world we could just use the Win32 SDK function GetSystemTimes to get the CPU usage but this function is only available on Windows Server 2003 or Windows XP SP1. Alternatively, we could use the performance counters in the registry or the PDH (Performance Data Helper) wrapper API for performance counters, but as an example and also to reduce the amount of code we require, lets use the semi-undocumented function "NtQuerySystemInformation". This function retrieves various kinds of system information, it has four parameters: the first parameter is a value from a SYSTEM_INFORMATION_CLASS enum, to specify the type of the requested information. The second parameter is a buffer to store the information and the third is the buffer's length. Finally, the fourth parameter is optional and it returns a pointer to where the actual size of the returned information is stored.

The DLL exposes 1 XP using the following XP++ code:

IMPLEMENT_XP(XP_CPUUSAGE, CCPUUsageExtendedStoredProcedure)

There are five main steps for using "NtQuerySystemInformation", the first step is to get the function pointer for "NtQuerySystemInformation":

LPNTQUERYSYSTEMINFORMATION pNtQuerySystemInformation = (LPNTQUERYSYSTEMINFORMATION) GetProcAddress(GetModuleHandle(_T("NTDLL")), "NtQuerySystemInformation");

The second step is to get the idle times on start:

SYSTEM_PROCESSOR_PERFORMANCE_INFORMATION StartSPI;
NTSTATUS status = pNtQuerySystemInformation(SystemProcessorPerformanceInformation, &StartSPI, sizeof(StartSPI), NULL);

The third step is to sleep for the specified interval (specified by an input parameter). CPU usage is a rate and it can only be interpreted properly if measured across a reasonable time interval. A reasonable value to pick would be 500 or 1000 milliseconds.

DBINT lVal = XP_INT4(m_pParameterData[CPUUSAGE_TIME_INTERVAL_INDEX].m_Data);
Sleep(lVal);

The fourth step is to get the idle times on end:

SYSTEM_PROCESSOR_PERFORMANCE_INFORMATION EndSPI;
status = pNtQuerySystemInformation(SystemProcessorPerformanceInformation, &EndSPI, sizeof(EndSPI), NULL);
if (status != NO_ERROR)
{
sprintf(szErrorMsg, "%s: An error occured calling NtQuerySystemInformation, Error:%x", m_szFunctionName, status);
SendErrorMsg(szErrorMsg);
return FALSE;
}

The fifth, and last step is to do the calculation:

__int64 TotalIdleAmount = ((__int64) (EndSPI.IdleTime.QuadPart) - (__int64) (StartSPI.IdleTime.QuadPart));
__int64 TotalKernelAmount = ((__int64) (EndSPI.KernelTime.QuadPart) - (__int64) (StartSPI.KernelTime.QuadPart));
__int64 TotalUserAmount = ((__int64) (EndSPI.UserTime.QuadPart) - (__int64) (StartSPI.UserTime.QuadPart));
int nCPUUsage = (int) (100 - ((TotalIdleAmount * 100) / (TotalKernelAmount + TotalUserAmount)));


The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm  

   

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter