How to research Resource Problems

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 Administration

How to research Resource Problems

Author

Topic

coreysan
Starting Member

13 Posts

Posted - 2015-04-28 : 13:36:43

Our single database runs on SQL Server 2008, and every day at 1pm it really sucks dry our resources - all 4 cpu cores are in use, for 1 hour.

As a fairly new db admin, how can I research what might be going on? I have the 2008 Admin's Pocket Consultant book, but that's going to be a LOT of reading.

It appears our db doesn't have any procs running in the background, and I'm not proficient to know what maintenance scripts might be lurking in the background.

I'd be grateful for any tips, or suggestions as to where to start looking!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-28 : 13:41:38

I'd check what's using the CPU in SQL:


select top 100
	DB_NAME(dbid), objectid, cpu_time, 
	total_elapsed_time, logical_reads,  [text]
from sys.dm_exec_requests 
cross apply sys.dm_exec_sql_text(sql_handle)
where status <> 'background' 
	and dbid not in (select database_id from master.sys.databases where name in ('master', 'tempdb', 'model', 'msdb'))
	and dbid <> 32767
order by 4 desc

High CPU utilization is typically due to missing indexes, so I'd be checking on that given the above output.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-04-29 : 01:40:01

As well as inefficient queries and SQL Server based workloads, check to see there aren't regular scheduled tasks on the OS .

Has this problem always occured or is it something recent? can you correlate with any changes that may have occured?
Do you have a solid index maintenance plan in place?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Kristen
Test

22859 Posts

Posted - 2015-04-29 : 09:45:26

Long shot: Server Time hasn't "slipped" such that overnight maintenance tasks are now running during the day?

(It would be a lot of "slip" !!!)

coreysan
Starting Member

13 Posts

Posted - 2015-04-29 : 19:52:42

Thanks to all of you! I'll definitely try the query tkizer provided.

Jack - the product we use was purchased from a company in Texas, and they converted our database from an old
Informix db to SQL Server. Trouble is, they put the entire db on one chunk, indexes, views, and all,
and I don't have their source code. I spend time every week making educated guesses. I feel like
I'm constrained to basic optimization issues, nothing more.

There may be more I can do, so I started here! I didn't initiate an index maintenance plan only because
I don't have their source code, so I end up adding indexes on fields I "think" the software will use.

Any other thoughts?

Kristen - thanks for your input. The scheduled jobs I run end at 7am, and the system runs great
until 1:30pm. I ran the profiler, but I'm just learning now how to use it. For example, I can see
a select with a duration of 80 minutes, but the start/end time is only 2 seconds. Doesn't make sense
to me.

There's no scheduled tasks during business hours at all. Just about 15 users doing data entry.
It started about 2 months ago, so that's new.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-04-30 : 01:34:21

@coreysan - Given the situation , firstly make sure you have some optimised settings in place. For example:
1) Statistics are up to date
2) Indexes are managed
3) Progress through http://www.sqlserver-dba.com/2012/06/sql-server-performance-checklist.html

Once you've completed some preliminary checks, attempt to identify the slow running queries at the time you've mentioned. Of course, there could be another reason - such as virus scanning.
Another option is to document the queries\issues and approach the vendor. My experience with vendors is you will need fairly detailed analysis before they acknowledge an issue.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Kristen
Test

22859 Posts

Posted - 2015-04-30 : 03:45:04

Possibly a couple of misunderstandings here

quote:
Originally posted by coreysan

I didn't initiate an index maintenance plan only because
I don't have their source code, so I end up adding indexes on fields I "think" the software will use.

Before adding new indexes make sure that the existing ones are being rebuilt, or reorganised, regularly. They get out of shape, over time, and need freshening up. Rebuilding an index will also recreate its statistics, reorganising (which is much less common, but more efficient on large indexes) need updating of statistics separately. Statistics can be rebuilt easily / on demand. Worth doing that before making improvements so that you are sure that everything is in Apple-pie order before making your test.

quote:
The scheduled jobs I run end at 7am, and the system runs great until 1:30pm

Sorry, I meant to check that the server clock time is correct. If it has slipped a job scheduled for 7am could now be running at 1:30pm. Like I said, that would be a lot of slip! and servers are usually configured to use a time server so that they freshen up their time regularly (but mine, I think, will stop doing that if the variance becomes too great as applying a sudden, large, adjustment might cause problems for data; yours might be set that way too and once it slips past that amount it won't, then, update the time at all)

quote:
There's no scheduled tasks during business hours at all

No regular backups of the Log file? (That wouldn't be causing the problem, just surprising you are not backing up every, say, 10 minutes in case of Disaster Recovery)

quote:
It started about 2 months ago, so that's new.

Sounds like it might be Virus Check or similar. For it to be new, and in SQL, something would have had to have changed. Unless your software provider had a fiddle/upgraded something at that time it seems unlikely it is SQL itself.

The data entry folk haven't started importing some massive XLS of data each day have they? (I'd expect them to have mentioned it, but sometimes folk do stuff because they think it is right / best way

)

Sounds like that's the one you have checked the SQL Agent scheduled tasks

Anything in Windows Scheduled Tasks?

I would look at which Windows task is using CPU - e.g. Windows Task manager. You might be able to see it from the [Processes] tab (turn on "Show all users") or you might need to use the [Performance] : Resource Monitor option. I'm sure you know this but in case not: Click on CPU column heading to sort, descending. If you like (e.g. everything is jumping around!) you can click on the checkbox for one/several services/programs to see the details in the lower panes (you will need to press the "Expand" down-arrow to display any particular Pane of interest)

It might be that SQL and some other service on the same server (such as IIS) are arguing over memory. If so you could reduce the memory allocated to SQL to stop it thrashing with, say, IIS.

Could also look at the Timestamp on Backup files to see if they are dated at 1:30pm. Backup doesn't slow down SQL significantly, but if the backups are on the same disk as Data / Log files then they would be competing for resource. I wouldn't expect to see that as CPU spike though, but when things go bad the circumstances can be "strange".

First objective should be to find the cause

but if it is hurting the users I would look at some possible solutions ... for example, I wonder if things are blocking.

Turn on ASync Stats rebuild perhaps?

SELECT 'is_auto_update_stats_async_on <> 1', is_auto_update_stats_async_on, name FROM master.sys.databases WHERE is_auto_update_stats_async_on <> 1 AND [name] NOT IN ('master', 'model', 'msdb', 'tempdb')

Consider turning on Read Committed Snapshot (ought to test the APP before turning this on, really, but I suppose 99% of cases it will be fine? Perhaps other folk here will disagree with just turning this on to see if it helps (without testing APP first)

) Depends how desperate you are to get performance back for the users PRIOR to being able to fully diagnose the problem

SELECT 'is_read_committed_snapshot_on <> 1', is_read_committed_snapshot_on, name FROM master.sys.databases WHERE is_read_committed_snapshot_on <> 1 AND [name] NOT IN ('master', 'model', 'msdb', 'tempdb')

coreysan
Starting Member

13 Posts

Posted - 2015-05-04 : 19:02:46

I can't thank you guys enough. You've primed my thoughts a ton, and I feel like I can approach this problem a little more intelligently!

It will take me some time to implement these suggestions - and I will!

So again, thank you all so very much!

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources