Questions tagged [perfmon]

Perfmon is short for Performance Monitor, which is a tool that comes with Windows Server used to collection performance metrics on servers

Filter by
Sorted by
Tagged with
0 votes
1 answer
268 views

Why is disk queue length an ineffective measure when there is no latency?

Link: https://social.technet.microsoft.com/wiki/contents/articles/3214.sql-server-monitoring-disk-usage.aspx You should only use the secondary counters for the drive(s) that have high latency. If the ...
variable's user avatar
  • 3,205
-3 votes
1 answer
214 views

How to monitor the workers used over a period of time?

Assume I have the default setting of max workers or say I have x number of max worker threads configured. Is there any windows or sql performance counter to monitor the number of workers used over a ...
variable's user avatar
  • 3,205
1 vote
0 answers
47 views

Does SSIS `Buffer memory` include `Flat buffer memory` and `Private buffer memory`?

I am looking at ssis perf mon counters to check for memory pressure from running ssis packages. Does SSIS Buffer memory include Flat buffer memory and Private buffer memory?
variable's user avatar
  • 3,205
-1 votes
1 answer
35 views

How would %privilege time of sqlsvr instance indicate cpu consumption of other processes?

I am looking at msdn doc to monitor cpu. Link: https://learn.microsoft.com/en-us/troubleshoot/sql/performance/troubleshoot-high-cpu-usage-issues#step-1-verify-that-sql-server-is-causing-high-cpu-usage ...
variable's user avatar
  • 3,205
3 votes
2 answers
214 views

Whether `SQL Server: Memory Manager: Target Server Memory (KB)` is ideal amount of memory based on recent workload OR the max memory setting?

Link: https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-memory-usage?view=sql-server-ver15 SQL Server: Memory Manager: Target Server Memory (KB) This counter ...
variable's user avatar
  • 3,205
3 votes
3 answers
159 views

Why is total and target server memory never reaching the configured memory value?

I have sql server with 100GB ram, of which 90GB is allocated to sql server. 2 GB is currently free as shown in task manager. 90GB=92160MB (as seen in the sql server memory settings) I am monitoring ...
variable's user avatar
  • 3,205
0 votes
3 answers
380 views

What does 0 memory grants pending, but low PLE, high lazy writes/sec and free list stalls/sec indicate?

My server memory is 100GB, of which 90GB is allocated to SQL server via the max memory setting. I am monitoring a sql server that has got 0 memory grants pending. This means no process is waiting for ...
variable's user avatar
  • 3,205
0 votes
1 answer
354 views

Perfmon Thread Count vs SQL Workers

We are trying to monitor SQL Worker Threads and in the past we used the following query: SELECT SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers We are now considering ...
SFDCShark's user avatar
0 votes
1 answer
152 views

decoding instance_name in sys.dm_os_performance_counters on Azure SQL Database

doing some work with the SQL Perfmon counters and banging my head against the wall trying to work out how to translate the instance_name column to a database name. So if I run a query like the ...
Stephen Morris - Mo64's user avatar
2 votes
1 answer
398 views

What perfmon metric will help me to monitor whether the SQL server memory is under pressure and resulting in usage of page file on disk?

SQL server is assigned 110GB memory. It is consuming the entire memory. I want to understand whether there is memory pressure or not. Usually SQL server will remove old pages from the memory (that it ...
variable's user avatar
  • 3,205
0 votes
0 answers
67 views

SQL Server 2012 RAM consumption

I'm running SQL Server 2012 (x64) on a Windows 10 (x64) machine with 64GB of RAM. I've allocated 32000MB to SQL Server. When running a Restore with NORECOVERY, RAM hardly gets used and the job takes ...
KCZAR's user avatar
  • 1
3 votes
1 answer
262 views

Availability Group- Redo Rate displayed on AG Dashboard vs Perfmon counter

I am bit confused on checking the metrics for REDO Rate KB/Sec from Always on AG dashboard, which for some scenarios matches with perfmon counter Database replica : Redone Bytes/Sec ( hopefully it's ...
BeginnerDBA's user avatar
  • 2,169
0 votes
0 answers
187 views

Get-Counter not accepting array created from SQL

When I try and generate an array from a SQL output, although it's the same data type and content, perfmon doesn't accept these values, whereas it does for a manually created a list. So the first call ...
Krishn's user avatar
  • 13
0 votes
0 answers
223 views

Why are my SQL Perfmon counters returning Zero?

I have set up a Perfmon data collector set on one of our servers with SQL server running on it. I also have a scheduled task which imports this data into SQL (set up by following this open source ...
Samantha's user avatar
1 vote
1 answer
1k views

How to create SQL Server Agent Queued jobs?

There is a perfmon counter "Queued jobs" in perfmon object "SQLAgent:Jobs". How to create a job queue that will reflect in this perfmon counter? Can I get this queued job info from somewhere else ...
Santosh Kale's user avatar
0 votes
2 answers
1k views

Strangely high writes per second

I'm experiencing some extremely strange behavior on one of our OLTP Servers. Just to provide a bit of background, we have an 'OLTP' which has many large and wide tables. We have 560 tables within it ...
Krishn's user avatar
  • 343
1 vote
0 answers
341 views

Cost / Capacity Planning: Quantify Data Ingress / Egress

To evaluate the migration of an on-prem SQL Server to a cloud-hosted VM, I want two fundamental pieces of information: The list of clients that access the on-prem DB Server. I'd like to know: client ...
Brent Arias's user avatar
2 votes
1 answer
1k views

Why "Data File(s) Used Size (KB)" PerfMon counter doesn't exist?

I wanted to create my own SQL Server baseline by collecting data with the Performance Monitor. Among all counters that I have decided to monitor, I also wanted to have a historic trend about data ...
Mattia Nocerino's user avatar
3 votes
1 answer
671 views

Cpu-Alert in SQL Server -2012 standard edition

My question is an extension to this. We have SQL Server Agent alerts(Deadlocks, Network IO etc) to monitor SQL Server performance. So i would like to write an alert for CPU increase percentage. ...
Tharunkumar Reddy's user avatar
0 votes
2 answers
3k views

Need to setup a sql alert on "waiting tasks" - similar to "waiting tasks" in Activity Monitor

My manager has asked me to work upon a request. I am not sure if it is reasonable or weird but he wants me to setup an alert based on the "waiting task" that we see in the activity monitor. Basically, ...
Feivel's user avatar
  • 507
0 votes
2 answers
726 views

SQL Server target/total server memory is not equal to sum of database size in dm_os_buffer_descriptors

I am having an issue in my SQL Server like high memory paging rate and more compilation/sec which lead me to a different observation in memory, so first i checked what is total size of memory of ...
info.sqldba's user avatar
1 vote
1 answer
364 views

SQL Server : Perfmon Counter Incorrect value

I'm trying to understand why this Performance counter shows it is 0. "Mssql$instance1:locks(_total)\lock wait time (ms)" I tried with wild card to check all instance_name and they're all 0. ...
SQL_'s user avatar
  • 13
0 votes
1 answer
46 views

Are 'per second' Performance Counters measured over a certain period of time? [duplicate]

This may be a very silly question, but when I'm looking at the current value of a 'per second' counter in PerfMon, for example SQL Statistics\Batch Requests/sec - how is that value actually measured? ...
Molenpad's user avatar
  • 1,774
2 votes
2 answers
521 views

VMWare Memory Sizing and SQL Server 2014

We have a production server that dumps it's Page Life Expectancy many times a day (climbs to 2500s and falls back to near 0s)... After watching some training videos about virtualization for SQL Server,...
Wes's user avatar
  • 1,126
1 vote
2 answers
503 views

Access to performance counters from T-SQL procedure

I want to write a stored procedure, which is returning CPU utilisation, Memory utilisation and Count of rows in table T1. So it should look like: SELECT cpu, memory, (SELECT COUNT(*) FROM T1) as ...
Alex Zhukovskiy's user avatar
1 vote
0 answers
46 views

Beefing up capacity of existing AAG cluster

I am planning to add 3 new databases to an existing AAG cluster in a VM. The AAG has Veeam databases on it as of now. Veeam backups are not running on it as of now apart from a couple of batch ...
buddinggeek's user avatar
5 votes
2 answers
1k views

How can I extract the perfmon counter "Memory: Pages/Sec" using t-sql?

I've googled this and so far, no luck so turning to all you awesome gurus for help. I'm trying to figure out if there's a way to get the perfmon counter "Memory: Pages/Sec" using t-sql as I'm trying ...
Chinesinho's user avatar
7 votes
2 answers
1k views

How to measure Key Lookups per Second on SQL Server

I am working on reducing the amount of transactions doing key lookups on our SQL Servers. First I want to define a baseline for the normal or tolerable number on that metric and as we constantly ...
Salvador L's user avatar
2 votes
0 answers
376 views

Memory\Pool Nonpaged Bytes low but Memory\Pool Paged Bytes high

Can someone help identify how these values can indicate memory leak. In the system I am monitoring Memory\Pool Nonpaged Bytes are low (on average 100mb) but Memory\Pool Paged Bytes are high (on ...
user2811633's user avatar
5 votes
1 answer
1k views

SQL Server - comparing PageSplits/sec with Batch Requests/Sec

I read here (http://www.databasejournal.com/features/mssql/article.php/3932406/Top-10-SQL-Server-Counters-for-Monitoring-SQL-Server-Performance.htm) that having a high value of PageSplits/sec compared ...
frankieta's user avatar
  • 465
2 votes
1 answer
506 views

Howto use typeperf to store performance monitor values in a custom schema?

I have setup a performance monitoring of my SQL server system using typeperf.exe to store the values directly to SQL Server. I went through the following article, that explains howto use typperf.exe ...
Magier's user avatar
  • 4,717
2 votes
1 answer
89 views

Any merit to MSSQLSERVER Trace from logman as opposed to XEvent Package 0

The general understanding of monitoring SQL Server activity is that using SQL Profiler is very heavy, server side traces are less heavy, but extended events are even less heavy. Today I discovered ...
Justin Dearing's user avatar
9 votes
4 answers
15k views

Monitoring free space in SQL Server data files

I have manually resized mdf/ndf files to a big size to avoid autogrow operations on SQL Server databases. Since the files are bigger there is very little free space on disk partitions and the ...
BuahahaXD's user avatar
  • 1,405
3 votes
2 answers
308 views

What perfmon counters should I use to get the fastest restore possible on an idle system?

I have a development VM running Windows 2012-R2 and Sql Server 2014. I have one disk from a SAN holding the OS and SQL. My backup file is stored locally on the disk. I want to restore database as ...
Justin Dearing's user avatar
3 votes
1 answer
849 views

Perfmon and SQL Server Clustering

I'll be going onsite to run some performance tests for a client. They have a Windows Failover Cluster, which has a clustered instance of SQL Server. Let's assume for this post that my client's ...
Dave Mason's user avatar
1 vote
1 answer
16k views

Understanding pagefile.sys % Usage performance monitor (perfmon) counter correctly

Lately we run into performance inssues and I started tracking some recommended sql, memory, disk and cpu counters. I informed myself about the recommended values and numbers to look at. One of the ...
RayofCommand's user avatar
  • 1,353
2 votes
1 answer
5k views

Get the application name

Is it possible -- does a perfmon counter exist -- that would show the name of the end-user application that is connecting to SQL Server?
udog's user avatar
  • 123
5 votes
1 answer
8k views

SQL Server PerfMon: Compilations/sec > Batch Requests/sec

I've been looking at various PerfMon metrics on one of our client's SQL Server instances trying to get a good gauge of where our database (or server) could use improvements. One measure that has me ...
John Russell's user avatar
1 vote
2 answers
4k views

Performance counter MSSQLInstance\SQL Errors\Errors/sec\Info Errors whats in it?

Im wondering what kind of errors would show up in the: SQL Errors\Errors/sec\Info errors performance counter. I read the TechNet article it just states: Information related to error messages ...
jakobandersen's user avatar
1 vote
0 answers
1k views

SQL Server: Batch Requests/sec showed increase in performance, but performance actually decreased

We are currently on SQL Server 2005, with a plan to move to SQL 2012. To make this move we have to test new database drivers for the application (Java). We performed 2 load tests using the 2 different ...
Duncan's user avatar
  • 81
3 votes
4 answers
5k views

SQL Server Page Life Expectancy sharp drop

I'm trying to put my hands on the reason this SQL Server slows down sporadically. There are no patterns detected so far. It's a SQL Server 2008 R2 running on a Windows Server 2008R2 VM on top of ...
Zero Subnet's user avatar
2 votes
2 answers
2k views

Reading SQL Performance Counters using PowerShell script

I'm trying create a PS script to read some performance counter about my SQL for my Level 1 partners but I'm having a problem with the instance name. I'm using this code: $computer = "\\\" + $ENV:...
Simkin's user avatar
  • 29
25 votes
2 answers
27k views

How badly do SQL Compilations impact the performance of SQL Server?

I am profiling an instance of a SQL Server 2005 and I, via PerfMon's SQLServer:SQL Statistics - SQL Compilations/sec metric, I see that the average is about 170 or so. I whipped out SQL Profiler ...
AngryHacker's user avatar
  • 1,919
4 votes
2 answers
133 views

Mirroring and Buffer Page Write/s

Is the "Buffer Manager: Page writes/sec" (* 8 KBytes) a good indicator of how much bandwidth will be used to replicate all databases via Availability Groups? If not, why are these not equivalent? Is ...
Kyle Brandt's user avatar
  • 2,335
19 votes
3 answers
87k views

High Disk I/O from sql server or is High disk I/O slowing sql server?

I've been arguing with a DBA and a couple hardware guys about performance issues on our SQL server. Normally everything is fine, however over the past few weeks we have been having huge lag spikes in ...
Edgey's user avatar
  • 191
11 votes
5 answers
16k views

How to revive SQL Server 2008 R2 performance counters?

I'm running SQL Server 2008 R2 Developer on Windows 7 x64, as the default instance. For some reason, SQL Server's performance counters seem to have disappeared. SELECT * FROM sys....
RickNZ's user avatar
  • 351
1 vote
0 answers
158 views

SQL Server and Perfmon [duplicate]

Possible Duplicate: SQL 2008 Server agent cant see perfmon counters I'm trying to setup SQL Server Perfmon counters on a Windows Server 2003 with SQL Server 2005 SP3. I'd like to run counters on ...
David's user avatar
  • 31
3 votes
1 answer
803 views

SQL 2008 Server agent cant see perfmon counters

I am trying to create a new SQL server agent alert to capture "SQLServer:General Statistics|Processes Blocked||>|5' but it looks like alert creation GUI is not able to see any perfmon counters as in ...
Manjot's user avatar
  • 1,173
22 votes
1 answer
5k views

When should extended events be used instead of SQL Profiler/perfmon?

The extended events seem like a better technology and less stress on the server, but the SQL Profiler/perfmon has better tooling. Also the extended events seem to have a steeper learning curve. In ...
Michael Hedgpeth's user avatar
5 votes
2 answers
1k views

DMV Queries as Perfmon Counters

Certain DMV queries that return various statistics that increment since boot would be useful as perfmon counters. For example, take: SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats; Most ...
Kyle Brandt's user avatar
  • 2,335