44

This SQL will help you select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal from (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 Result: ...


12

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. Test ...


11

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 ...


11

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.


10

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. USE msdb; GO CREATE TABLE dbo.ReportServerJob_FailQueue ( job_id UNIQUEIDENTIFIER, run_date INT, run_time INT, -- horrible ...


9

By the time the client connects, it typically doesn't present the hostname it used. The client resolves the hostname in DNS and connects to the IP(s) that were returned from the DNS lookup. Kerberos connections will fail if the client uses a hostname that doesn't correspond to an SPN registered for the SQL Server service account. And NTLM can fail if the ...


8

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....


8

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 ...


8

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''; SELECT @...


7

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 ...


7

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. EXEC sp_addmessage @msgnum=...


6

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 ...


6

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 ...


6

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 ...


6

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: innodb_lru_scan_depth=100 innodb_flushing_avg_loops=5 thread_cache_size=216 Fixed as of the upcoming 8.0.16 release, and here's the changelog entry: Static thread local variables ...


5

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.


5

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 ...


5

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 ...


5

The main indicator is the exit status of the pg_dump command. If it's non-zero, then something went wrong otherwise it worked all along. This is the implicit contract between any command and the shell, and breaching it would be a bug. And it's also testable when the sub-command could't be launched at all. Here's a basic skeleton in Unix shell that tests the ...


5

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 ...


5

Just to build on Aaron's and Kin's answers, you can do it with perf counters, but one of the user settable counters. I would: 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 ...


5

You should Query the V$SESSION_LONGOPS select ops.OPNAME, ops.TIME_REMAINING,ops.start_time from v$session_longops ops where ops.sid=&your_sid and ops.serial#=&your_serial and time_remaining>0; 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 ...


5

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 ...


5

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 ...


4

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/...


4

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.


4

I've voted to close this, because it's essentially a poll question, and there isn't a possible "right answer." You should be evaluating the vendors' offerings to see which product best suits your requirements and budget. Nobody can tell you which product will do that best. The major vendors you want to investigate are: SQL Sentry Quest FogLight / SpotLight ...


4

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 [...


4

That's not really a PostgreSQL monitoring tool, that's PostgreSQL's internal facilities for diagnostics, monitoring and database statistics. For host monitoring you want a tool like Icinga (modernized Nagios) or Zabbix. When monitoring PostgreSQL the check_postgres tool is likely to be of interest. If you're interested in getting the CPU use of a ...


4

Yes as the DBA you should have access to view the vSphere info. The biggest reason being to troubleshoot the noisy neighbor problem where another VM is taking all the cpu power not leaving any resources for you. It isn't possible to see this from within a VM. This isn't limited to CPU but can also be disk IO as well. You also want to be able to see exactly ...


Only top voted, non community-wiki answers of a minimum length are eligible