July 16, 2008 at 3:09 pm
I have a new dedicated sql server running with 2 quad core processors, 16 gig of ram, Windows server 2003 X64 bit, Sql Server 2005 Standard X64 and am getting a lot of sort warnings (more than 10 per minute). I think this very odd as the only database I have on it right now is a 2.6 gig database with 20 concurrent users. With 16 gigs of ram, how could I get so many sort errors? It should have plenty of ram to handle these queries even if they are less than optimal. The max row of this database in the biggest table is 150k rows. I have min and max server memory set to default (dynamic configuration)
Page Faults/sec running at 736 maximum and average of 242 with duration of 1:40
Pages/sec = 0
page reads/sec = 0
page writes/sec = 0
Target server memory = 14743528
Total Server Memory(kb) = 14484680
% Usage (Paging) 99.73
% Usage Peak ( Paging) 99.73
Looks like I'm low on RAM but HOW? How do I see what is using up all of it? It looks like Sql is wanting more than I have but I dont understand how this small of a database could want that much RAM. We have these same databases running on SS2k machines with 8 gig of RAM and doing fine. One SS2k machine has 30 databases on 3 different instances. The databases are copies of each other with different data in them (same table structure and such)
Help me figure out whats going on and how to read the performance counters I'm collecting to get to the bottom of this problem so I can put this box in full production
Thanks!
'nix
July 17, 2008 at 1:30 am
phoenixrising1599 (7/16/2008)I have min and max server memory set to default (dynamic configuration)
I would recommend changing that. On 64 bit with lots of memory, SQL's more than capable of using most of the memory on the server and starving the OS.
I would suggest a max memory of 14GB
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2008 at 9:06 am
Right, I did that and it didnt help. Take a look at the counter for Target Server Memory, The way I understand that counter, its the amount of memory that SQL wants. It is greater than the total server memory. If SQL wants more than I have I would normally interpret that to mean I need more RAM. The problem with this is I am only running 1/20th the load that we used on SQL2000 and it ran fine. I dont understand why SQL2k5 would want that much more ram than I have to run one simple little database.
The application/database on this server seems to be working fine but I'm wary about adding more databases when I'm seeing numbers like this, unless I am misinterpreting the meaning of the counters.
'nix
July 17, 2008 at 1:27 pm
You should review the query plans to see if your server is performing as you expected.
By the way, did you updated the stats /rebuilt the indices after the migration ?
* Noel
July 17, 2008 at 4:21 pm
Updated stats and rebuilt indexes. I'm making a few adjustments to see if it improves and will check the query plans as you suggested.
'nix
July 18, 2008 at 3:04 pm
Can you post the actual messages?
[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]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply