Possible Causes

Further Investigation

Stack Analyzer

1. Start Control Suite, choose Other Tools > Stack Analyzer > Launch.
2. Use your web browser to view the site in question.
3. Switch back to Stack Analyzer and click on the application pool of the site so traces for that site will be run.
4. Stack Analyzer lists all of the threads being run.

Things to look for include:

• Patterns - The bottom of the stack trace should not change and the top should change frequently. In the middle is an invisible line that indicates where the code is "stuck" and not performing properly. When clicking on that code Parameters and Local Variables can be displayed.
• SNI methods - Traces marked with SQL Native Interface indicate code that's accessing the database. They can indicate a long time to execute a stored procedure or an excessive amount of data being returned from the database.
• ASP.net code is often at the bottom, above that you will see DotNetNuke code, then other .net code above that. If you are suspicious about your code calling the database, you will need to investigate the SQL related code.

• Basic Trace Mode:

1. Start Control Suite, choose Other Tools > SQL Analyzer > Launch.
2. Click on File > New Trace.
3. Connect to (Local) server using Windows Authentication.
4. Click on the Events tab. Two items to check when beginning your analysis are RPC Started and RPC Completed. Click on Run.
5. Visit the website in question and it will list every stored procedure that was run. Look at the volume of commands that are run. If hundreds of commands fill you screen, it indicates too many tasks are being called and this could potentially be done more efficiently. Also look for expensive commands with high readings in reads, writes, or CPU cycles.

• Performance Dashboard

1. Click on File > New Performance Dashboard.
2. Connect to (Local) server using Windows Authentication.

This second feature of SQL Analyzer queries SQL server for a visual representation of how the web site is performing.  It tells how many user sessions are using the database. It also indicates "expensive queries".  They are categorized by CPU, Logical Reads, Logical Writes, Duration, Physical Reads and CLR time. Clicking on one of the categories will show queries with the highest value in a bar graph representation. The queries are listed, the number of times it has been executed and other information.

• Application Dashboard

1. Click on File > New Application Dashboard.
2. Connect to (Local) server using Windows Authentication.

Gives a drill-down view into what SQL server is doing. It groups and aggregates the data, making it easier to digest. Select the server, click on the web site in question, and it indicates the number of connections to the database. A large number of these listing may indicate code that's not properly releasing connections to the database or that you have a lot of users hitting the site, requesting information from the database.

Expanding a connection shows the commands that were performed within that connection. Look for the large numbers. Large numbers indicate potential data caching issues or a broken module.