Monitorizacion/trazas-Auditing.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Rendimiento de utilizar trazas de SQL Server vs Extended Events\n",
                "\n",
                "- la fuente real es esta, pero la web ha desaparecido: http://sqlblog.com/blogs/linchi\\_shea/archive/2012/01/24/performance-impact-sql2008-r2-audit-and-trace.aspx\n",
                "    - alternativamente en archive.org hay una copia: [Linchi Shea : Performance impact: SQL2008 R2 audit and trace (archive.org)](https://web.archive.org/web/20120211185134/http://sqlblog.com/blogs/linchi_shea/archive/2012/01/24/performance-impact-sql2008-r2-audit-and-trace.aspx)\n",
                "- No encuentro publicaciones recientes de Linchi: este libro es suyo: [Real World SQL Server Administration with Perl : Shea, Linchi: Amazon.es: Libros](https://www.amazon.es/Real-World-Server-Administration-Perl/dp/159059097X)\n",
                "- Coincidimos en el programa MVP. En mi opinión, el, y Joe Chang son las dos personas que mejor conocían el producto.\n",
                "\n",
                "  \n",
                "\n",
                "AQUI EL COPIA / PEGA de supublicación para referencias."
            ],
            "metadata": {
                "azdata_cell_guid": "8cf1a021-7c87-4e11-a147-ff45b5590567"
            },
            "attachments": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Performance impact: SQL2008 R2 audit and trace\n",
                "\n",
                "We are told that SQL Server 2008 R2 Audit (and SQL Server 2008 Audit) has much less performance overhead than SQL Trace when we try to capture the same information. Knowing how SQL Server R2 Audit is implemented (i.e. on top of the extended events infrastructure), I’ve always taken that as a given and never bothered to check it out.\n",
                "\n",
                "Recently, I had to capture some object access information, and it turned out that SQL Audit was not the most convenient tool for the task. I had to go back to SQL Trace. There, the question of how much cheaper is an audit relative to an equivalent trace came up. I thought it’s time to explore that a bit.\n",
                "\n",
                "I wanted to explore two questions:\n",
                "\n",
                "- In the most extreme case (i.e. when the processors are heavily loaded), how much degradation in transaction throughput should I expect if I turn on SQL Audit vs. if I turn SQL Trace?\n",
                "- If the processors are moderately loaded, should I expect a performance degradation if I turn on SQL Audit or SQL Trace?\n",
                "\n",
                "The first question is the worst case scenario you probably should never run into. But it’s always good to know where the boundary is. The second question is a realistic and more common scenario.\n",
                "\n",
                "The processor load levels were controlled with my trusty TPC-C workload driver as follows:\n",
                "\n",
                "- **Heavy CPU load**. This was generated by 100 simulated users submitting the standard TPC-C readonly queries without any wait between any two consecutive calls. The total processor time on the 12-core (or 24-logical-processor) server was sustained at ~92%, and the transaction throughput (as measured by one of the TPC-C transactions) was steady at ~16,000 per second or about 32,000 batch requests per second on the SQL Server 2008 R2 instance.\n",
                "- **Medium CPU load**. This was generated by 100 simulated users submitting the standard TPC-C readonly queries with a 10ms wait between two consecutive calls. Compared to the previous load scenario, this otherwise rather small wait caused the total processor time on the server to sustain at a very low level at about 2%. In a way, it may be more appropriate to label this a light load level. But the sustained transaction throughput was about 3,100 per second or 6,200 batch requests per second, which still kept SQL Server busy looking up the pages.\n",
                "\n",
                "For each of the above two load levels, three conditions were tested:\n",
                "\n",
                "- **No Audit/No Trace**. In this case, I did not enable any SQL Audit session or run any SQL Trace. This was our baseline.\n",
                "- **With Audit**. In this case, I turned on a standard audit with 1000ms queue delay and the output was sent to files with each file capped at 500MB. The database audit specification included all the SELECT statements on all the TPC-C user tables. No SQL Trace was enabled.\n",
                "- **With Trace**. A SQL Trace that was more or less equivalent to the audit described above was started with the trace results sent to the same drive folder as in the above audit. The trace files were also specified to rollover to a new one ever 500MB. No SQL audit was enabled.\n",
                "\n",
                "The SQL script to create the audit and the database audit specification is listed below:\n",
                "\n",
                "```\n",
                "USE [master]\n",
                "GO\n",
                "CREATE SERVER AUDIT [Audit]\n",
                "TO FILE \n",
                "(FILEPATH = N'E:\\'\n",
                ",MAXSIZE = 500 MB\n",
                ",MAX_ROLLOVER_FILES = 2147483647\n",
                ",RESERVE_DISK_SPACE = OFF\n",
                ")\n",
                "WITH\n",
                "(QUEUE_DELAY = 1000\n",
                ",ON_FAILURE = CONTINUE\n",
                ",AUDIT_GUID = '5501f383-4357-42f5-a9c4-c87a66ecc08b'\n",
                ")\n",
                "GO\n",
                "USE [sqlTestC]\n",
                "GO\n",
                "CREATE DATABASE AUDIT SPECIFICATION [DBAudit]\n",
                "FOR SERVER AUDIT [Audit]\n",
                "ADD (SELECT ON OBJECT::[dbo].[warehouse] BY [public]),\n",
                "ADD (SELECT ON OBJECT::[dbo].[district]  BY [public]),\n",
                "ADD (SELECT ON OBJECT::[dbo].[customer]  BY [public]),\n",
                "ADD (SELECT ON OBJECT::[dbo].[history]   BY [public]),\n",
                "ADD (SELECT ON OBJECT::[dbo].[new_order] BY [public]),\n",
                "ADD (SELECT ON OBJECT::[dbo].[orders]    BY [public]),\n",
                "ADD (SELECT ON OBJECT::[dbo].[item]      BY [public]),\n",
                "ADD (SELECT ON OBJECT::[dbo].[stock]     BY [public])\n",
                "WITH (STATE = ON)\n",
                "GO\n",
                "```\n",
                "\n",
                "And the SQL script to create the SQL trace is pasted below:\n",
                "\n",
                "```\n",
                "declare @rc int\n",
                "declare @TraceID int\n",
                "declare @maxfilesize bigint\n",
                "set @maxfilesize = 500\n",
                "exec @rc = sp_trace_create @TraceID output, 2, N'E:\\Audit_Trace', @maxfilesize, NULL \n",
                "if (@rc != 0) goto error\n",
                "declare @on bit\n",
                "set @on = 1\n",
                "exec sp_trace_setevent @TraceID, 45, 7, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 55, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 8, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 16, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 48, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 64, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 1, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 9, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 17, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 25, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 41, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 49, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 10, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 18, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 26, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 34, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 50, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 66, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 3, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 11, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 35, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 51, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 4, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 12, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 28, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 60, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 5, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 13, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 29, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 61, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 6, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 14, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 22, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 62, @on\n",
                "exec sp_trace_setevent @TraceID, 45, 15, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 7, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 15, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 55, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 48, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 64, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 1, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 9, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 25, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 41, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 49, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 26, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 50, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 66, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 3, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 11, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 35, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 51, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 4, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 12, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 60, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 5, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 13, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 29, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 61, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 6, @on\n",
                "exec sp_trace_setevent @TraceID, 41, 14, @on\n",
                "declare @intfilter int\n",
                "declare @bigintfilter bigint\n",
                "exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 95a2ce5b-6ec1-4d27-a667-fb86601af54a'\n",
                "exec sp_trace_setstatus @TraceID, 1\n",
                "select TraceID=@TraceID\n",
                "goto finish\n",
                "error: \n",
                "select ErrorCode=@rc\n",
                "finish: \n",
                "go\n",
                "\n",
                "```\n",
                "\n",
                "The test results are summarized in the next two charts.\n",
                "\n",
                "[![audit_performance_heavy_load](https://web.archive.org/web/20120211185134im_/http://sqlblog.com/blogs/linchi_shea/audit_performance_heavy_load_thumb_52163692.png)](https://web.archive.org/web/20120211185134/http://sqlblog.com/blogs/linchi_shea/audit_performance_heavy_load_7DC7008E.png)\n",
                "\n",
                "With all the CPUs on the server heavily loaded, turning on SQL Audit to log all the SELECT statements caused the transaction throughput to drop from 16,043 per second to 13,825 per second. That was about 14% drop. Given how heavy the load was and how busy the traffic was on the server, that’s a relatively small drop. Now the impact of turning on the trace was quite a different story. There, the transaction throughput went from 16,043 per second to a mere 5,012 per second. That was a 60% drop. if we use 5012 as the base reference, turning off the trace increased the transaction throughput for more than 300%. The difference in terms of performance impact was striking between SQL Audit and SQL Trace.\n",
                "\n",
                "Again, I should note that this was an extreme case. In a real world situation, most likely the system wouldn’t be this busy. So the 14% drop in throughput is probably the ceiling, and you should probably expect much less an adverse impact by turning on a similar audit on a real production server. This next chart shows the result of turning on an audit (and separately a trace) when we don’t beat up the server with non-stop transaction traffic. As you can see, by just giving the server a little bit breathing room with a 10ms wait between the calls, the load on the server was reduced significantly and turning on either the audit or the trace had no impact at all on the throughput. Yes, the total CPU consumption went up a little bit as a result of turning on the audit or the trace, but the difference was rather insignificant.\n",
                "\n",
                "[![audit_performance_medium_load](https://web.archive.org/web/20120211185134im_/http://sqlblog.com/blogs/linchi_shea/audit_performance_medium_load_thumb_28A1F552.png)](https://web.archive.org/web/20120211185134/http://sqlblog.com/blogs/linchi_shea/audit_performance_medium_load_4D3382D6.png)\n",
                "\n",
                "These test results are good news for the SQL Server 2008 R2 audit feature. Now, only if it could be made a bit [more useful](https://web.archive.org/web/20120211185134/http://sqlblog.com/blogs/linchi_shea/archive/2011/10/07/a-quick-look-at-sql-server-audit-in-denali-ctp3.aspx)!\n",
                "\n",
                "The test environment was a DL360 G7 with two X5690 (6 core) processors and 64GB of RAM. Hyperthreading was enabled. The OS was Windows 2008 R2 Enterprise x64 Edition with SP1 and DBMS was SQL Server 2008 R2 Enterprise x64 Edition (10.50.1600).\n",
                "\n",
                "Published Tuesday, January 24, 2012 11:29 PM by [Linchi Shea](https://web.archive.org/web/20120211185134/http://sqlblog.com/user/Profile.aspx?UserID=2236)"
            ],
            "metadata": {
                "azdata_cell_guid": "c774c84e-113f-4662-9f48-ba540ea16981"
            },
            "attachments": {}
        }
    ]
}