SQL Performance Issues?

I'm trying to find out if my SQL server is having any performance issues and was wondering if someone could look at my perfmon logs and tell me if they see anything out of the ordinary.  I'm trying to track down an issue between our in-house application and the backend SQL box it uses.

-Server Stats-
-Proliant BL460c G1
-Windows Server 2003 R2 Enterprise x64 SP2
-Intel Xeon X5365
-3GHz
-10GB of RAM
-8 Procs
-SQL Server 2005 Ent. Ed. x64

-Array-
Controller: MSA1500 CS Controller
Bus Interface: Fibre Channel
Raid: RAID 6 (ADG)
Size: 1.14TB

Let me know if I'm leaving something out.  Any help would be greatly appreciated.  Thanks!




Chicot_ParkerAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Ah, slight misunderstanding. "how do you find" really means "what do you think of" , and spindles does mean disks - raid6 with 6 disks should perform very well...

anyway.. perfrmon...
 
Buffer cache hit ratio is excellant at 100%   (worry at < 90%  - think ms say 97%)
page life expectancy is very very good - the higher the better   (worry at < 300)
processor queue length is very very good (small peak)  (worry at 5xprocessors ie > 40)
%processor time is very good (hard to tell from the stats what it is doing)  (worry at > 80%)

disk queues look good, generally there is nothing that really stands out. It would appear to be an overnight snapshot, so expect some of those small inconsequential spikes might be maintenance plans, and also, might not be representative of peak loads.

Leave it running for a day or two. Is there any particular reason why you are concerned ?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
<< was wondering if someone could look at my perfmon logs and tell me if they see anything out of the ordinary >>

You havent attached any
0
 
Mark WillsTopic AdvisorCommented:
Machine looks good - how do you find raid 6 ? how many spindles on that array ?

Perfmon looks ordinary - as rrjegan17 has alerted already...
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Chicot_ParkerAuthor Commented:
Sorry, I'm running IE8 and there, apparently, is a compatibility issue with uploading files to this site. Should be there now.
mark wills:   how do you find raid 6 ?  In the HP ACU.
how many spindles on that array ?   Not sure what you mean by spindles.  There are 6 drives in that array if that's what you are refering to.

SQL-Performance-Monitor-021809-0.xls
0
 
Mark WillsTopic AdvisorCommented:
and "why are you concerned" really means " what is it with the back end applications that seems to be sufferring"

How do they connect ?

Have you turned on disk caching - though it doesn't really seem to be too much of a problem - what was the transaction volume during that period ? What is the nature of data access / writes queries (data warehouse or oltp)...
0
 
Chicot_ParkerAuthor Commented:
We have a proprietary application that sends data out to the internet via FTP or Email.  This app sits on, what we call, our transmission server and communicates with our SQL db.  Each FTP or email gets a status logged to the database of whether it was sent successfully or not and there is a queue that these FTPs/Emails sit in and there are processing loops that happen behind the scenes that package this data in the queue and primp it for it's journey out to the internet.  All this info is logged to the database and/or uses the database info to process this outbound data.  We send out about 45,000 email/FTPs a day and they get sent out as fast as possible, according to the programmer.  On normal days our queue will have about 20 - 100 emails/ftps sitting at one time waiting to be processed.  And i've seen this application send out 180 email/ftps in one minute.  I'm telling you all this because during random times of the day, during random days of the week, this queue will grow to over a 1,000+ for about 3-4 hours and then will all of a sudden go back down to it's normal range.  There is no relation to busier hours, because I've seen it  backup during slow days, and I've seen the queue keep up during our peak hours.  So I'm trying to get a baseline of what that server looks like during normal times, which is what the log I attached should show, and then get a log of what it looks like when we are seeing this backup.  We are trying to figure out if it's the code of the software, network, or DB.  This software was written specifically for our company and we work closely with the programmer of the software and he is pointing towards the DB rather than his code.  Confused??
We have not turned on disk caching. I'm not sure how to do that.  And it's a data warehouse.  I guess I will post another log when this backup occurs and go from there.  Thanks for your help so far.
0
 
Mark WillsTopic AdvisorCommented:
Not confused, sounds fairly intense...

Few things, first up turn on disk caching - check the specs and user manual for your disk controller
http://h20000.www2.hp.com/bizsupport/TechSupport/Document.jsp?lang=en&cc=us&taskId=115&prodSeriesId=415598&prodTypeId=12169&prodSeriesId=415598&objectID=c00268377

Also any upgrades / trouble shooting etc on that controller : http://h20000.www2.hp.com/bizsupport/TechSupport/Home.jsp?lang=en&cc=us&prodTypeId=12169&prodSeriesId=415598&submit.y=8&submit.x=5&lang=en&cc=us

If it is a data warehouse, and can be regenerated, have you thought about "simple" recovery model, or, if "full" then how often are you running transaction log backups ?

That "pregnant pause" is probably the thing to try to focus on. What else is the machine doing ?
0
 
Chicot_ParkerAuthor Commented:
We are running Trans backups every 15 mins.  I don't want to change anything just yet.  We are currently experiencing a backup of our queue and I'd like to post up another perfmon log tonight and see how it compares to the original log I posted when the system was working during normal, off peak hours.  The only thing that this machine is used for is SQL.  It has our main applications database (which is the one we are troubleshooting), a Blackberry DB (with about 5 users), and a citrix farm DB (about 10 servers in the farm).
0
 
Mark WillsTopic AdvisorCommented:
Good call on perfmon, wasn't really suggesting changes just yet, other than make sure caching is on...

That blackberry DB - that is used for communincating with field operatives ? what type of activity do you have there ? Could be competing with comms based resrouce.
0
 
Chicot_ParkerAuthor Commented:
I'm not sure what all is read/writing to the Blackberry DB, but it just allows mobile phone users access to email.  And it's only about 5 mobile phone users, so I don't think it gets a lot of action.  Here is another perfmon log from a day we experienced our queue backing up.  Around 11:30am - 1:00pm and 2:45pm - 4:30pm on 2/23/09.
SQL-Performance-Monitor-021909-0.xls
0
 
Chicot_ParkerAuthor Commented:
The problem is still there, but from your comment, I believe we can rule out SQL performance issues.
0
All Courses

From novice to tech pro — start learning today.