XPHRTIMER.DLL - SQL Server High Resolution Timer

version: 1.0.0.3

last updated: 15 April 2005

platform: MSDE 2000, SQL Server 2000 running Windows 2000 and higher

Content:

Description

Usage

Parameters

Return codes

Installation

Uninstall

Version and platform support

Known limitations

FAQ

Troubleshooting

License

Planned functionality enhancements

Realized functionality enhancements

Change log

Description:

XPHRTIMER increases the accuracy of the internal timer mechanism used by SQL Server.

 

This is achieved by re-routing the existing Win32 GetTickCount() function to an alternative implementation using the Detours technology from Microsoft Research, to an implementation which is based on the Win32 QueryPerformanceCounter() function. The main reason for doing this, is because the OS kernel only refreshes the tick count every ~10 milliseconds, the interval depends on the underlying hardware platform, so despite the fact that GetTickcount() offers a millisecond resolution, effectively you can only measure buckets of ~10 milliseconds.

 

This becomes apparent when you start looking at the results returned by SQL Server, where it uses the GetTickcount() function to calculated elapsed times, wait times or durations; there are three places where GetTickcount() is used extensively:

  • SQL Trace - Duration column, did you ever wonder why most reported durations were of value either 0 (zero) or larger then 10 milliseconds?

  • [waittime] column in [master].[dbo].[sysprocesses]

  • dbcc sqlperf(waitstats) [wait time] and [signal wait time] columns

The alternative implementation based on the Win32 QueryPerformanceCounter() function, has a nanosecond resolution; the nanoseconds are calculated back to milliseconds, to assure the same output and that we will not overflow the existing implementation which can only deal with milliseconds. But there is one huge difference; you will have a much more accurate timer implementation.

 

If you will run SQL Trace/Profiler with the high resolution timer turned on, you will find that the Duration column provides much fine-grained results then when you are running with the high resolution timer replacement off.

 

•

Usage:

XPHRTIMER supports 3 commands:

  1. xp_hrtimer_start, reroutes calls to the existing GetTickcount() function to the alternative implementation based on QueryPerformanceCounter().

    Note: The start command is idempotent, it can be called multiple times; from the same or different connections, without any side effects.
     

  2. xp_hrtimer_stop, the stop command reverts the redirection of GetTickcount(), so that SQL Server uses the original function again.
     

  3. xp_hrtimer_status, the status command returns the current status, return code

-- ************************************************************************
-- retrieve the current state of the high resolution timer

-- ************************************************************************
 

declare @rc     int,
        @state  int

select  @rc     = 0,

        @state  = 0

exec    @rc = master.dbo.xp_hrtimer_status @state OUTPUT
select  rc = @rc,

        status = case @state

            when 0 then 'hrtimer not active'

            when 1 then 'hrtimer active'

            else        'hrtimer in unknown state'

        end
go

 

-- ************************************************************************
-- start the high resolution time

-- ************************************************************************
 

exec    @rc = master.dbo.xp_hrtimer_start
select  rc = @rc
go


-- ************************************************************************
-- stop the high resolution time

-- ************************************************************************
 

exec    @rc = master.dbo.xp_hrtimer_stop
select  rc = @rc
go

Parameters:

 

This is the complete list off parameters used per XP.

 

xp_hrtimer_start

 

NameData types allowedDefault valueMandatoryValue rangesDescription
@rcintn/an/a0 = success
1 = failure		return code

 

xp_hrtimer_start

 

NameData types allowedDefault valueMandatoryValue rangesDescription
@rcintn/an/a0 = success
1 = failure		return code

 

xp_hrtimer_status

 

NameData types allowedDefault valueMandatoryValue rangesDescription
@rcintn/an/a0 = success
1 = failure		return code
@statusintn/ayes, output0 = inactive
1 = active		state of the high resolution time

 

 

Return codes:

The procedure returns only two return codes, 0 (zero) indicating successful execution, 1 indicating failure always accompanied with an error message.

 

This is how to retrieve the return code:

 

declare @rc int

exec @rc = master.dbo.xp_hrtimer_start ...

select @rc

Installation:

