Free Microsoft 070-450 Exam Dumps Questions & Answers

You administer a SQL Server 2008 infrastructure.
Your company requires capacity planning information.
You need to design a long-term infrastructure monitoring strategy.
Which two tasks should you include in your design? (Each correct answer presents part of the solution. Choose two.)

• A.  Review system monitor counters on a regular basis.
• B.  Create a maintenance plan that rebuilds indexes every week.
• C.  Clear the system log and the application log every hour.
• D.  Backup all databases every day.
• E.  Baseline the system before you deploy production databases.

You administer a SQL Server 2008 instance.
The instance is using a 32-bit version on a Windows Server 2008 64-bit server. The awe enabled option is
enabled.
The instance will experience a predictable increase in query activity.
You plan to ascertain the appropriate time when the migration of the databases to a 64-bit SQL Server 2008
server
on the same hardware is beneficial.
You need to identify a data collector type that provides the appropriate information.
Which collector should you use?

• A.  Performance Counters collector
• B.  Query Activity collector
• C.  SQL Trace collector
• D.  T-SQL Query collector

You are designing a new SQL Server instance that will support a Web application.
The Web application is hosted on a Web farm that contains 32 nodes.
The server has 128 GB of memory and 16 processor cores. The application contains two databases and supports both online analytical processing (OLAP) and online transaction processing (OLTP) workloads.
When testing the application, you discover that some queries run extremely slow and some queries run very fast.
You need to ensure that the server processes database queries as fast as possible . What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A.  Execute the following command on the server: exec_sp_configure 'maximum degree of parallelism', 8
• B.  Execute the following command on the server: exec_sp_configure 'maximum degree of parallelism', 1
• C.  Use SQL Profiler to identify queries that experience CXPACKET waits. Add (OPTION MAXDOP 8) to each query
• D.  Use SQL Profiler to identify queries that experience CXPACKET waits. Add (OPTION MAXDOP 1) to each query

You are a professional level SQL Sever 2008 Database Administrator.
The computer on which the instance run has the following three features:
64-GB RAM, four quad-core processors, and several independent physical raid volumes A transactional database will be implemented on the instance.
In addition, the database should have a high volume of INSERT, UPDATE, and DELETE activities, creation of new tables is contained by the activities. You need to maximize disk bandwidth and decrease the contention in the storage allocation structures so as to have the performance of the database optimized.
Which action will you perform to finish the task?

• A.  To finish the task, the affinity mask option should be configured properly.
• B.  To finish the task, multiple data files should be create for the database.
• C.  To finish the task, the affinity I/O mask option should be configured properly.
• D.  To finish the task, database and log files should be placed on the same volume.

You deploy a new database application to a SQL Server 2008 instance on a server that has 4 processor
cores and 64 GB of RAM. The application uses partitioned tables.
During testing, deadlocks occasionally occur on queries that are attempting to read data.
You find out that the deadlocks are related to the table partitions.
You need to ensure that the application executes without deadlocks for the read queries.
You also need to ensure that dirty reads do not occur.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A.  Add additional database files to each partitioned table.
• B.  Move each partitioned table to its own filegroup.
• C.  Move the clustered indexes for the partitioned tables to a separate filegroup.
• D.  Disable partition-level lock escalation.