Partager via

Tempdb and I/O misconceptions

  • Article

(Português) - (English)

 

Many doubts are always around when the subject is the creation of extra files for tempdb, recently I followed a thread in our Brazilian SQL Server forum, where the subject was debated, and I would like to point a few things out based on my understanding about the subject:

- The myth about SQL Server allocating one thread for each physical file is incorrect. SQL Server has a pool of threads that get divided between the active connections (when there is an active task) and, accordingly with MAXDOP configuration and server pressure, the engine defines how many threads each connection receives. Once the query does an I/O request, the thread will be used to issue a request to the I/O subsystem, so a file can receive requests from many threads at once.

- The addiction of more files to the tempdb helps, a lot, critical environments where usually we have a greater tempdb pressure, due to more batches being processes simultaneously. Note that every object that gets created at tempdb in controlled initially thru the PFS and SGAM pages, if I have a lot of activity in those pages, SQL Server possibly has a contention point. This contention happens in memory because those pages are hot spots and there is no reason they should be written to disk.

When we create more tempdb files (even in the same disk), each file will have its control pages and SQL Server will be able to work with a round robin mechanism between all the files, dividing the pressure among those control pages by the number of files. Note that for this to happen, the files should have similar sizes, otherwise SQL Server will favor bigger sizes, unbalancing the even distribution of work.

For those who wants to know more about the subject:

https://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

(other references to my blog are only in Portuguese, sorry)

 

Muitas dúvidas sempre pairam quando o assunto é a criação de arquivos extras para a tempdb, recentemente eu acompanhei uma thread no fórum de SQL Server onde esse assunto foi discutido e, gostaria de acrescentar alguns comentários baseados no meu entendimento do assunto:

- O mito do SQL Server alocar uma thread para cada arquivo físico está incorreto. O SQL Server possui um pool de threads que é distribuído entre as conexões ativas no SQL Server (quando existe uma tarefa ativa) e, de acordo com o MAXDOP e pressão no servidor, a engine define quantas threads cada conexão "ganha". Uma vez que a consulta faz uma requisição de I/O, essa thread será utilizada para enviar a requisição para o subsistema de I/O, então um arquivo pode receber requisições de diversas threads.

- A adição de mais arquivos à tempdb ajuda, e muito, ambientes mais críticos onde normalmente existe uma pressão maior na tempdb, já que estão sendo processados mais batches simultaneamente. Note que todo objeto criado na tempdb é controlado inicialmente através das páginas PFS e SGAM, se eu tenho muita manipulação dessas páginas, o SQL Server possivelmente terá um ponto de contenção. E a contenção está em memória, pois essas páginas são hot spots e não existe porque serem escritas em disco.

Quando se cria mais arquivos para a tempdb (mesmo que seja no mesmo disco), cada arquivo terá suas páginas de controle e o SQL Server poderá trabalhar com um mecanismo de rodízio (round robin) entre os arquivos, dividindo a pressão sobre as páginas de controle pelo número de arquivos. Note que para isso acontecer, os arquivos devem estar com tamanhos semelhantes, senão o SQL Server vai favorecer arquivos maiores, alterando a distribuição igualitária de tarefas.

Para quem quiser saber mais sobre o assunto, acesse:

https://blogs.msdn.com/luti/archive/2006/06/05/618151.aspx

https://blogs.msdn.com/luti/archive/2006/06/05/618151.aspx

https://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

 

[]s
Luciano Caixeta Moreira
luciano.moreira@microsoft.com
===============================================
This post is provided "AS IS" and confers no right
===============================================

Comments