Adding Order by on clustered index

Hi all

I recently noticed when trying to optimise a major query of a chess website
I am the webmaster of, that adding an order by for "gamenumber" which is a
clustered index field as in for example "order by timeleft desc, gamenumber
desc" actually speeded up the queries and reduced sql server 2000 timeouts.
I have an ASP error log and I am fairly sure that a dramatic reduction in
sql server timeouts is simply attributed to adding an extra seemingly
redundant order by field - which is the clustered index. Is this phenomena
at all possible or is it my imagination?!

Other special attributes of the query includes the use of "Top" to obtain a
maximum specified number of rows. Perhaps it is just the unique
characteristics of the query, but I would have thought that the less order
by fields would imply faster performance. Has anyone else noticed that a
seemingly redundant order by column on for example the clustered index
column, can actually help speed up queries?!

Best wishes
Tryfon Gavriel
Webmaster
www.chessworld.net> I have an ASP error log and I am fairly sure that a dramatic reduction in
> sql server timeouts is simply attributed to adding an extra seemingly
> redundant order by field - which is the clustered index. Is this
> phenomena
> at all possible or is it my imagination?!

Apparently, SQL Server is choosing a more efficient execution plan to
satisfy your ORDER BY specification. This implies that the plan without the
ORDER BY is sub-optimal and may be an indication that statistics need to be
updated. There may be other factors but it's difficult to say without DDL
and sample data that demonstrates the problem.

ORDER BY is never redundant. Regardless of any indexes, SQL Server can
return rows in any sequence it deems appropriate unless ORDER BY is
explicitly specified. Similarly TOP n is meaningless without ORDER BY. If
ORDER BY is not specified, any rows can be returned.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tryfon Gavriel" <tryfon@.gtryfon.demon.co.uk> wrote in message
news:cniegl$k8p$1$8300dec7@.news.demon.co.uk...
> Hi all
> I recently noticed when trying to optimise a major query of a chess
> website
> I am the webmaster of, that adding an order by for "gamenumber" which is a
> clustered index field as in for example "order by timeleft desc,
> gamenumber
> desc" actually speeded up the queries and reduced sql server 2000
> timeouts.
> I have an ASP error log and I am fairly sure that a dramatic reduction in
> sql server timeouts is simply attributed to adding an extra seemingly
> redundant order by field - which is the clustered index. Is this
> phenomena
> at all possible or is it my imagination?!
> Other special attributes of the query includes the use of "Top" to obtain
> a
> maximum specified number of rows. Perhaps it is just the unique
> characteristics of the query, but I would have thought that the less order
> by fields would imply faster performance. Has anyone else noticed that a
> seemingly redundant order by column on for example the clustered index
> column, can actually help speed up queries?!
> Best wishes
> Tryfon Gavriel
> Webmaster
> www.chessworld.net|||> Other special attributes of the query includes the use of "Top" to obtain a
> maximum specified number of rows. Perhaps it is just the unique

If you just added a Top N, that should have a dramatic impact.

> characteristics of the query, but I would have thought that the less order
> by fields would imply faster performance. Has anyone else noticed that a
> seemingly redundant order by column on for example the clustered index
> column, can actually help speed up queries?!

If the query was sorting on anything other than the clustered index,
or if the records were coming out in no particular order, and not in
order by the clustering, that will help, too.

A "clustered" table is saved in order, row-by-row, so when you want
your data back in order, it doesn't have to sort the data. Just do a
table scan, and they'll come back already sorted. So if you sort on
something else, you're giving up that optimization, and then having to
sort on something else, too.|||> A "clustered" table is saved in order, row-by-row, so when you want
> your data back in order, it doesn't have to sort the data. Just do a
> table scan, and they'll come back already sorted.

Just to be clear, SQL server may choose an access method that causes rows to
be returned out-of-sequence even with a simple query against a table with a
clustered index. ORDER BY *must* be specified when you want to ensure data
are returned in a particular order.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Forrest" <ValhallaPhotos@.gmail.com> wrote in message
news:9d371039.0411182019.1014f0ed@.posting.google.c om...
>> Other special attributes of the query includes the use of "Top" to obtain
>> a
>> maximum specified number of rows. Perhaps it is just the unique
> If you just added a Top N, that should have a dramatic impact.
>> characteristics of the query, but I would have thought that the less
>> order
>> by fields would imply faster performance. Has anyone else noticed that a
>> seemingly redundant order by column on for example the clustered index
>> column, can actually help speed up queries?!
> If the query was sorting on anything other than the clustered index,
> or if the records were coming out in no particular order, and not in
> order by the clustering, that will help, too.
> A "clustered" table is saved in order, row-by-row, so when you want
> your data back in order, it doesn't have to sort the data. Just do a
> table scan, and they'll come back already sorted. So if you sort on
> something else, you're giving up that optimization, and then having to
> sort on something else, too.|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
>> A "clustered" table is saved in order, row-by-row, so when you want
>> your data back in order, it doesn't have to sort the data. Just do a
>> table scan, and they'll come back already sorted.
> Just to be clear, SQL server may choose an access method that causes
> rows to be returned out-of-sequence even with a simple query against a
> table with a clustered index. ORDER BY *must* be specified when you
> want to ensure data are returned in a particular order.

