(Continued from previous part...)

You are examining a SQL Server 2005 system as part of a consulting engagement. The client has complained the inserts and updates are slow to process on the system. You discover that there are over thirty indexes on some of the most heavily updated columns. How can you quickly determine if any of these indexes are really being used?

Answer: Query sys.dm_db_index_usage_stats

Explanation: SQL Server 2005 collects information on index usage since the last restart in sys.dm_db_index_usage_stats. You can query this after the system has run for a reasonable period of time to see if indexes are really being used.

Which commands are allowed when the "Ad Hoc Distributed Queries" server option is set to 0 in SQL Server 2005?

Answer: OPENDATASOURCE calls

Explanation: This command allows the use of OPENROWSET and OPENDATASOURCE commands.

When creating a SQL Server 2005 Service Broker message type, what does the EMPTY validation option mean?

Answer: The the message body field must be null.

Explanation: The validation determines how Service Broker will validate the message body for those messages created using this type. EMPTY signifies that the message body must be NULL.

You cannot remember what permissions that the db_accessadmin fixed database role has and Books Online is not installed on your laptop. You're working on the SQL Server 2000 security roles for a client and need to get the document finished and the ISP is down. How can you quickly determine the permissions for this role?

Answer: Run sp_dbfixedrolepermission

Explanation: While creating an account and trying all permissions will work, it's not a quick way to do it. The sp_dbfixedrolepermission stored procedure will give you a list of permissions.

When a user on a remote SQL Server wants to execute a stored procedure through a linked server on your local server, what security context is used?

Answer: The local login that is mapped from the remote procedure call.

Explanation: When a linked server is setup, remote logins must be mapped to local logins.

Which database holds the tables that Database Mail uses to store mail messages and other data in SQL Server 2005?

Answer: msdb

Explanation: The messaging objects, including tables, are stored in the msdb database.

You are wondering if your indexing scheme for the SQL Server 2005 database that you are managing is appropriate? You haven't been running Profiler, but you're wondering if you can determine what indexes might be more helpful for your users. What can you do?

Answer: Query sys.dm_db_missing_index_details.

Explanation: The database engine in SQL Server 2005 collects information on missing indexes based on attempts by the query optimizer. You can use this to determine if there might be better indexes you can create. It IS NOT the list of indexes you should create.

(Continued on next part...)