dm_os_wait_stats for a single database

  • I have a myriad of different databases running on a database instance.

    The wait stats that I'm seeing in production do not match the ones I see in QA. I think the difference is that waits are being generated by the other databases.

    My understanding is that this dm reports waits for all dbs in the instance. Is there a way to list stats for a single db?

    Thanks!

  • There is no way to get wait_stats for a specific database.

    If you are using SQL Server > 2008, you can setup Extended Event session for collecting wait events for a Single database.

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • You can check SQL Server built-in reports for TOP CPU usage and TOP IO usage reports then these type reports can help you to find the correct database for the resource

    select st.text,sp.* from sys.sysprocesses sp

    cross appyl sys.dm_exec_sql_text(sp.sql_handle) st

    where sp.cpu > 100

    this query can help your to find the specific locks,commands,user and database as well

    Regards,

    Syed Jahanzaib Bin Hassan

    MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • If all you're looking at is sys.dm_os_wait_stats then no, you can't narrow that down to a database. But if you start using some of the other DMOs like sys.dm_os_waiting_tasks, you can combine these through the session_id column to bring back all sorts of interesting data on the waits of the system. Yes, it'll be more work to set up, but it is doable if that's how you want to address performance tuning.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

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