DBCC SHOWCONTIG - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DBCC SHOWCONTIG

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-07 : 10:23:22

If i do a dbcc showcontig i see avg bytes free 1658.7

Data page 8kb extent 64kb
how many rows of data would that be

Trying to determine how many records i can insert into a table before the page split would occur

if it says 1000 bytes would that be 8 rows ?
so the 1658.7 would give me say 5 rows

When page split (would it be an entire extent or just another page)

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-09 : 15:50:11

Depends on which table uses the page and row size of the table.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-09 : 19:59:21

The 1658 is the size of the bytes
i was trying to decipher how many rows would fit on a page im thinking 5

If one record is read then i would see 1658 in sysprocesses

Thanks

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-09 : 20:04:05

DBCC UPDATEUSAGE corrects the rows, used, reserved, and dpages columns of the sysindexes table for tables and clustered indexes.
(In the maintenance plans which option is this)

It's different from update statistics. And you can turn off 'auto update statistics' if you like maintenance plan to handle it.
if db has auto update statistics (then to run this at night i would have to do which command)

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-09 : 20:15:53

There is no option in a maintenance plan to run dbcc updateusage.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-10 : 10:39:36

I believe tracesql means you can turn off 'auto update statistics' if you update statistics in maintenance plan.

For number of rows per page, you can calculate it like page size/row size. So in this case, 8060/1658, is 4 rows per page.

Kristen
Test

22859 Posts

Posted - 2007-04-10 : 15:32:33

rmiao:

My view is that it is A Good Thing to run an UPDATE STATISTICS frequently (in our case every night).

And having done an Update Stats EVERY night I then take the viewpoint that 'auto update statistics' is necessary because if the system detects, during the following day, that a particular statistics is "stale", then I'd prefer the total-system-slowdown of fixing that, rather than limping on until the overnight-job to update that statistic.

I am of course confident that the rest of the Statistics is recently-updated and likely to be fit-for-purpose.

But I'd be interested in your view - not least because lots of the things you've written in the last 24 hours-or-so make a lot of sense from where I am sitting! (Since you joined you've raised yourself from a position on page 1352 to page 72 (next time pleas e note that using the User Name "Aardvark" will gain you a couple of pages in the Hall Of Fame!!)

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-10 : 16:06:40

Auto update and auto create stats should be on, plus a scheduled job for update stats. There is a lot of information over at sql-server-performance.com on why. I just don't have the article handy.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-10 : 18:10:19

Thanks i have a look around.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-11 : 10:25:14

kristen, agree with you. Should do it either with own script or in maintenance plan.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-04-11 : 11:57:51

i changed some of my tables to do fill factor and now want to schedule auto script for this.

http://sigs.sqlpass.org/Articles/tabid/35/ctl/ArticleView/mid/349/articleId/1/AutomatedIndexAnalysisandMaintenancebyRandyDyess.aspx

how do i change this from his name
--Add owner name to object name rdyess

UPDATE #fraglist

SET ObjectName = (SELECT su.name + '.' + fl.ObjectName

FROM #fraglist fl

INNER JOIN rdyess_dbsecurity.dbo.sysobjects so

ON fl.ObjectId = so.id

INNER JOIN rdyess_dbsecurity.dbo.sysusers su

ON so.uid = su.uid

WHERE fl.ObjectId = #fraglist.ObjectId

AND fl.IndexName = #fraglist.IndexName)

-----------------
Then should i perform update statitics after this.

--
How do i check it is updating all statistics is this in sysindexes

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources