September 29, 2009 at 3:17 am
Hi,
I want to gather below SQL Server Performance data without using Performance Monitor, I want to do it using SQL Server DMVs and simple queries to system tables.
Is it possible?
Server Utilization
>CPU Usage
>Total % CPU Usage
>% CPU SQLServer Process
>% CPU SQLServer Agent Process
>Context Switches / Second
Disk Usage
>%Free Space
>Free Megabytes / GB
Disk Performance
>Average Disk Queue Length
>Current Disk Queue Length
>Disk Read and Write Latency
>Disk Read and Write / Second
Memory Usage
>Memory: Page Reads/Sec
>Memory: Pages Writes / Sec
>Memory: Pages / Sec
>Memory: Pool Nonpaged Bytes
>Memory: Pool Paged Bytes
Server Health Check
>SQL Agent
>Failed SQL Agent Jobs
SQL Server Backups
>Failed SQL Backups
SQL Server Databases
>Database Free Space Alert
>Database Unused Space Alert (More than 75%)
>Database Heath Alerts (Single_user, Restricted, DBO Use or Read_Only)
>Transaction Log Free Space Alerts
SQL Server Replication
>Replication Agent
>Agent Status ( Log Reader, Distributer...)
Distributor
>Delivered Commands / Sec
>Delivered Transaction / Sec
Log Reader
>Delivered Commands / Sec
>Delivered Transactions / Sec
Merge
>Conflicts /Sec
>Downloaded Chnages / Second
>Uploaded Chnages / Sec
Snapshot
>Delivered Commands / Sec
>Delivered Transactions / Sec
>SQL Server Performance
>Active Transactions
>Average Wait Time / ms
>DBCC Logical Scan Bytes / Sec
>Full Scans / Sec
>Lock Blocks
>Lock Timeouts / Sec
>Log Cache Reads / Sec
>Log Truncations
>Logins / Sec
>Failed Logins / Sec / Minute
>Memory Grants Pending
>Mixed Page Allocataions / Sec
>Page Writes /Sec
>Pages Allocated / Sec
DeadLock
>DeadLock / Sec / Min
ErrorLog
>ErrorLog Alerts ( Severity More than 17 or 18)
Thanks,
Raj
September 29, 2009 at 4:03 am
yes..you can use the DMV sys.dm_os_performance_counters.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
September 29, 2009 at 4:53 am
Try... sp_monitor
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
September 29, 2009 at 9:42 pm
Thanks a lot. For the Help!!!
Regards,
Raj
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply