What's been running all night? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

What's been running all night?

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-10 : 03:03:57

Morning all

I've got a spreadsheet that shows me the CPU usage on our server (thanks to the daily email from the other day) and it's shows 50% CPU usage overnight when it should have been virtually nothing.

Is there any way to check what queries, etc have been running?
I've checked the SQL server logs under Management and it shows some logins at odd times which I'm investigating but I can't see anything else in there.

All help gratefully received.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-10 : 07:26:44

Right-click on the database in the object explorer, and select Reports -> Standard Reports -> Object Execution Statistics. That may give you some insight if the object(s) are still in cache.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-10 : 07:58:16

Unfortunately, it looks like the cache has cleared as there's nothing untoward in any of the reports that I can see.
Looks like I'll either have to mark it under the heading "One of those things" or start interrogating people who have access to the server.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-25 : 02:32:33

Hi all

Thought I'd ressurect this thread rather than start a new one.
I've got some code which should track all the running queries at any given point. The code is this:-


SELECT 
GETDATE() as [Date]
,OBJECT_NAME(ObjectID) as ObjectName,
st.Text, 
DB_NAME(database_ID) as dbname,
session_id, sp.open_tran, nt_username, nt_domain
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
LEFT JOIN sys.sysprocesses sp ON r.session_id=sp.spid
group by
OBJECT_NAME(ObjectID),
st.Text, 
DB_NAME(database_ID),
session_id, sp.open_tran, nt_username, nt_domain

Unfortunately, it looks like it's missing a few as I have somthing running that's not showing up.
For example, I've got a job step which basically is a time-waster until a certain time appears (it's part of another legacy job I'm going through tidying up).
The code is this:-


while datepart(hour,getdate())<7 
    begin
        use symposiumdw
    end

To me, that should be using a tiny part of the processor (unless someone want's to correct me) but the processor is running at almost 50% (with nothing else apparently running).

So, I have two questions:-
1) Should the first piece of code I posted pick up all running queries (and can it be changed if it doesn't)?
2) Within that piece of code, can I track what percentage of the processor is being used by any given query? I fairly certain that the processor usage will vary wildy based on a number of factors, but the average or a snapshot will do.

Any help gratefully received.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-25 : 07:10:29

I would think that that while loop, bad as it is, should not gobble up 50% of the CPU. You might want to run resource monitor or perfmon on the box to see what is taking up the CPU.

A script that I have found very useful is Adam Machanic's sp_WhoIsActive. You might try that to see if it gives you any additional insights into the queries running on your server: http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-25 : 08:34:08

How else would you scipt that bit until I get round to rebuilding the whole job?
I'll have a quick look at Adam's SQL this afternoon and get back to you.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-25 : 08:43:54

You can use WAITFOR

WAITFOR TIME '07:00'

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-25 : 08:53:27

That's a new command for me.
I assume I can change that to '08:00' with no issues?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-25 : 08:57:05

Yes. You can specify a time or a delay. http://msdn.microsoft.com/en-us/library/ms187331(v=sql.100).aspx

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-25 : 10:08:52

while loop is actually doing work. It's running as fast as the sql server engine can run it calling the datepart(hour,getdate()) functions

WAITFOR is much, much, much better

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-25 : 10:17:35

Transat Charlie - thanks for that, but why would such a simple loop use that much processing power?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-25 : 11:13:09

I'm not sure it was -- just expressing my preference for the WAITFOR..

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-26 : 01:53:28

Thanks for that.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-26 : 06:49:50

quote:
Originally posted by rmg1

Transat Charlie - thanks for that, but why would such a simple loop use that much processing power?

My theory based on what Charlie pointed out (" running as fast as the sql server engine can run it calling the datepart(hour,getdate()) functions") is that, it is using 100% of the cycles on one CPU. So if you have a two CPU's, that would explain the 50%.

You can see how many CPU's you have using the following query (or look in properties by right-clicking on the server name in object explorer in SSMS)

select cpu_count from sys.dm_os_sys_info

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-26 : 07:34:09

I know the server has 2 CPUs so that would make sense.
It looks like the WAITFOR TIME code has killed off that little bit anyway.
Thanks for the help so far.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources