Physical IO and Logical IO

Question

Physical IO and Logical IO

karthik M

One Orange Chip

Points: 29139
More actions
August 31, 2007 at 8:43 am

#120367

Hi Experts,

In my recent interview,I have faced some questions,
1. Tell me about Physical read and logical read and give me an example.
which value would be considered during performance tuning ?
2. How to find the number of rows in a table without using count() function ?
Note : Table doesn't have any indexes
3. Do we Call a View in a procedure ? if yes ,will it raise cartesian product or not ?
4. what is mean by join transitive closure ? how to set it ?
5. I have dropped a table from the database ,but ,when you execute sp_help,
it shows you the table. what is the reason ?

Can any expert give me the answers ?
Regards
Karthik

karthik

Viewing 14 posts - 1 through 13 (of 13 total)

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

I believe I am going to fail. Now, try to be a pseudo expert and answer 3 questions.

2. You can always find the rowcount from sysindexes in each database, regardless whether or not a table has any index on it.

3. A procedure is a bunch of SQL commands. In this sense, we can say a view is a procedure.

4. Refer to the link:

http://db.apache.org/derby/docs/10.2/tuning/rtuntransform866547.html

John Rowan SSC Guru Points: 56440 More actions · Answer 2

As far as #1 goes, when performance tuning a query, logical and physical reads can be viewed by using SET STATISTICS IO ON. Physical reads represent how many pages SQL Server had to read from disk in order to satisfy the query. Logical reads represent how many pages SQL Server had to read from the cache to satisfy the query. For performance tuning, always look at the Logical reads as this will be the true number for how many pages SQL Server has to go through to retrieve the data because even pages that were read off disk have to be placed in cache and then read from there. Whether or not the data is in cache does not matter here.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Be carefull of this one

2. You can always find the rowcount from sysindexes in each database, regardless whether or not a table has any index on it.

Many people overlook this but

DBCC SHOWCONTIG (<table_name>,0) WITH TABLERESULTS

There is a column in the output of "Rows" which contains the count of the records in the index or total if heap, if a clustered index it will be the entire count as well.

From BOL

Number of data or index records at that level of the index. For a heap, this is the number of data records in the entire heap.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Confused by the wording of this one

3. Do we Call a View in a procedure ? if yes ,will it raise cartesian product or not ?

A view is used like a table and not called per se in a procedure (calling is a weird term to use at all). As to whether it will show a cartesian product or not the answer is "Not Enough Information to Determine that Fact." depends on what the view does or what else you are doing with it.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Again not sure based on the wording what they are truely asking

5. I have dropped a table from the database ,but ,when you execute sp_help, it shows you the table. what is the reason ?

I have never seen this happen, could I guess happen thou if you ran a drop again the wrong database, or the process of dropping failed and the system retained but I have never been able to produce this effect otherwise. Could I just answer "ID10T error, user did something wrong"?

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Antares,

5. I have dropped a table from the database ,but ,when you execute sp_help, it shows you the table. what is the reason ?

I realized the problem, reason is transaction is not committed, so that i saw when i execute sp_help.

My question is

Shall we call a View from a stored procedure ?

say for example,

Create Procedure P1

as

begin

.

select * from V1 --> View

.

End

Will it produce cartesian product ?

I would like to say thanks to all those experts who has given answer to my questions.

Regards

Karthik

karthik

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

Well, interview questions can be... 'funny' in many ways...

In answer to 5) - which is actually two questions in one..?

(and I agree that the wording is vague at best..)

Yes, it's perfectly ok to use a SELECT .. FROM myView inside a stored procedure.

Will the view produce a cartesian product? Maybe. It depends.

It has otoh nothing to do with if it's inside a proc or not.

If the view will produce a cartesian product depends on how the view is written.

If it does, then it will do so alone, or in a proc, it doesn't matter.

(so, the question is 'stupid'..)

...another viable answer could be - 'It depends'...

/Kenneth

SimonLiew SSCrazy Eights Points: 8304 More actions · Answer 8

I guess i'm trying to summarise the answers here. i'd reckon the answers could vary.

1. Tell me about Physical read and logical read and give me an example.

which value would be considered during performance tuning ?

use logical reads for performance tuning as this would not change. sql would not re-read the same data pages when it already exists in the buffer pool (unless pages read are dirty)

2. How to find the number of rows in a table without using count() function ?

Note : Table doesn't have any indexes

select object_name(id), rows from sysindexes where indid in (0, 1)

=> you're getting a table rowcount where indid = 0 is heap and 1 is clustered. so, this'll give you the table rowcount. but catch is you have to run DBCC UPDATEUSAGE('db') first so that you get the correct value instead of a possible outdated count.

Or, you can go to EM and double-click the table and you can also get the rowcount there.

3. Do we Call a View in a procedure ? if yes ,will it raise cartesian product or not ?

you may call view in SP, but cartesian product is depending on how you do your join in the view. cartesian product is produced by "CROSS JOIN" keyword in t-sql

4. what is mean by join transitive closure ? how to set it ?

i dont know the proper definition as in a dictionary, but in real life, join transitive are the equal signs that you use in your join (highlighted in red)

i.e. select xxx from table1 join table2 on col1 = col2

on top of this, the join transitive closure also means to provide additional filter so that sql can have more accurate estimate of the result

i.e. select xxx from table1 join table2 on col1 = col2 and col1 = 'xxx'

5. I have dropped a table from the database ,but ,when you execute sp_help,

it shows you the table. what is the reason ?

it could be because you've got 2 same table name but different owner. and sp_help picks up the "other" table.

Very interesting questions. Could be this interviewer has Sybase background too?

Simon

Simon Liew
Microsoft Certified Master: SQL Server 2008

karthik M One Orange Chip Points: 29139 More actions · Answer 9

Thanks a lot to give answer for all the questions !

Yes Simon , You are Correct ! They have Sybase BackGroud.How you find it ?

Regards

Karthik

karthik

SimonLiew SSCrazy Eights Points: 8304 More actions · Answer 10

Hi Karthik,

I was just guessing that they're from Sybase background because 2 of their questions catches my eyes.

Their 3rd question on "cartesian product" in view is not a typical question that would be asked by a pure MSSQL DBA, even myself. Its simply because in MSSQL, we interpret the execution plan differently from Sybase. I.e. SELECT xxx WHERE yy in ('a', 'b', 'c') in Sybase is inefficient and looks like a cartesian product whereas in MSSQL, its not an issue. Therefore, most Sybase DBA would avoid this syntax especially on large tables and put all values into a temp table and then do a proper join. MSSQL DBA normally doesnt put much notice when using the "IN" clause.

This leads to question 4 where the interviewer was interested to know the definition of transitive join closure. A Sybase old school would be more likely to use terms such as transitive join because Sybase has an option to enable transitive closure at session level. Although MSSQL DBA should also know the proper definition of transitive join closure (this term should be in one of your IT Degree courses), but normally, they would be more interested to know merge join, hash join, subquery, etc (Terms more often used in MSSQL).

I didnt have much exposure in Sybase but has worked with the product for a period of time. Therefore, had a brief encounter with DBAs from different platform.

My 2 cents.

Simon

Simon Liew
Microsoft Certified Master: SQL Server 2008

karthik M One Orange Chip Points: 29139 More actions · Answer 11

Thanks Simon !

Can you expalin about Merger Join and Hash Join ?

Regards

Karthik

karthik

SimonLiew SSCrazy Eights Points: 8304 More actions · Answer 12

Karthik,

Based on my experience, giving a definition for hash join, merge join, nested loop join isnt usually very hard. but if you really wanted to impress your interviewer, you should give them the definition, plus a deep understanding on how the 3 joins differs and applied in practical and in terms of sql architecture.

http://www.sqlservercentral.com/columnists/RDyess/optimizerjoinmethods.asp

Do you know when tuning t-sql, by changing the way how you code your t-sql, you can also change the join type sql uses. if possible, give them a real life scenario on how you've properly used your understanding on optimising queries.

I reckon the article on the link above explains the 3 joins pretty well.

Simon

Simon Liew
Microsoft Certified Master: SQL Server 2008

karthik M One Orange Chip Points: 29139 More actions · Answer 13

Experts,

Thanks a lot for your proper answers !

Karthik

karthik