Just to elaborate on Dan's comment a little more. In SQL Server 6.5 you
could be fairly sure that the rows would be returned in order of the
clustered index if you said things like:

SELECT * FROM tbl

But in SQL7 and later, SQL Server may open parallel streams on the table,
and the result is likely to be semi-ordered (which could be deceivable).
This is particularly true for large queries.

(In all versions of SQL Serger, a WHERE clause a on column with a non-
clustered index or a Select list covered by a non-covered index is likely
to use that index as the starting point for the return order.)

Thus, to echo Dan: you *must* specify ORDER BY if you want a certain order.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Adding new index to existing table in Merge Replication

I see from other posts that sp_addscriptexec is recommended for
updating/creating indexes. It isn't clear to me if I can use Enterprise
Manager to go into the table on the publisher and add additional indexes on
a table and then have the added indexes automatically populate to the
subscribers.
Additionally, BOL says that an index can contain upto 16 fields but they
recommend upto three. Does that mean I should create many indexes with only
upto three fields in each index? Help or references on this matter would
be greatly appreciated.
WB
The index can be added at the publisher as per usual. It won't get
automatically propagated to the subscribers and that is what
sp_addscriptexec is for. If I just have one or two subscribers then I tend
to create the index by hand, but if you have lots, then it is a way or being
sure they all receive the changes, especially if they are pull
subscriptions.
As for the size, this seems a general question to do with indexes. Have a
look for clustered, nonclustered and covering in BOL. Indexes are sorthed on
the first column, then for duplicates of the first column they are sorted on
the second and so on, so 2 indexes with 3 columns is not the same as one
index with 6 columns. An index with 6 columns will be 'wide' and therefore
slow, but on the other hand it might have been created as a covering index
for a particluar long-running query. This is a really big subject and I'm
sure there are loads of resources on it apart from BOL.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding index to published table

Is this possible?
How would I do it?
Rob
Rob,
if it's just to exist on the publisher then you can just go ahead and add it
using EM or TSQL. This index won't be propagated to the subscriber, and even
in SQL 2005 with the option to replicate schema changes this is true. To be
sure to get the index to all subscribers, you can use sp_addscriptexec.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
So can I also manaully add /modify /drop the same indexes at the subscriber
using EM and T-SQL as well? Planning to do some major performance tuning in
my application soon.
TQ in advance.
"Paul Ibison" wrote:

> Rob,
> if it's just to exist on the publisher then you can just go ahead and add it
> using EM or TSQL. This index won't be propagated to the subscriber, and even
> in SQL 2005 with the option to replicate schema changes this is true. To be
> sure to get the index to all subscribers, you can use sp_addscriptexec.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Yes - index alterations are not seen as schema changes in that regard. The
same is true in SQL Server 2005 where there is the option of automatic
schema alterations being replicated - ie indexes aren't.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

adding index NETS "Deadlock"

Can you guys help me understand the logic here.
have a very small jobQueue table
JobId
StartTime
EndTime
JobTypeId
ParentJobId
a few other trivial columns
table gets smacked all day long (thousands and thousands of times)
one stored procedure (spGetNextJob) is doing table scans based on no index
placed on StartTime. Taking 1,000+ ms to execute which is clearly
unacceptable.
I add a nonclustered index on StartTime and get Deadlocks in Production.
My confusion is that Indexes dont cause deadlocks, accessing objects in
inconsistent order causes deadlocks....
HOW, can adding an index on the table create a deadlock scenario.
(Table is constantly truncated, so never really has more than a couple
hundred records)
Greg Jackson
PDX, OregonJaxon wrote:

