Introduction
Hello, a few days ago I was think that even the high usage that I personally use of the xp_readerrorlog (or sp_readererrorlog) stored procedure to review the SQL Error Log, if do an internet search about it you will not find any official references to it. Like this stored procedure there are a few more that are undocumented on Books Online, but they can be helpful on different situations.
¿Why does undocumented store procedures exist?
Some authors think that in many cases there’s no important apparent reason to release documentation about this store procedures, so the users and administrators can use them without problems, or that they shouldn’t be available at all.
While this statement have some logic from the end user, we don’t think it’s not at all right, I have found information about this store procedures and other undocumented tools in general (DBCC commands, Functions, Trace Flags and Startup Options) might be used for testing purposes while building the product or might have been created for a deep debugging by Microsoft technical support.
¿It’s OK to use the undocumented store procedures?
We think that is not by chance or oblivion (like another author might think) that this tool are not documented, some of them might be dangerous when used wrong, and the fact that are not documented also warning about that in any moment (hotfix, patch, service pack or a new version is released) its functionality or behavior might change, or might be totally eliminated, it’s also true that this tools are not test against through and exhaustive tests, and is not recommended at all the use of them at end user applications (should not be referenced on the code).
For an example of how real it’s the risk when we implement this procedures inside the code I found an issue where a server processor was used at a 100%, on this server were a SQL Server 2005 SP3, on this instance an end user process was constantly calling the xp_readerrorlog, the root cause of the problem was a bug related to the execution of this extended store procedure (http://support.microsoft.com/kb/973524).
So we need to understand, that using this undocumented store procedures is at our own risk. However many of them are really useful on our daily activities.
A Few Useful undocumented procedures:
The execution of this store procedure gives us as a result the list of all the subdirectories of a directory specified as an input parameter:
EXECUTE master.sys.xp_dirtree ‘<path>’
Theres another extended store procedure (xp_subdir) that only returns the first level subdirectories, is like using the xp_dirtree with the condition depth = 1.
The execution of this store procedure gives us as a result the list of all the OLE DB for SQL Server:
EXEC master.sys.xp_enum_oledb_providers
The execution of this store procedure gives us as a result the list of files on the SQL Error Log with their last modification date
EXECUTE master.sys.xp_enumerrorlogs
The execution of this store procedure gives us as a result the list of the windows groups and its description
EXECUTE master.sys.xp_enumgroups
The execution of this store procedure gives us as a result the list of the disks (drives) and the amount of free space (on MB) for each of them:
EXEC master.sys.xp_fixeddrives
To finish, he execution of this store procedure gives us as a result the content of the SQL Error Log files, it’s pretty useful when looking for errors on the instances:
EXEC master.sys.xp_readerrorlog
It can receive input parameters:
The store procedure master.sys.sp_readerrorlog makes a call to the xp_readerrorlog, but the difference is that the last one can only receive the first 4 input parameters mention
On Conclusion
As we could see, the procedures presented where mostly extended store procedures, this execute an DDL that runs directly on SQL Server, on many cases these can only be executed by sysadmins.
There are others undocumented stored procedures onSQL Server 2008, if you want to obtain information from all store procedures available on SQL 2009, you can use the below query, and them look which of them appear on the SQL help or not:
SELECT OBJECT_NAME(c.id), c.*
FROM master..syscomments c JOIN master..sysobjects o ON c.id = o.id
WHERE o.type in('X', 'P')
ORDER by 1, 4
*Tip: the intellisense tool, can give you some information on undocumented store procedures and functions, as an example type:
EXECUTE master.sys.sp_re
You’ll see that intellisense will look for the sp_readerrorlog and give you information about the input parameters.
“The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft”
Hola a todos, hace algunos días reflexionaba que pese al uso común que personalmente hago del stored procedure xp_readerrorlog (o bien sp_readerrorlog) para revisar el log de errores de SQL Server, si realizamos una búsqueda del comando en la ayuda de SQL Server no encontraríamos referencias al mismo. Así como este stored hay varios más que no están documentados en la ayuda de SQL Server pero que en un momento dado nos pueden ser útiles para revisar información que necesitemos conocer.
En esta entrada al blog platico un poco acerca de estos “undocumented stored procedures” y presento una lista de algunos que pueden ayudarnos en nuestras actividades diarias.
Algunos autores opinan que en muchos casos no hay una razón importante para que Microsoft no libere documentación acerca de algunos de estos stored procedures con el objetivo de que los administradores y usuarios de SQL Server pudieran utilizarlos sin problemas, o bien, que en todo caso, ninguno de ellos debería estar accesible para su ejecución por parte de los administradores de los sistemas.
La posición anterior, si bien tiene un poco de lógica desde la perspectiva de usuario final, me parece que no es del todo correcta, según lo que he podido investigar muchos de estos stored procedures, y en general otras herramientas no documentadas disponibles en SQL Server (comandos DBCC, Funciones, Trace Flags y Startup Options) pudieron ser utilizados para propósitos de “testing” durante el desarrollo del Producto o bien fueron creados con propósitos de “deep debuggin” para Soporte del mismo.
Creo que no es casualidad u olvido (como algún otro autor opina) que las herramientas mencionadas no estén documentadas, muchas de ellas pueden llegar a ser “peligrosas” en su utilización, el que no estén documentadas también nos advierte del hecho que en cualquier momento (con la liberación de algún hotfix, parche de seguridad, service pack o bien una nueva versión del producto) puede ser cambiada su funcionalidad, el comportamiento de su ejecución o definitivamente ser eliminadas, es también muy probable que la funcionalidad de cada uno de ellos no este revisada con exhaustivos casos de prueba, así que definitivamente no es recomendable su utilización por parte de usuarios finales en sus aplicaciones (no deben incluirse referencias a ellos en el código), tampoco debe hacerse dependiente algún proceso de su ejecución.
Para poner un ejemplo de que tan real es el riesgo al que se expone alguno de nuestros clientes que implemente dentro de su código llamadas a alguno de estos procedimientos, basta mencionar que mientras preparaba esta entrada al blog, me enteré de que el uso de procesador de un server se iba a rangos del 100%, en tal servidor está instalada una instancia de SQL Server 2005 SP3, y en dicha instancia se estaba ejecutando un proceso del cliente que hace una llamada al xp_readerrorlog precisamente, es muy probable, de acuerdo al escenario que se presenta, que la causa raíz del problema sea un bug relacionado a la ejecución del extended stored procedure mencionado (http://support.microsoft.com/kb/973524).
Por tanto debe entenderse que, el posible uso que hagamos de cada uno de los stored procedures no documentados, es bajo nuestro propio riesgo, sin embargo como ya he mencionado antes, algunos de ellos pueden ser altamente útiles en nuestras actividades diarias.
A continuación menciono algunos procedimientos no documentados que nos pueden ser de utilidad:
La ejecución de este extended stored procedure nos da como resultado una lista de todas las sub-carpetas de una carpeta especificada como parámetro de entrada:
EXECUTE master.sys.xp_dirtree '<path>'
Existe otro extended stored procedure (xp_subdirs) que sólo nos regresa las sub-carpetas del primer nivel, es decir, es el resultado de xp_dirtree con la condición depth = 1.
El resultado de su ejecución es la lista de todos los proveedores de OLE DB disponibles para SQL Server:
Su ejecución nos regresa una lista de todos los archivos de log de SQL Server (error logs) con su última fecha de modificación:
Al ejecutarse regresa a lista de los grupos de Windows y su descripción:
Regresa una lista de todos los discos (drives) y el monto de espacio libre (en MB) para cada uno de ellos:
Para terminar, como ya había mencionado, uno de los que más uso. Regresa el contenido de los archivos de log de errores de SQL Server, me parece muy útil así cuando buscamos errores que están ocurriendo en las instancias:
Puede recibir hasta siete parámetros:
i. Valor del archivo de log de errores que deseas leer: 0 = actual, 1 = Archivo #1, 2 = Archivo #2, etc…
ii. Tipo de archivo de log de errores que quieres leer: 1 or NULL = Error Log, 2= SQL Agent Log
iii. Cadena de búsqueda 1: Cadena de caracteres para condicionar los resultados mostrados, es decir un “string” que estés buscando.
iv. Cadena de búsqueda 2: Cadena de caracteres secundaria para realizar una búsqueda mucho más precisa.
v. Fecha/Hora de inicio de la búsqueda: Se buscará a partir de la fecha y hora indicada en este parámetro.
vi. Fecha/Hora de fin de la búsqueda: Se buscará y desplegarán resultados (si los hubiera) hasta la fecha y hora especificadas en este parámetro.
vii. Ordenamiento para los resultados: N'asc' = ascendente, N'desc' = descendente.
El stored procedure master.sys.sp_readerrorlog hace una llamada a xp_readerrorlog pero a diferencia de éste sólo puede recibir los primeros cuatro parámetros mencionados.
Como pudimos darnos cuenta, los stored procedures que presento como ejemplo son extended estored procedures, recordemos que éstos son una ejecución de una “dynamic link library” que corre directamente dentro de SQL Server, en la mayoría de los casos estos extended stored procedures sólo pueden ser ejecutados por usuarios con privilegios de sysadmin.
Existen más stored procedures no documentados en SQL Server 2008, si se desea obtener la información de los stored procedures disponibles en nuestra instancia de SQL Server 2008 y de allí buscar cuales aparecen en la ayuda y documentación del producto y cuáles no, podemos usar la siguiente consulta:
*Como curiosidad: La herramienta de IntelliSense (nueva para SQL Server 2008) puede llegar a presentar información de algunos stored procedures no documentados (también de algunas funciones), como ejemplo puede probar escribiendo:
verá que IntelliSense ubica el stored sp_readerrorlog (aunque no aparece referencia a éste en la ayuda de SQL Server 2008) y te da información acerca del número de parámetros que puede recibir
“Las opiniones e ideas expresadas en este blog son las de los Autores y no necesariamente declaran o reflejan la opinión de Microsoft”
Este material tambien lo podras acceder en http://blogs.technet.com/b/sql_pfe_latam/
From SQL Server 2005 the SQLOS was implemented don SQL Server. We can see the SQLOS as a layer used by SQL Server to interact with the Operating System.
The need to have the SQLOS on SQL Server is given by the need to adapt to changes at the hardware and architecture levels. Windows is defined as a multipurpose Operating System, which allow running and working with as many and different types of applications, but this is not the best scenario for SQL. This is why SQLOS is introduce, to allow SQL to have some kind of degree of control on the platform that is running. SQLOS will manage all the SQL parts from a central location, allowing a more efficient way to work.
SQLOS mange many SQL systems and have many functions assigned, like:
SQLOS structure on Memory Management:
SQLOS is built by many components, like: Memory Nodes, Memory Clerks, Memory Caches and Memory Objects.
Since now why have explain the internal structure of SQLOS related to memory management.
Now let’s see another part of the memory management.
Buffer Pool:
SQL Server organize the memory assign to i ton 2 different regions: Buffer Pool and MenToLeave (or reserved memory). If you were using AWE, them it would be 3 regions, been the last the space use by memory assigns per AWE. The Buffer Pool it’s the most relevant of the 3.
The Buffer Pool is the cache for the Databases which are filled by the Buffer Manager, who is in charge to Access and update the data pages. Of course, is preferable to access the data pages on the memory cache instead of accessing at Disk, because IO operations can consume many resources and take a relative long time to finish, this is why we should use the most possible amount of memory possible to have a good performance on cache. If you’ll like to control the amount of memory used by the buffer pool you can use the configurations max server memory and min server memory, this configurations control the size of the buffer pool and NOT the amount of memory used by SQL Server as is the common belief.
When SQL Server is starting, it reserve the MenToLeave section, it try to do it on a contiguous address range of VAS (Virtual Address Space), then it decide the max size for the Buffer Pool. The default size for the MenToLeave region is 384 Mb, where 128Mb are for the worker thread stacks and 256Mb are for memory allocations outside of the Buffer Pool.
MemToLeave = Reserved Memory + (max worker threads * worker thread stack size)
The Worker thread stack is 512Kbfor 32 bits systems, 2Mb for 64 bits systems and 4Mb for IA64 systems; the number of threads is configured on max worker threads, on SQL 2005 and forward 0 is the default. The number of worker threads will depend on the number of CPUs, for the example will use 256 worker threads on a 32 bit system:
MemToLeave = 256Mb + (256 * 0,5Mb) => MemToLeave = 384Mb
The Reserved Memory size can be modified using the startup parameter –g. if we use the parameter –g512, we’ll reserve 512Mb making the MenToLeave 640Mb
The Buffer Pool assigns pages when they are requested, depending on the internal and external requirements. The size of a database page is 8Kb, so the pages are assign and drop on 8Kb portions to the Buffer Pool, this is the same size used to assign pages on the Memory Nodes. On this point is where SQLOS and the Buffer Pool converge, the memory node provides localization for the page, which is assigned through a single page allocator on the buffer pool.
Memory Node ---> Single Page Allocator ---> Buffer Pool
All SQL Server components are optimized to assign 8Kb of memory allocations, so they can make allocations using the single page allocator of SQLOS and in consequence through the Buffer Pool. However there are cases where there’s a need for bigger allocation, this will use a different cache, on those cases you’ll need to use different allocators like: Multi Pages Allocators and Virtual Page Allocators, they will allocate memory outside of the Buffer Pool, using the reserved memory region of the MenToLeave.
When AWE is used, the allocations are going to be done through this mechanism and not through a single page allocator. The API used by AWE creates a region on the Buffer Pool where reference views to the physical memory are going to be stored.
A partir de SQL 2005 se implementa SQLOS. Podemos ver a SQLOS como una capa (Modo Usuario) por al cual SQL interacciona con el Sistema Operativo.
La necesidad de tener al SQLOS en SQL viene dada por los continuos cambios a nivel hardware y arquitectura. Windows es definido como un Sistema Operativo multipropósito, el cual permite correr y trabajar con muchas y diferentes aplicaciones, sin embargo este no es el escenario ideal para SQL. Es por esto que se introduce SQLOS, para darle a SQL cierto grado de control sobre la plataforma en la que corre. Esto lleva a que SQLOS maneje todos los sistemas de SQL de manera centralizada, permitiendo trabajar de manera más eficiente.
SQLOS tiene muchas funciones asignadas (o sistemas que maneja), estas son algunas:
Estructura del manejo de memoria de SQLOS:
SQLOS está compuesto por varios componentes como: nodos de memoria (memory nodes), clerks de memoria (memory clerks), caches de memoria (memory cache) y objetos de memoria (memory objects).
Hasta acá vimos la estructura interna de SQLOS en lo que refiere a manejo de memoria.
Ahora veamos otra parte del manejo de memoria.
Buffer pool:
SQL Server organiza la memoria que el mismo se asigna en 2 regiones distintas: Buffer Pool y MemToLeave (o memoria reservada). Si se hiciera uso de AWE, serían entonces 3 las regiones, siendo esta última el espacio de memoria asignado por AWE. El Buffer Pool es la región más destacada de las 3.
El Buffer Pool es el cache de las Bases de Datos el cual es llenado por el Buffer manager que se encarga de acceder y actualizar los datos de las páginas. Es preferible que los datos existan en el cache de memoria en vez de tener que operar con el Disco rígido ya que las operaciones de E/S pueden consumir muchos recursos y tomar un tiempo relativamente alto en terminar, es por esto que la meta es usar la mayor cantidad de memoria posible para tener una buen desempeño del cache. Para controlar la cantidad de memoria usada por el Buffer Pool se utilizan las configuraciones de memoria en SQL: max server memory y min server memory. Estas configuraciones controlan el tamaño que puede llegar a tener el Buffer pool y no la cantidad total de memoria que va a utilizar SQL Server como se cree.
Al momento de iniciar SQL server, reserva la región de MemToLeave intentando hacerlo en un rango de direcciones contiguos de VAS (Virtual Address Space) y luego el Buffer Pool decide el tamaño máximo que va a tener. Por defecto el tamaño de la región de MemtoLeave es de 384Mb, siendo 128Mb para stacks de worker threads y 256Mb para asignaciones fuera del Buffer Pool.
El tamaño del worker thread stack es de 512Kb para sistemas de 32 bits, 2Mb para sistemas de 64 bits y 4Mb para IA64; la cantidad de threads se encuentra configurado en max worker threads, en 0 por defecto en SQL 2005 y en adelante,la cantidad de worker threads dependerá del número de procesadores. Para el ejemplo usaremos 256 worker threads en un sistema de 32 bits, Entonces la cuenta queda:
Reserved Memory puede modificar su tamaño si utilizamos el parámetro de inicio –g. Si pasamos el parámetro –g512, Reserved Memory toma un tamaño de 512Mb haciendo que MemToLeave cambie de tamaño a 640Mb.
El Buffer Pool asigna páginas a medida que se solicita, dependiendo de requerimientos internos y externos. El tamaño de página de una Base de Datos es de 8Kb, por consiguiente las páginas se asignan y se quitan con tamaños de 8Kb en el Buffer Pool, coincidentemente con el mismo tamaño en que se asignan las páginas por los Nodos de memoria. En este punto es donde convergen SQLOS y el Buffer Pool, el nodo de memoria provee la localización para una página, la cual es asignada por medio de un asignador de página simple en el Buffer Pool:
Nodo de memoria ---> Asignador de página simple ---> Buffer Pool
Todos los componentes de SQL server se encuentran optimizados para asignaciones de 8Kb de memoria, de manera que pueden hacer asignaciones de memoria a través del asignador de páginas simple de SQLOS y por consiguiente a través del Buffer Pool. Sin embargo hay casos en los que se necesitan hacer asignaciones que requieren de un cache más grande, en ese caso, se van a usar otros asignadores como: asignadores de páginas múltiples (Multi-Page Allocators) y asignadores de páginas virtuales (Virtual Page Allocators), por supuesto, la memoria asignada va a estar fuera del Buffer Pool, haciéndose la asignación en la región de MemToLeave en la parte de Reserved Memory.
Cuando AWE se encuentra presente, las asignaciones se van a realizar a través de este mecanismo y no a través del asignador de páginas simple. Las API que usa AWE crean una región en el Buffer Pool en que se van a ver vistas que referencian memoria física, siendo allí donde se guardan las páginas, por ende es lógico que las asignaciones se realicen a través de AWE.