sql local data: application

Showing posts with label application. Show all posts

Friday, March 30, 2012

Locking

I have a Stored Procedure serving an web application that returns data from
a
number of tables.
This dat resides on three seperate tables, two of which I join in a single
select statement, the third I use to lookup data dependent on some data
retrieved from the original select statement.
On some infrequent occasions the user will receive an error message stating
they are the victim of a deadlock etc, etc.
I have checked the logs on SQL but find no error message pertaining to this.
I then created a script in QA that access this Stored procedure in a loop at
1000 and then 10000 times, but whenever I run it, I receive no error
messages, just the recordset(s) returned correctly.
The data being accessed is live and updateable 24/7 by other users. This
Stored Procedure does not moify the data in any shape or form, simply reads
it.
Another developer I work with has suggested using the NOLOCK hint on all
reads, but I am loathed to use this as it may cause dirty reads.
1. Does the scenario indicate this is truly a deadlock situation or could
there be another cause for this error message (the web app is in .NET)
2. What is the general point of view on using the NOLOCK hint and should it
be used as a matter of course or very sparingly?
I am obliged for any assistance or guidance in this matter
(Note: This question has also been posted on SQLTeam Site)> 1. Does the scenario indicate this is truly a deadlock situation or could
> there be another cause for this error message (the web app is in .NET)
Most probably. You didn't post the exact error message, but the way you phra
sed it sounds like the
error was indeed an SQL Server error message.

> 2. What is the general point of view on using the NOLOCK hint and should i
t
> be used as a matter of course or very sparingly?
Only use NOLOCK if you can foresee the consequences and accept them. But I s
uggest you look into
other possibilities first. These can include things like keeping locks for a
s short time as possible
(do not include things in the transaction that doesn't have to be there, tun
e queries etc),
accessing the objects in the same sequence, adding or removing indexes.
The reason you didn't see deadlocks in your loop is that you probably only h
ad one connection.
Deadlock (generally) occurs when you have two or more workers accessing data
in a way so that the
deadlock arises.
You can Google on deadlock (etc) and find plenty of text on the subject, inc
luding SQL server
specific text.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
news:D6FCCDA1-DE27-4A79-B801-CDC9B3038B92@.microsoft.com...
>I have a Stored Procedure serving an web application that returns data from
a
> number of tables.
> This dat resides on three seperate tables, two of which I join in a single
> select statement, the third I use to lookup data dependent on some data
> retrieved from the original select statement.
> On some infrequent occasions the user will receive an error message statin
g
> they are the victim of a deadlock etc, etc.
> I have checked the logs on SQL but find no error message pertaining to thi
s.
> I then created a script in QA that access this Stored procedure in a loop
at
> 1000 and then 10000 times, but whenever I run it, I receive no error
> messages, just the recordset(s) returned correctly.
> The data being accessed is live and updateable 24/7 by other users. This
> Stored Procedure does not moify the data in any shape or form, simply read
s
> it.
> Another developer I work with has suggested using the NOLOCK hint on all
> reads, but I am loathed to use this as it may cause dirty reads.
> 1. Does the scenario indicate this is truly a deadlock situation or could
> there be another cause for this error message (the web app is in .NET)
> 2. What is the general point of view on using the NOLOCK hint and should i
t
> be used as a matter of course or very sparingly?
> I am obliged for any assistance or guidance in this matter
> (Note: This question has also been posted on SQLTeam Site)|||The error message was thus...
Transaction (Process ID 99) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior
behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable
dataTable, IDbCommand command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at ...etc, etc
which strongly indicates that SQL generated it.
In respect of the loop test, I was attempting emulate the web application
when it requests the data to ascertain whether it was on the server or not.
I
recently read an artcle on MSDN that indicated deadlocking problems could be
caused by Applications opening dual threads that block each other - leading
to a deadlock situation - but it didn't state what error message would be
created in this case over and above the regular SQL 1204/1205 error.
Any further thoughts of where to look for this?
"Tibor Karaszi" wrote:

> Most probably. You didn't post the exact error message, but the way you ph
rased it sounds like the
> error was indeed an SQL Server error message.
>
> Only use NOLOCK if you can foresee the consequences and accept them. But I
suggest you look into
> other possibilities first. These can include things like keeping locks for
as short time as possible
> (do not include things in the transaction that doesn't have to be there, t
une queries etc),
> accessing the objects in the same sequence, adding or removing indexes.
> The reason you didn't see deadlocks in your loop is that you probably only
had one connection.
> Deadlock (generally) occurs when you have two or more workers accessing da
ta in a way so that the
> deadlock arises.
> You can Google on deadlock (etc) and find plenty of text on the subject, i
ncluding SQL server
> specific text.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
> news:D6FCCDA1-DE27-4A79-B801-CDC9B3038B92@.microsoft.com...
>sql

Locking

I have a series of stored procedures that are called from an outside
application. This application supports multi-threading, so I can have 10
instances of these stored procedures running at once. This is causing a grea
t
deal of locking. As I am working through the code, the biggest offenders are
"exists" and "not exists" statements. Each one reduces performance
dramatically. Is there another way to get the same effect and not use
"exists" and "not exists"? "In" and "Not In" are worse for locking.
Thanks for your help.Can we see some code and DDL?
AMB
"joesql" wrote:

> I have a series of stored procedures that are called from an outside
> application. This application supports multi-threading, so I can have 10
> instances of these stored procedures running at once. This is causing a gr
eat
> deal of locking. As I am working through the code, the biggest offenders a
re
> "exists" and "not exists" statements. Each one reduces performance
> dramatically. Is there another way to get the same effect and not use
> "exists" and "not exists"? "In" and "Not In" are worse for locking.
> Thanks for your help.|||Have you checked the execution plans for your EXISTS/NOT EXISTS queries? Are
they doing a table or index scan? Make sure you get an index s

for best
performance.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"joesql" <joesql@.discussions.microsoft.com> wrote in message
news:FB142BD5-0386-4734-8BFE-AB87F3A54637@.microsoft.com...
I have a series of stored procedures that are called from an outside
application. This application supports multi-threading, so I can have 10
instances of these stored procedures running at once. This is causing a
great
deal of locking. As I am working through the code, the biggest offenders are
"exists" and "not exists" statements. Each one reduces performance
dramatically. Is there another way to get the same effect and not use
"exists" and "not exists"? "In" and "Not In" are worse for locking.
Thanks for your help.|||It's hard to say without actually seeing what you are doing but it sounds
like there are no indexes for the EXISTS to work against. With a proper
index the EXISTS should be extremely efficient. Also make sure the stored
procedures are being called with the owner specified. exec dbo.yoursp
Andrew J. Kelly SQL MVP
"joesql" <joesql@.discussions.microsoft.com> wrote in message
news:FB142BD5-0386-4734-8BFE-AB87F3A54637@.microsoft.com...
>I have a series of stored procedures that are called from an outside
> application. This application supports multi-threading, so I can have 10
> instances of these stored procedures running at once. This is causing a
> great
> deal of locking. As I am working through the code, the biggest offenders
> are
> "exists" and "not exists" statements. Each one reduces performance
> dramatically. Is there another way to get the same effect and not use
> "exists" and "not exists"? "In" and "Not In" are worse for locking.
> Thanks for your help.|||All tables have proper indexes and I am using dbo. for all calls.
"Andrew J. Kelly" wrote:

> It's hard to say without actually seeing what you are doing but it sounds
> like there are no indexes for the EXISTS to work against. With a proper
> index the EXISTS should be extremely efficient. Also make sure the stored
> procedures are being called with the owner specified. exec dbo.yoursp
>
> --
> Andrew J. Kelly SQL MVP
>
> "joesql" <joesql@.discussions.microsoft.com> wrote in message
> news:FB142BD5-0386-4734-8BFE-AB87F3A54637@.microsoft.com...
>
>|||Here is a sample of code:
update #temp_events
set external_calc_ind = 'Y'
from pending_event pe,
pending_commission pc,
#temp_events e
where pe.pending_event_id = e.pending_event_id
and pc.pending_event_id = pe.pending_event_id
and not exists(select 1
from comm_event_process
where comm_event_id = pe.comm_event_id )
"Alejandro Mesa" wrote:
> Can we see some code and DDL?
>
> AMB
> "joesql" wrote:
>|||You;re sure there's an index on comm_event_id in the comm_event_process tab
le?
"joesql" wrote:
> Here is a sample of code:
> update #temp_events
> set external_calc_ind = 'Y'
> from pending_event pe,
> pending_commission pc,
> #temp_events e
> where pe.pending_event_id = e.pending_event_id
> and pc.pending_event_id = pe.pending_event_id
> and not exists(select 1
> from comm_event_process
> where comm_event_id = pe.comm_event_id )
> "Alejandro Mesa" wrote:
>|||Try,
update #temp_events
set external_calc_ind = 'Y'
where exists (
select
*
from
pending_event as pe
inner join
pending_commission as pc
on pc.pending_event_id = pe.pending_event_id
left join
comm_event_process as c
on c.comm_event_id = pe.comm_event_id
where
pe.pending_event_id = #temp_events.pending_event_id
and c.comm_event_id is null
);
be sure to have indexes in:
- pending_event(pending_event_id)
- pending_event(comm_event_id)
- pending_commission(pending_event_id)
- comm_event_process(comm_event_id)
- #temp_events(pending_event_id)
AMB
"joesql" wrote:
> Here is a sample of code:
> update #temp_events
> set external_calc_ind = 'Y'
> from pending_event pe,
> pending_commission pc,
> #temp_events e
> where pe.pending_event_id = e.pending_event_id
> and pc.pending_event_id = pe.pending_event_id
> and not exists(select 1
> from comm_event_process
> where comm_event_id = pe.comm_event_id )
> "Alejandro Mesa" wrote:
>|||That is the first thing I looked for. This is the primary key too, so it is
a
clustered index.
"CBretana" wrote:
> You;re sure there's an index on comm_event_id in the comm_event_process t
able?
> "joesql" wrote:
>|||This doesn't lock as bad, thanks for the input.
"Alejandro Mesa" wrote:
> Try,
> update #temp_events
> set external_calc_ind = 'Y'
> where exists (
> select
> *
> from
> pending_event as pe
> inner join
> pending_commission as pc
> on pc.pending_event_id = pe.pending_event_id
> left join
> comm_event_process as c
> on c.comm_event_id = pe.comm_event_id
> where
> pe.pending_event_id = #temp_events.pending_event_id
> and c.comm_event_id is null
> );
> be sure to have indexes in:
> - pending_event(pending_event_id)
> - pending_event(comm_event_id)
> - pending_commission(pending_event_id)
> - comm_event_process(comm_event_id)
> - #temp_events(pending_event_id)
>
> AMB
> "joesql" wrote:
>

Lock: Timeout

I enabled SQL Server Profiler Trace to findout performance issue for one of the application. And I am seeing Lock: Timeout eventclass with duration of 0 on tempdb database with objectID 0. Does this mean anything or need to be worried about?

Because I am seeing this many times in 20 mins trace.

Please check whether you or application is generating static cursor. Static cursors create table in tempdb

Monday, March 26, 2012

Lock problem when inserting and deleting records

Hi all.

I have an application that is using a SQL compact edition database to save/process information.

I run a test that is creating two threads:

1. one is inserting data in Table1

2. the other one is deleting records from Table1

When I run the application, I get some exceptions on both threads saying that the insert/delete could not aquire a lock on the table.

After a while, when I try to connect to the database I get an exception saying that the database file might be corrupted.

Any thoughts?

Thanks.

Hello,

Can you send me the database, and if possible, tell me (or give me) the app, so that, I can see what the issue is? Send it to goteti.udaya.bhanu@.gmail.com. And also, let me know which version of SQL CE are you using (3.1 or 3.0 etc...). Are you using ADO.NET or a native app to do this. Can you send me the code.

Thanks

Udaya.

|||

The db has a very simple structure:

2 tables with strings and datetimes as columns data types.

I am using SQLCE 3.1 version and ADO.NET. So, there is no native call from my code.

Thanks.

Mircea

|||

Hello,

I have tried to repro the issue, with SQLCE 3.1, but, could not. I need one more detail for that, I think. Which version of .NET are you using? Are you using .NET CF or .NET (on a device or on the desktop)?

Thanks

Udaya.

|||

If your both threads are doing DML operations on the same thread at the same time, there is a good chance that you experience locking issues. It could be that both operations need a lock on the same index page and one of them is denied lock for a long time (2000 msec or some thing like that). This looks okay to me. We are trying to improve the experience.

How to solve it? You might solve the above problem by either handling the error or serializing your actions. In the first approach you might want to just catch the error and retry the operation.

But why file corruption? This should not happen. Unless we look into your code, it's difficult to explain. Can you please try using different connection objects in these two threads?

Thanks

Raja [MSFT]

P.S: If this solves your problem, please mark it as answered.

Lock problem when inserting and deleting records

Hi all.

I have an application that is using a SQL compact edition database to save/process information.

I run a test that is creating two threads:

1. one is inserting data in Table1

2. the other one is deleting records from Table1

When I run the application, I get some exceptions on both threads saying that the insert/delete could not aquire a lock on the table.

After a while, when I try to connect to the database I get an exception saying that the database file might be corrupted.

Any thoughts?

Thanks.

Hello,

Thanks

Udaya.

|||

The db has a very simple structure:

2 tables with strings and datetimes as columns data types.

I am using SQLCE 3.1 version and ADO.NET. So, there is no native call from my code.

Thanks.

Mircea

|||

Hello,

Thanks

Udaya.

|||

How to solve it? You might solve the above problem by either handling the error or serializing your actions. In the first approach you might want to just catch the error and retry the operation.

But why file corruption? This should not happen. Unless we look into your code, it's difficult to explain. Can you please try using different connection objects in these two threads?

Thanks

Raja [MSFT]

P.S: If this solves your problem, please mark it as answered.

Lock Problem and application is slowed down

Dear Memebers,

I have a critical problem. I have an application is running on 64 bit
machine. It used to be running on 32 bit machine. That application is
using a Stored Procedure that uses SELECT, INSERT, UPDATE statements.
Whenever this applicataion is being run all the processes are locked
and INSERTING operation becomes cumbersome. We tested again on 32 bit
machine however it happened again. So what might be the problem? Can
somebody help me ? Application creates a lot of processes in a minute
Should SQL Server be caple of recieving these fast inserting processes?
As a DB Admin what should I do to find out whether this problem is
coming from SQL Server or not?

If you respond me ASAP I really appreciate it

Regards

LSlaststubborn (arafatsalih@.gmail.com) writes:

Quote:

Originally Posted by

This question is difficult to answer because of lack of hard information,
and I'm afraid that I will have to ask for clarification.

So there is a stored procedure running. Do I understand that there are
multiple instances of the procedure running? What processes are blocked?
Other processes that are running the same stored procedure? Which operations
are blocked?

Which version of SQL Server do you have?

In general terms, the way to address blocking issues to investigate if
there are any indexes missing. The longer time a query takes to run,
the bigger the risk for blocking. Of course, you also need to know
what is blocked and where in the procedure blocking occurs. I have a
stored procedure that can assist with that, check out
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
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|||Hi Erland ,

Here are the answers of your questions:
-Yes the same SP was kept locing the Database
-The other processes are not the same we have other SELECt or UPDATE or
INSERT processes on our Database
-Our database is MS SQL 20000
Thanks

Erland Sommarskog wrote:

Quote:

Originally Posted by

laststubborn (arafatsalih@.gmail.com) writes:

Quote:

Originally Posted by

>
This question is difficult to answer because of lack of hard information,
and I'm afraid that I will have to ask for clarification.
>
So there is a stored procedure running. Do I understand that there are
multiple instances of the procedure running? What processes are blocked?
Other processes that are running the same stored procedure? Which operations
are blocked?
>
Which version of SQL Server do you have?
>
In general terms, the way to address blocking issues to investigate if
there are any indexes missing. The longer time a query takes to run,
the bigger the risk for blocking. Of course, you also need to know
what is blocked and where in the procedure blocking occurs. I have a
stored procedure that can assist with that, check out
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
>
>
--
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

|||laststubborn (arafatsalih@.gmail.com) writes:

Quote:

Originally Posted by

Sorry for the late respond. Eventhough we solved the problem and the
problem was coming from the application, I would like to know my
necessary steps to take the action on SQL Server along with your
suggestions.
>
Here are the answers of your questions:
-Yes the same SP was kept locing the Database
-The other processes are not the same we have other SELECt or UPDATE or
INSERT processes on our Database
-Our database is MS SQL 20000

I'm afraid that I don't have much to add than teh suggestion to use
aba_lockinfo to get an overview of who is locking whom, and from this
try to understand why.

One situation that I should have mentioned is that if your application
has set up a command timeout (which is 30 seconds by default in many
APIs) and cancels the batch after this time, the application should
always submit a

IF @.@.trancount 0 ROLLBACK TRANSACTION

since a timeout expired does not rollback any transactions, and not rolling
back in this situations can lead to locks piling up.

Lock Monitoring

Hi,
We're trying to investigate a performance issue with our application
which uses SQL Server 2000 as the backend. The performance has become
an issue in a live customer environment and we are trying to determine
what is acquiring and holding locks for a "long" time.
We've been monitoring with SQL Profiler, looking for command completion
that takes more than 2 seconds. This has indicated some scripts, but,
some of the scripts that are reaching the 30 second execution limit are
very simple and execute in (very) sub second time under normal
circumstances.
So, something must be holding a lock that they are waiting for. Nothing
we're seeing in our trace is making this obvious.
What I'm looking to do is find a way to identify stored procedures that
in their execution time are holding locks for "long" periods of time.
Can anyone suggest a way to do this? Perfmon gives me average lock time
in ms and things like that, but not what's causing it and SQL Profiler
doesn't seem to be able to tell me what has the lock and how long it's
held the lock for (and what type of lock it is).
Any help gratefully accepted!
Cheers,
MichaelIf you are experiencing deadlocks - Read up on Trace flags 1204, 1205
Use sp_who and sp_lock as well.
"Michael Jervis" <mjervis@.gmail.com> wrote in message
news:1159452500.950233.257270@.k70g2000cwa.googlegroups.com...
> Hi,
> We're trying to investigate a performance issue with our application
> which uses SQL Server 2000 as the backend. The performance has become
> an issue in a live customer environment and we are trying to determine
> what is acquiring and holding locks for a "long" time.
> We've been monitoring with SQL Profiler, looking for command completion
> that takes more than 2 seconds. This has indicated some scripts, but,
> some of the scripts that are reaching the 30 second execution limit are
> very simple and execute in (very) sub second time under normal
> circumstances.
> So, something must be holding a lock that they are waiting for. Nothing
> we're seeing in our trace is making this obvious.
> What I'm looking to do is find a way to identify stored procedures that
> in their execution time are holding locks for "long" periods of time.
> Can anyone suggest a way to do this? Perfmon gives me average lock time
> in ms and things like that, but not what's causing it and SQL Profiler
> doesn't seem to be able to tell me what has the lock and how long it's
> held the lock for (and what type of lock it is).
> Any help gratefully accepted!
> Cheers,
> Michael
>|||http://support.microsoft.com/kb/271509/EN-US/ has a lot of good information
on how to monitor blocking and links to other KB articles on locks and
blocking.
Tom
"Michael Jervis" <mjervis@.gmail.com> wrote in message
news:1159452500.950233.257270@.k70g2000cwa.googlegroups.com...
> Hi,
> We're trying to investigate a performance issue with our application
> which uses SQL Server 2000 as the backend. The performance has become
> an issue in a live customer environment and we are trying to determine
> what is acquiring and holding locks for a "long" time.
> We've been monitoring with SQL Profiler, looking for command completion
> that takes more than 2 seconds. This has indicated some scripts, but,
> some of the scripts that are reaching the 30 second execution limit are
> very simple and execute in (very) sub second time under normal
> circumstances.
> So, something must be holding a lock that they are waiting for. Nothing
> we're seeing in our trace is making this obvious.
> What I'm looking to do is find a way to identify stored procedures that
> in their execution time are holding locks for "long" periods of time.
> Can anyone suggest a way to do this? Perfmon gives me average lock time
> in ms and things like that, but not what's causing it and SQL Profiler
> doesn't seem to be able to tell me what has the lock and how long it's
> held the lock for (and what type of lock it is).
> Any help gratefully accepted!
> Cheers,
> Michael
>

Lock Monitor

I have a sql server (Sql 2k Sp3) that has a very high CPU utilization count
for the Lock Monitor. The database is used by a Java application that
controls all update transactions. There are some instances where the app is
saving data, querying data and then saving more data inside a distributed
transaction. We are trying to remove those instances.
But I need tips and help on how to get lower the Lock Monitor CPU usage.
What is a average count?Hi
If you are not in control of the scope of the transaction, then you may be
limited to looking at the high duration of high volume statements and try an
d
tune the database to make them quick. You may also want to look at
distributing the I/O better such as adding discs to a raid system or
splitting filegroups into files over different spindles.
Use sp_locks to see lock information, check the isolation level and look for
query hints in SQL Profiler.
John
"JI" wrote:

> I have a sql server (Sql 2k Sp3) that has a very high CPU utilization coun
t
> for the Lock Monitor. The database is used by a Java application that
> controls all update transactions. There are some instances where the app i
s
> saving data, querying data and then saving more data inside a distributed
> transaction. We are trying to remove those instances.
> But I need tips and help on how to get lower the Lock Monitor CPU usage.
> What is a average count?
>
>

Lock Monitor

I have a sql server (Sql 2k Sp3) that has a very high CPU utilization count
for the Lock Monitor. The database is used by a Java application that
controls all update transactions. There are some instances where the app is
saving data, querying data and then saving more data inside a distributed
transaction. We are trying to remove those instances.
But I need tips and help on how to get lower the Lock Monitor CPU usage.
What is a average count?Hi
If you are not in control of the scope of the transaction, then you may be
limited to looking at the high duration of high volume statements and try and
tune the database to make them quick. You may also want to look at
distributing the I/O better such as adding discs to a raid system or
splitting filegroups into files over different spindles.
Use sp_locks to see lock information, check the isolation level and look for
query hints in SQL Profiler.
John
"JI" wrote:
> I have a sql server (Sql 2k Sp3) that has a very high CPU utilization count
> for the Lock Monitor. The database is used by a Java application that
> controls all update transactions. There are some instances where the app is
> saving data, querying data and then saving more data inside a distributed
> transaction. We are trying to remove those instances.
> But I need tips and help on how to get lower the Lock Monitor CPU usage.
> What is a average count?
>
>sql

Lock Monitor

I have a sql server (Sql 2k Sp3) that has a very high CPU utilization count
for the Lock Monitor. The database is used by a Java application that
controls all update transactions. There are some instances where the app is
saving data, querying data and then saving more data inside a distributed
transaction. We are trying to remove those instances.
But I need tips and help on how to get lower the Lock Monitor CPU usage.
What is a average count?
Hi
If you are not in control of the scope of the transaction, then you may be
limited to looking at the high duration of high volume statements and try and
tune the database to make them quick. You may also want to look at
distributing the I/O better such as adding discs to a raid system or
splitting filegroups into files over different spindles.
Use sp_locks to see lock information, check the isolation level and look for
query hints in SQL Profiler.
John
"JI" wrote:

> I have a sql server (Sql 2k Sp3) that has a very high CPU utilization count
> for the Lock Monitor. The database is used by a Java application that
> controls all update transactions. There are some instances where the app is
> saving data, querying data and then saving more data inside a distributed
> transaction. We are trying to remove those instances.
> But I need tips and help on how to get lower the Lock Monitor CPU usage.
> What is a average count?
>
>

Friday, March 23, 2012

Lock Database

I have problem with my database, sometimes I can not run query from query
analyzer or from my application.
When I run the query the process is very long (timeout) and might be
something has locked the database. And it's will run normally after I
restart the server. Any one know what happen with the database? and I have
to do to avoid locking database. Please help...Hi
I'd run SQL Server Profiler to capture events while you execute the query.
Also, review your query , do you have indexes on the table, how many rows
your query returns?
"Firmansyah" <syahmail@.softhome.net> wrote in message
news:em$FBxOsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> I have problem with my database, sometimes I can not run query from query
> analyzer or from my application.
> When I run the query the process is very long (timeout) and might be
> something has locked the database. And it's will run normally after I
> restart the server. Any one know what happen with the database? and I
have
> to do to avoid locking database. Please help...
>
>|||Yes I have index on the table, the query return approximate 10k rows
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uclwL4OsEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'd run SQL Server Profiler to capture events while you execute the query.
> Also, review your query , do you have indexes on the table, how many rows
> your query returns?
>
>
> "Firmansyah" <syahmail@.softhome.net> wrote in message
> news:em$FBxOsEHA.1216@.TK2MSFTNGP10.phx.gbl...
>> I have problem with my database, sometimes I can not run query from query
>> analyzer or from my application.
>> When I run the query the process is very long (timeout) and might be
>> something has locked the database. And it's will run normally after I
>> restart the server. Any one know what happen with the database? and I
> have
>> to do to avoid locking database. Please help...
>>
>|||Hi
Perhaps you run the query during a workload in your company where there are
many users using sql server.
Does sp_who2 show any block issues?
"Firmansyah" <syahmail@.softhome.net> wrote in message
news:%23mekrKPsEHA.2612@.TK2MSFTNGP10.phx.gbl...
> Yes I have index on the table, the query return approximate 10k rows
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uclwL4OsEHA.2956@.TK2MSFTNGP12.phx.gbl...
> > Hi
> > I'd run SQL Server Profiler to capture events while you execute the
query.
> > Also, review your query , do you have indexes on the table, how many
rows
> > your query returns?
> >
> >
> >
> >
> > "Firmansyah" <syahmail@.softhome.net> wrote in message
> > news:em$FBxOsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> >> I have problem with my database, sometimes I can not run query from
query
> >> analyzer or from my application.
> >> When I run the query the process is very long (timeout) and might be
> >> something has locked the database. And it's will run normally after I
> >> restart the server. Any one know what happen with the database? and I
> > have
> >> to do to avoid locking database. Please help...
> >>
> >>
> >>
> >
> >
>|||check locks from Enterpise Manager.
"Firmansyah" <syahmail@.softhome.net> wrote in message
news:em$FBxOsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> I have problem with my database, sometimes I can not run query from query
> analyzer or from my application.
> When I run the query the process is very long (timeout) and might be
> something has locked the database. And it's will run normally after I
> restart the server. Any one know what happen with the database? and I
have
> to do to avoid locking database. Please help...
>
>|||Yes I found block issues, when I saw on Lock/process ID Spid 53 (Block). can
you advice me why this happen and how to release the blocked process.
I can't figure out why the process was blocked.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23X5CzYPsEHA.2560@.tk2msftngp13.phx.gbl...
> Hi
> Perhaps you run the query during a workload in your company where there
> are
> many users using sql server.
> Does sp_who2 show any block issues?
>
>
> "Firmansyah" <syahmail@.softhome.net> wrote in message
> news:%23mekrKPsEHA.2612@.TK2MSFTNGP10.phx.gbl...
>> Yes I have index on the table, the query return approximate 10k rows
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uclwL4OsEHA.2956@.TK2MSFTNGP12.phx.gbl...
>> > Hi
>> > I'd run SQL Server Profiler to capture events while you execute the
> query.
>> > Also, review your query , do you have indexes on the table, how many
> rows
>> > your query returns?
>> >
>> >
>> >
>> >
>> > "Firmansyah" <syahmail@.softhome.net> wrote in message
>> > news:em$FBxOsEHA.1216@.TK2MSFTNGP10.phx.gbl...
>> >> I have problem with my database, sometimes I can not run query from
> query
>> >> analyzer or from my application.
>> >> When I run the query the process is very long (timeout) and might be
>> >> something has locked the database. And it's will run normally after I
>> >> restart the server. Any one know what happen with the database? and I
>> > have
>> >> to do to avoid locking database. Please help...
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||Hi
http://www.sql-server-performance.com/blocking.asp
"Firmansyah" <syahmail@.softhome.net> wrote in message
news:u9vsvnPsEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Yes I found block issues, when I saw on Lock/process ID Spid 53 (Block).
can
> you advice me why this happen and how to release the blocked process.
> I can't figure out why the process was blocked.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23X5CzYPsEHA.2560@.tk2msftngp13.phx.gbl...
> > Hi
> > Perhaps you run the query during a workload in your company where there
> > are
> > many users using sql server.
> > Does sp_who2 show any block issues?
> >
> >
> >
> >
> > "Firmansyah" <syahmail@.softhome.net> wrote in message
> > news:%23mekrKPsEHA.2612@.TK2MSFTNGP10.phx.gbl...
> >> Yes I have index on the table, the query return approximate 10k rows
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> >> news:uclwL4OsEHA.2956@.TK2MSFTNGP12.phx.gbl...
> >> > Hi
> >> > I'd run SQL Server Profiler to capture events while you execute the
> > query.
> >> > Also, review your query , do you have indexes on the table, how many
> > rows
> >> > your query returns?
> >> >
> >> >
> >> >
> >> >
> >> > "Firmansyah" <syahmail@.softhome.net> wrote in message
> >> > news:em$FBxOsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> >> >> I have problem with my database, sometimes I can not run query from
> > query
> >> >> analyzer or from my application.
> >> >> When I run the query the process is very long (timeout) and might be
> >> >> something has locked the database. And it's will run normally after
I
> >> >> restart the server. Any one know what happen with the database? and
I
> >> > have
> >> >> to do to avoid locking database. Please help...
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Hi!
I guess one of your transaction still opened, and locking some resource.
First, you should try with sp_who2 to see which spid is blocking your query.
After that if you have the spid, try to run dbcc inputbuffer(#)
(write the spid number instead of the #)
This will probably show what this spid run last.
Another good thing is, that you can run sp_lock #
(write the spid number instead of the #)
This will show the resources blocked by the spid
(if you have the object_id , use select object_name(#object_id) to obtain
the name of the resource)
I hope it will help
Gza
"Firmansyah" wrote:
> I have problem with my database, sometimes I can not run query from query
> analyzer or from my application.
> When I run the query the process is very long (timeout) and might be
> something has locked the database. And it's will run normally after I
> restart the server. Any one know what happen with the database? and I have
> to do to avoid locking database. Please help...
>
>|||Thanks you very much your info useful for me.
Firman
"Gza" <Gza@.discussions.microsoft.com> wrote in message
news:5C993D6E-277A-4399-99BC-D20066F15C40@.microsoft.com...
> Hi!
> I guess one of your transaction still opened, and locking some resource.
> First, you should try with sp_who2 to see which spid is blocking your
> query.
> After that if you have the spid, try to run dbcc inputbuffer(#)
> (write the spid number instead of the #)
> This will probably show what this spid run last.
> Another good thing is, that you can run sp_lock #
> (write the spid number instead of the #)
> This will show the resources blocked by the spid
> (if you have the object_id , use select object_name(#object_id) to obtain
> the name of the resource)
> I hope it will help
> Gza
>
> "Firmansyah" wrote:
>> I have problem with my database, sometimes I can not run query from query
>> analyzer or from my application.
>> When I run the query the process is very long (timeout) and might be
>> something has locked the database. And it's will run normally after I
>> restart the server. Any one know what happen with the database? and I
>> have
>> to do to avoid locking database. Please help...
>>
>>

Lock Database

Wednesday, March 21, 2012

Location of query results (sql 2000)

I have a multi user application running with a query to fill GUI components
(Delphi)
I get a 'share violation' for 'del1.mb' (according to a user). All users run
with the same userid/password against the SQL server. Now I wonder where MS
SQL put the resultset from the query and if access rights to this folder is
the cause of this problem.
Can anyone help?
LanCan you tell what is del1.mb. I feel share violation is because of some
concurrency and locking and not to do with file access.
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"Lan" wrote:

> I have a multi user application running with a query to fill GUI component
s
> (Delphi)
> I get a 'share violation' for 'del1.mb' (according to a user). All users r
un
> with the same userid/password against the SQL server. Now I wonder where M
S
> SQL put the resultset from the query and if access rights to this folder i
s
> the cause of this problem.
> Can anyone help?
> Lan|||SQL Server doesn't store the results in a file. It sends the result to the c
lient application. So,
you have to ask this in a Delphi forum.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lan" <Lan@.discussions.microsoft.com> wrote in message
news:4A9D1C6F-7866-4BC8-81E3-C8F88C96FC90@.microsoft.com...
>I have a multi user application running with a query to fill GUI components
> (Delphi)
> I get a 'share violation' for 'del1.mb' (according to a user). All users r
un
> with the same userid/password against the SQL server. Now I wonder where M
S
> SQL put the resultset from the query and if access rights to this folder i
s
> the cause of this problem.
> Can anyone help?
> Lansql

Location of MDF / LDF / Backup / Log

For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your adviceStephen wrote:
> For a finance application, we use a Windows 2003 Server
> with C and D are local HD (They are mirrored as RAID 1).
> On the other hand, it is connected to a SAN with drive
> letter J.
> We find that the consultant has installed the SQLS Server
> 2000 DB MDF and LDF in the D drive. The finance
> application is installed on the J drive.
> We will backup both C,D and J drive daily. They haven't
> setup the database maintenance plan yet.
> For both performance purpose, we would like to get your
> advie where is the best place to put the backup files and
> log files ? Is it D or J drive ? Should we suggest them
> to put the transaction log file from D to J as well ?
> Thank you for your advice
If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored set (so that makes four local hard drives)? You don't have to
back up the drives themselves for SQL Server backup and recovery. What
you need to back up are the full, differential, and t-log backups you
make throughout the day. Generally, you want to put the backup files on
different spindles than the drive on which the data originates to
maximize bandwidth. So if the data is on the SAN, you can back up to one
of the local arrays.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> Stephen wrote:
>> For a finance application, we use a Windows 2003 Server
>> with C and D are local HD (They are mirrored as RAID 1).
>> On the other hand, it is connected to a SAN with drive
>> letter J.
>> We find that the consultant has installed the SQLS Server
>> 2000 DB MDF and LDF in the D drive. The finance
>> application is installed on the J drive.
>> We will backup both C,D and J drive daily. They haven't
>> setup the database maintenance plan yet.
>> For both performance purpose, we would like to get your
>> advie where is the best place to put the backup files and
>> log files ? Is it D or J drive ? Should we suggest them
>> to put the transaction log file from D to J as well ?
>> Thank you for your advice
> If you've paid for a SAN, it seems silly not use it for the data and
> t-logs. Are you saying that C is a mirrored set and drive D is a mirrored
> set (so that makes four local hard drives)? You don't have to back up the
> drives themselves for SQL Server backup and recovery. What you need to
> back up are the full, differential, and t-log backups you make throughout
> the day. Generally, you want to put the backup files on different spindles
> than the drive on which the data originates to maximize bandwidth. So if
> the data is on the SAN, you can back up to one of the local arrays.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Dear David,
> Thank you for your advice. Yes, both C and D are mirrored set.
> Someone suggest to put the transaction log file in the RAID 1 for recovery
> and data in RAID 5. Is it OK from your point of view ?
> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> Thanks
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> > Stephen wrote:
> >> For a finance application, we use a Windows 2003 Server
> >> with C and D are local HD (They are mirrored as RAID 1).
> >> On the other hand, it is connected to a SAN with drive
> >> letter J.
> >>
> >> We find that the consultant has installed the SQLS Server
> >> 2000 DB MDF and LDF in the D drive. The finance
> >> application is installed on the J drive.
> >>
> >> We will backup both C,D and J drive daily. They haven't
> >> setup the database maintenance plan yet.
> >>
> >> For both performance purpose, we would like to get your
> >> advie where is the best place to put the backup files and
> >> log files ? Is it D or J drive ? Should we suggest them
> >> to put the transaction log file from D to J as well ?
> >>
> >> Thank you for your advice
> >
> > If you've paid for a SAN, it seems silly not use it for the data and
> > t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored
> > set (so that makes four local hard drives)? You don't have to back up
the
> > drives themselves for SQL Server backup and recovery. What you need to
> > back up are the full, differential, and t-log backups you make
throughout
> > the day. Generally, you want to put the backup files on different
spindles
> > than the drive on which the data originates to maximize bandwidth. So if
> > the data is on the SAN, you can back up to one of the local arrays.
> >
> > --
> > David Gugick
> > Quest Software
> > www.imceda.com
> > www.quest.com
>|||Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Stephen
> Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
> buy RAID 10&1) put the log on
> RAID1 device and data on RAID-5
> (this option is appropriate if the writes activites are moderate)
>
> Note: The appropriate decision depends on your perfomance charctiristics
> and fault tolerance needs.
>
>
> "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>> Dear David,
>> Thank you for your advice. Yes, both C and D are mirrored set.
>> Someone suggest to put the transaction log file in the RAID 1 for
>> recovery
>> and data in RAID 5. Is it OK from your point of view ?
>> Where should I put the backup file ? RAID 1 or RAID 5 array ?
>> Thanks
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
>> > Stephen wrote:
>> >> For a finance application, we use a Windows 2003 Server
>> >> with C and D are local HD (They are mirrored as RAID 1).
>> >> On the other hand, it is connected to a SAN with drive
>> >> letter J.
>> >>
>> >> We find that the consultant has installed the SQLS Server
>> >> 2000 DB MDF and LDF in the D drive. The finance
>> >> application is installed on the J drive.
>> >>
>> >> We will backup both C,D and J drive daily. They haven't
>> >> setup the database maintenance plan yet.
>> >>
>> >> For both performance purpose, we would like to get your
>> >> advie where is the best place to put the backup files and
>> >> log files ? Is it D or J drive ? Should we suggest them
>> >> to put the transaction log file from D to J as well ?
>> >>
>> >> Thank you for your advice
>> >
>> > If you've paid for a SAN, it seems silly not use it for the data and
>> > t-logs. Are you saying that C is a mirrored set and drive D is a
> mirrored
>> > set (so that makes four local hard drives)? You don't have to back up
> the
>> > drives themselves for SQL Server backup and recovery. What you need to
>> > back up are the full, differential, and t-log backups you make
> throughout
>> > the day. Generally, you want to put the backup files on different
> spindles
>> > than the drive on which the data originates to maximize bandwidth. So
>> > if
>> > the data is on the SAN, you can back up to one of the local arrays.
>> >
>> > --
>> > David Gugick
>> > Quest Software
>> > www.imceda.com
>> > www.quest.com
>>
>|||Stepfen
I'd recommend you buy "Inside SQL Server 2000" written by Kalen Delaney (See
topic about RAID solutions)
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:e2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would like
to
> know the underlying reason to put the log on RAID 1 and data on RAID 5.
> On the other hand, should we put the Database and Transaction log backup
on
> RAID 5 array ?
> Thank you again.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> > Stephen
> > Most of SQL Server DBA/Programmers ( yep if the budget does not permit
to
> > buy RAID 10&1) put the log on
> > RAID1 device and data on RAID-5
> > (this option is appropriate if the writes activites are moderate)
> >
> >
> >
> > Note: The appropriate decision depends on your perfomance
charctiristics
> > and fault tolerance needs.
> >
> >
> >
> >
> > "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> > news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> >> Dear David,
> >>
> >> Thank you for your advice. Yes, both C and D are mirrored set.
> >>
> >> Someone suggest to put the transaction log file in the RAID 1 for
> >> recovery
> >> and data in RAID 5. Is it OK from your point of view ?
> >>
> >> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> >>
> >> Thanks
> >>
> >> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> >> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> >> > Stephen wrote:
> >> >> For a finance application, we use a Windows 2003 Server
> >> >> with C and D are local HD (They are mirrored as RAID 1).
> >> >> On the other hand, it is connected to a SAN with drive
> >> >> letter J.
> >> >>
> >> >> We find that the consultant has installed the SQLS Server
> >> >> 2000 DB MDF and LDF in the D drive. The finance
> >> >> application is installed on the J drive.
> >> >>
> >> >> We will backup both C,D and J drive daily. They haven't
> >> >> setup the database maintenance plan yet.
> >> >>
> >> >> For both performance purpose, we would like to get your
> >> >> advie where is the best place to put the backup files and
> >> >> log files ? Is it D or J drive ? Should we suggest them
> >> >> to put the transaction log file from D to J as well ?
> >> >>
> >> >> Thank you for your advice
> >> >
> >> > If you've paid for a SAN, it seems silly not use it for the data and
> >> > t-logs. Are you saying that C is a mirrored set and drive D is a
> > mirrored
> >> > set (so that makes four local hard drives)? You don't have to back up
> > the
> >> > drives themselves for SQL Server backup and recovery. What you need
to
> >> > back up are the full, differential, and t-log backups you make
> > throughout
> >> > the day. Generally, you want to put the backup files on different
> > spindles
> >> > than the drive on which the data originates to maximize bandwidth. So
> >> > if
> >> > the data is on the SAN, you can back up to one of the local arrays.
> >> >
> >> > --
> >> > David Gugick
> >> > Quest Software
> >> > www.imceda.com
> >> > www.quest.com
> >>
> >>
> >
> >
>|||Stephen wrote:
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would
> like to know the underlying reason to put the log on RAID 1 and data
> on RAID 5.
> On the other hand, should we put the Database and Transaction log
> backup on RAID 5 array ?
>
I didn't think RAID 5 was one of your installed options given you only
had 4 local drives...
In any case, for small and medium sized databases with a low percentage
of writes, RAID 5 is an inexpensive solution. RAID 5 writes data very
slowly, so it's only appropriate for data files. Transaction logs and
tempdb should reside on a mirrored set (which you have). It's best for
performance to keep data and t-logs on different drives, which are also
separate from the OS. In your environment, you have a SAN (J), and 2
local mirrored sets (C and D).
I would recommend you use C for the OS and applications like SQL Server.
Use D for the t-logs and tempdb, and use the SAN for the data.
Everything needs to backed up in some fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is a multi-part message in MIME format.
--030209030201000601050508
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
This website has a good intro to RAID levels and the basic pros & cons
(even if they are basically trying to sell you their products):
http://www.acnc.com/raid.html
(for those that can't afford Kalen's book _Inside SQL Server_, which in
an excellent book that I thoroughly recommend)
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Stephen wrote:
>Dear Uri,
>Thank you for your advice. However, I am new to RAID array. I would like to
>know the underlying reason to put the log on RAID 1 and data on RAID 5.
>On the other hand, should we put the Database and Transaction log backup on
>RAID 5 array ?
>Thank you again.
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>
>>Stephen
>>Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
>>buy RAID 10&1) put the log on
>>RAID1 device and data on RAID-5
>>(this option is appropriate if the writes activites are moderate)
>>
>>Note: The appropriate decision depends on your perfomance charctiristics
>>and fault tolerance needs.
>>
>>
>>"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
>>news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>>
>>Dear David,
>>Thank you for your advice. Yes, both C and D are mirrored set.
>>Someone suggest to put the transaction log file in the RAID 1 for
>>recovery
>>and data in RAID 5. Is it OK from your point of view ?
>>Where should I put the backup file ? RAID 1 or RAID 5 array ?
>>Thanks
>>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>>news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
>>
>>Stephen wrote:
>>
>>For a finance application, we use a Windows 2003 Server
>>with C and D are local HD (They are mirrored as RAID 1).
>>On the other hand, it is connected to a SAN with drive
>>letter J.
>>We find that the consultant has installed the SQLS Server
>>2000 DB MDF and LDF in the D drive. The finance
>>application is installed on the J drive.
>>We will backup both C,D and J drive daily. They haven't
>>setup the database maintenance plan yet.
>>For both performance purpose, we would like to get your
>>advie where is the best place to put the backup files and
>>log files ? Is it D or J drive ? Should we suggest them
>>to put the transaction log file from D to J as well ?
>>Thank you for your advice
>>
>>If you've paid for a SAN, it seems silly not use it for the data and
>>t-logs. Are you saying that C is a mirrored set and drive D is a
>>
>>mirrored
>>
>>set (so that makes four local hard drives)? You don't have to back up
>>
>>the
>>
>>drives themselves for SQL Server backup and recovery. What you need to
>>back up are the full, differential, and t-log backups you make
>>
>>throughout
>>
>>the day. Generally, you want to put the backup files on different
>>
>>spindles
>>
>>than the drive on which the data originates to maximize bandwidth. So
>>if
>>the data is on the SAN, you can back up to one of the local arrays.
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>>
>>
>
>
--030209030201000601050508
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>This website has a good intro to RAID levels and the basic pros
& cons (even if they are basically trying to sell you their
products): 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.acnc.com/raid.html</a> ">http://www.acnc.com/raid.html">http://www.acnc.com/raid.html</a> 
 
(for those that can't afford Kalen's book Inside SQL Server,
which in an excellent book that I thoroughly recommend) 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Stephen wrote:
<blockquote cite="mide2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:urid@.iscar.co.il"><urid@.iscar.co.il></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl">news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:anonymous@.discussions.microsoft.com"><anonymous@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl">news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for
recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl">news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Stephen wrote:
</pre>
<blockquote type="cite">
<pre wrap="">For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your advice
</pre>
</blockquote>
<pre wrap="">If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
</pre>
</blockquote>
</blockquote>
<pre wrap="">mirrored
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">set (so that makes four local hard drives)? You don't have to back up
</pre>
</blockquote>
</blockquote>
<pre wrap="">the
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">drives themselves for SQL Server backup and recovery. What you need to
back up are the full, differential, and t-log backups you make
</pre>
</blockquote>
</blockquote>
<pre wrap="">throughout
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">the day. Generally, you want to put the backup files on different
</pre>
</blockquote>
</blockquote>
<pre wrap="">spindles
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">than the drive on which the data originates to maximize bandwidth. So
if
the data is on the SAN, you can back up to one of the local arrays.
--
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--030209030201000601050508--

Friday, March 30, 2012

Locking

Locking

Lock: Timeout

Monday, March 26, 2012

Lock problem when inserting and deleting records

Lock problem when inserting and deleting records

Lock Problem and application is slowed down

Lock Monitoring

Lock Monitor

Lock Monitor

Lock Monitor

Friday, March 23, 2012

Lock Database

Lock Database

Lock Database

Wednesday, March 21, 2012

Location of query results (sql 2000)

Location of MDF / LDF / Backup / Log

Location of MDF / LDF / Backup / Log

Location of MDF / LDF / Backup / Log

sql local data

Blog Archive

About Me