You want to accelerate query response time and minimize administrative overhead. How should you reconfigure the database?
A．Create a SQL Server Agent job to execute the UPDATE STATISTICS statement, and schedule the job to run weekly.
B．Select the Auto Update Statistics check box.
C．Run the database maintenance plan wizard, and accept the default settings.
D．Rebuild the primary key as a nonclustered primary key.
E．Clear the Auto Create Statistics check box.
参考答案
正确答案：B
解析：Explanation:TheAutoUpdateStatisticsoptionisselectedbydefaultbutintheExhibitithasbeendisabled.Apparentlythestatisticsareout-of-dateandthequeriesarerunningslowly.TheAutoUpdateStatisticsoptionshouldbeenabledtoincreaseperformanceandminimizeadministrativeoverhead.Note:SQLServerkeepsstatisticsaboutthedistributionofthekeyvaluesineachindexandusesthesestatisticstodeterminewhichindexwouldbethebestindextouseforaparticularqueryprocess.Asthedatainacolumnchanges,indexandcolumnstatisticscanbecomeout-of-date.Thiscanhinderthequeryoptimizer’sabilitytomakeoptimaldecisionsonhowtoprocessaquery.Asaresultqueryperformancewilldeteriorate.Whenstatisticsbecomeout-of-datetheycanbeupdatedautomaticallyiftheAutoUpdateStatisticscheckboxonthetablepropertydialogboxischecked.IncorrectAnswers:A:Itwouldrequiresomeadministrativeefforttosetupjobthatupdatethestatistics.JustselectingtheAutoUpdateStatisticscheckisabettersolution.C:TheDatabaseMaintenancePlanWizardcannotbeusedtorecreatethestatistics,whicharethemainproblematthemoment.Note:TheDatabaseMaintenancePlanWizardcanbeusedtosetupthecoremaintenancetasksnecessarytoensurethatadatabaseperformswell,isregularlybackedup,andischeckedforinconsistencies.TheDatabaseMaintenancePlanWizardcreatesaSQLServer2000jobthatperformsthesemaintenancetasksautomaticallyatscheduledintervalsandcanbeusedtoreorganizethedataonthedataandindexpagesbyrebuildingindexeswithanewfillfactor;compressdatafilesbyremovingemptydatabasepages;updateindexstatisticstoensurethequeryoptimizerhasup-todateinformationaboutthedistributionofdatavaluesinthetables;perform.internalconsistencychecksofthedataanddatapageswithinthedatabasetoensurethatasystemorsoftwareproblemhasnotdamageddata;backupthedatabaseandtransactionlogfiles;andtosetuplogshipping.D:Lookingattheexhibitweseethatthisisastatisticsproblem,notaindexproblem.Note:Anonclusteredindexissimilartoanindexinatextbook.Withthistypeofindex,thedataisstoredinoneplaceandtheindexinanother.Theseindexes,however,pointtothestoragelocationofthedata.Nonclusteredindexesshouldbeusedoncolumnsthatcontainalargenumberofdistinctvalues;forqueriesthatdonotreturnlargeresultsets;andforcolumnsthatarefrequentlyinvolvedinsearchconditionsofaquerythatreturnexactmatches.Aclusteredindex,ontheotherhand,determinesthephysicalorderofdatainatable.ThePRIMARYKEYindexautomaticallycreatesaclusteredindexasitisusedtoidentifyarowunlessanonclusteredindexisspecified.E:WhensettheAutoCreateStatisticscheckboxischecked,statisticsareautomaticallycreatedoncolumnsthatareusedinapredicate.AddingstatisticsimprovesqueryperformancebecausetheSQLServerqueryoptimizercanbetterdeterminehowtoevaluateaquery.Ifthestatisticsarenotused,SQLServerautomaticallydeletesthem.Whenthischeckboxiscleared,statisticsarenotautomaticallycreated.