微软MCITPDatabase Administrator认证考试题库1.docx

微软MCITPDatabase Administrator认证考试题库1.docx

《微软MCITPDatabase Administrator认证考试题库1.docx》由会员分享，可在线阅读，更多相关《微软MCITPDatabase Administrator认证考试题库1.docx（64页珍藏版）》请在冰豆网上搜索。

微软MCITPDatabaseAdministrator认证考试题库1

70-450

QUESTION1

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternationalcorporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingandsustainingthedatabaselifecycle.

Inthecompany,yourjobistoimplementsolutionsonsecurity,troubleshooting,deploymentandoptimization.ASQLServer2008infrastructureismanagedbyyou.

Adatabaseisincludedbytheinstance,andatablenamedEmployeeDataiscontainedbythedatabase.

ThereisacolumnnamedProfileintheEmployeeDatatable.MicrosoftOfficeWord2003documentsarestoredinthecolumn.

ThespaceutilizedbytheProfilecolumnshouldbecuttotheleast.

Inaddition,areasonableperformanceshouldbemaintainedtoretrievethecontentofthecolumn.

Whichactionwillyouperform?

A.

ROWcompressionshouldbeutilizedfortheEmployeeDatatable.

B.

TheProfilecolumnshouldbestoredbyutilizingfilestreamstorage,andtheNTFSfilesystemcompressionshouldbeutilized.

C.

PAGELEVELcompressionshouldbeutilizedfortheEmployeeDatatable.

D.

TheProfilecolumnshouldbeimplementedasaCLRuser-definedtype.

Answer:

B

Explanation/Reference:

Muchofthedatathatiscreatedeverydayisunstructureddata,suchastextdocuments,images,andvideos.Thisunstructureddataisoftenstoredoutsidethedatabase,separatefromitsstructureddata.Thisseparationcancausedatamanagementcomplexities.Or,ifthedataisassociatedwithstructuredstorage,thefilestreamingcapabilitiesandperformancecanbelimited.

FILESTREAMintegratestheSQLServerDatabaseEnginewithanNTFSfilesystembystoringvarbinary（max）binarylargeobject（BLOB）dataasfilesonthefilesystem.Transact-SQLstatementscaninsert,update,query,search,andbackupFILESTREAMdata.Win32filesysteminterfacesprovidestreamingaccesstothedata.

FILESTREAMusestheNTsystemcacheforcachingfiledata.ThishelpsreduceanyeffectthatFILESTREAMdatamighthaveonDatabaseEngineperformance.TheSQLServerbufferpoolisnotused;therefore,thismemoryisavailableforqueryprocessing.

Note:

FILESTREAMdataisnotencryptedevenwhentransparentdataencryptionisenabled.

QUESTION2

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternationalcorporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingandsustainingthedatabaselifecycle.

Inthecompany,yourjobistoimplementsolutionsonsecurity,troubleshooting,deploymentandoptimization.

ASQLServer2008instanceshouldbemanaged.

Itisreportedbythecustomersthattheserverperformancedegradedduetoanewlyimplementedprocess.

DynamicManagementViewsisutilizedtoconfirmthatnolongrunningqueriesexist.Theoperatingsystemperformancedatashouldbecorrelatedwiththeactualqueryexecutiontrace,andtheleastadministrativeeffortshouldbeutilized.

Whichactionwillyouperformtofinishthetask?

A.

Tofinishthetask,DataCollectorshouldbeutilized.

B.

Tofinishthetask,theSQLdiag.exeutilityshouldbeutilized.

C.

Tofinishthetask,SQLServerProfilerandthetracerpt.exeutilityshouldbeutilized.

D.

Tofinishthetask,SQLServerProfilerandSystemMonitorshouldbeutilized.

Answer:

D

Explanation/Reference:

SQLServerprofilerdisplaysdataaboutalargenumberofSQLServerevents.WhereasWindowsSystemMonitorgraphicallydisplaysinformationabouttheserverinternals.YoucanmergethetwosetsofinformationandwalkthroughascenarioviewingbothperspectivesusingSQLServerProfiler.

Tosetupthedual-perspectiveexperience,youneedtosimultaneouslycaptureserverperformanceusingbothPerformanceMonitor’sCounterLogsandSQLServerProfiler.Thestepstodothisarelistedbelow:

1.ConfigureSystemMonitorwiththeexactcountersyouwanttoviewlater.Besuretogetthescaleandeverythingjustright.SetuptheCounterLogtotheexactsameconfiguration.

2.ConfigureProfilerwiththerightsetoftraceevents.TheymustincludethestartandendtimedatacolumnssothatProfilercanintegratethetwologslater.Savetheprofilerandcloseprofiler.

3.ManuallystarttheCounterLog.OpenSQLProfilertracecodetostarttheserver-sidetrace.

4.Whenthetestiscomplete,stopboththecounterLogandtheserversidetrace.YouneedtostoptheSQLProfilerwhichisanegativepointinthis.

5.Openprofilerandopenthesavedtracefile.

6.UsetheFile->ImportPerformanceDatamenucommandtoimporttheCounterLog.Youhavetheoptionofselectingonlytheimportantcountersfromtheperformancemonitor.Therewillbeperformanceissuesifyouselecttoomanycounters.

Source:

http:

//www.sql-server-

QUESTION3

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternationalcorporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingandsustainingthedatabaselifecycle.

Inthecompany,yourjobistoimplementsolutionsonsecurity,troubleshooting,deploymentandoptimization.ASQLServer2008infrastructureismanagedbyyou.

Thereisadatabaseintheinstance,andtheday-to-daybusinessofyourcompanyrequiresthedatabase.Whenreportsareexecuted,slowresponsetimewillbeexperiencedbyUsers.

Aperformancemonitoringstrategywillbeimplementedbyyousoastohavethreeaspectsofdatacapturedandstored:

Blockinganddeadlockinformation,

ExecutedTransact-SQLstatements

QueryactivityandCountersfordisk,CPU,andmemory.

Youarerequiredtoutilizetheleastamountofadministrativeefforttoimplementthemonitoringprocess.

Whichactionwillyouperformtofinishthetask?

A.

Tofinishthetask,theclient-sideprofilertraceshouldbeutilized.

B.

Tofinishthetask,thedynamicmanagementviewsshouldbeutilized.

C.

Tofinishthetask,thedatacollectorshouldbeutilized.

D.

Tofinishthetask,theSystemMonitorcounterlogtraceshouldbeutilized.

Answer:

C

Explanation/Reference:

SQLServer2008providesadatacollectorthatyoucanusetoobtainandsavedatathatisgatheredfromseveralsources.Thedatacollectorenablesyoutousedatacollectioncontainers,whichenableyoutodeterminethescopeandfrequencyofdatacollectiononaSQLServerserversystem.

Thedatacollectorprovidespredefinedcollectortypesthatyoucanusefordatacollection.Thecollectortypesprovidetheactualmechanismforcollectingdataanduploadingittothemanagementdatawarehouse.Forthisreleaseofthedatacollector,thefollowingcollectortypeisprovided.

InThisSection

Topic

Description

T-SQLQueryCollectorType

DescribesthestructureandbehavioroftheT-SQLQuerycollectortype.

SQLTraceCollectorType

DescribesthestructureandbehavioroftheSQLTracecollectortype.

PerformanceCountersCollectorType

DescribesthestructureandbehaviorofthePerformanceCounterscollectortype.

QueryActivityCollectorType

DescribesthestructureandbehavioroftheQueryActivitycollectortype.

QUESTION4

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternationalcorporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingandsustainingthedatabaselifecycle.

Inthecompany,yourjobistoimplementsolutionsonsecurity,troubleshooting,deploymentandoptimization.ASQLServer2008instanceshouldbemanagedbyyou.

Thecomputeronwhichtheinstancerunhasthefollowingthreefeatures:

a64-GBRAM,fourquad-coreprocessors,andseveralindependentphysicalraidvolumes

Atransactionaldatabasewillbeimplementedontheinstance.Inaddition,thedatabaseshouldhaveahighvolumeofINSERT,UPDATE,andDELETEactivities.

Creationofnewtablesiscontainedbytheactivities.

Youneedtomaximizediskbandwidthanddecreasethecontentioninthestorageallocationstructuressoastohavetheperformanceofthedatabaseoptimized.

Whichactionwillyouperformtofinishthetask?

A.

Tofinishthetask,databaseandlogfilesshouldbeplacedonthesamevolume.

B.

Tofinishthetask,theaffinitymaskoptionshouldbeconfiguredproperly.

C.

Tofinishthetask,multipledatafilesshouldbecreateforthedatabase.

D.

Tofinishthetask,theaffinityI/Omaskoptionshouldbeconfiguredproperly.

Answer:

C

Explanation/Reference:

Ifyourdatabaseisverylargeandverybusy,multiplefilescanbeusedtoincreaseperformance.Hereisoneexampleofhowyoumightusemultiplefiles.Let'ssayyouhaveasingletablewith10millionrowsthatisheavilyqueried.Ifthetableisinasinglefile,suchasasingledatabasefile,thenSQLServerwouldonlyuseonethreadtoperformareadoftherowsinthetable.Butifthetableweredividedintothreephysicalfiles,thenSQLServerwouldusethreethreads（oneperphysicalfile）toreadthetable,whichpotentiallycouldbefaster.Inaddition,ifeachfilewereonitsownseparatephysicaldiskordiskarray,theperformancegainwouldevenbegreater.

Essentially,themorefilesthatalargetableisdividedinto,thegreaterthepotentialperformance.Ofcoursethereisapointwheretheadditionalthreadsaren'tofmuchusewhenyoumaxouttheserver'sI/O.ButupuntilyoudomaxouttheI/O,additionalthreads（andfiles）shouldincreaseperformance

SOURCE:

http:

//www.sql-server-

QUESTION5

YouareaprofessionallevelSQLSever2008Database