DBCC Shrinkfile

What happens underlying the DBCC Shrinkfile statement ? What moves around
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? ThanksHassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||It moves data pages from the end of the file towards then beginning of the file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>

DBCC Shrinkfile

What happens underlying the DBCC Shrinkfile statement ? What moves around
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? ThanksHassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:

> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||It moves data pages from the end of the file towards then beginning of the f
ile.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl
..
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>

DBCC Shrinkfile

What happens underlying the DBCC Shrinkfile statement ? What moves around
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? Thanks
Hassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:

> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
|||It moves data pages from the end of the file towards then beginning of the file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>

DBCC SHOWCONTIG and Extent Switches

All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000
Kay
http://www.sql-server-performance.co...showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
|||Based on the result set provided and viewing the clustered index, your extent
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since the
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:

> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" and
> "LogicalFragmentation", I paid special attention to "Extent Swithces" which
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022 12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022 12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>

DBCC SHOWCONTIG and Extent Switches

All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your extent
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since the
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" and
> "LogicalFragmentation", I paid special attention to "Extent Swithces" which
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022 12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022 12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>

DBCC SHOWCONTIG and Extent Switches

All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.c..._showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your exten
t
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since th
e
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:

> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" an
d
> "LogicalFragmentation", I paid special attention to "Extent Swithces" whic
h
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSiz
e
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensit
y
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.32
2
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.41
7
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 16902
2
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 37
0
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 1
2
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 1
6
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>sql

DBCC SHOWCONTIG

When executing the DBCC SHOWCONTIG statement; will it block all inserts,
selects and updates to its designated table?
We have one table that is constantly being written to 27X7 that cannot
be locked.
I want to run DBCC SHOWCONTIG, to perform maintenance, but do not want
to if this will occur.
Thanks,
--
Ben MUse WITH FAST to avoidd blocking. See BOL for exact syntax
> We have one table that is constantly being written to 27X7
That's a lot of hours in a day :-)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ben" <ben@.brainspout.com> wrote in message
news:Or0Uspc4DHA.3416@.tk2msftngp13.phx.gbl...
> When executing the DBCC SHOWCONTIG statement; will it block all inserts,
> selects and updates to its designated table?
> We have one table that is constantly being written to 27X7 that cannot
> be locked.
> I want to run DBCC SHOWCONTIG, to perform maintenance, but do not want
> to if this will occur.
> Thanks,
> --
> Ben M
>|||Yeah that is alot of time:-) Thanks for the help.
Jasper Smith wrote:
> Use WITH FAST to avoidd blocking. See BOL for exact syntax
>
>>We have one table that is constantly being written to 27X7
>
> That's a lot of hours in a day :-)
>
--
Ben M

DBCC SHOWCONTIG

When executing the DBCC SHOWCONTIG statement; will it block all inserts,
selects and updates to its designated table?
We have one table that is constantly being written to 27X7 that cannot
be locked.
I want to run DBCC SHOWCONTIG, to perform maintenance, but do not want
to if this will occur.
Thanks,
--
Ben MUse WITH FAST to avoidd blocking. See BOL for exact syntax

quote:

---

> We have one table that is constantly being written to 27X7

---

That's a lot of hours in a day :-)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ben" <ben@.brainspout.com> wrote in message
news:Or0Uspc4DHA.3416@.tk2msftngp13.phx.gbl...

quote:

---

> When executing the DBCC SHOWCONTIG statement; will it block all inserts,
> selects and updates to its designated table?
> We have one table that is constantly being written to 27X7 that cannot
> be locked.
> I want to run DBCC SHOWCONTIG, to perform maintenance, but do not want
> to if this will occur.
> Thanks,
> --
> Ben M
>

---

|||Yeah that is alot of time:-) Thanks for the help.
Jasper Smith wrote:

quote:

---

> Use WITH FAST to avoidd blocking. See BOL for exact syntax
>
>
> That's a lot of hours in a day :-)
>

---

Ben M

DBCC Reindex factor

DBCC DBREINDEX mytable , 90)
fACTOR IS 90% MEANS 90% full... is that correct?
I feel that my table is 90% free space for that dbcc statement i ran.
Because after reindex the db size is 20gb instead of 5 gb. I have to shrink
it after reindex. any ideas?
Thanks
Sql2005 no sp applied yet.
after doing dbcc dbreindex with 90% factor. All dbs data and log files
allocation space is very big (70-80% of acture data space). That means I
have to do shrink db(both data and log) after reindex?
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
> DBCC DBREINDEX mytable , 90)
> fACTOR IS 90% MEANS 90% full... is that correct?
> I feel that my table is 90% free space for that dbcc statement i ran.
> Because after reindex the db size is 20gb instead of 5 gb. I have to
> shrink it after reindex. any ideas?
> Thanks
>
|||mecn,
How much of it is data space and how much is log space? If the log became
very large, that is easy to shrink, but shrinking the data is usually not a
wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
If you are actually working that close to full on your disks, then it is
time to considering buying more space.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Sql2005 no sp applied yet.
> after doing dbcc dbreindex with 90% factor. All dbs data and log files
> allocation space is very big (70-80% of acture data space). That means I
> have to do shrink db(both data and log) after reindex?
> Thanks
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>
|||The problem is that data size is 5gb, that db doesn't have that much
transactions.
I do weekly reindex. after I finish db reindex the log size (allocation ) is
like 21 GB. I have to do shrink log file.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
> mecn,
> How much of it is data space and how much is log space? If the log became
> very large, that is easy to shrink, but shrinking the data is usually not
> a wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
> If you are actually working that close to full on your disks, then it is
> time to considering buying more space.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>
|||What recovery mode are you running? If you are in FULL and don't do any log
backups during the reindex process your log must be able to hold all the log
entries from the reindex process. If you are using DBCC INDEXDEFRAG this can
result in a lot of log entries. Try setting the recovery mode to simple just
before the reindex and then back to full when done. Just remember to do a
FULL backup to reinitialize the log chain.
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
> The problem is that data size is 5gb, that db doesn't have that much
> transactions.
> I do weekly reindex. after I finish db reindex the log size (allocation )
> is like 21 GB. I have to do shrink log file.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>
|||Full
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
> What recovery mode are you running? If you are in FULL and don't do any
> log backups during the reindex process your log must be able to hold all
> the log entries from the reindex process. If you are using DBCC
> INDEXDEFRAG this can result in a lot of log entries. Try setting the
> recovery mode to simple just before the reindex and then back to full when
> done. Just remember to do a FULL backup to reinitialize the log chain.
> --
> Andrew J. Kelly SQL MVP
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
>
|||Well that is the answer then. If you are in FULL recovery mode every part of
the reindex will be fully logged and the log will grow when it runs out of
room.
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%232kuyUgzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> Full
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
>

DBCC Reindex factor

DBCC DBREINDEX mytable , 90)
fACTOR IS 90% MEANS 90% full... is that correct?
I feel that my table is 90% free space for that dbcc statement i ran.
Because after reindex the db size is 20gb instead of 5 gb. I have to shrink
it after reindex. any ideas?
ThanksSql2005 no sp applied yet.
after doing dbcc dbreindex with 90% factor. All dbs data and log files
allocation space is very big (70-80% of acture data space). That means I
have to do shrink db(both data and log) after reindex'
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
> DBCC DBREINDEX mytable , 90)
> fACTOR IS 90% MEANS 90% full... is that correct?
> I feel that my table is 90% free space for that dbcc statement i ran.
> Because after reindex the db size is 20gb instead of 5 gb. I have to
> shrink it after reindex. any ideas?
> Thanks
>|||mecn,
How much of it is data space and how much is log space? If the log became
very large, that is easy to shrink, but shrinking the data is usually not a
wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
If you are actually working that close to full on your disks, then it is
time to considering buying more space.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Sql2005 no sp applied yet.
> after doing dbcc dbreindex with 90% factor. All dbs data and log files
> allocation space is very big (70-80% of acture data space). That means I
> have to do shrink db(both data and log) after reindex'
> Thanks
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>|||The problem is that data size is 5gb, that db doesn't have that much
transactions.
I do weekly reindex. after I finish db reindex the log size (allocation ) is
like 21 GB. I have to do shrink log file.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
> mecn,
> How much of it is data space and how much is log space? If the log became
> very large, that is easy to shrink, but shrinking the data is usually not
> a wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
> If you are actually working that close to full on your disks, then it is
> time to considering buying more space.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>|||What recovery mode are you running? If you are in FULL and don't do any log
backups during the reindex process your log must be able to hold all the log
entries from the reindex process. If you are using DBCC INDEXDEFRAG this can
result in a lot of log entries. Try setting the recovery mode to simple just
before the reindex and then back to full when done. Just remember to do a
FULL backup to reinitialize the log chain.
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
> The problem is that data size is 5gb, that db doesn't have that much
> transactions.
> I do weekly reindex. after I finish db reindex the log size (allocation )
> is like 21 GB. I have to do shrink log file.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>|||Full
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
> What recovery mode are you running? If you are in FULL and don't do any
> log backups during the reindex process your log must be able to hold all
> the log entries from the reindex process. If you are using DBCC
> INDEXDEFRAG this can result in a lot of log entries. Try setting the
> recovery mode to simple just before the reindex and then back to full when
> done. Just remember to do a FULL backup to reinitialize the log chain.
> --
> Andrew J. Kelly SQL MVP
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
>|||Well that is the answer then. If you are in FULL recovery mode every part of
the reindex will be fully logged and the log will grow when it runs out of
room.
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%232kuyUgzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> Full
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
>

DBCC Reindex factor

DBCC DBREINDEX mytable , 90)
fACTOR IS 90% MEANS 90% full... is that correct?
I feel that my table is 90% free space for that dbcc statement i ran.
Because after reindex the db size is 20gb instead of 5 gb. I have to shrink
it after reindex. any ideas?
ThanksSql2005 no sp applied yet.
after doing dbcc dbreindex with 90% factor. All dbs data and log files
allocation space is very big (70-80% of acture data space). That means I
have to do shrink db(both data and log) after reindex'
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
> DBCC DBREINDEX mytable , 90)
> fACTOR IS 90% MEANS 90% full... is that correct?
> I feel that my table is 90% free space for that dbcc statement i ran.
> Because after reindex the db size is 20gb instead of 5 gb. I have to
> shrink it after reindex. any ideas?
> Thanks
>|||mecn,
How much of it is data space and how much is log space? If the log became
very large, that is easy to shrink, but shrinking the data is usually not a
wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
If you are actually working that close to full on your disks, then it is
time to considering buying more space.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Sql2005 no sp applied yet.
> after doing dbcc dbreindex with 90% factor. All dbs data and log files
> allocation space is very big (70-80% of acture data space). That means I
> have to do shrink db(both data and log) after reindex'
> Thanks
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>|||The problem is that data size is 5gb, that db doesn't have that much
transactions.
I do weekly reindex. after I finish db reindex the log size (allocation ) is
like 21 GB. I have to do shrink log file.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
> mecn,
> How much of it is data space and how much is log space? If the log became
> very large, that is easy to shrink, but shrinking the data is usually not
> a wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
> If you are actually working that close to full on your disks, then it is
> time to considering buying more space.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Sql2005 no sp applied yet.
>> after doing dbcc dbreindex with 90% factor. All dbs data and log files
>> allocation space is very big (70-80% of acture data space). That means I
>> have to do shrink db(both data and log) after reindex'
>> Thanks
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>>
>|||What recovery mode are you running? If you are in FULL and don't do any log
backups during the reindex process your log must be able to hold all the log
entries from the reindex process. If you are using DBCC INDEXDEFRAG this can
result in a lot of log entries. Try setting the recovery mode to simple just
before the reindex and then back to full when done. Just remember to do a
FULL backup to reinitialize the log chain.
--
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
> The problem is that data size is 5gb, that db doesn't have that much
> transactions.
> I do weekly reindex. after I finish db reindex the log size (allocation )
> is like 21 GB. I have to do shrink log file.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> mecn,
>> How much of it is data space and how much is log space? If the log
>> became very large, that is easy to shrink, but shrinking the data is
>> usually not a wise thing to do.
>> www.karaszi.com/SQLServer/info_dont_shrink.asp
>> If you are actually working that close to full on your disks, then it is
>> time to considering buying more space.
>> RLF
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Sql2005 no sp applied yet.
>> after doing dbcc dbreindex with 90% factor. All dbs data and log files
>> allocation space is very big (70-80% of acture data space). That means I
>> have to do shrink db(both data and log) after reindex'
>> Thanks
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>>
>>
>|||Full
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
> What recovery mode are you running? If you are in FULL and don't do any
> log backups during the reindex process your log must be able to hold all
> the log entries from the reindex process. If you are using DBCC
> INDEXDEFRAG this can result in a lot of log entries. Try setting the
> recovery mode to simple just before the reindex and then back to full when
> done. Just remember to do a FULL backup to reinitialize the log chain.
> --
> Andrew J. Kelly SQL MVP
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
>> The problem is that data size is 5gb, that db doesn't have that much
>> transactions.
>> I do weekly reindex. after I finish db reindex the log size (allocation )
>> is like 21 GB. I have to do shrink log file.
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> mecn,
>> How much of it is data space and how much is log space? If the log
>> became very large, that is easy to shrink, but shrinking the data is
>> usually not a wise thing to do.
>> www.karaszi.com/SQLServer/info_dont_shrink.asp
>> If you are actually working that close to full on your disks, then it is
>> time to considering buying more space.
>> RLF
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Sql2005 no sp applied yet.
>> after doing dbcc dbreindex with 90% factor. All dbs data and log files
>> allocation space is very big (70-80% of acture data space). That means
>> I have to do shrink db(both data and log) after reindex'
>> Thanks
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>>
>>
>>
>|||Well that is the answer then. If you are in FULL recovery mode every part of
the reindex will be fully logged and the log will grow when it runs out of
room.
--
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%232kuyUgzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> Full
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
>> What recovery mode are you running? If you are in FULL and don't do any
>> log backups during the reindex process your log must be able to hold all
>> the log entries from the reindex process. If you are using DBCC
>> INDEXDEFRAG this can result in a lot of log entries. Try setting the
>> recovery mode to simple just before the reindex and then back to full
>> when done. Just remember to do a FULL backup to reinitialize the log
>> chain.
>> --
>> Andrew J. Kelly SQL MVP
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
>> The problem is that data size is 5gb, that db doesn't have that much
>> transactions.
>> I do weekly reindex. after I finish db reindex the log size
>> (allocation ) is like 21 GB. I have to do shrink log file.
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> mecn,
>> How much of it is data space and how much is log space? If the log
>> became very large, that is easy to shrink, but shrinking the data is
>> usually not a wise thing to do.
>> www.karaszi.com/SQLServer/info_dont_shrink.asp
>> If you are actually working that close to full on your disks, then it
>> is time to considering buying more space.
>> RLF
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Sql2005 no sp applied yet.
>> after doing dbcc dbreindex with 90% factor. All dbs data and log files
>> allocation space is very big (70-80% of acture data space). That means
>> I have to do shrink db(both data and log) after reindex'
>> Thanks
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>>
>>
>>
>>
>

dbcc inputbuffer: looking for the actual statement

Hello all,
Is there a way to display (using TSQL) the actual statement that
a SPID is executing?
When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
Thanks
PS: I cannot use the profilerPossibly fn_get_sql can be of help. There are some things to think about whe
n using this (it was
introduced in SQL2K sp3), so Google and KB search and read about it first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Strider" <Strider@.discussions.microsoft.com> wrote in message
news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
> Hello all,
> Is there a way to display (using TSQL) the actual statement that
> a SPID is executing?
> When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
> Thanks
> PS: I cannot use the profiler|||Thank you Tibor.
This is just what I needed!
"Tibor Karaszi" wrote:

> Possibly fn_get_sql can be of help. There are some things to think about w
hen using this (it was
> introduced in SQL2K sp3), so Google and KB search and read about it first.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Strider" <Strider@.discussions.microsoft.com> wrote in message
> news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
>
>

dbcc inputbuffer: looking for the actual statement

Hello all,
Is there a way to display (using TSQL) the actual statement that
a SPID is executing?
When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
Thanks
PS: I cannot use the profilerPossibly fn_get_sql can be of help. There are some things to think about when using this (it was
introduced in SQL2K sp3), so Google and KB search and read about it first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Strider" <Strider@.discussions.microsoft.com> wrote in message
news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
> Hello all,
> Is there a way to display (using TSQL) the actual statement that
> a SPID is executing?
> When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
> Thanks
> PS: I cannot use the profiler|||Thank you Tibor.
This is just what I needed!
"Tibor Karaszi" wrote:
> Possibly fn_get_sql can be of help. There are some things to think about when using this (it was
> introduced in SQL2K sp3), so Google and KB search and read about it first.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Strider" <Strider@.discussions.microsoft.com> wrote in message
> news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
> > Hello all,
> >
> > Is there a way to display (using TSQL) the actual statement that
> > a SPID is executing?
> >
> > When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
> >
> > Thanks
> >
> > PS: I cannot use the profiler
>
>

dbcc inputbuffer: looking for the actual statement

Hello all,
Is there a way to display (using TSQL) the actual statement that
a SPID is executing?
When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
Thanks
PS: I cannot use the profiler
Possibly fn_get_sql can be of help. There are some things to think about when using this (it was
introduced in SQL2K sp3), so Google and KB search and read about it first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Strider" <Strider@.discussions.microsoft.com> wrote in message
news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
> Hello all,
> Is there a way to display (using TSQL) the actual statement that
> a SPID is executing?
> When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
> Thanks
> PS: I cannot use the profiler
|||Thank you Tibor.
This is just what I needed!
"Tibor Karaszi" wrote:

> Possibly fn_get_sql can be of help. There are some things to think about when using this (it was
> introduced in SQL2K sp3), so Google and KB search and read about it first.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Strider" <Strider@.discussions.microsoft.com> wrote in message
> news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
>
>

DBCC InputBuffer statement to be part of a select statement

I want to use DBCC inputbuffer function for all the spids in sysprocesses table without using a cursor or loop. In other words, I want to have the value of dbcc inputbuffer part of the select statement directly or indirectly. Is there a way??SQLDBAxxx,
Try running the following:

----------
set nocount on
select 'exec DBCC INPUTBUFFER('+convert(varchar,spid)+')'
from sysprocesses
go
----------

This will generate the DBCC code you need to run; it won't run
DBCC in the TSQL mode that you need. You can save the result set
and reload it to run it. Simple, but works...

