(I had posted this in another group that I realized is probably not the
right one).

I have a fairly complex SELECT statement.  It is running on a server
with 8 CPUs and 8GB RAM (using about 6.5GB).  The statistics that I get
when I run it are below.  It is not the first time that I ran it so the
execution plan already exists and it looks like the data is all in RAM
which is fine.

It is taking over 7 seconds to run and using about 1/2 sec of CPU time.
I'm in Performance Monitor watching the 8 CPUs and none of them are
near 100% - the entire CPU of the system gets to about 10-15% while
this is running.  The hard drives are not the bottleneck - they barely
budge during the execution.  Any ideas why it might be taking so long?
What else should I look at?


If the real time is 7.5 seconds and it is consuming .5 CPU seconds,
what is happening during the other 7 seconds.  Normally, I would expect
it to be disk I/O but I am pretty sure that is not happening in this
case.


Thanks,
Mark


SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.


(1242 row(s) affected)


Table 'Worktable'. Scan count 1239, logical reads 1249, physical reads
0, read-ahead reads 0.
Table 'Worktable'. Scan count 1241, logical reads 1249, physical reads
0, read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tbFirmClient'. Scan count 1, logical reads 18, physical reads 0,

read-ahead reads 0.
Table 'tbFirm'. Scan count 1, logical reads 2671, physical reads 0,
read-ahead reads 0.
Table 'tbFirmStatisticSuitability'. Scan count 0, logical reads 0,
physical reads 0, read-ahead reads 0.
Table 'tbSecurity'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tbStatistic'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tbSuitabilityStudy'. Scan count 4, logical reads 12, physical
reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tbAltpurchpower'. Scan count 784, logical reads 2287, physical
reads 0, read-ahead reads 0.
Table 'tbFirmScore'. Scan count 1243, logical reads 12644, physical
reads 0, read-ahead reads 0.
Table 'tbPosition'. Scan count 1243, logical reads 11966, physical
reads 0, read-ahead reads 0.
Table 'tbAddressPhone'. Scan count 2480, logical reads 12099, physical
reads 0, read-ahead reads 0.
Table 'tbRegion'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0.
Table 'tbCountry'. Scan count 1240, logical reads 2480, physical reads
0, read-ahead reads 0.
Table 'tbState'. Scan count 824, logical reads 1648, physical reads 0,
read-ahead reads 0.
Table 'tbCity'. Scan count 1240, logical reads 2487, physical reads 0,
read-ahead reads 0.
Table 'tbAddress'. Scan count 1242, logical reads 15080, physical reads

0, read-ahead reads 0.
Table 'tbTurnoverRating'. Scan count 1, logical reads 2, physical reads

0, read-ahead reads 0.
Table '#04BDFF0F'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tbIRXGroup'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0.
Table 'tbIRXLogin'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0.
Table 'tbFirmType'. Scan count 2, logical reads 82, physical reads 0,
read-ahead reads 0.
Table 'tbInvestmentStyle'. Scan count 2, logical reads 4, physical
reads 0, read-ahead reads 0.
Table 'tbMediaFocus'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0.
Table 'tbMediaFrequency'. Scan count 1, logical reads 2, physical reads

0, read-ahead reads 0.
Table 'tbFirmSuperType'. Scan count 1, logical reads 2, physical reads
0, read-ahead reads 0.


SQL Server Execution Times:
   CPU time = 469 ms,  elapsed time = 7442 ms

(mar***@comcast.net) writes:
Maybe a stupid check: but is the server idle when you run these tests?
Else there may be issue with blocking or just other processes stealing
resources.

column dataType
Joining 2 select statement with like fields
Applying a function to a subquery
Query from a dynamic table
Tuning