This SQL will help you
select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal
(select count(*) used from pg_stat_activity) t1,
(select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
(select setting::int max_conn from pg_settings where name=$$max_connections$$) t3
Not sure why you want to use performance counters for this when you can get it from a simple query. And in fact while you can get this information about log files from performance counters (Log File(s) Size (KB) / Log File(s) Used Size (KB)), there is no such counter for how much space is used in a data file.
;WITH f AS
SELECT name, size = size/128.0 ...
I agree with Aaron that RAISERROR...WITH NOWAIT can be very useful and is probably the way to go if you have full control over the script that is being generated.
However, if a long script is currently executing and you don't have the ability to change the script in order to add RAISERROR calls, there are also less direct ways to get this information.
You could create a job that checks the msdb.dbo.sysjobhistory table every minute (or however frequently you want). You might want to implement a queue table so you only ever send the message for any single instance failure once.
CREATE TABLE dbo.ReportServerJob_FailQueue
run_time INT, -- horrible ...
If you do select count(*) from million_row_table, one million row wills be returned, but only one row will be fetched.
I can't see I've ever found these fields useful for diagnosing performance problems. Find your slow query and do an EXPLAIN (ANALYZE, BUFFERS) of it.
Memory usage in linux in general and for Postgres in particular is a pretty complex subject, a good starting point is Bruce Momjians blog covering the subject and the usage of smem. It is well worth following the links in Chris Seibenmanns blog on the subject.
Not aware of any built-in method to record bytes over a particular port so if you really need that level of detail, Wireshark is a good bet.
If the server is dedicated to SQL Server I'd be reasonably confident that the majority of traffic over the network was related, so the perfmon counter Network Interface\Bytes Total/Sec should give you a broad guideline....
More than one year later I want to let everyone know my experience and the final result of this question / topic.
I started out creating things on my own. Initially I followed the Article Collect and store historical SQL Server performance counter data with CMVs by Tim Ford to get something up and extended this with whatever Data I wanted to collect. So ...
Obviously, a lot of this devolves to simple personal choice. Here are my own, personal, rationalizations.
I've been using Powershell with SQL SQL since PSH v 1.0, and before SQL Server started officially integrating it. (When I started with PSH, I was administering SQL Server 2000 and 2005 servers.) So, I learned with SMO (or it's slightly older ...
I have another method to proactively monitor data file space and alert if the free space falls below a certain percentage using SQL Alert.
The basics are
Create a user defined error message in sys.messages. This will be used by sql agent alert.
-- User-defined error messages can be an integer between 50001 and 2147483647.
Here is one way to generate a script that outputs data to the screen when one command is complete, and announces the next one as well. The important thing is to use RAISERROR with NOWAIT so that you aren't depending on the buffer output manager in SSMS deciding when you should see PRINT output in the messages pane.
DECLARE @sql NVARCHAR(MAX) = N'';
As I understand it, you want to monitor network traffic in between a web application hosted in IIS and SQL Server, both of which are running on the same server. This is going to be complicated by a few things:
Most packet sniffers don't support monitoring traffic over a loopback interface like you are using if your web app connects to SQL Server on ...
Here are some good articles with some practical examples that you can find here:
How to detect SQL Server performance issues using baselines – Part 1 – Introduction
How to detect SQL Server performance issues using baselines – Part 2 – Collecting metrics and reporting
How to detect SQL Server performance issues using baselines – Part 3
While Part 1 will ...
We're now looking for a way to monitor the replication; mostly to verify that the slave server is still up-to-date.
For monitoring replica lag, there are several ways that give slightly different answers, depending on which version of Postgres you are using. A simple query that can be done directly on the standby is:
SELECT (CASE WHEN ...
The issue is a Mysql bug (https://bugs.mysql.com/bug.php?id=94185 )
Anyway, some tuning mitigated the issue and stopped the number of open files to grow.
Specifically, we set:
Fixed as of the upcoming 8.0.16 release, and here's the changelog
Static thread local variables ...
How can I, only using Central Management Server, provide convincing
performance related information for the individual servers managed
Unless I've missed a key feature somewhere along the line, you can't. What does it give you?
Logical organisation of your servers and instances.
Policy Based Management can be applied to server groups.
Depending on what you're exactly looking for there are many different tools to consider. As always, there's no silver bullet in the monitoring world.
I started compiling a somewhat authoritative list of tools at the PostgreSQL Wiki which can be found here: http://wiki.postgresql.org/wiki/Monitoring
At the end of 2012 I also started working on a Postgres ...
You can snap sys.dm_exec_connections which will give you the net IO metrics for any given connection. The associated sys.dm_exec_sessions will identify the connection client (host, application). Since you need aggregated history and the view gives moment in time snapshot you will have to do the usual transformations to convert moment-in-time to aggregate ...
There's Sql Server Profiler which comes with the installation. It allows you to monitor all SQL statements with various filter criteria. You could also pair it up with PerfMon to investigate performance.
MSDN Sql Server Profiler
I also found this site Receiving Profiler Events. Which allows real-time tracking in .NET.
The DMV sys.dm_exec_connections has per-connection statistics for traffic, including bytes send/read. But it gets reset every time a physical connection is closed and also is a bad indicator of throughput, specially spikes.
That being said, traffic between client and server should never be a concern. Specially for a web app, where large return sets are not ...
Short Answer: Yes.
There are many reasons but the few that stick to mind:
1.) Trust but verify - SQL cares a lot about its environment, the hardware or virtualized system it is on. When I help a company with SQL on VM issues it is normally a misconfigured VM. In many cases the idea of SQL on VM is about to be thrown away.
2.) DBAs should look at memory ...
Just to build on Aaron's and Kin's answers, you can do it with perf counters, but one of the user settable counters.
create a stored procedure that will use Aaron's query to get the free space in a single file or loop through all files and get the min/max value that's of interest
create a job that will periodically run the stored proc
In case ...
You should Query the V$SESSION_LONGOPS
select ops.OPNAME, ops.TIME_REMAINING,ops.start_time
from v$session_longops ops
If the index rebuild is parallel, than you you must find the parallel slaves that do the work.
This can be done by querying the V$PX_SESSION. Your ...
Identifying parameter sniffing is hard work! It may not be apparent using monitoring tools or queries, because it typically requires some analysis of other executions of the query plan.
This is where the DMVs, and a plan cache analysis tool like sp_BlitzCache can help. You can download it here. Full disclosure: I work for Brent Ozar, and contribute to the ...
What is your actual goal? Are you trying to track down a troublesome query, performance tune your whole environment, or is this just out of curiosity?
Ultimately, I wouldn't recommend trying to track and log all queries. I suspect you only really care about poorly performing queries which are an actual business impact.
On most production servers, you will ...
Personally I ignore the excellent, poor, etc. As long as the number of records pending isn't very high, and the amount of time to catch up isn't very high I consider everything is fine. That front screen monitor is pretty misleading most of the time.
Polymon from codeplex is ideal for my uses. I'm monitoring not just server availability, but sql agent jobs success, a host of perfmon data, SQL locking/blocking, db and filesystem freespace, and a whole lot more.
It has a very nice Powershell interface, so any Powershell script you can imagine, it will run on the schedule you specify and then alert/notify/...
I think we might need to dip into sys.dm_os_ring_buffers and sys.dm_os_sys_info to get the CPU utilization data.
Please find below the script of Glenn Berry
-- Recent CPU Utilization History (SQL 2008/2008 R2 Only)
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(144) SQLProcessUtilization AS [...