SQL Server Performance and SQL Tuning

1.

1 point

You are running a query against the Employees table in HumanResources database. A clustered index exists on the Employee_Second_Name column of this table. The data in the table is case insensitive. You want to list all employees whose second name starts with M. You do not believe that any duplicate rows exists, and if they do, listing them does not cause a problem. Which of the following queries returns the information you need?

SELECT DISTINCT Employee_Second_Name FROM HumanResources.Employees WHERE SUBSTRING(Employee_Second_Name,1,1) = 'M'

SELECT Employee_Second_Name FROM umanResources.Employees WHERE SUBSTRING(Employee_Second_Name,1,1) = 'M'

SELECT DISTINCT Employee_Second_Name FROM HumanResources.Employees WHERE Employee_Second_Name LIKE 'm%'

SELECT Employee_Second_Name FROM HumanResources.Employees WHERE Employee_Second_Name LIKE 'm%'

2.

1 point

You are analyzing a query plan for a query that you believe is causing excessive logical read operations. You do not want to use resources on the production server, and you do not have no test or monitoring server. You do not have a problem with executing the query to analyze its plan. You need to find out the following information as quickly as possible : 1. The total number of logical reads performed by executions of this plan since it was compiled. 2. The number of logical reads performed the last time the plan was executed. 3. The minimum and maximum number of logical reads that this plan has ever performed during a single exection. How should you proceed? *

Copy the query into Query Editor. Use the SMSS graphical execution plan facility to graphically display the data retrieval methods chosen by the SQL Server query optimizer.

Copy the query into Query Editor. Access the DTA directly from SMSS.

Capture a trace by using Profiler. Run the DTA specifying this trace as a workload.

Use the sys.dm_exec_query_stats

3.

1 point

Define Database Normalization in your own words. List the 5 different type of Database Normalization.

4.

1 point

You need to capture the execution plan for a query. Which SET statement should you use?

5.

1 point

List types of indexes.

6.

1 point

You have been tasked to write a query to select one million rows. You need to optimize the query to return the first 50 rows as quickly as possible. What query hint should you use? *

OPTIMIZE FOR @ROWS=50

MAXDOP 50

TABLE HINT(Table, INDEX(50))

FAST 50

7.

1 point

You need to build CREATE INDEX statements for all the missing indexes that SQL Server hasidentified. Which Dynamic Managment View should you use?

sys.dm_db_missing_index_details

sys.dm_db_missing_index_columns

sys.dm_db_missing_index_group_stats

sys.dm_db_index_usage_stats

8.

1 point

You notice that a database server is responding slowly to queries. You run the following DMV query on the server. SELECT TOP (10) wait_time, wait_time_ms FORM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC The query returns a top wait type of SOS_SCHEDULER_YIELD. You need to identify what is causing the serve response issues. Which resource should youinvestigate. *

Network

Memory

CPU

Disk

9.

1 point

You need to find out the degree of fragmentation of a table index. How do you do this?

Use DBCC INDEXDEFRAG

Use the sys.indexes catalog view.

Use the CREATE INDEX Transact-SQL statement with the DROP_EXISTING clause.

Use the sys.dm_db_index_physical_stats DMV

10.

1 point

In addition to SQL Profiler, System Monitor(Performance Monitor), what other tools are used to troubleshoot SQL Server Resource bottlenecks.

11.

1 point

You have a database that uses stored procedures to perform INSERT, UPDATE, DELETE and SELECT statements. You are tasked with providing a recommendation of indexes to be created and dropped from the databases. You need to select the appropriate method to accomplish the task. Which method should you use?

Index Usage DMVs

Missing Index DMVs

SQL Server Profiler

Database Engine Tuning Advisor

12.

1 point

You are creating a new table in a database. Your business require you to store data in the table for only seven days. You need to implement a partioned table to meet this business requirement.

1. Create the partition function 2. Create the partition scheme 3.Create the table

1. Add a secondary file to the primary filegroups 2. Create the table 3. Create the distributed partioned view

1. Create the partition function 2. Create the table 3. Create a filtered index

1. Create the partion function 2. Create the partion scheme 3. Create the distributed partioned view

13.

1 point

You need to capture the SQL Server Execution Times: (Elapsed Time) for a query. Which SET statement should you use?

