SQL Server Urban Legends Discussed

re: SQL Server Urban Legends Discussed

Wesley Backelant — Wed, 21 Feb 2007 23:28:47 GMT

Bob,

When you say "unique disk drive", what does this mean for mountpoints?

And a second question, when you talk about NUMA there is 1 I/O thread per NUMA node. Do I understand correctly when I say that the "real" I/O in SQL Server is done by a single thread (or as many as there are NUMA nodes) while the workers just post async I/O requests for this thread to handle?

Kind regards,

Wesley Backelant

re: SQL Server Urban Legends Discussed

psssql — Mon, 26 Feb 2007 20:42:24 GMT

The unique disk disk logic that I have looked at in the code was the drive letter. I have not looked at mount point logic. I assume a unique mount point would work like a unique disk letter.

For NUMA the IO works the same as non-NUMA installations. Each worker will issue its own I/O.

What you are thinking of is that each node has a lazy writer thread to monitor and handle LW activity for the node. When checkpoint executes it assigns the I/Os to the LW associated with the NUMA node.

re: SQL Server Urban Legends Discussed

Wesley Backelant — Fri, 02 Mar 2007 14:59:42 GMT

Thanks for the explanation.

I'm a bit confused because the documentation states the I/O thread and the lazy writer thread separately.

"The benefits of soft-NUMA include reducing I/O and lazy writer bottlenecks on computers with many CPUs and no hardware NUMA. There is a single I/O thread and a single lazy writer thread for each NUMA node."

re: SQL Server Urban Legends Discussed

Wesley Backelant — Mon, 23 Apr 2007 16:37:00 GMT

Bob,

Another article that speaks about one thread per file is this: http://www.microsoft.com/technet/prodtechnol/sql/70/reskit/part2/sqc01.mspx?mfr=true

It says: "Whenever a data object (table) is accessed sequentially, a separate thread is created for each file in parallel. Therefore, a tablescan for a table that is assigned to a filegroup with four files uses four separate threads to read the data in parallel."

[RDORR] This is part of the problem with the myth. It does not work this way. If you have a partitioned view it is possible that a worker could be assigned to each partition and you get something like this. Again, that would be no different than a parallel query using multiple workers to scan the data. They each post I/O request for the pages they need. No seperate threads are created.

re: SQL Server Urban Legends Discussed

CindyGross — Tue, 14 Aug 2007 22:51:34 GMT

• For most user databases (this is explicitly not including TempDB) there is no performance advantage in multiple files or multiple file groups unless each file is on a separate IO path. The only exception would be the highly unusual case of a user database with many object drops and/or creates per second, which might benefit from multiple files. There can be a manageability benefit from multiple files/filegroups.

• None of this changes just because with table partitioning you have each partition on a separate file group. (Yes, there are now multiple files, but it’s because of the partitioning, not directly as a performance gain).

re: SQL Server Urban Legends Discussed

CindyGross — Tue, 14 Aug 2007 22:55:37 GMT

This statement in the below article is incorrect for user databases (though it is still true for tempdb). The real advantage of multiple files is managability and spreading files over multiple IO paths (controllers, LUNs, etc.).

Physical Database Storage Design

http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx

"The number of data files within a single filegroup should equal to the number of CPU cores."

Disk Queue Length: Some Data Points may Help!

Linchi Shea — Tue, 13 Nov 2007 01:47:18 GMT

There has been much discussion on the usefulness of disk queue length as an indicator of a disk I/O bottleneck.

SQL Server Myths - Disk Queue length a bit like buying Guiness

SimonS Blog on SQL Server Stuff — Fri, 16 Nov 2007 14:58:54 GMT

If you attended SQLBits you may have seen my SQL Myths session. One of the myths I didn't cover was that

Predeployment I/O Best Practices

Whitepapers — Wed, 21 Nov 2007 22:18:14 GMT

Published: June 5, 2007 Writer: Mike Ruthruff Contributors: Michael Thomassy, Prem Mehra Technical Reviewers

Lenda Urbana: tabelas temporárias são escritas diretamente em disco

LUTI @ Microsoft — Sat, 15 Dec 2007 20:59:44 GMT

Usualmente eu leio alguns blogs de SQL Server e certa vez eu me deparei com um artigo muito legal e detalhado,

Lenda Urbana: tabelas temporárias são escritas diretamente em disco

LUTI @ Microsoft — Sun, 16 Dec 2007 14:23:02 GMT

Usualmente eu leio alguns blogs de SQL Server e certa vez eu me deparei com um artigo muito legal e detalhado,

Lenda Urbana: tabelas temporárias são escritas diretamente em disco

Noticias externas — Sun, 16 Dec 2007 14:27:31 GMT

Usualmente eu leio alguns blogs de SQL Server e certa vez eu me deparei com um artigo muito legal e detalhado

配置前の I/O のベストプラクティス (英語)

Japanese Whitepapers — Mon, 17 Dec 2007 04:57:15 GMT

公開日 : 2007 年 6 月 5 日執筆者 : Mike Ruthruff 執筆協力者 : Michael Thomassy、Prem Mehra テクニカルレビュー担当者 : Robert Dorr

re: SQL Server Urban Legends Discussed

kirchner — Mon, 14 Jan 2008 21:43:39 GMT

Bob,

Frequently we see posts talking about debugging processes, finding problems, etc.

Surely I know how to debug my own programs in VS and alike, but how to do this when we have no source / debug symbols?

Can you point out some resources about it, or show us how to debug a "real" problem on SQL Server?

Thanks,

[rdorr Jan 23, 2008]

You can use WinDbg (http://www.microsoft.com/whdc/devtools/debugging/whatsnew.mspx ) and the Microsoft public symbols. These symbols are stripped but you can see symbolized call stacks and some other details. This can take you quite far.

I also know that Ken Henderson outlined how to use the public symbols to look at the SQL Server engine in his SQL 2000 publication.

I/O 前期部署最佳实践 (Predeployment, zh-cn)

中文（简体）白皮书 — Mon, 28 Jan 2008 19:29:16 GMT

发布日期 : 2007 年 6 月 5 日作者 : Mike Ruthruff 投稿人 : Michael Thomassy, Prem Mehra 技术评论家 : Robert Dorr, Stuart

Fragmentation Station - Stop #4 - How to avoid it

Chad Boyd — Thu, 14 Feb 2008 01:32:22 GMT

In the prior post , we discussed the major causes for each type of fragmentation, which followed posts

Tempdb and I/O misconceptions

LUTI @ Microsoft — Sun, 03 Aug 2008 01:15:10 GMT

function showDiv(post, lingua) { if (document.getElementById) { // DOM3 = IE5, NS6 document.getElementById(post

re: SQL Server Urban Legends Discussed

ncorp — Sat, 15 Nov 2008 08:45:41 GMT

Could you please expand on this paragraph from the above post?

<i>"Microsoft has seen that tempdb is an exception to the common rules here. Tempdb can be a high contention point for internal tracking structures. It is often better to create multiple files for tempdb so the internal latching and other activities achieve better separation (per file) so all the workers don’t cause a resource contention on a single tempdb file. This type of contention would be extremely rare to see on a user database and I have not seen that issue in a user database."</i>

Thanks

SQL Server Urban Legends Discussed

re: SQL Server Urban Legends Discussed

re: SQL Server Urban Legends Discussed

re: SQL Server Urban Legends Discussed

re: SQL Server Urban Legends Discussed

re: SQL Server Urban Legends Discussed

re: SQL Server Urban Legends Discussed

Disk Queue Length: Some Data Points may Help!

SQL Server Myths - Disk Queue length a bit like buying Guiness

Predeployment I/O Best Practices

Lenda Urbana: tabelas temporárias são escritas diretamente em disco

Lenda Urbana: tabelas temporárias são escritas diretamente em disco

Lenda Urbana: tabelas temporárias são escritas diretamente em disco

配置前の I/O のベスト プラクティス (英語)

re: SQL Server Urban Legends Discussed

I/O 前期部署最佳实践 (Predeployment, zh-cn)

Fragmentation Station - Stop #4 - How to avoid it

Tempdb and I/O misconceptions

re: SQL Server Urban Legends Discussed

配置前の I/O のベストプラクティス (英語)