Partager via

More follow up from my TechNet SQL 2005 presentation (14/6/05) - SMP boxes and database files

  • Article

During my TechNet presentation on Tuesday evening (14/6/05 in Reading), I got a little side tracked and, while talking about partitioning in SQL 2005, found myself drawing my audiences’ attention to the fact that only one thread can access a database file at any one time.  This fact being the foundation to my post https://blogs.technet.com/mat_stephen/archive/2005/02/02/365325.aspx

I misused the word access, more than one thread can access a database file at any one time for ‘read’.  What I had in mind was the fact only one thread can open a file for ‘write’ – hence the blog entry above.

In the same breath I said that ‘not many people know this’, and they don’t.  Not surprisingly I’ve since had some emails on this subject, all asking for more information.

For sometime I’ve struggled to find evidence for my assertions in this area – forgetting how I was first alerted to the issue and only being able to point to the win32 openfile api call for evidence.

Well I’ve now found the documentation I’ve been looking for all this sometime.  At last I’ve found it again here https://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx, look under the heading 'Parallel Data Retrieval'.

For those of you still keen to find out more in this area, I point you to another document dealing with a real scenario involving a SAN and the added complexity introduced by a SAN.  Checkout this 10Tb project to detail the Human Genome @ https://www.microsoft.com/sql/techinfo/administration/2000/rosetta.asp.

Hope this helps clarify this important and little appreciated fact.  I've seen the startling evidence myself - but I know this is not enough for you hard nose sceptics amongst you - I know this because I too am a hard nosed sceptic.

Comments

  • Anonymous
    June 17, 2005
    Could you clarify when the "disk" is actually a hardware array - doesn't this allow more than 1 write thread?
    p.s. another excellent presentation - thanks.
  • Anonymous
    June 20, 2005
    Hi Colin, either way, even if the disk is a RAID array, you can't have more than one thread write to the file simultaneaously.