> My confusion is that Indexes dont cause deadlocks, accessing objects
> in inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
Try enabling trace flag T1204. When this flag is enabled, the server will
print out detailed information about any deadlocks.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Add a RowID BigINT Identity(1,1) to the beginning of the table.
Clustered UNIQUE index on RowID.
Noncluster index on Startime
Write stored procedures to do inserts, updates, and deletes.
Make developers use those procedures ONLY for accessing the table.
Watch the system run very fast.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
> Can you guys help me understand the logic here.
> have a very small jobQueue table
> JobId
> StartTime
> EndTime
> JobTypeId
> ParentJobId
> a few other trivial columns
> table gets smacked all day long (thousands and thousands of times)
> one stored procedure (spGetNextJob) is doing table scans based on no index
> placed on StartTime. Taking 1,000+ ms to execute which is clearly
> unacceptable.
> I add a nonclustered index on StartTime and get Deadlocks in Production.
> My confusion is that Indexes dont cause deadlocks, accessing objects in
> inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
> (Table is constantly truncated, so never really has more than a couple
> hundred records)
>
> Greg Jackson
> PDX, Oregon
>|||From Geoff's post, If you will not have that many rows, you might get away
with using an INT data type rather than a bigint
Ray Higdon MCSE, MCDBA, CCNA
--
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23z$JyxuAEHA.2480@.TK2MSFTNGP11.phx.gbl...
> Add a RowID BigINT Identity(1,1) to the beginning of the table.
> Clustered UNIQUE index on RowID.
> Noncluster index on Startime
> Write stored procedures to do inserts, updates, and deletes.
> Make developers use those procedures ONLY for accessing the table.
> Watch the system run very fast.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
> news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
index
>|||SQL Profiler has Deadlock and Deadlock Chain events.
Couple this with looking at the statements being issued, you can get a very
good idea of what's happening, especially which processes are involved.
I think you will find that you are getting deadlocks on only one table. Ther
e are a couple of causes of this that I've had to deal with.
1. You have a trigger on the table. The trigger joins the INSERTED or DELETE
D tables onto itself. This trigger query can result in a Share Table lock. S
o you have two process, both aquire a row level eXclusive lock. They then de
adlock each other attemptin
g to get the Share Table lock. This is an escalation deadlock. This type of
problem foxed my company for 3 years until SQL Server 7.0 can in with the De
adlock and Deadlock Chain events to track it down. It then took 2 hours to f
ind and fix.
2. Two query are both using the same table, and locking a lot of rows. But o
nly one is using an index, or they are using different indexes. The rows are
locked in the order given by the indexes. The rows are then locked out of s
equence. In your case, it s
ounds as if the purge is still scanning the table, so locking all rows.
I hope this helps|||currently, the PKEY and Clustered Index are on JobId Which is a FREAKING
GUID (not my choice).
Currently there is no index on StartTime but when I ADD a nonclustered
there, I get deadlocks.
I've already analyzed the crap out of the deadlock situation.
deadlock is on the job table and is ocurring due to the two sprocs listed
above.
the sprocs SUCK (use cursors, etc etc etc) I'll fix them later.
I just dont understand how adding a nonclustered to the timeStart Column
results in Deadlocks and removing the index alleviates them.
Sure, I will fix the sprocs later (Like a 3 week task including QA, etc)
Could it just be that with the index added, it makes inserts that much
slower such that some transaction is being heald open a little longer
resulting in deadlock ?
In other words, is the index just the straw that is breaking my camels back
?
this entire job queue crap is an architectural nightmare.....the rewrite is
coming in the near future, believe me.
GAJ|||sorry, I didnt mention the sprocs...
the culprits are "spCheckJob" and "spGetNextJob"
they are both quite ugly you dont want me to send the definitions for these
believe me.
GAJ

adding index NETS "Deadlock"

