Apache is getting SUSPENDED in MSSQL by another process when doing more than 5-6 queries via PHP PDO resulting in timeout

Question

I will try to be as detailed as possible.
Up until now I was only using Stack Overflow and other sites like this but never posting. I was always able to find the answer to my question. But not this time.

I have a Mu Online game server setup and running fine.A part of this server is a program called "JoinServer". When I use the non-md5 version it is fine but I want to use the md5 version of the file.

With the md5 version, I have the following problem:
The server starts, runs and everything's fine until someone connects to game (here queries are being created and MSSQL starts to get loaded). Up untul this point the Apache's doing its queries very fast, no lag and everything runs perfect. But when someone connects (when JoinServer gets into action) the locks start to happen. This is persistent only with the md5-version of the JoinServer.

I mention md5 but not giving details because I think i should say if there's even a slight tiny chance md5 is involved in the whole situation.

The game runs fine but when I call more than 5-6 queries by Apache, Apache process gets locked and has status SUSPENDED inside MSSQL resource monitor.

The question here is: how to prevent Apache suspension?
- is it possible to prioritize a process (Apache) in MSSQL server?
- is it possible to prevent a process to lock another processes (prevent JoinServer to lock/suspend Apache's process)?
- is it possible that my web queries are not well written and they create too much load and MSSQL gets them suspended? Subjectively, they are not too much and they do not generate a great load on the SQL server. When the game server is offline, they run in an instant.
- it is NOT possible to alter game server (JoinServer) queries, I don't have access to its source code

Software used
- Windows Server 2012 R2 (tried on Windows 7 Pro - same)
- Microsoft SQL Server 2008 (tried with 2012 - same)
- PHP 7.3 via xampp 7.3.10
- PHP 7.3 and Apache on Ubuntu (same)
- PDO official Microsoft driver for PHP 7.3

Here's how I make my DB connection:

$dsn = 'sqlsrv:server='.Config::DB_SERVER.';Database='.Config::DB_NAME;
$dsn_web = 'sqlsrv:server='.Config::DB_SERVER.';Database=ANHIWEB';
$options = [
                PDO::ATTR_EMULATE_PREPARES   => false,
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_WARNING,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
                ];

try {

    $db = new PDO($dsn, Config::DB_USER, Config::DB_PASS, $options);
    $webdb = new PDO($dsn_web, Config::DB_USER, Config::DB_PASS, $options);

}

catch (PDOException $e) {
    die(print_r($e->getMessage()));
}

(I'm using 2 databases).

Here's an example of queries being run by PHP via PDO:

//Get online players count
$exec = $db->prepare("SELECT count(memb___id) FROM MEMB_STAT WHERE ConnectStat='1'");
    $exec->execute();
    $result = $exec->fetchAll();
    return @$result[0][''];

//Some rankings
    $exec = $db->prepare("SELECT
    RowNum
      ,C.[AccountID]
      ,[CharacterName]
      ,C.[Class]
      ,C.[cLevel]
      ,C.[Resets]
      ,[Point]
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY C.Class DESC ) AS RowNum, *
          FROM      [EVENT_INFO] as E
          WHERE C.ctlCode=0
        ) AS RowConstrainedResult
         left join Character as C ON CharacterName = Name

WHERE   RowNum >= $start
    AND RowNum < $limit
ORDER BY RowNum");

    $exec->execute();
    $result = $exec->fetchAll();

//Get total accounts
    $exec = $db->prepare("SELECT count(memb_guid) FROM MEMB_INFO");
    $exec->execute();
    $result = $exec->fetchAll();
    return @$result[0][''];

EDIT: I found out that this query is locking, it seems like JoinServer uses the table MEMB_STAT.
It's possible that this is the only query that does lock.

//Get online players
global $db;
    $exec = $db->prepare("SELECT count(memb___id) FROM MEMB_STAT WHERE ConnectStat='1'");
    $exec->execute();
    $result = $exec->fetchAll();
    return @$result[0][''];

Now the question is: how to select that count without causing lock?

I don't do anything to close/clean the connection as I have read online that it's not necessary and the PHP/PDO will manage itself and will be fine.

My efforts:
- Disabled IPv6 in Windows
- Disabled named pipes on SQL server
- Played with Parallelism - no luck
- Tried with Windows 7, everything else same - no luck
- Tried to run Apache on Ubuntu instead on Windows - no luck
- Tried MSSQL 2012 instead 2008 - same

Here are some details I'm able to get on the lock:
- Task state: SUSPENDED
- Command: SELECT - Application: Apache HTTP Server
- Wait time (ms): 115163 (and increasing)
- Wait type: LCK_M_S (I googled this, didn't help)
- Wait resource: keylock hobtid=*** dbid=* id=** and so on
- Locked by ID: 204 (this is the ID of JoinServer)

What else to do? Any suggestions?

Answer 1

Try prepare("SELECT count(memb___id) FROM MEMB_STAT with (nolock) WHERE ConnectStat='1'"); But do lookup the docs on nolock, it has drawbacks but I'd guess for this purpose they are acceptable. https://stackoverflow.com/questions/686724/what-is-with-nolock-in-sql-server