Lock on a table

Question

Lock on a table

Krasavita

SSC-Insane

Points: 23138
More actions
March 29, 2011 at 1:32 pm

#247930

I have a lock on a table by running stored procedure. I ran an exution plan
it showing 61% on clustered index updated
How do I know which is a problem and how to fix it?
Thank you

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Please post the execution plan.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

CirquedeSQLeil (3/29/2011)
Please post the execution plan.

How

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Read the first article in my signature (By Gail Shaw).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

Or watch this video on how to do it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Query and table and index definitions as well please

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Krasavita SSC-Insane Points: 23138 More actions · Answer 6

Krasavita

SSC-Insane

Points: 23138

March 30, 2011 at 6:30 am

#1305391

Guys, I loaded

Thank you so much

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 7

Krasavita (3/30/2011)
Guys, I loaded
Thank you so much

I don't think that uploaded correctly. It's only 399 bytes.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

It uploaded correctly, but it's a plan for nothing.

<?xml version="1.0" encoding="utf-16"?>

<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.4053.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

<BatchSequence>

<Batch>

<Statements>

<StmtSimple />

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Krasavita SSC-Insane Points: 23138 More actions · Answer 9

Krasavita

SSC-Insane

Points: 23138

March 30, 2011 at 7:05 am

#1305413

Sorry, is it better now?

Krasavita SSC-Insane Points: 23138 More actions · Answer 10

Can some please help me this is production server.

Thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

If it's critically urgent, hire a consultant. Please remember that we're all unpaid volunteers here, we all have our own jobs, our own problems.

Also, you haven't posted the query, table and index definitions as asked.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 12

Your UPDATE statement doesn't make sense to me. You are updating a table, NED_Employee, based on data from two other tables, but you have not specified how NED_Employee relates (joins) to those tables.

If you are using SQL Server 2008, you should look at the MERGE statement instead.

UPDATE dbo.NED_Employee

SET

Department_ID=d.Department_ID

FROM [DB1].MIT.dbo.National_Employees ne, dbo.ADP_Department d

WHERE ne.Office_Location=d.Office_Id

AND d.Effective_Status='A'

AND ne.Address_Book_ID=@NED_Emp_ID

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 13

You have a lot going on under the covers here. Remote queries are likely to cause issues when you're trying to join data. This query:

UPDATE dbo.NED_Employee

SET

Department_ID=d.Department_ID

FROM [DB1].MIT.dbo.National_Employees ne, dbo.ADP_Department d

WHERE ne.Office_Location=d.Office_Id

AND d.Effective_Status='A'

AND ne.Address_Book_ID=@NED_Emp_ID

Doesn't have a mechanism for joining all the data together. How do the two tables relate to NED_Employee? You have to define that relationship. That's why you're getting the missing predicate alert. Also, I'd start learning standard ANSI join syntax, ...FROM TableX JOIN TableY ON...

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Krasavita SSC-Insane Points: 23138 More actions · Answer 14

Krasavita

SSC-Insane

Points: 23138

March 30, 2011 at 8:19 am

#1305462

I am using SQL server 2005