Trouble Spots
As with any exam, it will vary from person to person as to what is deemed to be difficult. Be sure you are familiar with each of the topics in the exam objectives listed below.
- For local installations, you must run Setup as an administrator. If you install SQL Server from a remote share, you must use a domain account that has read and execute permissions on the remote share.
- Be sure to run SQL Server services by using the lowest possible permissions. You can do this by associating SQL Server services with low privileged Windows local user accounts, or domain user accounts.
- Performance Objects and Counters used to track SQL performance include the following:
- Memory—Available MBytes
- Paging File—% Usage
- Physical Disk—Avg. Disk sec/Read
- Physical Disk—Avg. Disk sec/Write
- Physical Disk—Disk Reads/sec
- Physical Disk—Disk Writes/sec
- Processor—% Processor Time
- SQLServer: Buffer Manager—Buffer cache hit ratio
- SQLServer: Buffer Manager—Page life expectancy
- SQLServer: General Statistics—User Connections
- SQLServer: Memory Manager—Memory Grants Pending
- SQLServer: SQL Statistics—Batch Requests/sec
- SQLServer: SQL Statistics—Compilations/sec
- SQLServer: SQL Statistics—Recompilations/sec
- System—Processor Queue Length
- Some common reasons for query time-outs are:
- The application starts using a query not optimal for the index
- Hardware changes/Configuration changes
- Increased load
- Adding role members in the development environment can overwrite changes made in production to the list of role members when you deploy. The easiest way to test dynamic security is to add role members. These role members should be removed before you deploy the model.