Exam A
QUESTION 1
You are a professional level SQL Sever 2008 Database Administrator.
A database is included by the instance, and a table named EmployeeData is contained by the database.
There is a column named Profile in the EmployeeData table. Microsoft Office Word 2003 documents are
stored in the column.
The space utilized by the Profile column should be cut to the least. In addition, a reasonable performance
should be maintained to retrieve the content of the column.
Which action will you perform?
A. ROW compression should be utilized for the EmployeeData table.
B.
The Profile column should be stored by utilizing filestream storage, and the NTFS file system
compression should be utilized.
C. PAGE LEVEL compression should be utilized for the EmployeeData table.
D. The Profile column should be implemented as a CLR user-defined type.
Correct Answer: B
Section: (none)
Explanation
Explanation/Reference:
Much of the data that is created every day is unstructured data, such as text documents, images,
and videos. This unstructured data is often stored outside the database, separate from its
structured data. This separation can cause data management complexities. Or, if the data is
associated with structured storage, the file streaming capabilities and performance can be limited.
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by
storing varbinary(max) binary large object (BLOB) data as files on the file system.
Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data.
Win32 file system interfaces provide streaming access to the data.
FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that
FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is
not used; therefore, this memory is available for query processing.
Note:
FILESTREAM data is not encrypted even when transparent data encryption is enabled.
QUESTION 2
You are a professional level SQL Sever 2008 Database Administrator.
It is reported by the customers that the server performance degraded due to a newly implemented
process. Dynamic Management Views is utilized to confirm that no long running queries exist.
The operating system performance data should be correlated with the actual query execution trace,
and the least administrative effort should be utilized.
Which action will you perform to finish the task?
A. To finish the task, Data Collector should be utilized.
B. To finish the task, the SQLdiag.exe utility should be utilized.
C. To finish the task, SQL Server Profiler and the tracerpt.exe utility should be utilized.
D. To finish the task, SQL Server Profiler and System Monitor should be utilized.
Correct Answer: D
Section: (none)
Explanation
