首页
外语
计算机
考研
公务员
职业资格
财经
工程
司法
医学
专升本
自考
实用职业技能
登录
计算机
You are the administrator of a SQL Server 2000 database. Using the default options configures the database. You drop indexes to
You are the administrator of a SQL Server 2000 database. Using the default options configures the database. You drop indexes to
admin
2009-05-19
18
问题
You are the administrator of a SQL Server 2000 database. Using the default options configures the database. You drop indexes to facilitate a large import of data, and rebuild them after the import is complete.
Users report that response times from a stored procedure have become slower. In the stored procedure, you examine the following code.
CREATE PROCEDURE GetCustomer
( @CustomerID Int )
AS
SELECT FirstName, LastName, Address1, Address2, City,
State, Zip, MAX(PurchaseDate) AS LastPurchase
FROM Customers C
JOIN Addresses A ON A.AddressID = C.AddressID
JOIN Purchases P ON P.CustomerID = C.CustomerID
WHERE C.CustomerID = @CustomerID
GROUP BY FirstName, LastName, Address1, Address2, City, State, Zip
You want to improve the performance of the procedure by using the least amount of administrative effort. What should you do?
选项
A、Recompile the stored procedure.
B、Update the database statistics.
C、Check the consistency of the database and repair errors.
D、Create a view and modify the stored procedure to use the view.
答案
A
解析
Explanation: By recompiling the stored procedure SQL Server will create a new execution plan for the statements in the procedure. SQL Server will detect that statistics are not up to date and it will automatically recreate the statistics during this process. The execution plan will be optimized for the current distribution of data in the tables.
Note: Stored procedures can be used to improve database query performance. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan that is cached on the server. When the database is changed by the addition of new indexes or when data in indexed columns are changed, the original query plans and stored procedures used to access the tables should be reoptimized by recompiling them.
This optimization happens automatically the first time a stored procedure is run after SQL Server 2000 is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not automatically occur until the next time the stored procedure is run after SQL Server is restarted. The stored procedure can be manually recompiled by using the sp_recompile system stored procedure. This forces the stored procedure to be recompiled the next time it is run.
Incorrect Answers:
B: SQL Server detects whether statistics are not up-to-date, and by default it automatically updates them during query optimization. By recompiling the query the statistics will automatically be updated.
Note: SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Query optimization depends on the accuracy of the distribution steps. If there is significant change in the key values in the index, or if a large amount of data in an indexed column has been added, changed, or removed, or the table has been truncated and then repopulated, UPDATE STATISTICS should be rerun.
C: Consistency of a database is maintained through the use of constraints and triggers. These
mechanisms are used to ensure that the database remains consistent by controlling the type of data that can be inserted into a column, through referential integrity checks, and through cascading referential constraints.
D: A view is a virtual table that can be used to restrict users access to the base table, or to join various base tables so that they appear as a single table. However, creating a view that the stored procedure must access would require the creation of indexes on the view and will have to be updated when the base tables are updates. This would require greater administrative effort.
转载请注明原文地址:https://kaotiyun.com/show/RuhZ777K
本试题收录于:
微软70228题库微软认证分类
0
微软70228
微软认证
相关试题推荐
[A]Thefirststepinpreparingamarketingplanisthatofproducingtheinformationnecessaryfordecision-making.Usually,a
Imaginativeworkssuchasnovels,plays,films,fairytales,andlegendspresentamoreaccurateandmeaningfulpictureofhuman
[A]Enduringproblemssuchaswar,poverty,andviolenceultimatelyspringfromhumannature,whichnotechnologicalinnovation
[A]"Ijustdon’tknowhowtomotivatethemtodoabetterjob.We’reinabudgetcrunchandIhaveabsolutelynofinancialrewa
EricHansenwritesabouttravelasaparticipatingenthusiastratherthanamereobserver.【T1】Itgivesthesenineessays,based
Herearesomemanagementtoolsthatcanbeusedtohelpyouleadapurposefullife.1.UseYourResourcesWisely.Yourdecisi
TheU.S.PostalService(USPS)continuestobleedredink.Itreportedanetlossof$5.6billionforfiscal2016,the10thst
InParagraphs1and2,thetextshowsPTK’s______.Skepticalobserversbelievethatancestrytestingfailsto______.
Emilyislisteningtonetworktrafficandcapturingpasswordsastheyaresenttotheauthenticationserver.Sheplanstouseth
Thefollowingscenariowillbeusedforquestions29and30.Johnisanetworkadministratorandhasbeentoldbyoneofhisnet
随机试题
下列各项中,2014年1—6月同比增长率最大的是:
现代的战略采购观点认为采购成功的关键是()
中孕期超声可发现的胎儿畸形有:①严重开放性脊柱裂;②脉络丛囊肿;③十二指肠闭锁;④肺囊腺瘤
男性,10岁,外伤后左膝附近肿胀,剧痛,体温达39~40℃,经大剂量抗生素应用3天,效果不明显。体检:急性病容,左大腿下段及膝关节肿胀明显,发红,皮温高,压痛明显,左膝活动受限,X线平片骨质未见破坏,血白细胞19×109/L,中性89%,最佳处理
股份有限公司经理的职权包括()。
公安机关的任务是由公安机关的( )决定的。
符号是人们共同约定来指称一定对象的标志物,它可以包括以任何形式通过感觉来显示意义的全部现象,在这些现象中某种可以感觉的东西就是对象及其意义的体现者。根据上述定义,下列选项属于符号的是:
其实,生活中的__________、折腾和挫败,都是不可避免的,正因为这些困境来势汹汹,安然度过以后,便有了一种__________和感激。真正可贵的幸福,不是从快乐之中来,而是从忧愁之中来。依次填入画横线部分最恰当的一项是()。
阅读以下说明,回答问题1~5,将解答填入对应的解答栏内。在图4-1所示的网络中,运行的路由协议是OSPF,有0、1和2三个区域,其中Router1的S0端口、Router2的S0端口属于区域0,Router1的E0端口、Router3的E0端口属于区
Whatdoesthespeakermean?
最新回复
(
0
)