Deadlocks slowing down the server

Hello,
We are doing a year-end Inventory and I have only 3 users (data-entry clerks
)
trying to insert into the same table, more or less working at the same time
from their computers.
The insert is a very short statement with very basic 3 columns.
As soon as they started entering the data, deadlock errors started poping
once in a while.
They use a front-end Web Application that traps any errors that are
encountered duringn DML operation and displays the message to the user.
Given that SQL Server 2000 can handle thousands of transactions at peak time
s
without any problems, I am surprised and curious what wrong I could be doing
even for only 3 users to be using the system properly.
Since doing Inventory is a tidious task and a lot of entry is needed within
a
short time, I would appreciate a suggestion as due to the locks, the server
performance degrades drastically.
If I kill some locked transactions in EM, it improves.....
Any ideas ?
Message posted via http://www.droptable.comHi
A badly designed application can deadlock with even just 2 users. I have
looked after applications with 1000's of simultaneous users, but the DB was
architected correctly, and the code was also implemented correctly, we never
had deadlocks.
If the basic rules are not followed on how to build multi-user and scalable
applications, you are going to have trouble.
http://msdn2.microsoft.com/en-us/library/ms191242.aspx
http://msdn.microsoft.com/library/e...con_7a_3hdf.asp
http://support.microsoft.com/defaul...kb;en-us;169960
http://www.sql-server-performance.com/blocking.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:591ee915763c1@.uwe...
> Hello,
> We are doing a year-end Inventory and I have only 3 users (data-entry
> clerks)
> trying to insert into the same table, more or less working at the same
> time
> from their computers.
> The insert is a very short statement with very basic 3 columns.
> As soon as they started entering the data, deadlock errors started poping
> once in a while.
> They use a front-end Web Application that traps any errors that are
> encountered duringn DML operation and displays the message to the user.
> Given that SQL Server 2000 can handle thousands of transactions at peak
> times
> without any problems, I am surprised and curious what wrong I could be
> doing
> even for only 3 users to be using the system properly.
> Since doing Inventory is a tidious task and a lot of entry is needed
> within a
> short time, I would appreciate a suggestion as due to the locks, the
> server
> performance degrades drastically.
> If I kill some locked transactions in EM, it improves.....
> Any ideas ?
> --
> Message posted via http://www.droptable.com|||Thanks Mike,
Lots of reading to do......
I am using ColdFusion and wrapping the insert statement within a
<cftransaction> block to make it behave as one single batch.
Nothing fancy...very simple but I guess I'll have to read more on the
articles.
In pseudocode, I'm doing this:
<transaction>
.....<query>
...............INSERT INTO Table_Name (column_list) VALUES (value_list
)
.....<query>
<transaction>
I'll have to see what more I can do to change this logic...
Message posted via http://www.droptable.com|||Sameer wrote on Tue, 20 Dec 2005 15:39:39 GMT:

> Thanks Mike,
> Lots of reading to do......
> I am using ColdFusion and wrapping the insert statement within a
> <cftransaction> block to make it behave as one single batch.
> Nothing fancy...very simple but I guess I'll have to read more on the
> articles.
> In pseudocode, I'm doing this:
> <transaction>
> .....<query>
> ...............INSERT INTO Table_Name (column_list) VALUES
> (value_list)
> .....<query>
> <transaction>
> I'll have to see what more I can do to change this logic...
>
A single statement like that in it's own transaction shouldn't cause
deadlocks, should it? Are there any triggers on the table, if so that's
where I'd look. There is a DBCC TRACE option you can enable to write
deadlock information into the SQL Server logs, it might help you locate the
source of the problem.
Dan|||Hi Daniel,
Yes, there is a trigger on that table and fires after every INSERT or UPDATE
.
This is the root of the delay I think and I'm looking into it right now.
Thanks for pointing it out.
Message posted via http://www.droptable.com|||OK...I modified the trigger and now everything seems to be working fine eve
n
when they are all entering at the same time.
After reading the articles, should I still make the following changes ?
1. Use a lower Isolation Level
2. Set READ_COMMITTED_SNAPSHOT to ON
3. Set ALLOW_SNAPSHOT_ISOLATION to ON
Can someone shed some insight on how to do that and what consequences will I
face ?
Thanks.
Message posted via http://www.droptable.com|||are we talking SQL2000 or 2005 ?
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.droptable.com|||> 1. Use a lower Isolation Level
What isolation level are you using? Read Committed should be fine for most
applications. If you are using Serializable you should rethink it.

> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
These are 2005 features only. It looks like you are on 2000.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.droptable.com|||SQL 2000 SP4 with Win2K Advanced Server 2000
Message posted via http://www.droptable.com|||Since I don't know how to set an Isolation Level, my guess is that it is
using the default one.
How do you check the setting ?
How do you set it ?
Message posted via http://www.droptable.com

Deadlocks slowing down the server

Hello,
We are doing a year-end Inventory and I have only 3 users (data-entry clerks)
trying to insert into the same table, more or less working at the same time
from their computers.
The insert is a very short statement with very basic 3 columns.
As soon as they started entering the data, deadlock errors started poping
once in a while.
They use a front-end Web Application that traps any errors that are
encountered duringn DML operation and displays the message to the user.
Given that SQL Server 2000 can handle thousands of transactions at peak times
without any problems, I am surprised and curious what wrong I could be doing
even for only 3 users to be using the system properly.
Since doing Inventory is a tidious task and a lot of entry is needed within a
short time, I would appreciate a suggestion as due to the locks, the server
performance degrades drastically.
If I kill some locked transactions in EM, it improves.....
Any ideas ?
Message posted via http://www.droptable.com
Hi
A badly designed application can deadlock with even just 2 users. I have
looked after applications with 1000's of simultaneous users, but the DB was
architected correctly, and the code was also implemented correctly, we never
had deadlocks.
If the basic rules are not followed on how to build multi-user and scalable
applications, you are going to have trouble.
http://msdn2.microsoft.com/en-us/library/ms191242.aspx
http://msdn.microsoft.com/library/en...on_7a_3hdf.asp
http://support.microsoft.com/default...b;en-us;169960
http://www.sql-server-performance.com/blocking.asp
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:591ee915763c1@.uwe...
> Hello,
> We are doing a year-end Inventory and I have only 3 users (data-entry
> clerks)
> trying to insert into the same table, more or less working at the same
> time
> from their computers.
> The insert is a very short statement with very basic 3 columns.
> As soon as they started entering the data, deadlock errors started poping
> once in a while.
> They use a front-end Web Application that traps any errors that are
> encountered duringn DML operation and displays the message to the user.
> Given that SQL Server 2000 can handle thousands of transactions at peak
> times
> without any problems, I am surprised and curious what wrong I could be
> doing
> even for only 3 users to be using the system properly.
> Since doing Inventory is a tidious task and a lot of entry is needed
> within a
> short time, I would appreciate a suggestion as due to the locks, the
> server
> performance degrades drastically.
> If I kill some locked transactions in EM, it improves.....
> Any ideas ?
> --
> Message posted via http://www.droptable.com
|||Thanks Mike,
Lots of reading to do......
I am using ColdFusion and wrapping the insert statement within a
<cftransaction> block to make it behave as one single batch.
Nothing fancy...very simple but I guess I'll have to read more on the
articles.
In pseudocode, I'm doing this:
<transaction>
......<query>
................INSERT INTO Table_Name (column_list) VALUES (value_list)
......<query>
<transaction>
I'll have to see what more I can do to change this logic...
Message posted via http://www.droptable.com
|||Sameer wrote on Tue, 20 Dec 2005 15:39:39 GMT:

> Thanks Mike,
> Lots of reading to do......
> I am using ColdFusion and wrapping the insert statement within a
> <cftransaction> block to make it behave as one single batch.
> Nothing fancy...very simple but I guess I'll have to read more on the
> articles.
> In pseudocode, I'm doing this:
> <transaction>
> .....<query>
> ...............INSERT INTO Table_Name (column_list) VALUES
> (value_list)
> .....<query>
> <transaction>
> I'll have to see what more I can do to change this logic...
>
A single statement like that in it's own transaction shouldn't cause
deadlocks, should it? Are there any triggers on the table, if so that's
where I'd look. There is a DBCC TRACE option you can enable to write
deadlock information into the SQL Server logs, it might help you locate the
source of the problem.
Dan
|||Hi Daniel,
Yes, there is a trigger on that table and fires after every INSERT or UPDATE.
This is the root of the delay I think and I'm looking into it right now.
Thanks for pointing it out.
Message posted via http://www.droptable.com
|||OK...I modified the trigger and now everything seems to be working fine even
when they are all entering at the same time.
After reading the articles, should I still make the following changes ?
1. Use a lower Isolation Level
2. Set READ_COMMITTED_SNAPSHOT to ON
3. Set ALLOW_SNAPSHOT_ISOLATION to ON
Can someone shed some insight on how to do that and what consequences will I
face ?
Thanks.
Message posted via http://www.droptable.com
|||are we talking SQL2000 or 2005 ?
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.droptable.com
|||> 1. Use a lower Isolation Level
What isolation level are you using? Read Committed should be fine for most
applications. If you are using Serializable you should rethink it.

> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
These are 2005 features only. It looks like you are on 2000.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.droptable.com
|||SQL 2000 SP4 with Win2K Advanced Server 2000
Message posted via http://www.droptable.com
|||Since I don't know how to set an Isolation Level, my guess is that it is
using the default one.
How do you check the setting ?
How do you set it ?
Message posted via http://www.droptable.com

Deadlocks slowing down the server

Hello,
We are doing a year-end Inventory and I have only 3 users (data-entry clerks)
trying to insert into the same table, more or less working at the same time
from their computers.
The insert is a very short statement with very basic 3 columns.
As soon as they started entering the data, deadlock errors started poping
once in a while.
They use a front-end Web Application that traps any errors that are
encountered duringn DML operation and displays the message to the user.
Given that SQL Server 2000 can handle thousands of transactions at peak times
without any problems, I am surprised and curious what wrong I could be doing
even for only 3 users to be using the system properly.
Since doing Inventory is a tidious task and a lot of entry is needed within a
short time, I would appreciate a suggestion as due to the locks, the server
performance degrades drastically.
If I kill some locked transactions in EM, it improves.....
Any ideas ?
--
Message posted via http://www.sqlmonster.comHi
A badly designed application can deadlock with even just 2 users. I have
looked after applications with 1000's of simultaneous users, but the DB was
architected correctly, and the code was also implemented correctly, we never
had deadlocks.
If the basic rules are not followed on how to build multi-user and scalable
applications, you are going to have trouble.
http://msdn2.microsoft.com/en-us/library/ms191242.aspx
http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_3hdf.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;169960
http://www.sql-server-performance.com/blocking.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:591ee915763c1@.uwe...
> Hello,
> We are doing a year-end Inventory and I have only 3 users (data-entry
> clerks)
> trying to insert into the same table, more or less working at the same
> time
> from their computers.
> The insert is a very short statement with very basic 3 columns.
> As soon as they started entering the data, deadlock errors started poping
> once in a while.
> They use a front-end Web Application that traps any errors that are
> encountered duringn DML operation and displays the message to the user.
> Given that SQL Server 2000 can handle thousands of transactions at peak
> times
> without any problems, I am surprised and curious what wrong I could be
> doing
> even for only 3 users to be using the system properly.
> Since doing Inventory is a tidious task and a lot of entry is needed
> within a
> short time, I would appreciate a suggestion as due to the locks, the
> server
> performance degrades drastically.
> If I kill some locked transactions in EM, it improves.....
> Any ideas ?
> --
> Message posted via http://www.sqlmonster.com|||Thanks Mike,
Lots of reading to do......
I am using ColdFusion and wrapping the insert statement within a
<cftransaction> block to make it behave as one single batch.
Nothing fancy...very simple but I guess I'll have to read more on the
articles.
In pseudocode, I'm doing this:
<transaction>
.....<query>
...............INSERT INTO Table_Name (column_list) VALUES (value_list)
.....<query>
<transaction>
I'll have to see what more I can do to change this logic...
--
Message posted via http://www.sqlmonster.com|||Sameer wrote on Tue, 20 Dec 2005 15:39:39 GMT:
> Thanks Mike,
> Lots of reading to do......
> I am using ColdFusion and wrapping the insert statement within a
> <cftransaction> block to make it behave as one single batch.
> Nothing fancy...very simple but I guess I'll have to read more on the
> articles.
> In pseudocode, I'm doing this:
> <transaction>
> .....<query>
> ...............INSERT INTO Table_Name (column_list) VALUES
> (value_list)
> .....<query>
> <transaction>
> I'll have to see what more I can do to change this logic...
>
A single statement like that in it's own transaction shouldn't cause
deadlocks, should it? Are there any triggers on the table, if so that's
where I'd look. There is a DBCC TRACE option you can enable to write
deadlock information into the SQL Server logs, it might help you locate the
source of the problem.
Dan|||Hi Daniel,
Yes, there is a trigger on that table and fires after every INSERT or UPDATE.
This is the root of the delay I think and I'm looking into it right now.
Thanks for pointing it out.
--
Message posted via http://www.sqlmonster.com|||OK...I modified the trigger and now everything seems to be working fine even
when they are all entering at the same time.
After reading the articles, should I still make the following changes ?
1. Use a lower Isolation Level
2. Set READ_COMMITTED_SNAPSHOT to ON
3. Set ALLOW_SNAPSHOT_ISOLATION to ON
Can someone shed some insight on how to do that and what consequences will I
face ?
Thanks.
--
Message posted via http://www.sqlmonster.com|||are we talking SQL2000 or 2005 ?
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||> 1. Use a lower Isolation Level
What isolation level are you using? Read Committed should be fine for most
applications. If you are using Serializable you should rethink it.
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
These are 2005 features only. It looks like you are on 2000.
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:592053207623d@.uwe...
> OK...I modified the trigger and now everything seems to be working fine
> even
> when they are all entering at the same time.
> After reading the articles, should I still make the following changes ?
> 1. Use a lower Isolation Level
> 2. Set READ_COMMITTED_SNAPSHOT to ON
> 3. Set ALLOW_SNAPSHOT_ISOLATION to ON
> Can someone shed some insight on how to do that and what consequences will
> I
> face ?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||SQL 2000 SP4 with Win2K Advanced Server 2000
--
Message posted via http://www.sqlmonster.com|||Since I don't know how to set an Isolation Level, my guess is that it is
using the default one.
How do you check the setting ?
How do you set it ?
--
Message posted via http://www.sqlmonster.com|||It really depends on how you are connecting to SQL Server. The easiest way
to see what isolation levels a specific connection is using is to run a
profiler trace. The Existing connection event will show the current
Isolation level and if the app or the driver changes it batch completed will
show the statement. You usually set it with SET TRANSACTION ISOLATION LEVEL
xxxx. See BooksOnLine for more details.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:5920cb73e8dd5@.uwe...
> Since I don't know how to set an Isolation Level, my guess is that it is
> using the default one.
> How do you check the setting ?
> How do you set it ?
> --
> Message posted via http://www.sqlmonster.com|||Thank you so much all....I highly appreciate your advises :)
--
Message posted via http://www.sqlmonster.com

Deadlocks

We have a deployed website with many concurrent users who are mostly
reading from the database although there are frequent inserts/updates
as well. At scheduled intervals, we run multiple matching queries
against a table with around 120,000 rows. We used to run it WITH
(NOLOCK), but we decided that the default behavior of skipping over
noncommited transactions was acceptible. However, whereas before it
might get a deadlock once or twice a day, after taking out the WITH
(NOLOCK) we are getting up to 9 deadlocks every time it is run! Does
anyone know why these (read-only) queries are deadlocking so much? Oh,
if it helps, the scheduled queries are sequential so they are not
interfering with each other. Here is some (renamed) DDL if it helps:
CREATE PROCEDURE [mycompany].[my_sp] (
@.id int,
@.age int)
AS
SELECT
f.code_alpha,
f.code_beta,
f.low,
f.high
FROM foo AS f
JOIN bar AS b ON f.site = b.site
WHERE
DATEDIFF(hh, f.created, getdate()) <= @.age AND
DATEDIFF(hh, f.created, getdate()) > 0 AND
b.id = @.id AND
((b.category1 = 1 AND f.category = 1) OR
(b.category2 = 1 AND f.category = 2) OR
(b.category3 = 1 AND f.category = 3) OR
(b.category4 = 1 AND f.category = 4) OR
(b.category5 = 1 AND f.category = 5) OR
(b.category6 = 1 AND f.category = 6)) AND
(b.low <= f.high AND
b.high >= f.low) AND
((b.policy = 1 AND f.policy_alpha IN (1, 3)) OR
(b.policy = 2 AND f.policy_beta IN (1, 3)) OR
(b.policy = 3 AND f.policy_alpha IN (1, 3) AND f.policy_beta IN (1,
3)) OR
b.policy = 0) AND
f.code_alpha >= b.code_alpha AND
f.code_beta >= b.code_beta AND
f.confirmed = 1 AND
f.valid = 1
GO(steve.edison@.gmail.com) writes:
> We have a deployed website with many concurrent users who are mostly
> reading from the database although there are frequent inserts/updates
> as well. At scheduled intervals, we run multiple matching queries
> against a table with around 120,000 rows. We used to run it WITH
> (NOLOCK), but we decided that the default behavior of skipping over
> noncommited transactions was acceptible. However, whereas before it
> might get a deadlock once or twice a day, after taking out the WITH
> (NOLOCK) we are getting up to 9 deadlocks every time it is run! Does
> anyone know why these (read-only) queries are deadlocking so much? Oh,
> if it helps, the scheduled queries are sequential so they are not
> interfering with each other. Here is some (renamed) DDL if it helps:
It's about impossible to tell why queries we know little about deadlock.
I would guess, though, that they clash with some updating process.
Have you look at the deadlock trace? If you have not enabled this, you
should do that. From Enterprise Manager, specify -T 1204 and -T 3605 as
startup parameters, and restart the server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Deadlocks

I have in a table with some 2000 records with a Document ID which is a Unique Seq Number and details with a Status Flag. Multiple Users access the Table. When a user accesses a particular Document ID the Status flag changes from "N" to "W". Once he finishes working with data on that particular Document ID the Status Changes to "C". When another user tries to pickup a Record the next record in the Seq with the Status as "N" will get fetched. I use a Stored Procedure to assign a Document id to a user and update the Record/status details in the Table. I have used the No lock clause in the SP while retrieving a particular Document ID. It was working fine till sometime. Now it has started giving the following error

Error: Run-time error '-2147467259(80004005)'

[Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction(process ID#127)was deadlocked with another process and as been chosen as the deadlock victim. Return your transaction.

Some one please Help me on how to go abt the problem. Thanks in advance.
Regards
Dinesh1. Run sp_recompile 'object_name' for all objects used.
2. Post it all. SP+DDL

Good luck !

Deadlock on communication buffer, Thread

In our environment, a large number of users are
simultaneously running same select query against database.
The query involves around 7 tables and one view that has
millions of rows. In summary, the query is resource
intensive. Sometimes this query fails with the error
message
"Your transaction has been chosen as a victim of deadlock
on Communication Buffer,Thread" .
My question is why should there be a deadlock involved in
a SELECT statement. Deadlock should happen in
update/insert statements that use transactions.
Any insights on this will be helpful.
Thanks.there is an article at BOL that can helpl with this issue.. TROUBLESHOOTING
DEADLOCKS
One thing is the communication buffer in your error message, the bol sugests
that this can be related to query paralelism
Try to start the DBCC TRACEON(1204) to isolate the deadlock cause.
HTH
--
Wandenkolk T. Neto
MCDBA , MCSE
Fundação Abrinq
www.fundabrinq.org.br
"Vinod" <vinoddua2000@.yahoo.com> escreveu na mensagem
news:01c301c3674e$6b278d10$a501280a@.phx.gbl...
> In our environment, a large number of users are
> simultaneously running same select query against database.
> The query involves around 7 tables and one view that has
> millions of rows. In summary, the query is resource
> intensive. Sometimes this query fails with the error
> message
> "Your transaction has been chosen as a victim of deadlock
> on Communication Buffer,Thread" .
> My question is why should there be a deadlock involved in
> a SELECT statement. Deadlock should happen in
> update/insert statements that use transactions.
> Any insights on this will be helpful.
> Thanks.

DeadLock Issue

I serveral triggers in a table that is accessed by mutilple users in the application I am writing. I have come across a deadlock issue and have tried to resolve the issue by breaking down the triggers into many much smaller trans with no success. In general terms, can some one suggest some technique I am missing that I can try to avoid this issue .There are 3 techniques that can be used to help you avoid deadlocks. Number one is to ensure the same order of access to objects within a transaction. This comes from the definition of the deadlock itself:

TableA has a lock placed by user1 who's trying to access TableB,while user2 has a lock on TableB and trying to access tableA.

To break this vicious circle you need to structure all transactions in such a way that TableA is always accessed first.

Number two is to make your transactions as short as possible, without violation business logic and business requirements of course.

Number three - and here there will be a lot of screaming, yelling, calling names, - but reality will prove them all wrong,- lower transaction isolation level.|||rdjabarov and I disagree on that last point. I keep insisting on correct answers, he prefers easy answers.

In reality, if what you need is a quick answer the rand() function will often get you there orders of magnitude faster than you can possibly get the correct answer, with no locking issues at all.

-PatP|||Number three - and here there will be a lot of screaming, yelling, calling names, - but reality will prove them all wrong,- lower transaction isolation level.

When the users are screaming that: WE CAN'T COMPLETE OUR WORK, IT'S TOO SLOW AND WHATE THE HECK IS THIS timeout-expired IN OUR APPLICATION?
I don't see anything rising in the PERFMON at my dbserver but LOCKREQUEST/SEC (constantly above thousand figures) and high processor queue length due to blocking.
What now!
Same is the case when there's deadlock error, sp_lock contains more than hundereds of rows.
I'm just venting and cryin for the "Number Three advice"

One more thing that's rather strange and confusing:
why isn't there any READPAST isolation level. I guess it's by default in ORACLE.
[user1]
BEGIN TRAN
UPDATE mytable SET a=b
..
[user2]
select * from mytable
all previously committed rows r returned.

But in SQL, user2 waits :confused: , rather have to put select * from with (readpast) from mytable.
I guess [readpast] is better than [nolock]

Howdy! ;)|||OK, Pat, it's all circumstantial, let's agree at least on something (otherwise one of us will have to choose a different forum, and that's not what I intend to do, nor do I encourage for you to consider). When my app references Countries+States+Zips tables while displaying information to 100+ simalteneous users at the call center, don't you think it would be an overkill to use READ COMMITTED default isolation level for SQL? What are the chances that a call is placed to a person that lives in a country that has just been discovered, recorded in Countries table, associated States entries made, and their postal system delivered zip mapping to the call center, probably along with a list citizens that immediately received telephone service and became potential customers? It borders with absurdity, but that's what I feel when I see so much passion in your postings about NOLOCK.

EDITED: And how can RAND() help you?|||Setting connection level lock handling to allow permissive locking (lock handling like what NOLOCK gives) is one thing, to override the locking for a specific table within a transaction (actually using NOLOCK) is entirely different even though both situations affect the handling of locks.

Setting the transaction isolation level for the spid says that locking isn't important for this thread (spid) and is usually quite safe. You won't inadvertantly clobber important data due to a mangled or misprocessed read/write combination either within or between threads. This is a fine solution for the kind of problem you proposed where multiple users might need browse access to the same data.

Setting the lock handling for a specific table to a lower level than the thread that uses it requires a lot of knowledge about the entire system involved in order to have a prayer of being safe. I've had to do this before, but it isn't anything I'm comfortable with and wouldn't recommend for anyone that I didn't really, really hate. It takes ongoing monitoring to prevent a rogue process from upsetting the delicate balance that it depends upon.

I don't have any problem with setting the transaction isolation level, but am really, really wary of using NOLOCK explicitly. There are cases where it can be quite necessary... These are not the kind of solutions that I recommend for the long haul. They might fill a particular need under very specific circumstances, but they are dangerous.

-PatP|||And how about EXPLICITLY specifying READPAST in queries, ain't it better.?|||And how about EXPLICITLY specifying READPAST in queries, ain't it better.?I can't see any way that it is better from the standpoint of avoiding lost data... You can do as you wish, just be forewarned that I've done exactly what you are describing and really, REALLY regret it. Indiscriminantly ignoring locking is an easy solution, but like most easy solutions, it can be VERY dangerous.

If you set the transaction isolation level down, then everything in that transaction is processed at the lower level. I don't know of any way to hurt yourself that way. Setting some (even one) table to a lower locking level than the transaction isolation level is dangerous from the standpoint of "lost" data.

-PatP|||Pat, relax, there are more important things in life...like beer ;) Go get one, willya?!|||If you set the transaction isolation level down, then everything in that transaction is processed at the lower level. I don't know of any way to hurt yourself that way. Setting some (even one) table to a lower locking level than the transaction isolation level is dangerous from the standpoint of "lost" data.

No doubt the teacher is always right

and guru, i love you! :)|||Pat, relax, there are more important things in life...like beer ;) Go get one, willya?!No can do this week. I'm at training, so I'm always the designated driver. This week needs "Commando Driving 301" skill almost all the time in Chicago and the burbs. They've done some quite "creative" things on both 88 and 294, making getting from place to place a whole new experience!

Last night I actually made better time on Ogden (from 294 to Naperville Road!) than a friend did on I-88 !!!

-PatP

