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 following SELECT top 10 left(counter_name, 25), left(instance_name,40), cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Log Bytes Received/sec' I get a resultset like Log Bytes Received/sec _Total 0 Log Bytes Received/sec 100f6a49-cd58-432a-8680-ed0f1069c5ea 0 Log Bytes Received/sec dd7b88c8-43c4-48a8-bf97-e174bb5c5eca 0 Log Bytes Received/sec 7a0dc4f3-b236-4c0f-b42c-357bfd521b16 0 Log Bytes Received/sec 5d527545-4bcc-4c66-b315-e1f8fc653245 0 How do I translate the value of instance_name (100f6a49-cd58-432a-8680-ed0f1069c5ea) to a database_name I thought I could look in sys.databases but no luck

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.dm_os_performance_counters returns zero rows. I tried running lodctr /T:perf-MSSQLSERVERsqlctr.ini. Although it completed without errors, it didn't fix anything, even after restarting the Remote Registry service. In fact, the counters are now missing from perfmon. unlodctr MSSQLSERVER still says that the counters are not installed, but lodctr /Q:MSSQLSERVER says they're present and enabled. Regular Windows and .NET counters are working fine. In the Windows Error log, I'm seeing Error 8317: > Cannot query value 'First Counter' associated with registry key > 'HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance'. SQL > Server performance counters are disabled. I suspect the problem may have been triggered by an installation of SQL 2012 RC0 that failed. Any suggestions for how to recover, short of reinstalling SQL Server 2008 R2?

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 drive has acceptable latency, there is no point > in moving forward. > > Disk counters that you can monitor to determine disk activity are > divided into the following two groups: > > - Primary > - PhysicalDisk: Avg. Disk sec/Write > - PhysicalDisk: Avg. Disk sec/Read > > - Secondary > - PhysicalDisk: Avg. Disk Queue Length > - PhysicalDisk: Disk Bytes/sec > - PhysicalDisk: Disk Transfers/sec Why is disk queue length an ineffective measure when there is no latency?

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 the following picture: The drop downs for Object, counter and instance are empty. I can't even create the alert using TSQL as follows: I can see these counters collecting data in perfmon. Any ideas? Thanks in advance...

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 period of time?

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?

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 It says I need to capture the %user time and %privileged time for sqlsvr instance. Then it goes to say high %user time means sqlsvr is keeping cpu busy. Where as high %privileges time means other processes or anti-virus is keeping the cpu busy. My question is - how would %privilege time of sqvsvr instance indicate cpu consumption of other processes?

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 > indicates an ideal amount of memory SQL Server could consume, based on > recent workload. Compare to Total Server Memory after a period of > typical operation to determine whether SQL Server has a desired amount > of memory allocated. I am getting conflicting definitions on the internet about the SQL Server: Memory Manager: Target Server Memory (KB) counter. MSDN doc definition is as shown in the above quote. Where as other online links say that this is the max server memory that we configure under the sql server options. Example: https://www.mssqltips.com/sqlservertip/6571/perfmon-counters-for-sql-server-memory/ > Target Server Memory (KB) is the amount of memory that SQL Server can > potentially allocate to the buffer pool under its current workload. > The Target Memory (KB) counter would reflect SQL Server "max server > memory (MB)" when it is set as that would be the amount of memory that > SQL Server could and would like to reach. Which definition is correct?

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 memory. If this was greater than 0 then it will indicate memory pressure which would get resolved upon increasing the max sql server memory setting. However I am seeing low PLE (page life expectancy), high lazy writes/sec and free list stalls/sec. Does this indicate memory pressure?

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 the total server memory and target server memory perf monitor counters. I can see that the target server memory never crosses 90150MB, and the total server memory never crosses 89100MB. What could be the reason for this? My understanding was that target memory should always be equal to the setting configured in sql server memory settings. But why is this different in my situation?

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 just using the PerfMon counter “Process>Thread Count” for each SQL Server Instance. During our analysis, we noticed that there is a slight difference between the PerfMon Thread Count and the Worker Threads as found in dm_os_schedulers or dm_os_workers. We also compared it to what was found in dm_os_threads and still saw a difference. Does anyone know the difference between what is being tracked in those Microsoft provided DMV’s vs what is being captured by PerfMon? The DMV’s are consistently lower than what is found in the Performance Monitor, so they must be "excluding" something. We need to understand what that something is. For example, the dm_os_threads dmv will show 137 but the perfmon counter will show 141 and the dm_os_scheduler and dm_os_workerss dmv's will show 122 and 123 respectively. We are on SQL Server 2017, CU22; Windows Server 2016

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 currently doesn't require) and pull in required pages from disk. However, when there is memory pressure - (assuming all pages in memory are required and actively used) SQL server will utilize the page file on disk as an alternate memory area. What perfmon metric will help me to monitor whether the SQL server memory is under pressure (that is disk page file is being used)? I know about Memory:Page Fault/sec - but this is not restricted to only SQL server memory pressure. What other perf mon metric can help me?

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 upwards of 17 hours. I have what seems to be a near identical setup on a different reporting server that runs the job in ~4hours. I've taken a look at some perfmon counters and noticed that while target server memory is at 32GB, total server memory hovers around 1GB. From what I understand, this means there is memory pressure on the server, however, total RAM usage on the box stays between 4%-6%. Any ideas on what could be causing the issue? We've all seen SQL gobble up memory, interesting that I can't get this instance to take any. FWIW, i've also added the NT Service/MSSQLSERVER to 'Gpedit - lock pages in memory' with no improvement. I'm starting to lean towards bad RAM or MOBO. However, Windows and BIOS seem to see the RAM fine, and a memtest comes back clean. Please tell me I'm overlooking something dumb.

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 the correct counter for redo rate via perfmon), and sometimes not at all. Most of the times when there is lot of activity ongoing, my belief is that AG dashboard and DMV shows REDO rate for e.g. 40 MB/Secs at given time which matches with perfmon counter. However, during less activity time or nothing much to send over to sec, REDO Rate on Dashboard and dmv seems to be showing incorrect values compared to perfmon counter. Not able to understand which value is correct and how to analyze. Any idea why or is it bug in dashboard? Screenshot as requested: No transactions occurred around that time - no major activity on primary end. I am collecting that perfmon on secondary and primary since after failover new secondary we would still need those counters running. However the data pulled in screenshot perfmon data is from secondary.

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:Computername $instance = $computer + '\MSSQL$SG25DB' write-Host $instance Write-Output -InputObject "Use of Memory Buffer. The expected value must be greater than 80." Get-Counter "$instance:Buffer Manager\Buffer cache hit ratio" and the output is: \\\\[hostname]\\MSSQL$SG25DB <--- Here appears the proper value :) Use of Memory Buffer. The expected value must be greater than 80. Get-Counter : The specified counter path could not be interpreted. At line:5 char:12 + Get-Counter <<<< "$instance:Buffer Manager\Buffer cache hit ratio" + CategoryInfo : InvalidResult: (:) [Get-Counter], Exception + FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand I have verified with Write-Host that the value of variable $instance is properly constructed but when I apply it to the Get-Counter command its fails. Do you have any idea? Could be related with the special character $? How can I make run it properly?

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 sql server. Its clear that SQL Server is waiting on disk I/O. But I keep getting told that it is beacuse SQL Server is asking for abnormally high I/O. Which isn't the case. I can see from what is running that there is nothing out of the normal, and all the DBA cares to look at is what is causing the blocking and so on, which is useless. For instance the major thing we see backing up is operation on the ASPState database, which we are using to manage the ASP Session State on the web servers. These operations are normally never seen on Sp_who2 active results because they occur so quickly. The database is in simple recovery mode and logging is miminal. However during these lag spikes we can see alot select and update operations on the database being blocked or waiting. I'm sure what is going on is that someone or some job is running something that is causing heavey disk usage on the raid arrays used for that databases log and data files. The problem is proving it, since no one wants to admit they are doing something that is killing our website. My question is what performance counters or whatever can I log that will help show that SQL server is waiting on I/O, but not because its asking for more than normaly, instead beacuse the disk is to busy to respond to the requests from sql server as quickly as it normally would?

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 which have more than 100 columns within them, and unfortunately as it is an outsourced application there is little I can do with the database design. So, the graphs below show page reads and writes per second. We have a large spike with reads at around 4 Am which is a load to Qlikview which we are aware of. However, page reads on the whole tend to be lowish, I think they are still higher than the 90 page per second threshold, but less of an issue in comparison to writes. Writes on the other hand, as the graph displays is much higher with regular instances where values are higher than 500 pages per second. Since 15/10/2018, for the vast majority of data capture intervals, the below has applied - where Lazywrites per second is greater than 1. - Page life expectancy also is normally very high. We do have instances where the value drops during the morning which we know about (4AM), however with the odd exception, we are almost always above the suggested threshold 7500s (Memory/4)*30. - Buffer Cache Hit Ratio also is very healthy at or around 99%. - Finally, we have had very few if any memory related waits, Resource_Semaphore is at 0 seconds and CMEM_THREAD has accumulated 60seconds since 27/10/2018. - Values for Memory grants pending is 0 and Memory grants Outstanding is 1 with data collected since 27/10/2018. These factors, in my opinion rule out memory pressure being an issue but I can't seem to explain the strange spikes with regards to writes per second. In my opinion, it's related to our database design. We do have extremely wide tables which can be inserted/updated/deleted to frequently. We also have many wide indexes with inefficient data types. From the data and graphs I've provided, are there any signs of what they high writes is attributed to or are there any metrics I can use to explain the spikes? **Version:** > Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64) > Jul 3 2017 02:25:44 Copyright (c) Microsoft Corporation Standard > Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) **Data Source** > - The graphs above are taken from solarwinds, the data I assume is collected from sys.dm_os_performance_counters > - All other counter data which I have references is derived from sys.dm_os_performance_counters, captured at periodic intervals with the wait stats taken from sys.dm_os_wait_stats. Thanks

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 for Get-Counter returns an error message as seen below, where as the second call with a manually created list works correctly. How do I resolve this? Get-Counter : Internal performance counter API call failed. Error: c0000bc4. At line:26 char:9 $SQLQueryGetCounters = " DECLARE @PerfmonCounterList VARCHAR(MAX) SELECT TOP 2 PerfmonCounter FROM Perfmon.PerfmonCounterList" $SQLQueryGetCounters = " DECLARE @PerfmonCounterList VARCHAR(MAX) SELECT top 2 ' "' +PerfmonCounter + '"' ItemValue FROM Perfmon.PerfmonCounterList " $Counters = @(Invoke-sqlcmd -ServerInstance Localhost -Database "DatabaseMonitoring" -Query $SQLQueryGetCounters )| select-object -expand ItemValue $List = [System.Collections.ArrayList]@() # New-Object System.Collections.Generic.List[System.Object] # [System.Collections.ArrayList]@() foreach ($Item in $Counters) { $List.Add($Item) } $List = $List.ToArray() Get-Counter -Counter $List $List = "\DATABASE(*)\DATABASE CACHE % HIT","\DATABASE(*)\DATABASE CACHE SIZE (MB)" Get-Counter -Counter $List

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 like DMV?

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 project: https://github.com/heraflux/BLGtoSQL ). All this runs with one user on the local machine. After leaving this running for a couple of weeks I have created a report to look at the data gathered so far. All the Perfmon counters EXCEPT the SQL ones work. All the SQL counters return zero - even though I know at least some of these counters CAN'T always be zero (e.g. User Connections, Free Memory KB etc). What's odd is that when I run and import the exact same setup on our development server the SQL counters work as desired. I've checked and the user has the same local permissions on each server. I've also granted the user with the following permissions on the SQL server: Connect Any Database, Connect SQL, View Server State. When looking at the SQL counters live on Perfmon I've found that they also don't seem to be working. I'm pretty stumped as to why they aren't showing anything.