How to Troubleshoot Microsoft SQL

The Microsoft SQL Server is a computer database server, which is a program that services databases to other computer programs under the client-server model. The latest release is the SQL Server 2008 R2 and was launched in April 2010. This version has a master data management system, a central management of master data entities and hierarchies. It also includes a centralized console that manages multiple SQL Server 2008 services including relational databases.

• 1

  Obtain a higher level of performance if your SQL Server is operating slowly. Little configuration tuning is required to do this. Various issues--including blocking, system resource contention, application design problems, or queries or stored procedures with long execution times--are the sources of most operating problems. Use an SQL Profiler to reveal what is happening in the server under a typical load. Most performance problems can be solved by running the Profiler with Microsoft Windows NT Performance Monitor to solve system blockage.

• 2

  Open SQL Profiler, then click on the Tools menu. Click "Options." Select "All Event Classes" and the "All Data Columns," then click "OK." Make a new trace. Point to "New" in the File menu and click "Trace." Specify the trace name and a file in the General tab to capture the data. Open the Events tab and include these event types to the trace: CurserPrepare, Missing Column Statistics, Attention, Auto-UpdateStats, Exec Prepared SQL, Execution Plan, Prepare SQL, Unprepare SQL, Connect, Disconnect, Existing Connection, SP: Completed, SP: Recompile, SP: Starting, SP: StmtCompleted, SQL: BatchCompleted, SQL: StmtCompleted and RPC: Completed. The trace should turn up and repair any of these problems.

• 3

  Use Performance Monitor to utilize Windows NT and SQL Server counters. Click "Log" under the View Menu. Click "Log" in the Options menu. Indicate a file name and location to monitor the performance counters. Adjust the update interval as you see appropriate. Click "Add to Log" under the Edit menu. Add the Windows NT and the SQL Server objects. Click "Log" in the Options menu to start the log, then click the "Start Log" button.

• 4

  Examine the results. Check for blocking by observing the sp_who output. If it is non-zero, then your system is blocked. Longer execution times are apparent if the processes are blocking each other. Examine the SQL Profiler output to resolve performance issues. View the captured data by clicking on the Properties tabs under the File menu. Search the whole trace or specific columns by clicking on "Find" in the Edit menu. Click "Properties" under the File menu. Click on the "Data Columns" tab and use the UP button to move Event Class under Groups heading. Click the "Down" button to delete other columns under the Groups heading. Click "OK."