14.

1 point

What are the 3 main resource bottlenecks in SQL Server 2005/2008/2012/2014?

15.

1 point

You have two tables named Customers and orders. For customers that have placed at least one order, you need to produce a list of customer names and the number of orders for each customer. Which query should you use?

SELECT c.CustomerName, SUM(o.OrderID) AS [orderCount] FROM Customer c JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName

SELECT c.CustomerName, COUNT(o.OrderID) AS [OrderCount] FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName HAVING COUNT(o.OrderID) > 1

SELECT COUNT(o.OrderID) AS [OrderCount] FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID

SELECT c.CustomerName, COUNT(o.OrderID) AS [OrderCount] FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName

16.

1 point

You are the DBA for a direct marketing organization. Your company uses SQL Server 2005 application that enables users to run both predefined and ad hoc queries against a customer database. You suspect that some of these queries consume an excessive amount of server resources. You need to identify which queries consume the most resources. You want to achieve this goal as quickly as possible. What should you do?

Use Profiler, and include the Showplan event classes

Use the sys.dm_exec_query_stats DMV

Use the sys.dm_db_index_physical_stats DMV

Use sqlcmd utility

17.

1 point

A query runs frequently against a database. It specifies the same 3 columns of a 40-column table and the same 4 columns of a 25 column table. Both tables are read intensive and updated infrequently. How do you optimize the query?

Create a standard view that contains all the columns that are used in the query.

Rebuild the indexes on th elarger table, and specify a fill factor of 75%.

Create an indexed view that contaions all the columns that are used in the query.

Rebuild the indexes on both tables, and enable the pad index option.

18.

1 point

You have two tables named Maintable and Archivetable. You need to move data older than 30 days from Maintable into ArchiveTable. Which code segment should you use?

DELETE FROM MainTable OUTPUT deleted.* WHERE RecordDate < DATEADD(D, -30, GETDATE())

DELETE FROM MainTable OUTPUT DELETED.* INTO ArchiveTable WHERE RecordDate < DATEADD(D, -30, GETDATE())

INSERT INTO ArchiveTable SELECT * FROM MainTable WHERE RecordDate < DATEADD(D,-30, GETDATE())

INSERT INTO ArchiveTable SELECT * FROM MainTable WHERE RecordDate < DATEADD(D, -30, GETDATE()) DELETE FROM MainTable

19.

1 point

You need to capture and record a workload for analysis by the Database Tuning Advisor(DTA). Which tool should you use?

Activity Monitor

DTA utility

Performance Monitor

SQL Server profiler

20.

1 point

Your compnay uses a SQL Server 2005 database that contains a table named Sales.Item. The table has 12 columns. The most common queries that are run against the table take the following form : SELECT Name, ProductLine, Price, Color, Style FROM Sales.Item WHERE ProductLine = '[product_line_name]' ORDER BY Name Which indexes should you create? (Choose all that apply).

Create a nonclustered index on the Name column.

Create a clustered index on the ProductLine, Price, and Style columns.

Create a clustered index on the Name column.

Create a nonclustered index on the ProductLine column, and include the Price, Color, and Style columns.

21.

1 point

Your database is 5GB and contains a table named SalesHistory. Sales information is frequently inserted and updated. You discover that excessive page splitting is occuring. You need to reduce the occurrence of page splitting in the SalesHistory table. Which code segment should you use?

UPDATE STATISTICS Sales.SalesHistory(Products) WITH FULLSCAN, NORECOMPUTE

ALTER DATABSE Sales MODIFY FILE(Name=SalesDat, SIZE=10GB)

ALTER INDEX ALL ON Sales.SalesHistory REBUILD WITH (FILLFACTOR=60)

EXEC sys.sp_configure 'FILL FACTOR()%','60'

22.

1 point

Which DMVs(Dynamic Management Views) are used to give a high level view of which queries are taking the most CPU Time.

sys.dm_exec_sql_text and sys.dm_exec_query_stats

sys.dm_exec_query_stats and sys.dm_exec_requests

sys.dm_exec_query_stats and sys.dm_exec_query_optimizer_info

sys.dm_tran_locks and sys.dm_exec_query_optimizer_info