Please help Index slowed down our production system

Hi all,
I have a query connecting users to my products by and user groups and
today I added a nonclustered index to my table the one that maps users
to user groups (added a nonclusted index to both userID and UserGroups
in ASC order). About 6 hours later our CPU usage went from .01 - .10%
to 100+%? So I removed it and disable some parts of my SQL to
improve the speed. What's happening here? Please help? Would
removing the index change my table back to it's previous index'
please helpppppppppppppppp...
Thx
MStill UPDATE STATISTICS.
"mazdotnet" <maflatoun@.gmail.com> wrote in message
news:1194993911.116978.257150@.v2g2000hsf.googlegroups.com...
> Hi all,
> I have a query connecting users to my products by and user groups and
> today I added a nonclustered index to my table the one that maps users
> to user groups (added a nonclusted index to both userID and UserGroups
> in ASC order). About 6 hours later our CPU usage went from .01 - .10%
> to 100+%? So I removed it and disable some parts of my SQL to
> improve the speed. What's happening here? Please help? Would
> removing the index change my table back to it's previous index'
> please helpppppppppppppppp...
>
> Thx
> M
>|||Can you show us the query + info about the indexes?
"mazdotnet" <maflatoun@.gmail.com> wrote in message
news:1194993911.116978.257150@.v2g2000hsf.googlegroups.com...
> Hi all,
> I have a query connecting users to my products by and user groups and
> today I added a nonclustered index to my table the one that maps users
> to user groups (added a nonclusted index to both userID and UserGroups
> in ASC order). About 6 hours later our CPU usage went from .01 - .10%
> to 100+%? So I removed it and disable some parts of my SQL to
> improve the speed. What's happening here? Please help? Would
> removing the index change my table back to it's previous index'
> please helpppppppppppppppp...
>
> Thx
> M
>

Planned Migration from SQL 2000 to 2005

Although we absolutely need to keep from updating our many SQL 2000 databases
to 2005 versions, I'd like to get our technical users familiar with the new
Management Studio / tools. Is there any reason not to upgrade client
machines to 2005 Management Studio, even though we aren't actually running
SQL Server 2005 anywhere yet? Thanks.
This is totaly fine and supported.
Peter Saddow [MSFT]
SQL Server 2005 Tools
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Brown" <RichardBrown@.discussions.microsoft.com> wrote in message
news:1D7331E9-5BB9-42BA-A86F-9C532DDD803E@.microsoft.com...
> Although we absolutely need to keep from updating our many SQL 2000
> databases
> to 2005 versions, I'd like to get our technical users familiar with the
> new
> Management Studio / tools. Is there any reason not to upgrade client
> machines to 2005 Management Studio, even though we aren't actually running
> SQL Server 2005 anywhere yet? Thanks.

PK column

I need to make is to my users can not accidentally delete or replace the pK field, how do I do that anyone got any suggestions??Ummmm..huh :confused:

USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY)
GO

INSERT INTO myTable99(Col1) SELECT 1 UNION ALL SELECT 2
GO

SELECT * FROM myTable99
GO

DELETE FROM myTable99 WHERE Col1 = 1
GO

SELECT * FROM myTable99
GO

UPDATE myTable99 SET Col1 = 1 WHERE Col1 = 2
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Ummmm..huh :confused:Yeah, what Brett said.

-PatP|||use pubs
go
deny update, delete on authors (au_id) to public
go|||thank you so much guys I appreciate it :). How do you donate to this website anyways??|||I explained that incorrectly what I am trying to say is I dont want my users to delete, or replace the data in the primary key field. Does that Make sense?|||Then this will suffice:

deny update on <your_table>(<your_primary_key>) to <your_users_or_public>

pk & trailing spaces

I have a situation where the primary key is:
user_id char(20) not null
user_cd char(2) not null
allows users to store trailing spaces, which in turn makes the key not unique.
For example it allows the following, one with spaces one without:
ernie 01
ernie 01
This in turn causes processing problems in the app.
How do I fix this problem? BTW, ANSI_PADDING is turned off.
Thanks,
GracieUse VARCHAR not CHAR.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||What if I can't?
"gracie" wrote:
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||Char is a fixed length datatype, it will always pad spaces to length of data declaration with
spaces. Did you mean varchar?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1CDF624A-3879-4BD9-81F1-01A4043E17D7@.microsoft.com...
> What if I can't?
> "gracie" wrote:
>> I have a situation where the primary key is:
>> user_id char(20) not null
>> user_cd char(2) not null
>> allows users to store trailing spaces, which in turn makes the key not unique.
>> For example it allows the following, one with spaces one without:
>> ernie 01
>> ernie 01
>> This in turn causes processing problems in the app.
>> How do I fix this problem? BTW, ANSI_PADDING is turned off.
>> Thanks,
>> Gracie|||Is it by design that you allow users to put trailing spaces? If not make a
small change in the insert statement on the web page sql statement...
RTRIM(user_id)
Am suggesting this only if you can't change the datatype to varchar.
Thanks
GYK
"gracie" wrote:
> What if I can't?
> "gracie" wrote:
> > I have a situation where the primary key is:
> >
> > user_id char(20) not null
> > user_cd char(2) not null
> >
> > allows users to store trailing spaces, which in turn makes the key not unique.
> >
> > For example it allows the following, one with spaces one without:
> >
> > ernie 01
> > ernie 01
> >
> > This in turn causes processing problems in the app.
> >
> > How do I fix this problem? BTW, ANSI_PADDING is turned off.
> >
> > Thanks,
> > Gracie|||"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
>I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
> unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
>
I don't understand.
For char(20)
'ernie'
and
'ernie '
are stored the same, as
'ernie '
and are considered duplicates.
ANSI_PADDING has no effect on non-nullable char columns. ANSI_PADDING
controls whether trailing blanks are trimmed from varchar columns, and
whether nullable char columns are padded to width before being stored (this
is because nullable char columns are actually stored as as varchars).
But in general, I agree with what others have said: don't use for user_id,
use varchar. CHAR is ok for fixed-width coded like user_cd, but for data
which actually varies in length, varchar is better.
David|||Gracie,
I can't reproduce the behavior you describe. Regardless of the data types
or collations on the columns, ('ernie','01') and ('ernie ','01') will
be considered
duplicate keys, since ('ernie' = 'ernie ') and ('01' = '01') is true.
Trailing spaces
are ignored in string comparisons for all string data types and for all
collations,
as far as I know.
Steve Kass
Drew University
gracie wrote:
>I have a situation where the primary key is:
>user_id char(20) not null
>user_cd char(2) not null
>allows users to store trailing spaces, which in turn makes the key not unique.
>For example it allows the following, one with spaces one without:
>ernie 01
>ernie 01
>This in turn causes processing problems in the app.
>How do I fix this problem? BTW, ANSI_PADDING is turned off.
>Thanks,
>Gracie
>

pk & trailing spaces

I have a situation where the primary key is:
user_id char(20) not null
user_cd char(2) not null
allows users to store trailing spaces, which in turn makes the key not uniqu
e.
For example it allows the following, one with spaces one without:
ernie 01
ernie 01
This in turn causes processing problems in the app.
How do I fix this problem? BTW, ANSI_PADDING is turned off.
Thanks,
GracieUse VARCHAR not CHAR.
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||What if I can't?
"gracie" wrote:

> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not uni
que.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||Char is a fixed length datatype, it will always pad spaces to length of data
declaration with
spaces. Did you mean varchar?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1CDF624A-3879-4BD9-81F1-01A4043E17D7@.microsoft.com...[vbcol=seagreen]
> What if I can't?
> "gracie" wrote:
>|||Is it by design that you allow users to put trailing spaces? If not make a
small change in the insert statement on the web page sql statement...
RTRIM(user_id)
Am suggesting this only if you can't change the datatype to varchar.
Thanks
GYK
"gracie" wrote:
[vbcol=seagreen]
> What if I can't?
> "gracie" wrote:
>|||"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
>I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
> unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
>
I don't understand.
For char(20)
'ernie'
and
'ernie '
are stored the same, as
'ernie '
and are considered duplicates.
ANSI_PADDING has no effect on non-nullable char columns. ANSI_PADDING
controls whether trailing blanks are trimmed from varchar columns, and
whether nullable char columns are padded to width before being stored (this
is because nullable char columns are actually stored as as varchars).
But in general, I agree with what others have said: don't use for user_id,
use varchar. CHAR is ok for fixed-width coded like user_cd, but for data
which actually varies in length, varchar is better.
David|||Gracie,
I can't reproduce the behavior you describe. Regardless of the data types
or collations on the columns, ('ernie','01') and ('ernie ','01') will
be considered
duplicate keys, since ('ernie' = 'ernie ') and ('01' = '01') is true.
Trailing spaces
are ignored in string comparisons for all string data types and for all
collations,
as far as I know.
Steve Kass
Drew University
gracie wrote:

>I have a situation where the primary key is:
>user_id char(20) not null
>user_cd char(2) not null
>allows users to store trailing spaces, which in turn makes the key not uniq
ue.
>For example it allows the following, one with spaces one without:
>ernie 01
>ernie 01
>This in turn causes processing problems in the app.
>How do I fix this problem? BTW, ANSI_PADDING is turned off.
>Thanks,
>Gracie
>

pk & trailing spaces

I have a situation where the primary key is:
user_idchar(20) not null
user_cd char(2) not null
allows users to store trailing spaces, which in turn makes the key not unique.
For example it allows the following, one with spaces one without:
ernie01
ernie01
This in turn causes processing problems in the app.
How do I fix this problem? BTW, ANSI_PADDING is turned off.
Thanks,
Gracie
Use VARCHAR not CHAR.
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie
|||What if I can't?
"gracie" wrote:

> I have a situation where the primary key is:
> user_idchar(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not unique.
> For example it allows the following, one with spaces one without:
> ernie01
> ernie01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie
|||Char is a fixed length datatype, it will always pad spaces to length of data declaration with
spaces. Did you mean varchar?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1CDF624A-3879-4BD9-81F1-01A4043E17D7@.microsoft.com...[vbcol=seagreen]
> What if I can't?
> "gracie" wrote:
|||Is it by design that you allow users to put trailing spaces? If not make a
small change in the insert statement on the web page sql statement...
RTRIM(user_id)
Am suggesting this only if you can't change the datatype to varchar.
Thanks
GYK
"gracie" wrote:
[vbcol=seagreen]
> What if I can't?
> "gracie" wrote:
|||"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
>I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
> unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
>
I don't understand.
For char(20)
'ernie'
and
'ernie '
are stored the same, as
'ernie '
and are considered duplicates.
ANSI_PADDING has no effect on non-nullable char columns. ANSI_PADDING
controls whether trailing blanks are trimmed from varchar columns, and
whether nullable char columns are padded to width before being stored (this
is because nullable char columns are actually stored as as varchars).
But in general, I agree with what others have said: don't use for user_id,
use varchar. CHAR is ok for fixed-width coded like user_cd, but for data
which actually varies in length, varchar is better.
David
|||Gracie,
I can't reproduce the behavior you describe. Regardless of the data types
or collations on the columns, ('ernie','01') and ('ernie ','01') will
be considered
duplicate keys, since ('ernie' = 'ernie ') and ('01' = '01') is true.
Trailing spaces
are ignored in string comparisons for all string data types and for all
collations,
as far as I know.
Steve Kass
Drew University
gracie wrote:

>I have a situation where the primary key is:
>user_idchar(20) not null
>user_cd char(2) not null
>allows users to store trailing spaces, which in turn makes the key not unique.
>For example it allows the following, one with spaces one without:
>ernie01
>ernie01
>This in turn causes processing problems in the app.
>How do I fix this problem? BTW, ANSI_PADDING is turned off.
>Thanks,
>Gracie
>