DBMS Query Log, is it possible?

We want to record all db query into log table, i开发者_开发技巧s it possible?

For sql server, see this answer about sql server profiler. For mysql, the query log is a solution. However, they both write to files but you can always parse the log files and insert them into tables if you want to query the data.

Beware, however that logging does not come free. You will see some performance degradation in both cases. If you only want to log the queries of an application, you could opt to log the queries there (optionally, asynchronously). You'll have to test to see what's the best option.

EDIT : And also, depending your amount of traffic, logging all queries can eat large amounts of diskspace in a short amount of time. If you log in the application, you could use an logging library like nlog that has a rollover system (i.e. if the logfiles reach > 100 mb, then start deleting the oldest files). In all three cases, you could also set aside a partition meant only for logging so it doesn't fill up your main hard disks.

From a SQL Server perspective......

As others have suggested, SQL Server Profiler is certainly one way to go but you're going to incur a resource hit from doing so. Should you choose this method you absolutely must implement it as a Server Side Trace rather than via the GUI.

You may also have some success monitoring, recording the contents of the Dynamic Management Views (DMV) for things such as query execution statistics.

You'll want to look at DMV's such as:

• sys.dm_exec_query_stats
• sys.dm_exec_sql_text
• sys.dm_exec_query_plan

For example, here is a query that can be used to identify the poorest performing top 20 SQL queries by CPU consumption. Not exactly what you are after but it does demonstrate how to use the DMV's that you would be interested in.

SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
   st.text,
   qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC

I don't think you can write to a DB table, MYSQL can write them to a file and you can write a script to parse the file and insert the queries.