January 22, 2009 at 2:14 pm
ashepard (1/22/2009)
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)
I don't know of one but, perhaps
select * from sys.dm_exec_requests
Not everything will be running (normally) but it will show all of the processes, internal and external (session_id <= 50 is system)
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 2:16 pm
Do interviewers ask these questions? It has been a very very long time since I have gone for an interview... (Thank God for that). When I interview for post, I ask them more practical things like what will you do if such and such situation arises...
-Roy
January 22, 2009 at 2:35 pm
Yeah, interviewers ask such questions. It's a good lead in to test simple basics before you get into "what would you do if..." type stuff.
There's no real point in getting into complex, or even relatively simple, problem-solving skills, if the person can't handle simple basics.
On the other hand, the opening question here, about "behind the scenes behind the tables and stuff" isn't simple basics. It's a very vague question, where you can only hope that you understand what the person is asking for.
As an aside, how often in a DBA's career does it really matter that pages are 8k in size, if that's what was being looked for? Sure, it can matter, but how often does it matter compared to things like normal form, or cursors, or avoiding cartesian joins, and a million other practical considerations? Or, for an administrator with no architect/dev duties, how often does it matter compared to knowing the business end of the backup command, the details of mirroring and replication, or how to handle it when a database crashes and you have to switch to the log-shipped backup that's in another part of the country?
I'm not sure what the interviewer was looking for, but I hope it was something more pertinent than the details of how tables actually store data.
- 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 22, 2009 at 3:48 pm
GSquared (1/22/2009)
how often in a DBA's career does it really matter that pages are 8k in size,
database sizing springs to mind, something a DBA may well be required to do
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 23, 2009 at 5:57 am
GSquared (1/22/2009)
As an aside, how often in a DBA's career does it really matter that pages are 8k in size, if that's what was being looked for?
We ask questions like this in interviews. Not because we want someone to come up with precise specs on row size to page to extent, blah, blah, but because we want to get some understanding of the level of depth of knowledge. These are meant as open-ended discussion type questsions, not trivia contests (the trivia contest is the phone screen where we ask 10 questions, of which, most people fail on 8 and believe me when I tell you, very few people out there with five years experience as a dba know the difference between a clustered and nonclustered index or the difference between a deadlock and a block).
Anyway, no, most of the time it doesn't matter that the page size is 8k. Day-in, day-out, you don't use that knowledge. But, understanding that there is this thing called a page and that getting more data on to it, rather than less, and it has a limit, that maybe you need to hit BOL to remember, fine, but you understand how things are put together. That's the kind of information we're looking for. Of course, this is for senior and above DBA's. Junior DBA's we expect them to know how to run a backup outside the GUI.
"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 23, 2009 at 6:52 am
Pages, clustering, processing, fragmentation and disk implimentaion.
4k, 8k, 16k, 32k - it depends from what I have been told.
First - the slowest part of the system is the disk. I've been told to make database page size an even multiple of disk page size. Note 0 is technicaly not an even number. Why make something a 4k page if the system stores things in 8k, 26k or 32k pages?
Second - what is the average row length? If you are working with short rows, like a domian table of values then a smaller page is helpful. If you are working with long text data, LOBs BLOBs for GIS then 64k page may be helpful.
Processing - if it is an online system processing one row at a time (OLTP) then smaller page holding a few rows is good. If it is a data warehouse or reporting sytem then many rows may be processed so larger pages may help.
Yes the new Hitachi SSD, a RAMSAN or disk in memory makes this point mute as all data is transfered at memory speeds. A RAMSAN can do 500,000 I/Os per second, not 150.
Hope this helps.
January 23, 2009 at 9:34 am
Perry Whittle (1/22/2009)
GSquared (1/22/2009)
how often in a DBA's career does it really matter that pages are 8k in size,database sizing springs to mind, something a DBA may well be required to do
Yes, definitely, but how often do you need to do that compared to how often do you need to know how to restore to a point-in-time? Compared to how often you'll need to set up and read a trace? How often you'll need to check for and handle deadlocks?
My point isn't that it's useless. My point is that I would consider a large number of other things significantly more important for a DBA to know.
Imagine these two scenarios:
1. Suddenly your database is giving you dozens of "this process has been assigned as the deadlock victim" issues. Your DBA has to go into Books Online to figure out what a deadlock is, what a victim is, and whether there's anything that can be done about it. Customer web pages are crashing because of the deadlocks. Orders are being lost.
2. You're doing annual reviews on hardware and software needs for the coming year. You need the DBA to figure out how much disk space should be allocated on virtual machines for databases, log files, traces, et al. He has to look up how much data goes onto a page in order to figure this out. He only has one month to get this data, so it can be included in planning and budgetting.
Which one of these two scenarios is more likely to end up with someone being fired?
Based on that, which one of these two data should be included in determining who should be hired in the first place? (Assuming that the purpose of an interview is to hire someone who won't have to be fired later.)
- 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 23, 2009 at 9:35 am
Grant Fritchey (1/23/2009)
GSquared (1/22/2009)
As an aside, how often in a DBA's career does it really matter that pages are 8k in size, if that's what was being looked for?We ask questions like this in interviews. Not because we want someone to come up with precise specs on row size to page to extent, blah, blah, but because we want to get some understanding of the level of depth of knowledge. These are meant as open-ended discussion type questsions, not trivia contests (the trivia contest is the phone screen where we ask 10 questions, of which, most people fail on 8 and believe me when I tell you, very few people out there with five years experience as a dba know the difference between a clustered and nonclustered index or the difference between a deadlock and a block).
Anyway, no, most of the time it doesn't matter that the page size is 8k. Day-in, day-out, you don't use that knowledge. But, understanding that there is this thing called a page and that getting more data on to it, rather than less, and it has a limit, that maybe you need to hit BOL to remember, fine, but you understand how things are put together. That's the kind of information we're looking for. Of course, this is for senior and above DBA's. Junior DBA's we expect them to know how to run a backup outside the GUI.
Totally agree with you on all points.
- 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 23, 2009 at 1:26 pm
ashepard (1/23/2009)
I've been told to make database page size an even multiple of disk page size.If you are working with short rows, like a domian table of values then a smaller page is helpful. If you are working with long text data, LOBs BLOBs for GIS then 64k page may be helpful.
If it is an online system processing one row at a time (OLTP) then smaller page holding a few rows is good. If it is a data warehouse or reporting sytem then many rows may be processed so larger pages may help.
Are you thinking of a different database engine? In SQL the page size is 8k and that is fixed.
Ot have I misunderstood what you were saying?
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 23, 2009 at 2:49 pm
Gail,
Hi. No its me thinking of Oracle where one tries to match the page size to the physical disk so things are read in cosecutive sectors.
I did not know there was a fixed 8k page size.
So does that mean the disk should be formatted for 8k sector size?
Allen
January 24, 2009 at 1:20 am
ashepard (1/23/2009)
So does that mean the disk should be formatted for 8k sector size?
No.
There's lots of info on IO config for SQL. Google should find them for you, or you can look on the white paper list here - http://sqlskills.com/
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
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply