Holy hordes of transactional data Batman

I have a LOG file of 113Gig with only 29 Gig of free space
available on the drive, I have no media that will hold
this as a backup.
I ran the "Backup Log" "with_truncate" and "no_truncate"
but nothing seems to be making this thing disappear.
What is my next step to dissolving this beast?
Truncating the log only marks space within the log as reusable. It does not
affect the physical size of the file.
Please read about DBCC SHRINKFILE to actually shrink the log file.
Also, always tell us what version you are using, and there are some specific
guidelines for shrinking that we can give you depending on the version.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||To shrink it you should use DBCC SHRINKFILE().
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||test
"Kalen Delaney" wrote:

> Truncating the log only marks space within the log as reusable. It does not
> affect the physical size of the file.
> Please read about DBCC SHRINKFILE to actually shrink the log file.
> Also, always tell us what version you are using, and there are some specific
> guidelines for shrinking that we can give you depending on the version.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "VidRo" <vid_ro@.myalias.postalias.com> wrote in message
> news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>
>
|||DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
from reoccuring, set up maintenance plan and check appropriate selctions
(Optimization tab).
"VidRo" wrote:

> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>
|||DBCC shrinkdatabase or DBCC shrinkfile will do it; however if you want to
prevent it from reoccuring, set up maintenance plan properly (Optimization
tab).
"VidRo" wrote:

> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>
|||Hi Adam
I suggested DBCC SHRINKFILE because you can control the files to be
affected. DBCC SHRINKDATABASE will shrink all the files. Shrinking and data
file is a very different operation than shrinking a log file, and it is NOT
something to be done on a regular basis. The overhead is far too high. I
would not recommended putting a shrink of the whole database in any kind of
regular maintenance plan.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Locus Adam" <Locus Adam@.discussions.microsoft.com> wrote in message
news:5132814B-3EEE-4AC7-A8DB-4588ADAE5D87@.microsoft.com...[vbcol=seagreen]
> DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
> from reoccuring, set up maintenance plan and check appropriate selctions
> (Optimization tab).
> "VidRo" wrote:

Holy hordes of transactional data Batman

I have a LOG file of 113Gig with only 29 Gig of free space
available on the drive, I have no media that will hold
this as a backup.
I ran the "Backup Log" "with_truncate" and "no_truncate"
but nothing seems to be making this thing disappear.
What is my next step to dissolving this beast?Truncating the log only marks space within the log as reusable. It does not
affect the physical size of the file.
Please read about DBCC SHRINKFILE to actually shrink the log file.
Also, always tell us what version you are using, and there are some specific
guidelines for shrinking that we can give you depending on the version.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||To shrink it you should use DBCC SHRINKFILE().
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||test
"Kalen Delaney" wrote:

> Truncating the log only marks space within the log as reusable. It does no
t
> affect the physical size of the file.
> Please read about DBCC SHRINKFILE to actually shrink the log file.
> Also, always tell us what version you are using, and there are some specif
ic
> guidelines for shrinking that we can give you depending on the version.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "VidRo" <vid_ro@.myalias.postalias.com> wrote in message
> news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>
>|||DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
from reoccuring, set up maintenance plan and check appropriate selctions
(Optimization tab).
"VidRo" wrote:

> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>|||DBCC shrinkdatabase or DBCC shrinkfile will do it; however if you want to
prevent it from reoccuring, set up maintenance plan properly (Optimization
tab).
"VidRo" wrote:

> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>|||Hi Adam
I suggested DBCC SHRINKFILE because you can control the files to be
affected. DBCC SHRINKDATABASE will shrink all the files. Shrinking and data
file is a very different operation than shrinking a log file, and it is NOT
something to be done on a regular basis. The overhead is far too high. I
would not recommended putting a shrink of the whole database in any kind of
regular maintenance plan.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Locus Adam" <Locus Adam@.discussions.microsoft.com> wrote in message
news:5132814B-3EEE-4AC7-A8DB-4588ADAE5D87@.microsoft.com...[vbcol=seagreen]
> DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
> from reoccuring, set up maintenance plan and check appropriate selctions
> (Optimization tab).
> "VidRo" wrote:
>sql

High values shown in fn_virtualfilestats

Hello
Recently taken a new DBA position, reviewing existing infrasturture and DB
config's
Ran fn_Virtualfilestats and got the following values
does anyone think these are high or show areas for further investigation,
don't want to waste time on further research unless it is warranted etc..
DbId FileId NumberReads NumberWrites BytesRead BytesWritten
IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
%Bytes %Stall
1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
1,971,159,040 12 14,953 - - -
1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
- - -
2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
4,685,123 265,700,286,464 - 56,711 - - -
2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
2,014,727,680 3 49,441 - - -
3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
5 55,757 - - -
3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
- -
4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
1,727,569,920 9 17,538 - - -
4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
1,123 - - -
5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
7,832,162 179,358,797,824 - 22,900 - - -
5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
1,020,696 146,016,542,720 - 143,055 - - -
6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
7,271,890,944 - 88,095 - - -
6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
13,559 - - -
18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
- -
--
Neil HamblyNeil,
That is a pretty unreadable post and most people willnot take the time to
clean it up. But a single snapshot of filestats is mostly meaningless by
itself. You need to take a baseline or starting point first. Then after
some period of time you take another reading and compare the differences.
That gives you a time period to reference how long it takes for the numbers
to accumulate.
--
Andrew J. Kelly SQL MVP
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:49136F95-EF7A-42AF-9042-08D670FBDA36@.microsoft.com...
> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hambly|||May be easier to read if values copied into excel
main concerns are with Byte
"Neil Hambly" wrote:
> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hamblysql

High values shown in fn_virtualfilestats

Hello
Recently taken a new DBA position, reviewing existing infrasturture and DB
config's
Ran fn_Virtualfilestats and got the following values
does anyone think these are high or show areas for further investigation,
don't want to waste time on further research unless it is warranted etc..
DbId FileId NumberReads NumberWrites BytesRead BytesWritten
IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
%Bytes %Stall
1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
1,971,159,040 12 14,953 - - -
1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
- - -
2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
4,685,123 265,700,286,464 - 56,711 - - -
2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
2,014,727,680 3 49,441 - - -
3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
5 55,757 - - -
3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
- -
4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
1,727,569,920 9 17,538 - - -
4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
1,123 - - -
5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
7,832,162 179,358,797,824 - 22,900 - - -
5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
1,020,696 146,016,542,720 - 143,055 - - -
6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
7,271,890,944 - 88,095 - - -
6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
13,559 - - -
18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
- -
Neil HamblyNeil,
That is a pretty unreadable post and most people willnot take the time to
clean it up. But a single snapshot of filestats is mostly meaningless by
itself. You need to take a baseline or starting point first. Then after
some period of time you take another reading and compare the differences.
That gives you a time period to reference how long it takes for the numbers
to accumulate.
Andrew J. Kelly SQL MVP
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:49136F95-EF7A-42AF-9042-08D670FBDA36@.microsoft.com...
> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hambly|||May be easier to read if values copied into excel
main concerns are with Byte
"Neil Hambly" wrote:

> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 9
40
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168
-
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hambly

High disk I/O with long running queries surprising?

