November 29, 2012 at 12:51 pm
Not sure if this should go in newbie section.
So we have a major app running on this network ever since i took this position.
random issues, not major, but noticeable by users. Vendor recommend I upgrade server to more cores, more rams. Server cpu spikes to 50% and 18%. Specs below:
I'm not a db admin or anything, but research a couple of stuff about sql diagnostic. Ran some queries, found out the top wait type is latch_ex and the latch_class is access_methods_dataset_parent. Also, ran a lot of performance counter which include memory, NICS, physical disk, processor, sql server, system. I don't see anything out of the ordinary. So I'm kinda stuck here. Any further advice would be awesome.
latch_classwaiting_requests_countwait_time_msmax_wait_time_ms
ACCESS_METHODS_DATASET_PARENT3273011151068172678905
NESTING_TRANSACTION_READONLY251670425964894
FGCB_ADD_REMOVE3415951256753
NESTING_TRANSACTION_FULL10628756014831
LOG_MANAGER378927530
ACCESS_METHODS_ACCESSOR_CACHE3159256916
this is my server.
Specs:
temValue
OS NameMicrosoft® Windows Server® 2008 Standard
Version6.0.6002 Service Pack 2 Build 6002
Other OS Description Not Available
OS ManufacturerMicrosoft Corporation
System Name
System ManufacturerHP
System ModelProLiant DL360 G7
System Typex64-based PC
ProcessorIntel(R) Xeon(R) CPU E5640 @ 2.67GHz, 2666 Mhz, 4 Core(s), 8 Logical Processor(s)
BIOS Version/DateHP P68, 9/30/2010
SMBIOS Version2.6
Windows DirectoryC:\Windows
System DirectoryC:\Windows\system32
Boot Device\Device\HarddiskVolume1
LocaleUnited States
Hardware Abstraction LayerVersion = "6.0.6002.18005"
User Name
Time ZoneEastern Standard Time
Installed Physical Memory (RAM)36.0 GB
Total Physical Memory32.0 GB
Available Physical Memory8.96 GB
Total Virtual Memory49.0 GB
Available Virtual Memory25.3 GB
Page File Space17.1 GB
Page FileC:\pagefile.sys
November 30, 2012 at 6:58 am
any thoughts?
November 30, 2012 at 8:02 am
Have you ran any statistics on your database?
How about your transaction log? Do you maintain it properly?
I am not a DBA either, but this what I would look first.
November 30, 2012 at 8:09 am
There my not be enough information here for people to help you. You say the CPU spikes at 50%, that doesn't seem too bad, as long as it isn't plateauing at 50% for extended periods. Have you tried using performance monitor to see things like disk queues to tell what disks could be the bottleneck, or page life to see how long SQL Server is keeping data pages in memory?
There are some good articles here such as:
http://www.sqlservercentral.com/articles/Administration/performancemonitoringbasiccounters/1348/
November 30, 2012 at 8:11 am
Zeev Kazhdan (11/30/2012)
Have you ran any statistics on your database?How about your transaction log? Do you maintain it properly?
I am not a DBA either, but this what I would look first.
what do you mean by stats? are you talking about batch/secs , etc? I have no idea about transaction log, researching now.
Chris Harshman (11/30/2012)
There my not be enough information here for people to help you. You say the CPU spikes at 50%, that doesn't seem too bad, as long as it isn't plateauing at 50% for extended periods. Have you tried using performance monitor to see things like disk queues to tell what disks could be the bottleneck, or page life to see how long SQL Server is keeping data pages in memory?There are some good articles here such as:
http://www.sqlservercentral.com/articles/Administration/performancemonitoringbasiccounters/1348/
yea i ran perfmon and look at disk queues and page life, nothing out of the ordinary. yea, the cpu spikes at 50%, but average about 20% i suppose.
November 30, 2012 at 8:14 am
I know how to work with statistics in Oracle.
In MSSQL I hope this one will help:
http://stackoverflow.com/questions/1328506/how-do-you-gather-statistics-from-sql-server
November 30, 2012 at 8:34 am
If you're only spiking to %50 adding more cores isn't a great suggestion as a starting point. Unfortunately troubleshooting performance issues are not a simple answer, there are many possibilities. I would start by asking the users what do they mean by "a noticeable difference". By that I mean ask them what they are doing when they notice it is slower.
If they are always performing the same task, I would then start investigating that task to see what it is doing. You can do things like running a profile trace when they start the task to determine what queries are being run and can then investigate the performance of those queries/indexes.
You need a better defined starting point otherwise you'll find yourself checking every aspect of the server, which can take a long time.
Jake
November 30, 2012 at 8:50 am
I agree with Chris and Jake. I would try to get a better understanding of what the end users are seeing so that you can narrow down your options. The information so far does not indicate a problem with CPU or Memory. If you can narrow down the issue to a certain set of tables/queries it is more cost effective to optimize in the long run than to throw unnecessary hardware at it even though CPU/Memory can be cheap in the short term. Though depending on the agreement with the vendor you may have very little options at optimizing anything you find.
As Chris mentioned, if you want to look at hardware, i would thoroughly look at the disk layer where most of your bottlenecks start. Are you using external storage like a SAN or NAS, or are the disk internal to the server?
November 30, 2012 at 9:02 am
joshua.scott2 (11/30/2012)
I agree with Chris and Jake. I would try to get a better understanding of what the end users are seeing so that you can narrow down your options. The information so far does not indicate a problem with CPU or Memory. If you can narrow down the issue to a certain set of tables/queries it is more cost effective to optimize in the long run than to throw unnecessary hardware at it even though CPU/Memory can be cheap in the short term. Though depending on the agreement with the vendor you may have very little options at optimizing anything you find.As Chris mentioned, if you want to look at hardware, i would thoroughly look at the disk layer where most of your bottlenecks start. Are you using external storage like a SAN or NAS, or are the disk internal to the server?
the random issues are random. I see it. Lockouts, getting kick out of the web page application, random issues that cannot be reproduce, but it does happen. the vendor said that the database is too busy that's why those issues are occurring.
If the issues is constant and I can reproduce, it'll be easier to troubleshoot, but not the case here.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply