How to configure tempdb in Azure SQL Managed Instance(preview)

Artigo
03/13/2018

Azure SQL Database Managed Instance (public preview) is a fully-managed SQL Server instance in Azure cloud. In the current public preview there are some known issues and bugs that will be fixed during public preview period. One of the potential issues is number of files and max_size that is set on tempdb. In this post, I will explain the issue and how you can solve it.

Managed Instance is currently in public preview and it has pre-configured tempdb database split into 12 files and each file has 14GB limit (max_size). This means that you can have queries that use up to 168 GB tempdb space.

NOTE: Managed Instance is now GA and tempdb cannot be configured anymore. On general Purpose tempdb can grow up to 24GB*VCore, while tempdb on BC don't have any limitation (except the max instance storage size). This article is not applicable anymore see service tier characteristics for the latest info.

Managed Instance allows you to manage database files. If you have workload that needs more tempdb space, you can alter files in tempdb and increase this limit:

 alter database tempdb modify file (name = 'tempdev', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'templog', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev2', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev3', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev4', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev5', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev6', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev7', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev8', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev9', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev10', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev11', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev12', maxsize = UNLIMITED)

You can easily generate this script and put some other max_size value instead of UNLIMITED using the following query:

 select concat('alter database tempdb modify file (name = ''',name,''', maxsize = UNLIMITED)')
from master.sys.master_files
where db_name(database_id) = 'tempdb'

One limitation in the current public preview is that tempdb don't preserves custom settings after fail-over happens. If you add new files to tempdb or change file size, these settings will not be preserved after fail-over, and original tempdb will be re-created on the new instance. This is a temporary limitation and it will be fixed during public preview.

However, since Managed Instance supports SQL Agent, and SQL Agent can be configured to execute some script when SQL Agent start, you can workaround this issue and create a SQL Agent job that will pre-configure your tempdb.

SQL Agent will start whenever Managed Instance fail-over and the job that contains script above can increase tempdb size before you start running your workload on the new instance.

One example of SQL Agent job that starts whenever Manage Instance starts and configures tempdb is shown in the following code:

 USE [msdb]
GO

/****** Object:  Job [Configure tempdb]    Script Date: 3/13/2018 3:02:18 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 3/13/2018 3:02:18 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Configure tempdb', 
     @enabled=1, 
        @notify_level_eventlog=0, 
      @notify_level_email=0, 
     @notify_level_netsend=0, 
       @notify_level_page=0, 
      @delete_level=0, 
       @description=N'No description available.', 
     @category_name=N'[Uncategorized (Local)]', 
     @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Increase max_size on tempdb]    Script Date: 3/13/2018 3:02:18 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Increase max_size on tempdb', 
        @step_id=1, 
        @cmdexec_success_code=0, 
       @on_success_action=1, 
      @on_success_step_id=0, 
     @on_fail_action=2, 
     @on_fail_step_id=0, 
        @retry_attempts=0, 
     @retry_interval=0, 
     @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'alter database tempdb modify file (name = ''tempdev'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev2'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev3'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev4'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev5'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev6'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev7'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev8'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev9'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev10'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev11'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev12'', maxsize = UNLIMITED)

', 
        @database_name=N'master', 
      @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'On failover', 
       @enabled=1, 
        @freq_type=64, 
     @freq_interval=0, 
      @freq_subday_type=0, 
       @freq_subday_interval=0, 
       @freq_relative_interval=0, 
     @freq_recurrence_factor=0, 
     @active_start_date=20180313, 
       @active_end_date=99991231, 
     @active_start_time=0, 
      @active_end_time=235959, 
       @schedule_uid=N'c2c03bd3-12be-4b41-ac40-b6dbe16479ad'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Compartilhar via

How to configure tempdb in Azure SQL Managed Instance(preview)

Recursos adicionais