Lesson 2: Troubleshooting Connectivity to a SQL Server Instance in VB.NET

Generate QR-Code in VB.NET Lesson 2: Troubleshooting Connectivity to a SQL Server Instance

1

You can use Windows Server 2003 Performance Tools, Profiler, DTA, and DMVs to analyze and troubleshoot instance performance.

A single tempdb database exists for each SQL Server instance, and tempdb issues can generate bottlenecks. You can configure the maximum number of user connections to a SQL Server instance. Allowing excessive connections can result in stress on the memory resource. Microsoft recommends that you configure a SQL Server instance to use a dynamic port address by specifying a port address of 0 in the Server Network utility. If this is not possible, use a port address of less than 1024. You can control the maximum degree of CPU parallelism for individual query and index operation statements by specifying the MAXDOP query hint or MAXDOP index option. You can monitor waits (for example, lock waits, queue waits, latch waits, and I/O waits) by using the SQLServer:Wait Statistics and SQLServer:Latches performance objects.

You can use the following questions to test your knowledge of the information in Lesson 2, Troubleshooting Connectivity to a SQL Server Instance. The questions are also available on the companion CD if you prefer to review them in electronic form.

Answers to these questions and explanations of why each answer choice is right or wrong are located in the Answers section at the end of the book.

1. You are a DBA employed by BlueSky Airlines. Users report that a booking application running on a SQL Server 2005 instance is performing poorly. After investigating the problem, you discover that disk I/O activity on the volume that contains the database data files is very high. You generate a Profiler trace while the application is running. How do you find out what SQL Server operations are causing the high disk activity A. Search for events that have high CPU activity. B. Search for events that have high read values or high write values. C. Search for events that have high duration values. D. Search for events with an SPID value of 56.

1

2. You are a DBA employed by Coho Vineyards. Users report that a SQL Server 2005 stock control application stops responding for several seconds at random times throughout the day and affects many users at once. You cannot detect excessive pressure on the physical server resources and suspect that locks might be an issue. You need more information. What should you do A. Examine the sys.dm_db_task_space_usage DMV. B. Examine the sys.dm_tran_locks DMV. C. Examine the sys.dm_os_memory_cache_clock_hands DMV. D. Run Profiler to examine the SQL: StmtRecompile event class. 3. You are the senior DBA at Northwind Traders. Your database administration team monitors a large number of SQL Server 2005 servers located at company headquarters and branch locations throughout the United States. Members of your team need to ensure that query response times comply with the requirements of Northwind Traders service level agreements (SLAs). You need to provide your team with a consistent way of monitoring query response times on the servers. What should you do A. Instruct the administrators to use the Performance console to monitor the SQL Server Buffer Manager: Buffer Cache Hit Ratio counter. B. Run Profiler by using the SQL: StmtRecompile event class. Distribute the Profiler trace to your team and instruct them to use DTA on their servers and follow the recommendations. C. Create SQL Server Profiler templates that include query start times and end times. Distribute these templates to the members of your team. D. Instruct your team to use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage DMVs.

SQL Server 2005 introduces SQL Server Management Studio, which provides a consistent and (arguably) user-friendly interface for managing and troubleshooting database issues. SSMS is an interactive, graphical tool that enables a DBA to write queries, execute multiple queries simultaneously, view results, analyze the query plan, and receive assistance to improve the query performance. The Execution Plan options graphically display the data retrieval methods chosen by the SQL Server query optimizer. Specific troubleshooting tools such as System Monitor, Performance Logs and Alerts, SQL Server Profiler, and the DTA are also relevant to database troubleshooting, in addition to server and instance troubleshooting. SQL Server 2005 provides DMVs, stored procedures, Transact-SQL statements, and Database Console Commands (DBCCs), which you can use to isolate database problems.

After this lesson, you will be able to:

Resolve space issues. Update statistics. Evaluate index usage. Defragment and rebuild indexes. Create missing indexes and drop inappropriate indexes. Audit and analyze poorly written queries. Monitor transaction log size and database growth. Investigate locking and resolve deadlocks. Determine optimum RAID configuration.