Which line of code should you modify?
###BeginCaseStudy###
Case Study: 5
Litware, Inc
Overview
General Overview
You are a database developer for a company named Litware, Inc. Litware has a main office in
Miami.
Litware has a job posting web application named WebApp1. WebApp1 uses a database
named DB1. DB1 is hosted on a server named Server1. The database design of DB1 is shown
in the exhibit. (Click the Exhibit button.)
WebApp1 allows a user to log on as a job poster or a job seeker. Candidates can search for
job openings based on keywords, apply to an opening, view their application, and load their
resume in Microsoft Word format. Companies can add a job opening, view the list of
candidates who applied to an opening, and mark an application as denied.
Users and Roles
DB1 has five database users named Company, CompanyWeb, Candidate, CandidateWeb, and
Administrator.
DB1 has three user-defined database roles. The roles are configured as shown in the
following table.
Keyword Search
The keyword searches for the job openings are performed by using the following stored
procedure named usp_GetOpenings:
Opening Update
Updates to the Openings table are performed by using the following stored procedure named
usp_UpdateOpening:
Problems and Reported Issues
Concurrency Problems
You discover that deadlocks frequently occur.
You identify that a stored procedure named usp_AcceptCandidate and a stored procedure
named usp_UpdateCandidate generate deadlocks. The following is the code for
usp_AcceptCandidate:
Salary Query Issues
Users report that when they perform a search for job openings without specifying a minimum
salary, only job openings that specify a minimum salary are displayed.
Log File Growth Issues
The current log file for DB1 grows constantly. The log file fails to shrink even when the daily
SQL Server Agent Shrink Database task runs.
Performance Issues
You discover that a stored procedure named usp_ExportOpenings takes a long time to run
and executes a table scan when it runs.
You also discover that the usp_GetOpenings stored procedure takes a long time to run and
that the non-clustered index on the Description column is not being used.
Page Split Issues
On DB1, many page splits per second spike every few minutes.
Requirements
Security and Application Requirements
Litware identifies the following security and application requirements:
• Only the Administrator, Company, and CompanyWeb database users must be able to
execute the usp_UpdateOpening stored procedure.
• Changes made to the database must not affect WebApp1.
Locking Requirements
Litware identifies the following locking requirements:
• The usp_GetOpenings stored procedure must not be blocked by the
usp_UpdateOpening stored procedure.
• If a row is locked in the Openings table, usp_GetOpenings must retrieve the latest
version of the row, even if the row was not committed yet.
Integration Requirements
Litware exports its job openings to an external company as XML data. The XML data uses
the following format:
A stored procedure named usp_ExportOpenings will be used to generate the XML data. The
following is the code for usp_ExportOpenings:
The stored procedure will be executed by a SQL Server Integration Services (SSIS) package
named Package1.
The XML data will be written to a secured folder named Folder1. Only a dedicated Active
Directory account named Account1 is assigned the permissions to read from or write to
Folder1.
Refactoring Requirements
Litware identifies the following refactoring requirements:
• New code must be written by reusing the following query:
• The results from the query must be able to be joined to other queries.
Upload Requirements
Litware requires users to upload their job experience in a Word file by using WebApp1.
WebApp1 will send the Word file to DB1 as a stream of bytes. DB1 will then convert the
Word file to text before the contents of the Word file is saved to the Candidates table.
A database developer creates an assembly named Conversions that contains the following:
• A class named Convert in the SqlConversions namespace
• A method named ConvertToText in the Convert class that converts Word files to text
The ConvertToText method accepts a stream of bytes and returns text. The method is used in
the following stored procedure:
Job Application Requirements
A candidate can only apply to each job opening once.
Data Recovery Requirements
All changes to the database are performed by using stored procedures. WebApp1 generates a
unique transaction ID for every stored procedure call that the application makes to the
database.
If a server fails, you must be able to restore data to a specified transaction.
###EndCaseStudy###
You need to implement a solution that meets the locking requirements.
Which line of code should you modify?
You need to configure a parameter for the database connection string
###BeginCaseStudy###
Case Study: 3
Data Architect
General Background
You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.
You have been tasked with designing a data warehouse that uses the company’s financial
database as the data source. From the data warehouse, you will develop a cube to simplify the
creation of accurate financial reports and related data analysis.
Background
You will utilize the following three servers:
• ServerA runs SQL Server Database Engine. ServerA is a production
server and also hosts the financial database.
• ServerB runs SQL Server Database Engine, SQL Server Analysis
Services (SSAS) in multidimensional mode, SQL Server Integration Services
(SSIS), and SQL Server Reporting Services (SSRS).
• ServerC runs SSAS in multidimensional mode.
• The financial database is used by a third-party application and the table
structures cannot be modified.
The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)
The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is
partitioned by month. It contains only the last five years of financial data. The CouponUsed,
OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is
less than 10 MB and has only one partition.
The SecurityFilter table specifies the sites to which each user has access.
Business Requirements
The extract, transform, load (ETL) process that updates the data warehouse must run daily
between 8:00 P.M. and 5:00 A.M. so that it doesn’t impact the performance of ServerA
during business hours. The cube data must be available by 8:00 A.M.
The cube must meet the following business requirements:
• Ensure that reports display the most current information available.
• Allow fast access to support ad-hoc reports and data analysis.
Business Analysts will access the data warehouse tables directly, and will access the cube by
using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint
Services. These tools will access only the cube and not the data warehouse.
Technical Requirements
SSIS solutions must be deployed by using the project deployment model.
You must develop the data warehouse and store the cube on ServerB. When the number of
concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to
ServerC and meet following requirements:
• Maintain copies of the cube on ServerB and ServerC.
• Ensure that the cube is always available on both servers.
• Minimize query response time.
The cube must meet the following technical requirements:
• The cube must be processed by using an SSIS package.
• The cube must contain the prior day’s data up to 8:00 P.M. but does not
need to contain same-day data.
• The cube must include aggregation designs when it is initially
deployed.
• A product dimension must be added to the cube. It will contain a
hierarchy comprised of product name and product color.
Because of the large size of the SalesTransactions table, the cube must store only
aggregations—the data warehouse must store the detailed data. Both the data warehouse and
the cube must minimize disk space usage.
As the cube size increases, you must plan to scale out to additional servers to minimize
processing time.
The data warehouse must use a star schema design. The table design must be as denormalized
as possible. The history of changes to the Customer table must be tracked in the data
warehouse. The cube must use the data warehouse as its only data source.
Security settings on the data warehouse and the cube must ensure that queries against the
SalesTransactions table return only records from the sites to which the current user has
access.
The ETL process must consist of multiple SSIS packages developed in a single project by
using the least amount of effort. The SSIS packages must use a database connection string
that is set at execution time to connect to the financial database. All data in the data
warehouse must be loaded by the SSIS packages.
You must create a Package Activity report that meets the following requirements:
• Track SSIS package execution data (including package name, status,
start time, end time, duration, and rows processed).
• Use the least amount of development effort.
###EndCaseStudy###
You need to configure a parameter for the database connection string.
What should you do?
Which Hidden property expression should you use for the report header?
You are developing a SQL Server Reporting Services (SSRS) sales summary report.
The report header consists of several images.
You need to ensure that the header of the report is hidden when a user exports the report to
PDF format.
Which Hidden property expression should you use for the report header? {More than one
answer choice may achieve the goal. Select the BEST answer.)
What should you implement?
###BeginCaseStudy###
Case Study: 6
Coho Winery
Overview
You are a database developer for a company named Coho Winery. Coho Winery has an
office in London.
Coho Winery has an application that is used to process purchase orders from customers and
retailers in 10 different countries.
The application uses a web front end to process orders from the Internet. The web front end
adds orders to a database named Sales. The Sales database is managed by a server named
Server1.
An empty copy of the Sales database is created on a server named Server2 in the London
office. The database will store sales data for customers in Europe.
A new version of the application is being developed. In the new version, orders will be placed
either by using the existing web front end or by loading an XML file.
Once a week, you receive two files that contain the purchase orders and the order details of
orders fromoffshore facilities.
You run the usp_ImportOders stored procedure and the usp_ImportOrderDetails stored
procedure to copy the offshore facility orders to the Sales database.
The Sales database contains a table named Orders that has more than 20 million rows.
Database Definitions
Database and Tables
The following scripts are used to create the database and its tables:
Stored Procedures
The following are the definitions of the stored procedures used in the database:
Indexes
The following indexes are part of the Sales database:
Data Import
The XML files will contain the list of items in each order. Each retailer will have its own
XML schema and will be able to use different types of encoding. Each XML schema will use
a default namespace. The default namespaces are not guaranteed to be unique.
For testing purposes, you receive an XSD file from a customer.
For testing purposes, you also create an XML schema collection named ValidateOrder.
ValidateOrder contains schemas for all of the retailers.
The new version of the application must validate the XML file, parse the data, and store the
parsed data along with the original XML file in the database. The original XML file must be
stored without losing any data.
Reported Issues
Performance Issues
You notice the following for the usp_GetOrdersAndItems stored procedure:
• The stored procedure takes a long time to complete.
• Less than two percent of the rows in the Orders table are retrieved by
usp_GetOrdersAndItems.
• A full table scan runs when the stored procedure executes.
• The amount of disk space used and the amount of time required to insert data are very
high.
You notice that the usp_GetOrdersByProduct stored procedure uses a table scan when the
stored procedure is executed.
Page Split Issues
Updates to the Orders table cause excessive page splits on the IX_Orders_ShipDate index.
Requirements
Site Requirements
Users located in North America must be able to view sales data for customers in North
America and Europe in a single report. The solution must minimize the amount of traffic over
the WAN link between the offices.
Bulk Insert Requirements
The usp_ImportOrderDetails stored procedure takes more than 10 minutes to complete. The
stored procedure runs daily. If the stored procedure fails, you must ensure that the stored
procedure restarts from the last successful set of rows.
Index Monitoring Requirements
The usage of indexes in the Sales database must be monitored continuously. Monitored data
must be maintained if a server restarts. The monitoring solution must minimize the usage of
memory resources and processing resources.
###EndCaseStudy###
You need to implement a solution that meets the site requirements.
What should you implement?
You need to restrict access to data in the tables in the data warehouse
###BeginCaseStudy###
Case Study: 3
Data Architect
General Background
You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.
You have been tasked with designing a data warehouse that uses the company’s financial
database as the data source. From the data warehouse, you will develop a cube to simplify the
creation of accurate financial reports and related data analysis.
Background
You will utilize the following three servers:
• ServerA runs SQL Server Database Engine. ServerA is a production
server and also hosts the financial database.
• ServerB runs SQL Server Database Engine, SQL Server Analysis
Services (SSAS) in multidimensional mode, SQL Server Integration Services
(SSIS), and SQL Server Reporting Services (SSRS).
• ServerC runs SSAS in multidimensional mode.
• The financial database is used by a third-party application and the table
structures cannot be modified.
The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)
The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is
partitioned by month. It contains only the last five years of financial data. The CouponUsed,
OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is
less than 10 MB and has only one partition.
The SecurityFilter table specifies the sites to which each user has access.
Business Requirements
The extract, transform, load (ETL) process that updates the data warehouse must run daily
between 8:00 P.M. and 5:00 A.M. so that it doesn’t impact the performance of ServerA
during business hours. The cube data must be available by 8:00 A.M.
The cube must meet the following business requirements:
• Ensure that reports display the most current information available.
• Allow fast access to support ad-hoc reports and data analysis.
Business Analysts will access the data warehouse tables directly, and will access the cube by
using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint
Services. These tools will access only the cube and not the data warehouse.
Technical Requirements
SSIS solutions must be deployed by using the project deployment model.
You must develop the data warehouse and store the cube on ServerB. When the number of
concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to
ServerC and meet following requirements:
• Maintain copies of the cube on ServerB and ServerC.
• Ensure that the cube is always available on both servers.
• Minimize query response time.
The cube must meet the following technical requirements:
• The cube must be processed by using an SSIS package.
• The cube must contain the prior day’s data up to 8:00 P.M. but does not
need to contain same-day data.
• The cube must include aggregation designs when it is initially
deployed.
• A product dimension must be added to the cube. It will contain a
hierarchy comprised of product name and product color.
Because of the large size of the SalesTransactions table, the cube must store only
aggregations—the data warehouse must store the detailed data. Both the data warehouse and
the cube must minimize disk space usage.
As the cube size increases, you must plan to scale out to additional servers to minimize
processing time.
The data warehouse must use a star schema design. The table design must be as denormalized
as possible. The history of changes to the Customer table must be tracked in the data
warehouse. The cube must use the data warehouse as its only data source.
Security settings on the data warehouse and the cube must ensure that queries against the
SalesTransactions table return only records from the sites to which the current user has
access.
The ETL process must consist of multiple SSIS packages developed in a single project by
using the least amount of effort. The SSIS packages must use a database connection string
that is set at execution time to connect to the financial database. All data in the data
warehouse must be loaded by the SSIS packages.
You must create a Package Activity report that meets the following requirements:
• Track SSIS package execution data (including package name, status,
start time, end time, duration, and rows processed).
• Use the least amount of development effort.
###EndCaseStudy###
You need to restrict access to data in the tables in the data warehouse.
What should you do?
You need to ensure that all of the text boxes in the table headers use the correct business unit colors
You are developing a new SQL Server Reporting Services (SSRS) report in SQL Server
Data Tools (SSDT).
The report must define a report parameter to prompt the user for the business unit. Each
business unit has a unique color scheme combination of foreground and background colors.
You need to ensure that all of the text boxes in the table headers use the correct business
unit colors.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)
What should you add to usp_GetOrdersAndItems?
###BeginCaseStudy###
Case Study: 6
Coho Winery
Overview
You are a database developer for a company named Coho Winery. Coho Winery has an
office in London.
Coho Winery has an application that is used to process purchase orders from customers and
retailers in 10 different countries.
The application uses a web front end to process orders from the Internet. The web front end
adds orders to a database named Sales. The Sales database is managed by a server named
Server1.
An empty copy of the Sales database is created on a server named Server2 in the London
office. The database will store sales data for customers in Europe.
A new version of the application is being developed. In the new version, orders will be placed
either by using the existing web front end or by loading an XML file.
Once a week, you receive two files that contain the purchase orders and the order details of
orders fromoffshore facilities.
You run the usp_ImportOders stored procedure and the usp_ImportOrderDetails stored
procedure to copy the offshore facility orders to the Sales database.
The Sales database contains a table named Orders that has more than 20 million rows.
Database Definitions
Database and Tables
The following scripts are used to create the database and its tables:
Stored Procedures
The following are the definitions of the stored procedures used in the database:
Indexes
The following indexes are part of the Sales database:
Data Import
The XML files will contain the list of items in each order. Each retailer will have its own
XML schema and will be able to use different types of encoding. Each XML schema will use
a default namespace. The default namespaces are not guaranteed to be unique.
For testing purposes, you receive an XSD file from a customer.
For testing purposes, you also create an XML schema collection named ValidateOrder.
ValidateOrder contains schemas for all of the retailers.
The new version of the application must validate the XML file, parse the data, and store the
parsed data along with the original XML file in the database. The original XML file must be
stored without losing any data.
Reported Issues
Performance Issues
You notice the following for the usp_GetOrdersAndItems stored procedure:
• The stored procedure takes a long time to complete.
• Less than two percent of the rows in the Orders table are retrieved by
usp_GetOrdersAndItems.
• A full table scan runs when the stored procedure executes.
• The amount of disk space used and the amount of time required to insert data are very
high.
You notice that the usp_GetOrdersByProduct stored procedure uses a table scan when the
stored procedure is executed.
Page Split Issues
Updates to the Orders table cause excessive page splits on the IX_Orders_ShipDate index.
Requirements
Site Requirements
Users located in North America must be able to view sales data for customers in North
America and Europe in a single report. The solution must minimize the amount of traffic over
the WAN link between the offices.
Bulk Insert Requirements
The usp_ImportOrderDetails stored procedure takes more than 10 minutes to complete. The
stored procedure runs daily. If the stored procedure fails, you must ensure that the stored
procedure restarts from the last successful set of rows.
Index Monitoring Requirements
The usage of indexes in the Sales database must be monitored continuously. Monitored data
must be maintained if a server restarts. The monitoring solution must minimize the usage of
memory resources and processing resources.
###EndCaseStudy###
You need to modify usp_GetOrdersAndItems to ensure that an order is NOT retrieved by
usp_GetOrdersAndItems while the order is being updated.
What should you add to usp_GetOrdersAndItems?
You need to ensure that managers can successfully run reports
###BeginCaseStudy###
Case Study: 4
WingTip Toys
General Background
You are a data architect for WingTip Toys. The company uses SQL Server 2012 Enterprise
edition. SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS)
are installed on separate servers.
Data Warehouse
The company’s data warehouse initially contained less than 100 MB and 100 million rows of
data from only one data source. It now contains more than 10 TB and 10 billion rows of data,
in 25 tables, from 12 data sources.
The largest table in the data warehouse, the factOrders table, contains 5 TB of data. The
factOrders table contains three date keys: OrderDateKey InvoiceDateKey, and ShipDateKey.
The data warehouse server has 1 TB of RAM. Memory usage is currently at 20 percent.
One billion rows of data are added to the data warehouse each month. New data is copied
each night from the data sources into SQL Server staging tables, and existing records are not
updated. The largest data set is order information, which is loaded in parallel into multiple
staging tables, one for each data source. All the staging tables have the same structure and
belong to the same filegroup as the factOrders table.
The dimCustomers table stores customer information that may change over time.
Data Models
You are developing three SSAS databases, as described in the following table.
Reporting
Business users frequently generate reports in Microsoft Excel by using PowerPivot. The
PowerPivot Management Dashboard does not currently display any usage data.
Several SSRS reports exist that use the data warehouse as a source. The data warehouse
queries are aggregate queries that use the factOrders table and one or more dimension tables.
All SSRS data sources use Integrated Windows authentication.
SSRS displays a security access error message when managers run SSRS reports based on the
Operations database.
Reporting performance has become unacceptably slow.
Business Requirements
Improve the query speed of the SSRS reports.
Allow business users to create reports by using PowerPivot and Power View.
Ensure that all users other than business users can view metadata for the Customers
dimension. Ensure that business users cannot view metadata for the Customers dimension.
Technical Requirements
Modify the tables in the data warehouse to minimize aggregate query processing time.
Minimize disk storage in the data warehouse.
Ensure that all multidimensional models process data as quickly as possible.
Create a fact table named factCustomerContact in the data warehouse to store the contact
date, customer key, and communication type for each instance of customer contact.
Store the history of customer information changes in the dimCustomers table.
Move data from the staging tables into the factOrders table as quickly as possible. When
creating dimensions for the date keys in the factOrders table, minimize storage space
requirements and optimize the cube processing time.
Ensure that queries against the Sales database return the most current data in the data
warehouse.
Ensure that the SSAS model of the Finance database does not page to disk or return a
memory error as the size of the database grows.
Create an SSAS monitoring solution that tracks the following data:
• Queries answered per second
• Queries from cache direct per second
• Queries from file per second.
###EndCaseStudy###
You need to ensure that managers can successfully run reports.
What should you do?
What should you recommend?
###BeginCaseStudy###
Case Study: 1
Tailspin Toys
Background
Tailspin Toys is a multinational company that manufactures toys. Tailspin Toys has offices in
five regions worldwide. The company sells toys at various retail stores. The company also
sells toys directly to consumers through a web site.
The company has the following departments:
• Sales
• Distribution
• Manufacturing
Each department has an office in each region.
The fiscal calendar of Tailspin Toys runs from June to May.
The network contains a server farm that has Microsoft SharePoint Server 2013 installed.
Existing Environment
Current Database Environment
Each department uses SharePoint team sites for internal collaboration.
All manufacturing information is stored in a relational database named Manufacturing. All
sales information is stored in a relational database named Sales.
Tailspin Toys deploys SQL Server Analysis Services (SSAS) and configures SSAS to use
tabular models. SSAS will be used for all sales reports.
Tailspin Toys deploys a SQL Server Reporting Services (SSRS) instance in SharePoint
mode.
Sales Database
A database named Sales contains two tables named FactSales and DimProduct. FactSales
contains the following columns:
• SalesID
• Total Due
• OrderDate
DimProduct contains the following columns:
• ProductID
• ProductName
• ProductCategory
• ProductSubcategory
The Sales database contains information about the products. Most of the products have a
category and a subcategory. Certain products only have a category.
A sample from DimProduct is shown in the following table.
Requirements
Security Requirements
Tailspin Toys identifies the following security requirement:
• Sales department users must be allowed to view the sales transactions from their
region only.
• Sales department users must be able to view the contents of the manufacturing
reports.
• Manufacturing department users must be able to create new manufacturing reports.
• Third-party and custom solutions must NOT be deployed to the reporting server.
• Sales department users must NOT be able to create new manufacturing reports.
Planned Reporting Implementation
The manufacturing department plans to use the SSRS instance for its reports. The
manufacturing department also plans to make its reports accessible from SharePoint. All
manufacturing reports will use an existing database named Manufacturing.
Reporting Requirements
Tailspin Toys identifies the following reporting requirements:
• All reports must contain the company logo and a header that contains the date and the
time that the report was executed.
• All reports must be created by using the SQL Server Data Tools.
Manufacturing report
You plan to create a report named Manufacturinglssues.rdl. The report has the following
requirements:
• Manufacturing department managers must be able to view product issues by product
type, manufacturing plant location, and error type.
• The manufacturing department managers must be able to change views by choosing
options from drop-down lists.
Sales reports
You plan to create a sales report named RegionalSales.rdl. The report has the following
requirements:
• Users must be able to view the report by using a web browser. By default,
subcategories and product details must be hidden when using the browser.
• Users must be able to subscribe to receive the report by email. The report must be sent
by email as a PDF attachment.
You plan to create a quarterly sales report named QuarterSales.rdl. The report must display
sales data by fiscal quarter.
Technical Requirements
Tailspin Toys identifies the following technical requirements:
• Products in the DimProduct table that do NOT have a subcategory must use the
category value as the subcategory value.
• SSRS must NOT connect to databases more frequently than once every 30 minutes.
• Sales department users must be able to use Microsoft Excel to browse tabular data.
###EndCaseStudy###
You need to recommend a solution for the sales department that meets the security
requirements.
What should you recommend?
Which statement should you execute?
###BeginCaseStudy###
Case Study: 6
Coho Winery
Overview
You are a database developer for a company named Coho Winery. Coho Winery has an
office in London.
Coho Winery has an application that is used to process purchase orders from customers and
retailers in 10 different countries.
The application uses a web front end to process orders from the Internet. The web front end
adds orders to a database named Sales. The Sales database is managed by a server named
Server1.
An empty copy of the Sales database is created on a server named Server2 in the London
office. The database will store sales data for customers in Europe.
A new version of the application is being developed. In the new version, orders will be placed
either by using the existing web front end or by loading an XML file.
Once a week, you receive two files that contain the purchase orders and the order details of
orders fromoffshore facilities.
You run the usp_ImportOders stored procedure and the usp_ImportOrderDetails stored
procedure to copy the offshore facility orders to the Sales database.
The Sales database contains a table named Orders that has more than 20 million rows.
Database Definitions
Database and Tables
The following scripts are used to create the database and its tables:
Stored Procedures
The following are the definitions of the stored procedures used in the database:
Indexes
The following indexes are part of the Sales database:
Data Import
The XML files will contain the list of items in each order. Each retailer will have its own
XML schema and will be able to use different types of encoding. Each XML schema will use
a default namespace. The default namespaces are not guaranteed to be unique.
For testing purposes, you receive an XSD file from a customer.
For testing purposes, you also create an XML schema collection named ValidateOrder.
ValidateOrder contains schemas for all of the retailers.
The new version of the application must validate the XML file, parse the data, and store the
parsed data along with the original XML file in the database. The original XML file must be
stored without losing any data.
Reported Issues
Performance Issues
You notice the following for the usp_GetOrdersAndItems stored procedure:
• The stored procedure takes a long time to complete.
• Less than two percent of the rows in the Orders table are retrieved by
usp_GetOrdersAndItems.
• A full table scan runs when the stored procedure executes.
• The amount of disk space used and the amount of time required to insert data are very
high.
You notice that the usp_GetOrdersByProduct stored procedure uses a table scan when the
stored procedure is executed.
Page Split Issues
Updates to the Orders table cause excessive page splits on the IX_Orders_ShipDate index.
Requirements
Site Requirements
Users located in North America must be able to view sales data for customers in North
America and Europe in a single report. The solution must minimize the amount of traffic over
the WAN link between the offices.
Bulk Insert Requirements
The usp_ImportOrderDetails stored procedure takes more than 10 minutes to complete. The
stored procedure runs daily. If the stored procedure fails, you must ensure that the stored
procedure restarts from the last successful set of rows.
Index Monitoring Requirements
The usage of indexes in the Sales database must be monitored continuously. Monitored data
must be maintained if a server restarts. The monitoring solution must minimize the usage of
memory resources and processing resources.
###EndCaseStudy###
You need to implement a solution that addresses the performance issues of the
usp_GetOrdersByProduct stored procedure.
Which statement should you execute?