Hello,
I am interested in opinions about a case that I recently ran into. We
have a server with SQL Server 2000 Standard where a 50 GB database is
run. A stored procedure is run every night that performs data updates
and perform several aggregations and calculations of the data for about
4 hours in this database. Based on this little information, would you
be surprised to find out that during the execution of this stored
procedure, SQL Server uses the disk to 100 % (or at least want to)?
/Stefan> Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
Unless all data can fit in the memory allocated to SQL Server, I would
expect significant disk activity during the process. Even then, modified
still data needs to be written to disk.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<sl@.invativa.se> wrote in message
news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> /Stefan
>|||<sl@.invativa.se> wrote in message
news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
>
Assuming that SQL Server is using a buffer cache of significantly less than
50GB, and these queries need to read a large percentage of the 50GB of data,
then SQL Server will need to read the data from disk.
So no, I would not be surprised.
David|||In addition to what the others have said, if this is based on the readings
from the % Disk Time counter, you may want to check a few more disk counters
to be sure. This counter can be funky. It's more reliable to check I/O
latency counters such as Avg Disk Sec/Read and Avg Disk Sec/Write.
Linchi
"sl@.invativa.se" wrote:
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> /Stefan
>|||And, of course, WRITE the updates back to disk.
No, I wouldn't be at all surprised.
The next question 'should be', based upon the following DDL and stored
procedure code, is there a more efficient way to accomplish the task?
(include table DDL and stored procedure code.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eacg56dmGHA.660@.TK2MSFTNGP05.phx.gbl...
> <sl@.invativa.se> wrote in message
> news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
>> Hello,
>> I am interested in opinions about a case that I recently ran into. We
>> have a server with SQL Server 2000 Standard where a 50 GB database is
>> run. A stored procedure is run every night that performs data updates
>> and perform several aggregations and calculations of the data for about
>> 4 hours in this database. Based on this little information, would you
>> be surprised to find out that during the execution of this stored
>> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> Assuming that SQL Server is using a buffer cache of significantly less
> than 50GB, and these queries need to read a large percentage of the 50GB
> of data, then SQL Server will need to read the data from disk.
> So no, I would not be surprised.
> David|||Thanks for all answers.
Great that you all confirmed that lots of disk I/O is no surprise in
this scenario.
/Stefan

High CPU - low IO, low RAM

Hi,
I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server SP4:
- set SQL server buffer cache at 16 MB, restarted;
- ran a read-intensive query (in terms of SQL Profiler data).
While the query was running CPU usage remained at or above 80%. But the disk
activity was not so high. Most of the time it remained under 30% despite the
minimal amount of RAM I allocated to the buffer cache. Disk queues were also
short, that is, way below 1 (by a few magnitudes). I'm not talking about
occasional spikes here.
So why do I get 10s of millions of (logical) reads in SQL Profiler but don't
see 100% load on a hard disk even with the tiny buffer cache?
-- Many thanks, Oskar.
What version of windows are you on Oskar and what disk performance counters
are you using, some of the counters in win 2000+ are a bit iffy to say the
least... ...also are you monitoring all disk arrays or just the array with
sql on?
"Oskar" wrote:

> Hi,
> I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server SP4:
> - set SQL server buffer cache at 16 MB, restarted;
> - ran a read-intensive query (in terms of SQL Profiler data).
> While the query was running CPU usage remained at or above 80%. But the disk
> activity was not so high. Most of the time it remained under 30% despite the
> minimal amount of RAM I allocated to the buffer cache. Disk queues were also
> short, that is, way below 1 (by a few magnitudes). I'm not talking about
> occasional spikes here.
> So why do I get 10s of millions of (logical) reads in SQL Profiler but don't
> see 100% load on a hard disk even with the tiny buffer cache?
> -- Many thanks, Oskar.
>
|||"Ben UK" wrote:

> What version of windows are you on Oskar and what disk performance counters
> are you using, some of the counters in win 2000+ are a bit iffy to say the
> least... ...also are you monitoring all disk arrays or just the array with
> sql on?
1) > > ... Windows 2000 Server SP4
2) Those are single disks and, yes, I looked at the disks with SQL Server
databases. Mind you, the activity was there, but it was less than I expected.
The database files are on a dedicated disk and tempdb is on the OS disk.
[vbcol=seagreen]
> "Oskar" wrote:
|||On Mon, 8 Jan 2007 07:22:00 -0800, Oskar
<Oskar@.discussions.microsoft.com> wrote:
>So why do I get 10s of millions of (logical) reads in SQL Profiler but don't
>see 100% load on a hard disk even with the tiny buffer cache?
If the data is tiny, you can get a zillion logical reads while its
kept in cache, that's why they are LOGICAL reads!
In fact, if the data is well-indexed and just the *index* is cached,
and especially if the data actually fetched is limited, you might get
the pattern you see.
J.
|||If I understood it correctly, your guess is that the data my query is working
on is actually only a little bit bigger than the buffer cache size I
specified. I find this unlikely.
"JXStern" wrote:

