SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Before going to interview with Microsoft
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/02/2005 :  12:41:21  Show Profile  Visit paulrandal's Homepage  Reply with Quote
quote:
Originally posted by spirit1

joking aside...
paul can you maybe also provide the correct answers??
because for some i have no idea...

Go with the flow & have fun! Else fight the flow



Absolutely - will do tomorrow.

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/02/2005 :  12:42:00  Show Profile  Visit spirit1's Homepage  Reply with Quote
great... thanx!

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/02/2005 :  13:13:57  Show Profile  Reply with Quote
"I actually believed you were serious for the first few answers"

You've made my day!

which for the rest of it has been spent hunched over a comparison of the Last Major Release with the Next Major Release looking for the Bannana-skin which has crept in and is going to cause clients to say our Q.A. is shot to hell ...

Kristen
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/03/2005 :  14:52:07  Show Profile  Visit paulrandal's Homepage  Reply with Quote
OK - answers (or pointers to answers - MSDN is your friend) as promised:

quote:

1) list the transaction isolation levels supported by SQL Server and describe when the use of each is appropriate


The list of isolation levels is at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_27cc.asp

In a nutshell, the choice of when to use which depends on what behavior you want to allow or can tolerate. It's a trade-off between correctness and concurrency.

quote:

2) define index fragmentation and when its appropriate to remove it


The best definition of index fragmentation is logical fragmentation. This is when the next logical page in the leaf level of an index is not the next physical page in the database. Fragmentation prevents efficient readahead (see Q15) and so slows down range scans.

Its only worth removing when you can correlate decreasing query performance with increasing fragmentation - far too many people blindly rebuild their indexes just because fragmentation has increased without understanding whether they're going to have any effect on their system's performance. See the following whitepaper I helped write for full details (we're updating this for SQL Server 2005):

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

quote:

3) explain how to make use of minimal logging mode when loading data into SQL Server


This is not just as simple as setting the recovery mode to bulk-logged - there are other conditions that have to be met. They're explained here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_9esz.asp

quote:

4) explain how to examine the query plan choice and look for performance problems


A simple explanation with further pointers is at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_536v.asp

quote:

5) explain how various relational constraints are physically manifested by SQL Server


The list of the 5 constraints we support is at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0777.asp

It's kind of a trick question because we only manifest PRIMARY KEY and UNIQUE constraints, as indexes. The others are stored in the sysconstraints table and used during qoery optimization and execution.

quote:

6) describe how to allow the use of AVG with an indexed view (just posted on this here yesterday)


The view has to include the SUM and COUNT_BIG of the column you want to AVG, then the AVG can easily be computed.

quote:

7) when does SQL Server honor locking hints?


Most of the time. There are some exceptions though. E.g.
1) when NOLOCK is specified with an UPDATE (that's just daft)
2) when NOLOCK is specified in the definition of an indexed view. No idea why this was even permitted, and its not any more in 9.0 compatibility mode in SQL Server 2005.

quote:

8) what is a deadlock and how do you debug it? What about a live-lock? What are the best practices for avoiding deadlocks?


Deadlock: When two threads are waiting for each other to release locks the other needs. A good example is at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_3xrf.asp
Debugging deadlocks: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp
Livelock: When two threads cannot progress past a piece of code but are kept busy (i.e. they're alive rather than dead). An example would be two threads, A and B, who are both stuck in loops converting objects from state 1 to state 2 and vice-versa. They cancel each others work out and can never progress.
Avoiding deadlocks: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_3xrf.asp

quote:

9) what is proportional fill?


This is the mechanism that the allocation code uses to determine which file to allocate from next when a filegroup contains multiple files. It ensures that allocations are fairly distributed between the files. Each file has a weighting, with at least one file having a weighting of 1, which ensures an allocation can always be made on the first pass through the weighting list. The weightings are recalculated after a certain number of allocations or when a file size changes or the file list changes. The pseudo-code that uses the weightings looks something like this:

while (1)
{
   currentFile = ++currentFile MOD numberOfFiles;

   if (currentWeighting [currentFile] != 1)
   {
      currentWeighting [currentFile]--;
   }
   else
   {
      // Reset the weighting again.
      //
      currentWeighting [currentFile] = storedWeighting [currentFile];
      break;
   }
}


quote:

10) what datatypes are not recommended as index keys? Why not?


The worst type for an index key is GUID, because of its randomness.

