39

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


17

You can get this (and more) from Dynamic Management Views (DMVs). To get statistics for a particular stored procedure, try the following query. SELECT OBJECT_NAME(qt.objectid) , qs.execution_count AS [Execution Count] , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second] , qs.total_worker_time / qs.execution_count ...


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

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


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


8

I have used the MVC Mini Profiler as part of an application to profile SQL Azure but depending on your application it may or may not work. What kind of tasks are you doing that need profiling?


8

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.


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

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.


7

I would suggest using the SQL Server Counters in the Windows PerfMon Utility. This is for SQL 2005 but the instructions provide the gist of things for you.


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


7

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


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

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


5

How can I, only using Central Management Server, provide convincing performance related information for the individual servers managed with it? 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. Multi-...


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

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

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

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

I try to stay away from having multiple databases in one instance. Instead I have multiple instances set up on a server where each one is dedicated to a database. MySQL Cacti templates are a good monitoring solution. If you are enterprise customer then MySQL Enterprise Monitor works. Also check out mycheckpoint from openark. EDIT Here are some tutorials ...


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.


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