To install XPHRTIMER follow these instructions:

  1. For SQL Server 2000, download  xphrtimer80-v1.0.0.3.zip and unzip the files
     

  2. Copy XPHRTIMER80.DLL and DETOURED.DLL into the SQL Server BINN directory.

    Note: For SQL Server 2000 the default location is "C:\Program Files\Microsoft SQL Server\MSSQL\Binn"
     

  3. Register the extended stored procedures  by executing the xphrtimer.sql scripts that is contained in the distribution file using OSQL.EXE or SQL Query Analyzer

Note: Only the members of the sysadmin role can execution the high resolution timer procedures.

Uninstall:

To remove XPHRTIMER from a system follow these instructions:

  1. Force the DLL out of memory, using OSQL.EXE or SQL Query Analyzer by executing:

    dbcc xphrtimer80('free') with no_infomsgs
     

  2. Unregistered the XP from the system, using OSQL or SQL Query Analyzer by executing:

    if ((object_id('xp_hrtimer_start') is not null) and
        (objectproperty(object_id('xp_hrtimer_start'), N'IsExtendedProc') = 1))
        exec sp_dropextendedproc 'xp_hrtimer_start'

    if ((object_id('xp_hrtimer_stop') is not null) and
        (objectproperty(object_id('xp_hrtimer_stop'), N'IsExtendedProc') = 1))
        exec sp_dropextendedproc 'xp_hrtimer_stop'

    if ((object_id('xp_hrtimer_status') is not null) and
        (objectproperty(object_id('xp_hrtimer_status'), N'IsExtendedProc') = 1))
        exec sp_dropextendedproc 'xp_hrtimer_status'
     

  3. Delete the XPHRTIMER80.DLL and DETOURED.DLL from the SQL Server BINN directory.

Version and platform support:

XPHRTIMER is tested and supported on:

  • SQL Server 2000 (using service pack 3, 3a and 4), running on Windows 2000 and Windows XP, Windows Server 2003

XPHRTIMER is supported on:

  • SQL Server 2000 (using service pack 3, 3a and 4), running on Windows 2000 and Windows XP, Windows Server 2003

  • MSDE 2000 (using service pack 3, 3a and 4), running on Windows 2000 and Windows XP, Windows Server 2003

XPHRTIMER is NOT supported or tested in any way or form on:

  • SQL Server 7.0 (all versions)

  • SQL Server 7.0 for Alpha processors (all versions)

  • MSDE 1.0 (all versions)

  • MSDE 2000 running on Windows 95, Windows 98, Windows 98 Second Edition or Windows Me

  • SQL Server 2000 running on Windows 95, Windows 98, Windows 98 Second Edition or Windows Me

  • SQL Server 2000 64-bit (Itanium)

Known limitations:

  • All error messages are in English and do not reflect the client side locale ID

  • ...

FAQ:

  • Q: ...
     

Troubleshooting:

  • None so far

License

XPHRTIMER - Copyright © SQLDev.Net 1991-2005 (http://SQLDev.Net)

All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

  1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

  2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

  3. Neither the name of SQLDev.Net nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

  4. Binaries, source code and any other parts of this distribution may not be incorporated into any software licensed under the terms of the GNU General Public License (GPL) or the GNU Lesser Public License (LGPL). Binaries, source code and any other parts of this distribution
    may not be incorporated into any software licensed under any license requiring source code disclosure of derivative works.

  5. Modified redistributions of source code, binaries and/or documentation must carry the above copyright as required by clauses (1) and (2) and may retain the name "SQLDev.Net" in source code, documentation and metadata.

  6. The name "SQLDev.Net" is a trademark of SQLDev.Net B.V. the Netherlands.

    THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
     

Planned functionality enhancements:

 

The next release will contain the following feature (subject to change):

  • None so far

Realized functionality enhancements:

  • Changed to 3 procedure entry points instead of 1 with parameter switches
    Added configurable sysadmin role verification
    Added in version 1.0.0.3

Change log:

VersionDateDescription
1.0.0.32005-05-15Release to web
1.0.0.22005-02-01First external release
1.0.0.12005-01-31Created

Questions or problems regarding this web site should be directed to Web Master.
Copyright © 1991-2005 SQLDev.Net. All rights reserved.
Last modified: 04/06/05.