Troubleshooting the Performance of a SQL Server Solution (Reading Notes, all in the original copy)

Debugging SQL server performance is a very time-consuming process. There are two basic methods:
1. Proactive approach: Pre-estimate possible problems.
2. Reactive solves the problem based on the user experience.
Several factors that affect SQL server2005 performance:
1. Hardware
2. Network
3. SqlServer database engine (configuration parameters
4. SQL server components (SSIS SSAS ...)
5. database
6. client application
There are three major problems:
Physical server
SQL Server instance
Database
Common tools:
Task Manager
This displays information about programs and processes running on the server (or desktop computer). You can use it to get an immediate overview of your server's performance.
System Monitor
System Monitor (perfmon.exe) is used to track resource usage on Windows operating systems through a set of installed performance object counters.
Performance Logs and Alerts
The Performance Logs and Alerts tool allows you to collect performance data automatically from local or remote computers.
Network Monitor Agent
This detects network problems and identifies network traffic patterns.

SQL Server ProfilerSQL Server Profiler is a graphical tool for using traces. A trace captures event data such as Transact-SQL (T-SQL) statements, the start of a stored procedure executing, a lock being acquired or released on a database object, or security permission checks against objects. you can save the captured data in a file or a table for later analysis. you can also use SQL Server Profiler for monitoring Analysis Services. another advantage of SQL Server Profiler in SQL Server 2005 is the option to correlate a trace with Windows performance log data.

In this exercise, you will create a deadlock and see it reflected inSQLServer: Lock: Number Of Deadlocks/secCounter. first you will set up the performance log and start a trace; after that you will run some simple T-SQL statements to create a deadlock. SQL Server Profiler can be of great help in detecting the cause of a deadlock. however, for this demonstration, you will use it just to record the T-SQL statements that will generate the deadlock.

1. Use the Windows Start menu, and choose All Programs Administrative Tools Performance.

2. Expand Performance Logs and Alerts (in the Windows Performance tool), right-click Counter Logs, and click New Log Settings.

3. TypeDeadlock LogAs the name for the counter log, and click OK.

4. On the General tab, click Add Counters.

5. In the Performance Object box, selectSQLServer: Locks.

6. AddNumber of Deadlocks/secCounter, and leave_ TotalSelected in the list of instances.

7. Click Close.

8. Enter1As the value for the Interval box under Sample Data Every.

9. click the Log Files tab, and choose Text File (Comma Delimited) from the Log File Type list (so you can share the log file among different versions of Windows or view the log files later with Microsoft Excel ).

10. On the Schedule tab, specify Manually for both the Start Log and Stop Log options.

11. Click OK to create the performance log.

12. Click the Counter Logs node, right-click Deadlock Log, and select Start from the context menu. Leave the System Monitor console open.

13. From the Windows Start menu, choose All Programs Microsoft SQL Server 2005, and click SQL Server Management Studio. For this exercise, leave SQL Server Management Studio open.

14. Connect to your SQL Server, and then from the Tools menu click SQL Server Profiler.

15. In the File menu (of SQL Server Profiler), select New Trace, and connect to your SQL Server.

16. On the General Tab, enterCorrelation TraceAs the trace name, select the TSQL_Replay template, and check the Save to File box to specify the trace file location and filename. (We used C: "Correlation Trace. trc .) click Run.

 

17. Switch back to SQL Server Management Studio. Open a new query window, and run the following query:

       USE tempdb ;

       GO

       CREATE TABLE Employee (

       EmployeeID INT,

     EmployeeName VARCHAR(64)

      )

       GO

    CREATE TABLE Orders (

         OrderID INT,

  Amount INT

    )

       GO

     INSERT INTO Employee VALUES (69, 'Angelina Jolie')

      GO

       INSERT INTO Orders VALUES (1000, 200)

GO

19. Type the following statements in the query window, but do not run the query yet:

       USE tempdb ;

      GO

       BEGIN TRAN

     UPDATE Employee

     SET EmployeeName = 'Lara Croft'

   WHERE EmployeeID = 69

   WAITFOR DELAY '00:00:10'

     UPDATE Orders

       SET Amount = 300

WHERE OrderID = 1000

20. Open a new query window, and type the following statements:

     USE tempdb ;

      GO

      BEGIN TRAN

      UPDATE Orders

   SET Amount = 350

    WHERE OrderID = 1000

      WAITFOR DELAY '00:00:10'

       UPDATE Employee

     SET EmployeeName = 'Aeon Flux'

WHERE EmployeeID = 69

21. run the query, and then switch to the first query to run it. it shoshould create a deadlock. in one of the query windows you will get an error message "Msg 1205, Level 13, State 45, Line 6 Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. rerun the transaction."

22. Switch to System Monitor, and stop the Deadlock log. (Click the Counter Logs node, right-click Deadlock Log, and select Stop from the context menu .)

23. Switch to SQL Server Profiler, stop the trace, and then close it.

24. From the File menu of SQL Server Profiler, select Open, and then click Trace File. Open the trace file you just created (C: "Correlation Trace. trc ).

25. From the File menu, select Import Performance Data, and select the log file recorded previusly (the default path and name are C: "PerfLogs" Deadlock log_000001.csv ).

26. In the Performance Counters Limit Dialog window, check the instance name of your server.

27. you can play with the pointer both from the trace and from the Performance Data window to get a feel for the tool. you can go directly to the maximum value of the counter (the value 1 in this case) by right-clicking the counter (you have just one counter) and selecting Go to Max Value.