Can you guys help me understand the logic here.
have a very small jobQueue table
JobId
StartTime
EndTime
JobTypeId
ParentJobId
a few other trivial columns
table gets smacked all day long (thousands and thousands of times)
one stored procedure (spGetNextJob) is doing table scans based on no index
placed on StartTime. Taking 1,000+ ms to execute which is clearly
unacceptable.
I add a nonclustered index on StartTime and get Deadlocks in Production.
My confusion is that Indexes dont cause deadlocks, accessing objects in
inconsistent order causes deadlocks....
HOW, can adding an index on the table create a deadlock scenario.
(Table is constantly truncated, so never really has more than a couple
hundred records)
Greg Jackson
PDX, OregonJaxon wrote:
> My confusion is that Indexes dont cause deadlocks, accessing objects
> in inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
Try enabling trace flag T1204. When this flag is enabled, the server will
print out detailed information about any deadlocks.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Add a RowID BigINT Identity(1,1) to the beginning of the table.
Clustered UNIQUE index on RowID.
Noncluster index on Startime
Write stored procedures to do inserts, updates, and deletes.
Make developers use those procedures ONLY for accessing the table.
Watch the system run very fast.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
> Can you guys help me understand the logic here.
> have a very small jobQueue table
> JobId
> StartTime
> EndTime
> JobTypeId
> ParentJobId
> a few other trivial columns
> table gets smacked all day long (thousands and thousands of times)
> one stored procedure (spGetNextJob) is doing table scans based on no index
> placed on StartTime. Taking 1,000+ ms to execute which is clearly
> unacceptable.
> I add a nonclustered index on StartTime and get Deadlocks in Production.
> My confusion is that Indexes dont cause deadlocks, accessing objects in
> inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
> (Table is constantly truncated, so never really has more than a couple
> hundred records)
>
> Greg Jackson
> PDX, Oregon
>|||From Geoff's post, If you will not have that many rows, you might get away
with using an INT data type rather than a bigint
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23z$JyxuAEHA.2480@.TK2MSFTNGP11.phx.gbl...
> Add a RowID BigINT Identity(1,1) to the beginning of the table.
> Clustered UNIQUE index on RowID.
> Noncluster index on Startime
> Write stored procedures to do inserts, updates, and deletes.
> Make developers use those procedures ONLY for accessing the table.
> Watch the system run very fast.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
> news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
> > Can you guys help me understand the logic here.
> >
> > have a very small jobQueue table
> >
> > JobId
> > StartTime
> > EndTime
> > JobTypeId
> > ParentJobId
> > a few other trivial columns
> >
> > table gets smacked all day long (thousands and thousands of times)
> >
> > one stored procedure (spGetNextJob) is doing table scans based on no
index
> > placed on StartTime. Taking 1,000+ ms to execute which is clearly
> > unacceptable.
> >
> > I add a nonclustered index on StartTime and get Deadlocks in Production.
> >
> > My confusion is that Indexes dont cause deadlocks, accessing objects in
> > inconsistent order causes deadlocks....
> >
> > HOW, can adding an index on the table create a deadlock scenario.
> >
> > (Table is constantly truncated, so never really has more than a couple
> > hundred records)
> >
> >
> > Greg Jackson
> > PDX, Oregon
> >
> >
>|||SQL Profiler has Deadlock and Deadlock Chain events.
Couple this with looking at the statements being issued, you can get a very good idea of what's happening, especially which processes are involved
I think you will find that you are getting deadlocks on only one table. There are a couple of causes of this that I've had to deal with
1. You have a trigger on the table. The trigger joins the INSERTED or DELETED tables onto itself. This trigger query can result in a Share Table lock. So you have two process, both aquire a row level eXclusive lock. They then deadlock each other attempting to get the Share Table lock. This is an escalation deadlock. This type of problem foxed my company for 3 years until SQL Server 7.0 can in with the Deadlock and Deadlock Chain events to track it down. It then took 2 hours to find and fix
2. Two query are both using the same table, and locking a lot of rows. But only one is using an index, or they are using different indexes. The rows are locked in the order given by the indexes. The rows are then locked out of sequence. In your case, it sounds as if the purge is still scanning the table, so locking all rows
I hope this helps|||currently, the PKEY and Clustered Index are on JobId Which is a FREAKING
GUID (not my choice).
Currently there is no index on StartTime but when I ADD a nonclustered
there, I get deadlocks.
I've already analyzed the crap out of the deadlock situation.
deadlock is on the job table and is ocurring due to the two sprocs listed
above.
the sprocs SUCK (use cursors, etc etc etc) I'll fix them later.
I just dont understand how adding a nonclustered to the timeStart Column
results in Deadlocks and removing the index alleviates them.
Sure, I will fix the sprocs later (Like a 3 week task including QA, etc)
Could it just be that with the index added, it makes inserts that much
slower such that some transaction is being heald open a little longer
resulting in deadlock ?
In other words, is the index just the straw that is breaking my camels back
?
this entire job queue crap is an architectural nightmare.....the rewrite is
coming in the near future, believe me.
GAJ|||sorry, I didnt mention the sprocs...
the culprits are "spCheckJob" and "spGetNextJob"
they are both quite ugly you dont want me to send the definitions for these
believe me.
GAJ

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?
|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.co m...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David
|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.co m...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
--
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David

Adding an index to an exiting publication

I have an existing publication and I want to modify an index on my main server. I do not want the index to be pushed out to the other server, but I'm wondering if the index will break replication.

