March 1, 2016 20:37 by
PeterIn this topic, I will show you how to collect value of performance counters from query analyzer. Microsoft® SQL Server SQL Query Analyzer is a graphical tool that allows you to:
- Create queries and other SQL scripts and execute them against SQL Server databases. (Query window)
- Quickly create commonly used database objects from predefined scripts. (Templates)
- Quickly copy existing database objects. (Object Browser scripting feature)
- Execute stored procedures without knowing the parameters. (Object Browser procedure execution feature)
- Debug stored procedures. (T-SQL Debugger)
- Debug query performance problems. (Show Execution Plan, Show Server Trace, Show Client Statistics, Index Tuning Wizard)
- Locate objects within databases (object search feature), or view and work with objects. (Object Browser)
- Quickly insert, update, or delete rows in a table. (Open Table window)
- Create keyboard shortcuts for frequently used queries. (custom query shortcuts feature)
- Add frequently used commands to the Tools menu. (customized Tools menu feature)
The following code is demo query to do same. You can create collect any counter vale for instant. Now write the code:
/*************************************************/
USE Master
GO
Create Table Perfmon (Object_name Varchar (200),
Counter_name varchar (300) , Instance_name varchar (100),
cntr_value bigint, cntr_type bigint , date varchar (20))
GO
Insert into Perfmon
SELECT object_name , counter_name , instance_name , cntr_value, cntr_type
, convert (varchar (20),getdate () , 120) as date
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME in
(
'MSSQL$Servername:Databases' ,
'MSSQL$Servername:General Statistics',
'MSSQL$Servername:Buffer Manager' ,
'MSSQL$Servername:Locks'
)
AND counter_name in
(
'Transactions/sec' ,'User Connections','Page life expectancy',
'Buffer cache hit ratio', 'Buffer cache hit ratio base' ,
'Free pages','Total pages','Target pages','Lock Wait Time (ms)'
)
Select @@servername
GO
Select *, case
when cntr_type = 65792 Then 'instant_value'
when cntr_type = 272696576 Then 'Incremental'
When cntr_type = 1073939712 Then 'Instant fraction'
When cntr_type = 537003264 Then 'Use this with Base value'
else 'null' end
from Master.dbo.perfmon
/*************************************************/
HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
