Apply Your Knowledge


Exercises

The following exercises take you through the variety of troubleshooting, performance tuning, and resource optimization tools available to the SQL Server administrator.

12.1 Exploring DBCC

Exercise 12.1 compares the output of CHECKALLOC to that of CHECKDB .

Estimated Time: 5 minutes.

  1. If not already open , load the SQL Query Analyzer. Supply the logon connection information if requested .

  2. Enter a query to check the consistency of disk space allocation structures:

     DBCC CHECKALLOC ('Northwind') 
  3. Compare the results against a check of the allocation and structural integrity of all the objects. ( CHECKDB includes CHECKALLOC .)

     DBCC CHECKDB ('Northwind') 

    Note that the output of the CHECKALLOC command covers a lot more detail than CHECKDB , although CHECKDB is more thorough.

12.2 Defining a Playback Trace

Exercise 12.2 traces SQL Server activity with a trace description that enables the activity to be played back.

Estimated Time: 20 minutes.

  1. If not already open, load the SQL Profiler. Supply the logon connection information if requested.

  2. Start a new trace definition. Supply the logon connection information if requested.

  3. Enter Playback for the name of the trace and select the QLProfilerTSQL_Replay template. Store the replay to a table named Playback and select a database for storage, other than Master or Northwind .

    It is a good idea and standard practice to have a database prepared that will store traces from a variety of other databases. Make sure that you name the stored traces well and document circumstances surrounding the capture.

  4. Select Run to begin the data capture.

  5. Open an instance of the SQL Query Analyzer and select Northwind from the database drop-down list box.

  6. Execute a series of simple queries similar to the following list:

     SELECT * FROM Employees GO SELECT * FROM Employees  WHERE Title LIKE 'Sales%' GO SELECT * FROM Customers  WHERE ContactTitle LIKE 'Own%' GO 
  7. Return to the Profiler window and stop the trace. Save the trace results and close the Trace window.

    Note that after a trace has been captured and saved to a table, you can reopen it and replay the trace later.

12.3 Replaying the Playback Trace

Exercise 12.3 replays the trace captured in Exercise 12.2.

Estimated Time: 15 minutes.

  1. If not already open, load the SQL Profiler. Supply the logon connection information if requested.

  2. From the File menu, select Open and then Trace Table. Supply the logon connection information if requested.

  3. Locate the database and table from the capture in Exercise 12.2 and select OK to open the trace.

  4. Click in the window and select the query:

     SELECT * FROM Employees  WHERE Title LIKE 'Sales%' 
  5. From the Replay menu, select Run to Cursor. Supply the logon connection information to your machine when requested. In the Query Selection dialog box, select the defaults and click Start to begin playback.

    Notice that the events replay up to the point of the query.

  6. From the Replay menu select Start to enable the remainder of the query to be replayed.

Review Questions

1:

Which DBCC operations should be performed on a regular basis? How frequently should they be executed?

A1:

CHECKDB should be run on a periodic basis to check storage allocation; INDEXDEFRAG should be run if the index structure appears to be too fragmented ; SHRINKDATBASE should be run if there is an excessive amount of free space in the database files.

2:

In what situations would the SQL Query Analyzer be used instead of the Profiler?

A2:

The SQL Query Analyzer is used to troubleshoot applications and query problems, and would be used when fine-tuning is to be done at a level that requires focusing on individual commands. The SQL Profiler is used to track SQL Server events on a broader scale.

3:

Which stored procedures can be used to get information similar to what is found in the Enterprise Manager Current Activity window?

A3:

The sp_who stored procedure provides information about the current user session, and sp_lock provides information on current processes and SQL Server locking in effect.

4:

What aspects of alerts and operators make them useful in maintaining a SQL Server environment?

A4:

Alerts can be set up to monitor situations that may need to be addressed by an administrator. The administrators can be set up as operators so that if the alert occurs, the administrator can be contacted via email, pager, or net send .

5:

How would you go about auditing a SQL Server?

A5:

The SQL Profiler is used to audit a server. You should carefully select the objects that you need to audit because this operation can add a significant amount of overhead to the server.

Exam Questions

1:

