Admin puzzler

  • ok so Friday off I get a call from user doing  posting taking over an hour( should take 5 minutes).  No blocking do deadlock no high CPU.  Active sessions show higher reads than normal.  No open trans.  Go to mgmt. studio and when I try to open objects on main pharmacy database it times out.  Go to query analyzer but when I select the drop down for same database it does not appear.  I can query the database in QA using another database.  We bounce the instance, bounce the server bounce vm host nothing works.  Call Microsoft ( of course my 2 hour promised call back becomes 8).  No errors in windows, or sql of any type.  It is part of an AG with RO node, so I add dummy table it goes to RO node instantly.  FInally after 3.5 hours behavior leaves, no trace of any kind.  Still can find no errors anywhere anyone want to take a guess?

  • Microsoft didn't find anything?

     

    Alex S
  • no help

  • Next time this happens, have a look at the wait_type and/or wait_resource fields in sys.dm_exec_requests.  That may give you a few more clues as to what is going on.  The last_wait_type field may also give clues, but I doubt will be at all conclusive.

  • sp_whoisactive. Sounds like blocking.

  • Wait, still the issue after bouncing the server? Could there be some rollback activity taking place?

     

  • no rollback

     

    One anomaly seems to be the query store, it had flipped to read only (there was plenty of space left on disk but the store was a max level which causes it to flip read only).  Still nothing anyplace in any log of what happened  beginning to think about this as possible, there were no transactions of any type after any of reboots while we tried but I don't think query store shows as open transaction

  • I have seen similar issues when a large data load is processing - or someone is running a process to create/rebuild indexes.  These processes can take out a lock that prevents accessing the objects from object explorer (or direct queries).

    Restarting the instance should clear that process - unless the person running it restarts it immediately after the instance has been restarted.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I have a symptom like this regularly on a server, but it usually completely kills the whole box and can't get anything out of it until restarting the instance. The jobs have implicit SQL in them and one of them has retry logic in it. I wanted to try implementing RCSI on it based on Brent's post yesterday, once we get it up to 2016 or 2017

  • Hi,

    If the behaivour stops after a while, I believe that is a transaction issue. I will should check temp db in order to see if there is plenty of space. If your share your disk space with another applications, then maybe the other applications are slowing down your sql server. Perhaps a full backup or a massiver copy from/to another server. Also, using the kit from Brent Ozar´s are a big help diagnosing performance problems in sql.

    Hope this helps.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply