SQLTeam.com | Weblogs | Forums

Query Optimization

Hi All,

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

(100 rows affected)
Table 'Category'. Scan count 0, logical reads 200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table Organization. Scan count 0, logical reads 200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Department'. Scan count 1, logical reads 3878, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Status'. Scan count 0, logical reads 200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Request'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 30 ms.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 31 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Is that Mean Department with logical reads 3878 root that giving issue on performance? How can I get a very optimize speed of query execution, any idea?

Execution Plan
Query Cost (relative to batch) : 100%

Select Cost 0% <- Compute Scalar Cost 0% <- Top Cost 0% <- Nested Loops (Left Outer Join) Cost 0 % <- 1. Nested Loops (Left Outer Join) Cost 0%

<- 2. Clustered Index Seek (Clustered) [Category].[PK_Category] [Category] Cost 11%

Expanded from <- 1. Nested Loops (Left Outer Join) Cost 0%

a. <- Nested Loops (Left Outer Join) Cost 14% <- Nested Loop (Left Outer Join) Cost 0%
i. <- Filter Cost 1% <- Clustered Index Scan (Clustered) [Request].[PK_Request] [Request] Cost 10%

ii. <- Clustered Index Seek (Clustered) [Status].[PK_Status] [Status] Cost 11%

b. <- Clustered Index Seek (Clustered) [Department].[PK_Department] [Department] Cost 29%

Thanks.

Regards,
Micheale

hi

please google search ..hope this helps ... :slight_smile: :slight_smile:
LOTS of articles on how to

Also... you've got a query that executes in 31ms, is your time really best served trying to shave milliseconds off this query; unless this query is executing many times a second you might be best served looking for another query to optimise....

when the query engine needs to read data that is a logical read. It will looks into memory but if the page is already in SQL Server's memory, then it uses that. If it can't find it in memory, then that triggers a physical read and the data page is read from disk. fixing missing indexes may resolve this issue.