DBA

save the following scripts into one and run it during performance issue, you will have an overall view of the server wait events, cpu load and what sql is running. 1. to clear dm_os_wait_stats   DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR) 2. use the following query to get the delta of 2 seconds of os wait statistics. select wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms into #originalwaitstatsnapshot from sys.dm_os_wait_stats  waitfor delay '00:00:02'  select wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms into #latestwaitstatsnapshot from sys.dm_os_wait_stats  select l.wait_type,(l.wait_time_ms-o.wait_time_ms) accum_wait_ms from #originalwaitstatsnapshot o inner join #latestwaitstatsnapshot l on o.wait_type=l.wait_type  where l.wait_time_ms > o.wait_time_ms order by accum_wait_ms desc 3. Query to find out which statement is running (stmt_end = -1), and cpu status (is_idle =0, runnable_tasks

DECLARE @Handle binary(100) SELECT @Handle =0x020000002AA01D147A952286D9A2BE3ECA9B96FC9D588946 select * from ::fn_get_sql(@handle)

found this blog is helpful to get CPU and IO statistics on oracle database. http://shob-dbadmin.blogspot.ca/2012/12/how-to-find-total-io-of-database.html courtesy to  Shomil Bansal , below are hist writing, not mine. How to find total IO of the database instance Total IO of database instance is sum of the physical reads, physical writes and redo writes. There are several views to find these values. v$sysmetric  - Reports metric values for only the most current time sample 60 secs. v$sysmetric_summary  - Reports metric values for time sample of 1 hour. v$sysmetric_history  - Reports metric values every 60 sec from the time instance is up. Better way to analyse IO using this view to take deltas between two time periods. dba_hist_sysmetric_history  - All the above views are refreshed when the instance is restarted. This view, part of AWR, stores the historical stats. I have used this view for my report. Query: ====== set lines 350 pages 50 feedback off set markup html

found this article. http://www.mssqltips.com/sql-server-tip-category/81/PowerShell/ http://www.mssqltips.com/sqlservertip/2911/using-powershell-to-access-event-logs-for-sql-server/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20130917 substract commands: about logs: Get-EventLog -list Get-EventLog -LogName "application" | Where-Object {$_.source -like "*SQL*"} | select-Object Index,entrytype, message Get-EventLog -LogName "application" -Message "*error*" -After "April 01, 2013 4:52:31 PM" | Where-Object {$_.source -like "*SQL*"} | format-list TimeGenerated, Source, Message Get-EventLog -LogName "system" -newest 20 | export-csv c:\tools\top_20_events.csv  Get-EventLog -LogName "system" | Where-Object {$_.source -like "*SQL*"} | select-Object Index,entrytype, message | out-file C:\tools\sql_server_logs.txt $begintime = Get-Date 10/31/2