> On Mon, 8 Jan 2007 07:22:00 -0800, Oskar
> <Oskar@.discussions.microsoft.com> wrote:
> If the data is tiny, you can get a zillion logical reads while its
> kept in cache, that's why they are LOGICAL reads!
> In fact, if the data is well-indexed and just the *index* is cached,
> and especially if the data actually fetched is limited, you might get
> the pattern you see.
> J.
>
|||On Mon, 8 Jan 2007 09:54:01 -0800, Oskar
<Oskar@.discussions.microsoft.com> wrote:

>If I understood it correctly, your guess is that the data my query is working
>on is actually only a little bit bigger than the buffer cache size I
>specified. I find this unlikely.
I'm just guessing, you should be able to tell how much data you're
fetching.
J.
[vbcol=seagreen]
>"JXStern" wrote:
|||This is actually what I would expect in this case. SQL Server does a lot of
things to optimize disk io. When you are doing pretty sequential reads the
IO subsystem will do read-ahead so a lot of data can be pulled into memory
with a single IO. The issue with low memory then becomes one of
continuously moving data around in the buffer pool to make room for new data
arriving from the disk and building the result set. This requires a lot of
processor cycles. I think what you're missing here (if your description is
correct) is that a lot of buffer space doesn't help you the first time you
run a query. When you scan a table the first time you have to read all the
sectors from disk no matter how much buffer memory you have. Where the
difference comes in is that if you have a lot of memory, the SECOND time you
scan a table, most of the data will be in buffer memory so it won't have to
be read from the disk.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
> Hi,
> I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server
> SP4:
> - set SQL server buffer cache at 16 MB, restarted;
> - ran a read-intensive query (in terms of SQL Profiler data).
> While the query was running CPU usage remained at or above 80%. But the
> disk
> activity was not so high. Most of the time it remained under 30% despite
> the
> minimal amount of RAM I allocated to the buffer cache. Disk queues were
> also
> short, that is, way below 1 (by a few magnitudes). I'm not talking about
> occasional spikes here.
> So why do I get 10s of millions of (logical) reads in SQL Profiler but
> don't
> see 100% load on a hard disk even with the tiny buffer cache?
> -- Many thanks, Oskar.
>
|||For Physical Disk monitoring use Disk Reads/ Sec & Disk Writes/ Sec coupled
with % Idle (100- %Idle = % Busy) for seeing how busy the disks are.. ..you
can then use Avg Disk Sec/ Read & Avg Disk Sec/ Write to measure the latency
and see if the disk is keeping up, and Avg Read Bytes/Sec & Avg Write
Bytes/Sec to understand the throughput.
You could also monitor Pages/Sec, Pages Input/Sec, Page Reads/Sec, Page
Output/Sec, Page Writes/Sec to see what is happening with regards to memory
and it's relation to the disk..
Check sql counters like Buffer Cache Hit Ratio, Lazy Writes/Sec, Avg Page
Life Expectency, Latch Waits/Sec, Avg Latch Wait Time to see how what your
doing is impacting sql.
Hope this helps
Ben
"Oskar" wrote:
[vbcol=seagreen]
>
> "Ben UK" wrote:
>
> 1) > > ... Windows 2000 Server SP4
> 2) Those are single disks and, yes, I looked at the disks with SQL Server
> databases. Mind you, the activity was there, but it was less than I expected.
> The database files are on a dedicated disk and tempdb is on the OS disk.
>
|||This is an interesting point. Because, if I'm right, it implies that it's
better to minimize the amount of the buffer cache allocated to a query that
does a large scan. The query would just waste precious CPU resources for
bringing data into the cache just to toss it out in a short while due to RAM
shortage.
As to the low disk activity: maybe this explains it all:
FROM
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
<<
Note on Disk Queue Length
This counter no longer yields meaningful results in interpreting disk I/O
problems with SQL Server 2000. This is because the SQL Server engines
dynamically manage disk I/O, which is why the Max Async I/O setting no longer
exists. A simplified explanation is that SQL Server issues an order for disk
I/O, but does not wait for that process to complete before issuing another
request. When the I/O request has completed its work, the database engine is
notified and processes the results. SQL Server monitors the throughput on
these requests, and will manage the amount of I/O that the disks can
effectively handle.[vbcol=seagreen]
-- Many thanks, Oskar.
"Roger Wolter[MSFT]" wrote:

> This is actually what I would expect in this case. SQL Server does a lot of
> things to optimize disk io. When you are doing pretty sequential reads the
> IO subsystem will do read-ahead so a lot of data can be pulled into memory
> with a single IO. The issue with low memory then becomes one of
> continuously moving data around in the buffer pool to make room for new data
> arriving from the disk and building the result set. This requires a lot of
> processor cycles. I think what you're missing here (if your description is
> correct) is that a lot of buffer space doesn't help you the first time you
> run a query. When you scan a table the first time you have to read all the
> sectors from disk no matter how much buffer memory you have. Where the
> difference comes in is that if you have a lot of memory, the SECOND time you
> scan a table, most of the data will be in buffer memory so it won't have to
> be read from the disk.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
>
>
|||"Greg Linwood" wrote:

> That disk queues are short only indicates that the disks are keeping up with
> requests.
> This shouldn't be surprising if either:
> (a) Disk requests are being managed by SQL's UMS
Not sure what do you mean...

> or
> (b) The disk/s are simply keeping up with the requests..
> Also, for a "read intensive query", do you mean a large table scan? If so,
> it's possible that fewer disk requests might be performed than you're
> expecting due to disk IO heuristics such as extent reads..
I didn't look at the execution plan - just the Reads column for
SQL:BatchCompleted and RPC:Completed events, so probably you're right that
some of the reads were read-ahead reads (maybe even a majority of them).
-- Thanks, Oskar.

> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
>
>

High CPU - low IO, low RAM

Hi,
I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server SP
4:
- set SQL server buffer cache at 16 MB, restarted;
- ran a read-intensive query (in terms of SQL Profiler data).
While the query was running CPU usage remained at or above 80%. But the disk
activity was not so high. Most of the time it remained under 30% despite the
minimal amount of RAM I allocated to the buffer cache. Disk queues were also
short, that is, way below 1 (by a few magnitudes). I'm not talking about
occasional spikes here.
So why do I get 10s of millions of (logical) reads in SQL Profiler but don't
see 100% load on a hard disk even with the tiny buffer cache?
-- Many thanks, Oskar.What version of windows are you on Oskar and what disk performance counters
are you using, some of the counters in win 2000+ are a bit iffy to say the
least... ...also are you monitoring all disk arrays or just the array with
sql on?
"Oskar" wrote:

> Hi,
> I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server
SP4:
> - set SQL server buffer cache at 16 MB, restarted;
> - ran a read-intensive query (in terms of SQL Profiler data).
> While the query was running CPU usage remained at or above 80%. But the di
sk
> activity was not so high. Most of the time it remained under 30% despite t
he
> minimal amount of RAM I allocated to the buffer cache. Disk queues were al
so
> short, that is, way below 1 (by a few magnitudes). I'm not talking about
> occasional spikes here.
> So why do I get 10s of millions of (logical) reads in SQL Profiler but don
't
> see 100% load on a hard disk even with the tiny buffer cache?
> -- Many thanks, Oskar.
>|||"Ben UK" wrote:

> What version of windows are you on Oskar and what disk performance counter
s
> are you using, some of the counters in win 2000+ are a bit iffy to say the
> least... ...also are you monitoring all disk arrays or just the array with
> sql on?
1) > > ... Windows 2000 Server SP4
2) Those are single disks and, yes, I looked at the disks with SQL Server
databases. Mind you, the activity was there, but it was less than I expected
.
The database files are on a dedicated disk and tempdb is on the OS disk.
[vbcol=seagreen]
> "Oskar" wrote:
>|||On Mon, 8 Jan 2007 07:22:00 -0800, Oskar
<Oskar@.discussions.microsoft.com> wrote:
>So why do I get 10s of millions of (logical) reads in SQL Profiler but don'
t
>see 100% load on a hard disk even with the tiny buffer cache?
If the data is tiny, you can get a zillion logical reads while its
kept in cache, that's why they are LOGICAL reads!
In fact, if the data is well-indexed and just the *index* is cached,
and especially if the data actually fetched is limited, you might get
the pattern you see.
J.|||If I understood it correctly, your guess is that the data my query is workin
g
on is actually only a little bit bigger than the buffer cache size I
specified. I find this unlikely.
"JXStern" wrote:

> On Mon, 8 Jan 2007 07:22:00 -0800, Oskar
> <Oskar@.discussions.microsoft.com> wrote:
> If the data is tiny, you can get a zillion logical reads while its
> kept in cache, that's why they are LOGICAL reads!
> In fact, if the data is well-indexed and just the *index* is cached,
> and especially if the data actually fetched is limited, you might get
> the pattern you see.
> J.
>|||That disk queues are short only indicates that the disks are keeping up with
requests.
This shouldn't be surprising if either:
(a) Disk requests are being managed by SQL's UMS
or
(b) The disk/s are simply keeping up with the requests..
Also, for a "read intensive query", do you mean a large table scan? If so,
it's possible that fewer disk requests might be performed than you're
expecting due to disk IO heuristics such as extent reads..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
> Hi,
> I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server
> SP4:
> - set SQL server buffer cache at 16 MB, restarted;
> - ran a read-intensive query (in terms of SQL Profiler data).
> While the query was running CPU usage remained at or above 80%. But the
> disk
> activity was not so high. Most of the time it remained under 30% despite
> the
> minimal amount of RAM I allocated to the buffer cache. Disk queues were
> also
> short, that is, way below 1 (by a few magnitudes). I'm not talking about
> occasional spikes here.
> So why do I get 10s of millions of (logical) reads in SQL Profiler but
> don't
> see 100% load on a hard disk even with the tiny buffer cache?
> -- Many thanks, Oskar.
>|||On Mon, 8 Jan 2007 09:54:01 -0800, Oskar
<Oskar@.discussions.microsoft.com> wrote:

>If I understood it correctly, your guess is that the data my query is worki
ng
>on is actually only a little bit bigger than the buffer cache size I
>specified. I find this unlikely.
I'm just guessing, you should be able to tell how much data you're
fetching.
J.
[vbcol=seagreen]
>"JXStern" wrote:
>|||This is actually what I would expect in this case. SQL Server does a lot of
things to optimize disk io. When you are doing pretty sequential reads the
IO subsystem will do read-ahead so a lot of data can be pulled into memory
with a single IO. The issue with low memory then becomes one of
continuously moving data around in the buffer pool to make room for new data
arriving from the disk and building the result set. This requires a lot of
processor cycles. I think what you're missing here (if your description is
correct) is that a lot of buffer space doesn't help you the first time you
run a query. When you scan a table the first time you have to read all the
sectors from disk no matter how much buffer memory you have. Where the
difference comes in is that if you have a lot of memory, the SECOND time you
scan a table, most of the data will be in buffer memory so it won't have to
be read from the disk.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
> Hi,
> I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server
> SP4:
> - set SQL server buffer cache at 16 MB, restarted;
> - ran a read-intensive query (in terms of SQL Profiler data).
> While the query was running CPU usage remained at or above 80%. But the
> disk
> activity was not so high. Most of the time it remained under 30% despite
> the
> minimal amount of RAM I allocated to the buffer cache. Disk queues were
> also
> short, that is, way below 1 (by a few magnitudes). I'm not talking about
> occasional spikes here.
> So why do I get 10s of millions of (logical) reads in SQL Profiler but
> don't
> see 100% load on a hard disk even with the tiny buffer cache?
> -- Many thanks, Oskar.
>|||For Physical Disk monitoring use Disk Reads/ Sec & Disk Writes/ Sec coupled
with % Idle (100- %Idle = % Busy) for seeing how busy the disks are.. ..you
can then use Avg Disk Sec/ Read & Avg Disk Sec/ Write to measure the latency
and see if the disk is keeping up, and Avg Read Bytes/Sec & Avg Write
Bytes/Sec to understand the throughput.
You could also monitor Pages/Sec, Pages Input/Sec, Page Reads/Sec, Page
Output/Sec, Page Writes/Sec to see what is happening with regards to memory
and it's relation to the disk..
Check sql counters like Buffer Cache Hit Ratio, Lazy Writes/Sec, Avg Page
Life Expectency, Latch Waits/Sec, Avg Latch Wait Time to see how what your
doing is impacting sql.
Hope this helps
Ben
"Oskar" wrote:
[vbcol=seagreen]
>
> "Ben UK" wrote:
>
> 1) > > ... Windows 2000 Server SP4
> 2) Those are single disks and, yes, I looked at the disks with SQL Server
> databases. Mind you, the activity was there, but it was less than I expect
ed.
> The database files are on a dedicated disk and tempdb is on the OS disk.
>|||This is an interesting point. Because, if I'm right, it implies that it's
better to minimize the amount of the buffer cache allocated to a query that
does a large scan. The query would just waste precious CPU resources for
bringing data into the cache just to toss it out in a short while due to RAM
shortage.
As to the low disk activity: maybe this explains it all:
FROM
http://www.microsoft.com/technet/pr...in/sqlops6.mspx
<<
Note on Disk Queue Length
This counter no longer yields meaningful results in interpreting disk I/O
problems with SQL Server 2000. This is because the SQL Server engines
dynamically manage disk I/O, which is why the Max Async I/O setting no longe
r
exists. A simplified explanation is that SQL Server issues an order for disk
I/O, but does not wait for that process to complete before issuing another
request. When the I/O request has completed its work, the database engine is
notified and processes the results. SQL Server monitors the throughput on
these requests, and will manage the amount of I/O that the disks can
effectively handle.
-- Many thanks, Oskar.
"Roger Wolter[MSFT]" wrote:
[vbcol=seagreen]
> This is actually what I would expect in this case. SQL Server does a lot
of
> things to optimize disk io. When you are doing pretty sequential reads th
e
> IO subsystem will do read-ahead so a lot of data can be pulled into memory
> with a single IO. The issue with low memory then becomes one of
> continuously moving data around in the buffer pool to make room for new da
ta
> arriving from the disk and building the result set. This requires a lot o
f
> processor cycles. I think what you're missing here (if your description i
s
> correct) is that a lot of buffer space doesn't help you the first time you
> run a query. When you scan a table the first time you have to read all th
e
> sectors from disk no matter how much buffer memory you have. Where the
> difference comes in is that if you have a lot of memory, the SECOND time y
ou
> scan a table, most of the data will be in buffer memory so it won't have t
o
> be read from the disk.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
>
>