| Author |
 Topic  |
|
|
rmg1
Posting Yak Master
245 Posts |
 Posted - 10/10/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/10/2012 : 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
Posting Yak Master
245 Posts |
Posted - 10/10/2012 : 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
Posting Yak Master
245 Posts |
Posted - 10/25/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/25/2012 : 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
Posting Yak Master
245 Posts |
Posted - 10/25/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/25/2012 : 08:43:54
|
You can use WAITFORWAITFOR TIME '07:00'
|
|
|
|
rmg1
Posting Yak Master
245 Posts |
Posted - 10/25/2012 : 08:53:27
|
That's a new command for me.
I assume I can change that to '08:00' with no issues? |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 10/25/2012 : 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
Posting Yak Master
245 Posts |
Posted - 10/25/2012 : 10:17:35
|
| Transat Charlie - thanks for that, but why would such a simple loop use that much processing power? |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 10/25/2012 : 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
Posting Yak Master
245 Posts |
Posted - 10/26/2012 : 01:53:28
|
| Thanks for that. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/26/2012 : 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
Posting Yak Master
245 Posts |
Posted - 10/26/2012 : 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. |
|
|
| |
Topic |
|
What's been running all night?
