SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 What's been running all night?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 10/10/2012 :  03:03:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/10/2012 :  07:26:44  Show Profile  Reply with Quote
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.
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 10/10/2012 :  07:58:16  Show Profile  Reply with Quote
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.
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 10/25/2012 :  02:32:33  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/25/2012 :  07:10:29  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 10/25/2012 :  08:34:08  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/25/2012 :  08:43:54  Show Profile  Reply with Quote
You can use WAITFOR
WAITFOR TIME '07:00'
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 10/25/2012 :  08:53:27  Show Profile  Reply with Quote
That's a new command for me.
I assume I can change that to '08:00' with no issues?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/25/2012 :  08:57:05  Show Profile  Reply with Quote
Yes. You can specify a time or a delay. http://msdn.microsoft.com/en-us/library/ms187331(v=sql.100).aspx
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 10/25/2012 :  10:08:52  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 10/25/2012 :  10:17:35  Show Profile  Reply with Quote
Transat Charlie - thanks for that, but why would such a simple loop use that much processing power?
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 10/25/2012 :  11:13:09  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 10/26/2012 :  01:53:28  Show Profile  Reply with Quote
Thanks for that.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/26/2012 :  06:49:50  Show Profile  Reply with Quote
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
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 10/26/2012 :  07:34:09  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000