DBREINDEX/INDEXDEFRAG a few questions

I pretty much understand the differences between DBCC DBREINDEX and DBCC INDEXDEFRAG. However, I need the forums help to understand a few specific issues relating to clustered/non-clustered indexes and the advantages/disadvantages of running the DBCC DBREINDEX/INDEXDEFRAG against the table or against each specific index...

"If a table has a clustered index, it's only necessary to re-index the clustered index because any non-clustered indexes on that table will be automatically re-indexed as well."

I think the above statement is true for DBCC DBREINDEX but is the following statement true for DBCC INDEXDEFRAG:-

"If a table has a clustered index, it's only necessary to Index Defrag the clustered index because any non-clustered indexes on that table will be automatically defragged as well."

Following on from the above, is there any advantage with an index maintenance strategy to individually running DBCC DBREINDEX against each specific index as opposed to running it against the table and letting SQL sort out the underlying indexes? Does the same apply to DBCC INDEXDEFRAG?

Regards,

Clive"If a table has a clustered index, it's only necessary to Index Defrag the clustered index because any non-clustered indexes on that table will be automatically defragged as well."

I just did a test of this, and the answer is no, the non-clustered indexes will not be defragged as well. This works out as a slight benefit to indexdefrag, as it saves you the time of rebuilding the non-clustered indexes at the same time as the clustered index.

If you have all non-clustered indexes, then there would be a slight benefit in space-savings by running dbcc dbreindex on each index separately. If there is a clustered index, then running dbreindex on the clustered index rebuilds all of the indexes. This would be a waste of any time spent on each index being rebuilt individually. Does this help?|||Yes, that helps. Thank you. In fact, I just put together a test myself and found the same result. Not unsurprisingly, the DBREINDEX produced 100% scan density on the clustered index and the same or close to it on the non-clustered indexes. However, I was surprised that INDEXDEFRAG on the clustered index actually lowered scan-density by a few percent!

Regards,

Clive|||What was the scan density when you started?

The main problem with indexdefrag is that it is only moving pages from and to page locations that are already allocated to the index being defragmented. It may coalesce some unused space among these allocations, but it does not touch anything that is already allocated to another index page or data page of the table. Since it has to work around all of these prior allocations, you almost never get a nice 100% result from indexdefrag, unless all you had to start with was the clustered index.

dbo question

Hi
I can see that in some of our databases, the user 'dbo' are mapped to a
specific loginname where in others it's not mapped to anything. How can I
change this so the 'dbo' user isn't mapped to a specific useraccount. The
reason is that I'd like to remove the login that's set as 'dbo' for those
databases.
I've looked in BOL, but I'm not quite sure about the steps to perform to get
this fixed. I know that all members of the Sysadmin role is mapped to 'dbo'
so that's what I'd like to have rather than mapping a specific user to the
'dbo' user.
Since it's in our production environment is has to be changed, I'd like to
be fairly sure about the steps to do before I just go ahead and change
it...;-)
Regards
SteenHello Steen,
You can use sp_changedbowner like this:
use <mydb>
exec sp_changedbowner 'sa'
This will map the sa login to dbo user in the mydb database. Why do you want
to remove the dbo user?
Mark.
> Hi
> I can see that in some of our databases, the user 'dbo' are mapped to
> a specific loginname where in others it's not mapped to anything. How
> can I change this so the 'dbo' user isn't mapped to a specific
> useraccount. The reason is that I'd like to remove the login that's
> set as 'dbo' for those databases.
> I've looked in BOL, but I'm not quite sure about the steps to perform
> to get this fixed. I know that all members of the Sysadmin role is
> mapped to 'dbo' so that's what I'd like to have rather than mapping a
> specific user to the 'dbo' user.
> Since it's in our production environment is has to be changed, I'd
> like to be fairly sure about the steps to do before I just go ahead
> and change it...;-)
> Regards
> Steen|||Hi Mark
The reason for removing this specific dbo user, is because we are trying to
"clean up" our SQL server installations. We have 8 servers for various
applications and there haven't really been any corporate guidelines for how
to install them. They are therefore installed and configured in different
ways.
As I understand the sp_changedbowner, it will map the username spcified to
the dbo role, but I'm not sure that's what I want.
If I look at some of the other SQLServers, it looks like no specific
loginname has been mapped to 'dbo'. In this case I assume that dbo is the
loginnames which are members of the System Administrators Role on the
server. I don't know if this scenario is advisable or not, but would it be
possible to remove the dbo mapping to a specific loginname and change it to
a "blank" loginname?
/Steen
Mark Allison wrote:
> Hello Steen,
> You can use sp_changedbowner like this:
> use <mydb>
> exec sp_changedbowner 'sa'
> This will map the sa login to dbo user in the mydb database. Why do
> you want to remove the dbo user?
> Mark.
>
>> Hi
>> I can see that in some of our databases, the user 'dbo' are mapped to
>> a specific loginname where in others it's not mapped to anything. How
>> can I change this so the 'dbo' user isn't mapped to a specific
>> useraccount. The reason is that I'd like to remove the login that's
>> set as 'dbo' for those databases.
>> I've looked in BOL, but I'm not quite sure about the steps to perform
>> to get this fixed. I know that all members of the Sysadmin role is
>> mapped to 'dbo' so that's what I'd like to have rather than mapping a
>> specific user to the 'dbo' user.
>> Since it's in our production environment is has to be changed, I'd
>> like to be fairly sure about the steps to do before I just go ahead
>> and change it...;-)
>> Regards
>> Steen|||dbo is always mapped to something. It is safe to map all of them to sa or
some other login you expect will always exist... I use either sa or a
trusted login so moving databases arouns isn't quite so much trouble.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u36YSpI$EHA.612@.TK2MSFTNGP09.phx.gbl...
> Hi Mark
> The reason for removing this specific dbo user, is because we are trying
to
> "clean up" our SQL server installations. We have 8 servers for various
> applications and there haven't really been any corporate guidelines for
how
> to install them. They are therefore installed and configured in different
> ways.
> As I understand the sp_changedbowner, it will map the username spcified to
> the dbo role, but I'm not sure that's what I want.
> If I look at some of the other SQLServers, it looks like no specific
> loginname has been mapped to 'dbo'. In this case I assume that dbo is the
> loginnames which are members of the System Administrators Role on the
> server. I don't know if this scenario is advisable or not, but would it be
> possible to remove the dbo mapping to a specific loginname and change it
to
> a "blank" loginname?
> /Steen
>
> Mark Allison wrote:
> > Hello Steen,
> >
> > You can use sp_changedbowner like this:
> >
> > use <mydb>
> > exec sp_changedbowner 'sa'
> >
> > This will map the sa login to dbo user in the mydb database. Why do
> > you want to remove the dbo user?
> >
> > Mark.
> >
> >
> >> Hi
> >>
> >> I can see that in some of our databases, the user 'dbo' are mapped to
> >> a specific loginname where in others it's not mapped to anything. How
> >> can I change this so the 'dbo' user isn't mapped to a specific
> >> useraccount. The reason is that I'd like to remove the login that's
> >> set as 'dbo' for those databases.
> >>
> >> I've looked in BOL, but I'm not quite sure about the steps to perform
> >> to get this fixed. I know that all members of the Sysadmin role is
> >> mapped to 'dbo' so that's what I'd like to have rather than mapping a
> >> specific user to the 'dbo' user.
> >>
> >> Since it's in our production environment is has to be changed, I'd
> >> like to be fairly sure about the steps to do before I just go ahead
> >> and change it...;-)
> >>
> >> Regards
> >> Steen
>|||Thanks Wayne,
I investigated it a little bit further, and I think I found out what
confused me. When I look at the dbo user for a database in EM, it shows no
Login Name. If I then look at the same db in QA (sp_helpdb) it shows the
owner ok.
The cases where EM doesn't show a login name, seems to be the ones where the
dbo isn't mapped to a login name that has been created as a login in SQL
but is a user that's member of the SystemAdministrator role (and/or in the
local admin groups on the server). This might be obvious and clear for
others, but it confused me a little bit...;-).
Thanks for your inputs.
Regards
Steen
Wayne Snyder wrote:
> dbo is always mapped to something. It is safe to map all of them to
> sa or some other login you expect will always exist... I use either
> sa or a trusted login so moving databases arouns isn't quite so much
> trouble.
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:u36YSpI$EHA.612@.TK2MSFTNGP09.phx.gbl...
>> Hi Mark
>> The reason for removing this specific dbo user, is because we are
>> trying to "clean up" our SQL server installations. We have 8 servers
>> for various applications and there haven't really been any corporate
>> guidelines for how to install them. They are therefore installed and
>> configured in different ways.
>> As I understand the sp_changedbowner, it will map the username
>> spcified to the dbo role, but I'm not sure that's what I want.
>> If I look at some of the other SQLServers, it looks like no specific
>> loginname has been mapped to 'dbo'. In this case I assume that dbo
>> is the loginnames which are members of the System Administrators
>> Role on the server. I don't know if this scenario is advisable or
>> not, but would it be possible to remove the dbo mapping to a
>> specific loginname and change it to a "blank" loginname?
>> /Steen
>>
>> Mark Allison wrote:
>> Hello Steen,
>> You can use sp_changedbowner like this:
>> use <mydb>
>> exec sp_changedbowner 'sa'
>> This will map the sa login to dbo user in the mydb database. Why do
>> you want to remove the dbo user?
>> Mark.
>>
>> Hi
>> I can see that in some of our databases, the user 'dbo' are mapped
>> to a specific loginname where in others it's not mapped to
>> anything. How can I change this so the 'dbo' user isn't mapped to
>> a specific useraccount. The reason is that I'd like to remove the
>> login that's set as 'dbo' for those databases.
>> I've looked in BOL, but I'm not quite sure about the steps to
>> perform to get this fixed. I know that all members of the Sysadmin
>> role is mapped to 'dbo' so that's what I'd like to have rather
>> than mapping a specific user to the 'dbo' user.
>> Since it's in our production environment is has to be changed, I'd
>> like to be fairly sure about the steps to do before I just go ahead
>> and change it...;-)
>> Regards
>> Steen

DBNETLIB Error

Hi All,

I have a problem with a specific query which causes the error below. It runs fine on the server but refuses to work on my Query Analyzer.

select a.cola
from tablea a
where a.cola not in (select b.cola from tableb b)

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

I have MDAC 2.8 installed. SQL Server 2000. There are no entries in the logs, no backups running or deadlocks detected. My Collegues machine runs it fine?

Any Ideas please? All help gratefully received...

Thanks,
JimAre you on windows 2003?
Try to execute other query statements to ensure no issues on your client machine.|||I was encountering similar problems with an application that retrieves data from a remote server (some SQL7, some SQL2K) and inserts into the local server (SQL2K in my case). A particular query ran in T-SQL but not from the ADO object when querying a SQL7 remote server; other queries worked fine.

In DBForums I saw many references to issues with MDAC 2.8, so I rolled MDAC back to 2.6 and discovered that a "Timeout Expired." error was being returned to my app. I then tried the query against another remote server and it worked fine. Looks like:

1 - MDAC 2.8 was shielding me from being able to see the real error, which was a timeout on the SQLServer 7 remote server.
2 - SQL7 or MDAC on the remote server was having trouble handling the query; KB 300519 recommends applying SQL7 SP4.|||I was encountering similar problems with an application that retrieves data from a remote server (some SQL7, some SQL2K) and inserts into the local server (SQL2K in my case). A particular query ran in T-SQL but not from the ADO object when querying a SQL7 remote server; other queries worked fine.

In DBForums I saw many references to issues with MDAC 2.8, so I rolled MDAC back to 2.6 and discovered that a "Timeout Expired." error was being returned to my app. I then tried the query against another remote server and it worked fine. Looks like:

1 - MDAC 2.8 was shielding me from being able to see the real error, which was a timeout on the SQLServer 7 remote server.
2 - SQL7 or MDAC on the remote server was having trouble handling the query; KB 300519 recommends applying SQL7 SP4.|||In any case ensure to maintain similar versions of MDAC if this kind of data compilation is involved.