CPU and Locking - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

CPU and Locking

Author

Topic

rikleo2001
Posting Yak Master

185 Posts

Posted - 2010-10-05 : 06:19:14

Hi

Here are stats for my server, looks like Shared lock and Update is causing High CPU usage and then performance issues for queries.

LCK_M_S 24195.27 32.40 32.40
LCK_M_U 23158.11 31.01 63.40
OLEDB 7403.30 9.91 73.32
BACKUPIO 6022.98 8.06 81.38
SOS_SCHEDULER_YIELD 4893.22 6.55 87.93
BACKUPTHREAD 4135.91 5.54 93.47
CXPACKET 2757.83 3.69 97.16

I have no clue what would be the best action to deal with the situation.. Shall I update all select queries with NOLOCK , will this solve the problem?

I am running SQL Server 2005 on VMWARE and recently upgraded from sQL Server2000. These queries works perfectly fine there.

I would appreciate your response.

Thanks

SKR

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-05 : 06:31:02

quote:

I have no clue what would be the best action to deal with the situation.. Shall I update all select queries with NOLOCK , will this solve the problem?

NO. NOLOCK is dangerous -- you can end up reading the same information multiple times or not at all depending on internal workings of the database engine. NOLOCK is not READ UNCOMMITTED.

What you need to do is to work out which queries are causing the locks. Profiler is probably your best friend here.

As you are on sql server 2005 you could try setting the default isolation level to READ COMMITTED SNAPSHOT to see if you get a boost.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rikleo2001
Posting Yak Master

185 Posts

Posted - 2010-10-05 : 06:48:31

Thanks for your help.

Good idea, but will this effect any business logic at any level?

SKR

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-05 : 07:09:34

Well it's generally what MS recommend.

There are a bunch of posts on it. Here's one
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=132825

If your business logic depends on dirty reads then it will effect it.

The best thing you can do though is to run profiler and work out what's happening. Profiler can be a bit of a PITA to work with but it's an awesome tool considering that it's bundled with sqlserver for nothing.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rikleo2001
Posting Yak Master

185 Posts

Posted - 2010-10-05 : 08:10:47

Ok, thanks for your help.

I will do as suggested and play with Profiler and Perfmon.

SKR

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-05 : 08:34:34

I think you need to examine your queries and indexes. Locking is not a bad thing. Blocking is.

From the very little information you provided, there is no reason not to suspect bad query plans/index usage

As Charlie said, you can run profiler to capture the problem queries, then review their execution plans. You may be able to catch the offending queries just by executing sp_who2 then DBCC INPUTBUFFER() as well.

rikleo2001
Posting Yak Master

185 Posts

Posted - 2010-10-05 : 09:57:07

Thanks for your feedback.

Averge wait time (ms) for Locks is 66562 as per the perfmon, which is I beleive very high and system processor queue length is Average 5.

Based on above looks like to me locking is happening.

Also when application was tested with one user there is no delay and no performance impact but as soon as multiple users conencts then comes the issue of performance and high CPU. and Lock_M_S and _U goes up.

I will try out that isolation snapshot option and try with this and then will catch the queries who are taking much time.

Thanks

SKR

rikleo2001
Posting Yak Master

185 Posts

Posted - 2010-10-07 : 09:58:38

Qucik question though

Say if I have Isolation level set to Read committed snapshot at DB level, what if the user have procedures with manual set command like

SET Transaction isolation level read uncommitted.

Does this means that manual set command override DB level isolation?

Thanks

SKR

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-07 : 10:20:30

yup.

Generally you really don't want to run at read uncommitted.

Did you get anywhere investigating indexes / blocking / etc

Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rikleo2001
Posting Yak Master

185 Posts

Posted - 2010-10-08 : 03:57:24

I ran profile with tunning template and find quite a few queries with over 15 min. First as suggested I have changed the DB level isolation level to read committed snapshot and waiting from application team to run a test again to see if any performance improves.

But these queries runs fine in single user enviornment, as long as multiple user kicks in performance degrades. I think it is realted with locking.

I will keep you posted.

Thanks again

SKR

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-08 : 04:11:58

What could a well written query possibly be doing for 15 minutes?

My advice is to sniff the sqltext from those badboys and start optimising. I'll bet money that there is some sort of Cursor or triangle join or *something* horrible in those queries.

As russel said: locking isn't the problem, blocking is. You can't eliminate locking (safely) because it's part of the transactional integrity guarantee. However code that is so insufficient that it holds locks for long times causes blocking with other processes. SQL Server will choose the most expensive (longest running) proccess to prioritise if this escalates to a deadlock.

Of course a query that runs for 15 minutes will be fine on it's own. It can't possibly conflict with anything. But if it's holding locks on the tables it's doing stuff to then other queries will be held up.

READ_COMMITTED_SNAPSHOT will only help in that it stops writes from blocking reads.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources