November 19, 2007 at 1:24 pm
I am facing an issue with one of SQL server instance running on a windows machine which has 4 processors, and the SQL server 2000 instances are configured to utilize all the processors and memory on that box,
now what is happening i am running following query
SET NOCOUNT ON
UPDATE IKB_ODS.dbo.WALMART_POS_STAGING
SET END_DT= NULL
WHERE END_DT IS NOT NULL
It will suppose to update 25 million rows
and when i check in the windows tasks manager and getting seeing that the CPU utilization for that particular box only jumps to 25% only and is not utilizing the full processor capacity while there is no other process running on that instance and box as well due to this reason the query is taking way much longer time, i have checked the SQL server is configured to use all the processors and memory so there is no restriction on it
i did some thing else as well i ran this query against the same database and SQL server instance by using the Informatica and it ran way much faster also the CPU utilization jumps to 75 to 80 % and the query completed way much faster.
I am wondering why when i run the same query through Query Analyzer and OSQL command prompt it takes longer time and the CPU utilization stays at 25% while the same query doing the same thing ran faster through Informatica and the CPU utlization increased as well.
any help in this regards will be higly appreciated
thanks
November 19, 2007 at 2:17 pm
The best place to start is to look at the execution plans for the two queries. You can run profiler and see what is happening in each case. The query optimizer in MS SQL Server takes a lot of information into account, so it can be very difficult to determine when it will enable a process to multi-thread enough to use all four processors at the same time.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply