April 17, 2009 at 8:47 am
I have some simple queries that should complete in 1 minute that hang for 10+ minutes and don't appear to update any records. Here are some stats.
SQL2005 SP3 Standard
Windows Servers 2003 Enterprise SP2 running on VMWare 3.5
Task Manager shows: Physical Memory (K) : 16.5 Gig Physical Memory Total, 1.7 Gig Available
PF Usage display shows 13.7 Gig
Under Processes, sqlservr Mem Usage = 237,324 K (nothing else using much)
2 processors running around 80 %, 2 running around 20-70% Total CPU average 50-75%
AWE enabled
Max Server Memory = 14145000 meg
PAE enabled by default
April 17, 2009 at 8:50 am
Did you see any blocking?
How about the Exec Plan?
April 17, 2009 at 8:55 am
No blocking. There was a table scan on a 56,000 record table, but they run this every week without incident. I added indexes to fix that but no help. We had various mysterious problems like this since moving to this environment a month ago.
April 17, 2009 at 10:36 am
You CPU usage is high; could it be there is CPU pressure? Delaying the execution of the query? If you can post the query and attach the execution plan we can see from there if there is something obvious that needs to be fixed (please attach it as a file).
In addition look at following counters when you run the query:
1) Memory Usage (page life exp, cache hit ratio, etc)
2) CPU Usage
3) Logical Disk IO (Avg. Disk Read Queue Length, Avg. Disk Write Queue Length, %Disk Time)
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 17, 2009 at 11:28 am
Is this really the setting you have for max memory?
Max Server Memory = 14145000 meg
14 million, 145 thousand megabytes? If so, for a system with 16GB of memory available you want to allocate no more than 14GB (14336MB), and I would recommend setting it at no more than 12GB (12288MB).
Remember, the OS needs at least 2GB of memory available just to manage the memory available when you get above 12GB of memory. That is why you would not set the /3GB switch in boot.ini on this system.
And, since this is Standard - you might even consider dropping that number even more. If you still see problems, then you definitely want to consider upgrading to Enterprise Edition, setting lock pages in memory and increasing the amount of memory on the system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply