We changed our privacy policy. Read more.
53

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


24

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.


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


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

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

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

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

'Batch Requests/Sec' is a server-level counter, not available for individual database instances. Keep in mind that single batch can access multiple databases. You can get an aggregated count of batches and RPCs executed per database with an XE trace with a histogram target like the below example. This will count only the context database where the batch was ...


6

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


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


6

First The blocked process report seconds is how many seconds blocking has to go on for before it makes it into the report. I don't have a specific recommendation here, but 10 or higher is usually a good choice. The parameter for sp_HumanEvents is the threshold for what gets shown to you or logged to a table. You are free to set either value to what makes ...


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

You can take advantage of the other tools built into SQL Server for gathering query metrics. The first is Dynamic Management Views (DMV). These can be queried to retrieve aggregate data about the queries currently in cache on the system. Query once every 15 minutes or so and you can build up behavior over time. It is only aggregations though. Also, it's only ...


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


4

You can use a combination of tools. I'll give you an abridged list of the ones I use. First, I assume you have database mail configured. Second, you can trigger a lot of run-of-the-mill alerts using SQL Server Alerts. You can also run a stored procedure using a scheduled job (say every five minutes or some interval like that) and fire the user counters ...


4

What you can do is to have below script stored on your server or make it as a stored procedure : use below with sqlcmd IF EXISTS ( SELECT 1 FROM MASTER.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher') BEGIN SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning' END ELSE BEGIN EXEC msdb.dbo....


4

There are doubtless many blogs that discuss this, but this post by Kendra Little (at BrentOzar.com) discussed the issues that concern you. See: http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/ Kendra discusses some of the problems that arise and how you can test for potential ...


4

Postgres' tablespace concept is completely different to Oracle's. Postgres doesn't use container files the way Oracle does. Each table is stored in one or more individual files. A tablespace is essentially just a directory where files are stored. It is not container file like in Oracle. If a table gets dropped or truncated the space is immediately released ...


4

As a fairly newly minted DBA under the gun, I have run the gamut of free tools and done some experimentation in the paid space (DPA, SQL Sentry, and Foglight) and it really depends on what you want the tool for. In my experience the most important thing was not just communicating performance baselines (management vastly didn't care unless there was someone ...


4

Severity 25 is a 'catch-all' fatal system error. A severity 25 error is a fatal system error. I have heard that severity 25 is more or less a catch-all for miscellaneous fatal errors. I have only seen this error when related to failed upgrades: something prevents one of the upgrade scripts from running, and a severity 25 error is thrown http://...


4

You are counting the toast tables twice. Once under their owner, as pg_total_relation_size(reltoastrelid), and again under their own entry in pg_class. You should use relkind, not reltype, to filter what you want. Also, trying to micromanage your RDBMS to this extent rarely pays off.


4

pg_database_size() essentially queries the filesystem for the size of $PGDATA/base/oid-of-database (the per-database data directory), plus the size of every such directory in non-default tablespaces added with CREATE TABLESPACE. If there are files in these directories that don't pertain to any relation , they will be counted too. Looking at a random ...


4

With SQL Server 2008 R2 (through current version), you can use SQL Server Audit which uses Extended Events. Look at Understanding SQL Server Audit: Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. You can use several methods of auditing for SQL Server, as described in Auditing (...


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