Issues with DBCC SHOWCONTIG

I am trying to resolve database fragmentation and I am having two issues wit
h
the DBCC SHOWCONTIG command.
1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
different values for logical fragmentation then running DBCC
SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
should be considered as correct?
2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
TABLERESULTS shows results for indexes that do not appear in the QA Object
Browser or the EM Manage Indexes/Relationship window. Consequently, running
DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
index name. What is going on here?
BONUS QUESTION:
1. Which is a more accurate reflection of fragmentation: Logical
Fragmentation or Scan Density?1) Can you elaborate, or show an example?
2) What indexid? Remember that 0 is the datapages for a table, and that is n
ot an index.
3) Logical Fragmentation. Scan Density will be (as I remember) removed from
next version as it will
show bogus values of the filegroup have more than one file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
>I am trying to resolve database fragmentation and I am having two issues wi
th
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently, runnin
g
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?|||1. This is known bug in SQL Server 2000. If you specify ALL_INDEXES or all
tables (by not passing in any parameter), logical fragmentation information
for some indexes might be a little bit off (It does not matter if the index
is large; the difference will show up if the index is small).
The problem is fixed in SQL Server 2000 SP4 (which is in Beta stage now).
2. I have no clue on this. Remember, index could be dropped between you
call DBCC SHOWCONTIG and when you use DBCC INDEXDEFRAG. That might be the
problem you see.
Bonus 1: Agree with Tibor's reply, Logical Fragmentation, with one
correction: for backward compatiable, Scan Density will still be supported
in DBCC SHOWCONTIG when SQL Server 2005 ships. (DBCC SHOWCONTIG will be
deprecated and replaced by a dynamic managed view - checkout SQL Server 2005
Beta 2 for more details)
Hope this helps.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
> I am trying to resolve database fragmentation and I am having two issues
with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently,
running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?

Issues with DBCC SHOWCONTIG

I am trying to resolve database fragmentation and I am having two issues with
the DBCC SHOWCONTIG command.
1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
different values for logical fragmentation then running DBCC
SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
should be considered as correct?
2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
TABLERESULTS shows results for indexes that do not appear in the QA Object
Browser or the EM Manage Indexes/Relationship window. Consequently, running
DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
index name. What is going on here?
BONUS QUESTION:
1. Which is a more accurate reflection of fragmentation: Logical
Fragmentation or Scan Density?
1) Can you elaborate, or show an example?
2) What indexid? Remember that 0 is the datapages for a table, and that is not an index.
3) Logical Fragmentation. Scan Density will be (as I remember) removed from next version as it will
show bogus values of the filegroup have more than one file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
>I am trying to resolve database fragmentation and I am having two issues with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently, running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?
|||1. This is known bug in SQL Server 2000. If you specify ALL_INDEXES or all
tables (by not passing in any parameter), logical fragmentation information
for some indexes might be a little bit off (It does not matter if the index
is large; the difference will show up if the index is small).
The problem is fixed in SQL Server 2000 SP4 (which is in Beta stage now).
2. I have no clue on this. Remember, index could be dropped between you
call DBCC SHOWCONTIG and when you use DBCC INDEXDEFRAG. That might be the
problem you see.
Bonus 1: Agree with Tibor's reply, Logical Fragmentation, with one
correction: for backward compatiable, Scan Density will still be supported
in DBCC SHOWCONTIG when SQL Server 2005 ships. (DBCC SHOWCONTIG will be
deprecated and replaced by a dynamic managed view - checkout SQL Server 2005
Beta 2 for more details)
Hope this helps.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
> I am trying to resolve database fragmentation and I am having two issues
with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename>) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently,
running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?

isqlw connection problems

I'm trying to connect to remote sql servers. I can connect to and work with
the servers in EM. But, when I click on "Tools|SQL Query Analyzer", it times
out trying to connect with the error messages:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or
access denied.
Followed by:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Conn
ect())
Why can't I connect with Query Analyzer, but I can with Enterprise Manager?
How do I resolve this?
I have no problems using Query Analyzer and connecting to my local sql
servers.
I have no problems using Query Analyzer to connect to local and remote sql
servers on my other computer, just with this one computer.
Thanks,
Wayne SheffieldFYI: I did check out http://support.microsoft.com/?kbid=328306, but nothing
there helped out.
Thanks,
Wayne
"Wayne Sheffield" <wayne_sheffield@.hotmail.com> wrote in message
news:OB2MF3caEHA.2812@.TK2MSFTNGP11.phx.gbl...
> I'm trying to connect to remote sql servers. I can connect to and work
with
> the servers in EM. But, when I click on "Tools|SQL Query Analyzer", it
times
> out trying to connect with the error messages:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does n
ot exist or
> access denied.
> Followed by:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Co
nnect())
> Why can't I connect with Query Analyzer, but I can with Enterprise
Manager?
> How do I resolve this?
> I have no problems using Query Analyzer and connecting to my local sql
> servers.
> I have no problems using Query Analyzer to connect to local and remote sql
> servers on my other computer, just with this one computer.
> Thanks,
> Wayne Sheffield
>|||Are you using the same account as EM uses?
Andrew J. Kelly SQL MVP
"Wayne Sheffield" <wayne_sheffield@.hotmail.com> wrote in message
news:OB2MF3caEHA.2812@.TK2MSFTNGP11.phx.gbl...
> I'm trying to connect to remote sql servers. I can connect to and work
with
> the servers in EM. But, when I click on "Tools|SQL Query Analyzer", it
times
> out trying to connect with the error messages:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does n
ot exist or
> access denied.
> Followed by:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Co
nnect())
> Why can't I connect with Query Analyzer, but I can with Enterprise
Manager?
> How do I resolve this?
> I have no problems using Query Analyzer and connecting to my local sql
> servers.
> I have no problems using Query Analyzer to connect to local and remote sql
> servers on my other computer, just with this one computer.
> Thanks,
> Wayne Sheffield
>|||Yes. When you open QA this way, it uses the EM account settings.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uiF7z8daEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Are you using the same account as EM uses?
> --
> Andrew J. Kelly SQL MVP
>
> "Wayne Sheffield" <wayne_sheffield@.hotmail.com> wrote in message
> news:OB2MF3caEHA.2812@.TK2MSFTNGP11.phx.gbl...
> with
> times
or[vbcol=seagreen]
> Manager?
sql[vbcol=seagreen]
>

isqlw connection problems

I'm trying to connect to remote sql servers. I can connect to and work with
the servers in EM. But, when I click on "Tools|SQL Query Analyzer", it times
out trying to connect with the error messages:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.
Followed by:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect())
Why can't I connect with Query Analyzer, but I can with Enterprise Manager?
How do I resolve this?
I have no problems using Query Analyzer and connecting to my local sql
servers.
I have no problems using Query Analyzer to connect to local and remote sql
servers on my other computer, just with this one computer.
Thanks,
Wayne Sheffield
FYI: I did check out http://support.microsoft.com/?kbid=328306, but nothing
there helped out.
Thanks,
Wayne
"Wayne Sheffield" <wayne_sheffield@.hotmail.com> wrote in message
news:OB2MF3caEHA.2812@.TK2MSFTNGP11.phx.gbl...
> I'm trying to connect to remote sql servers. I can connect to and work
with
> the servers in EM. But, when I click on "Tools|SQL Query Analyzer", it
times
> out trying to connect with the error messages:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> Followed by:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect())
> Why can't I connect with Query Analyzer, but I can with Enterprise
Manager?
> How do I resolve this?
> I have no problems using Query Analyzer and connecting to my local sql
> servers.
> I have no problems using Query Analyzer to connect to local and remote sql
> servers on my other computer, just with this one computer.
> Thanks,
> Wayne Sheffield
>
|||Are you using the same account as EM uses?
Andrew J. Kelly SQL MVP
"Wayne Sheffield" <wayne_sheffield@.hotmail.com> wrote in message
news:OB2MF3caEHA.2812@.TK2MSFTNGP11.phx.gbl...
> I'm trying to connect to remote sql servers. I can connect to and work
with
> the servers in EM. But, when I click on "Tools|SQL Query Analyzer", it
times
> out trying to connect with the error messages:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> Followed by:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect())
> Why can't I connect with Query Analyzer, but I can with Enterprise
Manager?
> How do I resolve this?
> I have no problems using Query Analyzer and connecting to my local sql
> servers.
> I have no problems using Query Analyzer to connect to local and remote sql
> servers on my other computer, just with this one computer.
> Thanks,
> Wayne Sheffield
>
|||Yes. When you open QA this way, it uses the EM account settings.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uiF7z8daEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Are you using the same account as EM uses?
> --
> Andrew J. Kelly SQL MVP
>
> "Wayne Sheffield" <wayne_sheffield@.hotmail.com> wrote in message
> news:OB2MF3caEHA.2812@.TK2MSFTNGP11.phx.gbl...
> with
> times
or[vbcol=seagreen]
> Manager?
sql
>