I've got the following stored procedure I'm working on:
-- ============================================= -- Create procedure postCash
-- ============================================= USE Prototype
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'postCash'
AND type = 'P')
DROP PROCEDURE postCash
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE postCash
@.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
@.post_date varchar(10), @.amount money, @.comment varchar(50),
@.check_number varchar(50) = NULL
AS
SET NOCOUNT ON
DECLARE @.err_code int
DECLARE @.current_bal money
BEGIN TRANSACTION
SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount, NewBalance,
Comment, CheckNumber)
VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
@.current_bal, @.comment, @.check_number);
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
COMMIT TRANSACTION
RETURN 0
AbortTransaction:
ROLLBACK TRANSACTION
RETURN @.err_code
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I want to make sure that no one else can read the value of NewBalance until
the entire transaction completes successfully. I've read through various
locking topics in BOL but I don't quite understand the difference between
putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
followed by GO. But if I use that in my SP then won't it wipe out all my
variable declarations? Will putting SERIALIZABLE after FROM produce the
result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE do I then need to "turn it off" at the end somewhere?Ron,
As I understand it, you could use HOLDLOCK for the query (equivalent to
SERIALIZABLE). No one should be able to see the INSERTed record until the
tran commits.
HTH
Jerry
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O8uWkNc1FHA.3892@.TK2MSFTNGP12.phx.gbl...
> I've got the following stored procedure I'm working on:
> -- =============================================> -- Create procedure postCash
> -- =============================================> USE Prototype
> GO
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'postCash'
> AND type = 'P')
> DROP PROCEDURE postCash
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE postCash
> @.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
> @.post_date varchar(10), @.amount money, @.comment varchar(50),
> @.check_number varchar(50) = NULL
> AS
> SET NOCOUNT ON
> DECLARE @.err_code int
> DECLARE @.current_bal money
> BEGIN TRANSACTION
> SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount,
> NewBalance,
> Comment, CheckNumber)
> VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
> @.current_bal, @.comment, @.check_number);
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> COMMIT TRANSACTION
> RETURN 0
> AbortTransaction:
> ROLLBACK TRANSACTION
> RETURN @.err_code
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I want to make sure that no one else can read the value of NewBalance
> until
> the entire transaction completes successfully. I've read through various
> locking topics in BOL but I don't quite understand the difference between
> putting, say SERIALIZABLE after FROM in the SELECT or using SET
> TRANSACTION
> ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is
> always
> followed by GO. But if I use that in my SP then won't it wipe out all my
> variable declarations? Will putting SERIALIZABLE after FROM produce the
> result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
> SERIALIZABLE do I then need to "turn it off" at the end somewhere?
>|||On Thu, 20 Oct 2005 15:12:40 -0700, "Ron Hinds"
<__ron__dontspamme@.wedontlikespam_garageiq.com> wrote:
>I want to make sure that no one else can read the value of NewBalance until
>the entire transaction completes successfully.
How sure?
If you mean COMPLETELY SURE I don't think you can do it (without
heroic methods) in SQLServer, if reader allows dirty reads. I don't
believe even serializable shuts out readers. You could throw the
database into single-user mode, but that's pretty extreme.
On the other hand, simply doing what you've already done, putting the
insert into a transaction, will assure that readers in the default
"read committed" or higher levels of isolation, will pend behind your
transaction if they try to read it.
> I've read through various
>locking topics in BOL but I don't quite understand the difference between
>putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
>ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
>followed by GO. But if I use that in my SP then won't it wipe out all my
>variable declarations?
Don't put GO in SPs, everything will be fine.
>Will putting SERIALIZABLE after FROM produce the
>result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
>SERIALIZABLE do I then need to "turn it off" at the end somewhere?
It ends with the SP, but I think you don't really need it.
J.sql
Showing posts with label working. Show all posts
Showing posts with label working. Show all posts
Friday, March 30, 2012
Locking
I've got the following stored procedure I'm working on:
-- =============================================
-- Create procedure postCash
-- =============================================
USE Prototype
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'postCash'
AND type = 'P')
DROP PROCEDURE postCash
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE postCash
@.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
@.post_date varchar(10), @.amount money, @.comment varchar(50),
@.check_number varchar(50) = NULL
AS
SET NOCOUNT ON
DECLARE @.err_code int
DECLARE @.current_bal money
BEGIN TRANSACTION
SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount, NewBalance,
Comment, CheckNumber)
VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
@.current_bal, @.comment, @.check_number);
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
COMMIT TRANSACTION
RETURN 0
AbortTransaction:
ROLLBACK TRANSACTION
RETURN @.err_code
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I want to make sure that no one else can read the value of NewBalance until
the entire transaction completes successfully. I've read through various
locking topics in BOL but I don't quite understand the difference between
putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
followed by GO. But if I use that in my SP then won't it wipe out all my
variable declarations? Will putting SERIALIZABLE after FROM produce the
result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE do I then need to "turn it off" at the end somewhere?
Ron,
As I understand it, you could use HOLDLOCK for the query (equivalent to
SERIALIZABLE). No one should be able to see the INSERTed record until the
tran commits.
HTH
Jerry
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O8uWkNc1FHA.3892@.TK2MSFTNGP12.phx.gbl...
> I've got the following stored procedure I'm working on:
> -- =============================================
> -- Create procedure postCash
> -- =============================================
> USE Prototype
> GO
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'postCash'
> AND type = 'P')
> DROP PROCEDURE postCash
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE postCash
> @.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
> @.post_date varchar(10), @.amount money, @.comment varchar(50),
> @.check_number varchar(50) = NULL
> AS
> SET NOCOUNT ON
> DECLARE @.err_code int
> DECLARE @.current_bal money
> BEGIN TRANSACTION
> SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount,
> NewBalance,
> Comment, CheckNumber)
> VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
> @.current_bal, @.comment, @.check_number);
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> COMMIT TRANSACTION
> RETURN 0
> AbortTransaction:
> ROLLBACK TRANSACTION
> RETURN @.err_code
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I want to make sure that no one else can read the value of NewBalance
> until
> the entire transaction completes successfully. I've read through various
> locking topics in BOL but I don't quite understand the difference between
> putting, say SERIALIZABLE after FROM in the SELECT or using SET
> TRANSACTION
> ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is
> always
> followed by GO. But if I use that in my SP then won't it wipe out all my
> variable declarations? Will putting SERIALIZABLE after FROM produce the
> result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
> SERIALIZABLE do I then need to "turn it off" at the end somewhere?
>
|||On Thu, 20 Oct 2005 15:12:40 -0700, "Ron Hinds"
<__ron__dontspamme@.wedontlikespam_garageiq.com> wrote:
>I want to make sure that no one else can read the value of NewBalance until
>the entire transaction completes successfully.
How sure?
If you mean COMPLETELY SURE I don't think you can do it (without
heroic methods) in SQLServer, if reader allows dirty reads. I don't
believe even serializable shuts out readers. You could throw the
database into single-user mode, but that's pretty extreme.
On the other hand, simply doing what you've already done, putting the
insert into a transaction, will assure that readers in the default
"read committed" or higher levels of isolation, will pend behind your
transaction if they try to read it.
> I've read through various
>locking topics in BOL but I don't quite understand the difference between
>putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
>ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
>followed by GO. But if I use that in my SP then won't it wipe out all my
>variable declarations?
Don't put GO in SPs, everything will be fine.
>Will putting SERIALIZABLE after FROM produce the
>result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
>SERIALIZABLE do I then need to "turn it off" at the end somewhere?
It ends with the SP, but I think you don't really need it.
J.
-- =============================================
-- Create procedure postCash
-- =============================================
USE Prototype
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'postCash'
AND type = 'P')
DROP PROCEDURE postCash
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE postCash
@.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
@.post_date varchar(10), @.amount money, @.comment varchar(50),
@.check_number varchar(50) = NULL
AS
SET NOCOUNT ON
DECLARE @.err_code int
DECLARE @.current_bal money
BEGIN TRANSACTION
SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount, NewBalance,
Comment, CheckNumber)
VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
@.current_bal, @.comment, @.check_number);
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
COMMIT TRANSACTION
RETURN 0
AbortTransaction:
ROLLBACK TRANSACTION
RETURN @.err_code
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I want to make sure that no one else can read the value of NewBalance until
the entire transaction completes successfully. I've read through various
locking topics in BOL but I don't quite understand the difference between
putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
followed by GO. But if I use that in my SP then won't it wipe out all my
variable declarations? Will putting SERIALIZABLE after FROM produce the
result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE do I then need to "turn it off" at the end somewhere?
Ron,
As I understand it, you could use HOLDLOCK for the query (equivalent to
SERIALIZABLE). No one should be able to see the INSERTed record until the
tran commits.
HTH
Jerry
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O8uWkNc1FHA.3892@.TK2MSFTNGP12.phx.gbl...
> I've got the following stored procedure I'm working on:
> -- =============================================
> -- Create procedure postCash
> -- =============================================
> USE Prototype
> GO
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'postCash'
> AND type = 'P')
> DROP PROCEDURE postCash
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE postCash
> @.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
> @.post_date varchar(10), @.amount money, @.comment varchar(50),
> @.check_number varchar(50) = NULL
> AS
> SET NOCOUNT ON
> DECLARE @.err_code int
> DECLARE @.current_bal money
> BEGIN TRANSACTION
> SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount,
> NewBalance,
> Comment, CheckNumber)
> VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
> @.current_bal, @.comment, @.check_number);
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> COMMIT TRANSACTION
> RETURN 0
> AbortTransaction:
> ROLLBACK TRANSACTION
> RETURN @.err_code
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I want to make sure that no one else can read the value of NewBalance
> until
> the entire transaction completes successfully. I've read through various
> locking topics in BOL but I don't quite understand the difference between
> putting, say SERIALIZABLE after FROM in the SELECT or using SET
> TRANSACTION
> ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is
> always
> followed by GO. But if I use that in my SP then won't it wipe out all my
> variable declarations? Will putting SERIALIZABLE after FROM produce the
> result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
> SERIALIZABLE do I then need to "turn it off" at the end somewhere?
>
|||On Thu, 20 Oct 2005 15:12:40 -0700, "Ron Hinds"
<__ron__dontspamme@.wedontlikespam_garageiq.com> wrote:
>I want to make sure that no one else can read the value of NewBalance until
>the entire transaction completes successfully.
How sure?
If you mean COMPLETELY SURE I don't think you can do it (without
heroic methods) in SQLServer, if reader allows dirty reads. I don't
believe even serializable shuts out readers. You could throw the
database into single-user mode, but that's pretty extreme.
On the other hand, simply doing what you've already done, putting the
insert into a transaction, will assure that readers in the default
"read committed" or higher levels of isolation, will pend behind your
transaction if they try to read it.
> I've read through various
>locking topics in BOL but I don't quite understand the difference between
>putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
>ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
>followed by GO. But if I use that in my SP then won't it wipe out all my
>variable declarations?
Don't put GO in SPs, everything will be fine.
>Will putting SERIALIZABLE after FROM produce the
>result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
>SERIALIZABLE do I then need to "turn it off" at the end somewhere?
It ends with the SP, but I think you don't really need it.
J.
Friday, March 23, 2012
Lock escalation not working
I occassionally see one of the spid holding around 10-20 million locks in
the server. The spid is supposed to update around 1 to 2 records out of 90
million rows table and its well indexed. The spid is generated by
application server and usually there are 4-5 processes trying to
insert/update same table.
Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
server do lock escalation when it reaches certain threashold? Is it a bug?
As a result of this, I am getting error 1204, "cannot obtain lock resource
at this time". I am running SQL 2000 SP4
I appreicate your answer.
If there are any other users with any shared or higher locks in that table
it can not escalate to a table lock. Since you say it is busy that sounds
like the case. But if it is taking out that many locks it is obviously not
doing what you think. My guess would be you have this in serializable mode.
Can you post the exact code for the UPDATE and the DDL for the table
including indexes. What does the estimated query plan look like?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>I occassionally see one of the spid holding around 10-20 million locks in
>the server. The spid is supposed to update around 1 to 2 records out of 90
>million rows table and its well indexed. The spid is generated by
>application server and usually there are 4-5 processes trying to
>insert/update same table.
> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
> server do lock escalation when it reaches certain threashold? Is it a bug?
> As a result of this, I am getting error 1204, "cannot obtain lock resource
> at this time". I am running SQL 2000 SP4
> I appreicate your answer.
>
|||Thanks for the reply. Update statement and DDL for the table/index is
attached. Estimated query looks good, using right indexes and returing
expected number of rows for update.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
> If there are any other users with any shared or higher locks in that table
> it can not escalate to a table lock. Since you say it is busy that sounds
> like the case. But if it is taking out that many locks it is obviously
> not
> doing what you think. My guess would be you have this in serializable
> mode.
> Can you post the exact code for the UPDATE and the DDL for the table
> including indexes. What does the estimated query plan look like?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>
|||Andrew,
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, May be in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, Can we put index hint on update statement so
that sql server use right index to search for those records that needs to be
updated?
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>
>
|||Well it isn't the expected number of rows that get updated that is the
factor here. It has more to do with how many it needs to look at to find
those rows. If 2 rows get updated but it has to scan an entire index to find
those 2 that is not good. Is this the index it is using?
IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
[long_short_indicator])
How many rows match those three columns for the values you are using in the
update? Are the values represented by variables or actual parameters to a
sp? Even though the estimated query plan looks good that does not mean that
is what was used when ran. You can have parameter sniffing happening here
and might be suffering from a bad query plan. But again this is only part
of the actual code so what isolation level are you running in during this
update? Are there other DML statements in the same transaction? How are
you calling this code?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>
>
|||Andrew, ( I sent this earlier too but just resending it)
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, I am planning to apply index hint to solve this
issuue.
Your comments are highly appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Well it isn't the expected number of rows that get updated that is the
> factor here. It has more to do with how many it needs to look at to find
> those rows. If 2 rows get updated but it has to scan an entire index to
> find those 2 that is not good. Is this the index it is using?
> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
> [long_short_indicator])
> How many rows match those three columns for the values you are using in
> the update? Are the values represented by variables or actual parameters
> to a sp? Even though the estimated query plan looks good that does not
> mean that is what was used when ran. You can have parameter sniffing
> happening here and might be suffering from a bad query plan. But again
> this is only part of the actual code so what isolation level are you
> running in during this update? Are there other DML statements in the same
> transaction? How are you calling this code?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>
|||OK that goes along with my original assumptions. For some reason it is
choosing the wrong plan at times. If you answer the rest of my questions
maybe it will help to narrow down the actual cause. But some things to
consider here. First is that you have two different WHERE clauses that may
require different plans of attack. I would create two sps, one for each of
those updates and call the appropriate one based on the parameters passed.
And if you find that the correct index for the update is always
IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
But make sure it is always the correct way to deal with the updates.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Andrew, ( I sent this earlier too but just resending it)
> Just to add one more point: The 20 million locks of mode 'U' is happening
> on
> IDX_CA_KEY5. Which is not a good index for the searching for that update
> statement.
> So, although, when I saw the estimated execution plan it was using good
> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
> environment, optimizer its using wrong index once in while causing all
> those millions locks.
> Does that make sense? If so, I am planning to apply index hint to solve
> this issuue.
> Your comments are highly appreciated.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>
|||Andrew,
Once again thanks for your time. I will reply to this thread once I
implemented the index hint and see if that will solve the issue or not.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
> OK that goes along with my original assumptions. For some reason it is
> choosing the wrong plan at times. If you answer the rest of my questions
> maybe it will help to narrow down the actual cause. But some things to
> consider here. First is that you have two different WHERE clauses that may
> require different plans of attack. I would create two sps, one for each of
> those updates and call the appropriate one based on the parameters passed.
> And if you find that the correct index for the update is always
> IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
> But make sure it is always the correct way to deal with the updates.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>
|||You may also want to try using OPTIMIZE FOR instead of and index hint. This
will allow for two situations: 1) someone renames/drops the existing index
and 2) someone builds a better index for the query.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"James" <kush@.brandes.com> wrote in message
news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> Once again thanks for your time. I will reply to this thread once I
> implemented the index hint and see if that will solve the issue or not.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>
|||Unfortunately he is running SQL2000 and can not go that route.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kui32a08dptc6@.corp.supernews.com...
> You may also want to try using OPTIMIZE FOR instead of and index hint.
> This will allow for two situations: 1) someone renames/drops the existing
> index and 2) someone builds a better index for the query.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "James" <kush@.brandes.com> wrote in message
> news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
>
sql
the server. The spid is supposed to update around 1 to 2 records out of 90
million rows table and its well indexed. The spid is generated by
application server and usually there are 4-5 processes trying to
insert/update same table.
Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
server do lock escalation when it reaches certain threashold? Is it a bug?
As a result of this, I am getting error 1204, "cannot obtain lock resource
at this time". I am running SQL 2000 SP4
I appreicate your answer.
If there are any other users with any shared or higher locks in that table
it can not escalate to a table lock. Since you say it is busy that sounds
like the case. But if it is taking out that many locks it is obviously not
doing what you think. My guess would be you have this in serializable mode.
Can you post the exact code for the UPDATE and the DDL for the table
including indexes. What does the estimated query plan look like?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>I occassionally see one of the spid holding around 10-20 million locks in
>the server. The spid is supposed to update around 1 to 2 records out of 90
>million rows table and its well indexed. The spid is generated by
>application server and usually there are 4-5 processes trying to
>insert/update same table.
> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
> server do lock escalation when it reaches certain threashold? Is it a bug?
> As a result of this, I am getting error 1204, "cannot obtain lock resource
> at this time". I am running SQL 2000 SP4
> I appreicate your answer.
>
|||Thanks for the reply. Update statement and DDL for the table/index is
attached. Estimated query looks good, using right indexes and returing
expected number of rows for update.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
> If there are any other users with any shared or higher locks in that table
> it can not escalate to a table lock. Since you say it is busy that sounds
> like the case. But if it is taking out that many locks it is obviously
> not
> doing what you think. My guess would be you have this in serializable
> mode.
> Can you post the exact code for the UPDATE and the DDL for the table
> including indexes. What does the estimated query plan look like?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>
|||Andrew,
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, May be in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, Can we put index hint on update statement so
that sql server use right index to search for those records that needs to be
updated?
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>
>
|||Well it isn't the expected number of rows that get updated that is the
factor here. It has more to do with how many it needs to look at to find
those rows. If 2 rows get updated but it has to scan an entire index to find
those 2 that is not good. Is this the index it is using?
IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
[long_short_indicator])
How many rows match those three columns for the values you are using in the
update? Are the values represented by variables or actual parameters to a
sp? Even though the estimated query plan looks good that does not mean that
is what was used when ran. You can have parameter sniffing happening here
and might be suffering from a bad query plan. But again this is only part
of the actual code so what isolation level are you running in during this
update? Are there other DML statements in the same transaction? How are
you calling this code?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>
>
|||Andrew, ( I sent this earlier too but just resending it)
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, I am planning to apply index hint to solve this
issuue.
Your comments are highly appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Well it isn't the expected number of rows that get updated that is the
> factor here. It has more to do with how many it needs to look at to find
> those rows. If 2 rows get updated but it has to scan an entire index to
> find those 2 that is not good. Is this the index it is using?
> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
> [long_short_indicator])
> How many rows match those three columns for the values you are using in
> the update? Are the values represented by variables or actual parameters
> to a sp? Even though the estimated query plan looks good that does not
> mean that is what was used when ran. You can have parameter sniffing
> happening here and might be suffering from a bad query plan. But again
> this is only part of the actual code so what isolation level are you
> running in during this update? Are there other DML statements in the same
> transaction? How are you calling this code?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>
|||OK that goes along with my original assumptions. For some reason it is
choosing the wrong plan at times. If you answer the rest of my questions
maybe it will help to narrow down the actual cause. But some things to
consider here. First is that you have two different WHERE clauses that may
require different plans of attack. I would create two sps, one for each of
those updates and call the appropriate one based on the parameters passed.
And if you find that the correct index for the update is always
IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
But make sure it is always the correct way to deal with the updates.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Andrew, ( I sent this earlier too but just resending it)
> Just to add one more point: The 20 million locks of mode 'U' is happening
> on
> IDX_CA_KEY5. Which is not a good index for the searching for that update
> statement.
> So, although, when I saw the estimated execution plan it was using good
> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
> environment, optimizer its using wrong index once in while causing all
> those millions locks.
> Does that make sense? If so, I am planning to apply index hint to solve
> this issuue.
> Your comments are highly appreciated.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>
|||Andrew,
Once again thanks for your time. I will reply to this thread once I
implemented the index hint and see if that will solve the issue or not.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
> OK that goes along with my original assumptions. For some reason it is
> choosing the wrong plan at times. If you answer the rest of my questions
> maybe it will help to narrow down the actual cause. But some things to
> consider here. First is that you have two different WHERE clauses that may
> require different plans of attack. I would create two sps, one for each of
> those updates and call the appropriate one based on the parameters passed.
> And if you find that the correct index for the update is always
> IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
> But make sure it is always the correct way to deal with the updates.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>
|||You may also want to try using OPTIMIZE FOR instead of and index hint. This
will allow for two situations: 1) someone renames/drops the existing index
and 2) someone builds a better index for the query.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"James" <kush@.brandes.com> wrote in message
news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> Once again thanks for your time. I will reply to this thread once I
> implemented the index hint and see if that will solve the issue or not.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>
|||Unfortunately he is running SQL2000 and can not go that route.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kui32a08dptc6@.corp.supernews.com...
> You may also want to try using OPTIMIZE FOR instead of and index hint.
> This will allow for two situations: 1) someone renames/drops the existing
> index and 2) someone builds a better index for the query.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "James" <kush@.brandes.com> wrote in message
> news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
>
sql
Lock escalation not working
I occassionally see one of the spid holding around 10-20 million locks in
the server. The spid is supposed to update around 1 to 2 records out of 90
million rows table and its well indexed. The spid is generated by
application server and usually there are 4-5 processes trying to
insert/update same table.
Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
server do lock escalation when it reaches certain threashold? Is it a bug?
As a result of this, I am getting error 1204, "cannot obtain lock resource
at this time". I am running SQL 2000 SP4
I appreicate your answer.If there are any other users with any shared or higher locks in that table
it can not escalate to a table lock. Since you say it is busy that sounds
like the case. But if it is taking out that many locks it is obviously not
doing what you think. My guess would be you have this in serializable mode.
Can you post the exact code for the UPDATE and the DDL for the table
including indexes. What does the estimated query plan look like?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>I occassionally see one of the spid holding around 10-20 million locks in
>the server. The spid is supposed to update around 1 to 2 records out of 90
>million rows table and its well indexed. The spid is generated by
>application server and usually there are 4-5 processes trying to
>insert/update same table.
> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
> server do lock escalation when it reaches certain threashold? Is it a bug?
> As a result of this, I am getting error 1204, "cannot obtain lock resource
> at this time". I am running SQL 2000 SP4
> I appreicate your answer.
>|||underprocessable|||Andrew,
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, May be in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, Can we put index hint on update statement so
that sql server use right index to search for those records that needs to be
updated?
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>
>|||Well it isn't the expected number of rows that get updated that is the
factor here. It has more to do with how many it needs to look at to find
those rows. If 2 rows get updated but it has to scan an entire index to find
those 2 that is not good. Is this the index it is using?
IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
[long_short_indicator])
How many rows match those three columns for the values you are using in the
update? Are the values represented by variables or actual parameters to a
sp? Even though the estimated query plan looks good that does not mean that
is what was used when ran. You can have parameter sniffing happening here
and might be suffering from a bad query plan. But again this is only part
of the actual code so what isolation level are you running in during this
update? Are there other DML statements in the same transaction? How are
you calling this code?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>
>|||Andrew, ( I sent this earlier too but just resending it)
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, I am planning to apply index hint to solve this
issuue.
Your comments are highly appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Well it isn't the expected number of rows that get updated that is the
> factor here. It has more to do with how many it needs to look at to find
> those rows. If 2 rows get updated but it has to scan an entire index to
> find those 2 that is not good. Is this the index it is using?
> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
> [long_short_indicator])
> How many rows match those three columns for the values you are using in
> the update? Are the values represented by variables or actual parameters
> to a sp? Even though the estimated query plan looks good that does not
> mean that is what was used when ran. You can have parameter sniffing
> happening here and might be suffering from a bad query plan. But again
> this is only part of the actual code so what isolation level are you
> running in during this update? Are there other DML statements in the same
> transaction? How are you calling this code?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>|||OK that goes along with my original assumptions. For some reason it is
choosing the wrong plan at times. If you answer the rest of my questions
maybe it will help to narrow down the actual cause. But some things to
consider here. First is that you have two different WHERE clauses that may
require different plans of attack. I would create two sps, one for each of
those updates and call the appropriate one based on the parameters passed.
And if you find that the correct index for the update is always
IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
But make sure it is always the correct way to deal with the updates.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Andrew, ( I sent this earlier too but just resending it)
> Just to add one more point: The 20 million locks of mode 'U' is happening
> on
> IDX_CA_KEY5. Which is not a good index for the searching for that update
> statement.
> So, although, when I saw the estimated execution plan it was using good
> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
> environment, optimizer its using wrong index once in while causing all
> those millions locks.
> Does that make sense? If so, I am planning to apply index hint to solve
> this issuue.
> Your comments are highly appreciated.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>|||Andrew,
Once again thanks for your time. I will reply to this thread once I
implemented the index hint and see if that will solve the issue or not.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
> OK that goes along with my original assumptions. For some reason it is
> choosing the wrong plan at times. If you answer the rest of my questions
> maybe it will help to narrow down the actual cause. But some things to
> consider here. First is that you have two different WHERE clauses that may
> require different plans of attack. I would create two sps, one for each of
> those updates and call the appropriate one based on the parameters passed.
> And if you find that the correct index for the update is always
> IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
> But make sure it is always the correct way to deal with the updates.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>|||You may also want to try using OPTIMIZE FOR instead of and index hint. This
will allow for two situations: 1) someone renames/drops the existing index
and 2) someone builds a better index for the query.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"James" <kush@.brandes.com> wrote in message
news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> Once again thanks for your time. I will reply to this thread once I
> implemented the index hint and see if that will solve the issue or not.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>|||Unfortunately he is running SQL2000 and can not go that route.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kui32a08dptc6@.corp.supernews.com...
> You may also want to try using OPTIMIZE FOR instead of and index hint.
> This will allow for two situations: 1) someone renames/drops the existing
> index and 2) someone builds a better index for the query.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "James" <kush@.brandes.com> wrote in message
> news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
>
the server. The spid is supposed to update around 1 to 2 records out of 90
million rows table and its well indexed. The spid is generated by
application server and usually there are 4-5 processes trying to
insert/update same table.
Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
server do lock escalation when it reaches certain threashold? Is it a bug?
As a result of this, I am getting error 1204, "cannot obtain lock resource
at this time". I am running SQL 2000 SP4
I appreicate your answer.If there are any other users with any shared or higher locks in that table
it can not escalate to a table lock. Since you say it is busy that sounds
like the case. But if it is taking out that many locks it is obviously not
doing what you think. My guess would be you have this in serializable mode.
Can you post the exact code for the UPDATE and the DDL for the table
including indexes. What does the estimated query plan look like?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>I occassionally see one of the spid holding around 10-20 million locks in
>the server. The spid is supposed to update around 1 to 2 records out of 90
>million rows table and its well indexed. The spid is generated by
>application server and usually there are 4-5 processes trying to
>insert/update same table.
> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
> server do lock escalation when it reaches certain threashold? Is it a bug?
> As a result of this, I am getting error 1204, "cannot obtain lock resource
> at this time". I am running SQL 2000 SP4
> I appreicate your answer.
>|||underprocessable|||Andrew,
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, May be in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, Can we put index hint on update statement so
that sql server use right index to search for those records that needs to be
updated?
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>
>|||Well it isn't the expected number of rows that get updated that is the
factor here. It has more to do with how many it needs to look at to find
those rows. If 2 rows get updated but it has to scan an entire index to find
those 2 that is not good. Is this the index it is using?
IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
[long_short_indicator])
How many rows match those three columns for the values you are using in the
update? Are the values represented by variables or actual parameters to a
sp? Even though the estimated query plan looks good that does not mean that
is what was used when ran. You can have parameter sniffing happening here
and might be suffering from a bad query plan. But again this is only part
of the actual code so what isolation level are you running in during this
update? Are there other DML statements in the same transaction? How are
you calling this code?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>
>|||Andrew, ( I sent this earlier too but just resending it)
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, I am planning to apply index hint to solve this
issuue.
Your comments are highly appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Well it isn't the expected number of rows that get updated that is the
> factor here. It has more to do with how many it needs to look at to find
> those rows. If 2 rows get updated but it has to scan an entire index to
> find those 2 that is not good. Is this the index it is using?
> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
> [long_short_indicator])
> How many rows match those three columns for the values you are using in
> the update? Are the values represented by variables or actual parameters
> to a sp? Even though the estimated query plan looks good that does not
> mean that is what was used when ran. You can have parameter sniffing
> happening here and might be suffering from a bad query plan. But again
> this is only part of the actual code so what isolation level are you
> running in during this update? Are there other DML statements in the same
> transaction? How are you calling this code?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>|||OK that goes along with my original assumptions. For some reason it is
choosing the wrong plan at times. If you answer the rest of my questions
maybe it will help to narrow down the actual cause. But some things to
consider here. First is that you have two different WHERE clauses that may
require different plans of attack. I would create two sps, one for each of
those updates and call the appropriate one based on the parameters passed.
And if you find that the correct index for the update is always
IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
But make sure it is always the correct way to deal with the updates.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Andrew, ( I sent this earlier too but just resending it)
> Just to add one more point: The 20 million locks of mode 'U' is happening
> on
> IDX_CA_KEY5. Which is not a good index for the searching for that update
> statement.
> So, although, when I saw the estimated execution plan it was using good
> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
> environment, optimizer its using wrong index once in while causing all
> those millions locks.
> Does that make sense? If so, I am planning to apply index hint to solve
> this issuue.
> Your comments are highly appreciated.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>|||Andrew,
Once again thanks for your time. I will reply to this thread once I
implemented the index hint and see if that will solve the issue or not.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
> OK that goes along with my original assumptions. For some reason it is
> choosing the wrong plan at times. If you answer the rest of my questions
> maybe it will help to narrow down the actual cause. But some things to
> consider here. First is that you have two different WHERE clauses that may
> require different plans of attack. I would create two sps, one for each of
> those updates and call the appropriate one based on the parameters passed.
> And if you find that the correct index for the update is always
> IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
> But make sure it is always the correct way to deal with the updates.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>|||You may also want to try using OPTIMIZE FOR instead of and index hint. This
will allow for two situations: 1) someone renames/drops the existing index
and 2) someone builds a better index for the query.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"James" <kush@.brandes.com> wrote in message
news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> Once again thanks for your time. I will reply to this thread once I
> implemented the index hint and see if that will solve the issue or not.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>|||Unfortunately he is running SQL2000 and can not go that route.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kui32a08dptc6@.corp.supernews.com...
> You may also want to try using OPTIMIZE FOR instead of and index hint.
> This will allow for two situations: 1) someone renames/drops the existing
> index and 2) someone builds a better index for the query.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "James" <kush@.brandes.com> wrote in message
> news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
>
Lock escalation not working
I occassionally see one of the spid holding around 10-20 million locks in
the server. The spid is supposed to update around 1 to 2 records out of 90
million rows table and its well indexed. The spid is generated by
application server and usually there are 4-5 processes trying to
insert/update same table.
Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
server do lock escalation when it reaches certain threashold? Is it a bug?
As a result of this, I am getting error 1204, "cannot obtain lock resource
at this time". I am running SQL 2000 SP4
I appreicate your answer.If there are any other users with any shared or higher locks in that table
it can not escalate to a table lock. Since you say it is busy that sounds
like the case. But if it is taking out that many locks it is obviously not
doing what you think. My guess would be you have this in serializable mode.
Can you post the exact code for the UPDATE and the DDL for the table
including indexes. What does the estimated query plan look like?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>I occassionally see one of the spid holding around 10-20 million locks in
>the server. The spid is supposed to update around 1 to 2 records out of 90
>million rows table and its well indexed. The spid is generated by
>application server and usually there are 4-5 processes trying to
>insert/update same table.
> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
> server do lock escalation when it reaches certain threashold? Is it a bug?
> As a result of this, I am getting error 1204, "cannot obtain lock resource
> at this time". I am running SQL 2000 SP4
> I appreicate your answer.
>|||Andrew,
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, May be in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, Can we put index hint on update statement so
that sql server use right index to search for those records that needs to be
updated?
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that sounds
>> like the case. But if it is taking out that many locks it is obviously
>> not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks in
>>the server. The spid is supposed to update around 1 to 2 records out of
>>90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>
>|||Well it isn't the expected number of rows that get updated that is the
factor here. It has more to do with how many it needs to look at to find
those rows. If 2 rows get updated but it has to scan an entire index to find
those 2 that is not good. Is this the index it is using?
IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
[long_short_indicator])
How many rows match those three columns for the values you are using in the
update? Are the values represented by variables or actual parameters to a
sp? Even though the estimated query plan looks good that does not mean that
is what was used when ran. You can have parameter sniffing happening here
and might be suffering from a bad query plan. But again this is only part
of the actual code so what isolation level are you running in during this
update? Are there other DML statements in the same transaction? How are
you calling this code?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that sounds
>> like the case. But if it is taking out that many locks it is obviously
>> not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks in
>>the server. The spid is supposed to update around 1 to 2 records out of
>>90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>
>|||Andrew, ( I sent this earlier too but just resending it)
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, I am planning to apply index hint to solve this
issuue.
Your comments are highly appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Well it isn't the expected number of rows that get updated that is the
> factor here. It has more to do with how many it needs to look at to find
> those rows. If 2 rows get updated but it has to scan an entire index to
> find those 2 that is not good. Is this the index it is using?
> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
> [long_short_indicator])
> How many rows match those three columns for the values you are using in
> the update? Are the values represented by variables or actual parameters
> to a sp? Even though the estimated query plan looks good that does not
> mean that is what was used when ran. You can have parameter sniffing
> happening here and might be suffering from a bad query plan. But again
> this is only part of the actual code so what isolation level are you
> running in during this update? Are there other DML statements in the same
> transaction? How are you calling this code?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and returing
>> expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that
>> sounds
>> like the case. But if it is taking out that many locks it is obviously
>> not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks
>>in
>>the server. The spid is supposed to update around 1 to 2 records out of
>>90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't
>> sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>>
>|||OK that goes along with my original assumptions. For some reason it is
choosing the wrong plan at times. If you answer the rest of my questions
maybe it will help to narrow down the actual cause. But some things to
consider here. First is that you have two different WHERE clauses that may
require different plans of attack. I would create two sps, one for each of
those updates and call the appropriate one based on the parameters passed.
And if you find that the correct index for the update is always
IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
But make sure it is always the correct way to deal with the updates.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Andrew, ( I sent this earlier too but just resending it)
> Just to add one more point: The 20 million locks of mode 'U' is happening
> on
> IDX_CA_KEY5. Which is not a good index for the searching for that update
> statement.
> So, although, when I saw the estimated execution plan it was using good
> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
> environment, optimizer its using wrong index once in while causing all
> those millions locks.
> Does that make sense? If so, I am planning to apply index hint to solve
> this issuue.
> Your comments are highly appreciated.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is the
>> factor here. It has more to do with how many it needs to look at to find
>> those rows. If 2 rows get updated but it has to scan an entire index to
>> find those 2 that is not good. Is this the index it is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using in
>> the update? Are the values represented by variables or actual parameters
>> to a sp? Even though the estimated query plan looks good that does not
>> mean that is what was used when ran. You can have parameter sniffing
>> happening here and might be suffering from a bad query plan. But again
>> this is only part of the actual code so what isolation level are you
>> running in during this update? Are there other DML statements in the
>> same transaction? How are you calling this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and returing
>> expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that
>> sounds
>> like the case. But if it is taking out that many locks it is obviously
>> not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks
>>in
>>the server. The spid is supposed to update around 1 to 2 records out of
>>90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't
>> sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>>
>>
>|||Andrew,
Once again thanks for your time. I will reply to this thread once I
implemented the index hint and see if that will solve the issue or not.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
> OK that goes along with my original assumptions. For some reason it is
> choosing the wrong plan at times. If you answer the rest of my questions
> maybe it will help to narrow down the actual cause. But some things to
> consider here. First is that you have two different WHERE clauses that may
> require different plans of attack. I would create two sps, one for each of
> those updates and call the appropriate one based on the parameters passed.
> And if you find that the correct index for the update is always
> IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
> But make sure it is always the correct way to deal with the updates.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Andrew, ( I sent this earlier too but just resending it)
>> Just to add one more point: The 20 million locks of mode 'U' is happening
>> on
>> IDX_CA_KEY5. Which is not a good index for the searching for that update
>> statement.
>> So, although, when I saw the estimated execution plan it was using good
>> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
>> environment, optimizer its using wrong index once in while causing all
>> those millions locks.
>> Does that make sense? If so, I am planning to apply index hint to solve
>> this issuue.
>> Your comments are highly appreciated.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is the
>> factor here. It has more to do with how many it needs to look at to find
>> those rows. If 2 rows get updated but it has to scan an entire index to
>> find those 2 that is not good. Is this the index it is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using in
>> the update? Are the values represented by variables or actual parameters
>> to a sp? Even though the estimated query plan looks good that does not
>> mean that is what was used when ran. You can have parameter sniffing
>> happening here and might be suffering from a bad query plan. But again
>> this is only part of the actual code so what isolation level are you
>> running in during this update? Are there other DML statements in the
>> same transaction? How are you calling this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and returing
>> expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that
>> sounds
>> like the case. But if it is taking out that many locks it is
>> obviously not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks
>>in
>>the server. The spid is supposed to update around 1 to 2 records out
>>of 90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't
>> sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>>
>>
>>
>|||You may also want to try using OPTIMIZE FOR instead of and index hint. This
will allow for two situations: 1) someone renames/drops the existing index
and 2) someone builds a better index for the query.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"James" <kush@.brandes.com> wrote in message
news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> Once again thanks for your time. I will reply to this thread once I
> implemented the index hint and see if that will solve the issue or not.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK that goes along with my original assumptions. For some reason it is
>> choosing the wrong plan at times. If you answer the rest of my questions
>> maybe it will help to narrow down the actual cause. But some things to
>> consider here. First is that you have two different WHERE clauses that
>> may require different plans of attack. I would create two sps, one for
>> each of those updates and call the appropriate one based on the
>> parameters passed. And if you find that the correct index for the update
>> is always IND_CASH_ACT_SPD1 you can add an index hint to force this to be
>> the case. But make sure it is always the correct way to deal with the
>> updates.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Andrew, ( I sent this earlier too but just resending it)
>> Just to add one more point: The 20 million locks of mode 'U' is
>> happening on
>> IDX_CA_KEY5. Which is not a good index for the searching for that update
>> statement.
>> So, although, when I saw the estimated execution plan it was using good
>> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
>> environment, optimizer its using wrong index once in while causing all
>> those millions locks.
>> Does that make sense? If so, I am planning to apply index hint to solve
>> this issuue.
>> Your comments are highly appreciated.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is the
>> factor here. It has more to do with how many it needs to look at to
>> find those rows. If 2 rows get updated but it has to scan an entire
>> index to find those 2 that is not good. Is this the index it is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using in
>> the update? Are the values represented by variables or actual
>> parameters to a sp? Even though the estimated query plan looks good
>> that does not mean that is what was used when ran. You can have
>> parameter sniffing happening here and might be suffering from a bad
>> query plan. But again this is only part of the actual code so what
>> isolation level are you running in during this update? Are there other
>> DML statements in the same transaction? How are you calling this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and returing
>> expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that
>> sounds
>> like the case. But if it is taking out that many locks it is
>> obviously not
>> doing what you think. My guess would be you have this in
>> serializable mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>>I occassionally see one of the spid holding around 10-20 million
>>>locks in
>>>the server. The spid is supposed to update around 1 to 2 records out
>>>of 90
>>>million rows table and its well indexed. The spid is generated by
>>>application server and usually there are 4-5 processes trying to
>>>insert/update same table.
>>>
>>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't
>>> sql
>>> server do lock escalation when it reaches certain threashold? Is it
>>> a bug?
>>> As a result of this, I am getting error 1204, "cannot obtain lock
>>> resource
>>> at this time". I am running SQL 2000 SP4
>>>
>>> I appreicate your answer.
>>>
>>
>>
>>
>>
>|||Unfortunately he is running SQL2000 and can not go that route.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kui32a08dptc6@.corp.supernews.com...
> You may also want to try using OPTIMIZE FOR instead of and index hint.
> This will allow for two situations: 1) someone renames/drops the existing
> index and 2) someone builds a better index for the query.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "James" <kush@.brandes.com> wrote in message
> news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
>> Andrew,
>> Once again thanks for your time. I will reply to this thread once I
>> implemented the index hint and see if that will solve the issue or not.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK that goes along with my original assumptions. For some reason it is
>> choosing the wrong plan at times. If you answer the rest of my questions
>> maybe it will help to narrow down the actual cause. But some things to
>> consider here. First is that you have two different WHERE clauses that
>> may require different plans of attack. I would create two sps, one for
>> each of those updates and call the appropriate one based on the
>> parameters passed. And if you find that the correct index for the update
>> is always IND_CASH_ACT_SPD1 you can add an index hint to force this to
>> be the case. But make sure it is always the correct way to deal with the
>> updates.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Andrew, ( I sent this earlier too but just resending it)
>> Just to add one more point: The 20 million locks of mode 'U' is
>> happening on
>> IDX_CA_KEY5. Which is not a good index for the searching for that
>> update
>> statement.
>> So, although, when I saw the estimated execution plan it was using good
>> index which is IND_CASH_ACT_SPD1, Looks like in actual run on
>> production
>> environment, optimizer its using wrong index once in while causing all
>> those millions locks.
>> Does that make sense? If so, I am planning to apply index hint to solve
>> this issuue.
>> Your comments are highly appreciated.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is the
>> factor here. It has more to do with how many it needs to look at to
>> find those rows. If 2 rows get updated but it has to scan an entire
>> index to find those 2 that is not good. Is this the index it is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using
>> in the update? Are the values represented by variables or actual
>> parameters to a sp? Even though the estimated query plan looks good
>> that does not mean that is what was used when ran. You can have
>> parameter sniffing happening here and might be suffering from a bad
>> query plan. But again this is only part of the actual code so what
>> isolation level are you running in during this update? Are there
>> other DML statements in the same transaction? How are you calling
>> this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and
>> returing expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>>> If there are any other users with any shared or higher locks in that
>>> table
>>> it can not escalate to a table lock. Since you say it is busy that
>>> sounds
>>> like the case. But if it is taking out that many locks it is
>>> obviously not
>>> doing what you think. My guess would be you have this in
>>> serializable mode.
>>> Can you post the exact code for the UPDATE and the DDL for the table
>>> including indexes. What does the estimated query plan look like?
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "James" <kush@.brandes.com> wrote in message
>>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>>I occassionally see one of the spid holding around 10-20 million
>>>locks in
>>>the server. The spid is supposed to update around 1 to 2 records out
>>>of 90
>>>million rows table and its well indexed. The spid is generated by
>>>application server and usually there are 4-5 processes trying to
>>>insert/update same table.
>>>
>>> Has anyone seen a single spid holding 10-20 million locks?
>>> Shouldn't sql
>>> server do lock escalation when it reaches certain threashold? Is it
>>> a bug?
>>> As a result of this, I am getting error 1204, "cannot obtain lock
>>> resource
>>> at this time". I am running SQL 2000 SP4
>>>
>>> I appreicate your answer.
>>>
>>>
>>
>>
>>
>>
>|||Oopsie! Looks like I need to be more careful when reviewing the thread
prior to posting.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uvSzFHuMIHA.5040@.TK2MSFTNGP04.phx.gbl...
> Unfortunately he is running SQL2000 and can not go that route.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13kui32a08dptc6@.corp.supernews.com...
>> You may also want to try using OPTIMIZE FOR instead of and index hint.
>> This will allow for two situations: 1) someone renames/drops the
>> existing index and 2) someone builds a better index for the query.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
>> Andrew,
>> Once again thanks for your time. I will reply to this thread once I
>> implemented the index hint and see if that will solve the issue or not.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK that goes along with my original assumptions. For some reason it is
>> choosing the wrong plan at times. If you answer the rest of my
>> questions maybe it will help to narrow down the actual cause. But some
>> things to consider here. First is that you have two different WHERE
>> clauses that may require different plans of attack. I would create two
>> sps, one for each of those updates and call the appropriate one based
>> on the parameters passed. And if you find that the correct index for
>> the update is always IND_CASH_ACT_SPD1 you can add an index hint to
>> force this to be the case. But make sure it is always the correct way
>> to deal with the updates.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Andrew, ( I sent this earlier too but just resending it)
>> Just to add one more point: The 20 million locks of mode 'U' is
>> happening on
>> IDX_CA_KEY5. Which is not a good index for the searching for that
>> update
>> statement.
>> So, although, when I saw the estimated execution plan it was using
>> good
>> index which is IND_CASH_ACT_SPD1, Looks like in actual run on
>> production
>> environment, optimizer its using wrong index once in while causing
>> all
>> those millions locks.
>> Does that make sense? If so, I am planning to apply index hint to
>> solve this issuue.
>> Your comments are highly appreciated.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is
>> the factor here. It has more to do with how many it needs to look at
>> to find those rows. If 2 rows get updated but it has to scan an
>> entire index to find those 2 that is not good. Is this the index it
>> is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using
>> in the update? Are the values represented by variables or actual
>> parameters to a sp? Even though the estimated query plan looks good
>> that does not mean that is what was used when ran. You can have
>> parameter sniffing happening here and might be suffering from a bad
>> query plan. But again this is only part of the actual code so what
>> isolation level are you running in during this update? Are there
>> other DML statements in the same transaction? How are you calling
>> this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>>> Thanks for the reply. Update statement and DDL for the table/index
>>> is attached. Estimated query looks good, using right indexes and
>>> returing expected number of rows for update.
>>>
>>>
>>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>>> If there are any other users with any shared or higher locks in
>>> that table
>>> it can not escalate to a table lock. Since you say it is busy that
>>> sounds
>>> like the case. But if it is taking out that many locks it is
>>> obviously not
>>> doing what you think. My guess would be you have this in
>>> serializable mode.
>>> Can you post the exact code for the UPDATE and the DDL for the
>>> table
>>> including indexes. What does the estimated query plan look like?
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "James" <kush@.brandes.com> wrote in message
>>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>>I occassionally see one of the spid holding around 10-20 million
>>>locks in
>>>the server. The spid is supposed to update around 1 to 2 records
>>>out of 90
>>>million rows table and its well indexed. The spid is generated by
>>>application server and usually there are 4-5 processes trying to
>>>insert/update same table.
>>>
>>> Has anyone seen a single spid holding 10-20 million locks?
>>> Shouldn't sql
>>> server do lock escalation when it reaches certain threashold? Is
>>> it a bug?
>>> As a result of this, I am getting error 1204, "cannot obtain lock
>>> resource
>>> at this time". I am running SQL 2000 SP4
>>>
>>> I appreicate your answer.
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>
the server. The spid is supposed to update around 1 to 2 records out of 90
million rows table and its well indexed. The spid is generated by
application server and usually there are 4-5 processes trying to
insert/update same table.
Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
server do lock escalation when it reaches certain threashold? Is it a bug?
As a result of this, I am getting error 1204, "cannot obtain lock resource
at this time". I am running SQL 2000 SP4
I appreicate your answer.If there are any other users with any shared or higher locks in that table
it can not escalate to a table lock. Since you say it is busy that sounds
like the case. But if it is taking out that many locks it is obviously not
doing what you think. My guess would be you have this in serializable mode.
Can you post the exact code for the UPDATE and the DDL for the table
including indexes. What does the estimated query plan look like?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>I occassionally see one of the spid holding around 10-20 million locks in
>the server. The spid is supposed to update around 1 to 2 records out of 90
>million rows table and its well indexed. The spid is generated by
>application server and usually there are 4-5 processes trying to
>insert/update same table.
> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
> server do lock escalation when it reaches certain threashold? Is it a bug?
> As a result of this, I am getting error 1204, "cannot obtain lock resource
> at this time". I am running SQL 2000 SP4
> I appreicate your answer.
>|||Andrew,
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, May be in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, Can we put index hint on update statement so
that sql server use right index to search for those records that needs to be
updated?
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that sounds
>> like the case. But if it is taking out that many locks it is obviously
>> not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks in
>>the server. The spid is supposed to update around 1 to 2 records out of
>>90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>
>|||Well it isn't the expected number of rows that get updated that is the
factor here. It has more to do with how many it needs to look at to find
those rows. If 2 rows get updated but it has to scan an entire index to find
those 2 that is not good. Is this the index it is using?
IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
[long_short_indicator])
How many rows match those three columns for the values you are using in the
update? Are the values represented by variables or actual parameters to a
sp? Even though the estimated query plan looks good that does not mean that
is what was used when ran. You can have parameter sniffing happening here
and might be suffering from a bad query plan. But again this is only part
of the actual code so what isolation level are you running in during this
update? Are there other DML statements in the same transaction? How are
you calling this code?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that sounds
>> like the case. But if it is taking out that many locks it is obviously
>> not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks in
>>the server. The spid is supposed to update around 1 to 2 records out of
>>90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>
>|||Andrew, ( I sent this earlier too but just resending it)
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, I am planning to apply index hint to solve this
issuue.
Your comments are highly appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Well it isn't the expected number of rows that get updated that is the
> factor here. It has more to do with how many it needs to look at to find
> those rows. If 2 rows get updated but it has to scan an entire index to
> find those 2 that is not good. Is this the index it is using?
> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
> [long_short_indicator])
> How many rows match those three columns for the values you are using in
> the update? Are the values represented by variables or actual parameters
> to a sp? Even though the estimated query plan looks good that does not
> mean that is what was used when ran. You can have parameter sniffing
> happening here and might be suffering from a bad query plan. But again
> this is only part of the actual code so what isolation level are you
> running in during this update? Are there other DML statements in the same
> transaction? How are you calling this code?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and returing
>> expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that
>> sounds
>> like the case. But if it is taking out that many locks it is obviously
>> not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks
>>in
>>the server. The spid is supposed to update around 1 to 2 records out of
>>90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't
>> sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>>
>|||OK that goes along with my original assumptions. For some reason it is
choosing the wrong plan at times. If you answer the rest of my questions
maybe it will help to narrow down the actual cause. But some things to
consider here. First is that you have two different WHERE clauses that may
require different plans of attack. I would create two sps, one for each of
those updates and call the appropriate one based on the parameters passed.
And if you find that the correct index for the update is always
IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
But make sure it is always the correct way to deal with the updates.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Andrew, ( I sent this earlier too but just resending it)
> Just to add one more point: The 20 million locks of mode 'U' is happening
> on
> IDX_CA_KEY5. Which is not a good index for the searching for that update
> statement.
> So, although, when I saw the estimated execution plan it was using good
> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
> environment, optimizer its using wrong index once in while causing all
> those millions locks.
> Does that make sense? If so, I am planning to apply index hint to solve
> this issuue.
> Your comments are highly appreciated.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is the
>> factor here. It has more to do with how many it needs to look at to find
>> those rows. If 2 rows get updated but it has to scan an entire index to
>> find those 2 that is not good. Is this the index it is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using in
>> the update? Are the values represented by variables or actual parameters
>> to a sp? Even though the estimated query plan looks good that does not
>> mean that is what was used when ran. You can have parameter sniffing
>> happening here and might be suffering from a bad query plan. But again
>> this is only part of the actual code so what isolation level are you
>> running in during this update? Are there other DML statements in the
>> same transaction? How are you calling this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and returing
>> expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that
>> sounds
>> like the case. But if it is taking out that many locks it is obviously
>> not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks
>>in
>>the server. The spid is supposed to update around 1 to 2 records out of
>>90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't
>> sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>>
>>
>|||Andrew,
Once again thanks for your time. I will reply to this thread once I
implemented the index hint and see if that will solve the issue or not.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
> OK that goes along with my original assumptions. For some reason it is
> choosing the wrong plan at times. If you answer the rest of my questions
> maybe it will help to narrow down the actual cause. But some things to
> consider here. First is that you have two different WHERE clauses that may
> require different plans of attack. I would create two sps, one for each of
> those updates and call the appropriate one based on the parameters passed.
> And if you find that the correct index for the update is always
> IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
> But make sure it is always the correct way to deal with the updates.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Andrew, ( I sent this earlier too but just resending it)
>> Just to add one more point: The 20 million locks of mode 'U' is happening
>> on
>> IDX_CA_KEY5. Which is not a good index for the searching for that update
>> statement.
>> So, although, when I saw the estimated execution plan it was using good
>> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
>> environment, optimizer its using wrong index once in while causing all
>> those millions locks.
>> Does that make sense? If so, I am planning to apply index hint to solve
>> this issuue.
>> Your comments are highly appreciated.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is the
>> factor here. It has more to do with how many it needs to look at to find
>> those rows. If 2 rows get updated but it has to scan an entire index to
>> find those 2 that is not good. Is this the index it is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using in
>> the update? Are the values represented by variables or actual parameters
>> to a sp? Even though the estimated query plan looks good that does not
>> mean that is what was used when ran. You can have parameter sniffing
>> happening here and might be suffering from a bad query plan. But again
>> this is only part of the actual code so what isolation level are you
>> running in during this update? Are there other DML statements in the
>> same transaction? How are you calling this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and returing
>> expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that
>> sounds
>> like the case. But if it is taking out that many locks it is
>> obviously not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks
>>in
>>the server. The spid is supposed to update around 1 to 2 records out
>>of 90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't
>> sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>>
>>
>>
>|||You may also want to try using OPTIMIZE FOR instead of and index hint. This
will allow for two situations: 1) someone renames/drops the existing index
and 2) someone builds a better index for the query.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"James" <kush@.brandes.com> wrote in message
news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> Once again thanks for your time. I will reply to this thread once I
> implemented the index hint and see if that will solve the issue or not.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK that goes along with my original assumptions. For some reason it is
>> choosing the wrong plan at times. If you answer the rest of my questions
>> maybe it will help to narrow down the actual cause. But some things to
>> consider here. First is that you have two different WHERE clauses that
>> may require different plans of attack. I would create two sps, one for
>> each of those updates and call the appropriate one based on the
>> parameters passed. And if you find that the correct index for the update
>> is always IND_CASH_ACT_SPD1 you can add an index hint to force this to be
>> the case. But make sure it is always the correct way to deal with the
>> updates.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Andrew, ( I sent this earlier too but just resending it)
>> Just to add one more point: The 20 million locks of mode 'U' is
>> happening on
>> IDX_CA_KEY5. Which is not a good index for the searching for that update
>> statement.
>> So, although, when I saw the estimated execution plan it was using good
>> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
>> environment, optimizer its using wrong index once in while causing all
>> those millions locks.
>> Does that make sense? If so, I am planning to apply index hint to solve
>> this issuue.
>> Your comments are highly appreciated.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is the
>> factor here. It has more to do with how many it needs to look at to
>> find those rows. If 2 rows get updated but it has to scan an entire
>> index to find those 2 that is not good. Is this the index it is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using in
>> the update? Are the values represented by variables or actual
>> parameters to a sp? Even though the estimated query plan looks good
>> that does not mean that is what was used when ran. You can have
>> parameter sniffing happening here and might be suffering from a bad
>> query plan. But again this is only part of the actual code so what
>> isolation level are you running in during this update? Are there other
>> DML statements in the same transaction? How are you calling this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and returing
>> expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that
>> sounds
>> like the case. But if it is taking out that many locks it is
>> obviously not
>> doing what you think. My guess would be you have this in
>> serializable mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>>I occassionally see one of the spid holding around 10-20 million
>>>locks in
>>>the server. The spid is supposed to update around 1 to 2 records out
>>>of 90
>>>million rows table and its well indexed. The spid is generated by
>>>application server and usually there are 4-5 processes trying to
>>>insert/update same table.
>>>
>>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't
>>> sql
>>> server do lock escalation when it reaches certain threashold? Is it
>>> a bug?
>>> As a result of this, I am getting error 1204, "cannot obtain lock
>>> resource
>>> at this time". I am running SQL 2000 SP4
>>>
>>> I appreicate your answer.
>>>
>>
>>
>>
>>
>|||Unfortunately he is running SQL2000 and can not go that route.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kui32a08dptc6@.corp.supernews.com...
> You may also want to try using OPTIMIZE FOR instead of and index hint.
> This will allow for two situations: 1) someone renames/drops the existing
> index and 2) someone builds a better index for the query.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "James" <kush@.brandes.com> wrote in message
> news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
>> Andrew,
>> Once again thanks for your time. I will reply to this thread once I
>> implemented the index hint and see if that will solve the issue or not.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK that goes along with my original assumptions. For some reason it is
>> choosing the wrong plan at times. If you answer the rest of my questions
>> maybe it will help to narrow down the actual cause. But some things to
>> consider here. First is that you have two different WHERE clauses that
>> may require different plans of attack. I would create two sps, one for
>> each of those updates and call the appropriate one based on the
>> parameters passed. And if you find that the correct index for the update
>> is always IND_CASH_ACT_SPD1 you can add an index hint to force this to
>> be the case. But make sure it is always the correct way to deal with the
>> updates.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Andrew, ( I sent this earlier too but just resending it)
>> Just to add one more point: The 20 million locks of mode 'U' is
>> happening on
>> IDX_CA_KEY5. Which is not a good index for the searching for that
>> update
>> statement.
>> So, although, when I saw the estimated execution plan it was using good
>> index which is IND_CASH_ACT_SPD1, Looks like in actual run on
>> production
>> environment, optimizer its using wrong index once in while causing all
>> those millions locks.
>> Does that make sense? If so, I am planning to apply index hint to solve
>> this issuue.
>> Your comments are highly appreciated.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is the
>> factor here. It has more to do with how many it needs to look at to
>> find those rows. If 2 rows get updated but it has to scan an entire
>> index to find those 2 that is not good. Is this the index it is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using
>> in the update? Are the values represented by variables or actual
>> parameters to a sp? Even though the estimated query plan looks good
>> that does not mean that is what was used when ran. You can have
>> parameter sniffing happening here and might be suffering from a bad
>> query plan. But again this is only part of the actual code so what
>> isolation level are you running in during this update? Are there
>> other DML statements in the same transaction? How are you calling
>> this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and
>> returing expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>>> If there are any other users with any shared or higher locks in that
>>> table
>>> it can not escalate to a table lock. Since you say it is busy that
>>> sounds
>>> like the case. But if it is taking out that many locks it is
>>> obviously not
>>> doing what you think. My guess would be you have this in
>>> serializable mode.
>>> Can you post the exact code for the UPDATE and the DDL for the table
>>> including indexes. What does the estimated query plan look like?
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "James" <kush@.brandes.com> wrote in message
>>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>>I occassionally see one of the spid holding around 10-20 million
>>>locks in
>>>the server. The spid is supposed to update around 1 to 2 records out
>>>of 90
>>>million rows table and its well indexed. The spid is generated by
>>>application server and usually there are 4-5 processes trying to
>>>insert/update same table.
>>>
>>> Has anyone seen a single spid holding 10-20 million locks?
>>> Shouldn't sql
>>> server do lock escalation when it reaches certain threashold? Is it
>>> a bug?
>>> As a result of this, I am getting error 1204, "cannot obtain lock
>>> resource
>>> at this time". I am running SQL 2000 SP4
>>>
>>> I appreicate your answer.
>>>
>>>
>>
>>
>>
>>
>|||Oopsie! Looks like I need to be more careful when reviewing the thread
prior to posting.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uvSzFHuMIHA.5040@.TK2MSFTNGP04.phx.gbl...
> Unfortunately he is running SQL2000 and can not go that route.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13kui32a08dptc6@.corp.supernews.com...
>> You may also want to try using OPTIMIZE FOR instead of and index hint.
>> This will allow for two situations: 1) someone renames/drops the
>> existing index and 2) someone builds a better index for the query.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
>> Andrew,
>> Once again thanks for your time. I will reply to this thread once I
>> implemented the index hint and see if that will solve the issue or not.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK that goes along with my original assumptions. For some reason it is
>> choosing the wrong plan at times. If you answer the rest of my
>> questions maybe it will help to narrow down the actual cause. But some
>> things to consider here. First is that you have two different WHERE
>> clauses that may require different plans of attack. I would create two
>> sps, one for each of those updates and call the appropriate one based
>> on the parameters passed. And if you find that the correct index for
>> the update is always IND_CASH_ACT_SPD1 you can add an index hint to
>> force this to be the case. But make sure it is always the correct way
>> to deal with the updates.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Andrew, ( I sent this earlier too but just resending it)
>> Just to add one more point: The 20 million locks of mode 'U' is
>> happening on
>> IDX_CA_KEY5. Which is not a good index for the searching for that
>> update
>> statement.
>> So, although, when I saw the estimated execution plan it was using
>> good
>> index which is IND_CASH_ACT_SPD1, Looks like in actual run on
>> production
>> environment, optimizer its using wrong index once in while causing
>> all
>> those millions locks.
>> Does that make sense? If so, I am planning to apply index hint to
>> solve this issuue.
>> Your comments are highly appreciated.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is
>> the factor here. It has more to do with how many it needs to look at
>> to find those rows. If 2 rows get updated but it has to scan an
>> entire index to find those 2 that is not good. Is this the index it
>> is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using
>> in the update? Are the values represented by variables or actual
>> parameters to a sp? Even though the estimated query plan looks good
>> that does not mean that is what was used when ran. You can have
>> parameter sniffing happening here and might be suffering from a bad
>> query plan. But again this is only part of the actual code so what
>> isolation level are you running in during this update? Are there
>> other DML statements in the same transaction? How are you calling
>> this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>>> Thanks for the reply. Update statement and DDL for the table/index
>>> is attached. Estimated query looks good, using right indexes and
>>> returing expected number of rows for update.
>>>
>>>
>>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>>> If there are any other users with any shared or higher locks in
>>> that table
>>> it can not escalate to a table lock. Since you say it is busy that
>>> sounds
>>> like the case. But if it is taking out that many locks it is
>>> obviously not
>>> doing what you think. My guess would be you have this in
>>> serializable mode.
>>> Can you post the exact code for the UPDATE and the DDL for the
>>> table
>>> including indexes. What does the estimated query plan look like?
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "James" <kush@.brandes.com> wrote in message
>>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>>I occassionally see one of the spid holding around 10-20 million
>>>locks in
>>>the server. The spid is supposed to update around 1 to 2 records
>>>out of 90
>>>million rows table and its well indexed. The spid is generated by
>>>application server and usually there are 4-5 processes trying to
>>>insert/update same table.
>>>
>>> Has anyone seen a single spid holding 10-20 million locks?
>>> Shouldn't sql
>>> server do lock escalation when it reaches certain threashold? Is
>>> it a bug?
>>> As a result of this, I am getting error 1204, "cannot obtain lock
>>> resource
>>> at this time". I am running SQL 2000 SP4
>>>
>>> I appreicate your answer.
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>
Wednesday, March 21, 2012
location of rdl files on server
Hi there ,
I am working on SSRS and I just wanted to know where
are the .rdl file stored on the server.I came along this problem as i
was searching a way to make the report content dynamic rather make the
report content customizable .I just wanted to know is there a way to
change rdl files to make them customizable.
thanks.AFAIK, the files are not stored anywhere. When you upload a report, the definition (contents of the
RDL file) is inserted into one or more tables in the report database. I'm not RS expert, though, so
you might want to verify this in an RS forum.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"unixbf" <bondfranco@.gmail.com> wrote in message
news:d2323315-9f7f-44a1-9d32-49795ad3efc2@.v46g2000hsv.googlegroups.com...
> Hi there ,
> I am working on SSRS and I just wanted to know where
> are the .rdl file stored on the server.I came along this problem as i
> was searching a way to make the report content dynamic rather make the
> report content customizable .I just wanted to know is there a way to
> change rdl files to make them customizable.
> thanks.|||Tibor Karaszi wrote:
> AFAIK, the files are not stored anywhere. When you upload a report, the definition (contents of the
> RDL file) is inserted into one or more tables in the report database. I'm not RS expert, though, so
> you might want to verify this in an RS forum.
>
true!
I am working on SSRS and I just wanted to know where
are the .rdl file stored on the server.I came along this problem as i
was searching a way to make the report content dynamic rather make the
report content customizable .I just wanted to know is there a way to
change rdl files to make them customizable.
thanks.AFAIK, the files are not stored anywhere. When you upload a report, the definition (contents of the
RDL file) is inserted into one or more tables in the report database. I'm not RS expert, though, so
you might want to verify this in an RS forum.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"unixbf" <bondfranco@.gmail.com> wrote in message
news:d2323315-9f7f-44a1-9d32-49795ad3efc2@.v46g2000hsv.googlegroups.com...
> Hi there ,
> I am working on SSRS and I just wanted to know where
> are the .rdl file stored on the server.I came along this problem as i
> was searching a way to make the report content dynamic rather make the
> report content customizable .I just wanted to know is there a way to
> change rdl files to make them customizable.
> thanks.|||Tibor Karaszi wrote:
> AFAIK, the files are not stored anywhere. When you upload a report, the definition (contents of the
> RDL file) is inserted into one or more tables in the report database. I'm not RS expert, though, so
> you might want to verify this in an RS forum.
>
true!
location of rdl files on report server
Hi there ,
I am working on SSRS and I just wanted to know where
are the .rdl file stored on the server.I came along this problem as i
was searching a way to make the report content dynamic rather make the
report content customizable .I just wanted to know is there a way to
change rdl files to make them customizable.
thanks.RDL files are not stored as files on the server. They are stored in the
database. Essentially you have to redeploy the rdl file which you can do by
using webservices. However, you cannot do this for a specific user.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"unixbf" <bondfranco@.gmail.com> wrote in message
news:448b5c16-8146-48b0-a412-8e38d873955d@.v67g2000hse.googlegroups.com...
> Hi there ,
> I am working on SSRS and I just wanted to know where
> are the .rdl file stored on the server.I came along this problem as i
> was searching a way to make the report content dynamic rather make the
> report content customizable .I just wanted to know is there a way to
> change rdl files to make them customizable.
> thanks.
I am working on SSRS and I just wanted to know where
are the .rdl file stored on the server.I came along this problem as i
was searching a way to make the report content dynamic rather make the
report content customizable .I just wanted to know is there a way to
change rdl files to make them customizable.
thanks.RDL files are not stored as files on the server. They are stored in the
database. Essentially you have to redeploy the rdl file which you can do by
using webservices. However, you cannot do this for a specific user.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"unixbf" <bondfranco@.gmail.com> wrote in message
news:448b5c16-8146-48b0-a412-8e38d873955d@.v67g2000hse.googlegroups.com...
> Hi there ,
> I am working on SSRS and I just wanted to know where
> are the .rdl file stored on the server.I came along this problem as i
> was searching a way to make the report content dynamic rather make the
> report content customizable .I just wanted to know is there a way to
> change rdl files to make them customizable.
> thanks.
Monday, March 19, 2012
LocalReport: Object-in-Object as DataSource not working?
Hi,
I have a Report (VB.NET 2005), and I'm using Objects as DataSource.
I have the object objCompany, which contains a public property MyAdress
(instance of objAdress). objAdress has a propert Street.
When I add objCompany as a DataSource, I want to be able to show the
Street-property of MyAdress. So I do a drag-and-drop of that property from
the Data Sources - Window to the Report. Everything seems fine: I get a
textbox on my report with Value "=First(Fields!Street.Value,
"MyApplication_objCompany")".
But when I run the report, it doesn't show any value in it...
Does anybody knows why this happens? I somehow think because of the fact
that Street isn't directly on MyApplication_objCompany, but when I change it
into MyApplication_objCompany_MyAdress is complains about the fact that it
doesn't exist...
How should I do this?
Thanks a lot in advance,
PieterI found the solution here for nested objects and reporting services
localreport in the reportviewer:
http://www.gotreportviewer.com/objectdatasources/index.html
Apparently I have to do something like "=First(Fields!MyAdress.Value.Street,
"MyApplication_objCompany")".
It gave me these 2 errors:
The Value expression for the textbox 'Adresse' refers to the field
'ReportAdresse'.
Report item expressions can only refer to fields within the current data set
scope or, if inside an aggregate, the specified data set scope.
The Value expression for the textbox 'textbox19' has a scope parameter that
is not valid for an aggregate function.
The scope parameter must be set to a string constant that is equal to either
the name of a containing group, the name of a containing data region, or the
name of a data set.
But sddenly it started to work...
strange... :-/
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:u64%23GAkiGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a Report (VB.NET 2005), and I'm using Objects as DataSource.
> I have the object objCompany, which contains a public property MyAdress
> (instance of objAdress). objAdress has a propert Street.
> When I add objCompany as a DataSource, I want to be able to show the
> Street-property of MyAdress. So I do a drag-and-drop of that property from
> the Data Sources - Window to the Report. Everything seems fine: I get a
> textbox on my report with Value "=First(Fields!Street.Value,
> "MyApplication_objCompany")".
> But when I run the report, it doesn't show any value in it...
> Does anybody knows why this happens? I somehow think because of the fact
> that Street isn't directly on MyApplication_objCompany, but when I change
> it into MyApplication_objCompany_MyAdress is complains about the fact that
> it doesn't exist...
> How should I do this?
> Thanks a lot in advance,
> Pieter
>
I have a Report (VB.NET 2005), and I'm using Objects as DataSource.
I have the object objCompany, which contains a public property MyAdress
(instance of objAdress). objAdress has a propert Street.
When I add objCompany as a DataSource, I want to be able to show the
Street-property of MyAdress. So I do a drag-and-drop of that property from
the Data Sources - Window to the Report. Everything seems fine: I get a
textbox on my report with Value "=First(Fields!Street.Value,
"MyApplication_objCompany")".
But when I run the report, it doesn't show any value in it...
Does anybody knows why this happens? I somehow think because of the fact
that Street isn't directly on MyApplication_objCompany, but when I change it
into MyApplication_objCompany_MyAdress is complains about the fact that it
doesn't exist...
How should I do this?
Thanks a lot in advance,
PieterI found the solution here for nested objects and reporting services
localreport in the reportviewer:
http://www.gotreportviewer.com/objectdatasources/index.html
Apparently I have to do something like "=First(Fields!MyAdress.Value.Street,
"MyApplication_objCompany")".
It gave me these 2 errors:
The Value expression for the textbox 'Adresse' refers to the field
'ReportAdresse'.
Report item expressions can only refer to fields within the current data set
scope or, if inside an aggregate, the specified data set scope.
The Value expression for the textbox 'textbox19' has a scope parameter that
is not valid for an aggregate function.
The scope parameter must be set to a string constant that is equal to either
the name of a containing group, the name of a containing data region, or the
name of a data set.
But sddenly it started to work...
strange... :-/
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:u64%23GAkiGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a Report (VB.NET 2005), and I'm using Objects as DataSource.
> I have the object objCompany, which contains a public property MyAdress
> (instance of objAdress). objAdress has a propert Street.
> When I add objCompany as a DataSource, I want to be able to show the
> Street-property of MyAdress. So I do a drag-and-drop of that property from
> the Data Sources - Window to the Report. Everything seems fine: I get a
> textbox on my report with Value "=First(Fields!Street.Value,
> "MyApplication_objCompany")".
> But when I run the report, it doesn't show any value in it...
> Does anybody knows why this happens? I somehow think because of the fact
> that Street isn't directly on MyApplication_objCompany, but when I change
> it into MyApplication_objCompany_MyAdress is complains about the fact that
> it doesn't exist...
> How should I do this?
> Thanks a lot in advance,
> Pieter
>
Monday, March 12, 2012
Localizing Labels
Hi,
I am working on localizing SSRS reports and wanted to store the text for the labels in the database for different languages. The Language id would be passed by the user as a parameter. Based on this Language ID, the required data for the labels would be fetched and stored in a dataset in the custom code. This dataset would then be searched for relevant LabelIDs and the label caption in the particular language would be returned. The dataset would be a shared variable so it would get initialized the first time. For initializing the dataset I would need the datasource information, which would either be stored in the Shared Data source or embedded in the Report.
Can I refer to the shared data source being used by the current report in the Custom Code Window ? Or can I retrieve the data source information embedded in the report and use it in the Custom Code? If so, how ?Thanks In Advance.
Ashish
Hi Guys,
Does any one know as to how can we access the data source connection information from Custom Code ?
I need that information to populate a custom dataset created in the Custom Code.
Please help.
TIA.
Ashish
Localization.Brain Storming.
Hello,
I am working on a Blog and a Documents systems.
What I need is:
1. Each blog can have various language versions.
2. Each document can have various language versions.
I have been thinking about this and I end up with two approaches:
1. Use a structure where all tables depend on a localized table:
BLOGS
|-- BlogsLocalized
|-- BlogsPosts
|-- BlogsRatings
|-- BlogsComments
2. Use a structure where each table has a localized version
BLOGS
|-- BlogsLocalized
|-- BlogsPosts
|-- BlogsPostsLocalized
|-- BlogsComments
|-- BlogsCommentsLocalized
3. Create a simpler, without localization, in SQL and in my web sites have different versions for each language.
The same approach is under thinking for DocumentsTables.
Could someone give me some advice?
I have been looking in internet but until no I couldn't find anything really useful.
Thanks,
Miguel
Hi Miguel,
I would suggest the 3rd way.
In the database, you only store a simple version of the blog contents. But in your website, you can have serveral resource files that contains all the languages for the web site.
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!
Localization of reports
Hi,
We are currently working on a project where we need to deploy some reports
(Reporting Services 2005) in a multi language environment, but we struggling
to find out how to localize reports in reporting services.
1) How do I localize report texts? I can see that there's language
property and a ValueLocId property? Should I use these and how?
2) How do I localize report parameter prompts?
3) How do I localize reportname and description? For the description I
can see that there's a DescriptionLocId?
Are there any good resources on how to localize reports in Reporting
Services 2005?
Kind Regards
HenrikDuplicate, please se my other post.
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:u%23H8N9x5GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We are currently working on a project where we need to deploy some reports
> (Reporting Services 2005) in a multi language environment, but we
> struggling
> to find out how to localize reports in reporting services.
> 1) How do I localize report texts? I can see that there's language
> property and a ValueLocId property? Should I use these and how?
> 2) How do I localize report parameter prompts?
> 3) How do I localize reportname and description? For the description
> I
> can see that there's a DescriptionLocId?
> Are there any good resources on how to localize reports in Reporting
> Services 2005?
> Kind Regards
> Henrik
>
We are currently working on a project where we need to deploy some reports
(Reporting Services 2005) in a multi language environment, but we struggling
to find out how to localize reports in reporting services.
1) How do I localize report texts? I can see that there's language
property and a ValueLocId property? Should I use these and how?
2) How do I localize report parameter prompts?
3) How do I localize reportname and description? For the description I
can see that there's a DescriptionLocId?
Are there any good resources on how to localize reports in Reporting
Services 2005?
Kind Regards
HenrikDuplicate, please se my other post.
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:u%23H8N9x5GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We are currently working on a project where we need to deploy some reports
> (Reporting Services 2005) in a multi language environment, but we
> struggling
> to find out how to localize reports in reporting services.
> 1) How do I localize report texts? I can see that there's language
> property and a ValueLocId property? Should I use these and how?
> 2) How do I localize report parameter prompts?
> 3) How do I localize reportname and description? For the description
> I
> can see that there's a DescriptionLocId?
> Are there any good resources on how to localize reports in Reporting
> Services 2005?
> Kind Regards
> Henrik
>
Localization
Hi,
We are currently working on a project where we need to deploy some reports
(Reporting Services 2005) in a multi language environment, but we struggling
to find out how to localize reports in reporting services.
1) How do I localize report texts? I can see that there's language
property and a ValueLocId property? Should I use these and how?
2) How do I localize report parameter prompts?
3) How do I localize reportname and description? For the description I
can see that there's a DescriptionLocId?
Are there any good resources on how to localize reports in Reporting
Services 2005?
Kind Regards
HenrikHello Henrik,
As for the Reporting Service 2005, it does support some localization
features, they include:
1)the localization of the built-in UI components, such as SSRS's
htmlviewer, report designer
2) some simple localization on the SSRS report's data
For 1), the SSRS has done the work for us already, for example, when we
visit the html report, the UI elements on the htmlviewer(button or other UI
element's text) will render the localized representation according to
client-side browser's user-language setting.
For 2), if we want to do some simple localization on the static data/text
displayed on our report, we can dynamically format them according to the
"User!Language" parameter in our report expression. Or you can even build
custom assembly that has custom code logic to generate localizaed text(from
net resource ) accordin to this parameter.
You can find all the localization support of SSRS 2005 in the BOL:
#International Considerations for Reporting Services
http://msdn2.microsoft.com/en-us/library/ms156493.aspx
Therefore, for your scenario and the questions you mentioned, my
understanding is:
1) How do I localize report texts? I can see that there's language
property and a ValueLocId property? Should I use these and how?
========================================These properties is mainly used to specify a fixed culture/locale for
text/data formatting. Our report expression's output will be affected by
these setting.
I'm wondering whether you also want to localize the data which will be
displayed on report(retrieved from datasource). If this is the case, I'm
afraid those settings can not help on this because the data is already
retrieved by data processing engine before rendering, and rendering engine
can not translate text on the fly. For report which has data dedicated to
different languages/cultures, it is recommended that we build multiple
reports for each culture/language respectively.
2) How do I localize report parameter prompts?
========================================I think the report parameter prompts is a part of the built-in report
htmlviewer which will render localized UI according to client browser's
language setting.
3) How do I localize reportname and description? For the description I
can see that there's a DescriptionLocId?
=========================================I think such LOCID is also mainly used for some date/time number formatting
, and can not help on text/data localized formatting. For displaying
multiple language specific reports, I would suggest create multiple reports
for each language(use the corresponding datasource from database) as
mentioned in #1.
Please feel freee to let me know if you have any questions or other
consideration on this.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Henrik,
How are you doing on this issue? I've just discussed with some other
engineers from reporting service team and they have help confirm that the
"LocId" of reportItem or the report is still an unsupported feature that
teams the SSRS runtime which generate report will ignore such properties on
each reportItem.
The only usage of this LocID property is that we can use this property to
do some static localization related transformation on the RDL file. For
example, we can use XSLT to transform the RDL into another RDL xml form and
use the "LocID" to reference certain elements in the RDL when performing
XSLT transforming.
Currently , for SSRS report localization, I've mentioned the most common
approaches in my last reply. Please feel free to let me know if you have
any further questions on this.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi all,
I'm working with local reports (rdlc) and what I'm trying to do is to
localize the static text (column headers), not displayed data.
I have my column headers in textboxes which have the 'ValueLocID'
property, but I can't figure out how to make it work.
Is it possible to accomplish what I want with a single rdlc file? (not
as many as languages)
Could somebody point me out to the right direction?
TIA,
Sebastian
On Oct 9, 7:05 am, stch...@.online.microsoft.com (Steven Cheng[MSFT])
wrote:
> Hello Henrik,
> How are you doing on this issue? I've just discussed with some other
> engineers from reporting service team and they have help confirm that the
> "LocId" of reportItem or the report is still an unsupported feature that
> teams the SSRS runtime which generate report will ignore such properties on
> each reportItem.
> The only usage of this LocID property is that we can use this property to
> do some static localization related transformation on the RDL file. For
> example, we can use XSLT to transform the RDL into another RDL xml form and
> use the "LocID" to reference certain elements in the RDL when performing
> XSLT transforming.
> Currently , for SSRS report localization, I've mentioned the most common
> approaches in my last reply. Please feel free to let me know if you have
> any further questions on this.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
> This posting is provided "AS IS" with no warranties, and confers no rights.
We are currently working on a project where we need to deploy some reports
(Reporting Services 2005) in a multi language environment, but we struggling
to find out how to localize reports in reporting services.
1) How do I localize report texts? I can see that there's language
property and a ValueLocId property? Should I use these and how?
2) How do I localize report parameter prompts?
3) How do I localize reportname and description? For the description I
can see that there's a DescriptionLocId?
Are there any good resources on how to localize reports in Reporting
Services 2005?
Kind Regards
HenrikHello Henrik,
As for the Reporting Service 2005, it does support some localization
features, they include:
1)the localization of the built-in UI components, such as SSRS's
htmlviewer, report designer
2) some simple localization on the SSRS report's data
For 1), the SSRS has done the work for us already, for example, when we
visit the html report, the UI elements on the htmlviewer(button or other UI
element's text) will render the localized representation according to
client-side browser's user-language setting.
For 2), if we want to do some simple localization on the static data/text
displayed on our report, we can dynamically format them according to the
"User!Language" parameter in our report expression. Or you can even build
custom assembly that has custom code logic to generate localizaed text(from
net resource ) accordin to this parameter.
You can find all the localization support of SSRS 2005 in the BOL:
#International Considerations for Reporting Services
http://msdn2.microsoft.com/en-us/library/ms156493.aspx
Therefore, for your scenario and the questions you mentioned, my
understanding is:
1) How do I localize report texts? I can see that there's language
property and a ValueLocId property? Should I use these and how?
========================================These properties is mainly used to specify a fixed culture/locale for
text/data formatting. Our report expression's output will be affected by
these setting.
I'm wondering whether you also want to localize the data which will be
displayed on report(retrieved from datasource). If this is the case, I'm
afraid those settings can not help on this because the data is already
retrieved by data processing engine before rendering, and rendering engine
can not translate text on the fly. For report which has data dedicated to
different languages/cultures, it is recommended that we build multiple
reports for each culture/language respectively.
2) How do I localize report parameter prompts?
========================================I think the report parameter prompts is a part of the built-in report
htmlviewer which will render localized UI according to client browser's
language setting.
3) How do I localize reportname and description? For the description I
can see that there's a DescriptionLocId?
=========================================I think such LOCID is also mainly used for some date/time number formatting
, and can not help on text/data localized formatting. For displaying
multiple language specific reports, I would suggest create multiple reports
for each language(use the corresponding datasource from database) as
mentioned in #1.
Please feel freee to let me know if you have any questions or other
consideration on this.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Henrik,
How are you doing on this issue? I've just discussed with some other
engineers from reporting service team and they have help confirm that the
"LocId" of reportItem or the report is still an unsupported feature that
teams the SSRS runtime which generate report will ignore such properties on
each reportItem.
The only usage of this LocID property is that we can use this property to
do some static localization related transformation on the RDL file. For
example, we can use XSLT to transform the RDL into another RDL xml form and
use the "LocID" to reference certain elements in the RDL when performing
XSLT transforming.
Currently , for SSRS report localization, I've mentioned the most common
approaches in my last reply. Please feel free to let me know if you have
any further questions on this.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi all,
I'm working with local reports (rdlc) and what I'm trying to do is to
localize the static text (column headers), not displayed data.
I have my column headers in textboxes which have the 'ValueLocID'
property, but I can't figure out how to make it work.
Is it possible to accomplish what I want with a single rdlc file? (not
as many as languages)
Could somebody point me out to the right direction?
TIA,
Sebastian
On Oct 9, 7:05 am, stch...@.online.microsoft.com (Steven Cheng[MSFT])
wrote:
> Hello Henrik,
> How are you doing on this issue? I've just discussed with some other
> engineers from reporting service team and they have help confirm that the
> "LocId" of reportItem or the report is still an unsupported feature that
> teams the SSRS runtime which generate report will ignore such properties on
> each reportItem.
> The only usage of this LocID property is that we can use this property to
> do some static localization related transformation on the RDL file. For
> example, we can use XSLT to transform the RDL into another RDL xml form and
> use the "LocID" to reference certain elements in the RDL when performing
> XSLT transforming.
> Currently , for SSRS report localization, I've mentioned the most common
> approaches in my last reply. Please feel free to let me know if you have
> any further questions on this.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
> This posting is provided "AS IS" with no warranties, and confers no rights.
Localizable Data in a SQL Database
Hello all,
I'm working on an ASP.NET with a SQL server for database. Some of the tables, for example, contain information such as different types of Fabrics (silk, cotton, etc..) . I'd like to have this table localizable (English and French for instance).
Is this possible ? Is there an equivalent of resource files in SQL server ?
Or do I have to do this manually ? (have 2 separate fields in the table for those 2 locales)
Friday, March 9, 2012
LOCAL USER vs GLOBAL USER set in agent service
My replication is not working. I was able to create a transactional replication successfully. However, when i tried starting the agent on the snapshot, it wouldn't work. I figure that it was because my agent service login is different from that of the subscriber. my publisher and distributor is on local user whereas my subsciber sql services use our global login.
Thank you in advance. Good day!
Can you post the error message you're seeing while trying to start snapshot agent job? From that we can confirm whether the issue you're seeing is due to agent account setup. Sometimes the issue may be caused by local user doesn't have permission on network resources.
Thanks,
Zhiqiang Feng
|||
mongol wrote:
My replication is not working. I was able to create a transactional replication successfully. However, when i tried starting the agent on the snapshot, it wouldn't work. I figure that it was because my agent service login is different from that of the subscriber. my publisher and distributor is on local user whereas my subsciber sql services use our global login.
Thank you in advance. Good day!
hi mongol
be sure the the login used by the service has an NTFS permission in the
replication shared folder
and the services has also write permission to the replication target database
local sql to server sql
I am working on a project that requires pc's on a production line to run a
product testing application. On each pc I would like to have a local sql
database that will have three simple tables. One for product testing
information, one for test results and one for error descriptions. I would
like to link these tables to a server based sql server. In the past, I have
accomplished the same function using Microsoft Access, by creating link
tables to the main sql server, and it has worked quite well. Can I do this
using a local sql server? I've been reading about Sql Server Compact and Sql
Server Express, but I'm not sure which would be better, or if either has the
capability to perform as I need them to. I'd appreciate any help or advice
on this.
Thanks.
Install SQL Server Express on the selected Client computers. And create
Linked Server in the Primary SQL Server to each, computer.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"jfmoyn" wrote:
> I am working on a project that requires pc's on a production line to run a
> product testing application. On each pc I would like to have a local sql
> database that will have three simple tables. One for product testing
> information, one for test results and one for error descriptions. I would
> like to link these tables to a server based sql server. In the past, I have
> accomplished the same function using Microsoft Access, by creating link
> tables to the main sql server, and it has worked quite well. Can I do this
> using a local sql server? I've been reading about Sql Server Compact and Sql
> Server Express, but I'm not sure which would be better, or if either has the
> capability to perform as I need them to. I'd appreciate any help or advice
> on this.
> Thanks.
|||Thanks. I appreciate your help.
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> Install SQL Server Express on the selected Client computers. And create
> Linked Server in the Primary SQL Server to each, computer.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "jfmoyn" wrote:
product testing application. On each pc I would like to have a local sql
database that will have three simple tables. One for product testing
information, one for test results and one for error descriptions. I would
like to link these tables to a server based sql server. In the past, I have
accomplished the same function using Microsoft Access, by creating link
tables to the main sql server, and it has worked quite well. Can I do this
using a local sql server? I've been reading about Sql Server Compact and Sql
Server Express, but I'm not sure which would be better, or if either has the
capability to perform as I need them to. I'd appreciate any help or advice
on this.
Thanks.
Install SQL Server Express on the selected Client computers. And create
Linked Server in the Primary SQL Server to each, computer.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"jfmoyn" wrote:
> I am working on a project that requires pc's on a production line to run a
> product testing application. On each pc I would like to have a local sql
> database that will have three simple tables. One for product testing
> information, one for test results and one for error descriptions. I would
> like to link these tables to a server based sql server. In the past, I have
> accomplished the same function using Microsoft Access, by creating link
> tables to the main sql server, and it has worked quite well. Can I do this
> using a local sql server? I've been reading about Sql Server Compact and Sql
> Server Express, but I'm not sure which would be better, or if either has the
> capability to perform as I need them to. I'd appreciate any help or advice
> on this.
> Thanks.
|||Thanks. I appreciate your help.
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> Install SQL Server Express on the selected Client computers. And create
> Linked Server in the Primary SQL Server to each, computer.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "jfmoyn" wrote:
local sql to server sql
I am working on a project that requires pc's on a production line to run a
product testing application. On each pc I would like to have a local sql
database that will have three simple tables. One for product testing
information, one for test results and one for error descriptions. I would
like to link these tables to a server based sql server. In the past, I have
accomplished the same function using Microsoft Access, by creating link
tables to the main sql server, and it has worked quite well. Can I do this
using a local sql server? I've been reading about Sql Server Compact and Sq
l
Server Express, but I'm not sure which would be better, or if either has the
capability to perform as I need them to. I'd appreciate any help or advice
on this.
Thanks.Install SQL Server Express on the selected Client computers. And create
Linked Server in the Primary SQL Server to each, computer.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"jfmoyn" wrote:
> I am working on a project that requires pc's on a production line to run a
> product testing application. On each pc I would like to have a local sql
> database that will have three simple tables. One for product testing
> information, one for test results and one for error descriptions. I would
> like to link these tables to a server based sql server. In the past, I ha
ve
> accomplished the same function using Microsoft Access, by creating link
> tables to the main sql server, and it has worked quite well. Can I do thi
s
> using a local sql server? I've been reading about Sql Server Compact and
Sql
> Server Express, but I'm not sure which would be better, or if either has t
he
> capability to perform as I need them to. I'd appreciate any help or advic
e
> on this.
> Thanks.|||Thanks. I appreciate your help.
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> Install SQL Server Express on the selected Client computers. And create
> Linked Server in the Primary SQL Server to each, computer.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "jfmoyn" wrote:
>
product testing application. On each pc I would like to have a local sql
database that will have three simple tables. One for product testing
information, one for test results and one for error descriptions. I would
like to link these tables to a server based sql server. In the past, I have
accomplished the same function using Microsoft Access, by creating link
tables to the main sql server, and it has worked quite well. Can I do this
using a local sql server? I've been reading about Sql Server Compact and Sq
l
Server Express, but I'm not sure which would be better, or if either has the
capability to perform as I need them to. I'd appreciate any help or advice
on this.
Thanks.Install SQL Server Express on the selected Client computers. And create
Linked Server in the Primary SQL Server to each, computer.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"jfmoyn" wrote:
> I am working on a project that requires pc's on a production line to run a
> product testing application. On each pc I would like to have a local sql
> database that will have three simple tables. One for product testing
> information, one for test results and one for error descriptions. I would
> like to link these tables to a server based sql server. In the past, I ha
ve
> accomplished the same function using Microsoft Access, by creating link
> tables to the main sql server, and it has worked quite well. Can I do thi
s
> using a local sql server? I've been reading about Sql Server Compact and
Sql
> Server Express, but I'm not sure which would be better, or if either has t
he
> capability to perform as I need them to. I'd appreciate any help or advic
e
> on this.
> Thanks.|||Thanks. I appreciate your help.
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> Install SQL Server Express on the selected Client computers. And create
> Linked Server in the Primary SQL Server to each, computer.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "jfmoyn" wrote:
>
Subscribe to:
Posts (Atom)