You are evaluating the database design given to you by another developer. This database was to be designed with an emphasis on query performance, and an attempt has been made to meet the design goal. Replying to this directive, the developer has sketched out a design for several indexes for the tables. These indexes have been put together with the highest expected query usage kept in mind. As you review his design, you notice that the new indexes provide varying degrees of benefit to a variety of queries. Which of his indexes is likely to be the most effective?

  1. An index on gender for 134,000 registered voters.

  2. An index on the sales agent's last initial for 25,000 orders.

  3. An index for the StateCode Primary Key column in a US_States table.

  4. An index for the State column in a PacificTime_ZIP_Codes table.

A1:

C. Gender is never a good column to supply an index against because it has only two possible values. In general, a column with a high percentage of unique values is the best choice for indexing. An agent's last initial has 26 possibilities in a table of 25,000, which is still a rather poor choice. An index created on State value would be a good choice in either C or D, but C provides the best ratio. For more information, consult Chapter 10.

2:

As a database implementer you are developing several new stored procedures on an existing SQL Server 2000 database that resides at your company headquarters. You are experimenting with a Query Analyzer session that contains each of the individual queries to be used in these procedures. After running a given SELECT query and using the graphical showplan feature to understand the execution plan, you want to prove that internal statistics are available for a particular column used in the query. How would you best find this information?

  1. Disable the graphical showplan display.

  2. Hold the mouse over each individual node relating to the desired column.

  3. Use a SET SHOWPLAN statement.

  4. Examine each individual node relating to the desired column without mousing over it.

A2:

B. A feature of the graphical showplan display enables you to see information about each node in the display by holding the mouse over the node. A display shows the node analysis based on the contents of the entire query. Of the choices available, this would be the best mechanism to use to gain the desired information. For more information, see the section entitled "Query Analyzer."

3:

You are consulting for a manufacturing company that is running a single SQL Server 2000 computer. The server contains a database named Sales . The database has a group of tables that are used to examine sales trends. The database options are set to their default values.

Analysts who use the database report that query performance has become slower. You analyze the clustered Primary Key on the Invoices table and receive the following results:

 DBCC SHOWCONTIG scanning 'Invoices' table... Table: 'Invoices' (21575115); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned...............................: 200 - Extents Scanned.............................: 50 - Extent Switches.............................: 40 - Avg. Pages per Extent.......................: 4.0 - Scan Density [Best Count:Actual Count].......: 60.00% [3:5] - Logical Scan Fragmentation...................: 0.00% - Extent Scan Fragmentation....................: 40.00% - Avg. Bytes Free per Page.....................: 146.5 - Avg. Page Density (full).....................: 98.19% DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

You want to improve performance of queries that join tables to the Invoices table. What are three possible T-SQL statements you can execute to achieve this goal? (Each correct answer represents a complete solution. Choose three.)

  1. DBCC UPDATEUSEAGE ('Sales','Invoices','PK_Invoices')

  2. CREATE UNIQUE CLUSTERED INDEX PK_Invoices On

     Invoices(InvoiceID) WITH DROP_EXISTING 
  3. DBCC INDEXDEFRAG ('Sales','Invoices','PK_Invoices')

  4. DBCC DBREINDEX (Sales.dbo.Invoices, 'PK_Invoices')

  5. UPDATE STATISTICS 'Invoices'

  6. DBCC CHECKALLOC (Sales, REPAIR_FAST)

A3:

B, C, D. Because of index fragmentation, the reduction in performance in this database could be improved by rebuilding, re-creating, or defragmenting the current index. For more information, see the "Validation DBCC Operations" section.

4:

You are a database implementer of a SQL Server 2000 environment that has a single database server. The server contains all your company's databases, including an investment-tracking database. Each day more than 100 operators make approximately 5,000 changes to customer investments. In addition, daily and monthly reports are created from the investment data.

Another development team at your company needs to optimize a database application. They need a sample of database query activity to discover whether they can speed up the transactions. The developers also want to replay the sample on another SQL Server computer.

You need to capture the sample, but you want to minimize any increase to the workload of the server. What should you do?

  1. Run SQL Profiler on a client computer. Configure SQL Profiler to monitor database activity, and log data to a .trc file.

  2. Run SQL Profiler on the server. Configure SQL Profiler to monitor database activity, and log data to a database table.

  3. Run System Monitor on a client computer. Configure System Monitor to monitor database activity, and log data to a .blg file.

  4. Start SQL Server from a command prompt. Specify trace flag 1204 to enable verbose logging.

A4:

A. Although it would be most desirable to run the query on another SQL Server, it is always best to select a machine other than the production machine to absorb the overhead of the Profiler itself. For more information, see the section "SQL Server Profiler."

