Hello,
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
Thanx
It's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=0
Logical Scan Fragmentation is not relavent for heaps
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
Showing posts with label dbcc. Show all posts
Showing posts with label dbcc. Show all posts
Friday, March 9, 2012
Logical Scan Fragmentation..issue!
Hello,
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
ThanxIt's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=
0
Logical Scan Fragmentation is not relavent for heaps
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
ThanxIt's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=
0
Logical Scan Fragmentation is not relavent for heaps
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
Logical Scan Fragmentation..issue!
Hello,
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
ThanxIt's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=0
Logical Scan Fragmentation is not relavent for heaps
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
ThanxIt's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=0
Logical Scan Fragmentation is not relavent for heaps
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
Wednesday, March 7, 2012
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)