1. How many devices are created during installation? Which system databases are stored on these devices?
Answer: Three devices are created during installation: the sysprocsdev device, which stores the sybsystemprocs database, the systemdbdev device which stores the sybsystemdb, and the master device, which stores the master, model, and tempdb databases.
2. How can you verify that a server is running?
Answer: You can verify a server is running by using the showserver command in Unix/Linux or in a Windows Command Prompt window by using the command ‘net start’. You can also go to Windows Services in the control panel.
3. What is the purpose of the interfaces/sql.ini file?
Answer: The interfaces/sql.ini file is an “address book” that client and servers use to determine which address they should use when communicating with each other.
4. Given a server named SYBASE, how is the SYBASE.bak file different from a file named SYBASE.001?
Answer: The SYBASE.bak file is a backup made at startup, and it contains a copy of the configuration file at startup. The file named SYBASE.001 is a copy of an earlier version of the configuration file made when the configuration file was modified via sp_configure. It could be the same as the backup file, or it could be a much earlier copy of the configuration file.
5. How should you configure a server that has two different processing requirements, such as one that is used for OLTP processing during the day and DSS processing at night?
Answer: You should probably create two different configuration files, one for each period of time, and then restart the server with the given configuration file at the start of each period.
6. What is the housekeeper?
Answer: The housekeeper is a task that becomes active when no other tasks are active. It writes dirty pages to disk, reclaims lost space, flushes statistics to systabstats, and checks license usage.
7. Name three configuration parameters that use significant amounts of memory.
Answer: number of user connections, number of open databases, number of open indexes, number of open objects, number of locks, number of devices.
8. What two system databases are involved in user database creation?
Answer: The master and model databases are involved in database creation. The contents of the model database are copied to form the contents of the new database, and information is recorded in master.
9. Why is it a good practice to place the log on a separate device?
Answer: Lets you back up the transaction log separately, decreases the likelihood that both the data and the log will be damaged at the same time, helps in monitoring space usage and managing the space, allows disk mirroring of the log for maximum uptime and up-to-the-minute recovery.
10. What sorts of problems can occur if tempdb is too small?
Answer: tempdb is used both by users creating temporary tables and by server processes that need to sort data, group data, store intermediate results, and so on. If tempdb is too small, users and server processes will have insufficient resources to process their queries rapidly, and their queries will fail.
11. How can a threshold help you monitor space usage?
Answer: A threshold can help monitor space usage by executing a stored procedure with a warning message when there is a certain amount of space left on a segment.
12. What performance benefits can be gained by configuring multiple temporary databases?
Answer:
· Distributes the workload across multiple tempdbs, Reduces system table contention
· Reduces resource contention
· Load balancing can be done automatically
· Heavy users or applications can be bound to their own temporary database
13. How is a user-defined role different from a group assignment?
Answer: User-defined roles are system-wide. Multiple user-defined roles can be given to a single user. Once defined, roles can be dynamically turned off and on by the user, thereby supporting more flexibility than with groups. A user-defined role can also include other user-defined roles.
User-defined roles can take advantage of creating:
· Hierarchical access
· Access defined for a specific application.
14. What is the primary purpose of a checkpoint?
Answer: The primary purpose of a checkpoint is to reduce the amount of time needed for recovery by writing all dirty pages to disk.
15. What is a “dirty page”?
Answer: A dirty page is a page that has been modified in data cache, but the modifications have not yet been written to disk.
16. Why does the server need to execute the automatic recovery process?
Answer: The server needs to execute the automatic recovery process because it must ensure that all transactions in process during or shortly before the last shutdown are either rolled forward or rolled back as a unit.
17. Why are Adaptive Server transaction logs known as “write-ahead logs”?
Answer: Adaptive Server transaction logs are known as write-ahead logs because log pages are written to disk before the corresponding data and index pages.
18. During recovery, when does the server “roll forward” a transaction? When does it “roll back” a transaction?
Answer: The server rolls forward transactions that are committed in the log but are not fully reflected on data pages. The server rolls back a transaction that is incomplete or is rolled back in the log but is partially or fully reflected in the data pages.
19. There are four circumstances under which pages in cache are written to disk. Name them?
Answer: Pages in cache are written to disk:
1) when the pages must be aged out,
2) when a checkpoint occurs,
3) when the housekeeper writes pages to disk, or
4) when a commit tran is executed. Note that the first three circumstances write log and data pages to disk, but the fourth circumstance writes only log pages to disk.
20. What is the last-chance threshold?
Answer: The last-chance threshold is a point in a database’s log segment determined by the server. When the number of used pages crosses this point, the server executes sp_thresholdaction and suspends or aborts all transactions in progress.
21. What happens if the last-chance threshold is crossed and there is no sp_thresholdaction procedure?
Answer: The server sends an error to the error log if a database has no sp_thresholdaction procedure and the last-chance threshold is crossed.
22. How can a long-running transaction limit the effectiveness of log truncation?
Answer: Truncation can remove only inactive pages from the log. If a transaction is long-running, it extends the active portion, thereby limiting the number of inactive pages that can be removed from the log.
23. What is the name of the system table that stores system error messages?
Answer: master..sysmessages
24. How can you identify that a process is a system process?
Answer: A system process can be identified by using the sp_who output. System processes have a NULL login name and no hostname.
25. What kinds of processes cannot be killed? What kinds of processes should be killed with extreme caution?
Answer: You cannot kill a system process, and you also cannot kill your own process. Processes that are “infected” or “background” should be killed with extreme caution, if at all.
No comments:
Post a Comment