5:

You are the administrator of a SQL Server computer. Users report that the database times out when they attempt to modify data. You use the Current Activity window to examine locks held in the database as shown in the Figure 12.12.

Figure 12.12. Current Activity window.

graphics/12fig12.gif

You need to discover why users cannot modify data in the database, but you do not want to disrupt normal database activities. What should you do?

  1. Use the spid 52 icon in the Current Activity window to discover which SQL statement is being executed.

  2. Use the sp_who stored procedure to discover who is logged in as spid 52.

  3. Use SQL Profiler to capture the activity of the user who is logged in as spid 52.

  4. Use System Monitor to log the locks that are granted in the database.

A5:

A. Although knowing who has the login may be useful later, the first thing to look for is what is executing that is causing the block. For more details, see "Current Activity" in SQL Server Books OnLine.

6:

You are working on a SQL Server 2000 computer that contains a database that stores product data for your company. You need to execute an existing stored procedure that examines prices for your company's products and can modify them if necessary. You execute the stored procedure after business hours, but it does not complete. You execute the sp_lock stored procedure and receive the following output.

 spid   dbid   ObjId       IndId   Type Mode ------ ------ ----------- ------- ---- ------- 61     7      0           0       DB   S 64     7      0           0       DB   S 72     7      0           0       DB   S 72     7      2145623952  1       PAG  IS 72     7      2145623952  0       TAB  IS 72     7      2145623952  1       KEY  S 78     7      0           0       DB   S 78     7      2145623952  1       PAG  IX 78     7      2145623952  0       TAB  IX 78     7      2145623952  1       KEY  X 

You want the stored procedure to complete successfully. What should you do?

  1. Execute the stored procedure, and specify the WITH RECOMPILE option.

  2. Execute the DBCC FREEPROCCACHE statement.

  3. Release the locks that are held by connections 61 and 64.

  4. Release the locks that are held by connections 72 and 78.

A6:

D. Procedures 72 and 78 are holding locks against the database and preventing the stored procedure from executing. For more information, see the section "sp_lock."

7:

You are working on a SQL Server 2000 computer that contains a database named Orders , which is used to record customer orders for the products your company sells. Your company's order volume exceeds 1 million orders per day. Each order uses approximately 100KB of space in the database. Users report that the database responds slowly when they enter new orders. You use SQL Profiler to monitor the activity on the database and receive the data shown in the Figure 12.13.

Figure 12.13. Data received by using SQL Profiler.

graphics/12fig13.gif

You need to modify the database to improve performance. What should you do?

  1. Double the size of the data file.

  2. Configure the database to automatically grow by 10%.

  3. Separate the database into two physical files.

  4. Increase the size of the transaction log file.

A7:

B. The data file is growing at too small an increment, which causes growth to occur in small, too-frequent increments . You may even want to set the growth rate higher than 10 percent, but of the available choices this is the best solution. For more information, consult the section "Alternative Mechanisms."

8:

You are working on a SQL Server 2000 computer that contains a database named Sales . The company's web-based application uses the Sales database to store sales transactions. The company web site has grown in popularity, and database utilization has increased. You need to collect data about the utilization of server resources so that you can provide capacity planning. You want to automate the collection process so that information is gathered as quickly as possible. What should you do?

  1. Configure System Monitor to collect data and store it in a SQL Server table.

  2. Create a SQL Server Agent job that executes the sp_statistics stored procedure daily and places the results in a text file.

  3. Use SQL Profiler to trace server activity and store the results in SQL Server tables.

  4. Configure SQL Server alerts to store information in the Windows application event log.

A8:

C. To monitor detailed activity of SQL Server, you should use the SQL Server Profiler and configure a trace to take in the necessary data and filter for the desired objects. For more details, see the "SQL Server Profiler" section.

Suggested Readings and Resources
  1. SQL Server Books Online

    • Transact -SQL Reference DBCC

    • Administering SQL ServerAutomating Administrative Tasks

    • Optimizing Database Performance

  2. Internet Web Links:

    • C2 Administrator's Guide

      http://www.microsoft.com/technet/prodtechnol/sql/maintain/security/sqlc2.asp

    • SQL Server Performance Tuning and Optimization

      http://www.sql-server-performance.com/

    This is an excellent site for looking further into any of the topics covered in this chapter and then some.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net