Any thoughts?

Try the link below for more information on SQL Server replication. Hope this helps.
http://www.replicationanswers.com/Default.asp

Adding An Index To A View

I need to create an index for a view that I have recently created.
My problem is that none of the columns currently in the view have unique
values therefore I am assuming it is impossible to index. What I would
like to add a column to this view with all unique values (autonumbered)
and index the view based on this added column. Does anyone know if this
is possible to do. If not then how does anyone index a view?
I would appreciate any help on this a lot.
Thanks.
--
Posted via http://dbforums.comHi Zoltar,
Please don't most the same question independently to multiple newsgroups,
you can post it to multiple newsgroups at once.
Your question has been answered in .programming
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"zoltar" <member34126@.dbforums.com> wrote in message
news:3334410.1062764149@.dbforums.com...
> I need to create an index for a view that I have recently created.
> My problem is that none of the columns currently in the view have unique
> values therefore I am assuming it is impossible to index. What I would
> like to add a column to this view with all unique values (autonumbered)
> and index the view based on this added column. Does anyone know if this
> is possible to do. If not then how does anyone index a view?
> I would appreciate any help on this a lot.
> Thanks.
>
> --
> Posted via http://dbforums.com

Adding an auto-incrementing index column to a select statement

Good Morning
Is there a method of adding an extra column to a select statement which
would be an incrementing number?
ie)select aa.col_A,aa.col_B,(new_index_col)
from mytable aa
with a result set like this
col_a col_b new_index_col
hat dog 1
fred rat 2
mike pete 3
Thanks
Dave Hills
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!There was identity function but I do not remember exact syntax, something
like
Select identity(1,1) from mytable
Bojidar Alexandrov
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:uM$ecX0MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Good Morning
> Is there a method of adding an extra column to a select statement which
> would be an incrementing number?
> ie)select aa.col_A,aa.col_B,(new_index_col)
> from mytable aa
> with a result set like this
> col_a col_b new_index_col
> hat dog 1
> fred rat 2
> mike pete 3
>
> Thanks
>
> Dave Hills
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||You're obliged to use INTO in the select statement, so
this type of syntax will work for you:
use pubs
go
select *, IDENTITY(int,1,1) as xxx into #myTempTable
from pub_info
select * from #myTempTable
drop table #myTempTable
HTH,
Paul Ibison

Adding an auto-incrementing index column to a select statement

Good Morning
Is there a method of adding an extra column to a select statement which
would be an incrementing number?
ie)select aa.col_A,aa.col_B,(new_index_col)
from mytable aa
with a result set like this
col_a col_b new_index_col
hat dog 1
fred rat 2
mike pete 3
Thanks
Dave Hills
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
There was identity function but I do not remember exact syntax, something
like
Select identity(1,1) from mytable
Bojidar Alexandrov
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:uM$ecX0MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Good Morning
> Is there a method of adding an extra column to a select statement which
> would be an incrementing number?
> ie)select aa.col_A,aa.col_B,(new_index_col)
> from mytable aa
> with a result set like this
> col_a col_b new_index_col
> hat dog 1
> fred rat 2
> mike pete 3
>
> Thanks
>
> Dave Hills
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||You're obliged to use INTO in the select statement, so
this type of syntax will work for you:
use pubs
go
select *, IDENTITY(int,1,1) as xxx into #myTempTable
from pub_info
select * from #myTempTable
drop table #myTempTable
HTH,
Paul Ibison

Adding an auto-incrementing index column to a select statement

Good Morning
Is there a method of adding an extra column to a select statement which
would be an incrementing number?
ie)select aa.col_A,aa.col_B,(new_index_col)
from mytable aa
with a result set like this
col_a col_b new_index_col
hat dog 1
fred rat 2
mike pete 3
Thanks
Dave Hills
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!There was identity function but I do not remember exact syntax, something
like
Select identity(1,1) from mytable
Bojidar Alexandrov
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:uM$ecX0MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Good Morning
> Is there a method of adding an extra column to a select statement which
> would be an incrementing number?
> ie)select aa.col_A,aa.col_B,(new_index_col)
> from mytable aa
> with a result set like this
> col_a col_b new_index_col
> hat dog 1
> fred rat 2
> mike pete 3
>
> Thanks
>
> Dave Hills
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You're obliged to use INTO in the select statement, so
this type of syntax will work for you:
use pubs
go
select *, IDENTITY(int,1,1) as xxx into #myTempTable
from pub_info
select * from #myTempTable
drop table #myTempTable
HTH,
Paul Ibison