May 9, 2014 at 1:02 am
Hello,
Sorry if the title threw you off... but I am still trying to get how the whole entire SQL works... so starting from what I know, the .mdf file has many pages depending on how long the row is etc, and those page files.... well if you look at the rootnode.gif, my first question...
1. looking at the rootnode.gif file that is attached, are all those page files, such as the root node is a page file, the intermediate files are page files, the leaf nodes are page files etc.? or that entire thing from root node to leaf node all of it makes up a page file?
2. Now looking at the clustereddiagram.jpg file, how does a table that has a clustered key fit in the with rootnode.gif diagram? any examples, pictures would be best etc.? Also what if the table didn't have a clustered key, would it be structured differently than the clustered diagram.jpg?
3. in a simple sentence, what is a page file?
let me know thanks in advanced 🙂
May 9, 2014 at 1:25 am
Page file isn't something in SQL Server. It's a file the OS uses for 'extra' memory (so it can move apps from memory into the page file if there's memory pressure)
What you have in those pictures are pages, not files.
Give these a try:
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2014 at 5:56 am
What you have in diagram 1 is a definition of an index and the balanced tree, or b-tree, that defines. Yes, each little box in that diagram is a page (as Gail noted, not a page file, that's totally different). A page represents up to 8k worth of data and that is how most of the information stored within SQL Server is organized. Even heaps, tables without clustered indexes, store the data into pages to write it to disk. Further, when reading from disk, it reads by page and moves that same 8k of information into memory. Diagram 2 is just showing a clustered index b-tree and a nonclustered index b-tree and the relationship between the two. A nonclustered index doesn't store the data. It only stores the key column(s) and any INCLUDE columns. To get back to the data it points either back to the clustered index key, or, in the case of a heap, back to the page location on disk. A page is how data is written to disk, and to memory, within SQL Server. That's the simplest explanation. After that, it gets odd.
But don't call it page file. It's not a page file.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 9, 2014 at 2:14 pm
Cool sorry for the confusion, yes page not page file, also one other question in regards to heaps and clustered index, the difference between the clustered pages and heap pages is that clustered pages has indexes on the pages or no?
thank you again all for the clarification and quick answer.
May 9, 2014 at 3:29 pm
Did you read the articles I referenced?
Wrong way around, pages don't have indexes, indexes have pages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2014 at 8:10 am
The pages on the disk are fundamentally the same, heap or index. It's what gets stored on those pages and how one page is related to another that is different. And, as usual, I agree with Gail.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply