Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
carter-greenan@hotmail.co
Starting Member
1 Post |
 Posted - 2007-10-26 : 20:42:00
|
| Hi, I have recently qualified as a MCITP - DBA with SQL 2005, but as I have no commercial experience, have been strugglling to get into the industry. However, I have finally been offered an interview on Monday. The problem is that the company use 2000, not 2005. I think I am pretty much up to speed on the differences, but was wondering (nay, pleading) if anybody could let me know the main commands for analysing query performance and index statistics etc, with a reference to thier equivalent DMV in 2005 (which I am totaly familiar with) This is something I could easily pick up whilst doing the job, but I am aware that there will be an SQL test to asses my knowledge during the interview and I am desperate not to get caught out. Please help! |
|
|
richard75013
Starting Member
19 Posts |
Posted - 2007-10-26 : 21:06:17
|
| not a whole lot changed between the 2 in the way of TSQL. The management interface is Enterprise manager and the layout of the management tool changed considerably. Install a eval version at home and play with it. could let me know the main commands for analysing query performance:CTL + L to show the execution plan or it on the menu. you want to avoid scans, you want everything to be a seek unless its a small table. You want to aviod bookmark lookups because of the additional IO to get the rest of the data that the nonclustered index didnt supply.Index Statistics:the same as 2005, dbcc showcontig, you want the logical frag to be as close to 100 as possible and you want the extent frag to be as close to zero as possible. the extent stats can be misleading if the table is spanned across > 1 file in that filegroup so you should always use Extents vs extent switrches to get a realistic picture. in the best case there should be 1 less extent switch than extents because you start on the 1st extent. I find using the with tableresults command more useful.make sure you have the common things down 1st like how many clustered indexes you can have and why you can only have 1 and how many noncl you can have and what is a FK and how is it used and what is a PK and why do you need one and what is a Unique constraint and is a PK always a clustered and when will it be by default and why are SPs better than adhoc and where do you maintain logins and how do permissions work, (deny is always checked 1st if non sa member) and what a default db is for and how to back up a database and what recovery options are avail and what a torn page is and how it works and where and how to set up jobs, operators and alerts and how to configure server settings GUI and sp_configure and when you can use AWE and when it doesnt matter and what is set to and what the msdb database used for and what is held in it and what is tempdb used for and what is model used for and how to restore to a point in time and what are the 3 types of replication in 2000, and how to restore to the latest time when you are using full, differential and log backups of full, incremental and log backups, etc.P.S. I like SQL interviews, I am usually on trhe other end giving them but their fun.Thanks..Richard |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-10-27 : 00:14:11
|
| wow richard. nice. and i like interviews too and like you, i am usually the one conducting 'em. but i always feel like as the candidate that this is my chance to really shine.carter, couple of random thoughts: know that ssis does not exist in sql 2000. dts is the ETL program shipped with 2000.also, sql 2000 SE can not use above 2gb memory as sql 2005 SE can. |
|
|
|
|
|
|
|
|
SQL 2000 v 2005 - URGENT, PLEASE HELP!