¿Para que es la base de datos tempdb?
¿Para que es la base de datos tempdb?
En un día de trabajo, mi quería amiga Vivi(pequeña saltamontes), pregunto que que era eso de tempdb, para que sirve y si tiene impacto en las consulta de la base de datos, pues me tome la tarea de escribir un poco para aclararle, espero y mi explicación sea suficiente, entonces comencemos con el cuento ?
La base de datos tempdb es un espacio de trabajo temporal. Entre otros usos, SQL Server emplea tempdb para: Almacenamiento de tablas temporales creadas explícitamente. Tablas de trabajo que contienen resultados intermedios creados durante el procesamiento y ordenación de consultas., en pocas palabras cuando ejecutamos una consulta en el SSMS, esta pasa por el tempdb, por ende de hay su importancia, por ende al instalar SQL Server debemos de configurar la base de datos para un buen funcionamiento
Como funciona la base de datos tempdb
Dicen que una imagen vale por mil palabras, en la imagen superior podemos observar que si tenemos un único archivo en .ldf en la base de datos y realizan múltiples querys en nuestra base de datos, tendremos un típico cuello de botella
Query–Para que es la fila ?,
Servidor–para la base de datos tempdb
Query– Enserio, y no hay otra puerta?
Servidor– Nop el dueño muy tacaño no contrato un DBA para realizar el diseño correcto
Moraleja el instalar siguiente—siguiente no es correcto en este caso
Pero ahora queridos lectores hablando seriamente, esto tiene arreglo, debemos realizar una serie de pasos, para que nuestra base de datos pueda dar signos de vida
Consultar cuantos procesadores tiene nuestro servidor, para lo cual usaremos la siguiente consulta sql
SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info
Crear un Data file por cada procesador lógico que exista en nuestro servidor, para esto validaremos cuantos DataFiles existen actualmente
select DB_NAME(mf.database_id) database_name
, mf.name logical_name, mf.file_id
, CONVERT (DECIMAL (20,2)
, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN ‘Yes’
ELSE ‘No’
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + ‘%’
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ‘ MB’
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2)
,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2)
, (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, physical_name from sys.master_files mf
where database_id =2 and type_desc= ‘rows’
Establezca el incremento de crecimiento de archivos en un tamaño razonable para evitar que los archivos de la base de datos tempdb crezcan en un porcentaje demasiado pequeño. Si el crecimiento de los archivos es demasiado pequeño comparado con la cantidad de datos que se escriben en tempdb, es posible que sea necesario expandir tempdb constantemente. Esto afectará al rendimiento.
Se recomienda seguir estas directrices generales al establecer el incremento de FILEGROWTH para archivos de tempdb.
|
Tamaño de archivos de tempdb
|
Incremento de FILEGROWTH
|
|---|---|
|
De 0 a 100 MB
|
10 MB
|
|
De 100 a 200 MB
|
20 MB
|
|
200 MB o superior
|
10%*
|
Podría ser necesario ajustar este porcentaje en función de la velocidad del subsistema de E/S en que estén ubicados los archivos de tempdb. Para evitar que se produzcan tiempos de espera de bloqueo temporal, se recomienda limitar la operación de crecimiento automático a unos dos minutos. Por ejemplo, si el subsistema de E/S puede inicializar un archivo a 50 MB por segundo, se deberá establecer el incremento de FILEGROWTH en 6 GB como máximo, independientemente del tamaño de los archivos de tempdb.
Siempre que sea posible, use la inicialización de archivos de base de datos instantánea para mejorar el rendimiento de las operaciones de crecimiento automático.
Recomendaciones
- Asigne a cada archivo de datos el mismo tamaño para obtener un rendimiento óptimo de relleno proporcional.
- Coloque la base de datos tempdb en un subsistema de E/S rápido. Cree bandas en disco si hay muchos discos conectados directamente.
- Coloque la base de datos tempdb en discos diferentes de los que utilizan las bases de datos de usuario.
SQListo!!!
Ahora, estoy listo para configurar mi tempdb?
Pequeños saltamontes paciencia debemos analizar primero como crece nuestras base de datos, para saber que tamaño seria correcto, con esto me refiero que el tiempo de crecimiento de nuestros archivos datafile no deben durar mas de 1 minuto al expandirse, se preguntaran y como se esto?
Crear una tabla para guardar la información
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE [dbo].[TempDBFileSize] ( [TFSID] [int] IDENTITY(1, 1) NOT NULL ,[FileID] [int] NULL ,[File_Logical_Name] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State_Desc] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Type_Desc] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Default_File_SizeMB] [int] NULL ,[ActualKB] [int] NULL ,[ActualMB] [int] NULL ,[File_MaxSize] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[File_Growth] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Growth_Type] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Physical_File_Name] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[DateCaptured] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[TempDBFileSize] WITH NOCHECK ADD CONSTRAINT [PK_TempDBFileSize] PRIMARY KEY CLUSTERED ([TFSID]) ON [PRIMARY] GO |
Luego crear procedimiento que llenara nuestra tabla con la informacion de crecimineto del log
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
CREATE PROCEDURE dbo.spTempdbFileSize SET NOCOUNT ON INSERT INTO dbo.TempDBFileSize ( FileID ,File_Logical_Name ,State_Desc ,Type_Desc ,[Default_File_SizeMB] ,ActualKB ,ActualMB ,File_MaxSize ,File_Growth ,Growth_Type ,Physical_File_Name ,DateCaptured ) SELECT File_ID ,MasterTbl.[Name] ,MasterTbl.State_Desc ,MasterTbl.Type_Desc ,(MasterTbl.[Size] * 8) / 1024 AS 'File_SizeMB' ,(TempTbl.[size] * 8) AS ActualKB ,(TempTbl.[size] * 8) / 1024 AS ActualMB ,File_MaxSize = CASE WHEN MasterTbl.[Max_Size] = 0 THEN 'NoGrowth' WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth' WHEN MasterTbl.[Max_Size] = - 1 THEN 'UnlimitedGrowth' WHEN MasterTbl.[Max_Size] = 268435456 THEN 'TLogMax' ELSE CAST((MasterTbl.[Max_Size] * 8) / 1024 AS VARCHAR(10)) END ,File_Growth = CASE WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth' WHEN MasterTbl.[Growth] & gt;0 AND MasterTbl.[is_percent_growth] = 0 THEN CAST((MasterTbl.[Growth] * 8) / 1024 AS VARCHAR(10)) WHEN MasterTbl.[Growth] & gt;0 AND MasterTbl.[is_percent_growth] = 1 THEN CAST(MasterTbl.[Growth] AS VARCHAR(10)) ELSE 'Unknown' END , Growth_Type = CASE WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth' WHEN MasterTbl.[is_percent_growth] = 0 THEN 'MegaBytes' WHEN MasterTbl.[is_percent_growth] = 1 THEN 'Percentage' ELSE 'Unknown' END ,MasterTbl.[Physical_Name] ,GETDATE() AS 'DateCaptured' FROM Master.sys.master_files MasterTbl(NOLOCK) LEFT JOIN TEMPDB.SYS.SYSFILES TempTbl(NOLOCK) ON MasterTbl.[Physical_Name] = TempTbl.[filename] WHERE Database_ID = 2 SET NOCOUNT OFF GO |
Posteriormente crear un Job, que recolectar nuestra información
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 |
BEGIN TRANSACTION DECLARE @JobID BINARY (16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF ( SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE NAME = N'[Uncategorized (Local)]' ) & lt;1 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' IF ( SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE NAME = N'Admin - TempDB Sizing' ) & gt;0 PRINT N'The job "Admin - TempDB Sizing" already exists so will not be replaced.' ELSE BEGIN -- Add the job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,@job_name = N'Admin - TempDB Sizing' ,@owner_login_name = N'sa' ,@description = N'06.16.2007 - ES - Capture the TempDB growth to determine the needed size for the TempDB database' ,@category_name = N'[Uncategorized (Local)]' ,@enabled = 1 ,@notify_level_email = 0 ,@notify_level_page = 0 ,@notify_level_netsend = 0 ,@notify_level_eventlog = 2 ,@delete_level = 0 IF ( @@ERROR & lt;& gt;0 OR @ReturnCode & lt;& gt;0 ) GOTO QuitWithRollback -- Add the job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID ,@step_id = 1 ,@step_name = N'EXEC dbo.spTempdbFileSize' ,@command = N'EXEC dbo.spTempdbFileSize' ,@database_name = N'TestTest' ,@server = N'' ,@database_user_name = N'' ,@subsystem = N'TSQL' ,@cmdexec_success_code = 0 ,@flags = 0 ,@retry_attempts = 0 ,@retry_interval = 1 ,@output_file_name = N'' ,@on_success_step_id = 0 ,@on_success_action = 1 ,@on_fail_step_id = 0 ,@on_fail_action = 2 IF ( @@ERROR & lt;& gt;0 OR @ReturnCode & lt;& gt;0 ) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID ,@start_step_id = 1 IF ( @@ERROR & lt;& gt;0 OR @ReturnCode & lt;& gt;0 ) GOTO QuitWithRollback -- Add the job schedules EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID ,@name = N'Daily Every 6 Hours' ,@enabled = 1 ,@freq_type = 4 ,@active_start_date = 20070524 ,@active_start_time = 0 ,@freq_interval = 1 ,@freq_subday_type = 8 ,@freq_subday_interval = 6 ,@freq_relative_interval = 0 ,@freq_recurrence_factor = 0 ,@active_end_date = 99991231 ,@active_end_time = 235959 IF ( @@ERROR & lt;& gt;0 OR @ReturnCode & lt;& gt;0 ) GOTO QuitWithRollback -- Add the Target Servers EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID ,@server_name = N'(local)' IF ( @@ERROR & lt;& gt;0 OR @ReturnCode & lt;& gt;0 ) GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT & gt;0) ROLLBACK TRANSACTION EndSave: |
Analizar los datos
|
1 2 |
SELECT * FROM [dbo].[TempDBFileSize];GO |
Espero que ahora entiendan Para que es la base de datos tempdb y logren una configuración para sus servidores
Feliz día, y Gracias Totales!!!!
Excelente explicación, Felicidades mi pana.
Gracias siempre a al orden
Podría enviármelo en un archivo PDF a mauriciocataldi@gmail.com
Amigo, ya le envie la inforamcion en PDF, que tenga feliz dia, no olvide que puede seguirme en el blog para tener siempre informacion actualizada
Buen día Juan. Interesante articulo, muy agradecido. Estoy tratando de implementarlo pero tengo dos detalles: 1) Error en el sp en las línea en donde esta gt;0, indica que es invalido, yo le coloque = y paso pero ahora dice que el campo gt no existe en la tabla. 2) La tabla y el sp se crear en cada BD a analizar, en la master u otra?