quote:

11) what is a merry-go-round scan?


This is why you cannot guarantee the order that the results of a select will be returned in unless you use an explicit ORDER BY, no matter how you defined your index.

A merry-go-round scan is when a query piggy-backs on an existing scan to reduce IO. Say two queries are issued and both require a scan of an index. Query A starts before query B and is half-way through the index when query B starts. Query B will use the results from query A's scna, and then go back to the start of the index and re-scan the portion is hasn't seen yet - so the results will look like the index scan started in the middle of the index. This is only available on Enterprise Editions.

Here (http://www.ntssug.com/admin/Portal/LinkClick.aspx?tabid=58&table=Documents&field=ItemID&id=4&link=2004-04-frag.ppt) is a presentation I did to the North Texas SQL Server User Group when I was in Dallas last April on Index Fragmentation that has a slide illustrating merry-go-round scans.

quote:

12) what data validation checks are performed using RPC?


Data range checks are not done for all types. This is fixed in SQL Server 2005.

quote:

13) why is shrinking a database not a good idea?


Don't get me started on this. There's a good reason the database had to grow its files. If you're managing your database competently, you should only ever shrink if you're making a database read-only or have deleted a large amount of info that you won't need the space again.

Basically, shrink causes fragmentation. Don't do it.

quote:

14) what is cardinality estimation? List 5 ways to screw it up.


Cardinality estimation is the process of working out how many rows will be returned by an operation and is extremely important in plan choice while optimizing a query.
Some ways to screw it up:
1) choose index keys that have poor selectivity
2) turn off statistics creation
3) turn off statistics updates so statistics become stale
4) run queries on non-deterministic computed columns
5) ok I can only think of 4

quote:

15) what is read-ahead?


There are various places we do readahead - the most obvious one is when doing a range-scan of an index, where we use the level about the leaf-level to read-in-advance the leaf pages that we'll need to process. Readahead can be done in chunks of 1, 8, or 32 contiguous pages at once - so fragmentation can prevent a run of 8 pages and so make readahead less efficient. The buffer pool itself can also choose to do readahead and DBCC CHECKDB has a sophisticated mechanism for doing readahead of multiple tables and indexes in parallel so it reads their pages in the most efficient way.

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/03/2005 :  14:59:51  Show Profile  Reply with Quote
damn paul, that is one hell of a great post.



-ec
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 08/03/2005 :  15:03:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
If I had to answer these questions, I would not have been offered the job.

Tara
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/03/2005 :  15:48:32  Show Profile  Reply with Quote
Get away with you, you could have fluttered your eyelashes!

Anyway, this is a Job Ad for someone to write DBCC, and I believe that post is already filled!

Kristen
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/03/2005 :  15:53:06  Show Profile  Visit paulrandal's Homepage  Reply with Quote
quote:
Originally posted by Kristen

Anyway, this is a Job Ad for someone to write DBCC, and I believe that post is already filled!



Nah - that was for a developer using SQL databases. Do you want my sample list of questions for the DBCC dev position (even though I have one already)?

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 08/03/2005 :  15:54:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
Those questions were given after Spirit asked to see the developer using SQL Server questions.

by Paul.

Tara

Edited by - tkizer on 08/03/2005 15:55:47
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/03/2005 :  15:54:47  Show Profile  Reply with Quote
Couple of questions Paul, if I may

"It's kind of a trick question because we only manifest PRIMARY KEY and UNIQUE constraints, as indexes"

This one I'm sure I should know: Why are there options for a Unique Constraint and a Unique Index?

"10) what datatypes are not recommended as index keys? Why not?

The worst type for an index key is GUID, because of its randomness
"

Even for a non-clustered index?

"5) ok I can only think of 4"

You did better than me!!

Krsiten
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/03/2005 :  15:57:54  Show Profile  Visit paulrandal's Homepage  Reply with Quote
quote:
Originally posted by tduggan

Those questions were given after Spirit asked to see the developer using SQL Server questions.

by Paul.

Tara



What does the picture mean?

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 08/03/2005 :  16:00:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
Sniped. It just means we replied with the same thing and your post beat mine in time.

Tara
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/03/2005 :  16:16:12  Show Profile  Visit paulrandal's Homepage  Reply with Quote
quote:
Originally posted by Kristen

Couple of questions Paul, if I may

"It's kind of a trick question because we only manifest PRIMARY KEY and UNIQUE constraints, as indexes"

This one I'm sure I should know: Why are there options for a Unique Constraint and a Unique Index?



ANSI compliance. There's no reason we have to use indexes to guarantee uniqueness. You could imagine a complex hashing scheme that would achieve the same thing.

"10) what datatypes are not recommended as index keys? Why not?

The worst type for an index key is GUID, because of its randomness
"

Even for a non-clustered index?
[/quote]
Yup - you should only have an index on a random GUID if you have a very good reason to, otherwise it causes nasty IO problems due to the random nature of additions to the b-tree.

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/03/2005 :  16:18:09  Show Profile  Visit paulrandal's Homepage  Reply with Quote
quote:
Originally posted by tduggan

Sniped. It just means we replied with the same thing and your post beat mine in time.

Tara



Well you learn something every day. Cool - now I don't have to bother for the rest of the afternoon

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/03/2005 :  16:22:36  Show Profile  Reply with Quote
Thanks Paul, makes sense now I've read your answers.

Kristen
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 08/03/2005 :  19:35:19  Show Profile  Reply with Quote
quote:

10) what datatypes are not recommended as index keys? Why not?



I thought that was a trick question... BOL sums it up nicely...
quote:

Columns consisting of the ntext, text, or image data types cannot be specified as columns for an index.



>>Yup - you should only have an index on a random GUID if you have a very good reason to...

Is "because its a logical key in my database and I have to ensure uniqueness" a good enough reason? Don't answer that....

Actually I am more interested in the role of guid data types in composite indexes..

UNIQUE CLUSTERED INDEX (StartTime DATETIME ASCENDING, ClientID GUID ASCENDING)


Where StartTime is being heavily inserted any time forward of GETDATE()...

Am I correct in visualising that the index is essential static where "StartTime < GETDATE()",
but "StartTime >= GETDATE()" is a den of inequity?

How much more "nasty IO problems" are caused by having guid's in this scenario?

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/03/2005 :  19:50:14  Show Profile  Visit paulrandal's Homepage  Reply with Quote
quote:
Originally posted by byrmol
Am I correct in visualising that the index is essential static where "StartTime < GETDATE()",
but "StartTime >= GETDATE()" is a den of inequity?

How much more "nasty IO problems" are caused by having guid's in this scenario?



Yes, before GETDATA (), the index will be static. After that, it should be layed out nicely unless you're inserting thousands of records per second. DATETIME has a resolution of 3.33 milliseconds so I think you'd be hard pushed to get more than one entry in the index for each StartTime value (unless I'm missing something). In which case, the only purpose the GUID in the key serves is to cover a query and won't have any effect on the split-pattern of the index.

Does this make sense?

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 08/03/2005 :  20:22:38  Show Profile  Reply with Quote
Thank you Paul, It made perfect sense..

It is possible (actually a business requirement) that there will be identical StartTime's.. The volume of concurrent StartTimes will rarely be greater than 30 and average about 10.

So given that my GUID will be effecting the split-pattern, is there anything I can do to ease the pain?

Expected transaction rate and volume are (I am hoping ) trivial enough to not warrant a design change..(100 - 1000 a day - Peak rate expected to be about 2 every second)

I just thought of an obscure feature request.. Secondary indexed columns can have a NOSORT option... :-)

Thanks once again Paul..


DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/03/2005 :  21:02:30  Show Profile  Reply with Quote
quote:
Originally posted by byrmol

Thank you Paul, It made perfect sense..

It is possible (actually a business requirement) that there will be identical StartTime's.. The volume of concurrent StartTimes will rarely be greater than 30 and average about 10.

So given that my GUID will be effecting the split-pattern, is there anything I can do to ease the pain?

Expected transaction rate and volume are (I am hoping ) trivial enough to not warrant a design change..(100 - 1000 a day - Peak rate expected to be about 2 every second)

I just thought of an obscure feature request.. Secondary indexed columns can have a NOSORT option... :-)

Thanks once again Paul..


DavidM

A front-end is something that tries to violate a back-end.




what about using Gert's sequential GUID generator?

http://sqldev.net/xp/xpguid.htm



-ec
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 08/03/2005 :  21:13:55  Show Profile  Reply with Quote
I saw that eyechart... It looks good, the price is right :)

ClientID is a FK so it wouldn't help in this case.. but I have another table that will love that!

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000