QUESTION 41
You are a professional level SQL Sever 2008 Database Administrator.
A thirdparty database application will be deployed to the instance. Stored procedures are utilized by the application, and the procedures are developed by utilizing SQL CLR integration. You should set the application to have the EXTERNAL_ACCESS code access security setting enabled. Since you are the technical support of the company, you should deploy the application to the instance with no loss of functionality.
Which action will you perform first?
A. Readwrite non-static fields in the code should be replaced with static fields.
B. The peverify.exe PEVerify tool should be utilized to confirm whether the type-safety requirements are
satisfied by the code.
C. Read-only static fields in the code should be replaced with readwrite static fields.
D. The regasm.exe assembly registration tool should be utilized to have the assembly registered on the
server before deployment.
Answer: B
QUESTION 42
You are designing the storage scheme for a new SQL Server 2008 database application The application will primarily be used to query data.
The database will be initially populated with 4 terabytes of data stored in 20 tables. The data is distributed evenly throughout all 20 tables. Every night, between 100 GB and 300 GB of new data will be loaded into 4 database tables. The remaining 16 tables will remain static. The average CPU utilization of the server where the application will reside is between 30 and 50 percent.
You need to ensure that the amount of disk space used is reduced.
You need to achieve this goal by ensuring minimum impact of performance on the server.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use page-level data compression on the 4 tables that are loaded every night.
B. Use row-level data compression on the 4 tables that are loaded every night.
C. Use page-level data compression on the 16 tables that remain static.
D. Use row-level data compression on the 16 tables that remain static.
Answer: B
QUESTION 43
You are the database administrator for a SQL Server that hosts an application defined as follows:
The database consists of 4 data files and one log file. The physical server has 16 CPUs and 64 GB of memory. Hardware non-uniform memory access (NUMA) is not available on this server platform.
Users of the application report that it is responding slowly. You use SQL Server Profiler to find out that the I/O performance issue is related to the lazywriter process.
You need to improve the performance of the application by reducing the I/O performance issue.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Add additional RAM to the server.
B. Add additional data files to the database.
C. Configure four software non-uniform memory access (soft NUMA) nodes on the server.
D. Configure a CPU affinity mask on the server to limit the instance of SQL Server to four CPUs
Answer: C
QUESTION 44
You administer a SQL Server 2008 infrastructure.
An instance runs on a computer that has eight quad-core processors and 128-GB RAM. Four different applications use the instance.
The instance experiences a low number of CXPACKET waits. The instance also experiences a large number of lazy writer waits. You need to optimize the performance of the instance. What should you do?
A. Configure the Resource Governor.
B. Configure the Windows System Resource Manager.
C. Configure software non-uniform memory access (soft-NUMA).
D. Configure an increase in the maximum degree of parallelism option.
Answer: C
QUESTION 45
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’, 1
B. Execute the following command on the server: exec_sp_configure ‘maximum degree ofparallelism’, 8
C. Use SQL Profiler to identify queries that experience CXPACKET waits. Add (OPTION MAXDOP 1) to
each query
D. Use SQL Profiler to identify queries that experience CXPACKET waits. Add (OPTION MAXDOP 8) to
each query
Answer: C
QUESTION 46
You administer a SQL Server 2008 instance.
You plan to deploy a new database to the instance. The database will be subject to frequent inserts and updates. The database will have multiple schemas. One of the schemas will contain a large amount of read-only reference data.
You need to design the physical database structure for optimal backup performance.
What should you do?
A. Create the database by using a single data file and a single log file.
B. Create the database by using a single data file and multiple log files.
C. Create the database by using a single log file and multiple filegroups.
D. Create the database by using a single log file and a filegroup that has multiple data files.
Answer: C
QUESTION 47
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. Disable partition-level lock escalation.
B. Move each partitioned table to its own filegroup.
C. Add additional database files to each partitioned table.
D. Move the clustered indexes for the partitioned tables to a separate filegroup.
Answer: A
QUESTION 48
You administer a SQL Server 2008 infrastructure.
You plan to upgrade a Microsoft SQL Server 2005 instance to SQL Server 2008 by using an in-place upgrade. All user databases use the simple recovery model and have the PAGE_VERIFY option turned off.
You need to ensure that the database engine can identify I/O path errors and memory corruptions for the data in tables.
Which two actions should you perform before the upgrade? (Each correct answer presents part of the solution. Choose two.)
A. Rebuild the nonclustered indexes.
B. Rebuild the clustered indexes and heaps.
C. Set the database recovery model to FULL.
D. Set the PAGE_VERIFY database option to CHECKSUM.
E. Set the PAGE_VERIFY database option to TORN_PAGE_DETECTION.
F. Execute the DBCC CHECKDB command by using the DATA_PURITY option.
Answer: CD
QUESTION 49
Your SQL Server 2008 server has only the SQL Server service running. All other SQL Services are disabled.
You have a user named DBReportsUser. You need to restrict DBReportsUser to login only between 17:00 hours and 22:00 hours. You want to achieve this goal by using the minimum amount of administrative effort and the least impact on the Server security.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use SQL Profiler.
B. Use a logon trigger.
C. Enable login auditing.
D. Create a SQL Server Agent job.
Answer: B
QUESTION 50
You administer a SQL Server 2008 instance.
The instance hosts a database that is used by a Web-based application. The application processes 15,000 transactions every minute. A table in the database contains a column that is used only by the application. This column stores sensitive data.
You need to store the sensitive data in the most secure manner possible. You also need to ensure that you minimize the usage of memory space and processor time.
Which encryption type should you use?
A. Symmetric key encryption
B. Asymmetric key encryption
C. Certificate-based encryption
D. Transparent data encryption
Answer: A
If you want to pass Microsoft 70-450 successfully, donot missing to read latest lead2pass Microsoft 70-450 dumps.
If you can master all lead2pass questions you will able to pass 100% guaranteed.