SQL Server Hogs 100% CPU

[Abbas]

I am running a t-sql script which makes a bunch of calls to stored
procedures, updates few tables and inserts records. I do this in Begin
Tran and commit. The problem I am having is that SQL Server hogs 100%
of cpu. This degrades the performance of other applications obviously.
The memory it uses remains flat at about less than half of total
memory. Is there any optimization required? Or if there is any script
to be run on SQL Server before running the t-sql script? Any help
appreciated.

Environment details:
OS: Windows 2000
SQL Server version: 2000
Memory: 1 GB
CPU: Intel P4 2.00 GHz

Thanks.

[Erland Sommarskog]

Abbas (ab********@yahoo.com) writes:

I am running a t-sql script which makes a bunch of calls to stored
procedures, updates few tables and inserts records. I do this in Begin
Tran and commit. The problem I am having is that SQL Server hogs 100%
of cpu. This degrades the performance of other applications obviously.
The memory it uses remains flat at about less than half of total
memory. Is there any optimization required? Or if there is any script
to be run on SQL Server before running the t-sql script? Any help
appreciated.

It looks like you need to first find out exactly where the script
things go bad. It is likely to be one or two statements that are
the culprit.

Once you have tracked them down, you need investigate what indexes
you can add, or if you can rewrite the query in some way.

A good tool for finding the troublesome statemens is the SQL Profiler.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp