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_class waiting_requests_count wait_time_ms max_wait_time_ms
ACCESS_METHODS_DATASET_PARENT 327301115 1068172678 905
NESTING_TRANSACTION_READONLY 2516704 259648 94
FGCB_ADD_REMOVE 34 159512 56753
NESTING_TRANSACTION_FULL 1062875 60148 31
LOG_MANAGER 37 8927 530
ACCESS_METHODS_ACCESSOR_CACHE 3159 2569 16
this is my server.
Specs:
tem Value
OS Name Microsoft® Windows Server® 2008 Standard
Version 6.0.6002 Service Pack 2 Build 6002
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name
System Manufacturer HP
System Model ProLiant DL360 G7
System Type x64-based PC
Processor Intel(R) Xeon(R) CPU E5640 @ 2.67GHz, 2666 Mhz, 4 Core(s), 8 Logical Processor(s)
BIOS Version/Date HP P68, 9/30/2010
SMBIOS Version 2.6
Windows Directory C:\Windows
System Directory C:\Windows\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "6.0.6002.18005"
User Name
Time Zone Eastern Standard Time
Installed Physical Memory (RAM) 36.0 GB
Total Physical Memory 32.0 GB
Available Physical Memory 8.96 GB
Total Virtual Memory 49.0 GB
Available Virtual Memory 25.3 GB
Page File Space 17.1 GB
Page File C:\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://qa.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://qa.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