interface supported: records

Showing posts with label records. Show all posts

Monday, March 26, 2012

Normal view compare to partitioned view

Hi,
I have one million records in one table, My data
contains all the records from for eg. January to December.
I use normal view to select out certain period of
records, for eg. January. then i query the records use
the remote view from foxpro to filter up the records from
this view. Does it make my data selection faster?
Or, do i need to separate out my data into few
tables and then use partitioned view to query the data
out?
Which one is faster?
Thanks.
regards,
florence
Hi
1'000'000 rows does not justify Partitioned Views. Once you get into the
1000's of millions it becomes justified (and then you nedd to put each table
on a seperate disk volume to make it perform). Rather make sure that you have
correct indexes and that you configure your hardware correctly.
Regards
Mike
"florencelee" wrote:

> Hi,
> I have one million records in one table, My data
> contains all the records from for eg. January to December.
> I use normal view to select out certain period of
> records, for eg. January. then i query the records use
> the remote view from foxpro to filter up the records from
> this view. Does it make my data selection faster?
> Or, do i need to separate out my data into few
> tables and then use partitioned view to query the data
> out?
> Which one is faster?
> Thanks.
> regards,
> florence
>

Normal view compare to partitioned view

Hi,
I have one million records in one table, My data
contains all the records from for eg. January to December.
I use normal view to select out certain period of
records, for eg. January. then i query the records use
the remote view from foxpro to filter up the records from
this view. Does it make my data selection faster?
Or, do i need to separate out my data into few
tables and then use partitioned view to query the data
out?
Which one is faster?
Thanks.
regards,
florenceHi
1'000'000 rows does not justify Partitioned Views. Once you get into the
1000's of millions it becomes justified (and then you nedd to put each table
on a seperate disk volume to make it perform). Rather make sure that you have
correct indexes and that you configure your hardware correctly.
Regards
Mike
"florencelee" wrote:
> Hi,
> I have one million records in one table, My data
> contains all the records from for eg. January to December.
> I use normal view to select out certain period of
> records, for eg. January. then i query the records use
> the remote view from foxpro to filter up the records from
> this view. Does it make my data selection faster?
> Or, do i need to separate out my data into few
> tables and then use partitioned view to query the data
> out?
> Which one is faster?
> Thanks.
> regards,
> florence
>

Noon to midnight formula help

I'm trying to write a custom formula to only show records that happened between noon and midnight over a specific date range. I have no problem with the date range but the time thing is killing me. The start time is in the ({Trips.calltime} table. The ending time is located in the {Trips.atstime} table. Thanks!What does 'happened' mean? Started on/after 12pm? Is the end time relevant?
Try the Time function, e.g.

and time({trips.calltime}) >= time(12,0,0)sql

Wednesday, March 21, 2012

non-matching recrods 2 tables (Newbie)

Hi,

I am new to SQL and these forums, and have a quick simple question.

I am trying to view the records that do not match in 2 tables. I have tried a few different ways but keep getting results in the hundreds of thousands and the table only has 36 thousand reocrds.

Here is an example of what the two tables contain.

Table 1
customer_no eaddress_no name address (36000 records)

Table2
customer_no eaddress_no email (17000 records)

I need to find out which customers are not in table 2 by linking the eaddress_no numbers.

This is one of the scripts i ran that gives me the large results with a ton of dups:

SELECT *
FROM T_EADDRESS inner JOIN
T_CUST_LOGIN ON T_EADDRESS.eaddress_no
!= T_CUST_LOGIN.eaddress_no

Thanks in advance for any help with this.Try this:

SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.eaddress_no <> Table2.eaddress_no|||I got the same results:

Alot of dups.

I did just find this at SQLTeam after i posted this message. I am just getting ready to try it now.

SELECT A.No,A.Date
FROM A LEFT OUTER JOIN B ON A.No = B.No AND A.Date = B.Date WHERE B.No IS NOT NULL|||If your query does not work, try this:

SELECT *
FROM Table1
WHERE eaddress_no NOT IN
(SELECT eaddress_no FROM Table2)|||gyuan that worked.
yours looked a little easier for me to follow so i just used that one.
Thanks a million.|||Using a left join is generally preferable to NOT EXISTS.

SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
WHERE Table1.eaddress_no is null

This can also be easily be modified to show records that are missing from either table:

SELECT Table1.*, Table2.*
FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
WHERE Table1.eaddress_no is null or Table2.eaddress_no is null|||Thanks Blind Man,

Those make sense to me now . I helps to see the table names in the query.|||Blindman,

you have an extra ) where would the opening one go?|||Just remove it...you don't need it...|||Sorry. No Parenthesis is necessary. It was a copy/paste error.|||I tried that and it returns no records.|||If you run this

SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table1.eaddress_no is null

and no records are returned, then there are no eaddress_no values in Table1 that are not also in Table2.

Run the second one with the FULL OUTER JOIN clause and see what you get:

SELECT Table1.*, Table2.*
FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table1.eaddress_no is null or Table2.eaddress_no is null|||OOPS!
WHERE clause was looking at wrong table! We want to see if Table2 is null...

SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table2.eaddress_no is null|||Yep that did it. And it took about 2 seconds few to run.

Thanks blindman, now i see.

Non-logged delete of records??

I have a database in Simple mode from which I wish to delete several million rows of one table (not all rows so 'TRUNCATE TABLE' is no good), without making the transaction log file grow to several GB in size. Any ideas? I'm almost at the point where I might create a new table and copy the records I want to keep instead...
Thanks, Simon.Assuming the rows have a primary key and you can select the PK from the Primary table , you could setup a secondary table (PKToDelete) with, say, 10,000 PK , populate that table with a select, and then join the PKToDelete and Primary tables for the delete operation. Upon successful completion of the delete, truncate the PKToDelete table, and repeat at perodic intervals until all Primary table deletes are complete.

Since you are in simple mode, the transaction log will be checkpointed when your pulsed delete completes, thereby preventing major growth of the transaction log.|||declare @.batchsize int
set @.batchsize=10000
set rowcount @.batchsize
delete from bigtab where something='some value'
while @.@.rowcount=@.batchsize
begin
delete from bigtab where something='some value'
end
set rowcount 0

Edit:
PS. Can do the same for updates e.g.
declare @.batchsize int
set @.batchsize=10000
set rowcount @.batchsize
update bigtab set type='shrub'
where id between 2867127 and 1908917872
and type='tree'
while @.@.rowcount=@.batchsize
begin
update bigtab set type='shrub'
where id between 2867127 and 1908917872
and type='tree'
end
set rowcount 0

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
DonUse select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with 90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file structure
>> address, char,30
>> thanks,
>> Don
>|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don
>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
>> Use select into statement
>> That is not logged.
>> You can select * or select fieldlist.
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file
structure
>> address, char,30
>> thanks,
>> Don
>>
>
>.
>|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don
>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file structure
>> address, char,30
>> thanks,
>> Don
>>
>.
>|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
>>--Original Message--
>>Just to be clear you can never have a non-logged
> operation in SQL Server.
>>You can get what is called a Minimally Logged operation
> by doing a Select
>>Into, BCP or Bulk Insert when the proper conditions are
> set. This still
>>logs entries to the transaction log but are minimal
> compared to the fully
>>logged operations. In the case of the Select Into you
> need to be in the
>>Bulk Logged or Simple recovery mode for this to occur.
>>
>>--
>>Andrew J. Kelly SQL MVP
>>
>><news.optonline.net> wrote in message
>>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
>> Use select into statement
>> That is not logged.
>> You can select * or select fieldlist.
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
> 90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file
> structure
>> address, char,30
>> thanks,
>> Don
>>
>>
>>.|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:
> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
> >--Original Message--
> >To do this I would create a view on the new table
> >create view vw_newtable
> >as
> >select * from newtable
> >
> >than bcp the data out
> >
> >master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
> N'
> >then bcp in
> >master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
> N'
> >
> >Note there must be no indexes on the dest table.
> >For 90 million recs I would consider doing the above but
> maybe 10 million
> >recs at a time. Use a query to extract the data with
> queryout on an indexed
> >column (or you can use views in the same way)
> >
> >
> >
> >
> >
> >
> >"Don" wrote:
> >
> >> SQL 7.0
> >>
> >> I need to use nonlogged bcp to copy from a table with
> 90
> >> millin records to an empty table structure that's
> >> different than the source structure.
> >> Does someone know the syntax?
> >>
> >> old table example:
> >> name, char, 30
> >> address,char,30
> >>
> >> new table example:
> >> name,char,30
> >> id,decimal,10 this is NULL on the source file structure
> >> address, char,30
> >>
> >> thanks,
> >> Don
> >>
> >>
> >.
> >
>sql

NonLogged Bulk copy syntax

Monday, March 19, 2012

Nonclustered index question

I have a table that has around 100,000 records in it, growing at a rate
of 200 records/day:
CREATE TABLE [Main] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Code] [char] (17) NOT NULL ,
[AgentID] [int] NULL ,
[DueDate] [smalldatetime] NOT NULL ,
[Status] [varchar] (18) NOT NULL
)
(This table also has about 30 more fields, but I have left them out for
brevity.)
I am looking to speed up a few specific search queries. I already have
a clustered index on my primary key field [ID], and a non-clustered
index on field [Code]. These indexes are fine performance wise.
I am looking to speed up 2 searches:
1) A combination of [AgentID] and [DueDate]: "... WHERE AgentID = n
AND DueDate BETWEEN (...)
2) [Status]: WHERE [Status]=xxx. Actually, this search is already
fast, and [Status] will really only have about 7 distinct values, so I
didn't index this field, as I figured a table scan would be faster in
this case. IS THIS A CORRECT ASSUMPTION. The result set for this
query is almost aways under 25 rows
My main question is: Should I put nonclustered indexes on [DueDate]
and [AgentID]. AgentID will have maybe 25 distinct values throughout
the table. The result set will usually be under 50 rows.
I just want to make sure I am not overindexing the table, and that if I
index these fields it will be beneficial.
Any help would be appreciated.for query 1, an index on ([AgentID], [DueDate]) could be an option, but
it looks like AgentID is not too sleective:

> AgentID will have maybe 25 distinct values throughout
so I'd also consider indexes on ([DueDate], [AgentID]) and just on
([DueDate])|||> 2) [Status]: WHERE [Status]=xxx. Actually, this search is already
> fast, and [Status] will really only have about 7 distinct values, so I
> didn't index this field, as I figured a table scan would be faster in
> this case. IS THIS A CORRECT ASSUMPTION. The result set for this
> query is almost aways under 25 rows
How exactly are you using this column? (i.e. how many rows per each value)
Searching for individual status values might be improved through the use of
indexed views - one per each status value. But may have a negative impact on
inserts/updates.
ML
http://milambda.blogspot.com/|||With 7 distinct values for Status, 100,000 rows in the table, and
WHERE [Status]=xxx returning under 25 rows, I have to assume that the
values for Status are severely skewed, and the status being tested is
one of the low frequency ones. Even with so few values, a query on a
specific low frequency value may in fact benefit from an index. I
would add an index on Status and see if it was used and if it helped.
With 100,000 rows, 25 distinct values for AgentID, and a result set of
around 50 rows, I have to assume that the DueDate BETWEEN test is
quite restrictive. I would try an index on (AgentID,DueDate).
Roy
On 23 Feb 2006 08:29:24 -0800, kaczmar2@.hotmail.com wrote:

>I have a table that has around 100,000 records in it, growing at a rate
>of 200 records/day:
>CREATE TABLE [Main] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Code] [char] (17) NOT NULL ,
> [AgentID] [int] NULL ,
> [DueDate] [smalldatetime] NOT NULL ,
> [Status] [varchar] (18) NOT NULL
> )
>(This table also has about 30 more fields, but I have left them out for
>brevity.)
>I am looking to speed up a few specific search queries. I already have
>a clustered index on my primary key field [ID], and a non-clustered
>index on field [Code]. These indexes are fine performance wise.
>I am looking to speed up 2 searches:
>1) A combination of [AgentID] and [DueDate]: "... WHERE AgentID = n
>AND DueDate BETWEEN (...)
>2) [Status]: WHERE [Status]=xxx. Actually, this search is already
>fast, and [Status] will really only have about 7 distinct values, so I
>didn't index this field, as I figured a table scan would be faster in
>this case. IS THIS A CORRECT ASSUMPTION. The result set for this
>query is almost aways under 25 rows
>My main question is: Should I put nonclustered indexes on [DueDate]
>and [AgentID]. AgentID will have maybe 25 distinct values throughout
>the table. The result set will usually be under 50 rows.
>I just want to make sure I am not overindexing the table, and that if I
>index these fields it will be beneficial.
>Any help would be appreciated.|||kaczmar2@.hotmail.com wrote:
> I have a table that has around 100,000 records in it, growing at a
> rate of 200 records/day:
> CREATE TABLE [Main] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Code] [char] (17) NOT NULL ,
> [AgentID] [int] NULL ,
> [DueDate] [smalldatetime] NOT NULL ,
> [Status] [varchar] (18) NOT NULL
> )
> (This table also has about 30 more fields, but I have left them out
> for brevity.)
> I am looking to speed up a few specific search queries. I already
> have a clustered index on my primary key field [ID], and a
> non-clustered index on field [Code]. These indexes are fine
> performance wise.
> I am looking to speed up 2 searches:
> 1) A combination of [AgentID] and [DueDate]: "... WHERE AgentID = n
> AND DueDate BETWEEN (...)
Depending on the selectiveness of your query criteria and distribuition of
values it might in fact be better to change the PK to non clustered and
have a clustered index on (DueDate, AgentID). If inserted DueDates are
increasing then inserts may also benefit from this index layout. On the
other hand, ID will be increasing, too. But for a range query a clustered
index is usually favourable.

> 2) [Status]: WHERE [Status]=xxx. Actually, this search is already
> fast, and [Status] will really only have about 7 distinct values, so I
> didn't index this field, as I figured a table scan would be faster in
> this case. IS THIS A CORRECT ASSUMPTION. The result set for this
> query is almost aways under 25 rows
Assuming equal distribution of values you'll get only 14% of the rows with
this criterion. I would assume that an index pays off here as this seems
pretty selective. Since you seem to be mostly searching for low freq
values it's highly likely that and index would help, especially as the
table grows. The more often you search for low freq values the more the
index pays off.

> My main question is: Should I put nonclustered indexes on [DueDate]
> and [AgentID]. AgentID will have maybe 25 distinct values throughout
> the table. The result set will usually be under 50 rows.
> I just want to make sure I am not overindexing the table, and that if
> I index these fields it will be beneficial.
You'll have to do some testing to get definitive answers. And you
probably should verify your findings from time to time as the table grows.
You might as well create a test table with the same layout and much more
data (but with typical distribution) if you want to see the behavior for
large tables beforehand.
Kind regards
robert

NonClustered Index Corruption

Hi,
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
JerryFirst off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table that
>> contains over 150 million records. After determining that the NC index
>> was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
>> and DBCC CHECKDB (doing both to make sure something else isn't causing
>> the issue) - both hung again (over 5 hours yesterday). Dropping the NC
>> index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that mean?
>> Were they being blocked? Was the spid doing anything? Is the NCI on a
>> single column, computed etc? In any case if you have hardware corruption
>> going on it will usually manifest itself in the most active process.
>> Creating an index on a 150M row table gives a lot of opportunity for
>> corruption if the hardware is suspect. If you have bad disk sectors then
>> creating the index again might very well use the same space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table that
>> contains over 150 million records. After determining that the NC index
>> was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat DBCC
>> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
>> the NC index is on a single column - no it is not a computed column.
>> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
>> never completes and has to be stopped manually. It could be a hardware
>> issue (RAID 5). What operations would you suggest to determine if this
>> could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that mean?
>> Were they being blocked? Was the spid doing anything? Is the NCI on a
>> single column, computed etc? In any case if you have hardware
>> corruption going on it will usually manifest itself in the most active
>> process. Creating an index on a 150M row table gives a lot of
>> opportunity for corruption if the hardware is suspect. If you have bad
>> disk sectors then creating the index again might very well use the same
>> space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table that
>> contains over 150 million records. After determining that the NC index
>> was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>>
>|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat DBCC
>> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original
>> post the NC index is on a single column - no it is not a computed
>> column. Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6
>> hours and never completes and has to be stopped manually. It could be a
>> hardware issue (RAID 5). What operations would you suggest to determine
>> if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have hardware
>> corruption going on it will usually manifest itself in the most active
>> process. Creating an index on a 150M row table gives a lot of
>> opportunity for corruption if the hardware is suspect. If you have bad
>> disk sectors then creating the index again might very well use the same
>> space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table
>> that contains over 150 million records. After determining that the NC
>> index was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>>
>>
>|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>> Jerry,
>> Sorry I didn't catch the single column. What level of DBCC did you run?
>> Try with the Physical_Only option first just to see if it goes through.
>> In any case unless it is blocked it should not just sit there doing
>> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
>> would have expected some errors if it were disk related. I would run
>> chkdisk to be sure anyway when you can. Next time you try it take a look
>> at the SPID in sysprocesses to see how much I/O CPU etc. it used before
>> it "hangs".
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat
>> DBCC CHECKDB includes DBCC CHECKTABLE. Again as I stated in the
>> original post the NC index is on a single column - no it is not a
>> computed column. Hung? A 20 minute operations (DBCC CHECKDB) takes
>> more than 6 hours and never completes and has to be stopped manually.
>> It could be a hardware issue (RAID 5). What operations would you
>> suggest to determine if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have
>> hardware corruption going on it will usually manifest itself in the
>> most active process. Creating an index on a 150M row table gives a lot
>> of opportunity for corruption if the hardware is suspect. If you have
>> bad disk sectors then creating the index again might very well use the
>> same space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table
>> that contains over 150 million records. After determining that the
>> NC index was the cause, I removed the NC index and reran the DBCC
>> CHECKDB statement. The results were no errors after 20 minutes.
>> Today I recreated the NC index (single column). I reran both the
>> DBCC CHECKTABLE and DBCC CHECKDB (doing both to make sure something
>> else isn't causing the issue) - both hung again (over 5 hours
>> yesterday). Dropping the NC index again and the checks work fine.
>> Any ideas?
>> Thanks
>> Jerry
>>
>>
>>
>>
>|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> Andrew,
>> No worries. I was running DBCC CHECKDB (with the default options). I
>> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
>> statment. This time the statement returned in a timely manner with no
>> errors reported. I'm thinking the drive(s). Does the SQL Server service
>> deed to be stopped before I have the network guy run the chkdsk command?
>> Thanks again.
>> Jerry
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>> Jerry,
>> Sorry I didn't catch the single column. What level of DBCC did you run?
>> Try with the Physical_Only option first just to see if it goes through.
>> In any case unless it is blocked it should not just sit there doing
>> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
>> would have expected some errors if it were disk related. I would run
>> chkdisk to be sure anyway when you can. Next time you try it take a
>> look at the SPID in sysprocesses to see how much I/O CPU etc. it used
>> before it "hangs".
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat
>> DBCC CHECKDB includes DBCC CHECKTABLE. Again as I stated in the
>> original post the NC index is on a single column - no it is not a
>> computed column. Hung? A 20 minute operations (DBCC CHECKDB) takes
>> more than 6 hours and never completes and has to be stopped manually.
>> It could be a hardware issue (RAID 5). What operations would you
>> suggest to determine if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have
>> hardware corruption going on it will usually manifest itself in the
>> most active process. Creating an index on a 150M row table gives a
>> lot of opportunity for corruption if the hardware is suspect. If you
>> have bad disk sectors then creating the index again might very well
>> use the same space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>>> Hi,
>>>
>>> Yesterday I had some corruption in a nonclustered index on a table
>>> that contains over 150 million records. After determining that the
>>> NC index was the cause, I removed the NC index and reran the DBCC
>>> CHECKDB statement. The results were no errors after 20 minutes.
>>> Today I recreated the NC index (single column). I reran both the
>>> DBCC CHECKTABLE and DBCC CHECKDB (doing both to make sure something
>>> else isn't causing the issue) - both hung again (over 5 hours
>>> yesterday). Dropping the NC index again and the checks work fine.
>>> Any ideas?
>>>
>>> Thanks
>>>
>>> Jerry
>>>
>>
>>
>>
>>
>>
>|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat DBCC
>> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original
>> post the NC index is on a single column - no it is not a computed
>> column. Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6
>> hours and never completes and has to be stopped manually. It could be a
>> hardware issue (RAID 5). What operations would you suggest to determine
>> if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have hardware
>> corruption going on it will usually manifest itself in the most active
>> process. Creating an index on a 150M row table gives a lot of
>> opportunity for corruption if the hardware is suspect. If you have bad
>> disk sectors then creating the index again might very well use the same
>> space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table
>> that contains over 150 million records. After determining that the NC
>> index was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>>
>>
>

