首页
外语
计算机
考研
公务员
职业资格
财经
工程
司法
医学
专升本
自考
实用职业技能
登录
计算机
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
74
问题
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
微软认证
相关试题推荐
Severaltypesoffinancialriskareencounteredininternationalmarketing;themajorproblemsincludecommercial,political,an
Ofallthegoodsandservicestradedinthemarketeconomy,pharmaceuticalsareperhapsthemostcontentious.Thoughproducedby
[A]Marketforglasscraftsisgrowing[B]Dependenceofcomputerdevelopmentonglass[C]Behindtheadaptabilityofglass[D]
School-agechildrenshouldparticipatein60minutesormoreofmoderatetovigorousphysicalactivitydaily,accordingtoanex
Imaginativeworkssuchasnovels,plays,films,fairytales,andlegendspresentamoreaccurateandmeaningfulpictureofhuman
Writeanessayof160-200wordsbasedonthefollowingdrawings.Inyouressay,youshould1)describethedrawingsbriefly,
Whileyouenjoythesunlight,itcastsyourshadowontheground.Therehasbeenadiscussionrecentlyontheissueinanewspap
Thehumanbraincontains10thousandmillioncellsandeachofthesemayhaveathousandconnections.Suchenormousnumbersused
Notlongago,chiefexecutiveofficers(CEOS)werelandedasheroes,leadersofthebraveneweconomy.BillGatesandJackWelchw
GivingmillionsofexistingPayPalcustomerstheabilitytobuyattheclickofabuttonhasenormouspotentialforincreasing
随机试题
关于业主方项目管理目标和任务的说法,正确的是()。
《海关法》规定,当事人逾期不履行海关的处罚决定,海关可以将其保证金没收或者将其扣留的货物、物品、运输工具变价折抵。海关采取此种措施属于下列哪一种性质的行为?()
勘察设计项目投标文件一般包括()。
(2010年)某晶体管放大电路的空载放大倍数Ak=-80、输入电阻ri=1kΩ和输出电阻r0=3kΩ,将信号源(us=10sinωtmV,Rs=1kΩ)和负载(RL=5kΩ)接于该放大电路之后(见图8-56),负载电压u0将为()V。
学校教育心理学的主要研究方法。
A.Iwanttocheckin.B.Youmayboardnow.C.I’mafraidyourbagisfourkilosoverweight,A:IsthistherightcounterforC
在进行数据库物理设计时,为提高查询效率,需要在基本表的一些列上建立索引。有下列情况:Ⅰ.查询语句的wHERE子句中引用率比较高的列Ⅱ.经常参与连接操作的列Ⅲ.经常在orderby子句中出现的列Ⅳ.经常使用LIKE操
—Thanksforinvitingmetodinnerlastnight,Cathy.Ihadareallygoodtime.Theseflowersareforyou.—Oh,Bill,______.I
Asitisadifficulttask,wehavetodiscussitbeforehand.
A、Theyneedtoturnrightahead.B、SheagreeswiththemanonwhereStateStreetis.C、Theyfoundnoleftturnatthelastinter
最新回复
(
0
)