Abbas (abbasrazam@yahoo.com) writes:[color=blue]
> 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.[/color]
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,
sommar@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
