• It could be any number of things but from the information you provided so far it sounds like you may be experiencing intermittent blocking. Select count(*) with no where-clause means the entire table will require a scan. There's really no way to make that go faster. If anything else is running that has an exclusive lock on any part of the table needed by the scan then the count(*) query will be forced to wait.

    INF: Understanding and resolving SQL Server blocking problems

    How many rows are in the table? Start looking at what else is going on in the database when the query starts taking a long time. Forget Activity Monitor. Start getting familiar with the DMVs

    From SQL Server: Transaction Management

    -- Look for blocking

    SELECT tl.resource_type , tl.resource_database_id , tl.resource_associated_entity_id , tl.request_mode , tl.request_session_id , wt.blocking_session_id , wt.wait_type , wt.wait_duration_ms

    FROM sys.dm_tran_locks AS tl INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address

    ORDER BY wait_duration_ms DESC ;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato