January 9, 2012 at 3:54 pm
How could I sort the result set of sp_lock by a specific count and then pass the spid of the lock into sp_who?
Basically I'm just trying to find a way to determine locks by user login and if possible display logins that have locks greater than a specific value such as 10
If anyone has any thoughts on this or knows of a way to do it please let me know.
January 9, 2012 at 3:55 pm
You could just use the DMVs instead, query and aggregate sys.dm_tran_locks (though it's a complex view) and join to sys.dm_exec_requests and sys.dm_exec_sessions.
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
January 9, 2012 at 4:03 pm
Cool. Thanks Gail. I always forget about those nifty dmv's. I'll give it a try.
January 9, 2012 at 4:18 pm
The query in this article shows how to combine those in essence
http://www.sqlservercentral.com/articles/sp_who2/70222/
It uses the new dmvs and the tran_locks as suggested by Gail.
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
January 10, 2012 at 2:11 am
Why re-invent the wheel? Adam Machanic wrote a great piece of code known as "sp_WhoIsActive", that will tell you this and much more. Give it a try!
-- Gianluca Sartori
January 10, 2012 at 2:50 am
Gianluca Sartori (1/10/2012)
Why re-invent the wheel? Adam Machanic wrote a great piece of code known as "sp_WhoIsActive", that will tell you this and much more. Give it a try!
Agreed. It is a great piece of work, and completely free, with extensive documentation on Adam's blog at SQLblog.com. There are also many other resources on the web including videos and reviews.
January 10, 2012 at 7:11 am
Thanks for the suggestions everyone. I really like sp_whoisactive, that is some interesting code.
I'll probably still build my own query from the DMV's Gail suggested though as I can specify criteria in a where clause.
Really good information overall though. Now I just have to process it.... :ermm:
January 10, 2012 at 7:15 am
kwoznica (1/10/2012)
Thanks for the suggestions everyone. I really like sp_whoisactive, that is some interesting code.I'll probably still build my own query from the DMV's Gail suggested though as I can specify criteria in a where clause.
Really good information overall though. Now I just have to process it.... :ermm:
SERIOUSLY, take 5 minutes to read through all what you can get out of sp_whoisactive.
That thing should cost 200$ per server. It's that good.
January 10, 2012 at 7:27 am
🙂 Ok ok....I'll read through it. I can see the benefit of it after running it and I'll dive into it when I have more free time today. I'd like to make it selective to specific login_name but I can't because its an SP. Thats why I mentioned that the dmv's may still be useful to me.
Plus alot of the information looks like what activity monitor gives in sql 2008r2. But I promise I will look more into the data it returns. Right now I'm trying to address a locking issue on one of my databases.
What situations in your day make you say, "let me run sp_whoisactive" ?
January 10, 2012 at 7:30 am
kwoznica (1/10/2012)
🙂 Ok ok....I'll read through it. I can see the benefit of it after running it and I'll dive into it when I have more free time today. I'd like to make it selective to specific login_name but I can't because its an SP. Thats why I mentioned that the dmv's may still be useful to me.Plus alot of the information looks like what activity monitor gives in sql 2008r2. But I promise I will look more into the data it returns. Right now I'm trying to address a locking issue on one of my databases.
What situations in your day make you say, "let me run sp_whoisactive" ?
Something wrong with the server.
BTW you can filter on just about anything IIRC. I don't use that much here because there are so few connection, it's faster to just skim through it.
January 10, 2012 at 7:38 am
The server seems good. It seems to be more of an applicaiton issue. We have an invoice program which is an add on to our ERP. It creates a pdf of the invoice then emails the customer. It runs under a specific login. When it runs it creates an enormous amount of locks at times and other times it does not. When it does cause the problem, hundreds of users can't work. If I kill the spid the locks go away and all the users are happy.
I need to see how granular the locks are by looking at the locking types, document them, and give feedback to the application team. Of course its not their problem. I need to prove its their problem.
January 10, 2012 at 7:43 am
That's what the activity monitor runs. It has some decent info that (might) complement sp_whoisactive.
--Borrowed from the activity monitor
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)
-- [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
January 10, 2012 at 8:02 am
Now that is really good stuff. Thanks for that Ninja!. I'll let you know how my findings go.
January 10, 2012 at 8:03 am
kwoznica (1/10/2012)
🙂 Ok ok....I'll read through it.
Please do. It can do everything you have asked easily, you just need to invest a little time trying it. I know it is more exciting to write your own thing, but trust me, you'll get stuff wrong and end up re-inventing the wheel, just with new square corners. Adam spent hundreds of hours on this, and there are all sorts of deeply technical corrections and workarounds for weird SQL Server bugs and behaviours. It's the only tool I ever use. I haven't touched Activity Monitor for years.
January 10, 2012 at 11:04 am
SQL Kiwi (1/10/2012)
kwoznica (1/10/2012)
🙂 Ok ok....I'll read through it.Please do. It can do everything you have asked easily, you just need to invest a little time trying it. I know it is more exciting to write your own thing, but trust me, you'll get stuff wrong and end up re-inventing the wheel, just with new square corners. Adam spent hundreds of hours on this, and there are all sorts of deeply technical corrections and workarounds for weird SQL Server bugs and behaviours. It's the only tool I ever use. I haven't touched Activity Monitor for years.
Activity what? 😀
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply