April 21, 2008 at 8:11 am
Hello,
I have a performance problem with a SQL server 2005. I got 2 servers (development and production) with the following configuration:
windows 2003 SP1 standard edition x64
9.00.2047.00 SP1 Standard Edition (64-bit),
current compatibility level 80
Same hardware
Ram - 4GB on both servers
CPU-2
Sql server 2005(standard edition) 64 bit on both servers
On the production server the SQLserver.exe is consuming a lot of memory and not releasing it. This results in a lot of Hard Page/Fault /sec. We don't have the same problem on our development server.
I set the Max server Memory of the sql server to +/-2.5 GB memory (2500 Mb)
I ran some traces on the server and got the following results
averageminmax
Memory\Available Mbytes199.6094675561030
Memory\Pages/sec41.1369889501288.226532
Network Interface\Bytes Total /sec67555.62113684.2437292619236.3725
PhysicalDisk(_Total)\% Disk Time1504.8557260.96871290222484.58841
PhysicalDisk(_Total)\Avg. Disk Queue Length30.11659330.019354077449.6917682
PhysicalDisk(0 C:)\% Idle Time1279.9485202866.299877
PhysicalDisk(1 D:)\% Idle Time404.273847502866.033589
Processor(_Total)\% Processor Time57.55152373.45983485892.39554132
SQLServer:Access Methods\Full Scans/sec10.081596940600.5185089
SQLServer:Buffer Manager\Buffer cache hit ratio99.8301787897.7313009699.8917456
SQLServer:Databases(_Total)\Transactions/sec12.324772690.73191151131.1149225
SQLServer:General Statistics\User Connections8.579881657111
SQLServer:Locks(_Total)\Average Wait Time (ms)4797.1676590193500
SQLServer:Memory Manager\Total Server Memory (KB)2118657.89320222722252800
System\Processor Queue Length3.781065089044
I now set the Max server memory to 2 Gb (2048 Mb), to see if this gives some results (traces running again).
I suppose that I have a disk I/O problem, but would like to be sure that this is the problem.
Does any of you experienced the same problem or can anyone give me some tips so that I can pinpoint the problem and propose a solution?
Thanks,
Peter
April 21, 2008 at 8:33 am
It looks to me like you're on the right path.
What else is running on that server? SQL Server doesn't "play nice", when it comes to taking over RAM and CPU and such, and is very greedy about those.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 21, 2008 at 8:35 am
From what I can see here, I'd focus on the code. You've got a lot of waits occurring. Do you know why? You also have a reasonably high number of scans. I'd collect Profiler data & identify the most frequently called procedures and the longest running procedures and see what can be done with those to reduce or eliminate the wait states.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2008 at 8:48 am
Apart from the HP Insight Manager we don't have anything runnning on this server. But we have it also running on our development server where we don't have the problem.
The CPU usage is also quite low, so I don't think there's a concurrency problem with it, but will try to find out if some queries or stored procedures are using up all the resources.
Thanks,
Peter
April 21, 2008 at 8:54 am
I assume there's a real difference between the number of users and transactions between dev & production? That alone will show a pretty large difference in behavior. But I'd still focus on the code in this case. To start anyway.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2008 at 1:58 pm
Something is wrong with these numbers. In particular:
peter Vramby (4/21/2008)
PhysicalDisk(0 C: )\% Idle Time1279.9485202866.299877PhysicalDisk(1 D: )\% Idle Time404.273847502866.033589
You shouldn't be getting Idle% greater than 100%. What is your disk configuration?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 21, 2008 at 2:06 pm
Also, you do not actually have Hard Page Faults listed anywhere in these statistics ("Page Reads/sec"). Pages/sec does not directly correspond with "Page Reads/sec" and a value of 40+ for Pages/sec is not necessarily bad.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 22, 2008 at 2:11 am
Sorry that I forgot to add the Hard page faults counter in the initial message. (I forgot to paste it over from the 2nd trace file)
Memory\Page Faults/sec
average 414.1140232
min value 0
max value 2672.28215
The system is running on 4 physical disks with mirroring.
C drive is used for the System and the system databases (including tempdb)
D drive is used for the user databases.
April 22, 2008 at 2:35 am
---- UPDATE ----
I ran an SQL trace and didn't find any Stored Procedures or Queries taking over 100 ms. So for me from a SQL point of view everything looks to be optimal, but I still have this huge amount of hardware page faults.
So I had a look at the task monitor to see what processes are having these page faults and I only have 3 processes that increase their page faults every refresh:
sqlserver.exe
explorer.exe
java.exe *32
Knowing now that sqlserver looks optimal, I will focus on the java process.
Anyone had similar problems?
April 22, 2008 at 12:37 pm
peter Vramby (4/22/2008)
Sorry that I forgot to add the Hard page faults counter in the initial message. (I forgot to paste it over from the 2nd trace file)Memory\Page Faults/sec
average 414.1140232
min value 0
max value 2672.28215
The system is running on 4 physical disks with mirroring.
C drive is used for the System and the system databases (including tempdb)
D drive is used for the user databases.
"Page Faults/sec" includes all page faults (hard and soft) and thus are not the same as Hard page faults. Usually, page faults will be mostly (80-90%) soft faults which could be much higher than 400/sec and still be OK.
Hard page faults are in the perfmon measure "Memory\Page Reads/sec".
So far it doesn't look like anything is wrong with your performance. Are you or your users experiencing response-time issues?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 23, 2008 at 4:10 am
I've ran another trace (only 15 minutes) to see the results of the Memory\Page Reads/sec. Here's the result:
Average 2.2627
Minimum value 0.0000
Maximum value 41.4943
For the moment my users are not connected to it because we are having this performance problem before putting the server online. So my test users are having performance problems, even timeouts when they connect to the database.
Thanks for the advise so far.
Peter
April 23, 2008 at 11:17 am
average Hard page faults below 10/sec should not be a problem.
Suggest you do the following:
1) Get the CPU% and the (correct) Disk Idle% for all of your disks
2) use profiler to look at the queries that are running the longest and see if they are contending for the same tables/locks, etc. .. Or if there is something else running that may be interferring.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 24, 2008 at 4:19 am
We finally found that there was a hardware problem with the disks.
We moved everyhting over to another server so that the hardware guy can fix this.
Thank you everybody for your advise and help on this matter.
Peter
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply