BUG: Index Lost Extended Properties?

Hi
As title, i lost the extended properties when
i changed indexed collumns!
If i just change other index property such as
"unique", the extended properties is not lose.
I think it is a bug of SQL Server Management Studio.
Green Xiar
You can report bugs at http://connect.microsoft.com/sql. Make sure you include a repro and have
version number.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"greenxiar" <greenxiar@.hotmail.com> wrote in message news:OM7qlSBtHHA.2752@.TK2MSFTNGP06.phx.gbl...
> Hi
> As title, i lost the extended properties when
> i changed indexed collumns!
> If i just change other index property such as
> "unique", the extended properties is not lose.
> I think it is a bug of SQL Server Management Studio.
> Green Xiar
>

BUG: Index Lost Extended Properties?

Hi
As title, i lost the extended properties when
i changed indexed collumns!
If i just change other index property such as
"unique", the extended properties is not lose.
I think it is a bug of SQL Server Management Studio.
Green Xiar
You can report bugs at http://connect.microsoft.com/sql. Make sure you include a repro and have
version number.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"greenxiar" <greenxiar@.hotmail.com> wrote in message news:OM7qlSBtHHA.2752@.TK2MSFTNGP06.phx.gbl...
> Hi
> As title, i lost the extended properties when
> i changed indexed collumns!
> If i just change other index property such as
> "unique", the extended properties is not lose.
> I think it is a bug of SQL Server Management Studio.
> Green Xiar
>
sql

BUG: Index Lost Extended Properties?

Hi
As title, i lost the extended properties when
i changed indexed collumns!
If i just change other index property such as
"unique", the extended properties is not lose.
I think it is a bug of SQL Server Management Studio.
Green XiarYou can report bugs at http://connect.microsoft.com/sql. Make sure you include a repro and have
version number.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"greenxiar" <greenxiar@.hotmail.com> wrote in message news:OM7qlSBtHHA.2752@.TK2MSFTNGP06.phx.gbl...
> Hi
> As title, i lost the extended properties when
> i changed indexed collumns!
> If i just change other index property such as
> "unique", the extended properties is not lose.
> I think it is a bug of SQL Server Management Studio.
> Green Xiar
>

bug: "autostat" of an index reported by SQL Server Management Studio for 2005

Do not know if any one has complained this to Microsoft.
SQL server management studio does not seem to display the "autostat" of
an index correctly. The value in SQL server management studio is
always the reverse of the value from "sp_autostats".
to reproduce, high light a table in object explorer -> right click to
select Modify -> click "manage indexes and keys" -> select the index ,
the value of "Re-compute Statistics" is always the reverse of the value
of the corresponding index from "sp_autostats".
Thanks.
Kong
(likong@.email.com) writes:
> Do not know if any one has complained this to Microsoft.
> SQL server management studio does not seem to display the "autostat" of
> an index correctly. The value in SQL server management studio is
> always the reverse of the value from "sp_autostats".
> to reproduce, high light a table in object explorer -> right click to
> select Modify -> click "manage indexes and keys" -> select the index ,
> the value of "Re-compute Statistics" is always the reverse of the value
> of the corresponding index from "sp_autostats".
Actually, I can't recall having seen this particular issue reported.
When playing around, it appears that someone has gotten confused over
the DLL, where the option is STATISTICS_NORECOMPUTE=ON/OFF. If you believe
what the dialog says, and change No to Yes, you will alter the setting.
The place to report bugs like this is
http://lab.msdn.microsoft.com/ProductFeedback/, and I encourage you to
go there.
By the way, if you right-click the index, select Properties, and go to
the Options page, you will see that on this screen they have it right.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Bug

I have a client that I wrote a script for them to change the clustered index
from the PK field (which is an identity) to another field. The new
clustering field happens to contain some nulls (which is not supposed to
happen but..). Anyway, they ran the script and the new table with the new
clustered index was created without apparent errors. Then they ran a query
that looks like this:
SELECT *
FROM myTable
WHERE clusterField is not null and someOtherField is null
The return set returned records where the clusterField is null. I tried to
duplicate this on one of my servers and didn't have the problem. So I went
to their office and ran it and the problem persisted. After a couple of
hours of testing I found that the problem only existed above a certain
threshold of records in the table (about 1.1 million). After not being able
to find a solution I went home to do some more troubleshooting on some of my
servers and found that on one server it had the problem but on two others it
didn't have the problem. the one server that had the problem was using
service pack 3 while the two that worked were on SP3a. I thought I found
the problem and told my client to make sure that their servers were on SP3a.
They had on that was on SP3 and one on 3a. They tested the script again on
the server with 3a and still had the problem. I don't know where to go
next. I'm going to post the exact script that I used to exhibit the problem
in hopes that someone can find a solution or verify that this is a bug and
under what circumstances it occurs.
CREATE TABLE [HIST_NEW] (
[HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
[LOANOID] [bigint] NULL ,
[USERFILEOID] [bigint] NULL ,
[LOANPURGEOID] [bigint] NULL ,
[SystemDate] [varchar] (8) DEFAULT (''),
[SystemDateSQL] [datetime] default getdate(),
[SystemTime] [varchar] (6) DEFAULT (''),
[SystemTImeSQL] [datetime] default getdate(),
[TransactionID] [varchar] (5) DEFAULT (''),
[ActionCode] [varchar] (1) DEFAULT (''),
[BatchDate] [varchar] (8) DEFAULT (''),
[BatchDateSQL] [datetime] default getdate(),
[CompanyAgent] [varchar] (20) DEFAULT (''),
[DocumentID] [varchar] (20) DEFAULT (''),
[EffDate] [varchar] (8) DEFAULT (''),
[EffDateSQL] [datetime] default getdate(),
[Entry] [int] NULL ,
[ExpDate] [varchar] (8) DEFAULT (''),
[ExpDateSQL] [datetime] default getdate(),
[HistoryNumber] [int] NULL ,
[LNUM] [varchar] (18) DEFAULT (''),
[MailDate] [varchar] (8) DEFAULT (''),
[MailDateSQL] [datetime] default getdate(),
[ModCount] [smallint] NULL ,
[Policy] [varchar] (15) DEFAULT (''),
[UserID] [varchar] (10) DEFAULT (''),
[lenderNumber] [char] (4) DEFAULT (''),
CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
) ON [PRIMARY]
GO
--create a new clustered index on the LOANOID column
create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
declare @.counter
set @.counter = 1
while @.counter < 3500000
BEGIN
INSERT HIST_NEW (LOANOID, LNUM)
SELECT case when @.counter %6 = 0 then null else @.counter end,
case when @.counter %500 = 0 then null else @.counter end,
SET @.counter = @.counter + 1
END
--This query should not return nulls in the LOANOID field but is does in
some cases.
select lnum, LOANOID
from HIST_new
where LOANOID is not null
and lnum is null
I appreciate any insight that can be provided on this issue. Also, I ahve
been running this on SQL Server Standard Edition on Win 2K with SP4 in all
cases and on various types of hardware.Is this on a multi-processor computer? If so, perhaps this is the bug
documented in 814509:
http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
set the max degree of parallelism to 1.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> I have a client that I wrote a script for them to change the clustered
index
> from the PK field (which is an identity) to another field. The new
> clustering field happens to contain some nulls (which is not supposed to
> happen but..). Anyway, they ran the script and the new table with the new
> clustered index was created without apparent errors. Then they ran a
query
> that looks like this:
> SELECT *
> FROM myTable
> WHERE clusterField is not null and someOtherField is null
> The return set returned records where the clusterField is null. I tried
to
> duplicate this on one of my servers and didn't have the problem. So I
went
> to their office and ran it and the problem persisted. After a couple of
> hours of testing I found that the problem only existed above a certain
> threshold of records in the table (about 1.1 million). After not being
able
> to find a solution I went home to do some more troubleshooting on some of
my
> servers and found that on one server it had the problem but on two others
it
> didn't have the problem. the one server that had the problem was using
> service pack 3 while the two that worked were on SP3a. I thought I found
> the problem and told my client to make sure that their servers were on
SP3a.
> They had on that was on SP3 and one on 3a. They tested the script again
on
> the server with 3a and still had the problem. I don't know where to go
> next. I'm going to post the exact script that I used to exhibit the
problem
> in hopes that someone can find a solution or verify that this is a bug and
> under what circumstances it occurs.
>
> CREATE TABLE [HIST_NEW] (
> [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> [LOANOID] [bigint] NULL ,
> [USERFILEOID] [bigint] NULL ,
> [LOANPURGEOID] [bigint] NULL ,
> [SystemDate] [varchar] (8) DEFAULT (''),
> [SystemDateSQL] [datetime] default getdate(),
> [SystemTime] [varchar] (6) DEFAULT (''),
> [SystemTImeSQL] [datetime] default getdate(),
> [TransactionID] [varchar] (5) DEFAULT (''),
> [ActionCode] [varchar] (1) DEFAULT (''),
> [BatchDate] [varchar] (8) DEFAULT (''),
> [BatchDateSQL] [datetime] default getdate(),
> [CompanyAgent] [varchar] (20) DEFAULT (''),
> [DocumentID] [varchar] (20) DEFAULT (''),
> [EffDate] [varchar] (8) DEFAULT (''),
> [EffDateSQL] [datetime] default getdate(),
> [Entry] [int] NULL ,
> [ExpDate] [varchar] (8) DEFAULT (''),
> [ExpDateSQL] [datetime] default getdate(),
> [HistoryNumber] [int] NULL ,
> [LNUM] [varchar] (18) DEFAULT (''),
> [MailDate] [varchar] (8) DEFAULT (''),
> [MailDateSQL] [datetime] default getdate(),
> [ModCount] [smallint] NULL ,
> [Policy] [varchar] (15) DEFAULT (''),
> [UserID] [varchar] (10) DEFAULT (''),
> [lenderNumber] [char] (4) DEFAULT (''),
> CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> ) ON [PRIMARY]
> GO
> --create a new clustered index on the LOANOID column
> create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
>
> declare @.counter
> set @.counter = 1
> while @.counter < 3500000
> BEGIN
> INSERT HIST_NEW (LOANOID, LNUM)
> SELECT case when @.counter %6 = 0 then null else @.counter end,
> case when @.counter %500 = 0 then null else @.counter end,
> SET @.counter = @.counter + 1
> END
>
> --This query should not return nulls in the LOANOID field but is does in
> some cases.
> select lnum, LOANOID
> from HIST_new
> where LOANOID is not null
> and lnum is null
>
> I appreciate any insight that can be provided on this issue. Also, I ahve
> been running this on SQL Server Standard Edition on Win 2K with SP4 in all
> cases and on various types of hardware.
>|||Thanks Dan, that appears to be the problem. I've advised my client to turn
off parallelism until a service pack with that fix becomes available.
--Buddy
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23%237dZTBKEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Is this on a multi-processor computer? If so, perhaps this is the bug
> documented in 814509:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
> A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
> set the max degree of parallelism to 1.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
> news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> >
> > I have a client that I wrote a script for them to change the clustered
> index
> > from the PK field (which is an identity) to another field. The new
> > clustering field happens to contain some nulls (which is not supposed to
> > happen but..). Anyway, they ran the script and the new table with the
new
> > clustered index was created without apparent errors. Then they ran a
> query
> > that looks like this:
> >
> > SELECT *
> > FROM myTable
> > WHERE clusterField is not null and someOtherField is null
> >
> > The return set returned records where the clusterField is null. I tried
> to
> > duplicate this on one of my servers and didn't have the problem. So I
> went
> > to their office and ran it and the problem persisted. After a couple of
> > hours of testing I found that the problem only existed above a certain
> > threshold of records in the table (about 1.1 million). After not being
> able
> > to find a solution I went home to do some more troubleshooting on some
of
> my
> > servers and found that on one server it had the problem but on two
others
> it
> > didn't have the problem. the one server that had the problem was using
> > service pack 3 while the two that worked were on SP3a. I thought I
found
> > the problem and told my client to make sure that their servers were on
> SP3a.
> > They had on that was on SP3 and one on 3a. They tested the script again
> on
> > the server with 3a and still had the problem. I don't know where to go
> > next. I'm going to post the exact script that I used to exhibit the
> problem
> > in hopes that someone can find a solution or verify that this is a bug
and
> > under what circumstances it occurs.
> >
> >
> >
> > CREATE TABLE [HIST_NEW] (
> > [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> > [LOANOID] [bigint] NULL ,
> > [USERFILEOID] [bigint] NULL ,
> > [LOANPURGEOID] [bigint] NULL ,
> > [SystemDate] [varchar] (8) DEFAULT (''),
> > [SystemDateSQL] [datetime] default getdate(),
> > [SystemTime] [varchar] (6) DEFAULT (''),
> > [SystemTImeSQL] [datetime] default getdate(),
> > [TransactionID] [varchar] (5) DEFAULT (''),
> > [ActionCode] [varchar] (1) DEFAULT (''),
> > [BatchDate] [varchar] (8) DEFAULT (''),
> > [BatchDateSQL] [datetime] default getdate(),
> > [CompanyAgent] [varchar] (20) DEFAULT (''),
> > [DocumentID] [varchar] (20) DEFAULT (''),
> > [EffDate] [varchar] (8) DEFAULT (''),
> > [EffDateSQL] [datetime] default getdate(),
> > [Entry] [int] NULL ,
> > [ExpDate] [varchar] (8) DEFAULT (''),
> > [ExpDateSQL] [datetime] default getdate(),
> > [HistoryNumber] [int] NULL ,
> > [LNUM] [varchar] (18) DEFAULT (''),
> > [MailDate] [varchar] (8) DEFAULT (''),
> > [MailDateSQL] [datetime] default getdate(),
> > [ModCount] [smallint] NULL ,
> > [Policy] [varchar] (15) DEFAULT (''),
> > [UserID] [varchar] (10) DEFAULT (''),
> > [lenderNumber] [char] (4) DEFAULT (''),
> > CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> > ) ON [PRIMARY]
> > GO
> >
> > --create a new clustered index on the LOANOID column
> > create clustered index cix_hist on hist_new (LOANOID) with fillfactor =90
> >
> >
> > declare @.counter
> > set @.counter = 1
> >
> > while @.counter < 3500000
> > BEGIN
> > INSERT HIST_NEW (LOANOID, LNUM)
> > SELECT case when @.counter %6 = 0 then null else @.counter end,
> > case when @.counter %500 = 0 then null else @.counter end,
> >
> > SET @.counter = @.counter + 1
> > END
> >
> >
> > --This query should not return nulls in the LOANOID field but is does in
> > some cases.
> > select lnum, LOANOID
> > from HIST_new
> > where LOANOID is not null
> > and lnum is null
> >
> >
> >
> > I appreciate any insight that can be provided on this issue. Also, I
ahve
> > been running this on SQL Server Standard Edition on Win 2K with SP4 in
all
> > cases and on various types of hardware.
> >
> >
>

Boots Performance

Hi ,
I already create clustered and non clustered index in order to improve the
reports performance. But after I test several times on my reports , the
processing time almost the same as before I index the table.
Any other method to increase the performance ? Can I index the "view" I
created ?
Travis Tan
Travis,
The group will need substantially more information in order to help diagnose
your performance issue. The basic answers to your questions are Yes. and
most likely yes depending on the version you are running.
Please post more information including:
SQL Server version
Basic hardware configuration
The query
The estimated query plan
What is the database used for (OLTP, DSS, Reporting, etc.)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:5A01556F-70F4-4CC4-9779-2CAB8D4710F4@.microsoft.com...
> Hi ,
> I already create clustered and non clustered index in order to improve
> the
> reports performance. But after I test several times on my reports , the
> processing time almost the same as before I index the table.
> Any other method to increase the performance ? Can I index the "view" I
> created ?
>
> --
> Travis Tan

Boots Performance

Hi ,
I already create clustered and non clustered index in order to improve the
reports performance. But after I test several times on my reports , the
processing time almost the same as before I index the table.
Any other method to increase the performance ? Can I index the "view" I
created ?
Travis TanTravis,
The group will need substantially more information in order to help diagnose
your performance issue. The basic answers to your questions are Yes. and
most likely yes depending on the version you are running.
Please post more information including:
SQL Server version
Basic hardware configuration
The query
The estimated query plan
What is the database used for (OLTP, DSS, Reporting, etc.)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:5A01556F-70F4-4CC4-9779-2CAB8D4710F4@.microsoft.com...
> Hi ,
> I already create clustered and non clustered index in order to improve
> the
> reports performance. But after I test several times on my reports , the
> processing time almost the same as before I index the table.
> Any other method to increase the performance ? Can I index the "view" I
> created ?
>
> --
> Travis Tan