October 20, 2009 at 7:27 am
steveb. (10/20/2009)
GilaMonster (10/16/2009)
Index types. Never yet found someone who could tell me two types and the differences between themSeriously? I find this a bit worrying, was this for DBA positions?
You'd be amazed...
Though actually, these days, you should probably ask about 3 index types. Clustered, non-clustered, and XML. Could break XML down into two sub-types, but that's getting too fine-grained for the basic question. XML indexes can be considered a subset of non-clustered indexes, but I'd consider a plus if someone mentioned them as a "there's also..." kind of comment.
- 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
October 20, 2009 at 8:26 am
steveb. (10/20/2009)
GilaMonster (10/16/2009)
Index types. Never yet found someone who could tell me two types and the differences between themSeriously? I find this a bit worrying, was this for DBA positions?
Database developer or database performance specialist actually. I never asked people applying for DBA (administrator) questions on indexes. Would likely have been a waste of my time.
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
October 20, 2009 at 8:44 am
If all they're going to do is administer, not performance tune or write code, I'd focus on questions about backup chains and types, I/O performance, that kind of thing. Indexes are for performance tuning.
- 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
October 20, 2009 at 8:49 am
Agreed. Interviews that I did for admins I rather ask for two types of SQL backups.
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
October 20, 2009 at 8:58 am
GSquared (10/20/2009)
If all they're going to do is administer, not performance tune or write code, I'd focus on questions about backup chains and types, I/O performance, that kind of thing. Indexes are for performance tuning.
Really!
You'd be happy to hire a DBA (in the Administrative sense) that knew nothing about indexes!
I realise there is a big difference between development and performance tuning and administering, but I'd be seriouusly worried if I worked with a DBA that knew NOTHING about indexes.
October 20, 2009 at 9:21 am
Ian Scarlett (10/20/2009)
GSquared (10/20/2009)
If all they're going to do is administer, not performance tune or write code, I'd focus on questions about backup chains and types, I/O performance, that kind of thing. Indexes are for performance tuning.Really!
You'd be happy to hire a DBA (in the Administrative sense) that knew nothing about indexes!
I realise there is a big difference between development and performance tuning and administering, but I'd be seriouusly worried if I worked with a DBA that knew NOTHING about indexes.
It would depend on defined duties.
If I were looking for someone to administer the servers, including disaster recovery/prevention planning and implementation, physical performance issues (I/O, latency, memory pressure), security, data growth, log growth, storage issues (capacity, speed, redundancy), and related administrative functions, but who had nothing to do with database design, coding, code performance tuning, and related subjects, I'd be perfectly happy to hire someone who could do all the designated duties even if they'd never heard the word "index" before.
Do you have to know what an index is if your primary duty is making sure that the databases have 5-nines uptime and are virtually immune to data loss through corruption? Not really.
Conversely, if someone's job is coding, design and performance tuning, but doesn't include administration, I couldn't care less if that person doesn't know how to do a point-in-time restore, or hasn't ever heard of one. Does that person need to know how to set up an active/passive cluster? No. Do they need to understand the limitations of log shipping? Not even slightly. Do they need to understand B-trees, the differences between key columns and included columns, the difference between the leading edge and other columns, the relationship between seeks and bookmark lookups, the difference between clustered and non-clustered, the fact that the clustered index is included in every non-clustered index whether you want it or not, fragmentation, partitioning, and (in 2008) filtered indexes? You bet!
More often than not, especially in smaller companies, the person needs to be a jack of all trades, and the HR dept is looking for master of all trades if you read "necessary skills" portions of the openings listed on Monster.com and similar places. If we honestly look at the fact that very, very few, if any, people really can master both the advanced aspects of administration and the advanced aspects of design/code/tune, it's an unrealistic expectation.
Since I prefer realistic expectations, I'll look for people who know enough about both to hold a single-hatted generalist job, or look for specialists who really know whichever one of those is relevant to what I need. It would depend on the real needs of the company, of course.
- 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
October 20, 2009 at 11:03 am
GSquared (10/20/2009)
Ian Scarlett (10/20/2009)
GSquared (10/20/2009)
If all they're going to do is administer, not performance tune or write code, I'd focus on questions about backup chains and types, I/O performance, that kind of thing. Indexes are for performance tuning.Really!
You'd be happy to hire a DBA (in the Administrative sense) that knew nothing about indexes!
I realise there is a big difference between development and performance tuning and administering, but I'd be seriouusly worried if I worked with a DBA that knew NOTHING about indexes.
It would depend on defined duties.
If I were looking for someone to administer the servers, including disaster recovery/prevention planning and implementation, physical performance issues (I/O, latency, memory pressure), security, data growth, log growth, storage issues (capacity, speed, redundancy), and related administrative functions, but who had nothing to do with database design, coding, code performance tuning, and related subjects, I'd be perfectly happy to hire someone who could do all the designated duties even if they'd never heard the word "index" before.
Do you have to know what an index is if your primary duty is making sure that the databases have 5-nines uptime and are virtually immune to data loss through corruption? Not really.
Conversely, if someone's job is coding, design and performance tuning, but doesn't include administration, I couldn't care less if that person doesn't know how to do a point-in-time restore, or hasn't ever heard of one. Does that person need to know how to set up an active/passive cluster? No. Do they need to understand the limitations of log shipping? Not even slightly. Do they need to understand B-trees, the differences between key columns and included columns, the difference between the leading edge and other columns, the relationship between seeks and bookmark lookups, the difference between clustered and non-clustered, the fact that the clustered index is included in every non-clustered index whether you want it or not, fragmentation, partitioning, and (in 2008) filtered indexes? You bet!
More often than not, especially in smaller companies, the person needs to be a jack of all trades, and the HR dept is looking for master of all trades if you read "necessary skills" portions of the openings listed on Monster.com and similar places. If we honestly look at the fact that very, very few, if any, people really can master both the advanced aspects of administration and the advanced aspects of design/code/tune, it's an unrealistic expectation.
Since I prefer realistic expectations, I'll look for people who know enough about both to hold a single-hatted generalist job, or look for specialists who really know whichever one of those is relevant to what I need. It would depend on the real needs of the company, of course.
Interesting quotes, Makes we wonder why I dont work in the US, I have been a production DBA but I have been doing reporting services since the beta, I know SSIS as well as log shipping, clustering, mirroring. I can do development work and have done so, I can performance tune. I can basically turn my hand to anything database related.
Compared to the UK, there is no distinction between job titles. most if not all DBA jobs in the uk, mention performance tuning as well as the BI tools, as well as T-SQL. There is no distinction as performance tuning being specific to one type of job. As far as I have seen you need to be aware if not capable of all aspects of sql server. the questions I have encountered over the years encompass every subject. questions about Indexes turning up in practically every interview as well as monitoring, disaster recovery, how to tune queries, execution plans as well as high availability. if would be nice if dba roles wanted specific skills, which you could focus on learning, but in my experience that isnt the case and you need to have a broad experience to meet the current skills that employers look for nowadays.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 20, 2009 at 11:33 am
Silverfox, if you notice my second-to-last paragraph, I pretty much cover what most companies are looking for, and that's what you're describing.
My contention is that generalization produces less efficiency than specialization. The cost of an expert generalist, with competence in all fields of a subject, is higher than the cost of an expert specialist in just the area desired.
That may not seem obvious at first glance. Specialists often have a higher price tag than generalists. But, in the vast majority of cases, the total cost is lower.
For example, let's say you have a database desperately in need of performance tuning. You have two people available for the job:
DBA 1 is a generalist. He's competent at admin, tuning, design, modeling, SSIS, SSRS, Windows server and domain set-up, hardware configuration, networking, and he's a good short-order chef (just because). Because of his wide field of competence and two decades of work with SQL 2008, he charges $200/hour as a consultant.
DBA 2 is a performance tuning specialist. He's heard of Windows, he been told that backups are a good thing, he knows there's storage but would think that RAID refers to what he does with his World of Warcraft crew if you asked him about it, and so on. At the same time, he's familiar with more aspects of T-SQL performance by a proportionate amount than DBA 1. Because he's a world-famous specialist with books published on the matter, he charges $500/hour.
Looks like the generalist is cheaper? Not likely. First, he'll take longer to get the job done, which may or may not soak up the cost difference. Even if it doesn't, hiring him has an opportunity cost because your in-house DBA (probably a generalist, just not one with 2 decades on SQL 2008) would learn more from 2 than from 1. It has another cost in that you won't get as performant a database, which is usually compensated for with higher power hardware (cost), lower performance long-term (cost), and the need in some cases to redo the job again later.
Most people won't take any of those non-immediate-financial costs into account. The phrase is "penny wise, pound foolish", or "penny wise, dollar foolish".
Want a real life example? Who would you rather have if you needed to recover from a crashed database: Gail Shaw or me?
I'm a generalist, with a bit of a specialization in design and performance tuning, and a heavy specialization in business automation. Gail's a specialist in performance tuning and database recovery. She's much better at that than I am.
On the other hand, which would you prefer if you needed an SSIS package set up on a complex workflow? Gail doesn't touch SSIS if she can avoid it. I've automated two whole businesses with it, saving literally millions of dollars per year for each of them, and gaining significant competitive advantages for each because of speed of delivery on the affected business lines.
Either of us will serve if what you're looking for is an SQL Server generalist. We're both well above average in the subject in general, and both obviously able to learn more when needed/desired.
But a business person who doesn't take specialization into account is essentially ignoring all of the history of human civilization and all the lessons from it. In Hollywood's universe, the underskilled underdog always wins. That's why it's fiction.
- 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
October 20, 2009 at 11:41 am
I think Silverfox and I are in agreement (maybe because we're both in the UK)... I have never worked at a client were there has been such a rigid distinction between the various possible DBA duties.
Even in larger companies (where specialisation is more possible), there was a certain amount of fluidity between development and production DBAs.
If the "Administrators" didn't have to do performance tuning, they were still expected to have a damn good idea of the concept of indexes.
Likewise, development DBAs were expected to know the concept of backups and restores... chances are they would be having to backup and restore their development databases.
Edit. Added below after seeing Gus's last post.
Your point about the specialists is valid... but I bet you still know the concept of recovering from corruption, and I bet Gail knows what SSIS is all about.
I hate the idea of XML in a database, and avoid it where I can... but I still know the concept and can recognize it when it hits me in the face.
Therefore I would expect an Administrator to know what an index was.
October 20, 2009 at 11:49 am
Trust me, that's not just a UK thing. I've never encountered it yet in the US. I was asked what I would do. I consider most managers and business owners more than a little undereducated on the concepts of efficiency and effectiveness.
(No, I'm not in an invory tower on the subject. I've got more training in business administration and executive function than an MBA does, and I've operated strategic programs that measured their results in millions of dollars per week. Just don't have a degree in the subject because the training didn't follow the accreditation rules for US schools.)
- 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
October 20, 2009 at 2:20 pm
Ian Scarlett (10/20/2009)
I think Silverfox and I are in agreement (maybe because we're both in the UK)... I have never worked at a client were there has been such a rigid distinction between the various possible DBA duties.Even in larger companies (where specialisation is more possible), there was a certain amount of fluidity between development and production DBAs.
If the "Administrators" didn't have to do performance tuning, they were still expected to have a damn good idea of the concept of indexes.
Likewise, development DBAs were expected to know the concept of backups and restores... chances are they would be having to backup and restore their development databases.
Edit. Added below after seeing Gus's last post.
Your point about the specialists is valid... but I bet you still know the concept of recovering from corruption, and I bet Gail knows what SSIS is all about.
I hate the idea of XML in a database, and avoid it where I can... but I still know the concept and can recognize it when it hits me in the face.
Therefore I would expect an Administrator to know what an index was.
I agree, and since I asked the original question I intended these questions to be geared more toward DBA's and as far as I am concerned I daal only with two types, production and development and I do work in the US. The other job types that have come up I have never heard of before and is kind of off the point anyway. I asked for "simple" interview questions that are not answered in an interview, not an explanation of the many different job description within database operations. I gave an example of this as well and would expect both to know the answer. . In either production or development scenario, I agree with you, I would be extremely concerned if someone did not know what the two types of indexes were. Knowing XML indexes would not concern me as much as not knowing Clustered and Non-Clustered Indexes and know the basic difference between them since both job descriptions would have to create them in both development environments. Production DBA's usually strictly create them in QA and production environments. So they both need to know the difference. as far as I am concerned anyway...
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
October 20, 2009 at 2:32 pm
And you might be shocked by how many people who call themselves DBAs or who even have jobs as DBAs can't answer that question. I've met dozens.
- 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
October 20, 2009 at 2:56 pm
GSquared (10/20/2009)
And you might be shocked by how many people who call themselves DBAs or who even have jobs as DBAs can't answer that question. I've met dozens.
Agreed, but has been discussed before on this forum, there are many DBA's who chose to be a DBA by choice in their career path and then there are many who have been thrust into the job with no choice at all. There is a big difference in desire and technical aptitude IMHO, and I do ask that screening question very early in the interview. It helps me further direct my interview path just a little different...even still though, it is a big red flag if they don't know simple questions...
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
October 20, 2009 at 3:46 pm
GSquared (10/20/2009)
And you might be shocked by how many people who call themselves DBAs or who even have jobs as DBAs can't answer that question. I've met dozens.
On that point we do agree.
Although, having done no end of interviewing over the years, I can't say I'm shocked any more, but I am appalled that people who claim 5+ years of experience (and even MS Certification) would get no more than 3/20 on the 20 "simple" questions we would ask at the beginning of the interview e.g.
what is the difference between a clustered/non clustered index
what is a trigger
what is referential integrity
what does truncate table do
what is the difference between simple and full recovery model etc etc.
Anybody who had 5 "real" years of experience should be getting 15+/20 easily.
October 20, 2009 at 3:59 pm
GilaMonster (10/16/2009)
GSquared (10/16/2009)
Of course, if that particular page was affected by some transaction in the lost file....As far as I'm aware, it doesn't matter. With a missing log file, the page can't be restored alone. That requires restoring the page and all of the logs since the full, regardless of whether the log backup in question had a transaction affecting the page or not.
If I am not mistaken, this issue could be avoided completely by setting up a database mirror in SQL Server 2008. SQL Server 2008 will attempt to automatically repair a corrupt page from the partner database.
At least, that is what is shown here: http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Automatic-Page-Repair-with-Database-Mirroring.aspx
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 16 through 30 (of 51 total)
You must be logged in to reply to this topic. Login to reply