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
50
questions
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 ...
- 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 ...
- 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?
- 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
...
- 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
...
- 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 ...
- 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 ...
- 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 ...
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 ...
- 3,703
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 81
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 ...
- 51
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 ...
- 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 ...
- 191
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 ...
- 602
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. ...
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, ...
- 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 ...
- 317
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.
...
- 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?
...
- 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,...
- 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 ...
- 333
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 ...
- 625
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 ...
- 611
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 ...
- 131
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 ...
- 21
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 ...
- 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 ...
- 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 ...
- 2,667
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 ...
- 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 ...
- 2,667
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 ...
- 865
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 ...
- 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?
- 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 ...
- 717
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 ...
- 133
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 ...
- 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 ...
- 183
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:...
- 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 ...
- 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 ...
- 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 ...
- 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....
- 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 ...
- 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 ...
- 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 ...
- 1,331
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 ...
- 2,335