Is there Any Differeence ?

Question

Is there Any Differeence ?

Bhuvnesh

SSC Guru

Points: 59351
More actions
August 20, 2008 at 12:49 am

#211352

Hi
i m new in administration....
please run thi script....
set statistics profile on
exec sp_helpserver
set statistics profile off
And explain following terms...how they should be analyzed?
--PhysicalOp
--logicalOp
--estimatedIO
--EstimateCPU
--AvgRowSize
And one more this Is there any diff between Index Scan And Index Seek ?
And how Sqwl optimizer go for these ???
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Atif Hall of Fame Points: 3578 More actions · Answer 1

Index scan is same as table scan that it scans all values. It usually occurs when optimizer determines that it will be quicker to simply scan all values in index rather to use the keys provided by index. You may fine tune the where clause to avoid index scan.

Index seek is used when optimizer determines that it will find all values through index. it just walks between rows and gets values. Key values are used to quickly identify the location. It provides best performance.

DBDigger Microsoft Data Platform Consultancy.

Atif Hall of Fame Points: 3578 More actions · Answer 2

You may use following query to analyze all these aspects

select hostname, program_name, loginame, cpu, physical_io, memusage, login_time, last_batch, status

from master.dbo.sysprocesses

order by hostname

Further modify the query according to requirement and u may provide dbid to analyze a specific db.

DBDigger Microsoft Data Platform Consultancy.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

thanks............now please see this also

please run thi script....

set statistics profile on

exec sp_helpserver

set statistics profile off

And explain following terms...how they should be analyzed?

--PhysicalOp

--logicalOp

--estimatedIO

--EstimateCPU

--AvgRowSize

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

arjun.tewari Ten Centuries Points: 1013 More actions · Answer 4

PhisycalOp referes to Physical Operations. Physical operators displayed in red indicate that the query optimizer has issued a warning, such as missing column statistics which is turn forces the optimizer to choose some less effective execution plan. Example of Physicalop: Nested Loop

The logical operator that matches the physical operator. The logical operator, if different from the physical operator, is listed after the physical operator at the top of the ToolTip and separated by a forward slash. Example: Join operator

The estimatedIO cost of all I/O activity for the operation. This value should be as low as possible.

The estimated cost for all CPU activity (CPU Cost) for the operation.

The estimated size of the row output by the operator.

INDEX SCAN: The Index Scan logical and physical operator retrieves all rows from the nonclustered index specified in the Argument column. If an optional WHERE:() predicate appears in the Argument column, only those rows that satisfy the predicate are returned.

The Index Seek logical and physical operator uses the seeking ability of indexes to retrieve rows from a nonclustered index and retrieves only the rows that satisfy the SEEK condition.

I hope you are clear now.:)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

For info on execution plans, there's a great book available from redgate (free when you download an evaluation of any of their tools) on reading execution plans.

Or you can have a look at these blog posts for a series (unfinished) on reading execution plans - http://sqlinthewild.co.za/index.php/category/sql-server/execution-plans/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Atif Hall of Fame Points: 3578 More actions · Answer 6

This procedure reports about a particular remote or replication server, or about all servers of both types. Provides the server name, the server’s network name, the server’s replication status, and the server’s identification number.

please use it as

USE [master]

exec sp_helpserver

or to get info about a specific server

USE [master]

exec sp_helpserver 'server name'

DBDigger Microsoft Data Platform Consultancy.

Atif Hall of Fame Points: 3578 More actions · Answer 7

statistics profile displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, triggers, and stored procedures.

DBDigger Microsoft Data Platform Consultancy.