I've never been able to execute a DBCC statement using ANSI-SQL syntax( select,update, insert ).|||SQLDBAxxx,
Try running the following:

----------
set nocount on
select 'DBCC INPUTBUFFER('+convert(varchar,spid)+')'
from sysprocesses
go
----------

This will generate the DBCC code you need to run; it won't run
DBCC in the TSQL mode that you need. You can save the result set
and reload it to run it. Simple, but works...

I've never been able to execute a DBCC statement using ANSI-SQL syntax( select,update, insert ).|||Thank you Scooter. But this is not what I wanted to do. I want to capture the input buffer for a given spid. I can do it thro a cursor or loop but if my recordset is big then by the time it comes to execute the dbcc inputbuffer for the last spid the inputbuffer may have changed. That is the reason I wanted to try by some other way to narrow down the time difference.

Dbcc Inputbuffer

Hi folks.
DBCC INPUTBUFFER(79)
the statement returned is too short, not full. Can i get full STATEMENT that is sent by the user.
Howdy!If you are using sp3a see "What's New" in the release notes. Otherwise, no.

-PatP|||Hi Pat, thanx.
select serverproperty('productlevel')
returns SP3.
There are BOOKSONLINE in the programs group only. Where to find the release notes.

Howdy!|||There is a new function in SP3 called fn_get_sql.

Here is an article:

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-14-fn_get_sql.htm

