April 18, 2011 at 10:01 am
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!
April 18, 2011 at 11:09 am
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
April 18, 2011 at 11:40 am
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
April 18, 2011 at 5:34 pm
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply