DBfile usage: performance

Showing posts with label performance. Show all posts

Sunday, March 25, 2012

DBCC SHOWCONTIG results: Good/bad?

My company is running Microsoft Navision Axapta as our ERP solution.
We've had some performance problems (high disk loads). After reading
"Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
...I decided to run DBCC SHOWCONTIG on the Axapta database.
The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
(500 kb text)
I'm new to administering SQL servers with large datbases and heavy
load. I need som expert opinions! Is this database heavily fragmented
or not? Should I schedule a daily index defragmentation job?
Any feedback appreciated!
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
Look to the best:actual counts. If there was no fragmentation they would be
close to the same.
However, fragmentation is not necessarily a bad thing. In an OLTP database
(loads of updates, few reports) it is good. In an OLAP database (few
updates, bags of reports) it is bad.
You really need to know why your database is running slow by analysing
output from Profiler and System Manager. There's roughly a gazillion things
that can affect performance, fragmentation being only one of them.
Does it affect just SELECT statements? INSERT/DELETE/UPDATE statements?
Both?
If your Indexes are fragmented, try rebuilding the indexes with a lower
FILL_FACTOR setting. That will cause fewer page splits, though SELECT
statements may theoretically increase as SQL will have to read more pages
per index search.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||You really don't expect us to go through all that, do you? ;-)
I suggest you run SHOWCONTIG this way:
DBCC SHOWCONTIG WITH TABLERESULTS
That gives you a resultset. I even use below method (from VB code):
INSERT INTO #tbl (...)
DBCC SHOWCONTIG WITH TABLERESULTS
You have to create the temp tables first, with proper columns, but that gives you the ability to do
SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY LogicalFragmentation DESC, for example.
General tips:
If you don't have > 500 to 1000 pages, don't worry about fragmentation.
If you have > 1 database files, use Logial Fragmentation, scan density will not report correct.
Seems you have some tables without clustered index... Any particular reason?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||> Look to the best:actual counts. If there was no fragmentation they would be
> close to the same.
Just watch out if > 1 database file for the filegroup. Jumps between files will count as
fragmentation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver|||You should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which explains everything you need to know about managing fragmentation.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:#0LxzdInDHA.2416@.TK2MSFTNGP10.phx.gbl...
> You really don't expect us to go through all that, do you? ;-)
> I suggest you run SHOWCONTIG this way:
> DBCC SHOWCONTIG WITH TABLERESULTS
> That gives you a resultset. I even use below method (from VB code):
> INSERT INTO #tbl (...)
> DBCC SHOWCONTIG WITH TABLERESULTS
> You have to create the temp tables first, with proper columns, but that
gives you the ability to do
> SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY
LogicalFragmentation DESC, for example.
> General tips:
> If you don't have > 500 to 1000 pages, don't worry about fragmentation.
> If you have > 1 database files, use Logial Fragmentation, scan density
will not report correct.
> Seems you have some tables without clustered index... Any particular
reason?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
> news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> > My company is running Microsoft Navision Axapta as our ERP solution.
> > We've had some performance problems (high disk loads). After reading
> > "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
> >
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >
> > ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> > The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> > (500 kb text)
> >
> > I'm new to administering SQL servers with large datbases and heavy
> > load. I need som expert opinions! Is this database heavily fragmented
> > or not? Should I schedule a daily index defragmentation job?
> >
> > Any feedback appreciated!
> >
> >
> > H.M.Haga
> > '98 Subaru Impreza GT
> > '91 Suzuki Bandit 400
> > http://www.imprezadriver.com/phpBB2
> > http://home.c2i.net/hmhaga
>|||On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>You really don't expect us to go through all that, do you? ;-)
No... ;-)
After I read your answers, I signed up for "2072 Administering a MS
SQL Server 2000 Database" :-)
Our server has been running for years AS IS, but after we decided to
put our ERP solution on it - it suddenly requires a lot more attention
- and knowledge!
I've just started to analyze the database, currently it is in the same
state as it was when our vendor installed the solution (Axapta).
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga|||> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
Teaching those courses, I just want to say that the Admin course do not deal with indexes,
fragmentation and such. The programming course does (2073). Just a heads up. :-)
Again, check out the tables with some 500 pages or more, and concentrate on those. Also, consider
why some tables doesn't have clustered indexes. That would be a good start. And read the paper that
Paul referred to. My tips...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:dlfspv8vi4esvgf2cv2ktgv3ot1ejp9pht@.4ax.com...
> On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
> >You really don't expect us to go through all that, do you? ;-)
> No... ;-)
> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
> Our server has been running for years AS IS, but after we decided to
> put our ERP solution on it - it suddenly requires a lot more attention
> - and knowledge!
> I've just started to analyze the database, currently it is in the same
> state as it was when our vendor installed the solution (Axapta).
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||On Tue, 28 Oct 2003 12:08:46 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>Teaching those courses, I just want to say that the Admin course do not deal with indexes,
>fragmentation and such. The programming course does (2073). Just a heads up. :-)
Thanks! I've just signed 2073 as well, 2 weeks of SQL courses then :)
As for clustered index, neither the database nor the application has
been tuned or optimized in any way after the implementation - so I
guess that is up to me. Our dealer has very limited knowledge of SQL
(it's like a black box), they've put a layer of Axapta application
logic between themself and SQL! ;-)
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga

DBCC SHOWCONTIG question

Hi Freinds,
SQL 2000
I have performance issues on my database and got to this point that DBCC
SHOWCONTIG result for my tables are:
DBCC SHOWCONTIG scanning 'dup_source_title' table...
Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 19296
- Extents Scanned.......................: 2429
- Extent Switches.......................: 2428
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
- Logical Scan Fragmentation ..............: 0.06%
- Extent Scan Fragmentation ...............: 1.03%
- Avg. Bytes Free per Page................: 7265.6
- Avg. Page Density (full)................: 10.24%
DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 180459
- Extents Scanned.......................: 22605
- Extent Switches.......................: 23105
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
- Logical Scan Fragmentation ..............: 3.78%
- Extent Scan Fragmentation ...............: 12.67%
- Avg. Bytes Free per Page................: 656.4
- Avg. Page Density (full)................: 91.89%
is this good result? bad result? which statistic is not good and need
tuning? and how , what should I do to tune it?
Thanks in advance,
PatThey are both fine in regards to fragmentation but the first one is only 10%
full where as the second is 90% full. There is no right or wrong number for
fullness since it depends a lot on how the index is used. But 10% is
probably way too low of a fill factor. Have a look at these:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have performance issues on my database and got to this point that DBCC
> SHOWCONTIG result for my tables are:
> DBCC SHOWCONTIG scanning 'dup_source_title' table...
> Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 19296
> - Extents Scanned.......................: 2429
> - Extent Switches.......................: 2428
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
> - Logical Scan Fragmentation ..............: 0.06%
> - Extent Scan Fragmentation ...............: 1.03%
> - Avg. Bytes Free per Page................: 7265.6
> - Avg. Page Density (full)................: 10.24%
> DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
> Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 180459
> - Extents Scanned.......................: 22605
> - Extent Switches.......................: 23105
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
> - Logical Scan Fragmentation ..............: 3.78%
> - Extent Scan Fragmentation ...............: 12.67%
> - Avg. Bytes Free per Page................: 656.4
> - Avg. Page Density (full)................: 91.89%
> is this good result? bad result? which statistic is not good and need
> tuning? and how , what should I do to tune it?
> Thanks in advance,
> Pat
>|||Thank you very much for the answer,
I am struggling with the performance now and need to do smt to get the db
running.
What does Scan Density [Best Count:Actual Count] show? Should it be low ?
High?
Where are the main places that Ihave to look to tune the database?
Thanks again ina advance ,
Pat
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
> They are both fine in regards to fragmentation but the first one is only
> 10% full where as the second is 90% full. There is no right or wrong
> number for fullness since it depends a lot on how the index is used. But
> 10% is probably way too low of a fill factor. Have a look at these:
>
> http://www.microsoft.com/technet/pr..._showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/col...fillfactors.asp
> Fill Factors
> http://www.sql-server-performance.c...red_indexes.asp Clustered
> Indexes
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||The 2nd link I gave you is all about DBCC SHOWCONTIG and should answer most
if not all of your questions on that topic. The other links are extremely
beneficial as well. But fragmentation is probably not the first place you
should look if you are having performance issues. Have a look at these to
help you narrow down the culprits:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance Checklist
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:ej0DZfcLGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Thank you very much for the answer,
> I am struggling with the performance now and need to do smt to get the db
> running.
> What does Scan Density [Best Count:Actual Count] show? Should it be low ?
> High?
> Where are the main places that Ihave to look to tune the database?
> Thanks again ina advance ,
> Pat
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
>

DBCC SHOWCONTIG & DB Performance

Hi,
If I run DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES on our DB I
get info back our indexes. As I understand it, an indexid = 0 is the index
representing the heap of a table with no clustered index, so we don't need t
o
worry about the scandensity or fragmentation of these for db performance.
Indexid's 1-254 are actual indexes & may need looking at if low scandensity
or high fragmentation.
What does an index with an indexid = 255 represent though, and do they have
an impact on performance?
TIASteve,
Indid = 255 is the entry for tables with text / image columns, for example,
if you create table t(colA text), then you will have two entries in table
sysindexes for this table idnid = 0 and indid = 255. I do not know if we
should defrag indid = 255.
AMB
"Steve" wrote:

> Hi,
> If I run DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES on our DB I
> get info back our indexes. As I understand it, an indexid = 0 is the index
> representing the heap of a table with no clustered index, so we don't need
to
> worry about the scandensity or fragmentation of these for db performance.
> Indexid's 1-254 are actual indexes & may need looking at if low scandensit
y
> or high fragmentation.
> What does an index with an indexid = 255 represent though, and do they hav
e
> an impact on performance?
> TIAsql

Wednesday, March 21, 2012

DBCC PROCCACHE Question.

What exactly does this command show you. I need to know what the numbers it
shows actually mean. I have been having SERIOUS performance issues with my
SQL cluster and just by looking at the numbers from this command it looks
like my stored proc cache just grows and grows. I think...
Are high numbers from this command a bad thing or just normal?
Any help appreciated.
TIA
Cary LaPora
Database Administrator
The Lancair Company
If your proccache keeps growing that basically means you are not reusing
your query plans properly. That in and of itself can be a major performance
issue but this is simply one aspect of what could be a host of issues. You
may want to have a look at these:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
|||If that is the case look inside syscacheobjects to see if you have actual
plan reuse.
dbcc cachestats -- will show you hit ratios
dbcc memorystatus -- will show you how memory is divided inside SQL Server
(for SQL Server controlled memory grants)
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
|||Thanks for the info guys.
Gert,
I don't suppose you can point me to any help about those commands. I can't
seem to find them in the T-SQL help and I'm not really sure exactly what they
are telling me.
TIA
Cary LaPora
Database Administrator
The Lancair Company
"Gert E.R. Drapers" wrote:

> If that is the case look inside syscacheobjects to see if you have actual
> plan reuse.
> dbcc cachestats -- will show you hit ratios
> dbcc memorystatus -- will show you how memory is divided inside SQL Server
> (for SQL Server controlled memory grants)
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
>
>
|||Sure see:
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;271624
I will digg up some info on cachestats, not handy right now.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0D87AA70-9DF1-447B-8562-60B02AACADDB@.microsoft.com...[vbcol=seagreen]
> Thanks for the info guys.
> Gert,
> I don't suppose you can point me to any help about those commands. I
> can't
> seem to find them in the T-SQL help and I'm not really sure exactly what
> they
> are telling me.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
> "Gert E.R. Drapers" wrote:

DBCC PROCCACHE Question.

What exactly does this command show you. I need to know what the numbers it
shows actually mean. I have been having SERIOUS performance issues with my
SQL cluster and just by looking at the numbers from this command it looks
like my stored proc cache just grows and grows. I think...
Are high numbers from this command a bad thing or just normal?
Any help appreciated.
TIA
Cary LaPora
Database Administrator
The Lancair CompanyIf your proccache keeps growing that basically means you are not reusing
your query plans properly. That in and of itself can be a major performance
issue but this is simply one aspect of what could be a host of issues. You
may want to have a look at these:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company|||If that is the case look inside syscacheobjects to see if you have actual
plan reuse.
dbcc cachestats -- will show you hit ratios
dbcc memorystatus -- will show you how memory is divided inside SQL Server
(for SQL Server controlled memory grants)
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company|||Thanks for the info guys.
Gert,
I don't suppose you can point me to any help about those commands. I can't
seem to find them in the T-SQL help and I'm not really sure exactly what they
are telling me.
TIA
Cary LaPora
Database Administrator
The Lancair Company
"Gert E.R. Drapers" wrote:
> If that is the case look inside syscacheobjects to see if you have actual
> plan reuse.
> dbcc cachestats -- will show you hit ratios
> dbcc memorystatus -- will show you how memory is divided inside SQL Server
> (for SQL Server controlled memory grants)
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright Â© SQLDev.Net 1991-2005 All rights reserved.
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> > What exactly does this command show you. I need to know what the numbers
> > it
> > shows actually mean. I have been having SERIOUS performance issues with
> > my
> > SQL cluster and just by looking at the numbers from this command it looks
> > like my stored proc cache just grows and grows. I think...
> >
> > Are high numbers from this command a bad thing or just normal?
> >
> > Any help appreciated.
> >
> > TIA
> > Cary LaPora
> > Database Administrator
> > The Lancair Company
>
>|||Sure see:
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;271624
I will digg up some info on cachestats, not handy right now.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0D87AA70-9DF1-447B-8562-60B02AACADDB@.microsoft.com...
> Thanks for the info guys.
> Gert,
> I don't suppose you can point me to any help about those commands. I
> can't
> seem to find them in the T-SQL help and I'm not really sure exactly what
> they
> are telling me.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
> "Gert E.R. Drapers" wrote:
>> If that is the case look inside syscacheobjects to see if you have actual
>> plan reuse.
>> dbcc cachestats -- will show you hit ratios
>> dbcc memorystatus -- will show you how memory is divided inside SQL
>> Server
>> (for SQL Server controlled memory grants)
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2005 All rights reserved.
>> "Cary" <Cary@.discussions.microsoft.com> wrote in message
>> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
>> > What exactly does this command show you. I need to know what the
>> > numbers
>> > it
>> > shows actually mean. I have been having SERIOUS performance issues
>> > with
>> > my
>> > SQL cluster and just by looking at the numbers from this command it
>> > looks
>> > like my stored proc cache just grows and grows. I think...
>> >
>> > Are high numbers from this command a bad thing or just normal?
>> >
>> > Any help appreciated.
>> >
>> > TIA
>> > Cary LaPora
>> > Database Administrator
>> > The Lancair Company
>>

DBCC PROCCACHE Question.

What exactly does this command show you. I need to know what the numbers it
shows actually mean. I have been having SERIOUS performance issues with my
SQL cluster and just by looking at the numbers from this command it looks
like my stored proc cache just grows and grows. I think...
Are high numbers from this command a bad thing or just normal?
Any help appreciated.
TIA
Cary LaPora
Database Administrator
The Lancair CompanyIf your proccache keeps growing that basically means you are not reusing
your query plans properly. That in and of itself can be a major performance
issue but this is simply one aspect of what could be a host of issues. You
may want to have a look at these:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company|||If that is the case look inside syscacheobjects to see if you have actual
plan reuse.
dbcc cachestats -- will show you hit ratios
dbcc memorystatus -- will show you how memory is divided inside SQL Server
(for SQL Server controlled memory grants)
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company|||Thanks for the info guys.
Gert,
I don't suppose you can point me to any help about those commands. I can't
seem to find them in the T-SQL help and I'm not really sure exactly what the
y
are telling me.
TIA
Cary LaPora
Database Administrator
The Lancair Company
"Gert E.R. Drapers" wrote:

> If that is the case look inside syscacheobjects to see if you have actual
> plan reuse.
> dbcc cachestats -- will show you hit ratios
> dbcc memorystatus -- will show you how memory is divided inside SQL Server
> (for SQL Server controlled memory grants)
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
>
>|||Sure see:
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/defaul...kb;en-us;271624
I will digg up some info on cachestats, not handy right now.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0D87AA70-9DF1-447B-8562-60B02AACADDB@.microsoft.com...[vbcol=seagreen]
> Thanks for the info guys.
> Gert,
> I don't suppose you can point me to any help about those commands. I
> can't
> seem to find them in the T-SQL help and I'm not really sure exactly what
> they
> are telling me.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
> "Gert E.R. Drapers" wrote:
>sql

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/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.

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.

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.

Sunday, March 11, 2012

DBCC INDEXDEFRAG shows more fragmentation but better performance - Why?

am a bit confused about something. I am trying to solve a problem. My
developers wrote a piece of code that inserted 2million rows of data and
then updated the data . .this obviously resulted in significant
fragmentation . . . . scan density reported was quite low . . resulting in
pagiolatch_sh waittypes(this slowed down performance) during reporting
processes (this is partly also to do with the fact that the data and log
files reside on a mirrored array) - pls don't ask, this is a system I have
inherited and as a result I have to live with it for now , I have to work
around these constraints taking into consideration that these export
processes have always completed in less that 5minutes in the past.. .
..obviously something has changed . .I have advised the developers about
re-writing the query so that the inserts are just inserts and not 1)inserts
then 2)updates. . . what I am trying to find an explanation for is
1) I advised for the code to be re-written to limit fragmentation - and it
did 98% scan density
2) logical frag and extent frag was low as well
3) developer runs query and query takes forever!!
a) I advise to run indexdefrag on clustered index
b) extent frag and logical frag still low
c) scan density is now worse
d) BUT query is excuted in 5mins
PS. I am not 'hands-on' just advising so I am waiting for exec plan for the
two scenarios.
I know that the exec plan would be similar as stats has not changed post
index defrag.
any ideas why indxdefrag will report worse scan density?
Olu Adedeji
Do you have multiple files in the filegroup by any chance? If so the only
figure to trust is the Logical fragmentation. Have a look here for more
details:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Sounds like you are getting a lot of memory pressure and the data on the
mirrored array with the log files does not help at all. It sounds like this
routine probably does table scans but you can verify that by looking at the
query plans. Before you did the defrag what was the total page count? And
after what was it? I am willing to bet the large amount of fragmentation
caused the page count to be extremely large and not very full resulting in a
lot of I/O's. The fact the pages were out of order didn't help any either.
Sounds like you would have been better to do a DBCC DBREINDEX instead due to
the large amount of fragmentation.
Andrew J. Kelly SQL MVP
"Olu Adedeji" <anonymous@.email.com> wrote in message
news:uOCXQA2nEHA.4068@.tk2msftngp13.phx.gbl...
> am a bit confused about something. I am trying to solve a problem. My
> developers wrote a piece of code that inserted 2million rows of data and
> then updated the data . .this obviously resulted in significant
> fragmentation . . . . scan density reported was quite low . . resulting in
> pagiolatch_sh waittypes(this slowed down performance) during reporting
> processes (this is partly also to do with the fact that the data and log
> files reside on a mirrored array) - pls don't ask, this is a system I have
> inherited and as a result I have to live with it for now , I have to work
> around these constraints taking into consideration that these export
> processes have always completed in less that 5minutes in the past.. .
> .obviously something has changed . .I have advised the developers about
> re-writing the query so that the inserts are just inserts and not
1)inserts
> then 2)updates. . . what I am trying to find an explanation for is
> 1) I advised for the code to be re-written to limit fragmentation - and it
> did 98% scan density
> 2) logical frag and extent frag was low as well
> 3) developer runs query and query takes forever!!
> a) I advise to run indexdefrag on clustered index
> b) extent frag and logical frag still low
> c) scan density is now worse
> d) BUT query is excuted in 5mins
>
> PS. I am not 'hands-on' just advising so I am waiting for exec plan for
the
> two scenarios.
> I know that the exec plan would be similar as stats has not changed post
> index defrag.
> any ideas why indxdefrag will report worse scan density?
>
> --
> Olu Adedeji
>

DBCC INDEXDEFRAG shows more fragmentation but better performance - Why?

am a bit confused about something. I am trying to solve a problem. My
developers wrote a piece of code that inserted 2million rows of data and
then updated the data . .this obviously resulted in significant
fragmentation . . . . scan density reported was quite low . . resulting in
pagiolatch_sh waittypes(this slowed down performance) during reporting
processes (this is partly also to do with the fact that the data and log
files reside on a mirrored array) - pls don't ask, this is a system I have
inherited and as a result I have to live with it for now , I have to work
around these constraints taking into consideration that these export
processes have always completed in less that 5minutes in the past.. .
.obviously something has changed . .I have advised the developers about
re-writing the query so that the inserts are just inserts and not 1)inserts
then 2)updates. . . what I am trying to find an explanation for is
1) I advised for the code to be re-written to limit fragmentation - and it
did 98% scan density
2) logical frag and extent frag was low as well
3) developer runs query and query takes forever!!
a) I advise to run indexdefrag on clustered index
b) extent frag and logical frag still low
c) scan density is now worse
d) BUT query is excuted in 5mins
PS. I am not 'hands-on' just advising so I am waiting for exec plan for the
two scenarios.
I know that the exec plan would be similar as stats has not changed post
index defrag.
any ideas why indxdefrag will report worse scan density?
--
Olu AdedejiDo you have multiple files in the filegroup by any chance? If so the only
figure to trust is the Logical fragmentation. Have a look here for more
details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Sounds like you are getting a lot of memory pressure and the data on the
mirrored array with the log files does not help at all. It sounds like this
routine probably does table scans but you can verify that by looking at the
query plans. Before you did the defrag what was the total page count? And
after what was it? I am willing to bet the large amount of fragmentation
caused the page count to be extremely large and not very full resulting in a
lot of I/O's. The fact the pages were out of order didn't help any either.
Sounds like you would have been better to do a DBCC DBREINDEX instead due to
the large amount of fragmentation.
--
Andrew J. Kelly SQL MVP
"Olu Adedeji" <anonymous@.email.com> wrote in message
news:uOCXQA2nEHA.4068@.tk2msftngp13.phx.gbl...
> am a bit confused about something. I am trying to solve a problem. My
> developers wrote a piece of code that inserted 2million rows of data and
> then updated the data . .this obviously resulted in significant
> fragmentation . . . . scan density reported was quite low . . resulting in
> pagiolatch_sh waittypes(this slowed down performance) during reporting
> processes (this is partly also to do with the fact that the data and log
> files reside on a mirrored array) - pls don't ask, this is a system I have
> inherited and as a result I have to live with it for now , I have to work
> around these constraints taking into consideration that these export
> processes have always completed in less that 5minutes in the past.. .
> .obviously something has changed . .I have advised the developers about
> re-writing the query so that the inserts are just inserts and not
1)inserts
> then 2)updates. . . what I am trying to find an explanation for is
> 1) I advised for the code to be re-written to limit fragmentation - and it
> did 98% scan density
> 2) logical frag and extent frag was low as well
> 3) developer runs query and query takes forever!!
> a) I advise to run indexdefrag on clustered index
> b) extent frag and logical frag still low
> c) scan density is now worse
> d) BUT query is excuted in 5mins
>
> PS. I am not 'hands-on' just advising so I am waiting for exec plan for
the
> two scenarios.
> I know that the exec plan would be similar as stats has not changed post
> index defrag.
> any ideas why indxdefrag will report worse scan density?
>
> --
> Olu Adedeji
>

DBCC INDEXDEFRAG

We are currently running SQL Server 2000 on single box for the last 2 years.
There were some performance issues on the database and after analysing I saw
that the database required defragmentation. So I executed DBCC INDEXDEFRAG
on all the tables and things went back to normal in terms of the
performance. After two days our application users complained about some un
expected results. After the analysis following is the information.
The table that has 3 columns
1. TarifTypeKey, LanguageKey and Description
2. There is a composite clustered index on TariffTypeKey and LanguageKey
3. The table returns only 5 rows in the following format
1. Download
2. Upgrade
3. Deletion
4. Force
5. Print
4. These values are returned back from the database from a Stored Procedure
and following is the code
SELECT TariffTypeKey, Description
FROM TariffTypesDescription
WHERE LanguageKey = 'EN'
The same query worked fine since the first day. But since I have executed
the script to defragment the indexes we get back results in the following
order
5. Print
3. Deletion
1. Download
2. Upgrade
4. Force
Nothing has changed in the stored procedure. I did some more research and
tried to break down the query into two parts
1. SELECT TariffTypeKey, Description
FROM TariffTypesDescription (until here we get the desired results)
2. WHERE LanguageKey = 'EN' (as soon as we use this the results are changed)
Kindly provide some of your knowledge on this as this is on the live
database. Please do not hesitate to contact me if you need further
information.
-Saj> 1. SELECT TariffTypeKey, Description
> FROM TariffTypesDescription (until here we get the desired results)
> 2. WHERE LanguageKey = 'EN' (as soon as we use this the results are
> changed)
The engine is free to produce the resultset in ANY ORDER if the query does
not have an order-by clause. Your assumption based on past experience is
the problem.|||Thanks a ton for everyone who replied to this question as I already fixed
the problem using ORDER BY clause but I was wondering how come the orders of
the rows changed.
I appreciate it.
"Sajid S. Malik" <sajid_malick@.yahoo.com> wrote in message
news:ucoSH0D$GHA.4704@.TK2MSFTNGP04.phx.gbl...
> We are currently running SQL Server 2000 on single box for the last 2
> years. There were some performance issues on the database and after
> analysing I saw that the database required defragmentation. So I executed
> DBCC INDEXDEFRAG on all the tables and things went back to normal in terms
> of the performance. After two days our application users complained about
> some un expected results. After the analysis following is the information.
> The table that has 3 columns
> 1. TarifTypeKey, LanguageKey and Description
> 2. There is a composite clustered index on TariffTypeKey and LanguageKey
> 3. The table returns only 5 rows in the following format
> 1. Download
> 2. Upgrade
> 3. Deletion
> 4. Force
> 5. Print
> 4. These values are returned back from the database from a Stored
> Procedure and following is the code
> SELECT TariffTypeKey, Description
> FROM TariffTypesDescription
> WHERE LanguageKey = 'EN'
> The same query worked fine since the first day. But since I have executed
> the script to defragment the indexes we get back results in the following
> order
> 5. Print
> 3. Deletion
> 1. Download
> 2. Upgrade
> 4. Force
> Nothing has changed in the stored procedure. I did some more research and
> tried to break down the query into two parts
> 1. SELECT TariffTypeKey, Description
> FROM TariffTypesDescription (until here we get the desired results)
> 2. WHERE LanguageKey = 'EN' (as soon as we use this the results are
> changed)
> Kindly provide some of your knowledge on this as this is on the live
> database. Please do not hesitate to contact me if you need further
> information.
> -Saj
>

Thursday, March 8, 2012

DBCC INDEXDEFRAG

dbcc freeproccache only solution??

Recently upgranded to SQL2005 sp1
Daily we have times of very poor performance. We tracked down to full table
scans occuring, indexes not used.
Perform a dbcc freeproccache to solve. Any idea why this is occurring?
Happening more often since upgrade - did notice same issue when on sql2000...
Any ideas?
tia
chris
On Fri, 11 May 2007 15:55:00 -0700, Chris
<Chris@.discussions.microsoft.com> wrote:

>Recently upgranded to SQL2005 sp1
>Daily we have times of very poor performance. We tracked down to full table
>scans occuring, indexes not used.
>Perform a dbcc freeproccache to solve. Any idea why this is occurring?
>Happening more often since upgrade - did notice same issue when on sql2000...
>Any ideas?
Many ideas.
But they all start with isolating the problem code, the DDL, and the
stats involved.
J.
|||Sounds like parameter sniffing issues. Did you update the stats after the
upgrade? Have a look at these:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
My guess is that most likely your stored proc query plans are becoming
invalid.
A simple test would be to find one called a lot and do a recompile on it and
see if the performance gets better.
If it does, there's a few things to loook at.
Are you doing a lot of updates/inserts/deletes that invalidate the stats for
your indices? If so, you can update stats more often (among other things).
Also lok for stuff like a "IF @.FOO then X else Y" Typically the first time
the procedure is called, a particular path is followed (say @.FOO Is true,
then X is followed.) This is the query plan that will be cashed. If later
the call has @.FOO is false and Y is followed the query plan will be invalid.
So that's something else to look at.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

dbcc freeproccache only solution??

Recently upgranded to SQL2005 sp1
Daily we have times of very poor performance. We tracked down to full table
scans occuring, indexes not used.
Perform a dbcc freeproccache to solve. Any idea why this is occurring?
Happening more often since upgrade - did notice same issue when on sql2000..
.
Any ideas?
tia
chrisOn Fri, 11 May 2007 15:55:00 -0700, Chris
<Chris@.discussions.microsoft.com> wrote:

>Recently upgranded to SQL2005 sp1
>Daily we have times of very poor performance. We tracked down to full table
>scans occuring, indexes not used.
>Perform a dbcc freeproccache to solve. Any idea why this is occurring?
>Happening more often since upgrade - did notice same issue when on sql2000.
.
>Any ideas?
Many ideas.
But they all start with isolating the problem code, the DDL, and the
stats involved.
J.|||Sounds like parameter sniffing issues. Did you update the stats after the
upgrade? Have a look at these:
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx
http://www.microsoft.com/technet/pr...005/recomp.mspx
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
My guess is that most likely your stored proc query plans are becoming
invalid.
A simple test would be to find one called a lot and do a recompile on it and
see if the performance gets better.
If it does, there's a few things to loook at.
Are you doing a lot of updates/inserts/deletes that invalidate the stats for
your indices? If so, you can update stats more often (among other things).
Also lok for stuff like a "IF @.FOO then X else Y" Typically the first time
the procedure is called, a particular path is followed (say @.FOO Is true,
then X is followed.) This is the query plan that will be cashed. If later
the call has @.FOO is false and Y is followed the query plan will be invalid.
So that's something else to look at.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

dbcc freeproccache only solution??

Recently upgranded to SQL2005 sp1
Daily we have times of very poor performance. We tracked down to full table
scans occuring, indexes not used.
Perform a dbcc freeproccache to solve. Any idea why this is occurring?
Happening more often since upgrade - did notice same issue when on sql2000...
Any ideas?
tia
chrisOn Fri, 11 May 2007 15:55:00 -0700, Chris
<Chris@.discussions.microsoft.com> wrote:
>Recently upgranded to SQL2005 sp1
>Daily we have times of very poor performance. We tracked down to full table
>scans occuring, indexes not used.
>Perform a dbcc freeproccache to solve. Any idea why this is occurring?
>Happening more often since upgrade - did notice same issue when on sql2000...
>Any ideas?
Many ideas.
But they all start with isolating the problem code, the DDL, and the
stats involved.
J.|||Sounds like parameter sniffing issues. Did you update the stats after the
upgrade? Have a look at these:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
My guess is that most likely your stored proc query plans are becoming
invalid.
A simple test would be to find one called a lot and do a recompile on it and
see if the performance gets better.
If it does, there's a few things to loook at.
Are you doing a lot of updates/inserts/deletes that invalidate the stats for
your indices? If so, you can update stats more often (among other things).
Also lok for stuff like a "IF @.FOO then X else Y" Typically the first time
the procedure is called, a particular path is followed (say @.FOO Is true,
then X is followed.) This is the query plan that will be cashed. If later
the call has @.FOO is false and Y is followed the query plan will be invalid.
So that's something else to look at.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Saturday, February 25, 2012

DBCC DBREINDEX and diskspace

I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?It will fail and the index rebuild will be rolled back. You need 1.2X the
size of the data in your table in free space in order to use DBREINDEX. If
you cannot get more space, consider using DBCC INDEXDEFRAG.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?|||This helps me out alot:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure admin_DBCCDBReindex
as
declare @.Tablename varchar(128),@.db varchar(128),@.exec varchar(256)
set nocount on
set @.db = (select db_name())
declare @.Table table(TableName varchar(128))
insert into @.Table
select so.name
from sysobjects so
where so.name not like 'sys%' and
so.name not like 'dt%' and
so.name not like 'tmp%' and
so.xtype = 'u'
order by so.name
declare mycur cursor for (select TableName from @.Table)
open mycur
fetch next from mycur into @.TableName
while @.@.fetch_status = 0
begin
set @.exec = 'dbcc dbreindex (''' + @.db + '.' + 'dbo.' + @.TableName + ''')'
exec (@.exec)
BACKUP LOG [tmpReindex] TO [tmp]
WITH INIT , NOUNLOAD , NAME = N'tmp log backup', NOSKIP , STATS = 10,
NOFORMAT
fetch next from mycur into @.TableName
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?|||As Tom suggests, consider DBCC INDEXDEFRAG. There's a whitepaper at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
that explains the difference between the two, how to choose between them,
performance comparison etc.
Let us know if you have any more questions after reading the whitepaper.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?

DBCC DBREINDEX

After we run a dbcc dbreindex for a specific table, we have noticed a huge
drop in, Logical Scan Fragmentation and definite performance improvements.
However, Extent Scan Fragmentation still seems to remain quite high, in most
cases over 50%.
Is there any reason for this?
Thx
This is expected when the index spans multiple files. Also, the number is
not relevant for heaps (tables with no clustered index).
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx
|||Hi,
Have a look into this article:-
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx

DBCC DBREINDEX

After we run a dbcc dbreindex for a specific table, we have noticed a huge
drop in, Logical Scan Fragmentation and definite performance improvements.
However, Extent Scan Fragmentation still seems to remain quite high, in most
cases over 50%.
Is there any reason for this?
ThxThis is expected when the index spans multiple files. Also, the number is
not relevant for heaps (tables with no clustered index).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx|||Hi,
Have a look into this article:-
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx

Friday, February 24, 2012

DBCC DBREINDEX

After we run a dbcc dbreindex for a specific table, we have noticed a huge
drop in, Logical Scan Fragmentation and definite performance improvements.
However, Extent Scan Fragmentation still seems to remain quite high, in most
cases over 50%.
Is there any reason for this?
ThxThis is expected when the index spans multiple files. Also, the number is
not relevant for heaps (tables with no clustered index).
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx|||Hi,
Have a look into this article:-
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx

Sunday, March 25, 2012

DBCC SHOWCONTIG results: Good/bad?

DBCC SHOWCONTIG question

DBCC SHOWCONTIG & DB Performance

Wednesday, March 21, 2012

DBCC PROCCACHE Question.

DBCC PROCCACHE Question.

DBCC PROCCACHE Question.

Monday, March 19, 2012

DBCC memusage problem

DBCC memusage problem

DBCC memusage problem

Sunday, March 11, 2012

DBCC INDEXDEFRAG shows more fragmentation but better performance - Why?

DBCC INDEXDEFRAG shows more fragmentation but better performance - Why?

DBCC INDEXDEFRAG

Thursday, March 8, 2012

DBCC INDEXDEFRAG

dbcc freeproccache only solution??

dbcc freeproccache only solution??

dbcc freeproccache only solution??

Saturday, February 25, 2012

DBCC DBREINDEX and diskspace

DBCC DBREINDEX

DBCC DBREINDEX

Friday, February 24, 2012

DBCC DBREINDEX

DBfile usage

Blog Archive

About Me