Capacity Planning for tempdb

SQL Server 2008 R2

Other Versions

This topic provides guidelines for determining the appropriate amount of disk space that tempdb requires. This topic also includes recommendations about how to configure tempdb for optimal performance in a production environment and information about how to monitor tempdb space usage.

How tempdb Is Used

The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.

User Objects

User objects are explicitly created by the user. These objects may be in the scope of a user session or in the scope of the routine in which the object is created. A routine is a stored procedure, trigger, or user-defined function. User objects can be one of the following:

• User-defined tables and indexes
• System tables and indexes
• Global temporary tables and indexes
• Local temporary tables and indexes
• Table variables
• Tables returned in table-valued functions

Internal Objects

Internal objects are created as necessary by the SQL Server Database Engine to process SQL Server statements. Internal objects are created and dropped within the scope of a statement. Internal objects can be one of the following:

• Work tables for cursor or spool operations and temporary large object (LOB) storage.
• Work files for hash join or hash aggregate operations.
• Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.

Each internal object uses a minimum of nine pages; one IAM page and one eight-page extent. For more information about pages and extents, see Understanding Pages and Extents.

Version Stores

A version store is a collection of data pages that hold the data rows that are required to support the features that use row versioning. There are two version stores: a common version store and an online-index-build version store. The version stores contain the following:

• Row versions that are generated by data modification transactions in a database that uses snapshot or read committed using row versioning isolation levels.
• Row versions that are generated by data modification transactions for features such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

The following table lists the features in SQL Server that create user objects, internal objects, or row versions in tempdb. Whenever possible, the methods for estimating disk space use are provided.

Feature	tempdb use	Additional information
Bulkload operations with triggers enabled	Bulk-import optimizations are available when triggers are enabled. SQL Server uses row versioning for triggers that update or delete transactions. A copy of each deleted or updated row is added to the version store. See "Triggers" that follows later in this table.	Optimizing Bulk Import Performance
Common table expression queries	A common table expression can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. When the query plan for a common table expression query uses a spool operator to save intermediate query results, the Database Engine creates a work table in tempdb to support this operation.	Using Common Table Expressions WITH common_table_expression (Transact-SQL)
Cursors	Keyset-driven and static cursors use work tables that are built in tempdb. Keyset-driven cursors use the work tables to store the set of keys that identify the rows in the cursor. Static cursors use a work table to store the complete result set of the cursor. The disk space usage for cursors may vary depending on the query plan that is chosen. If the query plan is the same as earlier versions of SQL Server, the disk space usage is approximately the same.	About Choosing a Cursor Type
Database Mail	See "Service Broker" that follows later in this table.	Database Mail
DBCC CHECKDB	DBCC CHECKDB uses tempdb work tables to hold intermediate results and for sort operations. To determine tempdb disk space requirements for the operation, run DBCC CHECKDB WITH ESTIMATEONLY.	DBCC CHECKDB (Transact-SQL) Optimizing DBCC CHECKDB Performance
Event notifications	See "Service Broker" that follows later in this table.	Understanding Event Notifications
Indexes	When you create or rebuild an index (offline or online) and set the SORT_IN_TEMPDB option to ON, you direct the Database Engine to use tempdb to store the intermediate sort results that are used to build the index. When SORT_IN_TEMPDB is specified and sorting is required, tempdb must have sufficient disk space to hold the largest index plus disk space that is equal to the value of the index create memory option. For more information, see Index Disk Space Example. Tables and indexes can be partitioned. For partitioned indexes, if the SORT_IN_TEMPDB index option is specified and the index is aligned with the base table, there must be sufficient space in tempdb to hold the intermediate sort runs of the largest partition. If the index is not aligned, there must be sufficient space in tempdb to hold the intermediate sort runs of all partitions. For more information, see Special Guidelines for Partitioned Indexes. Online index operations use row versioning to isolate the index operation from the effects of modifications that are made by other transactions. Row versioning removes the need for requesting share locks on rows that have been read. Concurrent user update and delete operations during online index operations require space for version records in tempdb. When online index operations use SORT_IN_TEMPDB and sorting is required, tempdb must also have the additional disk space previously described for intermediate sort results. Online index operations that create, drop, or rebuild a clustered index also require additional disk space to build and maintain a temporary mapping index. CREATE and UPDATE STATISTICS operations can use tempdb to sort the sample of rows for building statistics. For more information, see Disk Space Requirements for Index DDL Operations.	tempdb and Index Creation Special Guidelines for Partitioned Indexes Disk Space Requirements for Index DDL Operations Index Disk Space Example How Online Index Operations Work
Large object (LOB) data type variables and parameters	The large object data types are varchar(max), nvarchar(max), varbinary(max) text, ntext, image, and xml. These types can be up to 2 GB in size and can be used as variables or parameters in stored procedures, user-defined functions, batches, or queries. Parameters and variables that are defined as a LOB data type use main memory as storage if the values are small. However, large values are stored in tempdb. When LOB variables and parameters are stored in tempdb, they are treated as internal objects. You can query the sys.dm_db_session_space_usage dynamic management view to report the pages allocated to internal objects for a given session. Some intrinsic string functions, such as SUBSTRING or REPLICATE, may require intermediate temporary storage in tempdb when they are operating on LOB values. Similarly, when a row versioning-based transaction isolation level is enabled on the database and modifications of large objects are made, the changed fragment of the LOB is copied to the version store in tempdb.	Using Large-Value Data Types
Multiple Active Result Sets (MARS)	Multiple active result sets can occur under a single connection; this is commonly referred as MARS. If a MARS session issues a data modification statement (such as INSERT, UPDATE, or DELETE) when there is an active result set, the rows that are affected by the modification statement are stored in the version store in tempdb. See "Row versioning" that follows later in this table.	Using Multiple Active Result Sets (MARS)
Query notifications	See "Service Broker" that follows later in this table.	Using Query Notifications
Queries	Queries that contain SELECT, INSERT, UPDATE, and DELETE statements can use internal objects to store intermediate results for hash joins, hash aggregates, or sorting. When a query execution plan is cached, the work tables required by the plan are cached. When a work table is cached, the table is truncated and nine pages remain in the cache for reuse. This improves the performance of the next execution of the query. If the system is low on memory, the Database Engine can remove the execution plan and drop the associated work tables.	Execution Plan Caching and Reuse
Row versioning	Row versioning is a general framework that is used to support the following features: Triggers Multiple Active Result Sets (MARS) Index operations that specify the ONLINE option Row versioning-based transaction isolation levels: A new implementation of read-committed isolation level that uses row versioning to provide statement-level read consistency. A snapshot isolation level to provide transaction-level read consistency. Row versions are held in the tempdb version store for as long as an active transaction must access it. The content of the current version store is returned in sys.dm_tran_version_store. Version-store pages are tracked at the file level because they are global resources. You can use the version_store_reserved_page_count column in sys.dm_db_file_space_usage to view the current size of the version store. Version-store cleanup must consider the longest running transaction that requires access to the particular version. The longest running transaction related to version store clean-up can be discovered by viewing the elapsed_time_seconds column in sys.dm_tran_active_snapshot_database_transactions. The counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object can be used to monitor the size and rate of growth of the row-version store in tempdb. For more information, see SQL Server, Transactions Object. To estimate how much space is required in tempdb for row versioning, you have to first consider that an active transaction must keep all its changes in the version store. This means that a snapshot transaction that starts later can access the old versions. Also, if there is an active snapshot transaction, all the version store data that is generated by transactions that are active when the snapshot starts must also be kept. Here is a basic formula: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction]	Understanding Row Versioning-Based Isolation Levels Row Versioning Resource Usage
Service Broker	Service Broker helps developers build asynchronous, loosely coupled applications in which independent components work together to accomplish a task. These application components exchange messages that contain the information that is required to complete the task. Service Broker explicitly uses tempdb for preserving existing dialog context that cannot stay in memory. The size is approximately 1 KB per dialog. Also, Service Broker implicitly uses tempdb by the caching of objects in the context of query execution, such as work tables that are used for timer events and background delivered conversations. Database Mail, Event Notifications, and Query Notifications implicitly use Service Broker.	Overview (Service Broker)
Stored procedures	Stored procedures can create user objects such as global or local temporary tables and their indexes, variables, or parameters. Temporary objects in stored procedures can be cached to optimize the operations that drop and create these objects. This behavior can increase tempdb disk space requirements. Up to nine pages per temporary object are stored for reuse. See "Temporary tables and table variables" that follows later in this table.	Creating Stored Procedures (Database Engine)
Temporary tables and table variables User-defined tables and indexes System tables and indexes Global temporary tables and indexes Local temporary tables and indexes table variables Tables returned in table-valued functions	Temporary tables and table variables are stored in tempdb. The disk space requirements for temporary table objects are the same as earlier versions of SQL Server. The method for estimating the size of a temporary table size is the same as estimating the size of a standard table. For more information, see Estimating the Size of a Table. A table variable behaves like a local variable. A table variable is of type table and is primarily used for the temporary storage of a set of rows that are returned as the result set of a table-valued function. The disk space that is required to hold a table variable depends on the size of the declared variable and the value stored in the variable. Local temporary tables and variables are cached when the following conditions are satisfied: Named constraints are not created. Data Definition Language (DDL) statements that affect the table are not run after the temporary table has been created, such as the CREATE INDEX or CREATE STATISTICS statements. The temporary object is not created by using dynamic SQL, such as: sp_executesql N'create table #t(a int)'. The temporary object is created inside another object, such as a stored procedure, trigger, user-defined function; or is the return table of a user-defined, table-valued function. When a temporary table or table variable is cached, the temporary object is not deleted when its purpose ends. Instead, the temporary object is truncated. Up to nine pages are stored and reused the next time that the calling object is executed. Caching allows operations that drop and create the objects to execute very quickly and reduces page allocation contention. For optimal performance, you should calculate the disk space that is required for cached local temporary tables or table variables in tempdb by using the following formula: 9 page per temp table * number of average temp tables per procedure * number of maximum simultaneous executions of the procedure	CREATE TABLE (Transact-SQL) Using Variables and Parameters (Database Engine) DECLARE @local_variable (Transact-SQL)
Triggers	The inserted and deleted tables that are used in AFTER triggers are created in tempdb. That is, the rows that are updated or deleted by the trigger are versioned. This includes all of the rows that are modified by the statement that fired the trigger. Rows that are inserted by the trigger are not versioned. INSTEAD OF triggers use tempdb in way similar to queries. The disk space usage for INSTEAD OF triggers is the same as earlier versions of SQL Server. See "Queries" previously in this table. When you bulk load data with triggers enabled, a copy of each deleted or updated row is added to the version store.	CREATE TRIGGER (Transact-SQL) Optimizing Bulk Import Performance Row Versioning Resource Usage
User-defined functions	User-defined functions can create temporary user objects, such as global or local tables and their indexes, variables, or parameters. For example, the return table of a table-valued function is stored in tempdb. The data types that are allowed for parameters and return values in scalar functions and table-valued functions include most LOB data types. For example, a return value can be of type xml or varchar(max). See "Large object (LOB) data type variables and parameters" previously in this table. Temporary objects in table-valued user-defined functions can be cached to optimize the operations that drop and create these objects. See "Temporary tables and table variables" previously in this table.	CREATE FUNCTION (Transact-SQL)
XML	Variables and parameters of type xml can be up to 2 GB. They use main memory as storage as long as the values are small. However, large values are stored in tempdb. See "Large object (LOB) data type variables and parameters" previously in this table. The sp_xml_preparedocument system stored procedure creates a work table in tempdb. The MSXML parser uses the work table to store the parsed XML document. The disk space requirements for tempdb is nearly proportional to the size of the specified XML document when the stored procedure is execute.	Implementing XML in SQL Server sp_xml_preparedocument (Transact-SQL) Querying XML Using OPENXML

Capacity Planning for Upgrades to SQL Server

Determining the appropriate size for tempdb in a production environment depends on many factors. As described previously in this topic, these factors include the existing workload and the SQL Server features that are used. We recommend that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

1. Set autogrow on for tempdb.
2. Execute individual queries or workload trace files and monitor tempdb space use.
3. Execute index maintenance operations, such as rebuilding indexes and monitor tempdb space.
4. Use the space-use values from the previous steps to predict your total workload usage; adjust this value for projected concurrent activity, and then set the size of tempdb accordingly.

For more information about monitoring tempdb space, see Troubleshooting Insufficient Disk Space in tempdb. For more information about estimating tempdb usage during index operations, see Index Disk Space Example.

Configuring tempdb for Production Environments

To achieve optimal tempdb performance, follow the guidelines and recommendations provided in Optimizing tempdb Performance.

How to Monitor tempdb Use

Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used by these features in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. There are also several counters that can be used to monitor the free space that is available in tempdb and also the resources that are using tempdb. For more information, see Troubleshooting Insufficient Disk Space in tempdb.

Planejamento de capacidade para tempdb

SQL Server 2008 R2

Outras versões

Este tópico fornece diretrizes para determinar o espaço adequado em disco necessário para tempdb. Este tópico também inclui recomendações sobre como configurar tempdb para obter o desempenho ideal em um ambiente de produção e informações sobre como monitorar a utilização de espaço de tempdb.

Como utilizar tempdb

O banco de dados do sistema tempdb é um recurso global disponível a todos os usuários conectados a uma instância do SQL Server. O banco de dados tempdb é utilizado para armazenar os seguintes objetos: objetos do usuário, objetos internos e repositórios de versão.

Objetos do usuário

Os objetos do usuário são criados explicitamente pelo usuário. Esses objetos podem estar no escopo de uma sessão de usuário ou no escopo da rotina na qual o objeto é criado. Uma rotina é um procedimento armazenado, gatilho ou função definida pelo usuário. Os objetos do usuário podem ser um dos seguintes:

• Tabelas e índices definidos pelo usuário
• Índices e tabelas do sistema
• Tabelas e índices temporários globais
• Tabelas e índices temporários locais
• Variáveis de tabela
• Tabelas retornadas em funções com valor de tabela

Objetos internos

Os objetos internos são criados quando necessário pelo Mecanismo de banco de dados do SQL Server para processar instruções SQL Server. Os objetos internos são criados e posicionados dentro do escopo de uma instrução. Os objetos internos podem ser um dos seguintes:

• Tabelas de trabalho para operações de cursor ou spool e armazenamento temporário de LOB (Objeto Grande).
• Arquivos de trabalho para operações de junção de hash ou de agregado de hash.
• Resultados intermediários de classificação para operações como criar ou recriar índices (se SORT_IN_TEMPDB for especificado) ou determinadas consultas GROUP BY, ORDER BY ou UNION.

Cada objeto interno usa um mínimo de nove páginas; uma página IAM e uma extensão de oito páginas. Para obter mais informações sobre essas páginas e extensões, consulte Compreendendo páginas e extensões.

Armazenamento de versão

Um repositório de versão é uma coleção de páginas de dados que contém linhas de dados necessárias para oferecer suporte aos recursos que utilizam controle de versão de linha. Existem dois armazenamentos de versão: um repositório de versão comum e um armazenamento de versão de criação de índice online. Os armazenamentos de versão contêm o seguinte:

• Versões de linhas geradas através de transações de modificação de dados em um banco de dados que usa instantâneo ou leitura confirmada utilizando níveis de isolamento de controle de versão de linha.
• Versões de linhas geradas por meio de transações de modificação de dados para recursos como: operações de índice online, vários conjuntos de resultados ativos (MARS) e gatilhos AFTER.

A tabela a seguir lista os recursos do SQL Server que criam objetos de usuário, objetos internos ou versões de linha em tempdb. Sempre que possível, são fornecidos os métodos para calcular a utilização do espaço em disco.

Recurso	Utilização de tempdb	Informações adicionais
Operações de carregamento em massa com gatilhos habilitados	As otimizações de importação em massa ficam disponíveis quando os gatilhos são habilitados. O SQL Server usa o controle de versão de linha para gatilhos que atualizam ou excluem transações. Uma cópia de cada linha excluída ou atualizada é adicionada ao armazenamento de versão. Consulte “Gatilhos” posteriormente nesta tabela.	Otimizando o desempenho de importação em massa
Consultas de expressões comuns da tabela	Podemos pensar em uma expressão comum da tabela como sendo um conjunto de resultados temporário definido no escopo de execução de uma única instrução SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Quando o plano de consulta para uma consulta de expressão comum da tabela usa um operador de spool para salvar os resultados intermediários de consulta , o Mecanismo de Banco de Dados cria uma tabela de trabalho em tempdb para oferecer suporte a essa operação.	Usando expressões de tabela comuns WITH common_table_expression (Transact-SQL)
Cursores	Os cursores controlados por conjuntos de chaves e os cursores estáticos usam tabelas de trabalho internas do tempdb. Os cursores controlados por conjuntos de chaves usam as tabelas de trabalho para armazenar o conjunto de chaves que identifica as linhas no cursor. Os cursores estáticos usam uma tabela de trabalho para armazenar todo o conjunto de resultados do cursor. A utilização do espaço em disco para cursores pode variar, dependendo do plano de consulta selecionado. Se o plano de consulta for o mesmo que as versões anteriores do SQL Server, a utilização do espaço em disco será aproximadamente a mesma.	Sobre como escolher um tipo de cursor
Database Mail	Consulte “Service Broker” posteriormente nesta tabela.	Database Mail
DBCC CHECKDB	DBCC CHECKDB utiliza as tabelas de trabalho do tempdb para manter os resultados intermediários e para operações de classificação. Para determinar a necessidade de espaço em disco do tempdb para a operação, execute DBCC CHECKDB WITH ESTIMATEONLY.	DBCC CHECKDB (Transact-SQL) Otimizando o desempenho de DBCC CHECKDB
Notificações de eventos	Consulte “Service Broker” posteriormente nesta tabela.	Compreendendo notificações de eventos
Índices	Quando você cria ou recria um índice (offline ou online) e define a opção SORT_IN_TEMPDB para ON, o Mecanismo de Banco de Dados utiliza o tempdb para armazenar os resultados intermediários de classificação utilizados para criar o índice. Quando for especificado SORT_IN_TEMPDB e for necessária uma classificação, o tempdb deverá ter espaço em disco suficiente para manter o maior índice somado ao espaço em disco que será igual ao valor da opção index create memory. Para obter mais informações, consulte Exemplo de espaço em disco de índice. As tabelas e os índices podem ser particionados. Para índices particionados, se for especificada a opção de índice SORT_IN_TEMPDB e o índice estiver alinhado com a tabela base, deverá haver espaço suficiente em tempdb para manter as execuções intermediárias de classificação da partição maior. Se o índice não estiver alinhado, deverá haver espaço suficiente em tempdb para manter as execuções intermediárias de classificação de todas as partições. Para obter mais informações, consulte Diretrizes especiais para índices particionados. As operações de índice online utilizam o controle de versão de linha para isolar a operação de índice dos efeitos de modificações feitas por outras transações. O controle de versão de linha remove a necessidade de solicitar bloqueios de compartilhamento de linhas que já foram lidas. Operações simultâneas de atualização e exclusão de usuários durante operações de índice online precisam de espaço para o registro de versão em tempdb. Quando as operações de índice online utilizam SORT_IN_TEMPDB e é necessária uma classificação, o tempdb também deverá ter espaço em disco adicional descrito anteriormente para resultados intermediários de classificação. As operações de índice online que criam, cancelam ou recriam um índice clusterizado também precisam de espaço adicional em disco para criar e manter um índice de mapeamento temporário. As operações CREATE e UPDATE STATISTICS podem usar tempdb para classificar o exemplo de linhas para compilação de estatísticas. Para obter mais informações, consulte Requisitos de espaço em disco para operações de índice DDL.	tempdb e criação de índice Diretrizes especiais para índices particionados Requisitos de espaço em disco para operações de índice DDL Exemplo de espaço em disco de índice Como funcionam as operações de índice online
Variáveis e parâmetros do tipo de dados LOB (Objeto Grande)	Os tipos de dados de objetos grandes são varchar(max), nvarchar(max), varbinary(max)text, ntext, image e xml. Esses tipos podem ter até 2 GB e podem ser utilizados como variáveis ou parâmetros em procedimentos armazenados, funções definidas pelo usuário, lotes ou consultas. Os parâmetros e as variáveis definidos como tipo de dados de LOB utilizam a memória principal como armazenamento se os valores forem pequenos. Entretanto, os valores grandes são armazenados no tempdb. Quando são armazenados variáveis e parâmetros LOB no tempdb, eles são tratados como objetos internos. Você pode consultar a exibição dinâmica de gerenciamento sys.dm_db_session_space_usage para informar as páginas alocadas a objetos internos para uma determinada sessão. Algumas funções intrínsecas de cadeia de caracteres, como SUBSTRING ou REPLICATE, podem exigir armazenamento intermediário temporário em tempdb quando estiverem funcionando em valores LOB. Da mesma forma, quando um nível de isolamento da transação baseada em controle da versão de linha é habilitado no banco de dados e são feitas modificações de objetos grandes, o fragmento alterado do LOB é copiado no repositório de versão em tempdb.	Usando tipos de dados de valor grande
MARS (Vários Conjuntos de Resultados Ativos)	Vários conjuntos de resultados ativos podem acontecer em uma única conexão; isso geralmente é chamado de MARS. Se uma sessão de MARS emite uma instrução de modificação de dados (como INSERT, UPDATE ou DELETE) quando há um conjunto de resultados ativo, as linhas afetadas pela instrução de modificação são armazenadas no repositório de versão em tempdb. Consulte “Controle de versão de linha” posteriormente nesta tabela.	Usando MARS (vários conjuntos de resultados ativos)
Notificações de consultas	Consulte “Service Broker” posteriormente nesta tabela.	Usando notificações de consulta
Consultas	As consultas que contêm instruções SELECT, INSERT, UPDATE e DELETE podem utilizar objetos internos para armazenar resultados intermediários para junções de hash, agregados de hash ou classificação. Quando um plano de execução de consulta é armazenado em cache, as tabelas de trabalho exigidas pelo plano são armazenadas em cache. Quando uma tabela de trabalho é armazenada em cache, a tabela é truncada e nove páginas permanecem no cache para reutilização. Isso melhora o desempenho da próxima execução da consulta. Se o sistema estiver com pouca memória, o Mecanismo de Banco de Dados poderá remover o plano de execução e cancelar as tabelas de trabalho associadas.	Reutilização e armazenamento em cache do plano de execução
Controle de versão de linha	O controle de versão de linha é uma estrutura geral utilizada para oferecer suporte aos seguintes recursos: Gatilhos MARS (Vários Conjuntos de Resultados Ativos) Operações de índice que especificam a opção ONLINE Níveis de isolamento de transação baseada em controle de versão de linha: Uma implementação nova de nível de isolamento de confirmação de leitura que utiliza o controle de versão de linha para fornecer a consistência de leitura de nível de instrução. Um nível de isolamento do instantâneo para fornecer a consistência de leitura de nível de transação. As versões de linhas são mantidas no repositório de versão tempdb durante o tempo em que uma transação ativa deva acessá-las. O conteúdo do repositório de versão atual é retornado em sys.dm_tran_version_store. As páginas de armazenamento de versão são controladas no nível de arquivo porque são recursos globais. Você pode utilizar a coluna version_store_reserved_page_count em sys.dm_db_file_space_usage para exibir o tamanho atual do armazenamento de versão. A limpeza total do armazenamento de versão deve considerar a transação mais longa em execução que requer acesso à versão particular. A transação mais longa em execução relacionada com a limpeza do repositório de versão pode ser descoberta exibindo a coluna elapsed_time_seconds em sys.dm_tran_active_snapshot_database_transactions. Os contadores Espaço Livre em tempdb (KB) e Tamanho do Repositório de Versão (KB) no objeto Transações podem ser utilizados para monitorar o tamanho e a taxa de crescimento do armazenamento de controle de versão de linha em tempdb. Para obter mais informações, consulte SQL Server, objeto de transações. Para calcular o espaço necessário em tempdb para o controle de versão de linha, primeiramente você precisa levar em consideração que uma transação ativa deve manter todas as suas alterações no armazenamento de versão. Isso significa que uma transação de instantâneo iniciada posteriormente pode acessar as versões antigas. Da mesma forma, se houver uma transação de instantâneo ativa, todos os dados de repositório de versão gerados por transações que estiverem ativas quando o instantâneo for iniciado também deverão ser mantidos. Esta é uma fórmula básica: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction]	Compreendendo níveis de isolamento com base em controle de versão de linha Uso do recurso de controle de versão de linha
Service Broker	O Service Broker ajuda os desenvolvedores a criarem aplicativos assíncronos, livremente acoplados, nos quais os componentes independentes trabalham em conjunto para realizar uma tarefa. Esses componentes de aplicativo trocam mensagens que contêm as informações necessárias para conclusão da tarefa. O Service Broker utiliza explicitamente o tempdb para preservar caixas de diálogo de contexto existentes que não podem ficar na memória. O tamanho é de aproximadamente 1 KB por caixa de diálogo. Além disso, o Service Broker utiliza implicitamente tempdb pelo cache de objetos no contexto de execução de consulta, como tabelas de trabalho utilizadas para eventos de timer e plano de fundo de conversações entregues. Database Mail, Notificações de eventos e Notificações de consulta utilizam Service Brokerimplicitamente.	Visão geral (Service Broker)
Procedimentos armazenados	O procedimentos armazenados podem criar objetos de usuário como tabelas temporárias globais ou locais e seus índices, variáveis ou parâmetros. Os objetos temporários nos procedimentos armazenados podem ser armazenados em cache para aperfeiçoar as operações que cancelam e criam tais objetos. Esse comportamento pode aumentar as exigências de espaço em disco de tempdb. São armazenadas até nove páginas por objeto temporário para reutilização. Consulte “Tabelas temporárias e variáveis de table” posteriormente nesta tabela.	Criando procedimentos armazenados (Mecanismos de Banco de Dados)
Tabelas temporárias e variáveis de table Tabelas e índices definidos pelo usuário Índices e tabelas do sistema Tabelas e índices temporários globais Tabelas e índices temporários locais Variáveis de table Tabelas retornadas em funções com valor de tabela	São armazenadas tabelas temporárias e variáveis de table em tempdb. As exigências de espaço em disco para objetos de tabela temporária são iguais às versões anteriores do SQL Server. O método para calcular o tamanho de uma tabela temporária é o mesmo utilizado para calcular o tamanho de uma tabela padrão. Para obter mais informações, consulte Estimando o tamanho de uma tabela. Uma variável table se comporta como uma variável local. Uma variável de table é do tipo table e é utilizada principalmente para o armazenamento temporário de um conjunto de linhas retornadas como o conjunto de resultados de uma função com valor de tabela. O espaço em disco exigido para manter uma variável de table depende do tamanho da variável declarada e do valor armazenado na variável. As tabelas temporárias locais e as variáveis são armazenadas em cache quando as seguintes condições são satisfeitas: Não são criadas restrições nomeadas. Não são executadas instruções DDL (linguagem de definição de dados) que afetam a tabela depois da criação da tabela temporária, como instruções CREATE INDEX ou CREATE STATISTICS. O objeto temporário não é criado utilizando o SQL dinâmico, como: sp_executesql N'create table #t(a int)'. O objeto temporário é criado dentro de outro objeto, como um procedimento de armazenamento, gatilho, função definida pelo usuário; ou é a tabela de retorno de uma função definida pelo usuário, com valor de tabela. Quando uma tabela temporária ou uma variável de table é armazenada em cache, o objeto temporário não é excluído quando seu objetivo é alcançado. Ao invés disso, o objeto temporário é truncado. Na próxima vez que o objeto de chamada é executado são armazenadas e reutilizadas até nove páginas. O armazenamento em cache permite que as operações de cancelamento e criação de objetos sejam executadas rapidamente e reduz a contenção de alocação de página. Para otimizar o desempenho, você deve calcular o espaço em disco necessário para armazenar em cache tabelas temporárias locais ou variáveis de table no tempdb utilizando a seguinte fórmula: 9 page per temp table * number of average temp tables per procedure * number of maximum simultaneous executions of the procedure	CREATE TABLE (Transact-SQL) Usando variáveis e parâmetros (Mecanismo de Banco de Dados) DECLARE @local_variable (Transact-SQL)
Gatilhos	As tabelas inseridas e excluídas utilizadas em gatilhos AFTER são criadas no tempdb. Ou seja, as linhas que são atualizadas ou excluídas pelo gatilho são controladas por versão. Isso inclui todas as linhas modificadas pela instrução que acionou o gatilho. Ou seja, as linhas inseridas pelo gatilho não são controladas por versão. Os gatilhos INSTEAD OF utilizam tempdb de modo semelhante para consultas. A utilização do espaço em disco para gatilhos INSTEAD OF é a mesma das versões anteriores do SQL Server. Consulte “Consultas” previamente nesta tabela. Quando você carrega dados em massa com gatilhos habilitados, uma cópia de cada linha excluída ou atualizada é adicionada ao armazenamento de versão.	CREATE TRIGGER (Transact-SQL) Otimizando o desempenho de importação em massa Uso do recurso de controle de versão de linha
Funções definidas pelo usuário	As funções definidas pelo usuário podem criar objetos de usuário temporários, como tabelas globais ou locais e seus índices, variáveis ou parâmetros. Por exemplo, a tabela de retorno de uma função com valor de tabela é armazenada em tempdb. Os tipos de dados permitidos para obter parâmetros e valores de retorno em funções escalares e funções com valor de tabela incluem a maioria dos tipos de dados de LOB. Por exemplo, um valor de retorno pode ser do tipo xml ou varchar(max). Consulte “Variáveis e parâmetros do tipo dados de LOB (Objeto Grande)” previamente nesta tabela. Os objetos temporários nas funções definidas pelo usuário com valor de tabela podem ser armazenados em cache para aperfeiçoar as operações que cancelam e criam tais objetos. Consulte “Tabelas temporárias e variáveis de table” previamente nesta tabela.	CREATE FUNCTION (Transact-SQL)
XML	Variáveis e parâmetros do tipo xml podem ter até 2 GB. Eles utilizam a memória principal como armazenamento contanto que os valores sejam pequenos. Entretanto, os valores grandes são armazenados no tempdb. Consulte “Variáveis e parâmetros do tipo dados de LOB (Objeto Grande)” previamente nesta tabela. O procedimento armazenado do sistema sp_xml_preparedocument cria uma tabela de trabalho em tempdb. O analisador MSXML utiliza a tabela de trabalho para armazenar o documento XML analisado. As exigências de espaço em disco para tempdb são praticamente proporcionais ao tamanho do documento XML especificado quando é executado o procedimento armazenado.	Implementando XML no SQL Server sp_xml_preparedocument (Transact-SQL) Consultando XML usando OPENXML

Planejamento de capacidade para atualizações do SQL Server

A determinação do tamanho apropriado para tempdb em um ambiente de produção depende de muitos fatores. Como previamente descrito neste tópico, esses fatores incluem a carga de trabalho existente e os recursos SQL Server utilizados. Nós recomendamos que você analise a carga de trabalho existente executando as seguintes tarefas em um ambiente de teste do SQL Server:

1. Defina crescimento automático para tempdb.
2. Execute consultas individuais ou arquivos de rastro de carga de trabalho e monitore a utilização de espaço de tempdb.
3. Execute operações de manutenção de índice, como recriar índices e monitore o espaço de tempdb.
4. Utilize os valores de utilização de espaço das etapas anteriores para prever sua utilização total de carga de trabalho; ajuste esse valor para atividades simultâneas projetadas e defina adequadamente o tamanho de tempdb.

Para obter mais informações sobre como monitorar o espaço de tempdb, consulte Solucionando problemas de espaço insuficiente em disco em tempdb. Para obter mais informações sobre como calcular a utilização de tempdb durante operações de índice, consulte Exemplo de espaço em disco de índice.

Configurando tempdb para ambientes de produção

Para obter o melhor desempenho de tempdb, siga as diretrizes e as recomendações fornecidas em Aperfeiçoando o desempenho de tempdb.

Como monitorar a utilização de tempdb

A execução fora do espaço em disco em tempdb pode causar interrupções significativas no ambiente de produção do SQL Server e pode impedir que aplicativos que estão em execução concluam as operações. Você pode utilizar a exibição dinâmica de gerenciamento sys.dm_db_file_space_usage para monitorar o espaço em disco utilizado por esses recursos nos arquivos tempdb. Além disso, para monitorar a alocação de página ou a atividade de desalocação em tempdb em nível de sessão ou tarefa, você pode utilizar as exibições dinâmicas de gerenciamento sys.dm_db_session_space_usage e sys.dm_db_task_space_usage. Essas exibições podem ser utilizadas para identificar consultas grandes, tabelas temporárias ou variáveis de tabela que estão utilizando muito espaço em disco de tempdb. Existem vários contadores que podem ser utilizados para monitorar o espaço livre disponível em tempdb e também os recursos que estão utilizando tempdb. Para obter mais informações, consulte Solucionando problemas de espaço insuficiente em disco em tempdb.

Configuration Best Practices for SQL Server Tempdb–Placement

By Christian Bolton, Justin Langford, Glenn Berry, Gavin Payne, Amit Banerjee, Rob Farley

This part of a three-part article consolidating a number of best practices for configuring SQL Server tempdb focuses on tempdb placement. You won’t just find prescriptive rules here, but also the background to the recommendations and guidance on how to choose the best configuration for any particular environment. In particular this section covers the following:

• Where to place tempdb
• Initial sizing and autogrowth (see part 2)
• Configuring multiple files (see part 3)

Tempdb file placement

It’s quite a well-known best practice to separate data, transaction logs, and tempdb, and if you knew that already, are you sure you know why? The origin of this recommendation lies with the separation of types of workload between different physical storage, i.e. separate physical disks.

This is still a valid recommendation for environments where you can guarantee that separation, but more commonly we see customers deploying SQL Server in a shared storage environment, where physical separation is much harder to achieve and usually isn’t even necessary for performance reasons.

It is still a good idea however to maintain separation to help with manageability so that potential problems are easier to isolate. For example, separating tempdb onto its own logical disk means that you can pre-size it to fill the disk without worrying about space requirements for other files, and the more separation you implement the easier it is to correlate logical disk performance to specific database files.

At the very minimum you should aim to have one logical disk for data files, one for transaction log files, and one for tempdb data files. I prefer to keep the tempdb data files on their own drive so they can be sized to fill the drive and place the tempdb log files with the user database log files where there should be enough free disk space for unexpected autogrow events for any log file.

Local tempdb for failover cluster instances

Until SQL Server 2012, a failover cluster instance of SQL Server required all its database files to be on shared disk resources within the cluster. This was to ensure that when the instance failed over to another node in the cluster, all its dependent disks could be moved with it.

Nothing in tempdb persists after a restart and it’s effectively recreated every time. The failover process for a clustered instance involves a restart of SQL Server so nothing in tempdb needs to be moved across to the other node and there’s no technical reason why tempdb should be on a shared disk.

In SQL Server 2008 R2 you could force tempdb onto a local disk but it wasn’t supported; in SQL Server 2012 it’s fully supported and very straightforward to implement. All you need to do is use ALTER DATABASElike this:

USE master ; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D: empdbdata empdb.mdf') ; GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E: empdblogs emplog.ldf') ; GO

1 2 3 4 5 6 7 8

USE master ; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D: empdbdata empdb.mdf') ; GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E: empdblogs emplog.ldf') ; GO

You will see messages after execution that look like this:

Local directory 'D: empdbdata empdb.mdf'is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server service has read/write permission on it. The file ”tempdev” has been modified in the system catalog. The new path will be used the next time the database is started. Local directory 'E: empdblogs emplog.ldf' is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server service has read/write permission on it. The file ”templog” has been modified in the system catalog. The new path will be used the next time the database is started.

1 2 3 4 5 6 7 8 9 10

Local directory 'D: empdbdata empdb.mdf'is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server service has read/write permission on it. The file ”tempdev” has been modified in the system catalog. The new path will be used the next time the database is started. Local directory 'E: empdblogs emplog.ldf' is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server service has read/write permission on it. The file ”templog” has been modified in the system catalog. The new path will be used the next time the database is started.

That’s all there is to it. All you need to remember is that you need to have the same path available on all cluster nodes, and the service account needs to have read/write permission so that tempdb can start after failover.

Why might a local tempdb be useful?

There are two reasons why you might want to move tempdb from a shared disk to a local disk, and both are related to performance.

The first reason is that the relatively recent increase in cost effective, ultra-fast solid-state storage presents an opportunity to achieve significant performance gains on servers experiencing heavy tempdb usage. The challenge prior to SQL Server 2012 was that solid-state storage cards, like those provided by FusionIO and Texas Instruments, plug straight into a server’s motherboard to avoid all the overhead of traditional storage buses. This made it very difficult to use them at all in failover cluster instances and now they can be used for the discrete task of running tempdb.

The second reason you might want to use a local tempdb is to take I/O requests off your shared storage to improve the performance of the shared storage. We used this to great effect for one customer who was really at the peak of their SANs performance capacity; a FusionIO card was placed in each node of several failover clusters and all tempdb activity was re-directed locally. Even though tempdb performance was never bad before, the result was a significant reduction in load against the SAN which extended its life by an additional six months.

Read about initial sizing and autogrowth of tempdb in part 2 and configuring multiple files in part 3.

Tags: Tempdb

Source: http://logicalread.solarwinds.com

custom validation in codeigniter for username using callback function

      August 27, 2013      CodeIgniter     0 Comments

custom validation in codeigniter using callback function is easy way to validate input field on form dynamically. We can add custom validation to any input field before insertion in DB. CodeIgniter provide all basic validation with its form_validation class like required, numeric, min_length, max_length, alpha, alpha_numeric etc.
But If we want to add our custom validation for any particular field (mainly for the checking username availability). we can use its callback technique.
Example :

//Apply to any input field... $this->form_validation->set_rules('username', 'Username', 'required|numeric|callback_check_username|trim'); //here callback_check_username is the keyword which call check_username function from same class for validation. function check_username($username) { //$username is passed automatically to this function which is the value entered by the User. //This function checks the availability of the username in Database. $return_value = $this->user_model->check_username($username); if ($return_value) { //set Error message. $this->form_validation->set_message('username_check', 'Sorry, This username is already used by another user please select another one'); return FALSE; } else { //after satisfying our conditions return TRUE to the origin with no errors. return TRUE; } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

//Apply to any input field... $this->form_validation->set_rules('username', 'Username', 'required|numeric|callback_check_username|trim');   //here callback_check_username is the keyword which call check_username function from same class for validation.   function check_username($username) {         //$username is passed automatically to this function which is the value entered by the User.           //This function checks the availability of the username in Database.         $return_value = $this->user_model->check_username($username);         if ($return_value)         {                   //set Error message.                   $this->form_validation->set_message('username_check', 'Sorry, This username is already used by another user please select another one');                   return FALSE;         }         else         {                   //after satisfying our conditions return TRUE to the origin with no errors.                   return TRUE;         } }

Here $username is passed automatically to check_username function, we can use our custom rules over here to validate it.
Along with CodeIgniters basic validations you can use your custom conditions (like regular expressions) for a validation on any input field. you can also use regular expression to validate it in your way