Hot answers tagged monitoring
8
Oracles Enterprise Monitor console shows a whole wealth of information about which SQL queries are taking the max CPU, bottlenecks, top activity in the database, blocking SQLs et al.
For a historical approach, you can use Oracle's AWR reports to pin point areas concerning you.
7
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 ...
6
Something you might do that is just a thought, throwing ideas out...
Create a single job that periodically checks the job table in msdb to see if any jobs show as failed, that can be done with a good T-SQL query. Then you could go into the sysjobsteps table and see if an output log is set for the job. Have a stored procedure send an email attaching that ...
5
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 ...
5
Is pg_statsinfo something for you? Maybe the default stats collected are sufficient? There are some nice posts on SO with a similar topic.
5
Here is the SQL to do the job. Open for trial.
Step 1: Determine the installatin IDs & user IDs.
SELECT inst_id,sid FROM gv$session WHERE username='<ENTER-USERNAME>';
Step 2:
SELECT
s.sid
,s.CLIENT_INFO
,s.MACHINE
,s.PROGRAM
,s.TYPE
,s.logon_time
,s.osuser
,sq.sorts
,sq.DISK_READS
...
5
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.
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.
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
Here's a link to a bunch of links on "tuning best practices."
The short form is: It depends. What are you tuning for? Tuning read performance is different than write performance and everything changes when you move to a cluster. There is no one set of "magic variables" that can be quietly optimized for every situation.
From these, you can look at ...
4
What are you using for your other monitoring, e.g. disk space, processor load, etc? If it is Nagios then you can just get a Postgres plugin for it. That is probably better than having a whole 'nother monitoring framework.
4
What is 'normal' will depend on your situation. An OLTP database and a warehouse will have very different usage profiles.
If you use a connection pool, then you probably don't expect to see a wide variation in the number of connections or sessions. Such a change would indicate a problem.
Generally you are best off looking for dramatic changes in your ...
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 ...
4
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 ...
4
I'll do you a favor: Performance Tuning with SQL Server Dynamic Management Views by Ford and Davidson should have many answers. Very helpful book indeed, but there is a awful lot to learn if you have not much experience using DMVs (like me). You can try to adapt the scripts in the book to suit your needs better, that was a good learning experience for me.
3
This solution does not require triggers but requires setup and causes a performance hit if you enable it for all tables.
Auditing has been built into Oracle for many releases. There is an article here which goes into some detail.
Basically, you turn it on, tune it for what level of detail you want and the output is available as a dba view or as XML.
Check ...
3
I have experience with the aforementioned idea. It's good, but a better idea would be to do something like Shawn said.
What we did was to make a job that runs every 5 minutes and scans MSDB tables about job failures. For each job that had a failure we'd run the SP spDBA_job_notification with it's own ID, so the SP will scan the MSDB history steps for errors ...
3
We have a little site to monitor this kind of MySql Replication. Actually, I monitor and admin like 40 MySql databases between servers and end user machines, all of them replicating like crazy daily =P
The site is http://tinyurl.com/4fmu88l
(Because of my boss I preferred to make a tinyurl, sorry hehe)
It's written in PHP. I could share some code if you ...
3
You'd have more options here:
Perfmon (or ResourceMonitor on W7+) for general resource information;
Activity Monitor in Management Studio;
Inside SQL Server:
Glenn Berry's DMV queries;
Adam Machanic's stored procedure sp_WhoIsActive.
There are also many other external monitoring tools (some free, some paid) that you can use, but these ones are free and ...
3
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 ...
3
As per the help section on their website, they describe Connection Time as the following:
Connection Time: Displays the time taken to get connected to the SQL database server.
From that brief description, I'd be willing to say that it is the time duration between initiating the connection to the SQL Server instance.
If this is the case, then the ...
3
If you are looking for free why not try SQL Server Performance Dashboard? The link below is pretty detailed on how to set it up and get it running:
http://www.sql-server-performance.com/2007/bm-performance-dashboard-2005/
Like Shawn said though, there aren't that many free ones available but it is pretty easy to roll your own using SQL/CLR and SSRS. If ...
3
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 ...
2
You can also use V$SQL, there are several interesting columns RUNTIME_MEM, EXECUTIONS, DISK_READS, SORTS, ELAPSED_TIME, SQL_FULLTEXT etc.
This would give you top 10 statements by disk read (note - this is cumulative for all executions):
select sql_id,child_number from
(
select sql_id,child_number from v$sql
order by disk_reads desc
)
where rownum<11
...
2
The only thing I can think of is finding some what (maybe through a cron job) to record the 'Threads_connected' values every minute, and then calculate the max of these values.
I'm not sure if you can do exactly what you are asking for without resetting the 'Max_used_connections' before your monitoring period.
2
There is a new tool available from Cybertec:
http://www.cybertec.at/en/postgresql_products/pgwatch-cybertec-enterprise-postgresql-monitor
(I have not used it though)
2
I might have found a couple of answers on my own. Posting them here in case others need to know:
Google (or my google skills) seems to be getting increasingly less reliable.
Searching the forums at https://forums.oracle.com/ I found this thread: https://forums.oracle.com/forums/thread.jspa?messageID=9956411, which references ...
2
Presumably they are active and doing things so looks like you might need to get more info about your sessions before killing them off. I usually use a query like this to get important info about active sessions including how long they have been logged on (oldest at top), and also how long they have been running the current SQL.
SELECT USERNAME,
...
2
I experimented with trying to create a trigger for a system table (mysql.slow_log). It simply cannot be done. However, there is a rather unconventional method you may want to try:
Step 01) Activate the slow_log with the output as a table and restart mysql
[mysqld]
log-output=TABLE
long-query_time=0.0001
Step 02) Convert slow_log to MyISAM, copy it to ...
Only top voted, non community-wiki answers of a minimum length are eligible