DDL Trigger to update Instead Of Insert trigger

Hello NG,
In order to prohibit users from updating a CreationDate I have a database
with an Instead Of Insert trigger on several tables. To make life easier I
created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
Instead Of Insert trigger for a given table and adds/removes new/deleted
columns from the Instert statement.
As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
executing a query looking like this:
ALTER TRIGGER [ioiApplicationTrigger]
ON [dbo].[Application]
INSTEAD OF INSERT
AS
INSERT INTO Application
(ApplicationID, Title, Type, test1, test2, CreationDate)
SELECT
ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
FROM inserted
This works as expected.
In order to make life even easier I tried to create a DDL trigger looking
like this:
CREATE TRIGGER [UpdateStandardTriggers]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @.trigger_name nvarchar(max);
DECLARE @.table_name nvarchar(max);
DECLARE @.data XML
-- Get table name from eventdata
SET @.data = EVENTDATA()
SET @.table_name =
@.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
EXEC UPDATE_IOI_TRIGGER @.table_name
END
The idea was thet this will automatically update my trigger whenever a
column in my table has been added, removed or changed.
However, this does not work. When I try to save a table after a change I get
the following error:
'Application' table
- Unable tp preserve trigger 'ioiApplicationTrigger'.
Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
because an INSTEAD OF INSERT trigger already exists.
In my procedure I check for the trigger using IF EXISTS and then I tried
both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a DROP
TRIGGER.
Any idea how I can achieve what I tried to explain before?
Peter
Peter,
Why not just use column level DENY, e.g.,
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly1>>
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly2>>
...
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Peter Gloor" <p_gloor@.hotmail.com> wrote in message
news:ecCVYCuQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello NG,
> In order to prohibit users from updating a CreationDate I have a database
> with an Instead Of Insert trigger on several tables. To make life easier I
> created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
> Instead Of Insert trigger for a given table and adds/removes new/deleted
> columns from the Instert statement.
> As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
> executing a query looking like this:
> ALTER TRIGGER [ioiApplicationTrigger]
> ON [dbo].[Application]
> INSTEAD OF INSERT
> AS
> INSERT INTO Application
> (ApplicationID, Title, Type, test1, test2, CreationDate)
> SELECT
> ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
> FROM inserted
> This works as expected.
> In order to make life even easier I tried to create a DDL trigger looking
> like this:
> CREATE TRIGGER [UpdateStandardTriggers]
> ON DATABASE
> FOR CREATE_TABLE, ALTER_TABLE
> AS
> BEGIN
> DECLARE @.trigger_name nvarchar(max);
> DECLARE @.table_name nvarchar(max);
> DECLARE @.data XML
> -- Get table name from eventdata
> SET @.data = EVENTDATA()
> SET @.table_name =
> @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
> EXEC UPDATE_IOI_TRIGGER @.table_name
> END
> The idea was thet this will automatically update my trigger whenever a
> column in my table has been added, removed or changed.
> However, this does not work. When I try to save a table after a change I
> get the following error:
> 'Application' table
> - Unable tp preserve trigger 'ioiApplicationTrigger'.
> Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
> because an INSTEAD OF INSERT trigger already exists.
> In my procedure I check for the trigger using IF EXISTS and then I tried
> both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a
> DROP TRIGGER.
> Any idea how I can achieve what I tried to explain before?
> Peter
>
>

DDL Trigger to update Instead Of Insert trigger

Hello NG,
In order to prohibit users from updating a CreationDate I have a database
with an Instead Of Insert trigger on several tables. To make life easier I
created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
Instead Of Insert trigger for a given table and adds/removes new/deleted
columns from the Instert statement.
As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
executing a query looking like this:
ALTER TRIGGER [ioiApplicationTrigger]
ON [dbo].[Application]
INSTEAD OF INSERT
AS
INSERT INTO Application
(ApplicationID, Title, Type, test1, test2, CreationDate)
SELECT
ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
FROM inserted
This works as expected.
In order to make life even easier I tried to create a DDL trigger looking
like this:
CREATE TRIGGER [UpdateStandardTriggers]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @.trigger_name nvarchar(max);
DECLARE @.table_name nvarchar(max);
DECLARE @.data XML
-- Get table name from eventdata
SET @.data = EVENTDATA()
SET @.table_name =
@.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
EXEC UPDATE_IOI_TRIGGER @.table_name
END
The idea was thet this will automatically update my trigger whenever a
column in my table has been added, removed or changed.
However, this does not work. When I try to save a table after a change I get
the following error:
'Application' table
- Unable tp preserve trigger 'ioiApplicationTrigger'.
Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
because an INSTEAD OF INSERT trigger already exists.
In my procedure I check for the trigger using IF EXISTS and then I tried
both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a DROP
TRIGGER.
Any idea how I can achieve what I tried to explain before?
PeterPeter,
Why not just use column level DENY, e.g.,
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly1>
>
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly2>
>
...
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Peter Gloor" <p_gloor@.hotmail.com> wrote in message
news:ecCVYCuQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello NG,
> In order to prohibit users from updating a CreationDate I have a database
> with an Instead Of Insert trigger on several tables. To make life easier I
> created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
> Instead Of Insert trigger for a given table and adds/removes new/deleted
> columns from the Instert statement.
> As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
> executing a query looking like this:
> ALTER TRIGGER [ioiApplicationTrigger]
> ON [dbo].[Application]
> INSTEAD OF INSERT
> AS
> INSERT INTO Application
> (ApplicationID, Title, Type, test1, test2, CreationDate)
> SELECT
> ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
> FROM inserted
> This works as expected.
> In order to make life even easier I tried to create a DDL trigger looking
> like this:
> CREATE TRIGGER [UpdateStandardTriggers]
> ON DATABASE
> FOR CREATE_TABLE, ALTER_TABLE
> AS
> BEGIN
> DECLARE @.trigger_name nvarchar(max);
> DECLARE @.table_name nvarchar(max);
> DECLARE @.data XML
> -- Get table name from eventdata
> SET @.data = EVENTDATA()
> SET @.table_name =
> @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
> EXEC UPDATE_IOI_TRIGGER @.table_name
> END
> The idea was thet this will automatically update my trigger whenever a
> column in my table has been added, removed or changed.
> However, this does not work. When I try to save a table after a change I
> get the following error:
> 'Application' table
> - Unable tp preserve trigger 'ioiApplicationTrigger'.
> Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
> because an INSTEAD OF INSERT trigger already exists.
> In my procedure I check for the trigger using IF EXISTS and then I tried
> both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a
> DROP TRIGGER.
> Any idea how I can achieve what I tried to explain before?
> Peter
>
>