HTH,
Roby2222|||If you check the release notes for sp3a (http://www.microsoft.com/sql/downloads/2000/sp3.asp?SD=gn&LN=en-us&gssnb=1), you'll find a reference to fn_get_sql (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_3nqa.asp) that ought to solve your problem.

-PatP|||Thanx all.
But there's a problem.
i can get the STATEMENT only for SPIDs that r currently running. Not for those which have status SLEEPING.
DBCC INPUTBUFFER returns the last statment for SLEEPING connections but ::fn_get_sql doesn't.

eg. if i open QA , execute STATEMENT and don't disconnect, i can get the text from DBCCINPUTBUFFER but not from the function.

Howdy!

DBCC DBREINDEX vs DBCC INDEXDEFRAG

I am a little confused of which on does what by this Microsoft statement:
Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an
online operation, so it does not hold long-term locks that can block running
queries or updates. Depending on the amount of fragmentation, DBCC
INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a
relatively unfragmented index can be defragmented much faster than a new
index can be built. Another advantage is that with DBCC INDEXDEFRAG, the
index is always available, unlike DBREINDEX. A large amount of fragmentation
can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX,
which may or may not outweigh the benefit of the command's online
capabilities.
Which one is safe to use when users are logged in?
Thx,
DaveIn addition to Russel's excellent post, have a look at
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dave" <captan@.hotmail.com> wrote in message
news:%23GFGvXvSDHA.1920@.TK2MSFTNGP11.phx.gbl...
I am a little confused of which on does what by this Microsoft statement:
Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an
online operation, so it does not hold long-term locks that can block running
queries or updates. Depending on the amount of fragmentation, DBCC
INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a
relatively unfragmented index can be defragmented much faster than a new
index can be built. Another advantage is that with DBCC INDEXDEFRAG, the
index is always available, unlike DBREINDEX. A large amount of fragmentation
can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX,
which may or may not outweigh the benefit of the command's online
capabilities.
Which one is safe to use when users are logged in?
Thx,
Dave

DBCC DBReindex on NonClustered

If I execute the following statement on a nonclustered index there is no
reduction in fragmentation on the index:
DBCC DBREINDEX ('DBName.dbo.MyTable','IDX_MyTable_NonClustered',0) WITH
NO_INFOMSGS
The table on which I am performing the above statement has a clustered index
.
Is that the reason the above DBCC DBReindex would not reduce fragmentation
using the above statement? In other words, if a table has a clustered index,
then in order to reduce fragmentation on a nonclustered index in the same
table that has the clustered index, I would need to perform a DROP and CREAT
E
INDEX on the nonclustered index?
Message posted via http://www.droptable.comcbrichards via droptable.com wrote:
> If I execute the following statement on a nonclustered index there is no
> reduction in fragmentation on the index:
> DBCC DBREINDEX ('DBName.dbo.MyTable','IDX_MyTable_NonClustered',0) WITH
> NO_INFOMSGS
> The table on which I am performing the above statement has a clustered ind
ex.
> Is that the reason the above DBCC DBReindex would not reduce fragmentation
> using the above statement? In other words, if a table has a clustered inde
x,
> then in order to reduce fragmentation on a nonclustered index in the same
> table that has the clustered index, I would need to perform a DROP and CRE
ATE
> INDEX on the nonclustered index?
>
Please post the output of DBCC SHOWCONTIG for this index...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (862501003); index ID: 43, database ID: 61
LEAF level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Logical Scan Fragmentation ..............: 85.71%
- Extent Scan Fragmentation ...............: 42.86%
- Avg. Bytes Free per Page................: 488.7
- Avg. Page Density (full)................: 93.96%
DBCC execution completed. If DBCC printed error messages, contact your syste
m
administrator.
Tracy McKibben wrote:
>[quoted text clipped - 8 lines]
>Please post the output of DBCC SHOWCONTIG for this index...
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||Since your last reply I have performed the following operations:
I have dropped and recreated this nonclustered index, and there is no
difference in fragmentation.
I have dropped and recreated the clustered index, and there is no difference
in fragmentation on the clustered or nonclustered index.
Any ideas?
Tracy McKibben wrote:
>[quoted text clipped - 8 lines]
>Please post the output of DBCC SHOWCONTIG for this index...
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||Your index only has 7 pages in it. Anything less than 8 pages will use a
mixed extent. That means it will share as much as 8 extents and will always
show as fragmented. Only after you get more than 8 pages can you make them
all contiguous. But this is pretty moot anyway. I wouldn't worry about
anything with such few pages as I am sure there are more pressing issues
that you can address. I usually don't pay much attention to indexes with
less than several hundred pages at least.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:64eb85d653ff4@.uwe...
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (862501003); index ID: 43, database ID: 61
> LEAF level scan performed.
> - Pages Scanned........................: 7
> - Extents Scanned.......................: 7
> - Extent Switches.......................: 6
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
> - Logical Scan Fragmentation ..............: 85.71%
> - Extent Scan Fragmentation ...............: 42.86%
> - Avg. Bytes Free per Page................: 488.7
> - Avg. Page Density (full)................: 93.96%
> DBCC execution completed. If DBCC printed error messages, contact your
> system
> administrator.
> Tracy McKibben wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>

DBCC DBReindex on NonClustered

If I execute the following statement on a nonclustered index there is no
reduction in fragmentation on the index:
DBCC DBREINDEX ('DBName.dbo.MyTable','IDX_MyTable_NonClustered',0) WITH
NO_INFOMSGS
The table on which I am performing the above statement has a clustered index.
Is that the reason the above DBCC DBReindex would not reduce fragmentation
using the above statement? In other words, if a table has a clustered index,
then in order to reduce fragmentation on a nonclustered index in the same
table that has the clustered index, I would need to perform a DROP and CREATE
INDEX on the nonclustered index?
--
Message posted via http://www.sqlmonster.comcbrichards via SQLMonster.com wrote:
> If I execute the following statement on a nonclustered index there is no
> reduction in fragmentation on the index:
> DBCC DBREINDEX ('DBName.dbo.MyTable','IDX_MyTable_NonClustered',0) WITH
> NO_INFOMSGS
> The table on which I am performing the above statement has a clustered index.
> Is that the reason the above DBCC DBReindex would not reduce fragmentation
> using the above statement? In other words, if a table has a clustered index,
> then in order to reduce fragmentation on a nonclustered index in the same
> table that has the clustered index, I would need to perform a DROP and CREATE
> INDEX on the nonclustered index?
>
Please post the output of DBCC SHOWCONTIG for this index...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (862501003); index ID: 43, database ID: 61
LEAF level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Logical Scan Fragmentation ..............: 85.71%
- Extent Scan Fragmentation ...............: 42.86%
- Avg. Bytes Free per Page................: 488.7
- Avg. Page Density (full)................: 93.96%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Tracy McKibben wrote:
>> If I execute the following statement on a nonclustered index there is no
>> reduction in fragmentation on the index:
>[quoted text clipped - 8 lines]
>> table that has the clustered index, I would need to perform a DROP and CREATE
>> INDEX on the nonclustered index?
>Please post the output of DBCC SHOWCONTIG for this index...
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||Since your last reply I have performed the following operations:
I have dropped and recreated this nonclustered index, and there is no
difference in fragmentation.
I have dropped and recreated the clustered index, and there is no difference
in fragmentation on the clustered or nonclustered index.
Any ideas?
Tracy McKibben wrote:
>> If I execute the following statement on a nonclustered index there is no
>> reduction in fragmentation on the index:
>[quoted text clipped - 8 lines]
>> table that has the clustered index, I would need to perform a DROP and CREATE
>> INDEX on the nonclustered index?
>Please post the output of DBCC SHOWCONTIG for this index...
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||Your index only has 7 pages in it. Anything less than 8 pages will use a
mixed extent. That means it will share as much as 8 extents and will always
show as fragmented. Only after you get more than 8 pages can you make them
all contiguous. But this is pretty moot anyway. I wouldn't worry about
anything with such few pages as I am sure there are more pressing issues
that you can address. I usually don't pay much attention to indexes with
less than several hundred pages at least.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:64eb85d653ff4@.uwe...
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (862501003); index ID: 43, database ID: 61
> LEAF level scan performed.
> - Pages Scanned........................: 7
> - Extents Scanned.......................: 7
> - Extent Switches.......................: 6
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
> - Logical Scan Fragmentation ..............: 85.71%
> - Extent Scan Fragmentation ...............: 42.86%
> - Avg. Bytes Free per Page................: 488.7
> - Avg. Page Density (full)................: 93.96%
> DBCC execution completed. If DBCC printed error messages, contact your
> system
> administrator.
> Tracy McKibben wrote:
>> If I execute the following statement on a nonclustered index there is no
>> reduction in fragmentation on the index:
>>[quoted text clipped - 8 lines]
>> table that has the clustered index, I would need to perform a DROP and
>> CREATE
>> INDEX on the nonclustered index?
>>Please post the output of DBCC SHOWCONTIG for this index...
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>

DBCC CHECKTABLE on a View

I'm trying to run the following statement:
DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
But I get this error message:
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
So I run this statement:
SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
And get this result:
name id xtype uid info status base_schema_ver replinfo parent_obj crdate
ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
version deltrig instrig updtrig seltrig category cache
vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993 0
16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
(1 row(s) affected)
What is going on here? TIA!CHECKTABLE is checking physical consistency of the data. A view doesn't store any data so there is
nothing to check. Run CHECKTABLE on the tables that the view is querying instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
> I'm trying to run the following statement:
> DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> But I get this error message:
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
> So I run this statement:
> SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> And get this result:
> name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
> version deltrig instrig updtrig seltrig category cache
> vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993 0
> 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> (1 row(s) affected)
> What is going on here? TIA!
>|||Thanks! I'm taking your word for it, but that seems to be in conflict with
what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
DBCC CHECKTABLE
Checks the integrity of the data, index, text, ntext, and image pages for
the specified table or indexed view.
Syntax
DBCC CHECKTABLE
( 'table_name' | 'view_name'
[ , NOINDEX
| index_id
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD }
]
) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> CHECKTABLE is checking physical consistency of the data. A view doesn't
store any data so there is
> nothing to check. Run CHECKTABLE on the tables that the view is querying
instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
message
> news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
> > I'm trying to run the following statement:
> >
> > DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> >
> > But I get this error message:
> >
> > Server: Msg 2501, Level 16, State 1, Line 1
> > Could not find a table or object named 'vLostSaleCount'. Check
sysobjects.
> >
> > So I run this statement:
> >
> > SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> >
> > And get this result:
> >
> > name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> > ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel
refdate
> > version deltrig instrig updtrig seltrig category cache
> > vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03
13:19:14.993 0
> > 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> >
> > (1 row(s) affected)
> >
> > What is going on here? TIA!
> >
> >
>|||Is the view qualified with a name other than DBO?
"Ron Hinds" wrote:
> I'm trying to run the following statement:
> DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> But I get this error message:
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
> So I run this statement:
> SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> And get this result:
> name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
> version deltrig instrig updtrig seltrig category cache
> vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993 0
> 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> (1 row(s) affected)
> What is going on here? TIA!
>
>|||No - I even tried qualifying it with dbo and still got the error.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:0309BF46-E4A8-4F84-8B12-68B767AE40B6@.microsoft.com...
> Is the view qualified with a name other than DBO?
> "Ron Hinds" wrote:
> > I'm trying to run the following statement:
> >
> > DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> >
> > But I get this error message:
> >
> > Server: Msg 2501, Level 16, State 1, Line 1
> > Could not find a table or object named 'vLostSaleCount'. Check
sysobjects.
> >
> > So I run this statement:
> >
> > SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> >
> > And get this result:
> >
> > name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> > ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel
refdate
> > version deltrig instrig updtrig seltrig category cache
> > vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03
13:19:14.993 0
> > 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> >
> > (1 row(s) affected)
> >
> > What is going on here? TIA!
> >
> >
> >|||BOL is talking about indexed views.
An indexed view is not a 'normal' view and use database storage.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ron Hinds" wrote:
> Thanks! I'm taking your word for it, but that seems to be in conflict with
> what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
> DBCC CHECKTABLE
> Checks the integrity of the data, index, text, ntext, and image pages for
> the specified table or indexed view.
> Syntax
> DBCC CHECKTABLE
> ( 'table_name' | 'view_name'
> [ , NOINDEX
> | index_id
> | { REPAIR_ALLOW_DATA_LOSS
> | REPAIR_FAST
> | REPAIR_REBUILD }
> ]
> ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> [ , [ TABLOCK ] ]
> [ , [ ESTIMATEONLY ] ]
> [ , [ PHYSICAL_ONLY ] ]
> }
> ]
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> > CHECKTABLE is checking physical consistency of the data. A view doesn't
> store any data so there is
> > nothing to check. Run CHECKTABLE on the tables that the view is querying
> instead.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
> message
> > news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
> > > I'm trying to run the following statement:
> > >
> > > DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> > >
> > > But I get this error message:
> > >
> > > Server: Msg 2501, Level 16, State 1, Line 1
> > > Could not find a table or object named 'vLostSaleCount'. Check
> sysobjects.
> > >
> > > So I run this statement:
> > >
> > > SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> > >
> > > And get this result:
> > >
> > > name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> > > ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel
> refdate
> > > version deltrig instrig updtrig seltrig category cache
> > > vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03
> 13:19:14.993 0
> > > 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> > >
> > > (1 row(s) affected)
> > >
> > > What is going on here? TIA!
> > >
> > >
> >
>
>|||Note "*indexed* view". Do you have an index on that view?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23I6bXxVPGHA.2236@.TK2MSFTNGP15.phx.gbl...
> Thanks! I'm taking your word for it, but that seems to be in conflict with
> what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
> DBCC CHECKTABLE
> Checks the integrity of the data, index, text, ntext, and image pages for
> the specified table or indexed view.
> Syntax
> DBCC CHECKTABLE
> ( 'table_name' | 'view_name'
> [ , NOINDEX
> | index_id
> | { REPAIR_ALLOW_DATA_LOSS
> | REPAIR_FAST
> | REPAIR_REBUILD }
> ]
> ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> [ , [ TABLOCK ] ]
> [ , [ ESTIMATEONLY ] ]
> [ , [ PHYSICAL_ONLY ] ]
> }
> ]
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
>> CHECKTABLE is checking physical consistency of the data. A view doesn't
> store any data so there is
>> nothing to check. Run CHECKTABLE on the tables that the view is querying
> instead.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
> message
>> news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
>> > I'm trying to run the following statement:
>> >
>> > DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
>> >
>> > But I get this error message:
>> >
>> > Server: Msg 2501, Level 16, State 1, Line 1
>> > Could not find a table or object named 'vLostSaleCount'. Check
> sysobjects.
>> >
>> > So I run this statement:
>> >
>> > SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
>> >
>> > And get this result:
>> >
>> > name id xtype uid info status base_schema_ver replinfo parent_obj crdate
>> > ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel
> refdate
>> > version deltrig instrig updtrig seltrig category cache
>> > vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03
> 13:19:14.993 0
>> > 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
>> >
>> > (1 row(s) affected)
>> >
>> > What is going on here? TIA!
>> >
>> >
>|||I see. Thanks guys!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uA5Qo$VPGHA.720@.TK2MSFTNGP14.phx.gbl...
> Note "*indexed* view". Do you have an index on that view?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
message
> news:%23I6bXxVPGHA.2236@.TK2MSFTNGP15.phx.gbl...
> > Thanks! I'm taking your word for it, but that seems to be in conflict
with
> > what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
> >
> > DBCC CHECKTABLE
> > Checks the integrity of the data, index, text, ntext, and image pages
for
> > the specified table or indexed view.
> >
> > Syntax
> > DBCC CHECKTABLE
> > ( 'table_name' | 'view_name'
> > [ , NOINDEX
> > | index_id
> > | { REPAIR_ALLOW_DATA_LOSS
> > | REPAIR_FAST
> > | REPAIR_REBUILD }
> > ]
> > ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> > [ , [ TABLOCK ] ]
> > [ , [ ESTIMATEONLY ] ]
> > [ , [ PHYSICAL_ONLY ] ]
> > }
> > ]
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> >> CHECKTABLE is checking physical consistency of the data. A view doesn't
> > store any data so there is
> >> nothing to check. Run CHECKTABLE on the tables that the view is
querying
> > instead.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
> > message
> >> news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
> >> > I'm trying to run the following statement:
> >> >
> >> > DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> >> >
> >> > But I get this error message:
> >> >
> >> > Server: Msg 2501, Level 16, State 1, Line 1
> >> > Could not find a table or object named 'vLostSaleCount'. Check
> > sysobjects.
> >> >
> >> > So I run this statement:
> >> >
> >> > SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> >> >
> >> > And get this result:
> >> >
> >> > name id xtype uid info status base_schema_ver replinfo parent_obj
crdate
> >> > ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel
> > refdate
> >> > version deltrig instrig updtrig seltrig category cache
> >> > vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03
> > 13:19:14.993 0
> >> > 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> >> >
> >> > (1 row(s) affected)
> >> >
> >> > What is going on here? TIA!
> >> >
> >> >
> >>
> >
> >
>