Deshabilitar todos los Jobs
declare @sql nvarchar(max) = ''; select @sql += N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0; ' from msdb.dbo.sysjobs where enabled = 1 order by name; print @sql; exec (@sql);
Los números de 2014
Los duendes de las estadísticas de WordPress.com prepararon un informe sobre el año 2014 de este blog.
Aquí hay un extracto:
Un tren subterráneo de la ciudad de Nueva York transporta 1.200 personas. Este blog fue visto alrededor de 4.700 veces en 2014. Si fuera un tren de NY, le tomaría cerca de 4 viajes transportar tantas personas.
Script para crear de forma dinámica PIVOT queries en SQL Server
Invocar un SOAP Web Services desde un Stored Procedure
En este ejemplo creamos el Stored Procedure : spHTTPRequest quien va a encargarse de ejecutar el Web Service que pasemos por parametro, devolviendo el XML de respuesta.
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE proc [dbo].[spHTTPRequest]
@URI varchar(2000) = '',
@methodName varchar(50) = '',
@requestBody varchar(8000) = '',
@SoapAction varchar(255),
@UserName nvarchar(100), -- Domain\UserName or UserName
@Password nvarchar(100),
@responseText varchar(8000) output
as
SET NOCOUNT ON
IF @methodName = ''
BEGIN
select FailPoint = 'Method Name must be set'
return
END
set @responseText = 'FAILED'
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255)
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'Create failed',
MedthodName = @methodName
goto destroy
return
END
-- open the destination URI with Specified method
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'Open failed',
MedthodName = @methodName
goto destroy
return
END
-- set request headers
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @methodName
goto destroy
return
END
-- set soap action
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @methodName
goto destroy
return
END
declare @len int
set @len = len(@requestBody)
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @methodName
goto destroy
return
END
-- send the request
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'Send failed',
MedthodName = @methodName
goto destroy
return
END
declare @statusText varchar(1000), @status varchar(1000)
-- Get status text
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
exec sp_OAGetProperty @objectID, 'Status', @status out
select @status, @statusText, @methodName
-- Get response text
exec sp_OAGetProperty @objectID, 'responseText', @responseText out
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'ResponseText failed',
MedthodName = @methodName
goto destroy
return
END
destroy:
exec sp_OADestroy @objectID
SET NOCOUNT OFF
Este es el ejemplo de llamada para un determinado Web-Service de descarga de cartera de RSA.
DECLARE @xmlOut VARCHAR(8000)
DECLARE @RequestText AS VARCHAR(8000);
SET @RequestText=
'<soap:Envelope xmlns:xsi=''http://www.w3.org/2001/XMLSchema-instance'' xmlns:xsd=''http://www.w3.org/2001/XMLSchema'' xmlns:soap=''http://schemas.xmlsoap.org/soap/envelope/''>
<soap:Body>
<DescargarCarteraRSA xmlns=''http://tempuri.org/'' />
</soap:Body>
</soap:Envelope>'
EXEC spHTTPRequest
'http://200.58.107.204/wsIntegracion/service.asmx',
'POST',
@RequestText,
'http://tempuri.org/DescargarCarteraRSA',
'', '', @xmlOut OUT
Script para obtener todos los Convert Implicit
Entre todos los temas que producen degradación de performance, están los Convert Implicit.
Muchas veces, los desarrolladores omiten revisar correctamente los tipos de datos al momento de programar un stored procedure o función.
También los arquitectos y quizas, DBA’s poco experimentados, realizan cambios en el diseño físico de ciertas tablas sin revisar correctamente los objetos relacionados a la misma.
El siguiente script, detalla todos los Convert Implicit de la base de datos y su Execution Plan para verificar el costo del mismo una vez solucionado el problema.
Espero les sirva de ayuda y referencia.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
stmt.value('(@StatementText)[1]', 'varchar(max)'),
t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'),
t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'),
t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'),
ic.DATA_TYPE AS ConvertFrom,
ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo,
t.value('(@Length)[1]', 'int') AS ConvertToLength,
query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
JOIN INFORMATION_SCHEMA.COLUMNS AS ic
ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1
Listar todos los Jobs de SQL Server junto a la cantidad de Steps que cada uno posee.
Consulta rápida para obtener esta información.
SELECT DISTINCT substring(a.name,1,100) AS [Job Name],
COUNT(t.step_id) AS Steps,
'Enabled'=case
WHEN a.enabled = 0 THEN 'No'
WHEN a.enabled = 1 THEN 'Yes'
end,
substring(b.name,1,30) AS [Name of the schedule],
'Frequency of the schedule execution'=case
WHEN b.freq_type = 1 THEN 'Once'
WHEN b.freq_type = 4 THEN 'Daily'
WHEN b.freq_type = 8 THEN 'Weekly'
WHEN b.freq_type = 16 THEN 'Monthly'
WHEN b.freq_type = 32 THEN 'Monthly relative'
WHEN b.freq_type = 32 THEN 'Execute when SQL Server Agent starts'
END,
'Units for the freq_subday_interval'=case
WHEN b.freq_subday_type = 1 THEN 'At the specified time'
WHEN b.freq_subday_type = 2 THEN 'Seconds'
WHEN b.freq_subday_type = 4 THEN 'Minutes'
WHEN b.freq_subday_type = 8 THEN 'Hours'
END
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysJobschedules c ON a.job_id = c.job_id
INNER JOIN msdb.dbo.SysSchedules b on b.Schedule_id=c.Schedule_id
INNER JOIN msdb.dbo.sysjobsteps t on a.job_id=t.job_id
GROUP BY substring(a.name,1,100), a.enabled, substring(b.name,1,30), b.freq_type, b.freq_subday_type
La presentación ‘Tema 06: Introducción a la Programación Concurrente MAESTRÍA EN INGENIERIA DE SISTEMAS E INFORMÁTICA ADMINISTRACIÓN DE TECNOLOGÍAS DE INFORMACIÓN E S.’
Generar total de objetos por tipo de una base de datos.
Muchas veces tuvimos que obtener para informes estadísticos, el total de objetos que componen una base de datos especifica.
Aquí esta el script que puede facilitarnos esta tarea al momento de ser solicitada.
SELECT
CASE TYPE
WHEN 'U'
THEN 'User Defined Tables'
WHEN 'S'
THEN 'System Tables'
WHEN 'IT'
THEN 'Internal Tables'
WHEN 'P'
THEN 'Stored Procedures'
WHEN 'PC'
THEN 'CLR Stored Procedures'
WHEN 'X'
THEN 'Extended Stored Procedures'
END,
COUNT(*)
FROM SYS.OBJECTS
WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X')
GROUP BY TYPE
UNION
SELECT 'Indexes', COUNT(*) FROM sys.indexes
Identificar indices no usados
En algunas oportunidades de negocio, y en ciertas tareas, los desarrolladores crean nuevos indices sin investigar si realmente los indices existentes son suficientes para responder su consulta en tiempo y forma.
Ocasionalmente en lugar de modificar sus consultas para que usen alguno de los indices existentes observando el plan de ejecución, crean indices idénticos, o casi iguales.
La siguiente consulta no detecta la duplicidad de indices, el SQL Server se encarga solo de tomar el indice indicado para cada consulta, y en caso de que existiera alguno duplicado, el motor descarta a uno de los dos de manera natural.
La consulta para identificar estos casos es la siguiente:
SELECT
sch.name + '.' + tbl.name AS [Table Name],
ind.name AS [Index Name],
ind.index_id AS [Index_id],
ind.type_desc,
ISNULL(user_seeks + user_scans
+ user_lookups,0) AS [Total Reads
ISNULL(user_updates,0) AS [Total Writes],
ius.last_user_seek,
ius.last_user_scan ,
ius.last_user_lookup,
(ps.reserved_page_count*8.0)/1024 AS [SpaceUsed_MB]
FROM sys.indexes AS ind
FULL OUTER JOIN sys.dm_db_index_usage_stats AS ius
ON ius.object_id = ind.object_id
AND ius.index_id = ind.index_id
AND ius.database_id = db_id()
AND objectproperty(ius.object_id,'IsUserTable') = 1
INNER JOIN sys.tables AS tbl
ON ind.object_id = tbl.object_id
INNER JOIN sys.schemas AS sch
ON tbl.schema_id = sch.schema_id
LEFT OUTER JOIN sys.dm_db_partition_stats AS ps
ON ind.index_id = ps.index_id
AND ind.object_id = ps.object_id
--WHERE ius.object_id = object_id('InvoicingUnits')
WHERE ISNULL(user_seeks + user_scans
+ user_lookups,0) = 0
ORDER BY [Table Name], [index name]
Espero les sirva.
Saludos!.
Obtener el promedio de Fragmentacion de los Datafiles de una base de datos
Mediante esta consulta, podemos obtener el promedio de fragmentacion total que cada Datafile posee en nuestra base de datos.
DECLARE @FragmentationDB AS TABLE ([Row] SMALLINT, Fragmentation MONEY)
INSERT INTO @FragmentationDB
SELECT ROW_NUMBER()
OVER (ORDER BY CAST(1.00*((CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))-(CONVERT(DECIMAL(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)))) / (CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2))) *100 AS DECIMAL(6,2))) AS Row,
AVG(CAST(1.00*((CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))-(CONVERT(DECIMAL(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)))) / (CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2))) *100 AS DECIMAL(6,2))) AS Fragmentation
FROM sysfiles a
INNER JOIN sys.database_files b ON a.fileid=b.file_id
WHERE CAST(1.00*((CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))-(CONVERT(DECIMAL(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)))) / (CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2))) *100 AS DECIMAL(6,2)) != 0
GROUP BY CAST(1.00*((CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))-(CONVERT(DECIMAL(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)))) / (CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2))) *100 AS DECIMAL(6,2))
--- Uncomment for Detail
--SELECT * FROM @FragmentationDB
SELECT SUM(Fragmentation) / @@ROWCOUNT AS AvgDatabaseFragmentation
FROM @FragmentationDB
En caso de que queramos ver en detalle, cual es dicho porcentaje por cada Datafile, simplemente tenemos que descomentar la siguiente linea:
--SELECT * FROM @FragmentationDB
Tips de SQL Server y fuera de la oficina... 