Post reply

yikes

  • July 15, 2013 at 8:05 am

    #295544

    Have a prod server with 3rd party app which is buried this morning. SQL CPU at 95% no blocking or deadlocking getting lots of suspended sql statements. no long running queries

  • July 15, 2013 at 4:22 pm

    #1632699

    Could be lots of things. For instance excessive compilation because of non-parameterised statements.

    What does "SELECT @@version" report?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • July 16, 2013 at 1:31 am

    #1632780

    Those suspended queries are suspended waiting for what?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 16, 2013 at 8:14 am

    #1632884

    MAXDOP issue?

    Cheers

  • July 16, 2013 at 8:24 am

    #1632888

    no maxdop issue, ran sql against all DMV's looking for anything even have Idera DM running against it, issue went away not to be found

  • July 16, 2013 at 8:58 am

    #1632901

    Have a look at sys.dm_os_waiting_tasks as a starting point, should give you some more information.

    Andrew

  • July 16, 2013 at 9:11 am

    #1632908

    did was just the normal stuff, it was like a quick crash, there was some network IO packets backed up checking into that

  • December 31, 2013 at 9:45 am

    #1676621

    General things to check on SQL 2008 when you are hit with a massive performance whack like that...

    1. Is your ARITHABORT for the database set to ON? If not, follow your procedures to get production change approval and set it to on, and then run DBCC FREEPROCCACHE. This is a known SQL bug that produces these very symptoms.

    2. Is there SAN I/O bus contention? If no SAN, then question answered. However, look for the IO Bus Contention issue regardless of IO subsystem.

    3. Check the missing indexes DM view. Pinal Dave has a wonderful query to do this at http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/. It will even generate CREATE INDEX statements (need to be checked for proper column cardinality) to use.

    4. See if end users are running "query of death" ad hoc queries on your production machine. This happens a lot.

    This is a suggested list of ways to get past unexplained hits to performance. Also, if you don't have a good monitoring tool, GET ONE! 😀

    Thanks

    John

Viewing 8 posts - 1 through 7 (of 7 total)

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