Case Scenario in Visual Basic .NET

Paint QR Code in Visual Basic .NET Case Scenario

In the following case scenario, you will apply what you ve learned in this chapter. You can find the answers to the questions in the Answers section at the end of this chapter.

You are a DBA with Trey Research and are responsible for a SQL Server 2005 member server named SQLA that is located at company headquarters. SQLA contains the company s Human Resources (HR) database, customer database, and research projects database. Trey Research has expanded rapidly in the last year, resulting in an expansion of the number of employees, customers, and research projects. As a result, the response time for queries run against all three databases, but especially the research projects database, has increased noticeably. Management has provided a budget to upgrade SQLA if necessary, but they want to ensure that expenditure is prioritized to resolve major system bottlenecks. You suspect that some queries are misconfigured, causing excessive recompiles. You are also concerned that transaction logs might be becoming full. SQLA is running SQL Server 2005 SP1 Enterprise Edition on a Windows Server 2003 member server in the Trey Research Active Directory domain. It is an SMP server with four Pentium 4 CPUs running at 3.0 GHz, with 4 GB of RAM, and six physical disks at 60 GB apiece. Of the six physical disks, the first two form a hardware RAID1 array that holds the operating system, SQL Server 2005, and the transaction log. The remaining four disks are used in a hardware RAID10 configuration and contain the HR, customer, and research projects databases. The current size of the research projects database is 10 GB, and the database has been growing at an average rate of 5 percent per month for the past year. Customer growth is occurring at the same rate. The current size of the HR database is 5 GB, and this database has been growing at an average rate of 1.5 percent per month for the past year. At present, there is no failover server. Peak activity for the database server occurs between 6 P.M. and 9 P.M. local time. You have been collecting counter logs during these times. Viewing these logs in System Monitor reveals the average values shown in Table 1-3.

Table 1-3

Average Reading 60% 25 42%

Table 1-3

Object: Counter Physical Disk: Avg. Disk Queue Length SQL Server Buffer Manager: Buffer Cache Hit Ratio

Average Reading 2 89%

1. Which subsystem is the most likely cause of the long response times during hours of peak usage 2. What short-term fix could improve SQL Server 2005 performance 3. What tools can you use to analyze and optimize queries 4. How can you detect excessive recompiles 5. What graphical tool can you use to analyze deadlocks and find what is causing them 6. You need to be alerted if a transaction log is becoming full. How should you configure this alert

To help you successfully master the exam objectives presented in this chapter, complete the following tasks.

The main tools for troubleshooting physical server performance are the Windows Server 2003 (or Windows 2000 Server) Performance Tools, particularly Performance Logs and Alerts.

Configure counter logs, trace logs, and alerts. Investigate the use of instances. Click the Explain button to find out what the various counters record.

Practice 1: Use SQL Server Profiler. Use Profiler to generate and analyze traces generated by queries, stored procedures, and applications. Search the Internet for sample Profiler traces (for example, in online magazines).

You can use several tools to troubleshoot database performance. These include SQL Server Management Studio, Transact-SQL statements, stored procedures, DMVs, SQL Server Profiler, and the DTA.

Practice 1: Use SQL Server Management Studio. This tool is likely your main inter-

face with SQL Server. Use Object Explorer, Query Editor, and Activity Monitor. Find out what additional features the tool provides.

Practice 2: Use Transact-SQL statements, stored procedures, and DMVs. You are not expected to write large Transact-SQL applications, but you do need to be able to use Query Editor for administration and troubleshooting. Practice executing Transact-SQL statements, stored procedures, and DMVs from Query Editor. Build on the examples given in this chapter. Practice 3: Use Profiler.

Generate Profiler traces, and investigate the Deadlock Run the DTA on your test network. Do not run this tool