I have spent some time trying to figure out the way to set query timeout in mssql and its not easy. Below you can see the process i am using now, unfortunately it does not work with the current PHP mssql extension (to make it work you will need to apply my patch or get latest sources).

Why do you need the query timeouts?

In high availability sites you can not allow any pages to timeout. If there is a problem with the DB (index broken or server overloaded) You still want your customers to see the page on time. It may have banners missing or some footer broken but its still better than waiting 30s and getting a blank page. After 2-3 seconds most users will click refresh button (putting the last nail in your coffin).

Using connection and query timeouts will allow you to say "i'm willing to wait 2 seconds for that query and if it does not come back in that time, just forget it". Timeouts allow you to enforce responsiveness.

The thing to remember is that using timeouts may cause DB calls to fail more often so you have to make sure you handle them gracefully in your PHP code.

How to set query timeouts?

This is the logic:

• DB request from PHP application (with optional connection and query timeouts)
• Get values for default_socket_timeout, mssql.connect_timeout, mssql.timeout and store them in a variable
• Set connection and query timeouts using the above
• If there is no connection then try to connect to the DB
• Execute the query
• If there was no result and error code says 'Changed database context to'... then its probably a timeout. For some reason this is the way freetds library (under mssql extension) reports the query timeout.
• To make sure you did get timeout you should acquire microtime before and after the call. In case of the error compare the times. If difference is more than the defined timeout then you treat it as timeout. Otherwise it may actually be a warning from the server or some other issue. Communication with mssql is not perfect in PHP and some errors may be ignored or misunderstood by the module.
• Restore timeouts from before the call

The problem with the described solution is that PHP mssql extension checks timeout setting only while connecting to the server. That way no matter what timeouts you set for your queries they will not apply and only the initial timeout will work.

There is hope as the mssql extension manager accepted my patch : )

The underlying freetds library (currently in lenny debian) supports changing query timeouts for existing connections. So all you have to do is modify php_mssql.c file and edit

PHP_FUNCTION(mssql_query) to add

dbsettime(MS_SQL_G(timeout));

This seems to solve the problem and you can change query timeouts on the fly. It may sound insignificant but when you think of it you will realize it is a important feature. Especially in PHP applications where responsiveness is critical.

Test script showing the basic idea:

        $dbhost = "x";
        $dbuser = "x";
        $dbpass = "x";
        $dbname = "x";

        ini_set('default_socket_timeout',   1);
        ini_set('mssql.connect_timeout',    1);
        ini_set('mssql.timeout',            3);

        // connect
        $connection = mssql_connect($dbhost, $dbuser, $dbpass);
        mssql_select_db($dbname, $connection);

        // call sproc with results and no delay
        $start = microtime(true);
        $rid = mssql_query("exec sprocname;");
        while ($rec = mssql_fetch_assoc($rid)) {
                $row[] = $rec;
        }
        echo "=".print_r( $row, true )."= in".(microtime(true)-$start)." with ".mssql_get_last_message()."\n";

        // set new timeouts - they are ignored as connection is already there!!
        ini_set('default_socket_timeout',   1);
        ini_set('mssql.connect_timeout',    1);
        ini_set('mssql.timeout',            6);

        // call 8s delayed sproc
        $start = microtime(true);
        $rid = mssql_query("exec sprocname;");
        while ($rec = mssql_fetch_assoc($rid)) {
                $row[] = $rec;
        }
        echo "=".print_r( $row, true )."= in".(microtime(true)-$start)." with ".mssql_get_last_message()."\n";

Summary

I hope this helps you in some way.

You can download mssql extension code from svn now and compile yourself to have full support of connection and query timeouts (if your freetds is up to date).

If you dont want to go through all the trouble in a few months fix should be already in latest extension build as its status may be tracked through this link php extension patch page

Good luck!