February 23, 2017 at 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
February 23, 2017 at 4:27 pm
ricardo_chicas - Thursday, February 23, 2017 1:56 PMHello 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
February 24, 2017 at 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.
"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
February 24, 2017 at 6:13 am
Grant Fritchey - Friday, February 24, 2017 5:59 AMAzure 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
February 24, 2017 at 6:43 am
ricardo_chicas - Friday, February 24, 2017 6:13 AMGrant Fritchey - Friday, February 24, 2017 5:59 AMAzure 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 thenthanks 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
February 24, 2017 at 9:32 am
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.
"
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply