ASE: Q) What are the main advantages and disadvantages of using identity columns? A) The advantage is in the ordering of the table and creation of unique rows, disadvantage is identity gaps. Q) How to bcp out a subset of rows from a large table? A) Create a view with the subset and bcp out via it. Q) What does set forceplan on do? A) Forces the join order for queries to be the order in which the tables appear in the from clause. Q) How do you find out which process has filled up tempdb? A) select * from master..syslogshold but will need to create an additional temporary database and bind it to your login first. Q) What are the options for doing an upgrade to a Sybase server? A) Can do a Sybase upgrade but much better to create a new server and migrate over the databases/logins etc. Q) What does the term covered query mean? A) It is if the columns specified in a query (columns to be returned plus columns in where clauses) are completely satisfied by an index. Q) What is the difference between an MRU and an LRU caching strategy? A) It stands for most recently used and least recently used and defines which records are swapped out first, the default is LRU i.e. the pages least recently used get flushed out when more space is needed. MRU is referred to as fetch and discard and is ideal for queries where a page is only needed once by the query for example table scans on heap tables. Q) Why would you want to use an MRU index? A) MRU is ideal for queries where a page is only needed once by the query for example table scans on heap tables or where you have more than one table in a join query. Q) In Adaptive Server 11.9.2 or earlier explain the difference between a character and a block device and when they are typically used or could be used A) A character or un-cooked/raw device writes directly to the disk and guarantees transactions are written to the disk immediately. A block or cooked device does not write to the disk immediately, which can cause the database to lose its integrity should there be a media failure. Character devices are normally used for live databases. Using block devices in Tempdb can in some circumstances improve performance for applications that create a lot of temporary tables. Q) Briefly explain what the difference between a transient and hard 605 error. A) A transient error: Operating system notifies SQL Server that an I/O operation is complete when it is not. Any time buffer information about a page is different from the disk information about the page (and the disk has the correct information).  ~ Normally a dbcc checkdb and dbcc checkalloc should produce no more 605 errors. A hard error: Normally hardware related can be caused by in-correctly formatted disks (overlapping partitions) or disk media failure. Q) Explain the difference between slow and fast BCP and how to apply this. A) A slow BCP leaves indexes in place and is the default.     Fast BCP involves dropping the indexes/triggers for the table in question. In fast BCP the indexes are recreated after the data has been loaded in. The BCP –A flag can be used to increase the default Network packet size. This can increase the speed of a BCP, however the under-lying UNIX o/s needs to be changed to support the larger packet size.  In addition you would need to allocate additional network memory in the Sybase dataserver which is used by the bigger network packets. Replication server: Q) What is the Save interval used for pertaining to the Stable Queue. A) It determines how long the stable queue will wait until it deletes information already applied to the replicate db. It is specified using rs_configure save_interval(in minutes). It can be used for redundancy in the event of a replication system failure. The save interval lets the sending Replication Server re-create all messages saved during the save interval. Q) What two Replication Server parameters should always be increased from the default values. A) The 2 most important are memory_limit which sets the total memory available to the Replication server and sqt_max_cache_size which sets the limit of cache which is available to the stable queue Q) When a DSI Thread suspends due to a transactional error, what are the two methods used to force resumption of the queue. A) Resume connection to dataserver.database skip transaction set autocorrection on for replication_definition with replicate at data_server.database Q) What Replication Server command shows all the configuration parameters pertaining to a Replication Server. A) rs_configure – run in RSSD for Replication Server. Q) What is the ID Server in a replication environment? A) It is the master replication server which holds information on all the other replication servers. Q) What happens if the ID Server goes down in a multi repserver environment. A) It runs ok until you try and update any rep defs etc or restart any of the other replication servers. Q) How would you find what command caused a dsi to go into suspend? A) Check the rep server error log. Q) If the error log indicates that dsi is down due to duplicate key error what do you do? A) First do a sysadmin log_first_tran to output the bad transaction, it can then be viewed with rs_helpexception. If you decided it can be skipped then do a resume connection with skip_transaction. Q) What is autocorrection? A) It is an option set on a rep def which converts updates and inserts into a delete followed by an insert, this is useful in dev environments where you tend to sometimes get a lot of duplicate transactions. Q) How do you determine the time of the last transaction applied to the replicate database? A) rs_lastcommit Q) What is the easiest way to completely replicate a database from London to Singapore and New York? A) Use MSA, database replication definitions. Q) When was MSA (database replication definition) introduced? A) Version 12.6. Q) Does MSA replication work over a WAN? A) Yes I think so, I can’t see why not, warm standby works over WAN. MSSQL: Q) If someone has a problem with a spid, how would you investigate it? A) Use the activity monitor, and perhaps dbcc inputbuffer, dbcc opentran to show longest running tran. Q) How would you put a table completely into memory? A) You can’t Q) How do you change the default location of a database? A) You can specify the default location for data and log files in the server properties, database settings. Q) What is the difference between dbcc reindex and dbcc index (defrag)? A) dbcc reindex is an offline procedure, dbcc index (defrag) is performed online. Q) How would you check the statistics for a table? A) Run dbcc show_statistics against it. Q) What things would you configure on the analysis server, high level? A) Increase minimum allocated memory, increase read-ahead buffer size. Increase Process buffer size, enable Proactive caching, maybe increase maximum number of threads. Q) There are a few option which MSSQL server lets you set which shouldn’t be set on a production server? A) You should NOT enable Auto Shrink or Auto Close and a production database should be set to full or bulk logged recovery model NOT simple. Q) What services can be clustered in SQL Server 2008? A) SQL Server, SQL Server agent, analysis services, reporting services. Q) How can you set the memory of the server to not go above 3GB? A) In server properties set both the minimum and maximum server memory to 3GB. Q) How would you move the tempdb? A) Execute the following 2 commands: alter database tempdb modify file (name=tempdev|templog, filename=’c:\Sqldata\tempdb.mdf|templog.ldf’), then restart the server and the tempdb will be created on the new devices in the new location, you can then remove the old devices. Q) In SQL Server 2005 what is port 1434 used for? A) The SQL Server browser uses, by default, port 1434 to listen for incoming connections. Q) Where is logging information kept within sql server? A) You can access the logs from the SQL Server management Studio, both SQL Server, System and SQL Server Agent logs can be viewed but the actual physical logs are on the file system. Q) How do you start a SQL Server form the command prompt in single user mode? A) To start the default instance it’s sqlserver.exe –m for a named server it is sqlserver.exe –m –s <servername>. Q) What are the differences between clustered and non clustered indexes? A) A clustered index forms and orders the table where the leaf level is the actual data page whereas a non-clustered index is separate from the table and just points to the relevant pages within the table. Q) How would you display fragmentation information for a specific table? A) Use dbcc showcontig command against the table or index. Q) What command would you use to check the integrity of a database, what option to check the physical allocation only? A) You would use dbcc checkdb and if you only want to check the physical allocation then you add the Physical_only option, i.e dbcc checkdb (‘dbname’) with physical_only. Q) What is locking and how would you get information on locks? A) Locking is the process of ensuring data consistency in a database, so for example as one process is updating a row of data it will not allow another row to update it at the same time. You can get information on locks by using the sp_lock stored proc, querying the dm_tran_locks DMV and for more detail by running SQL Profiler. Q) What is blocking and how would you check for it? A) Blocking is a result of locking where one process holds resources that the other needs, it can be identified by using the sp_who stored proc, and drilling down with sp_lock, querying the dm_tran_locks DMV and for even more detail by running SQL Profiler. Q) What are traceflags and when would you use them? A) Trace flags are used to set specific server characteristics. For example it can be used to print out extra information about deadlocks with traceflag 1204 and 1222. They are either set at startup with the –T option or can be explicitly started with dbcc traceon(<traceflag number>). Q) What are dynamic management views? A) DMV’s were introduced in SQL Server 2005 and provide a simple way for accessing the state of a server and gathering diagnostic information without too much overhead on the SQL Server. Q) What are the benefits of SQL Server 64 bit architecture? A) The main benefit is that you can use much more memory than you could on 32bit and also you can take advantage of 64 bit Windows server. Q) What is the dedicated admin port and when would you use it? A) The Dedicated Admin Connection listens on a special port and allows an administrator to connect to an otherwise unresponsive server due to lack of user connections for example. It is accessed by running sqlcmd –A or by prefixing admin: to the instance name in SSMS. Q) What is the Distributed Transaction Co-ordinator (DTC) used for. A) The DTC manages transactions across a network of Windows based systems. MS DTC allows: – Distributed transaction support for Microsoft SQL Server. – Updated data residing on two or more Microsoft SQL Server systems.