January 17, 2009 at 4:34 am
Hi! I am a SQL Server DBA in WA, I was recently laid off from a major bank and I am actively interviewing. Today I went to an interview with this one company, I did ok on indexes, triggers, administration, joins, etc., but there was one question that I did not know how to answer, which was: "Could you explain the architecture of SQL Server?". I asked him what he means and he said "how does SQL Server store data"? I said SQL Server stores data in files, mdf is the data file and ldf is the transaction log. He said "I guess I am looking for more details on what's going on behind the scenes". I couldn't come up with anything and he didn't seem happy.
Now, when I got home, I read a chapter in my SQL Server Bible on pages, extents, etc. but I must admit, I don't understand anything it's talking about :w00t:
Finally my question is, what would you gurus on the forum expect to hear in response to that question? Thanks!
January 18, 2009 at 10:51 am
he was probably looking for something along the lines of
sql server stores data on disk in pages which are 8Kb in size. 8 pages make up one extent and these are stored inside the parent data file. Sql server reads data pages from the disk file into memory and page modifications are made in RAM. The log file is written too serially and is the undo/redo file when database startup\recovery occurs. Data is flushed from memory periodically and written to disk
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 18, 2009 at 11:18 am
Two excellent places to look for details on the storage engine - http://blogs.msdn.com/sqlserverstorageengine/default.aspx and http://www.sqlskills.com/blogs/paul/
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
January 18, 2009 at 12:38 pm
In addition to the web sites Gail provided (and the storage engine web site from Microsoft is just fantastic), you might want to read through Kalen Delany's book on the Storage Engine.
"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
January 20, 2009 at 2:36 pm
Hmm, I like the answers but there is so much more.
SQL statement comes in, is parsed, is checked for syntax and authorization against the current database and security tables. The resultant code generates a plan based on indexes and statatistics. A temp tables are formed for rows fetched and row access is checked agains the lock table to see if what if any locks exsist on the index and table rows.
Locks are also applied based on the execution of your SQL. Data is checked to see if the rows you want are already in memory or if they need to be read from disk.
The resultant tables are processed and accumulated untill the final resultant table is created. Since it is a pure select no log files are created but the deadlock engine runs. In the final tables if you wanted jus the "Top X Rows Only" then that rule is applied to the final resultant table and processing stops. All select locks are released.
Finaly the results are displayed back to you.
This may be what the inteviewer was asking about - but I could be wrong.
January 20, 2009 at 3:04 pm
I'm going with he wants to know about the pages, extents, et al, but, like everyone else who isn't the person who asked the question, I'm guessing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2009 at 11:56 pm
ashepard (1/20/2009)
A temp tables are formed for rows fetched
They are?
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
January 22, 2009 at 5:43 am
Hi,
As per my knowledge...
Sql Server Structure..
having physical and logical database
physical database contains File groups and Transcation logs
Filegroups contains datafiles
datafiles contains tables
tables contains extents
extents contains pages
pages contains rows
Transcation log is user defined file group by default.it's primary file group
Logical database objects like views,storedprocedures,triggers,functions.
please let me know any thing wrong.
January 22, 2009 at 7:16 am
Gail,
Hi. Yes there is a temporary internal table for the resultant set rows that the cursor runs against I believe
(Source sybase which was SQL 6.0, 6.5 and 7.0 I think : http://manuals.sybase.com/onlinebooks/group-asarc/srg1100e/sqlref/@Generic__BookTextView/61444;pt=60135/*
I read about this somewhere but have not found the book.
Temporary table (temp and global temp) was proabbably a bad term as they refer to temp tables people create v.s. temp table SQL creates.
I guess I'm odd-man-out on the architecture as well. Ted Codd always said SQL is to be independent of the physical implimentaion.
Let me know if I got the table thing wrong.
January 22, 2009 at 7:28 am
ashepard (1/22/2009)
Hi. Yes there is a temporary internal table for the resultant set rows that the cursor runs against I believe
That's cursors. Not general queries
For general queries, the rows are passed from operator to operator through the query processor as an in-memory result set.
(Source sybase which was SQL 6.0, 6.5 and 7.0
The earliest versions of SQL were sybase with a different name. The last version that bore any resemblance internally to sybase was 6.5. 7 was a total rewrite of the DB engine. For any version past 6.5, I would not trust the sybase manual any more than I'd use a SQL internals manual to understand the internals of the latest sybase engine.
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
January 22, 2009 at 9:32 am
Good point about the Sybase stuff
The "in-memory result set" - is that a table ?
Not a physical table kept on disk, just a set of one or more columns with zero or more rows?
January 22, 2009 at 9:47 am
ashepard (1/22/2009)
The "in-memory result set" - is that a table ?
Depends on your definition of table.
Do you refer to the output of a query as a table? If so, then yes, that's a table
I prefer to say resultset or rowset (interchangeably), as that better describes what it is - a set of rows, whereas the term table brings to mind the things that CREATE TABLE creates.
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
January 22, 2009 at 9:59 am
Back to the original question, I would speculate there were a lot of possible correct answers, and what the question was really about was probing your level of understanding of how SQL Server really works and your ability to explain a difficult technical concept.
In other words, it was testing both your level of expert knowledge and your ability to communicate that knowledge.
January 22, 2009 at 2:03 pm
Gail,
No you are right. Table is something more physical than a view which is more than "a resultset"
They may all be 'tables' but as you said table referes to a real place to store data.
Gail, do you have a good web rescource showing all the internal procecess that SQL 2005 or 2008 runs (log writer, dead lock detection, memory manager, etc)
The guy who runs the EMC SAN keeps saying "a database can only do one thing at a time" and I would like to show that SQL server does many things at once.
Thanks in advance,
January 22, 2009 at 2:06 pm
One thing at a time? Is this guy using a computer built in the 1950s?????
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply