How can I get the waits for each database?

  • Hello all,
    I need to create a report that gets me the waits, time and percentage at database level for a period of time, but all I can find is stats at server level
    any idea on how can I get that data?

    Thanks in adavance

  • ricardo_chicas - Thursday, February 23, 2017 1:56 PM

    Hello all,
    I need to create a report that gets me the waits, time and percentage at database level for a period of time, but all I can find is stats at server level
    any idea on how can I get that data?

    Thanks in adavance

    I'm not even sure how that would be defined. Waits are only captured at the server level and in SQL Server 2016 also the session level. You could potentially get some information by session but that still wouldn't satisfy that request since since it's not by database, threads wait and not sessions and sessions can have multiple threads and those threads could potentially wait on various resources. What if I'm in master and use three part naming to execute a query that references a few different databases. Where is the wait? And what if I issue a distributed transaction and wait on DTC? Does that tell you anything about the database where I executed that transaction? And what if it's something running in parallel? There are a lot of things along those lines. And your on 2014 anyway. Maybe the report and whatever information is desired needs to be redefined. 

    Sue

  • Azure SQL Database has a DMV sys.dm_db_wait_stats, which is absolutely wonderful. It will show you wait stats per database. However, the boxed product doesn't have that DMV. The one way you can get close would be to set up extended event sessions, filtered by database, and capture waits in that way. I don't think it's going to be the most effective mechanism though.

    "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

  • Grant Fritchey - Friday, February 24, 2017 5:59 AM

    Azure SQL Database has a DMV sys.dm_db_wait_stats, which is absolutely wonderful. It will show you wait stats per database. However, the boxed product doesn't have that DMV. The one way you can get close would be to set up extended event sessions, filtered by database, and capture waits in that way. I don't think it's going to be the most effective mechanism though.

    Thank you Grant
    I know there is a way since we have a third party tool that is doing something like that, but I need the data in a different way....
    It sounds like too much effort just to get a different visualization from the one I have already, I will just think on a different approach then

    thanks again

  • ricardo_chicas - Friday, February 24, 2017 6:13 AM

    Grant Fritchey - Friday, February 24, 2017 5:59 AM

    Azure SQL Database has a DMV sys.dm_db_wait_stats, which is absolutely wonderful. It will show you wait stats per database. However, the boxed product doesn't have that DMV. The one way you can get close would be to set up extended event sessions, filtered by database, and capture waits in that way. I don't think it's going to be the most effective mechanism though.

    Thank you Grant
    I know there is a way since we have a third party tool that is doing something like that, but I need the data in a different way....
    It sounds like too much effort just to get a different visualization from the one I have already, I will just think on a different approach then

    thanks again

    You can also repeatedly query the DMVs to see waits per database. But it does require a bit of thrash on your system. I know that's how some monitoring tools get it done.

    "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

  • I use view:
    sys.dm_db_index_operational_stats
    for that type of analysis / review on a given table/index.
    Be aware, though, of the potential volatility of this data, based on the quote below from BOL.
    Since, however, you're looking for the entire db level, rather than an individual table/index, I think this view should you exactly what you need.

    "                                                     

    How the Counters in the Metadata Cache Are Reset

    The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, sys.db_db_index_usage_stats.The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available. Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. Other DDL operations against the index may cause the value of the statistics to be reset to zero.

    "

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 6 posts - 1 through 5 (of 5 total)

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