DDL Trigger to update Instead Of Insert trigger

Hello NG,
In order to prohibit users from updating a CreationDate I have a database
with an Instead Of Insert trigger on several tables. To make life easier I
created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
Instead Of Insert trigger for a given table and adds/removes new/deleted
columns from the Instert statement.
As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
executing a query looking like this:
ALTER TRIGGER [ioiApplicationTrigger]
ON [dbo].[Application]
INSTEAD OF INSERT
AS
INSERT INTO Application
(ApplicationID, Title, Type, test1, test2, CreationDate)
SELECT
ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
FROM inserted
This works as expected.
In order to make life even easier I tried to create a DDL trigger looking
like this:
CREATE TRIGGER [UpdateStandardTriggers]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @.trigger_name nvarchar(max);
DECLARE @.table_name nvarchar(max);
DECLARE @.data XML
-- Get table name from eventdata
SET @.data = EVENTDATA()
SET @.table_name = @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
EXEC UPDATE_IOI_TRIGGER @.table_name
END
The idea was thet this will automatically update my trigger whenever a
column in my table has been added, removed or changed.
However, this does not work. When I try to save a table after a change I get
the following error:
'Application' table
- Unable tp preserve trigger 'ioiApplicationTrigger'.
Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
because an INSTEAD OF INSERT trigger already exists.
In my procedure I check for the trigger using IF EXISTS and then I tried
both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a DROP
TRIGGER.
Any idea how I can achieve what I tried to explain before?
PeterPeter,
Why not just use column level DENY, e.g.,
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly1>>
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly2>>
...
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Peter Gloor" <p_gloor@.hotmail.com> wrote in message
news:ecCVYCuQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello NG,
> In order to prohibit users from updating a CreationDate I have a database
> with an Instead Of Insert trigger on several tables. To make life easier I
> created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
> Instead Of Insert trigger for a given table and adds/removes new/deleted
> columns from the Instert statement.
> As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
> executing a query looking like this:
> ALTER TRIGGER [ioiApplicationTrigger]
> ON [dbo].[Application]
> INSTEAD OF INSERT
> AS
> INSERT INTO Application
> (ApplicationID, Title, Type, test1, test2, CreationDate)
> SELECT
> ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
> FROM inserted
> This works as expected.
> In order to make life even easier I tried to create a DDL trigger looking
> like this:
> CREATE TRIGGER [UpdateStandardTriggers]
> ON DATABASE
> FOR CREATE_TABLE, ALTER_TABLE
> AS
> BEGIN
> DECLARE @.trigger_name nvarchar(max);
> DECLARE @.table_name nvarchar(max);
> DECLARE @.data XML
> -- Get table name from eventdata
> SET @.data = EVENTDATA()
> SET @.table_name => @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
> EXEC UPDATE_IOI_TRIGGER @.table_name
> END
> The idea was thet this will automatically update my trigger whenever a
> column in my table has been added, removed or changed.
> However, this does not work. When I try to save a table after a change I
> get the following error:
> 'Application' table
> - Unable tp preserve trigger 'ioiApplicationTrigger'.
> Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
> because an INSTEAD OF INSERT trigger already exists.
> In my procedure I check for the trigger using IF EXISTS and then I tried
> both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a
> DROP TRIGGER.
> Any idea how I can achieve what I tried to explain before?
> Peter
>
>

dbTrace to find Index Non-Use - How To?

I read somewhere or heard you can trace for activity on table indexes to
ultimately find where indexes are NOT being used when users perform
searches...
Any idea how to set this up? I don't see events related to indexes...
TIA,
ChrisHi,
Easy method is "Use the Execution Plan" graphical option in Query
Analyzer -- Query option --"Show Execution plan"
Thanks
Hari
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris|||Chris,
You might try the Index Tuning Wizard.
HTH
Jerry
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris|||I need to monitor ALL indexes on all db tables, then find those that are NOT
being used... can't do that w/ Query Analyzer show plan, can't do that w/ th
e
tuning wizard. I need to collect this activity either thru a dbTrace or
PerfMon counters...
Any ideas'
"Chris" wrote:

> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris|||Chris,
What about ITWIZ?
See:
http://msdn.microsoft.com/library/d...r />
_8p2x.asp
HTH
Jerry
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:34A64A78-9F16-4851-A5C3-1DB66D7B390A@.microsoft.com...[vbcol=seagreen]
> I need to monitor ALL indexes on all db tables, then find those that are
> NOT
> being used... can't do that w/ Query Analyzer show plan, can't do that w/
> the
> tuning wizard. I need to collect this activity either thru a dbTrace or
> PerfMon counters...
> Any ideas'
> "Chris" wrote:
>|||Capture the execution plan over a relevant time period, parse it, compare ag
ainst the indexes you
have in your tables. Anything in the trace that isn't in sysindexes? There y
ou have it, those
indexes wasn't used by the SQL submitted over that trace. There will be bett
er ways in 2005 to do
this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:34A64A78-9F16-4851-A5C3-1DB66D7B390A@.microsoft.com...[vbcol=seagreen]
> I need to monitor ALL indexes on all db tables, then find those that are N
OT
> being used... can't do that w/ Query Analyzer show plan, can't do that w/
the
> tuning wizard. I need to collect this activity either thru a dbTrace or
> PerfMon counters...
> Any ideas'
> "Chris" wrote:
>|||Hi Chris
I do it by using trace to capture a workload over as long a period of time
as I can, and then run that through the Index Tuning Wizard. ITW generates a
set of reports, one of which is a list of which of your current indexes are
being used what percent of the time.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris
>

dbTrace to find Index Non-Use - How To?

I read somewhere or heard you can trace for activity on table indexes to
ultimately find where indexes are NOT being used when users perform
searches...
Any idea how to set this up? I don't see events related to indexes...
TIA,
Chris
Hi,
Easy method is "Use the Execution Plan" graphical option in Query
Analyzer -- Query option --"Show Execution plan"
Thanks
Hari
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris
|||Chris,
You might try the Index Tuning Wizard.
HTH
Jerry
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris
|||I need to monitor ALL indexes on all db tables, then find those that are NOT
being used... can't do that w/ Query Analyzer show plan, can't do that w/ the
tuning wizard. I need to collect this activity either thru a dbTrace or
PerfMon counters...
Any ideas?
"Chris" wrote:

> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris
|||Chris,
What about ITWIZ?
See:
http://msdn.microsoft.com/library/de...isqlw_8p2x.asp
HTH
Jerry
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:34A64A78-9F16-4851-A5C3-1DB66D7B390A@.microsoft.com...[vbcol=seagreen]
> I need to monitor ALL indexes on all db tables, then find those that are
> NOT
> being used... can't do that w/ Query Analyzer show plan, can't do that w/
> the
> tuning wizard. I need to collect this activity either thru a dbTrace or
> PerfMon counters...
> Any ideas?
> "Chris" wrote:
|||Capture the execution plan over a relevant time period, parse it, compare against the indexes you
have in your tables. Anything in the trace that isn't in sysindexes? There you have it, those
indexes wasn't used by the SQL submitted over that trace. There will be better ways in 2005 to do
this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:34A64A78-9F16-4851-A5C3-1DB66D7B390A@.microsoft.com...[vbcol=seagreen]
> I need to monitor ALL indexes on all db tables, then find those that are NOT
> being used... can't do that w/ Query Analyzer show plan, can't do that w/ the
> tuning wizard. I need to collect this activity either thru a dbTrace or
> PerfMon counters...
> Any ideas?
> "Chris" wrote:
|||Hi Chris
I do it by using trace to capture a workload over as long a period of time
as I can, and then run that through the Index Tuning Wizard. ITW generates a
set of reports, one of which is a list of which of your current indexes are
being used what percent of the time.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris
>

dbTrace to find Index Non-Use - How To?

I read somewhere or heard you can trace for activity on table indexes to
ultimately find where indexes are NOT being used when users perform
searches...
Any idea how to set this up? I don't see events related to indexes...
TIA,
ChrisHi,
Easy method is "Use the Execution Plan" graphical option in Query
Analyzer -- Query option --"Show Execution plan"
Thanks
Hari
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris|||Chris,
You might try the Index Tuning Wizard.
HTH
Jerry
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris|||I need to monitor ALL indexes on all db tables, then find those that are NOT
being used... can't do that w/ Query Analyzer show plan, can't do that w/ the
tuning wizard. I need to collect this activity either thru a dbTrace or
PerfMon counters...
Any ideas'
"Chris" wrote:
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris|||Chris,
What about ITWIZ?
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_isqlw_8p2x.asp
HTH
Jerry
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:34A64A78-9F16-4851-A5C3-1DB66D7B390A@.microsoft.com...
> I need to monitor ALL indexes on all db tables, then find those that are
> NOT
> being used... can't do that w/ Query Analyzer show plan, can't do that w/
> the
> tuning wizard. I need to collect this activity either thru a dbTrace or
> PerfMon counters...
> Any ideas'
> "Chris" wrote:
>> I read somewhere or heard you can trace for activity on table indexes to
>> ultimately find where indexes are NOT being used when users perform
>> searches...
>> Any idea how to set this up? I don't see events related to indexes...
>> TIA,
>> Chris|||Capture the execution plan over a relevant time period, parse it, compare against the indexes you
have in your tables. Anything in the trace that isn't in sysindexes? There you have it, those
indexes wasn't used by the SQL submitted over that trace. There will be better ways in 2005 to do
this...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:34A64A78-9F16-4851-A5C3-1DB66D7B390A@.microsoft.com...
> I need to monitor ALL indexes on all db tables, then find those that are NOT
> being used... can't do that w/ Query Analyzer show plan, can't do that w/ the
> tuning wizard. I need to collect this activity either thru a dbTrace or
> PerfMon counters...
> Any ideas'
> "Chris" wrote:
>> I read somewhere or heard you can trace for activity on table indexes to
>> ultimately find where indexes are NOT being used when users perform
>> searches...
>> Any idea how to set this up? I don't see events related to indexes...
>> TIA,
>> Chris|||Hi Chris
I do it by using trace to capture a workload over as long a period of time
as I can, and then run that through the Index Tuning Wizard. ITW generates a
set of reports, one of which is a list of which of your current indexes are
being used what percent of the time.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris
>

dbreindex

Hi,
I would like to know what methodology other users use in
order to trim down the transaction log after running 'dbcc
dbreindex'. I can think of couple ways like the following
but would like to know any better ways to automate the
whole proces:
- place a 'trunc. trans log' statement before & after the
command to avoid the excessive log
- run shrinkfile after the command is run
Any ideas/suggestions?Why do you want to shrink it in the first place? If it needed to get that
big today, don't you think it will need to be that big again the next time
you run reindex? Growing and shrinking of the files are expensive and
absolutely un-necessary in most cases. It might be good to backup the log
after a reindex but certainly don't shrink it.
--
Andrew J. Kelly
SQL Server MVP
"Pete." <darksage618@.hotmail.com> wrote in message
news:04c101c365d1$f13ddf40$a401280a@.phx.gbl...
> Hi,
> I would like to know what methodology other users use in
> order to trim down the transaction log after running 'dbcc
> dbreindex'. I can think of couple ways like the following
> but would like to know any better ways to automate the
> whole proces:
> - place a 'trunc. trans log' statement before & after the
> command to avoid the excessive log
> - run shrinkfile after the command is run
> Any ideas/suggestions?|||In addition to Andrew's post:
Consider using DBCC INDEXDEFRAG instead. It will most probably cut doesn the amount of changes done
(hence cut down on the log records produced), depending on how much reorg there is to be performed.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Pete." <darksage618@.hotmail.com> wrote in message news:04c101c365d1$f13ddf40$a401280a@.phx.gbl...
> Hi,
> I would like to know what methodology other users use in
> order to trim down the transaction log after running 'dbcc
> dbreindex'. I can think of couple ways like the following
> but would like to know any better ways to automate the
> whole proces:
> - place a 'trunc. trans log' statement before & after the
> command to avoid the excessive log
> - run shrinkfile after the command is run
> Any ideas/suggestions?

dbo's Login Name is blank and can't be edited from Enterprise Manager

We are using SQL Server 2000 SP4. I just created a new database from the EM
but found that in the Users folder under this new db name the Login Name for
dbo was blank. I double-clicked the dbo line and it showed <None> in the
properities dialog box which could not be edited. Is it okay to exe
sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
Thanks,
Eli
> Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
Yes, sp_changedbowner will fix the database owner. I think it's odd that a
new database would have a NULL owner, though. I usually see that only when
the Windows account that was the database owner is deleted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2000 SP4. I just created a new database from the
> EM
> but found that in the Users folder under this new db name the Login Name
> for
> dbo was blank. I double-clicked the dbo line and it showed <None> in the
> properities dialog box which could not be edited. Is it okay to exe
> sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
> Thanks,
> Eli
>
|||Thanks Dan. It works. Appreciate your meesage.
Regards,
Eli
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> Yes, sp_changedbowner will fix the database owner. I think it's odd that
a
> new database would have a NULL owner, though. I usually see that only
when[vbcol=seagreen]
> the Windows account that was the database owner is deleted.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eli" <efeng@.kerisys.com> wrote in message
> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
idea?
>
|||I'm glad I was able to help. Thanks for taking the time to confirm.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:uZ80fLvIIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Thanks Dan. It works. Appreciate your meesage.
> Regards,
> Eli
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> a
> when
> idea?
>

dbo's Login Name is blank and can't be edited from Enterprise Manager

We are using SQL Server 2000 SP4. I just created a new database from the EM
but found that in the Users folder under this new db name the Login Name for
dbo was blank. I double-clicked the dbo line and it showed <None> in the
properities dialog box which could not be edited. Is it okay to exe
sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
Thanks,
Eli> Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
Yes, sp_changedbowner will fix the database owner. I think it's odd that a
new database would have a NULL owner, though. I usually see that only when
the Windows account that was the database owner is deleted.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2000 SP4. I just created a new database from the
> EM
> but found that in the Users folder under this new db name the Login Name
> for
> dbo was blank. I double-clicked the dbo line and it showed <None> in the
> properities dialog box which could not be edited. Is it okay to exe
> sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
> Thanks,
> Eli
>|||Thanks Dan. It works. Appreciate your meesage.
Regards,
Eli
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> > Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
> Yes, sp_changedbowner will fix the database owner. I think it's odd that
a
> new database would have a NULL owner, though. I usually see that only
when
> the Windows account that was the database owner is deleted.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eli" <efeng@.kerisys.com> wrote in message
> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> > We are using SQL Server 2000 SP4. I just created a new database from the
> > EM
> > but found that in the Users folder under this new db name the Login Name
> > for
> > dbo was blank. I double-clicked the dbo line and it showed <None> in the
> > properities dialog box which could not be edited. Is it okay to exe
> > sp_changedbowner 'sa' sepcially for this new database? Or any better
idea?
> > Thanks,
> > Eli
> >
> >
>|||I'm glad I was able to help. Thanks for taking the time to confirm.
--
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:uZ80fLvIIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Thanks Dan. It works. Appreciate your meesage.
> Regards,
> Eli
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
>> > Is it okay to exe sp_changedbowner 'sa' sepcially for this new
>> > database?
>> Yes, sp_changedbowner will fix the database owner. I think it's odd that
> a
>> new database would have a NULL owner, though. I usually see that only
> when
>> the Windows account that was the database owner is deleted.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Eli" <efeng@.kerisys.com> wrote in message
>> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> > We are using SQL Server 2000 SP4. I just created a new database from
>> > the
>> > EM
>> > but found that in the Users folder under this new db name the Login
>> > Name
>> > for
>> > dbo was blank. I double-clicked the dbo line and it showed <None> in
>> > the
>> > properities dialog box which could not be edited. Is it okay to exe
>> > sp_changedbowner 'sa' sepcially for this new database? Or any better
> idea?
>> > Thanks,
>> > Eli
>> >
>> >
>

dbo's Login Name is blank and can't be edited from Enterprise Manager

We are using SQL Server 2000 SP4. I just created a new database from the EM
but found that in the Users folder under this new db name the Login Name for
dbo was blank. I double-clicked the dbo line and it showed <None> in the
properities dialog box which could not be edited. Is it okay to exe
sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
Thanks,
Eli> Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
Yes, sp_changedbowner will fix the database owner. I think it's odd that a
new database would have a NULL owner, though. I usually see that only when
the Windows account that was the database owner is deleted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2000 SP4. I just created a new database from the
> EM
> but found that in the Users folder under this new db name the Login Name
> for
> dbo was blank. I double-clicked the dbo line and it showed <None> in the
> properities dialog box which could not be edited. Is it okay to exe
> sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
> Thanks,
> Eli
>|||Thanks Dan. It works. Appreciate your meesage.
Regards,
Eli
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> Yes, sp_changedbowner will fix the database owner. I think it's odd that
a
> new database would have a NULL owner, though. I usually see that only
when
> the Windows account that was the database owner is deleted.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eli" <efeng@.kerisys.com> wrote in message
> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
idea?[vbcol=seagreen]
>|||I'm glad I was able to help. Thanks for taking the time to confirm.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:uZ80fLvIIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Thanks Dan. It works. Appreciate your meesage.
> Regards,
> Eli
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> a
> when
> idea?
>

dbo-owner

when I run the same scripts twice with different users (all having db_owner
rights), the SQL server duplicates the objects (for example, the stored
procedures).
How can I avoid it . If I define "dbo." for each object , it will be ok ?
thanks
That should do it !
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"ft" <ft@.discussions.microsoft.com> schrieb im Newsbeitrag
news:636B38A4-CFCB-44F5-9775-1717A2409A16@.microsoft.com...
> when I run the same scripts twice with different users (all having
> db_owner
> rights), the SQL server duplicates the objects (for example, the stored
> procedures).
> How can I avoid it . If I define "dbo." for each object , it will be ok ?
> thanks
>

dbo-owner

when I run the same scripts twice with different users (all having db_owner
rights), the SQL server duplicates the objects (for example, the stored
procedures).
How can I avoid it . If I define "dbo." for each object , it will be ok ?
thanksThat should do it !
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ft" <ft@.discussions.microsoft.com> schrieb im Newsbeitrag
news:636B38A4-CFCB-44F5-9775-1717A2409A16@.microsoft.com...
> when I run the same scripts twice with different users (all having
> db_owner
> rights), the SQL server duplicates the objects (for example, the stored
> procedures).
> How can I avoid it . If I define "dbo." for each object , it will be ok ?
> thanks
>

dbo-owner

when I run the same scripts twice with different users (all having db_owner
rights), the SQL server duplicates the objects (for example, the stored
procedures).
How can I avoid it . If I define "dbo." for each object , it will be ok ?
thanksThat should do it !
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ft" <ft@.discussions.microsoft.com> schrieb im Newsbeitrag
news:636B38A4-CFCB-44F5-9775-1717A2409A16@.microsoft.com...
> when I run the same scripts twice with different users (all having
> db_owner
> rights), the SQL server duplicates the objects (for example, the stored
> procedures).
> How can I avoid it . If I define "dbo." for each object , it will be ok ?
> thanks
>