Posts

Showing posts from November, 2016

No se puede borrar base de datos en Single User Mode

En algunas ocasiones una base de datos se pasa a Single User Mode y después no podemos borrarla. Para hacer esto necesitamos primero borrar las conexiones hacia la base de datos y después podemos borrarla usando el siguiente script

SELECT request_session_id FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('nombre_bd')

kill 76

USE Master
ALTER DATABASE nombre_bd SET MULTI_USER

drop database nombre_bd

Introduccion herramientas R con SQL Server

Image
En este video vemos las herramientas con soporte de R de Microsoft y vemos su integración con SQL Server 2016 R Services.

Revision instalacion y monitoreo de R Services

Image
En este video vemos cómo revisar que la instalación de R Services en SQL Server 2016 ha sido correcta, además vemos scripts para monitoreo del desempeño.

Video sobre Instalacion y monitoreo de R Services

Image

SQL Server 2016 R Services Monitoring with DMVs

Image
SQL Server 2016 includes features for running advanced analytics scripts within the database these scripts are based on the R language.In this blog post we will see some DMV that we can use to be able to monitor the performance of our server.

These administrative views give us information about the amount of scripts that are running at this time as well as the duration of each of them, in this way we can determine what is the impact of running scripts of R within our SQL Server.

The following are the most common administrative views for monitoring the performance of R Scripts.

-DMV sys.dm_os_performance_counters

-TotalExecutions:Indicatesthe number of processes initiated locally or remotely-ParallelExecutions:Number of times a script includes a parallelexecution-StreamingExecutions:Number of times the function was used
-SQL CCExecutions:This column indicates the amount of Rscriptsthat were initiated remotely and are utilizorto SQL Server as a context of execution
-TotalexecutionTime(Ms):Thi…

Video Resource Governor con SQL Server 2016 R Services

Image

Using Resource Governor with SQL Server 2016 R Services

SQL Server 2016 includes new advanced analitcs features like R script execution, these features are very useful, however, because those features may use a lot of memory and a lot and a lot of processor it could be the case that all the resources are used by the R Scripts execution.
To control the amount of resources that are allocated to the execution of R scripts you can use the feature known as resource governor.The following T-SQL gets the current configuration of the sql server for the execution and control of resources and resources.
- Check the amount of resources that are assigned by default
SELECT*DESDEsys.resource_governor_resource_poolsWHEREname=Default

The following statute verifies what resources are allocated to the pool external resources. Remember that the R scripts are executed as external scripts so we must use a dedicated pool for that execution
SELECT*DESDEsys.resource_governor_external_resource_pools
WHEREname=Default

Uso de Resource Governor con SQL Server 2016 R Services

SQL Server 2016 incluye nuevas características de análisis avanzado,  dichas características son muy útiles,  sin embargo, debido a que esas características utilizan mucha memoria y mucho  y mucho procesador se podría podría dar el caso que se consumen todos los recursos de una instancia de sql server.
Para controlar la cantidad de recursos que se asignan a la ejecución de scripts de R  se puede utilizar la característica del controlador de recursos conocida como resource governor.  El siguiente estatuto SQL obtiene la configuración actual que tiene el sql server para la ejecución y control de recursos y de los recursos.
-- Se verifica la cantidad de recursos que están asignados por default
SELECT*FROMsys.resource_governor_resource_poolsWHERE name ='default'

El siguiente estatuto verifica Cuáles son los recursos asignados para el pool recursos externos.  recordemos que los estatutos de R  son ejecutados como scripts externos asi que debemos utilizar un pool dedicado para esa ejecu…