Field duration in Sql profiler

Bgestel

Hi sql guys and gals,

i'm stil profiling and have a little questing

SELECT TOP 1 * FROM "SSC W1500SP0 SI1170 Performance Test"."dbo"."Performance Test Company$G_L Entry" WITH (UPDLOCK, ROWLOCK)   ORDER BY "Entry No_" DESC

is a query that takes a long time, which i find strange. SQL is only going for 1 record , the first , isn't it?

So my question is , does the duration of a query include the wait time for a lock ?

stryk

Could be. "Duration" gives the time the SQL Server spent to process the query. "CPU" give the time spent on the processors.
Normally "Duration" and "CPU" are quite the same (+-); so if your "CPU" time is remarkably shorter than "Duration" this basically indicates a block ...

But you should also look at the "Reads". With standard NAV - Entry No. = PK & CI - the query should be executed as a "Clustered Index Seek" which should take just a few "Reads" (depending on the number of records, but less than 100 anyway).
If you have too many "Reads" this would also indicate an Indexing problem ...

P.S.: This query is caused by this algorithm, which you could find e.g. in CU 80:

GLEntry.LOCKTABLE;
GLEntry.FINDLAST;

It's getting the first record of the table in descending order.
And you have the "Always Rowlock" feature enable, which IMHO you shouldn't ...

Nobody

Are you running this query on SQL 2000 or 2005? There is a bug in SQL 2000 when you look at a clustered index in reverse order it would do a complete and VERY costly clustered index scan to find the record(Entry No. is the clustered index on GL Entry and by default it is in ASC order not DESC) If I remeber correctly it was fixed in SP4 for SQL 2000.

This issue does not exist in SQL 2005, and to answer your question "Yes, the wait time is included in the Duration calculation of the query"

stryk

Nobody wrote:

... it would do a complete and VERY costly clustered index scan to find the record...

And the first "indicator" to see this behaviour would be the "Reads" in the Profiler. Normally you should have mostly values less than 100. If a Clustered Index Scan on "G/L Entry" is performed, then you will have thousands of "Reads" ...