Query Performance
From: Ramnadh (Ramnadh_at_discussions.microsoft.com)
Date: 11/15/04
- Next message: Vishal Parkar: "Re: SQL statement to get all tables in the database"
- Previous message: Itzik Ben-Gan: "Re: Leftmost column in an index"
- Next in thread: avnrao: "Re: Query Performance"
- Reply: avnrao: "Re: Query Performance"
- Reply: Uri Dimant: "Re: Query Performance"
- Reply: Gert-Jan Strik: "Re: Query Performance"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 15 Nov 2004 01:49:13 -0800
Hi,
This is my query execution plan which i got when executing the query and the
statistics while executing the query
|--Compute Scalar(DEFINE:([Expr1006]=If ([MSGS].[SenderId]=-1) then
'Publisher' else ([Resource].[FirstName]+' '+[Resource].[LastName])))
|--Sort(ORDER BY:([MSGS].[SentOn] DESC))
|--Merge Join(Right Outer Join,
MERGE:([T].[Id])=([MSGS].[PriorityTypeId]),
RESIDUAL:([MSGS].[PriorityTypeId]=[T].[Id]))
|--Clustered Index
Scan(OBJECT:([Viper_Onsite].[dbo].[Type].[PK_Type] AS [T]), ORDERED FORWARD)
|--Sort(ORDER BY:([MSGS].[PriorityTypeId] ASC))
|--Merge Join(Right Outer Join,
MERGE:([Resource].[Id])=([MSGS].[SenderId]),
RESIDUAL:([MSGS].[SenderId]=[Resource].[Id]))
|--Clustered Index
Scan(OBJECT:([Viper_Onsite].[dbo].[Resource].[PK_Resource]), ORDERED FORWARD)
|--Sort(ORDER BY:([MSGS].[SenderId] ASC))
|--Hash Match(Inner Join,
HASH:([TP].[Id])=([MSGS].[TypeId]))
|--Clustered Index
Scan(OBJECT:([Viper_Onsite].[dbo].[Type].[PK_Type] AS [TP]))
|--Merge Join(Inner Join,
MERGE:([MSGS].[Id])=([MSGR].[MessageId]),
RESIDUAL:([MSGR].[MessageId]=[MSGS].[Id]))
|--Clustered Index
Scan(OBJECT:([Viper_Onsite].[dbo].[Message].[PK_Message] AS [MSGS]), ORDERED
FORWARD)
|--Clustered Index
Scan(OBJECT:([Viper_Onsite].[dbo].[MessageRecipient].[IX_MessageRecipient_MessageId]
AS [MSGR]), WHERE:(([MSGR].[RecipientId]=[@recipientId] AND
[MSGR].[MessageState]<>'PD') AND [MSGR].[Mes
Statitstics:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'MessageRecipient'. Scan count 1, logical reads 577, physical reads 0,
read-ahead reads 536.
Table 'Message'. Scan count 1, logical reads 3792, physical reads 0,
read-ahead reads 3576.
Table 'Type'. Scan count 2, logical reads 8, physical reads 0, read-ahead
reads 0.
Table 'Resource'. Scan count 1, logical reads 39, physical reads 0,
read-ahead reads 40.
SQL Server Execution Times:
CPU time = 451 ms, elapsed time = 5673 ms.
Table 'MessageRecipient'. Scan count 1, logical reads 6010, physical reads
0, read-ahead reads 5.
SQL Server Execution Times:
CPU time = 60 ms, elapsed time = 104 ms.
SQL Server Execution Times:
CPU time = 60 ms, elapsed time = 105 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
I doesn't understand exactly what are the logical reads. Can anyone explain
what the logical reads are ?
and also it is showing that elapsed time is more than 5000 ms. What is the
elapsed time ?
As i am new to sql server.. i doesn't understand the execution plan properly.
Can anyone help me out....
- Next message: Vishal Parkar: "Re: SQL statement to get all tables in the database"
- Previous message: Itzik Ben-Gan: "Re: Leftmost column in an index"
- Next in thread: avnrao: "Re: Query Performance"
- Reply: avnrao: "Re: Query Performance"
- Reply: Uri Dimant: "Re: Query Performance"
- Reply: Gert-Jan Strik: "Re: Query Performance"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
