When I use ShrinkDB or reindex and some other DB performance utilities, then when I use DBCC memusage it throws some error however it get corrected when I restart SQL Server
Is there any simplest way to correct this problem without restarting the SQL server
Regards
SunilCan you share the error? We might be able to debug this more easily.
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:41FCD39E-3F6F-44B7-88FE-31ADC023397B@.microsoft.com...
> When I use ShrinkDB or reindex and some other DB performance utilities,
then when I use DBCC memusage it throws some error however it get corrected
when I restart SQL Server.
>
> Is there any simplest way to correct this problem without restarting the
SQL server.
>
> Regards,
> Sunil|||This error is coming
--
Server: Msg 8966, Level 16, State 4, Line
Could not read and latch page (5:562) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1621) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1622) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1623) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1809) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1810) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1811) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1812) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1813) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1814) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1815) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1816) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1840) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1841) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1842) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1843) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1844) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1845) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1846) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1847) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1848) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1863) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1864) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1865) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1866) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1867) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1868) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1869) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1870) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1871) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1872) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1873) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1874) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1849) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1850) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1851) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1852) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1875) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1876) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1877) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1878) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1488) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1489) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1490) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1560) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1561) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1562) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1563) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1564) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1565) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1567) with latch type SH. VerifyPageId failed.
Could not read and latch page (1:811) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
BCC execution completed. If DBCC printed error messages, contact your system administrator.
Showing posts with label memusage. Show all posts
Showing posts with label memusage. Show all posts
Monday, March 19, 2012
DBCC memusage problem
When I use ShrinkDB or reindex and some other DB performance utilities, then
when I use DBCC memusage it throws some error however it get corrected when
I restart SQL Server.
Is there any simplest way to correct this problem without restarting the SQL
server.
Regards,
SunilCan you share the error? We might be able to debug this more easily.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:41FCD39E-3F6F-44B7-88FE-31ADC023397B@.microsoft.com...
> When I use ShrinkDB or reindex and some other DB performance utilities,
then when I use DBCC memusage it throws some error however it get corrected
when I restart SQL Server.
>
> Is there any simplest way to correct this problem without restarting the
SQL server.
>
> Regards,
> Sunil|||This error is coming :
--
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (5:562) with latch type SH. VerifyPageId faile
d.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1621) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1622) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1623) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1809) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1810) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1811) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1812) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1813) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1814) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1815) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1816) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1840) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1841) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1842) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1843) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1844) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1845) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1846) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1847) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1848) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1863) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1864) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1865) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1866) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1867) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1868) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1869) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1870) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1871) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1872) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1873) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1874) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1849) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1850) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1851) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1852) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1875) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1876) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1877) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1878) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1488) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1489) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1490) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1560) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1561) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1562) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1563) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1564) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1565) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1567) with latch type SH. VerifyPageId fail
ed.
Could not read and latch page (1:811) with latch type SH. VerifyPageId faile
d.
Server: Msg 8966, Level 16, State 1, Line 1
BCC execution completed. If DBCC printed error messages, contact your system
administrator.
when I use DBCC memusage it throws some error however it get corrected when
I restart SQL Server.
Is there any simplest way to correct this problem without restarting the SQL
server.
Regards,
SunilCan you share the error? We might be able to debug this more easily.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:41FCD39E-3F6F-44B7-88FE-31ADC023397B@.microsoft.com...
> When I use ShrinkDB or reindex and some other DB performance utilities,
then when I use DBCC memusage it throws some error however it get corrected
when I restart SQL Server.
>
> Is there any simplest way to correct this problem without restarting the
SQL server.
>
> Regards,
> Sunil|||This error is coming :
--
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (5:562) with latch type SH. VerifyPageId faile
d.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1621) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1622) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1623) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1809) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1810) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1811) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1812) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1813) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1814) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1815) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1816) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1840) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1841) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1842) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1843) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1844) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1845) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1846) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1847) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1848) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1863) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1864) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1865) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1866) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1867) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1868) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1869) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1870) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1871) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1872) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1873) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1874) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1849) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1850) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1851) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1852) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1875) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1876) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1877) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1878) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1488) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1489) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1490) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1560) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1561) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1562) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1563) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1564) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1565) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1567) with latch type SH. VerifyPageId fail
ed.
Could not read and latch page (1:811) with latch type SH. VerifyPageId faile
d.
Server: Msg 8966, Level 16, State 1, Line 1
BCC execution completed. If DBCC printed error messages, contact your system
administrator.
DBCC memusage problem
When I use ShrinkDB or reindex and some other DB performance utilities, then when I use DBCC memusage it throws some error however it get corrected when I restart SQL Server.
Is there any simplest way to correct this problem without restarting the SQL server.
Regards,
Sunil
Can you share the error? We might be able to debug this more easily.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:41FCD39E-3F6F-44B7-88FE-31ADC023397B@.microsoft.com...
> When I use ShrinkDB or reindex and some other DB performance utilities,
then when I use DBCC memusage it throws some error however it get corrected
when I restart SQL Server.
>
> Is there any simplest way to correct this problem without restarting the
SQL server.
>
> Regards,
> Sunil
|||This error is coming :
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (5:562) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1621) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1622) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1623) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1809) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1810) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1811) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1812) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1813) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1814) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1815) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1816) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1840) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1841) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1842) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1843) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1844) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1845) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1846) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1847) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1848) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1863) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1864) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1865) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1866) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1867) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1868) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1869) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1870) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1871) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1872) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1873) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1874) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1849) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1850) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1851) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1852) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1875) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1876) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1877) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1878) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1488) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1489) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1490) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1560) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1561) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1562) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1563) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1564) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1565) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1567) with latch type SH. VerifyPageId failed.
Could not read and latch page (1:811) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
BCC execution completed. If DBCC printed error messages, contact your system administrator.
Is there any simplest way to correct this problem without restarting the SQL server.
Regards,
Sunil
Can you share the error? We might be able to debug this more easily.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:41FCD39E-3F6F-44B7-88FE-31ADC023397B@.microsoft.com...
> When I use ShrinkDB or reindex and some other DB performance utilities,
then when I use DBCC memusage it throws some error however it get corrected
when I restart SQL Server.
>
> Is there any simplest way to correct this problem without restarting the
SQL server.
>
> Regards,
> Sunil
|||This error is coming :
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (5:562) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1621) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1622) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1623) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1809) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1810) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1811) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1812) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1813) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1814) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1815) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1816) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1840) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1841) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1842) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1843) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1844) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1845) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1846) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1847) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1848) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1863) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1864) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1865) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1866) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1867) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1868) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1869) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1870) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1871) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1872) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1873) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1874) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1849) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1850) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1851) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1852) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1875) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1876) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1877) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1878) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1488) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1489) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1490) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1560) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1561) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1562) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1563) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1564) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1565) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1567) with latch type SH. VerifyPageId failed.
Could not read and latch page (1:811) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
BCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC memusage
DBCC memusage reports the buffers used by a particalur object to be far and
away more than any other object. I'm surpised as this table is a historical
system message table for the app that resides on the DB. How does an object
get buffers(I assume reading and writing to the object)? All the views in
the system do select * from tablename, so why this object and not another
transactional table? The ratio of buffers between the 1st and 2nd object is
like 170,000 to 3000. I'm hoping to get clearance to purge this table as our
system is IO bound, disk channels are pegged at 100% and all of it is read
activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
channels RAID5, quad procs with hyperthreading and performance is dismal.Has anyone used DBCC PINTABLE on this table?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Paul's question would be my first guess as well. If that isn't it then you
should run a trace to see how that table is being accessed and how often.
If it is being queried enough and it does a scan it can certainly lead to
this type behavior. When you say "4 disk channels RAID 5" do you mean you
actually have 4 different RAID 5 arrays each on their own channel? Or a 4
disk RAID 5?
--
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Negative on the pin table aspect although I've been comtemplating pinning a
table myself. I got clearance to purge the table and after purging 60 days
worth of data, other objects are starting to show more than a few hundred
cache buffers. However, another object that holds useless historical data is
showing as the top object in memusage. The first table is accessed for evey
message the app server generates but the base view is a select *. Same for
evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
All data was on one logical drive pegged at 100% utilization and long disk
queues. After moving data around, the three channels are pegged or nearly
pegged all the time. Reads and readaheds are accounting for the usage.
"Andrew J. Kelly" wrote:
> Paul's question would be my first guess as well. If that isn't it then you
> should run a trace to see how that table is being accessed and how often.
> If it is being queried enough and it does a scan it can certainly lead to
> this type behavior. When you say "4 disk channels RAID 5" do you mean you
> actually have 4 different RAID 5 arrays each on their own channel? Or a 4
> disk RAID 5?
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> > DBCC memusage reports the buffers used by a particalur object to be far
> > and
> > away more than any other object. I'm surpised as this table is a
> > historical
> > system message table for the app that resides on the DB. How does an
> > object
> > get buffers(I assume reading and writing to the object)? All the views in
> > the system do select * from tablename, so why this object and not another
> > transactional table? The ratio of buffers between the 1st and 2nd object
> > is
> > like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> > our
> > system is IO bound, disk channels are pegged at 100% and all of it is read
> > activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> > channels RAID5, quad procs with hyperthreading and performance is dismal.
>
>|||If you have 3 separate arrays and all the channels are pegged you are
probably doing way too much access. You must be scanning most tables (or at
least these big ones you are mentioning) a lot. By the way pinning the
table is usually not a good idea and will go away in 2005 anyway. Sounds
like you just need to optimize your code and or tables so you do more seeks
than scans. You would be amazed that most systems hve just a few calls or
sps that eat up most of the I/O. Once you tackle those others will pop to
the top but you can do a lot of damage control by tuning the top x calls.
--
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
> Negative on the pin table aspect although I've been comtemplating pinning
> a
> table myself. I got clearance to purge the table and after purging 60
> days
> worth of data, other objects are starting to show more than a few hundred
> cache buffers. However, another object that holds useless historical data
> is
> showing as the top object in memusage. The first table is accessed for
> evey
> message the app server generates but the base view is a select *. Same
> for
> evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> All data was on one logical drive pegged at 100% utilization and long disk
> queues. After moving data around, the three channels are pegged or nearly
> pegged all the time. Reads and readaheds are accounting for the usage.
> "Andrew J. Kelly" wrote:
>> Paul's question would be my first guess as well. If that isn't it then
>> you
>> should run a trace to see how that table is being accessed and how often.
>> If it is being queried enough and it does a scan it can certainly lead to
>> this type behavior. When you say "4 disk channels RAID 5" do you mean
>> you
>> actually have 4 different RAID 5 arrays each on their own channel? Or a
>> 4
>> disk RAID 5?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
>> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>> > DBCC memusage reports the buffers used by a particalur object to be far
>> > and
>> > away more than any other object. I'm surpised as this table is a
>> > historical
>> > system message table for the app that resides on the DB. How does an
>> > object
>> > get buffers(I assume reading and writing to the object)? All the views
>> > in
>> > the system do select * from tablename, so why this object and not
>> > another
>> > transactional table? The ratio of buffers between the 1st and 2nd
>> > object
>> > is
>> > like 170,000 to 3000. I'm hoping to get clearance to purge this table
>> > as
>> > our
>> > system is IO bound, disk channels are pegged at 100% and all of it is
>> > read
>> > activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4
>> > disk
>> > channels RAID5, quad procs with hyperthreading and performance is
>> > dismal.
>>|||I found that the table in question is accessed by every transaction that hits
the system and typically we add 7500-10000 records a day. No data had been
purged for 2 years. Once I purged this and another historical table, the
buffer count leveled out across the top 20 objects.
Now I find out that there is a little monitoring app that hits all the
messaging tables in the system to report status. This is keeping the table
well cached and keeping other transactional tables out of cache. Is there an
opposite of pintable, I'd like to excluded this and a few other tables from
cache if at all possible until a data purge process is implemented.
"Andrew J. Kelly" wrote:
> If you have 3 separate arrays and all the channels are pegged you are
> probably doing way too much access. You must be scanning most tables (or at
> least these big ones you are mentioning) a lot. By the way pinning the
> table is usually not a good idea and will go away in 2005 anyway. Sounds
> like you just need to optimize your code and or tables so you do more seeks
> than scans. You would be amazed that most systems hve just a few calls or
> sps that eat up most of the I/O. Once you tackle those others will pop to
> the top but you can do a lot of damage control by tuning the top x calls.
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
> > Negative on the pin table aspect although I've been comtemplating pinning
> > a
> > table myself. I got clearance to purge the table and after purging 60
> > days
> > worth of data, other objects are starting to show more than a few hundred
> > cache buffers. However, another object that holds useless historical data
> > is
> > showing as the top object in memusage. The first table is accessed for
> > evey
> > message the app server generates but the base view is a select *. Same
> > for
> > evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> > array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> > All data was on one logical drive pegged at 100% utilization and long disk
> > queues. After moving data around, the three channels are pegged or nearly
> > pegged all the time. Reads and readaheds are accounting for the usage.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Paul's question would be my first guess as well. If that isn't it then
> >> you
> >> should run a trace to see how that table is being accessed and how often.
> >> If it is being queried enough and it does a scan it can certainly lead to
> >> this type behavior. When you say "4 disk channels RAID 5" do you mean
> >> you
> >> actually have 4 different RAID 5 arrays each on their own channel? Or a
> >> 4
> >> disk RAID 5?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> >> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> >> > DBCC memusage reports the buffers used by a particalur object to be far
> >> > and
> >> > away more than any other object. I'm surpised as this table is a
> >> > historical
> >> > system message table for the app that resides on the DB. How does an
> >> > object
> >> > get buffers(I assume reading and writing to the object)? All the views
> >> > in
> >> > the system do select * from tablename, so why this object and not
> >> > another
> >> > transactional table? The ratio of buffers between the 1st and 2nd
> >> > object
> >> > is
> >> > like 170,000 to 3000. I'm hoping to get clearance to purge this table
> >> > as
> >> > our
> >> > system is IO bound, disk channels are pegged at 100% and all of it is
> >> > read
> >> > activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4
> >> > disk
> >> > channels RAID5, quad procs with hyperthreading and performance is
> >> > dismal.
> >>
> >>
> >>
>
>|||No there isn't anything like that other than to clear the whole cache with
DBCC DROPClEANBUFFERS. SQL Server is pretty good about keeping in memory
what is used most often. If these tables are accessed that often and were
not in cache you would have to go to disk each time and pay that penalty.
It only caches what it reads so maybe if you tuned those status queries you
would read less data and free up that memory for other things. Maybe an
Indexed view would help with status type queries?
--
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:5505B468-AC5D-4368-93CD-10D0E0309D35@.microsoft.com...
>I found that the table in question is accessed by every transaction that
>hits
> the system and typically we add 7500-10000 records a day. No data had
> been
> purged for 2 years. Once I purged this and another historical table, the
> buffer count leveled out across the top 20 objects.
> Now I find out that there is a little monitoring app that hits all the
> messaging tables in the system to report status. This is keeping the
> table
> well cached and keeping other transactional tables out of cache. Is there
> an
> opposite of pintable, I'd like to excluded this and a few other tables
> from
> cache if at all possible until a data purge process is implemented.
> "Andrew J. Kelly" wrote:
>> If you have 3 separate arrays and all the channels are pegged you are
>> probably doing way too much access. You must be scanning most tables (or
>> at
>> least these big ones you are mentioning) a lot. By the way pinning the
>> table is usually not a good idea and will go away in 2005 anyway. Sounds
>> like you just need to optimize your code and or tables so you do more
>> seeks
>> than scans. You would be amazed that most systems hve just a few calls
>> or
>> sps that eat up most of the I/O. Once you tackle those others will pop
>> to
>> the top but you can do a lot of damage control by tuning the top x calls.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
>> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
>> > Negative on the pin table aspect although I've been comtemplating
>> > pinning
>> > a
>> > table myself. I got clearance to purge the table and after purging 60
>> > days
>> > worth of data, other objects are starting to show more than a few
>> > hundred
>> > cache buffers. However, another object that holds useless historical
>> > data
>> > is
>> > showing as the top object in memusage. The first table is accessed for
>> > evey
>> > message the app server generates but the base view is a select *. Same
>> > for
>> > evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
>> > array.(Dell 6600 with a 22Os and two dual channel 2960 PERC
>> > controllers.)
>> > All data was on one logical drive pegged at 100% utilization and long
>> > disk
>> > queues. After moving data around, the three channels are pegged or
>> > nearly
>> > pegged all the time. Reads and readaheds are accounting for the usage.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Paul's question would be my first guess as well. If that isn't it
>> >> then
>> >> you
>> >> should run a trace to see how that table is being accessed and how
>> >> often.
>> >> If it is being queried enough and it does a scan it can certainly lead
>> >> to
>> >> this type behavior. When you say "4 disk channels RAID 5" do you mean
>> >> you
>> >> actually have 4 different RAID 5 arrays each on their own channel? Or
>> >> a
>> >> 4
>> >> disk RAID 5?
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote
>> >> in
>> >> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>> >> > DBCC memusage reports the buffers used by a particalur object to be
>> >> > far
>> >> > and
>> >> > away more than any other object. I'm surpised as this table is a
>> >> > historical
>> >> > system message table for the app that resides on the DB. How does
>> >> > an
>> >> > object
>> >> > get buffers(I assume reading and writing to the object)? All the
>> >> > views
>> >> > in
>> >> > the system do select * from tablename, so why this object and not
>> >> > another
>> >> > transactional table? The ratio of buffers between the 1st and 2nd
>> >> > object
>> >> > is
>> >> > like 170,000 to 3000. I'm hoping to get clearance to purge this
>> >> > table
>> >> > as
>> >> > our
>> >> > system is IO bound, disk channels are pegged at 100% and all of it
>> >> > is
>> >> > read
>> >> > activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4
>> >> > disk
>> >> > channels RAID5, quad procs with hyperthreading and performance is
>> >> > dismal.
>> >>
>> >>
>> >>
>>
away more than any other object. I'm surpised as this table is a historical
system message table for the app that resides on the DB. How does an object
get buffers(I assume reading and writing to the object)? All the views in
the system do select * from tablename, so why this object and not another
transactional table? The ratio of buffers between the 1st and 2nd object is
like 170,000 to 3000. I'm hoping to get clearance to purge this table as our
system is IO bound, disk channels are pegged at 100% and all of it is read
activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
channels RAID5, quad procs with hyperthreading and performance is dismal.Has anyone used DBCC PINTABLE on this table?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Paul's question would be my first guess as well. If that isn't it then you
should run a trace to see how that table is being accessed and how often.
If it is being queried enough and it does a scan it can certainly lead to
this type behavior. When you say "4 disk channels RAID 5" do you mean you
actually have 4 different RAID 5 arrays each on their own channel? Or a 4
disk RAID 5?
--
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Negative on the pin table aspect although I've been comtemplating pinning a
table myself. I got clearance to purge the table and after purging 60 days
worth of data, other objects are starting to show more than a few hundred
cache buffers. However, another object that holds useless historical data is
showing as the top object in memusage. The first table is accessed for evey
message the app server generates but the base view is a select *. Same for
evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
All data was on one logical drive pegged at 100% utilization and long disk
queues. After moving data around, the three channels are pegged or nearly
pegged all the time. Reads and readaheds are accounting for the usage.
"Andrew J. Kelly" wrote:
> Paul's question would be my first guess as well. If that isn't it then you
> should run a trace to see how that table is being accessed and how often.
> If it is being queried enough and it does a scan it can certainly lead to
> this type behavior. When you say "4 disk channels RAID 5" do you mean you
> actually have 4 different RAID 5 arrays each on their own channel? Or a 4
> disk RAID 5?
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> > DBCC memusage reports the buffers used by a particalur object to be far
> > and
> > away more than any other object. I'm surpised as this table is a
> > historical
> > system message table for the app that resides on the DB. How does an
> > object
> > get buffers(I assume reading and writing to the object)? All the views in
> > the system do select * from tablename, so why this object and not another
> > transactional table? The ratio of buffers between the 1st and 2nd object
> > is
> > like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> > our
> > system is IO bound, disk channels are pegged at 100% and all of it is read
> > activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> > channels RAID5, quad procs with hyperthreading and performance is dismal.
>
>|||If you have 3 separate arrays and all the channels are pegged you are
probably doing way too much access. You must be scanning most tables (or at
least these big ones you are mentioning) a lot. By the way pinning the
table is usually not a good idea and will go away in 2005 anyway. Sounds
like you just need to optimize your code and or tables so you do more seeks
than scans. You would be amazed that most systems hve just a few calls or
sps that eat up most of the I/O. Once you tackle those others will pop to
the top but you can do a lot of damage control by tuning the top x calls.
--
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
> Negative on the pin table aspect although I've been comtemplating pinning
> a
> table myself. I got clearance to purge the table and after purging 60
> days
> worth of data, other objects are starting to show more than a few hundred
> cache buffers. However, another object that holds useless historical data
> is
> showing as the top object in memusage. The first table is accessed for
> evey
> message the app server generates but the base view is a select *. Same
> for
> evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> All data was on one logical drive pegged at 100% utilization and long disk
> queues. After moving data around, the three channels are pegged or nearly
> pegged all the time. Reads and readaheds are accounting for the usage.
> "Andrew J. Kelly" wrote:
>> Paul's question would be my first guess as well. If that isn't it then
>> you
>> should run a trace to see how that table is being accessed and how often.
>> If it is being queried enough and it does a scan it can certainly lead to
>> this type behavior. When you say "4 disk channels RAID 5" do you mean
>> you
>> actually have 4 different RAID 5 arrays each on their own channel? Or a
>> 4
>> disk RAID 5?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
>> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>> > DBCC memusage reports the buffers used by a particalur object to be far
>> > and
>> > away more than any other object. I'm surpised as this table is a
>> > historical
>> > system message table for the app that resides on the DB. How does an
>> > object
>> > get buffers(I assume reading and writing to the object)? All the views
>> > in
>> > the system do select * from tablename, so why this object and not
>> > another
>> > transactional table? The ratio of buffers between the 1st and 2nd
>> > object
>> > is
>> > like 170,000 to 3000. I'm hoping to get clearance to purge this table
>> > as
>> > our
>> > system is IO bound, disk channels are pegged at 100% and all of it is
>> > read
>> > activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4
>> > disk
>> > channels RAID5, quad procs with hyperthreading and performance is
>> > dismal.
>>|||I found that the table in question is accessed by every transaction that hits
the system and typically we add 7500-10000 records a day. No data had been
purged for 2 years. Once I purged this and another historical table, the
buffer count leveled out across the top 20 objects.
Now I find out that there is a little monitoring app that hits all the
messaging tables in the system to report status. This is keeping the table
well cached and keeping other transactional tables out of cache. Is there an
opposite of pintable, I'd like to excluded this and a few other tables from
cache if at all possible until a data purge process is implemented.
"Andrew J. Kelly" wrote:
> If you have 3 separate arrays and all the channels are pegged you are
> probably doing way too much access. You must be scanning most tables (or at
> least these big ones you are mentioning) a lot. By the way pinning the
> table is usually not a good idea and will go away in 2005 anyway. Sounds
> like you just need to optimize your code and or tables so you do more seeks
> than scans. You would be amazed that most systems hve just a few calls or
> sps that eat up most of the I/O. Once you tackle those others will pop to
> the top but you can do a lot of damage control by tuning the top x calls.
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
> > Negative on the pin table aspect although I've been comtemplating pinning
> > a
> > table myself. I got clearance to purge the table and after purging 60
> > days
> > worth of data, other objects are starting to show more than a few hundred
> > cache buffers. However, another object that holds useless historical data
> > is
> > showing as the top object in memusage. The first table is accessed for
> > evey
> > message the app server generates but the base view is a select *. Same
> > for
> > evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> > array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> > All data was on one logical drive pegged at 100% utilization and long disk
> > queues. After moving data around, the three channels are pegged or nearly
> > pegged all the time. Reads and readaheds are accounting for the usage.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Paul's question would be my first guess as well. If that isn't it then
> >> you
> >> should run a trace to see how that table is being accessed and how often.
> >> If it is being queried enough and it does a scan it can certainly lead to
> >> this type behavior. When you say "4 disk channels RAID 5" do you mean
> >> you
> >> actually have 4 different RAID 5 arrays each on their own channel? Or a
> >> 4
> >> disk RAID 5?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> >> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> >> > DBCC memusage reports the buffers used by a particalur object to be far
> >> > and
> >> > away more than any other object. I'm surpised as this table is a
> >> > historical
> >> > system message table for the app that resides on the DB. How does an
> >> > object
> >> > get buffers(I assume reading and writing to the object)? All the views
> >> > in
> >> > the system do select * from tablename, so why this object and not
> >> > another
> >> > transactional table? The ratio of buffers between the 1st and 2nd
> >> > object
> >> > is
> >> > like 170,000 to 3000. I'm hoping to get clearance to purge this table
> >> > as
> >> > our
> >> > system is IO bound, disk channels are pegged at 100% and all of it is
> >> > read
> >> > activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4
> >> > disk
> >> > channels RAID5, quad procs with hyperthreading and performance is
> >> > dismal.
> >>
> >>
> >>
>
>|||No there isn't anything like that other than to clear the whole cache with
DBCC DROPClEANBUFFERS. SQL Server is pretty good about keeping in memory
what is used most often. If these tables are accessed that often and were
not in cache you would have to go to disk each time and pay that penalty.
It only caches what it reads so maybe if you tuned those status queries you
would read less data and free up that memory for other things. Maybe an
Indexed view would help with status type queries?
--
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:5505B468-AC5D-4368-93CD-10D0E0309D35@.microsoft.com...
>I found that the table in question is accessed by every transaction that
>hits
> the system and typically we add 7500-10000 records a day. No data had
> been
> purged for 2 years. Once I purged this and another historical table, the
> buffer count leveled out across the top 20 objects.
> Now I find out that there is a little monitoring app that hits all the
> messaging tables in the system to report status. This is keeping the
> table
> well cached and keeping other transactional tables out of cache. Is there
> an
> opposite of pintable, I'd like to excluded this and a few other tables
> from
> cache if at all possible until a data purge process is implemented.
> "Andrew J. Kelly" wrote:
>> If you have 3 separate arrays and all the channels are pegged you are
>> probably doing way too much access. You must be scanning most tables (or
>> at
>> least these big ones you are mentioning) a lot. By the way pinning the
>> table is usually not a good idea and will go away in 2005 anyway. Sounds
>> like you just need to optimize your code and or tables so you do more
>> seeks
>> than scans. You would be amazed that most systems hve just a few calls
>> or
>> sps that eat up most of the I/O. Once you tackle those others will pop
>> to
>> the top but you can do a lot of damage control by tuning the top x calls.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
>> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
>> > Negative on the pin table aspect although I've been comtemplating
>> > pinning
>> > a
>> > table myself. I got clearance to purge the table and after purging 60
>> > days
>> > worth of data, other objects are starting to show more than a few
>> > hundred
>> > cache buffers. However, another object that holds useless historical
>> > data
>> > is
>> > showing as the top object in memusage. The first table is accessed for
>> > evey
>> > message the app server generates but the base view is a select *. Same
>> > for
>> > evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
>> > array.(Dell 6600 with a 22Os and two dual channel 2960 PERC
>> > controllers.)
>> > All data was on one logical drive pegged at 100% utilization and long
>> > disk
>> > queues. After moving data around, the three channels are pegged or
>> > nearly
>> > pegged all the time. Reads and readaheds are accounting for the usage.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Paul's question would be my first guess as well. If that isn't it
>> >> then
>> >> you
>> >> should run a trace to see how that table is being accessed and how
>> >> often.
>> >> If it is being queried enough and it does a scan it can certainly lead
>> >> to
>> >> this type behavior. When you say "4 disk channels RAID 5" do you mean
>> >> you
>> >> actually have 4 different RAID 5 arrays each on their own channel? Or
>> >> a
>> >> 4
>> >> disk RAID 5?
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote
>> >> in
>> >> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>> >> > DBCC memusage reports the buffers used by a particalur object to be
>> >> > far
>> >> > and
>> >> > away more than any other object. I'm surpised as this table is a
>> >> > historical
>> >> > system message table for the app that resides on the DB. How does
>> >> > an
>> >> > object
>> >> > get buffers(I assume reading and writing to the object)? All the
>> >> > views
>> >> > in
>> >> > the system do select * from tablename, so why this object and not
>> >> > another
>> >> > transactional table? The ratio of buffers between the 1st and 2nd
>> >> > object
>> >> > is
>> >> > like 170,000 to 3000. I'm hoping to get clearance to purge this
>> >> > table
>> >> > as
>> >> > our
>> >> > system is IO bound, disk channels are pegged at 100% and all of it
>> >> > is
>> >> > read
>> >> > activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4
>> >> > disk
>> >> > channels RAID5, quad procs with hyperthreading and performance is
>> >> > dismal.
>> >>
>> >>
>> >>
>>
dbcc memusage
when I issued "dbcc memusage",
I got the errors:
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (1:929) with latch type SH. VerifyPageId failed.
but when I issued: "dbcc checkdb", it is working ok...
I have searched on the net, couldn't find any solution for it..
Would you please advise?Well if you look in BooksOnLine it will tell you that it is no longer
supported and I believe there were even reports of it being dangerous to
run. What is it you are trying to look for?
--
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> when I issued "dbcc memusage",
> I got the errors:
> Server: Msg 8966, Level 16, State 4, Line 1
> Could not read and latch page (1:929) with latch type SH. VerifyPageId
> failed.
> but when I issued: "dbcc checkdb", it is working ok...
> I have searched on the net, couldn't find any solution for it..
> Would you please advise?|||Andrew,
I was looking for why this 'dbcc memusage' is not working on this server
only, but working on other servers...Is something wroing with this server?
If not, how can I approve to the manager? What is not supported? it is in SQL
Server 2000 SP4. Thanks in advance.
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> > when I issued "dbcc memusage",
> > I got the errors:
> > Server: Msg 8966, Level 16, State 4, Line 1
> > Could not read and latch page (1:929) with latch type SH. VerifyPageId
> > failed.
> >
> > but when I issued: "dbcc checkdb", it is working ok...
> >
> > I have searched on the net, couldn't find any solution for it..
> >
> > Would you please advise?
>
>|||I hope you got my reply I just sent to you...
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> > when I issued "dbcc memusage",
> > I got the errors:
> > Server: Msg 8966, Level 16, State 4, Line 1
> > Could not read and latch page (1:929) with latch type SH. VerifyPageId
> > failed.
> >
> > but when I issued: "dbcc checkdb", it is working ok...
> >
> > I have searched on the net, couldn't find any solution for it..
> >
> > Would you please advise?
>
>|||This is directly from BOL:
Removed; no longer supported or available. Remove all references of DBCC
MEMUSAGE and replace with references to these Performance Monitor counters.
Just because a command can be executed it does not mean in any way that it
is supported. If it is not in BOL then all bets are off and you run it at
your own risk. Who knows why it fails on that one machine since it is
unsupported in the first place. Use on of the supported commands to get the
information you require.
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:2A28A250-24D7-4059-BD72-047C71EE4106@.microsoft.com...
> Andrew,
> I was looking for why this 'dbcc memusage' is not working on this server
> only, but working on other servers...Is something wroing with this
> server?
> If not, how can I approve to the manager? What is not supported? it is in
> SQL
> Server 2000 SP4. Thanks in advance.
> "Andrew J. Kelly" wrote:
>> Well if you look in BooksOnLine it will tell you that it is no longer
>> supported and I believe there were even reports of it being dangerous to
>> run. What is it you are trying to look for?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "renhai" <renhai@.discussions.microsoft.com> wrote in message
>> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
>> > when I issued "dbcc memusage",
>> > I got the errors:
>> > Server: Msg 8966, Level 16, State 4, Line 1
>> > Could not read and latch page (1:929) with latch type SH. VerifyPageId
>> > failed.
>> >
>> > but when I issued: "dbcc checkdb", it is working ok...
>> >
>> > I have searched on the net, couldn't find any solution for it..
>> >
>> > Would you please advise?
>>
I got the errors:
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (1:929) with latch type SH. VerifyPageId failed.
but when I issued: "dbcc checkdb", it is working ok...
I have searched on the net, couldn't find any solution for it..
Would you please advise?Well if you look in BooksOnLine it will tell you that it is no longer
supported and I believe there were even reports of it being dangerous to
run. What is it you are trying to look for?
--
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> when I issued "dbcc memusage",
> I got the errors:
> Server: Msg 8966, Level 16, State 4, Line 1
> Could not read and latch page (1:929) with latch type SH. VerifyPageId
> failed.
> but when I issued: "dbcc checkdb", it is working ok...
> I have searched on the net, couldn't find any solution for it..
> Would you please advise?|||Andrew,
I was looking for why this 'dbcc memusage' is not working on this server
only, but working on other servers...Is something wroing with this server?
If not, how can I approve to the manager? What is not supported? it is in SQL
Server 2000 SP4. Thanks in advance.
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> > when I issued "dbcc memusage",
> > I got the errors:
> > Server: Msg 8966, Level 16, State 4, Line 1
> > Could not read and latch page (1:929) with latch type SH. VerifyPageId
> > failed.
> >
> > but when I issued: "dbcc checkdb", it is working ok...
> >
> > I have searched on the net, couldn't find any solution for it..
> >
> > Would you please advise?
>
>|||I hope you got my reply I just sent to you...
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> > when I issued "dbcc memusage",
> > I got the errors:
> > Server: Msg 8966, Level 16, State 4, Line 1
> > Could not read and latch page (1:929) with latch type SH. VerifyPageId
> > failed.
> >
> > but when I issued: "dbcc checkdb", it is working ok...
> >
> > I have searched on the net, couldn't find any solution for it..
> >
> > Would you please advise?
>
>|||This is directly from BOL:
Removed; no longer supported or available. Remove all references of DBCC
MEMUSAGE and replace with references to these Performance Monitor counters.
Just because a command can be executed it does not mean in any way that it
is supported. If it is not in BOL then all bets are off and you run it at
your own risk. Who knows why it fails on that one machine since it is
unsupported in the first place. Use on of the supported commands to get the
information you require.
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:2A28A250-24D7-4059-BD72-047C71EE4106@.microsoft.com...
> Andrew,
> I was looking for why this 'dbcc memusage' is not working on this server
> only, but working on other servers...Is something wroing with this
> server?
> If not, how can I approve to the manager? What is not supported? it is in
> SQL
> Server 2000 SP4. Thanks in advance.
> "Andrew J. Kelly" wrote:
>> Well if you look in BooksOnLine it will tell you that it is no longer
>> supported and I believe there were even reports of it being dangerous to
>> run. What is it you are trying to look for?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "renhai" <renhai@.discussions.microsoft.com> wrote in message
>> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
>> > when I issued "dbcc memusage",
>> > I got the errors:
>> > Server: Msg 8966, Level 16, State 4, Line 1
>> > Could not read and latch page (1:929) with latch type SH. VerifyPageId
>> > failed.
>> >
>> > but when I issued: "dbcc checkdb", it is working ok...
>> >
>> > I have searched on the net, couldn't find any solution for it..
>> >
>> > Would you please advise?
>>
DBCC memusage
DBCC memusage reports the buffers used by a particalur object to be far and
away more than any other object. I'm surpised as this table is a historical
system message table for the app that resides on the DB. How does an object
get buffers(I assume reading and writing to the object)? All the views in
the system do select * from tablename, so why this object and not another
transactional table? The ratio of buffers between the 1st and 2nd object is
like 170,000 to 3000. I'm hoping to get clearance to purge this table as ou
r
system is IO bound, disk channels are pegged at 100% and all of it is read
activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
channels RAID5, quad procs with hyperthreading and performance is dismal.Has anyone used DBCC PINTABLE on this table?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Paul's question would be my first guess as well. If that isn't it then you
should run a trace to see how that table is being accessed and how often.
If it is being queried enough and it does a scan it can certainly lead to
this type behavior. When you say "4 disk channels RAID 5" do you mean you
actually have 4 different RAID 5 arrays each on their own channel? Or a 4
disk RAID 5?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Negative on the pin table aspect although I've been comtemplating pinning a
table myself. I got clearance to purge the table and after purging 60 days
worth of data, other objects are starting to show more than a few hundred
cache buffers. However, another object that holds useless historical data i
s
showing as the top object in memusage. The first table is accessed for evey
message the app server generates but the base view is a select *. Same for
evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
All data was on one logical drive pegged at 100% utilization and long disk
queues. After moving data around, the three channels are pegged or nearly
pegged all the time. Reads and readaheds are accounting for the usage.
"Andrew J. Kelly" wrote:
> Paul's question would be my first guess as well. If that isn't it then yo
u
> should run a trace to see how that table is being accessed and how often.
> If it is being queried enough and it does a scan it can certainly lead to
> this type behavior. When you say "4 disk channels RAID 5" do you mean you
> actually have 4 different RAID 5 arrays each on their own channel? Or a 4
> disk RAID 5?
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>
>|||If you have 3 separate arrays and all the channels are pegged you are
probably doing way too much access. You must be scanning most tables (or at
least these big ones you are mentioning) a lot. By the way pinning the
table is usually not a good idea and will go away in 2005 anyway. Sounds
like you just need to optimize your code and or tables so you do more seeks
than scans. You would be amazed that most systems hve just a few calls or
sps that eat up most of the I/O. Once you tackle those others will pop to
the top but you can do a lot of damage control by tuning the top x calls.
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...[vbcol=seagreen]
> Negative on the pin table aspect although I've been comtemplating pinning
> a
> table myself. I got clearance to purge the table and after purging 60
> days
> worth of data, other objects are starting to show more than a few hundred
> cache buffers. However, another object that holds useless historical data
> is
> showing as the top object in memusage. The first table is accessed for
> evey
> message the app server generates but the base view is a select *. Same
> for
> evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> All data was on one logical drive pegged at 100% utilization and long disk
> queues. After moving data around, the three channels are pegged or nearly
> pegged all the time. Reads and readaheds are accounting for the usage.
> "Andrew J. Kelly" wrote:
>|||I found that the table in question is accessed by every transaction that hit
s
the system and typically we add 7500-10000 records a day. No data had been
purged for 2 years. Once I purged this and another historical table, the
buffer count leveled out across the top 20 objects.
Now I find out that there is a little monitoring app that hits all the
messaging tables in the system to report status. This is keeping the table
well cached and keeping other transactional tables out of cache. Is there a
n
opposite of pintable, I'd like to excluded this and a few other tables from
cache if at all possible until a data purge process is implemented.
"Andrew J. Kelly" wrote:
> If you have 3 separate arrays and all the channels are pegged you are
> probably doing way too much access. You must be scanning most tables (or
at
> least these big ones you are mentioning) a lot. By the way pinning the
> table is usually not a good idea and will go away in 2005 anyway. Sounds
> like you just need to optimize your code and or tables so you do more seek
s
> than scans. You would be amazed that most systems hve just a few calls or
> sps that eat up most of the I/O. Once you tackle those others will pop to
> the top but you can do a lot of damage control by tuning the top x calls.
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
>
>|||No there isn't anything like that other than to clear the whole cache with
DBCC DROPClEANBUFFERS. SQL Server is pretty good about keeping in memory
what is used most often. If these tables are accessed that often and were
not in cache you would have to go to disk each time and pay that penalty.
It only caches what it reads so maybe if you tuned those status queries you
would read less data and free up that memory for other things. Maybe an
Indexed view would help with status type queries?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:5505B468-AC5D-4368-93CD-10D0E0309D35@.microsoft.com...[vbcol=seagreen]
>I found that the table in question is accessed by every transaction that
>hits
> the system and typically we add 7500-10000 records a day. No data had
> been
> purged for 2 years. Once I purged this and another historical table, the
> buffer count leveled out across the top 20 objects.
> Now I find out that there is a little monitoring app that hits all the
> messaging tables in the system to report status. This is keeping the
> table
> well cached and keeping other transactional tables out of cache. Is there
> an
> opposite of pintable, I'd like to excluded this and a few other tables
> from
> cache if at all possible until a data purge process is implemented.
> "Andrew J. Kelly" wrote:
>
away more than any other object. I'm surpised as this table is a historical
system message table for the app that resides on the DB. How does an object
get buffers(I assume reading and writing to the object)? All the views in
the system do select * from tablename, so why this object and not another
transactional table? The ratio of buffers between the 1st and 2nd object is
like 170,000 to 3000. I'm hoping to get clearance to purge this table as ou
r
system is IO bound, disk channels are pegged at 100% and all of it is read
activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
channels RAID5, quad procs with hyperthreading and performance is dismal.Has anyone used DBCC PINTABLE on this table?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Paul's question would be my first guess as well. If that isn't it then you
should run a trace to see how that table is being accessed and how often.
If it is being queried enough and it does a scan it can certainly lead to
this type behavior. When you say "4 disk channels RAID 5" do you mean you
actually have 4 different RAID 5 arrays each on their own channel? Or a 4
disk RAID 5?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.|||Negative on the pin table aspect although I've been comtemplating pinning a
table myself. I got clearance to purge the table and after purging 60 days
worth of data, other objects are starting to show more than a few hundred
cache buffers. However, another object that holds useless historical data i
s
showing as the top object in memusage. The first table is accessed for evey
message the app server generates but the base view is a select *. Same for
evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
All data was on one logical drive pegged at 100% utilization and long disk
queues. After moving data around, the three channels are pegged or nearly
pegged all the time. Reads and readaheds are accounting for the usage.
"Andrew J. Kelly" wrote:
> Paul's question would be my first guess as well. If that isn't it then yo
u
> should run a trace to see how that table is being accessed and how often.
> If it is being queried enough and it does a scan it can certainly lead to
> this type behavior. When you say "4 disk channels RAID 5" do you mean you
> actually have 4 different RAID 5 arrays each on their own channel? Or a 4
> disk RAID 5?
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>
>|||If you have 3 separate arrays and all the channels are pegged you are
probably doing way too much access. You must be scanning most tables (or at
least these big ones you are mentioning) a lot. By the way pinning the
table is usually not a good idea and will go away in 2005 anyway. Sounds
like you just need to optimize your code and or tables so you do more seeks
than scans. You would be amazed that most systems hve just a few calls or
sps that eat up most of the I/O. Once you tackle those others will pop to
the top but you can do a lot of damage control by tuning the top x calls.
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...[vbcol=seagreen]
> Negative on the pin table aspect although I've been comtemplating pinning
> a
> table myself. I got clearance to purge the table and after purging 60
> days
> worth of data, other objects are starting to show more than a few hundred
> cache buffers. However, another object that holds useless historical data
> is
> showing as the top object in memusage. The first table is accessed for
> evey
> message the app server generates but the base view is a select *. Same
> for
> evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> All data was on one logical drive pegged at 100% utilization and long disk
> queues. After moving data around, the three channels are pegged or nearly
> pegged all the time. Reads and readaheds are accounting for the usage.
> "Andrew J. Kelly" wrote:
>|||I found that the table in question is accessed by every transaction that hit
s
the system and typically we add 7500-10000 records a day. No data had been
purged for 2 years. Once I purged this and another historical table, the
buffer count leveled out across the top 20 objects.
Now I find out that there is a little monitoring app that hits all the
messaging tables in the system to report status. This is keeping the table
well cached and keeping other transactional tables out of cache. Is there a
n
opposite of pintable, I'd like to excluded this and a few other tables from
cache if at all possible until a data purge process is implemented.
"Andrew J. Kelly" wrote:
> If you have 3 separate arrays and all the channels are pegged you are
> probably doing way too much access. You must be scanning most tables (or
at
> least these big ones you are mentioning) a lot. By the way pinning the
> table is usually not a good idea and will go away in 2005 anyway. Sounds
> like you just need to optimize your code and or tables so you do more seek
s
> than scans. You would be amazed that most systems hve just a few calls or
> sps that eat up most of the I/O. Once you tackle those others will pop to
> the top but you can do a lot of damage control by tuning the top x calls.
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
>
>|||No there isn't anything like that other than to clear the whole cache with
DBCC DROPClEANBUFFERS. SQL Server is pretty good about keeping in memory
what is used most often. If these tables are accessed that often and were
not in cache you would have to go to disk each time and pay that penalty.
It only caches what it reads so maybe if you tuned those status queries you
would read less data and free up that memory for other things. Maybe an
Indexed view would help with status type queries?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:5505B468-AC5D-4368-93CD-10D0E0309D35@.microsoft.com...[vbcol=seagreen]
>I found that the table in question is accessed by every transaction that
>hits
> the system and typically we add 7500-10000 records a day. No data had
> been
> purged for 2 years. Once I purged this and another historical table, the
> buffer count leveled out across the top 20 objects.
> Now I find out that there is a little monitoring app that hits all the
> messaging tables in the system to report status. This is keeping the
> table
> well cached and keeping other transactional tables out of cache. Is there
> an
> opposite of pintable, I'd like to excluded this and a few other tables
> from
> cache if at all possible until a data purge process is implemented.
> "Andrew J. Kelly" wrote:
>
dbcc memusage
when I issued "dbcc memusage",
I got the errors:
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (1:929) with latch type SH. VerifyPageId faile
d.
but when I issued: "dbcc checkdb", it is working ok...
I have searched on the net, couldn't find any solution for it..
Would you please advise?Well if you look in BooksOnLine it will tell you that it is no longer
supported and I believe there were even reports of it being dangerous to
run. What is it you are trying to look for?
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> when I issued "dbcc memusage",
> I got the errors:
> Server: Msg 8966, Level 16, State 4, Line 1
> Could not read and latch page (1:929) with latch type SH. VerifyPageId
> failed.
> but when I issued: "dbcc checkdb", it is working ok...
> I have searched on the net, couldn't find any solution for it..
> Would you please advise?|||Andrew,
I was looking for why this 'dbcc memusage' is not working on this server
only, but working on other servers...Is something wroing with this server?
If not, how can I approve to the manager? What is not supported? it is in SQ
L
Server 2000 SP4. Thanks in advance.
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
>
>|||I hope you got my reply I just sent to you...
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
>
>|||This is directly from BOL:
Removed; no longer supported or available. Remove all references of DBCC
MEMUSAGE and replace with references to these Performance Monitor counters.
Just because a command can be executed it does not mean in any way that it
is supported. If it is not in BOL then all bets are off and you run it at
your own risk. Who knows why it fails on that one machine since it is
unsupported in the first place. Use on of the supported commands to get the
information you require.
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:2A28A250-24D7-4059-BD72-047C71EE4106@.microsoft.com...[vbcol=seagreen]
> Andrew,
> I was looking for why this 'dbcc memusage' is not working on this server
> only, but working on other servers...Is something wroing with this
> server?
> If not, how can I approve to the manager? What is not supported? it is in
> SQL
> Server 2000 SP4. Thanks in advance.
> "Andrew J. Kelly" wrote:
>
I got the errors:
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (1:929) with latch type SH. VerifyPageId faile
d.
but when I issued: "dbcc checkdb", it is working ok...
I have searched on the net, couldn't find any solution for it..
Would you please advise?Well if you look in BooksOnLine it will tell you that it is no longer
supported and I believe there were even reports of it being dangerous to
run. What is it you are trying to look for?
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> when I issued "dbcc memusage",
> I got the errors:
> Server: Msg 8966, Level 16, State 4, Line 1
> Could not read and latch page (1:929) with latch type SH. VerifyPageId
> failed.
> but when I issued: "dbcc checkdb", it is working ok...
> I have searched on the net, couldn't find any solution for it..
> Would you please advise?|||Andrew,
I was looking for why this 'dbcc memusage' is not working on this server
only, but working on other servers...Is something wroing with this server?
If not, how can I approve to the manager? What is not supported? it is in SQ
L
Server 2000 SP4. Thanks in advance.
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
>
>|||I hope you got my reply I just sent to you...
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
>
>|||This is directly from BOL:
Removed; no longer supported or available. Remove all references of DBCC
MEMUSAGE and replace with references to these Performance Monitor counters.
Just because a command can be executed it does not mean in any way that it
is supported. If it is not in BOL then all bets are off and you run it at
your own risk. Who knows why it fails on that one machine since it is
unsupported in the first place. Use on of the supported commands to get the
information you require.
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:2A28A250-24D7-4059-BD72-047C71EE4106@.microsoft.com...[vbcol=seagreen]
> Andrew,
> I was looking for why this 'dbcc memusage' is not working on this server
> only, but working on other servers...Is something wroing with this
> server?
> If not, how can I approve to the manager? What is not supported? it is in
> SQL
> Server 2000 SP4. Thanks in advance.
> "Andrew J. Kelly" wrote:
>
dbcc memusage
when I issued "dbcc memusage",
I got the errors:
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (1:929) with latch type SH. VerifyPageId failed.
but when I issued: "dbcc checkdb", it is working ok...
I have searched on the net, couldn't find any solution for it..
Would you please advise?
Well if you look in BooksOnLine it will tell you that it is no longer
supported and I believe there were even reports of it being dangerous to
run. What is it you are trying to look for?
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> when I issued "dbcc memusage",
> I got the errors:
> Server: Msg 8966, Level 16, State 4, Line 1
> Could not read and latch page (1:929) with latch type SH. VerifyPageId
> failed.
> but when I issued: "dbcc checkdb", it is working ok...
> I have searched on the net, couldn't find any solution for it..
> Would you please advise?
|||Andrew,
I was looking for why this 'dbcc memusage' is not working on this server
only, but working on other servers...Is something wroing with this server?
If not, how can I approve to the manager? What is not supported? it is in SQL
Server 2000 SP4. Thanks in advance.
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
>
>
|||I hope you got my reply I just sent to you...
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
>
>
|||This is directly from BOL:
Removed; no longer supported or available. Remove all references of DBCC
MEMUSAGE and replace with references to these Performance Monitor counters.
Just because a command can be executed it does not mean in any way that it
is supported. If it is not in BOL then all bets are off and you run it at
your own risk. Who knows why it fails on that one machine since it is
unsupported in the first place. Use on of the supported commands to get the
information you require.
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:2A28A250-24D7-4059-BD72-047C71EE4106@.microsoft.com...[vbcol=seagreen]
> Andrew,
> I was looking for why this 'dbcc memusage' is not working on this server
> only, but working on other servers...Is something wroing with this
> server?
> If not, how can I approve to the manager? What is not supported? it is in
> SQL
> Server 2000 SP4. Thanks in advance.
> "Andrew J. Kelly" wrote:
I got the errors:
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (1:929) with latch type SH. VerifyPageId failed.
but when I issued: "dbcc checkdb", it is working ok...
I have searched on the net, couldn't find any solution for it..
Would you please advise?
Well if you look in BooksOnLine it will tell you that it is no longer
supported and I believe there were even reports of it being dangerous to
run. What is it you are trying to look for?
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
> when I issued "dbcc memusage",
> I got the errors:
> Server: Msg 8966, Level 16, State 4, Line 1
> Could not read and latch page (1:929) with latch type SH. VerifyPageId
> failed.
> but when I issued: "dbcc checkdb", it is working ok...
> I have searched on the net, couldn't find any solution for it..
> Would you please advise?
|||Andrew,
I was looking for why this 'dbcc memusage' is not working on this server
only, but working on other servers...Is something wroing with this server?
If not, how can I approve to the manager? What is not supported? it is in SQL
Server 2000 SP4. Thanks in advance.
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
>
>
|||I hope you got my reply I just sent to you...
"Andrew J. Kelly" wrote:
> Well if you look in BooksOnLine it will tell you that it is no longer
> supported and I believe there were even reports of it being dangerous to
> run. What is it you are trying to look for?
> --
> Andrew J. Kelly SQL MVP
>
> "renhai" <renhai@.discussions.microsoft.com> wrote in message
> news:8764FA14-93CF-4AC8-8165-3AEDEF7C4B93@.microsoft.com...
>
>
|||This is directly from BOL:
Removed; no longer supported or available. Remove all references of DBCC
MEMUSAGE and replace with references to these Performance Monitor counters.
Just because a command can be executed it does not mean in any way that it
is supported. If it is not in BOL then all bets are off and you run it at
your own risk. Who knows why it fails on that one machine since it is
unsupported in the first place. Use on of the supported commands to get the
information you require.
Andrew J. Kelly SQL MVP
"renhai" <renhai@.discussions.microsoft.com> wrote in message
news:2A28A250-24D7-4059-BD72-047C71EE4106@.microsoft.com...[vbcol=seagreen]
> Andrew,
> I was looking for why this 'dbcc memusage' is not working on this server
> only, but working on other servers...Is something wroing with this
> server?
> If not, how can I approve to the manager? What is not supported? it is in
> SQL
> Server 2000 SP4. Thanks in advance.
> "Andrew J. Kelly" wrote:
DBCC memusage
DBCC memusage reports the buffers used by a particalur object to be far and
away more than any other object. I'm surpised as this table is a historical
system message table for the app that resides on the DB. How does an object
get buffers(I assume reading and writing to the object)? All the views in
the system do select * from tablename, so why this object and not another
transactional table? The ratio of buffers between the 1st and 2nd object is
like 170,000 to 3000. I'm hoping to get clearance to purge this table as our
system is IO bound, disk channels are pegged at 100% and all of it is read
activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
channels RAID5, quad procs with hyperthreading and performance is dismal.
Has anyone used DBCC PINTABLE on this table?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.
|||Paul's question would be my first guess as well. If that isn't it then you
should run a trace to see how that table is being accessed and how often.
If it is being queried enough and it does a scan it can certainly lead to
this type behavior. When you say "4 disk channels RAID 5" do you mean you
actually have 4 different RAID 5 arrays each on their own channel? Or a 4
disk RAID 5?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.
|||Negative on the pin table aspect although I've been comtemplating pinning a
table myself. I got clearance to purge the table and after purging 60 days
worth of data, other objects are starting to show more than a few hundred
cache buffers. However, another object that holds useless historical data is
showing as the top object in memusage. The first table is accessed for evey
message the app server generates but the base view is a select *. Same for
evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
All data was on one logical drive pegged at 100% utilization and long disk
queues. After moving data around, the three channels are pegged or nearly
pegged all the time. Reads and readaheds are accounting for the usage.
"Andrew J. Kelly" wrote:
> Paul's question would be my first guess as well. If that isn't it then you
> should run a trace to see how that table is being accessed and how often.
> If it is being queried enough and it does a scan it can certainly lead to
> this type behavior. When you say "4 disk channels RAID 5" do you mean you
> actually have 4 different RAID 5 arrays each on their own channel? Or a 4
> disk RAID 5?
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>
>
|||If you have 3 separate arrays and all the channels are pegged you are
probably doing way too much access. You must be scanning most tables (or at
least these big ones you are mentioning) a lot. By the way pinning the
table is usually not a good idea and will go away in 2005 anyway. Sounds
like you just need to optimize your code and or tables so you do more seeks
than scans. You would be amazed that most systems hve just a few calls or
sps that eat up most of the I/O. Once you tackle those others will pop to
the top but you can do a lot of damage control by tuning the top x calls.
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...[vbcol=seagreen]
> Negative on the pin table aspect although I've been comtemplating pinning
> a
> table myself. I got clearance to purge the table and after purging 60
> days
> worth of data, other objects are starting to show more than a few hundred
> cache buffers. However, another object that holds useless historical data
> is
> showing as the top object in memusage. The first table is accessed for
> evey
> message the app server generates but the base view is a select *. Same
> for
> evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> All data was on one logical drive pegged at 100% utilization and long disk
> queues. After moving data around, the three channels are pegged or nearly
> pegged all the time. Reads and readaheds are accounting for the usage.
> "Andrew J. Kelly" wrote:
|||I found that the table in question is accessed by every transaction that hits
the system and typically we add 7500-10000 records a day. No data had been
purged for 2 years. Once I purged this and another historical table, the
buffer count leveled out across the top 20 objects.
Now I find out that there is a little monitoring app that hits all the
messaging tables in the system to report status. This is keeping the table
well cached and keeping other transactional tables out of cache. Is there an
opposite of pintable, I'd like to excluded this and a few other tables from
cache if at all possible until a data purge process is implemented.
"Andrew J. Kelly" wrote:
> If you have 3 separate arrays and all the channels are pegged you are
> probably doing way too much access. You must be scanning most tables (or at
> least these big ones you are mentioning) a lot. By the way pinning the
> table is usually not a good idea and will go away in 2005 anyway. Sounds
> like you just need to optimize your code and or tables so you do more seeks
> than scans. You would be amazed that most systems hve just a few calls or
> sps that eat up most of the I/O. Once you tackle those others will pop to
> the top but you can do a lot of damage control by tuning the top x calls.
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
>
>
|||No there isn't anything like that other than to clear the whole cache with
DBCC DROPClEANBUFFERS. SQL Server is pretty good about keeping in memory
what is used most often. If these tables are accessed that often and were
not in cache you would have to go to disk each time and pay that penalty.
It only caches what it reads so maybe if you tuned those status queries you
would read less data and free up that memory for other things. Maybe an
Indexed view would help with status type queries?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:5505B468-AC5D-4368-93CD-10D0E0309D35@.microsoft.com...[vbcol=seagreen]
>I found that the table in question is accessed by every transaction that
>hits
> the system and typically we add 7500-10000 records a day. No data had
> been
> purged for 2 years. Once I purged this and another historical table, the
> buffer count leveled out across the top 20 objects.
> Now I find out that there is a little monitoring app that hits all the
> messaging tables in the system to report status. This is keeping the
> table
> well cached and keeping other transactional tables out of cache. Is there
> an
> opposite of pintable, I'd like to excluded this and a few other tables
> from
> cache if at all possible until a data purge process is implemented.
> "Andrew J. Kelly" wrote:
away more than any other object. I'm surpised as this table is a historical
system message table for the app that resides on the DB. How does an object
get buffers(I assume reading and writing to the object)? All the views in
the system do select * from tablename, so why this object and not another
transactional table? The ratio of buffers between the 1st and 2nd object is
like 170,000 to 3000. I'm hoping to get clearance to purge this table as our
system is IO bound, disk channels are pegged at 100% and all of it is read
activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
channels RAID5, quad procs with hyperthreading and performance is dismal.
Has anyone used DBCC PINTABLE on this table?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.
|||Paul's question would be my first guess as well. If that isn't it then you
should run a trace to see how that table is being accessed and how often.
If it is being queried enough and it does a scan it can certainly lead to
this type behavior. When you say "4 disk channels RAID 5" do you mean you
actually have 4 different RAID 5 arrays each on their own channel? Or a 4
disk RAID 5?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
> DBCC memusage reports the buffers used by a particalur object to be far
> and
> away more than any other object. I'm surpised as this table is a
> historical
> system message table for the app that resides on the DB. How does an
> object
> get buffers(I assume reading and writing to the object)? All the views in
> the system do select * from tablename, so why this object and not another
> transactional table? The ratio of buffers between the 1st and 2nd object
> is
> like 170,000 to 3000. I'm hoping to get clearance to purge this table as
> our
> system is IO bound, disk channels are pegged at 100% and all of it is read
> activity. We have 220GB of data on a Win2003 EE with 8GB of RAM, 4 disk
> channels RAID5, quad procs with hyperthreading and performance is dismal.
|||Negative on the pin table aspect although I've been comtemplating pinning a
table myself. I got clearance to purge the table and after purging 60 days
worth of data, other objects are starting to show more than a few hundred
cache buffers. However, another object that holds useless historical data is
showing as the top object in memusage. The first table is accessed for evey
message the app server generates but the base view is a select *. Same for
evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
All data was on one logical drive pegged at 100% utilization and long disk
queues. After moving data around, the three channels are pegged or nearly
pegged all the time. Reads and readaheds are accounting for the usage.
"Andrew J. Kelly" wrote:
> Paul's question would be my first guess as well. If that isn't it then you
> should run a trace to see how that table is being accessed and how often.
> If it is being queried enough and it does a scan it can certainly lead to
> this type behavior. When you say "4 disk channels RAID 5" do you mean you
> actually have 4 different RAID 5 arrays each on their own channel? Or a 4
> disk RAID 5?
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:9D0209A6-8165-428F-ABF5-0376540E3C88@.microsoft.com...
>
>
|||If you have 3 separate arrays and all the channels are pegged you are
probably doing way too much access. You must be scanning most tables (or at
least these big ones you are mentioning) a lot. By the way pinning the
table is usually not a good idea and will go away in 2005 anyway. Sounds
like you just need to optimize your code and or tables so you do more seeks
than scans. You would be amazed that most systems hve just a few calls or
sps that eat up most of the I/O. Once you tackle those others will pop to
the top but you can do a lot of damage control by tuning the top x calls.
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...[vbcol=seagreen]
> Negative on the pin table aspect although I've been comtemplating pinning
> a
> table myself. I got clearance to purge the table and after purging 60
> days
> worth of data, other objects are starting to show more than a few hundred
> cache buffers. However, another object that holds useless historical data
> is
> showing as the top object in memusage. The first table is accessed for
> evey
> message the app server generates but the base view is a select *. Same
> for
> evey other table/view. We have 3 sepearate raid 5 arrays and 1 mirror
> array.(Dell 6600 with a 22Os and two dual channel 2960 PERC controllers.)
> All data was on one logical drive pegged at 100% utilization and long disk
> queues. After moving data around, the three channels are pegged or nearly
> pegged all the time. Reads and readaheds are accounting for the usage.
> "Andrew J. Kelly" wrote:
|||I found that the table in question is accessed by every transaction that hits
the system and typically we add 7500-10000 records a day. No data had been
purged for 2 years. Once I purged this and another historical table, the
buffer count leveled out across the top 20 objects.
Now I find out that there is a little monitoring app that hits all the
messaging tables in the system to report status. This is keeping the table
well cached and keeping other transactional tables out of cache. Is there an
opposite of pintable, I'd like to excluded this and a few other tables from
cache if at all possible until a data purge process is implemented.
"Andrew J. Kelly" wrote:
> If you have 3 separate arrays and all the channels are pegged you are
> probably doing way too much access. You must be scanning most tables (or at
> least these big ones you are mentioning) a lot. By the way pinning the
> table is usually not a good idea and will go away in 2005 anyway. Sounds
> like you just need to optimize your code and or tables so you do more seeks
> than scans. You would be amazed that most systems hve just a few calls or
> sps that eat up most of the I/O. Once you tackle those others will pop to
> the top but you can do a lot of damage control by tuning the top x calls.
> --
> Andrew J. Kelly SQL MVP
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:7C7A5141-70E3-4BFA-9406-D717578EF56E@.microsoft.com...
>
>
|||No there isn't anything like that other than to clear the whole cache with
DBCC DROPClEANBUFFERS. SQL Server is pretty good about keeping in memory
what is used most often. If these tables are accessed that often and were
not in cache you would have to go to disk each time and pay that penalty.
It only caches what it reads so maybe if you tuned those status queries you
would read less data and free up that memory for other things. Maybe an
Indexed view would help with status type queries?
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:5505B468-AC5D-4368-93CD-10D0E0309D35@.microsoft.com...[vbcol=seagreen]
>I found that the table in question is accessed by every transaction that
>hits
> the system and typically we add 7500-10000 records a day. No data had
> been
> purged for 2 years. Once I purged this and another historical table, the
> buffer count leveled out across the top 20 objects.
> Now I find out that there is a little monitoring app that hits all the
> messaging tables in the system to report status. This is keeping the
> table
> well cached and keeping other transactional tables out of cache. Is there
> an
> opposite of pintable, I'd like to excluded this and a few other tables
> from
> cache if at all possible until a data purge process is implemented.
> "Andrew J. Kelly" wrote:
Subscribe to:
Posts (Atom)