February 6, 2014 at 1:02 pm
SELECT r.id
FROM dbo.Rag r
where HIrid = 'test'
HIrid (nvarchar(50),null)
Gid (int,null)
Pk,cluster Index - ID (int)
Index 1 - Key column HIrid
Index 2 - Key columns HIrid,Gid
My question is when i use Index 2 Vs Index 1 i see more number of physical reads(same number of logical reads though), though overall execution time is pretty close. Can someone tell me why there are more physical reads and same number of logical reads?
February 6, 2014 at 1:07 pm
How are you choosing index 2 over index1?
Why would you choose index 2 over index1?
What happens when you run each query two or three times?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 6, 2014 at 1:16 pm
ChrisM@home (2/6/2014)
How are you choosing index 2 over index1?Dropping index 1 and creating Index 2.
Why would you choose index 2 over index1?
I am not, i just asked a question based on my observation
What happens when you run each query two or three times?
Why?
February 6, 2014 at 1:23 pm
curious_sqldba (2/6/2014)
ChrisM@home (2/6/2014)
How are you choosing index 2 over index1?Dropping index 1 and creating Index 2.
Why would you choose index 2 over index1?
I am not, i just asked a question based on my observation
What happens when you run each query two or three times?
Why?
Because the first time you run a query, the data may not all be in cache - and you're comparing apples with beetle skeletons.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 6, 2014 at 1:24 pm
ChrisM@home (2/6/2014)
curious_sqldba (2/6/2014)
ChrisM@home (2/6/2014)
How are you choosing index 2 over index1?Dropping index 1 and creating Index 2.
Why would you choose index 2 over index1?
I am not, i just asked a question based on my observation
What happens when you run each query two or three times?
Why?
Because the first time you run a query, the data may not all be in cache - and you're comparing apples with beetle skeletons.
Oh,...yeah i cleared buffer and cache for each time......in either case i would assume Physical reads should be 0 if it is cached...
February 6, 2014 at 1:29 pm
curious_sqldba (2/6/2014)
ChrisM@home (2/6/2014)
curious_sqldba (2/6/2014)
ChrisM@home (2/6/2014)
How are you choosing index 2 over index1?Dropping index 1 and creating Index 2.
Why would you choose index 2 over index1?
I am not, i just asked a question based on my observation
What happens when you run each query two or three times?
Why?
Because the first time you run a query, the data may not all be in cache - and you're comparing apples with beetle skeletons.
Oh,...yeah i cleared buffer and cache for each time......in either case i would assume Physical reads should be 0 if it is cached...
Why did you do that? Wouldn't it be easier just to run the query twice? It's less typing!
So after you cleared a buffer and a cache (nobody knows yet how, which or why), what did you observe?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 6, 2014 at 1:43 pm
ChrisM@home (2/6/2014)
curious_sqldba (2/6/2014)
ChrisM@home (2/6/2014)
curious_sqldba (2/6/2014)
ChrisM@home (2/6/2014)
How are you choosing index 2 over index1?Dropping index 1 and creating Index 2.
Why would you choose index 2 over index1?
I am not, i just asked a question based on my observation
What happens when you run each query two or three times?
Why?
Because the first time you run a query, the data may not all be in cache - and you're comparing apples with beetle skeletons.
Oh,...yeah i cleared buffer and cache for each time......in either case i would assume Physical reads should be 0 if it is cached...
Why did you do that? Wouldn't it be easier just to run the query twice? It's less typing!
I wanted to be very sure that it makes use of correct index. I didn't have to type (copy/pasted 😉 )
[/quote]
So after you cleared a buffer and a cache (nobody knows yet how, which or why), what did you observe?[/quote]
As i said in my original post....physical reads are different...logical reads are same
February 6, 2014 at 1:51 pm
Hot cache and you are still getting physical reads after running your query multiple times...How many rows in your table?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply