Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 3, 2000

Monitoring Performance With SQL Server Profiler

By Maxim Smirnov

Monitoring performance of the stored procedures and ad-hoc request running in production databases can help you in early identification of the possible system overload problems.

 Usually, analyzing simple counters like procedure duration and number of reads performed by database engine is enough to identify procedures or queries that create unnecessary overload for the system. Usually this is happening due to a number of reasons such as logical errors in the code, pour database design, incorrect indexing strategy, etc.

 There are a number of sophisticated monitoring tools on the market that help you to get any statistics and also will determine possible drawbacks in the system for you, but from my point of view, the most effective way is the simplest one. Microsoft SQL Profiler designed to collect all information you need for analysis. The best thing about SQL Profiler - it's able to store collected information (not just counters, but actual statements) into the database table. And after all, it's a simple and very friendly tool to work with.

SQL Profiler setup 

Start SQL Profiler and from FILE menu select NEW and then select TRACE. You will be prompted to provide the information about the SQL Server you want to monitor. Select server name, fill out login and password fields and then click OK.

 On the new screen, name the new trace as TRACE1. Check the box "Save to table" -- you will be prompted to select SQL Server, database and table where you want to store collected information. It is a very good idea to use separate SQL Server (for example the desktop version you have installed on your workstation) in order to avoid performance hit due to sequential inserts into storage table.

 On the EVENTS tab first remove all counters from the right window and then select all collections of counters for STORED PROCEDURES and TSQL. If you want to monitor any additional events, feel free to add them as required.

 On the filters tab select parameter OBJECTID from the tree and check the flag "Exclude system objects".  

Now, go back to the GENERAL tab and hit button RUN. SQL Profiler starts to collect information about all procedures and ad-hoc queries running on SQL Server. Let it run for a few hours during operation time and now you are ready for the analysis. 

Data analysis 

There are two main counters you want to keep track of in your system: READS and DURATIOIN. For each completed procedure or query SQL Profiler will capture this information along with the statement, user id and other parameters.

 Before running queries I suggest to create indexes for columns DURATION and READS in the storage table (TRACE1). This will dramatically speed up your analysis.

             CREATE NONCLUSTERED INDEX IND_TRACE_1 ON dbo.TRACE1(Duration)
                GO

CREATE NONCLUSTERED INDEX IND_TRACE_2 ON dbo.TRACE1(Reads)
               
GO

First thing you might be interested in is identifying long running procedures and queries. The following query will identify 20 procedures, which take the most of the time to complete:

 SELECT * FROM TRACE WHERE Duration IN
(SELECT DISTINCT TOP 20 Duration
FROM TRACE ORDER BY Duration DESC)

In order to understand why procedure takes a long time to finish, take a look on parameters READS.

 When the value of READS is low, then probably the required resources (e.g. tables or views) were locked by another process. This indicates possible locking problems and you might want to take a look on the procedures that use same objects.

 High READS might indicate table scans or not optimal indexes chosen by query optimizer. Usually, you want to keep READS parameter as low as possible because operations related to accessing hard drives (reading and writing) are the slowest ones in the system.

 Paste the statement you want to check into the Query Analyzer and select Show Execution Plan command from Query menu. Run the statement and tale a look at the query plan. It will show you if any table or index scans occur during the execution. Table scans can be fixed by applying proper index to the columns participated in the WHERE clause or joins. Index scans are much faster then table scans and some times can not be avoided, but when clustered index scan occur you must check it the columns you use in WHERE clause or joins are in the clustered index and if this is not the case, then creation of the separate non clustered index for this columns will boost query performance and decrease READS volume.

 Statements that uses data a lot (READS is high) but do not take too much time to complete can by identified using the similar query:

SELECT * FROM TRACE WHERE Reads IN
(SELECT DISTINCT TOP 20 Reads
FROM TRACE ORDER BY Duration DESC)

 

Use the same technique to find out if large READS volume caused by unnecessary scan operations.  I hope this information will help you and wish you good luck in your research



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date