I was wondering if somebody could critique my query tuning technique.
I am primarily a developer and only a part time dba. I was trained by
a great dba, but it was back in the sql 6.5 and 7.0 days. I
concentrate pretty heavily on logical reads, and I am wondering if my
technique is still valid or if I should be concentrating elsewhere?
We use sql profiler and find queries that are taking longer than 15
seconds to run. I get the sql and run it in query analyzer and turn
statistics io on and look at logical reads. I keep tuning the sql and
try to get logical reads down. My goal is to get them to less than
10,000 logical reads. But if I get a query from 4,000,000 read to
200,000, I believe I have made a good improvement.
Some of our code calls multiple sql statements in sequence, so I
analyze the code and have a pretty good idea what tables will probably
be cached. If I see that the tables will be cached because of previous
sql statements, then I can live with a query that has higher logical
reads.
I pretty much don't understand the graphical Execution Plan. Am I
crazy ignoring this? Or is my above technique pretty good?The goal is always to get the logical reads to a minimum. But there is no
way to tell if a particular # is as low as it can be without looking at the
query plan to some degree. I don't know what kind of queries you are doing
but if your only looking for queries over 15 seconds and think 10K reads is
great you guy's must have a lot of time on your hands to wait for
results<g>. Usually anything over 150ms is too long for me. Hopefully
these are some type of reporting queries and the amount of reads are
justified..
--
Andrew J. Kelly SQL MVP
"Bill Ray" <rinfo@.mail.com> wrote in message
news:63b44ea.0407020726.156a0c50@.posting.google.com...
> I was wondering if somebody could critique my query tuning technique.
> I am primarily a developer and only a part time dba. I was trained by
> a great dba, but it was back in the sql 6.5 and 7.0 days. I
> concentrate pretty heavily on logical reads, and I am wondering if my
> technique is still valid or if I should be concentrating elsewhere?
> We use sql profiler and find queries that are taking longer than 15
> seconds to run. I get the sql and run it in query analyzer and turn
> statistics io on and look at logical reads. I keep tuning the sql and
> try to get logical reads down. My goal is to get them to less than
> 10,000 logical reads. But if I get a query from 4,000,000 read to
> 200,000, I believe I have made a good improvement.
> Some of our code calls multiple sql statements in sequence, so I
> analyze the code and have a pretty good idea what tables will probably
> be cached. If I see that the tables will be cached because of previous
> sql statements, then I can live with a query that has higher logical
> reads.
> I pretty much don't understand the graphical Execution Plan. Am I
> crazy ignoring this? Or is my above technique pretty good?
Showing posts with label tuning. Show all posts
Showing posts with label tuning. Show all posts
Wednesday, March 7, 2012
Logical reads to tune sql
I was wondering if somebody could critique my query tuning technique.
I am primarily a developer and only a part time dba. I was trained by
a great dba, but it was back in the sql 6.5 and 7.0 days. I
concentrate pretty heavily on logical reads, and I am wondering if my
technique is still valid or if I should be concentrating elsewhere?
We use sql profiler and find queries that are taking longer than 15
seconds to run. I get the sql and run it in query analyzer and turn
statistics io on and look at logical reads. I keep tuning the sql and
try to get logical reads down. My goal is to get them to less than
10,000 logical reads. But if I get a query from 4,000,000 read to
200,000, I believe I have made a good improvement.
Some of our code calls multiple sql statements in sequence, so I
analyze the code and have a pretty good idea what tables will probably
be cached. If I see that the tables will be cached because of previous
sql statements, then I can live with a query that has higher logical
reads.
I pretty much don't understand the graphical Execution Plan. Am I
crazy ignoring this? Or is my above technique pretty good?
The goal is always to get the logical reads to a minimum. But there is no
way to tell if a particular # is as low as it can be without looking at the
query plan to some degree. I don't know what kind of queries you are doing
but if your only looking for queries over 15 seconds and think 10K reads is
great you guy's must have a lot of time on your hands to wait for
results<g>. Usually anything over 150ms is too long for me. Hopefully
these are some type of reporting queries and the amount of reads are
justified..
Andrew J. Kelly SQL MVP
"Bill Ray" <rinfo@.mail.com> wrote in message
news:63b44ea.0407020726.156a0c50@.posting.google.co m...
> I was wondering if somebody could critique my query tuning technique.
> I am primarily a developer and only a part time dba. I was trained by
> a great dba, but it was back in the sql 6.5 and 7.0 days. I
> concentrate pretty heavily on logical reads, and I am wondering if my
> technique is still valid or if I should be concentrating elsewhere?
> We use sql profiler and find queries that are taking longer than 15
> seconds to run. I get the sql and run it in query analyzer and turn
> statistics io on and look at logical reads. I keep tuning the sql and
> try to get logical reads down. My goal is to get them to less than
> 10,000 logical reads. But if I get a query from 4,000,000 read to
> 200,000, I believe I have made a good improvement.
> Some of our code calls multiple sql statements in sequence, so I
> analyze the code and have a pretty good idea what tables will probably
> be cached. If I see that the tables will be cached because of previous
> sql statements, then I can live with a query that has higher logical
> reads.
> I pretty much don't understand the graphical Execution Plan. Am I
> crazy ignoring this? Or is my above technique pretty good?
I am primarily a developer and only a part time dba. I was trained by
a great dba, but it was back in the sql 6.5 and 7.0 days. I
concentrate pretty heavily on logical reads, and I am wondering if my
technique is still valid or if I should be concentrating elsewhere?
We use sql profiler and find queries that are taking longer than 15
seconds to run. I get the sql and run it in query analyzer and turn
statistics io on and look at logical reads. I keep tuning the sql and
try to get logical reads down. My goal is to get them to less than
10,000 logical reads. But if I get a query from 4,000,000 read to
200,000, I believe I have made a good improvement.
Some of our code calls multiple sql statements in sequence, so I
analyze the code and have a pretty good idea what tables will probably
be cached. If I see that the tables will be cached because of previous
sql statements, then I can live with a query that has higher logical
reads.
I pretty much don't understand the graphical Execution Plan. Am I
crazy ignoring this? Or is my above technique pretty good?
The goal is always to get the logical reads to a minimum. But there is no
way to tell if a particular # is as low as it can be without looking at the
query plan to some degree. I don't know what kind of queries you are doing
but if your only looking for queries over 15 seconds and think 10K reads is
great you guy's must have a lot of time on your hands to wait for
results<g>. Usually anything over 150ms is too long for me. Hopefully
these are some type of reporting queries and the amount of reads are
justified..
Andrew J. Kelly SQL MVP
"Bill Ray" <rinfo@.mail.com> wrote in message
news:63b44ea.0407020726.156a0c50@.posting.google.co m...
> I was wondering if somebody could critique my query tuning technique.
> I am primarily a developer and only a part time dba. I was trained by
> a great dba, but it was back in the sql 6.5 and 7.0 days. I
> concentrate pretty heavily on logical reads, and I am wondering if my
> technique is still valid or if I should be concentrating elsewhere?
> We use sql profiler and find queries that are taking longer than 15
> seconds to run. I get the sql and run it in query analyzer and turn
> statistics io on and look at logical reads. I keep tuning the sql and
> try to get logical reads down. My goal is to get them to less than
> 10,000 logical reads. But if I get a query from 4,000,000 read to
> 200,000, I believe I have made a good improvement.
> Some of our code calls multiple sql statements in sequence, so I
> analyze the code and have a pretty good idea what tables will probably
> be cached. If I see that the tables will be cached because of previous
> sql statements, then I can live with a query that has higher logical
> reads.
> I pretty much don't understand the graphical Execution Plan. Am I
> crazy ignoring this? Or is my above technique pretty good?
Logical reads to tune sql
I was wondering if somebody could critique my query tuning technique.
I am primarily a developer and only a part time dba. I was trained by
a great dba, but it was back in the sql 6.5 and 7.0 days. I
concentrate pretty heavily on logical reads, and I am wondering if my
technique is still valid or if I should be concentrating elsewhere?
We use sql profiler and find queries that are taking longer than 15
seconds to run. I get the sql and run it in query analyzer and turn
statistics io on and look at logical reads. I keep tuning the sql and
try to get logical reads down. My goal is to get them to less than
10,000 logical reads. But if I get a query from 4,000,000 read to
200,000, I believe I have made a good improvement.
Some of our code calls multiple sql statements in sequence, so I
analyze the code and have a pretty good idea what tables will probably
be cached. If I see that the tables will be cached because of previous
sql statements, then I can live with a query that has higher logical
reads.
I pretty much don't understand the graphical Execution Plan. Am I
crazy ignoring this? Or is my above technique pretty good?The goal is always to get the logical reads to a minimum. But there is no
way to tell if a particular # is as low as it can be without looking at the
query plan to some degree. I don't know what kind of queries you are doing
but if your only looking for queries over 15 seconds and think 10K reads is
great you guy's must have a lot of time on your hands to wait for
results<g>. Usually anything over 150ms is too long for me. Hopefully
these are some type of reporting queries and the amount of reads are
justified..
Andrew J. Kelly SQL MVP
"Bill Ray" <rinfo@.mail.com> wrote in message
news:63b44ea.0407020726.156a0c50@.posting.google.com...
> I was wondering if somebody could critique my query tuning technique.
> I am primarily a developer and only a part time dba. I was trained by
> a great dba, but it was back in the sql 6.5 and 7.0 days. I
> concentrate pretty heavily on logical reads, and I am wondering if my
> technique is still valid or if I should be concentrating elsewhere?
> We use sql profiler and find queries that are taking longer than 15
> seconds to run. I get the sql and run it in query analyzer and turn
> statistics io on and look at logical reads. I keep tuning the sql and
> try to get logical reads down. My goal is to get them to less than
> 10,000 logical reads. But if I get a query from 4,000,000 read to
> 200,000, I believe I have made a good improvement.
> Some of our code calls multiple sql statements in sequence, so I
> analyze the code and have a pretty good idea what tables will probably
> be cached. If I see that the tables will be cached because of previous
> sql statements, then I can live with a query that has higher logical
> reads.
> I pretty much don't understand the graphical Execution Plan. Am I
> crazy ignoring this? Or is my above technique pretty good?
I am primarily a developer and only a part time dba. I was trained by
a great dba, but it was back in the sql 6.5 and 7.0 days. I
concentrate pretty heavily on logical reads, and I am wondering if my
technique is still valid or if I should be concentrating elsewhere?
We use sql profiler and find queries that are taking longer than 15
seconds to run. I get the sql and run it in query analyzer and turn
statistics io on and look at logical reads. I keep tuning the sql and
try to get logical reads down. My goal is to get them to less than
10,000 logical reads. But if I get a query from 4,000,000 read to
200,000, I believe I have made a good improvement.
Some of our code calls multiple sql statements in sequence, so I
analyze the code and have a pretty good idea what tables will probably
be cached. If I see that the tables will be cached because of previous
sql statements, then I can live with a query that has higher logical
reads.
I pretty much don't understand the graphical Execution Plan. Am I
crazy ignoring this? Or is my above technique pretty good?The goal is always to get the logical reads to a minimum. But there is no
way to tell if a particular # is as low as it can be without looking at the
query plan to some degree. I don't know what kind of queries you are doing
but if your only looking for queries over 15 seconds and think 10K reads is
great you guy's must have a lot of time on your hands to wait for
results<g>. Usually anything over 150ms is too long for me. Hopefully
these are some type of reporting queries and the amount of reads are
justified..
Andrew J. Kelly SQL MVP
"Bill Ray" <rinfo@.mail.com> wrote in message
news:63b44ea.0407020726.156a0c50@.posting.google.com...
> I was wondering if somebody could critique my query tuning technique.
> I am primarily a developer and only a part time dba. I was trained by
> a great dba, but it was back in the sql 6.5 and 7.0 days. I
> concentrate pretty heavily on logical reads, and I am wondering if my
> technique is still valid or if I should be concentrating elsewhere?
> We use sql profiler and find queries that are taking longer than 15
> seconds to run. I get the sql and run it in query analyzer and turn
> statistics io on and look at logical reads. I keep tuning the sql and
> try to get logical reads down. My goal is to get them to less than
> 10,000 logical reads. But if I get a query from 4,000,000 read to
> 200,000, I believe I have made a good improvement.
> Some of our code calls multiple sql statements in sequence, so I
> analyze the code and have a pretty good idea what tables will probably
> be cached. If I see that the tables will be cached because of previous
> sql statements, then I can live with a query that has higher logical
> reads.
> I pretty much don't understand the graphical Execution Plan. Am I
> crazy ignoring this? Or is my above technique pretty good?
Logical read and DBCC dropcleanbuffers
Hi! I post a question last week but didn't get any responses, so trying it
again.
Basically, I am in a process of tuning a query and number of logical reads
gets change after I run dbcc dropcleanbuffers.
I understand that physical reads will be different after cache is flushed
out but why logical read get changed after flushing the cache but with exact
same execution plan.
Sql 200 sp4
Hi James
The Reads reported in Profiler also includes reads performed in the
procedure cache, which is variable depending on activity levels & dbcc
commands.
Profiler's reads data is useful for zero'ing on problems, but you should use
SET STATISTICS IO ON for more accurate report on the logical reads
performed by an individual query in the Query Analyser / Management Studio.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
> Hi! I post a question last week but didn't get any responses, so trying it
> again.
> Basically, I am in a process of tuning a query and number of logical reads
> gets change after I run dbcc dropcleanbuffers.
> I understand that physical reads will be different after cache is flushed
> out but why logical read get changed after flushing the cache but with
> exact same execution plan.
> Sql 200 sp4
>
|||Hi Greg,
Thanks for the reply. I am already using Set statistics io on to get the
logical reads, its not from Profiler.
Do you think of any reason why logical read would be different for same
qyery, same dataset with exact same execution plan and only difference is I
flushed the cache with DBCC dropcleanbuffer.
Any input is greatly appreciated.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
> Hi James
> The Reads reported in Profiler also includes reads performed in the
> procedure cache, which is variable depending on activity levels & dbcc
> commands.
> Profiler's reads data is useful for zero'ing on problems, but you should
> use SET STATISTICS IO ON for more accurate report on the logical reads
> performed by an individual query in the Query Analyser / Management
> Studio.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>
|||Hi James
Not off the top unfortunately. How much difference are you seeing? Are you
seeing only small differences or large differences/
Is your scenario reproducable via a script that you could post? If so, I'd
be happy to spend some time looking into it. If not, any chance you could
post the set statistics io output & profiler showplan all event's BinaryData
output for each call?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
> Hi Greg,
> Thanks for the reply. I am already using Set statistics io on to get the
> logical reads, its not from Profiler.
> Do you think of any reason why logical read would be different for same
> qyery, same dataset with exact same execution plan and only difference is
> I flushed the cache with DBCC dropcleanbuffer.
> Any input is greatly appreciated.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Greg,
I have replied to your email address the actual query, statistics io output
and profiler trace output that you asked for. Forgive me for sending to your
email rather than entire group. I thought with all the attachment and stuff
it would be better send it to directly. Let me know if any problem with
that. Thanks once again.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5NBAjz5GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi James
> Not off the top unfortunately. How much difference are you seeing? Are you
> seeing only small differences or large differences/
> Is your scenario reproducable via a script that you could post? If so, I'd
> be happy to spend some time looking into it. If not, any chance you could
> post the set statistics io output & profiler showplan all event's
> BinaryData output for each call?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
>
again.
Basically, I am in a process of tuning a query and number of logical reads
gets change after I run dbcc dropcleanbuffers.
I understand that physical reads will be different after cache is flushed
out but why logical read get changed after flushing the cache but with exact
same execution plan.
Sql 200 sp4
Hi James
The Reads reported in Profiler also includes reads performed in the
procedure cache, which is variable depending on activity levels & dbcc
commands.
Profiler's reads data is useful for zero'ing on problems, but you should use
SET STATISTICS IO ON for more accurate report on the logical reads
performed by an individual query in the Query Analyser / Management Studio.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
> Hi! I post a question last week but didn't get any responses, so trying it
> again.
> Basically, I am in a process of tuning a query and number of logical reads
> gets change after I run dbcc dropcleanbuffers.
> I understand that physical reads will be different after cache is flushed
> out but why logical read get changed after flushing the cache but with
> exact same execution plan.
> Sql 200 sp4
>
|||Hi Greg,
Thanks for the reply. I am already using Set statistics io on to get the
logical reads, its not from Profiler.
Do you think of any reason why logical read would be different for same
qyery, same dataset with exact same execution plan and only difference is I
flushed the cache with DBCC dropcleanbuffer.
Any input is greatly appreciated.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
> Hi James
> The Reads reported in Profiler also includes reads performed in the
> procedure cache, which is variable depending on activity levels & dbcc
> commands.
> Profiler's reads data is useful for zero'ing on problems, but you should
> use SET STATISTICS IO ON for more accurate report on the logical reads
> performed by an individual query in the Query Analyser / Management
> Studio.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>
|||Hi James
Not off the top unfortunately. How much difference are you seeing? Are you
seeing only small differences or large differences/
Is your scenario reproducable via a script that you could post? If so, I'd
be happy to spend some time looking into it. If not, any chance you could
post the set statistics io output & profiler showplan all event's BinaryData
output for each call?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
> Hi Greg,
> Thanks for the reply. I am already using Set statistics io on to get the
> logical reads, its not from Profiler.
> Do you think of any reason why logical read would be different for same
> qyery, same dataset with exact same execution plan and only difference is
> I flushed the cache with DBCC dropcleanbuffer.
> Any input is greatly appreciated.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Greg,
I have replied to your email address the actual query, statistics io output
and profiler trace output that you asked for. Forgive me for sending to your
email rather than entire group. I thought with all the attachment and stuff
it would be better send it to directly. Let me know if any problem with
that. Thanks once again.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5NBAjz5GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi James
> Not off the top unfortunately. How much difference are you seeing? Are you
> seeing only small differences or large differences/
> Is your scenario reproducable via a script that you could post? If so, I'd
> be happy to spend some time looking into it. If not, any chance you could
> post the set statistics io output & profiler showplan all event's
> BinaryData output for each call?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
>
Logical read and DBCC dropcleanbuffers
Hi! I post a question last week but didn't get any responses, so trying it
again.
Basically, I am in a process of tuning a query and number of logical reads
gets change after I run dbcc dropcleanbuffers.
I understand that physical reads will be different after cache is flushed
out but why logical read get changed after flushing the cache but with exact
same execution plan.
Sql 200 sp4Hi James
The Reads reported in Profiler also includes reads performed in the
procedure cache, which is variable depending on activity levels & dbcc
commands.
Profiler's reads data is useful for zero'ing on problems, but you should use
SET STATISTICS IO ON for more accurate report on the logical reads
performed by an individual query in the Query Analyser / Management Studio.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
> Hi! I post a question last week but didn't get any responses, so trying it
> again.
> Basically, I am in a process of tuning a query and number of logical reads
> gets change after I run dbcc dropcleanbuffers.
> I understand that physical reads will be different after cache is flushed
> out but why logical read get changed after flushing the cache but with
> exact same execution plan.
> Sql 200 sp4
>|||Hi Greg,
Thanks for the reply. I am already using Set statistics io on to get the
logical reads, its not from Profiler.
Do you think of any reason why logical read would be different for same
qyery, same dataset with exact same execution plan and only difference is I
flushed the cache with DBCC dropcleanbuffer.
Any input is greatly appreciated.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
> Hi James
> The Reads reported in Profiler also includes reads performed in the
> procedure cache, which is variable depending on activity levels & dbcc
> commands.
> Profiler's reads data is useful for zero'ing on problems, but you should
> use SET STATISTICS IO ON for more accurate report on the logical reads
> performed by an individual query in the Query Analyser / Management
> Studio.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>|||Hi James
Not off the top unfortunately. How much difference are you seeing? Are you
seeing only small differences or large differences/
Is your scenario reproducable via a script that you could post? If so, I'd
be happy to spend some time looking into it. If not, any chance you could
post the set statistics io output & profiler showplan all event's BinaryData
output for each call?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
> Hi Greg,
> Thanks for the reply. I am already using Set statistics io on to get the
> logical reads, its not from Profiler.
> Do you think of any reason why logical read would be different for same
> qyery, same dataset with exact same execution plan and only difference is
> I flushed the cache with DBCC dropcleanbuffer.
> Any input is greatly appreciated.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>|||Hi Greg,
I have replied to your email address the actual query, statistics io output
and profiler trace output that you asked for. Forgive me for sending to your
email rather than entire group. I thought with all the attachment and stuff
it would be better send it to directly. Let me know if any problem with
that. Thanks once again.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5NBAjz5GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi James
> Not off the top unfortunately. How much difference are you seeing? Are you
> seeing only small differences or large differences/
> Is your scenario reproducable via a script that you could post? If so, I'd
> be happy to spend some time looking into it. If not, any chance you could
> post the set statistics io output & profiler showplan all event's
> BinaryData output for each call?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
>
again.
Basically, I am in a process of tuning a query and number of logical reads
gets change after I run dbcc dropcleanbuffers.
I understand that physical reads will be different after cache is flushed
out but why logical read get changed after flushing the cache but with exact
same execution plan.
Sql 200 sp4Hi James
The Reads reported in Profiler also includes reads performed in the
procedure cache, which is variable depending on activity levels & dbcc
commands.
Profiler's reads data is useful for zero'ing on problems, but you should use
SET STATISTICS IO ON for more accurate report on the logical reads
performed by an individual query in the Query Analyser / Management Studio.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
> Hi! I post a question last week but didn't get any responses, so trying it
> again.
> Basically, I am in a process of tuning a query and number of logical reads
> gets change after I run dbcc dropcleanbuffers.
> I understand that physical reads will be different after cache is flushed
> out but why logical read get changed after flushing the cache but with
> exact same execution plan.
> Sql 200 sp4
>|||Hi Greg,
Thanks for the reply. I am already using Set statistics io on to get the
logical reads, its not from Profiler.
Do you think of any reason why logical read would be different for same
qyery, same dataset with exact same execution plan and only difference is I
flushed the cache with DBCC dropcleanbuffer.
Any input is greatly appreciated.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
> Hi James
> The Reads reported in Profiler also includes reads performed in the
> procedure cache, which is variable depending on activity levels & dbcc
> commands.
> Profiler's reads data is useful for zero'ing on problems, but you should
> use SET STATISTICS IO ON for more accurate report on the logical reads
> performed by an individual query in the Query Analyser / Management
> Studio.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>|||Hi James
Not off the top unfortunately. How much difference are you seeing? Are you
seeing only small differences or large differences/
Is your scenario reproducable via a script that you could post? If so, I'd
be happy to spend some time looking into it. If not, any chance you could
post the set statistics io output & profiler showplan all event's BinaryData
output for each call?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
> Hi Greg,
> Thanks for the reply. I am already using Set statistics io on to get the
> logical reads, its not from Profiler.
> Do you think of any reason why logical read would be different for same
> qyery, same dataset with exact same execution plan and only difference is
> I flushed the cache with DBCC dropcleanbuffer.
> Any input is greatly appreciated.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>|||Hi Greg,
I have replied to your email address the actual query, statistics io output
and profiler trace output that you asked for. Forgive me for sending to your
email rather than entire group. I thought with all the attachment and stuff
it would be better send it to directly. Let me know if any problem with
that. Thanks once again.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5NBAjz5GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi James
> Not off the top unfortunately. How much difference are you seeing? Are you
> seeing only small differences or large differences/
> Is your scenario reproducable via a script that you could post? If so, I'd
> be happy to spend some time looking into it. If not, any chance you could
> post the set statistics io output & profiler showplan all event's
> BinaryData output for each call?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
>
Logical read and DBCC dropcleanbuffers
Hi! I post a question last week but didn't get any responses, so trying it
again.
Basically, I am in a process of tuning a query and number of logical reads
gets change after I run dbcc dropcleanbuffers.
I understand that physical reads will be different after cache is flushed
out but why logical read get changed after flushing the cache but with exact
same execution plan.
Sql 200 sp4Hi James
The Reads reported in Profiler also includes reads performed in the
procedure cache, which is variable depending on activity levels & dbcc
commands.
Profiler's reads data is useful for zero'ing on problems, but you should use
SET STATISTICS IO ON for more accurate report on the logical reads
performed by an individual query in the Query Analyser / Management Studio.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
> Hi! I post a question last week but didn't get any responses, so trying it
> again.
> Basically, I am in a process of tuning a query and number of logical reads
> gets change after I run dbcc dropcleanbuffers.
> I understand that physical reads will be different after cache is flushed
> out but why logical read get changed after flushing the cache but with
> exact same execution plan.
> Sql 200 sp4
>|||Hi Greg,
Thanks for the reply. I am already using Set statistics io on to get the
logical reads, its not from Profiler.
Do you think of any reason why logical read would be different for same
qyery, same dataset with exact same execution plan and only difference is I
flushed the cache with DBCC dropcleanbuffer.
Any input is greatly appreciated.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
> Hi James
> The Reads reported in Profiler also includes reads performed in the
> procedure cache, which is variable depending on activity levels & dbcc
> commands.
> Profiler's reads data is useful for zero'ing on problems, but you should
> use SET STATISTICS IO ON for more accurate report on the logical reads
> performed by an individual query in the Query Analyser / Management
> Studio.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>> Hi! I post a question last week but didn't get any responses, so trying
>> it again.
>> Basically, I am in a process of tuning a query and number of logical
>> reads gets change after I run dbcc dropcleanbuffers.
>> I understand that physical reads will be different after cache is flushed
>> out but why logical read get changed after flushing the cache but with
>> exact same execution plan.
>> Sql 200 sp4
>|||Hi James
Not off the top unfortunately. How much difference are you seeing? Are you
seeing only small differences or large differences/
Is your scenario reproducable via a script that you could post? If so, I'd
be happy to spend some time looking into it. If not, any chance you could
post the set statistics io output & profiler showplan all event's BinaryData
output for each call?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
> Hi Greg,
> Thanks for the reply. I am already using Set statistics io on to get the
> logical reads, its not from Profiler.
> Do you think of any reason why logical read would be different for same
> qyery, same dataset with exact same execution plan and only difference is
> I flushed the cache with DBCC dropcleanbuffer.
> Any input is greatly appreciated.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>> Hi James
>> The Reads reported in Profiler also includes reads performed in the
>> procedure cache, which is variable depending on activity levels & dbcc
>> commands.
>> Profiler's reads data is useful for zero'ing on problems, but you should
>> use SET STATISTICS IO ON for more accurate report on the logical reads
>> performed by an individual query in the Query Analyser / Management
>> Studio.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "james" <kush@.brandes.com> wrote in message
>> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>> Hi! I post a question last week but didn't get any responses, so trying
>> it again.
>> Basically, I am in a process of tuning a query and number of logical
>> reads gets change after I run dbcc dropcleanbuffers.
>> I understand that physical reads will be different after cache is
>> flushed
>> out but why logical read get changed after flushing the cache but with
>> exact same execution plan.
>> Sql 200 sp4
>>
>|||Hi Greg,
I have replied to your email address the actual query, statistics io output
and profiler trace output that you asked for. Forgive me for sending to your
email rather than entire group. I thought with all the attachment and stuff
it would be better send it to directly. Let me know if any problem with
that. Thanks once again.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5NBAjz5GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi James
> Not off the top unfortunately. How much difference are you seeing? Are you
> seeing only small differences or large differences/
> Is your scenario reproducable via a script that you could post? If so, I'd
> be happy to spend some time looking into it. If not, any chance you could
> post the set statistics io output & profiler showplan all event's
> BinaryData output for each call?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
>> Hi Greg,
>> Thanks for the reply. I am already using Set statistics io on to get the
>> logical reads, its not from Profiler.
>> Do you think of any reason why logical read would be different for same
>> qyery, same dataset with exact same execution plan and only difference is
>> I flushed the cache with DBCC dropcleanbuffer.
>> Any input is greatly appreciated.
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>> Hi James
>> The Reads reported in Profiler also includes reads performed in the
>> procedure cache, which is variable depending on activity levels & dbcc
>> commands.
>> Profiler's reads data is useful for zero'ing on problems, but you should
>> use SET STATISTICS IO ON for more accurate report on the logical reads
>> performed by an individual query in the Query Analyser / Management
>> Studio.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "james" <kush@.brandes.com> wrote in message
>> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>> Hi! I post a question last week but didn't get any responses, so trying
>> it again.
>> Basically, I am in a process of tuning a query and number of logical
>> reads gets change after I run dbcc dropcleanbuffers.
>> I understand that physical reads will be different after cache is
>> flushed
>> out but why logical read get changed after flushing the cache but with
>> exact same execution plan.
>> Sql 200 sp4
>>
>>
>
again.
Basically, I am in a process of tuning a query and number of logical reads
gets change after I run dbcc dropcleanbuffers.
I understand that physical reads will be different after cache is flushed
out but why logical read get changed after flushing the cache but with exact
same execution plan.
Sql 200 sp4Hi James
The Reads reported in Profiler also includes reads performed in the
procedure cache, which is variable depending on activity levels & dbcc
commands.
Profiler's reads data is useful for zero'ing on problems, but you should use
SET STATISTICS IO ON for more accurate report on the logical reads
performed by an individual query in the Query Analyser / Management Studio.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
> Hi! I post a question last week but didn't get any responses, so trying it
> again.
> Basically, I am in a process of tuning a query and number of logical reads
> gets change after I run dbcc dropcleanbuffers.
> I understand that physical reads will be different after cache is flushed
> out but why logical read get changed after flushing the cache but with
> exact same execution plan.
> Sql 200 sp4
>|||Hi Greg,
Thanks for the reply. I am already using Set statistics io on to get the
logical reads, its not from Profiler.
Do you think of any reason why logical read would be different for same
qyery, same dataset with exact same execution plan and only difference is I
flushed the cache with DBCC dropcleanbuffer.
Any input is greatly appreciated.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
> Hi James
> The Reads reported in Profiler also includes reads performed in the
> procedure cache, which is variable depending on activity levels & dbcc
> commands.
> Profiler's reads data is useful for zero'ing on problems, but you should
> use SET STATISTICS IO ON for more accurate report on the logical reads
> performed by an individual query in the Query Analyser / Management
> Studio.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>> Hi! I post a question last week but didn't get any responses, so trying
>> it again.
>> Basically, I am in a process of tuning a query and number of logical
>> reads gets change after I run dbcc dropcleanbuffers.
>> I understand that physical reads will be different after cache is flushed
>> out but why logical read get changed after flushing the cache but with
>> exact same execution plan.
>> Sql 200 sp4
>|||Hi James
Not off the top unfortunately. How much difference are you seeing? Are you
seeing only small differences or large differences/
Is your scenario reproducable via a script that you could post? If so, I'd
be happy to spend some time looking into it. If not, any chance you could
post the set statistics io output & profiler showplan all event's BinaryData
output for each call?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"james" <kush@.brandes.com> wrote in message
news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
> Hi Greg,
> Thanks for the reply. I am already using Set statistics io on to get the
> logical reads, its not from Profiler.
> Do you think of any reason why logical read would be different for same
> qyery, same dataset with exact same execution plan and only difference is
> I flushed the cache with DBCC dropcleanbuffer.
> Any input is greatly appreciated.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>> Hi James
>> The Reads reported in Profiler also includes reads performed in the
>> procedure cache, which is variable depending on activity levels & dbcc
>> commands.
>> Profiler's reads data is useful for zero'ing on problems, but you should
>> use SET STATISTICS IO ON for more accurate report on the logical reads
>> performed by an individual query in the Query Analyser / Management
>> Studio.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "james" <kush@.brandes.com> wrote in message
>> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>> Hi! I post a question last week but didn't get any responses, so trying
>> it again.
>> Basically, I am in a process of tuning a query and number of logical
>> reads gets change after I run dbcc dropcleanbuffers.
>> I understand that physical reads will be different after cache is
>> flushed
>> out but why logical read get changed after flushing the cache but with
>> exact same execution plan.
>> Sql 200 sp4
>>
>|||Hi Greg,
I have replied to your email address the actual query, statistics io output
and profiler trace output that you asked for. Forgive me for sending to your
email rather than entire group. I thought with all the attachment and stuff
it would be better send it to directly. Let me know if any problem with
that. Thanks once again.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5NBAjz5GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi James
> Not off the top unfortunately. How much difference are you seeing? Are you
> seeing only small differences or large differences/
> Is your scenario reproducable via a script that you could post? If so, I'd
> be happy to spend some time looking into it. If not, any chance you could
> post the set statistics io output & profiler showplan all event's
> BinaryData output for each call?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "james" <kush@.brandes.com> wrote in message
> news:enD3PXz5GHA.1860@.TK2MSFTNGP04.phx.gbl...
>> Hi Greg,
>> Thanks for the reply. I am already using Set statistics io on to get the
>> logical reads, its not from Profiler.
>> Do you think of any reason why logical read would be different for same
>> qyery, same dataset with exact same execution plan and only difference is
>> I flushed the cache with DBCC dropcleanbuffer.
>> Any input is greatly appreciated.
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:OB$Xvum5GHA.2536@.TK2MSFTNGP06.phx.gbl...
>> Hi James
>> The Reads reported in Profiler also includes reads performed in the
>> procedure cache, which is variable depending on activity levels & dbcc
>> commands.
>> Profiler's reads data is useful for zero'ing on problems, but you should
>> use SET STATISTICS IO ON for more accurate report on the logical reads
>> performed by an individual query in the Query Analyser / Management
>> Studio.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "james" <kush@.brandes.com> wrote in message
>> news:u51QEfm5GHA.3560@.TK2MSFTNGP03.phx.gbl...
>> Hi! I post a question last week but didn't get any responses, so trying
>> it again.
>> Basically, I am in a process of tuning a query and number of logical
>> reads gets change after I run dbcc dropcleanbuffers.
>> I understand that physical reads will be different after cache is
>> flushed
>> out but why logical read get changed after flushing the cache but with
>> exact same execution plan.
>> Sql 200 sp4
>>
>>
>
Subscribe to:
Posts (Atom)