Monday, March 12, 2012

Non unique Clustered index

I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
There is only a non clustered unique PK on the table, no clustered index.
I am wondering which would be better
1. Put a unique Clustered PK constraint on the 40 byte fields
member(int),vin(20),stock(18) (indexes would be large)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the
identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
as a unique non
clustered constraint.
This table has heavy updates(no Pkey fields updated ) and inserts at night
in batch (15,000 updates 5,000 inserts approx per night).
There is currently no clustered index and there is no way to control
fragmentation.
Thanks,
Jon A
If you have only one index on a table, it's usually best to be clustered.
The downside to a wide clustered index is that the clustered index keys are
stored in non-clustered indexes as well. This is not an issue when you
don't have non-clustered indexes, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E1406DAF-36A7-46AB-9EFD-27F942A59B51@.microsoft.com...
>I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A
|||Jon A wrote:
> I have 1,000,000 records there are unique by
> member(int),vin(20),stock(18). there are 1200 unique members all
> updates are done by the primary key (member,vin,stock). My customer
> does not want to add an identity column. There is only a non
> clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18) as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at
> night in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
I would argue that because you have a natural key as your PK, you're
likely to see page splitting if you use a clustered index. Since this is
a nightly batch, it may not matter. But then again, having a clustered
index on this table may not matter either, depnding on how the SELECTS
and UPDATES look.
I do agree with Dan. That is, it's best for most, if not all, tables to
have a clustered index. But to add one without a careful investigation
of the table and how it's used is necessary. Just as you would consider
what columns would best make use of a clustered index during database
design, you should perform the same due diligence now.
Look at your queries and table access. See how the data is updated. Is
it more than one row at a time? Is it ever changing a column value that
could be in the clustered index? What do the inserts look like? Are they
adding rows with column values that will most likely cause spage
splitting and slower insert performance at night? Look at the SELECTS on
the table. Do you ever return more than one row at a time? If so, what
criteria determine the rows returned? Do you have ORDER BY statements in
your queries? Do they really need to be there?
If you can post more information about how the table is used, we may be
able to offer more advice.
David Gugick
Imceda Software
www.imceda.com
|||Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A
|||An IDENTITY is a lousy candidate for a Clustered Index, almost as horrible
as allowing a table without a Clustered Index at all (a heap).
Heaps are large and, as you've noticed, do not allow you to as easily
control your index rebuild (defragmentaiton) as easily. First of all, the
Clustered Index itself adds no space to the table; its only the use of the
key as a pointer in the other indexes that can grow your non-clustered
indexes. However, consider how large the ROWID is as the alternative to the
clustered index key.
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique. Weigh
that against the composite index length, not to mention the size of the heap
alternative.
As to the IDENTITY, if you use one, NEVER make it a clustered index, unless
there are absolutely no other candidates. When will you EVER query an
IDENTITY by range? Also, if you use an IDENTITY, this is normally used as a
surrogate, as in your case, which does not remove the uniqueness requirement
of the business key you would be replacing as the Primary Key. So, better
add an UNIQUE non-Clustered Constraint to the original candidate(s).
Sincerely,
Anthony Thomas

"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422B03AC.344C5898@.toomuchspamalready.nl...
Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by
member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A
|||I added the clustered PK index as (member(int),vin(20),stock(18)). And with
adjustment the page splitting is minimal. But the Table is now 3x the size it
was previously.
My question is this in general terms. What is the problems / overhead of a
non unique clustered index? Is this a bad thing? I have never had a case
where I would do that. But as a result of this problem I am now curious.
"David Gugick" wrote:
|||I wouldn't expect changing the PK from non-clustered to clustered to
increase space requirements. In fact, I would think the space would
decrease. Are you certain there are no non-clustered indexes on the table?
You can double check with sp_helpindex.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E551392D-6A3D-4176-B1EB-7847AB685B51@.microsoft.com...
>I added the clustered PK index as (member(int),vin(20),stock(18)). And with
> adjustment the page splitting is minimal. But the Table is now 3x the size
> it
> was previously.
> My question is this in general terms. What is the problems / overhead of a
> non unique clustered index? Is this a bad thing? I have never had a case
> where I would do that. But as a result of this problem I am now curious.
> "David Gugick" wrote:
>
|||My sources: http://www.sql-server-performance.co...ed_indexes.asp
say that the uniqueifier used in a non-unique clustered index is a 4 byte value, as opposed to a (16 byte) GUID. Is there any other support either way? I can't find any in BOL.
jg

Quote:

...Originally posted by Anthony Thomas
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique...

Monday, March 26, 2012

Normal view compare to partitioned view

Normal view compare to partitioned view

Noon to midnight formula help

Wednesday, March 21, 2012

non-matching recrods 2 tables (Newbie)

Non-logged delete of records??

NonLogged Bulk copy syntax

NonLogged Bulk copy syntax

NonLogged Bulk copy syntax

Monday, March 19, 2012

Nonclustered index question

NonClustered Index Corruption

NonClustered Index Corruption

NonClustered Index Corruption

Monday, March 12, 2012

Non unique Clustered index

interface supported

Blog Archive

About Me