December 14, 2009 at 10:32 am
Currently, I am load testing database server at the moment, I can see that Available memory megabytes is dropping to about 700mb. The server has 8GB physical memory, windows 2003 server Enterprise with x64. AWE configured and Max server memory configured to be 6GB as its purely a SQL server box. The nature of the queries on the server are ones which require the use of tempdb quite a lot, creating work tables within stored procedures , sorting, joins, order by etc. What I do see is that the figure just drops drastically, I am left thinking, what could be the cause of the problem as to why I'm seeing such low figures. Queries tend to be taking some time as well, especially when executed within the load testing harness. I must say that some of the queries make use of tempdb and can hold thousands of wide rows.
I also notice that the write queue length can jump to around 10 especially when running queries that makes use of those temp tables, this relates to average que length in terms of writes, On the other hand, If i attempt to avoid the write by using a CTE or a derived table, I dont see much improvements in terms of the duration either. Is there anything I can look out for here.
December 14, 2009 at 10:47 am
What is your disk configuration and tempdb configuration?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 14, 2009 at 12:51 pm
CirquedeSQLeil (12/14/2009)
What is your disk configuration and tempdb configuration?
Configuration for the database files is pretty poor, its all based in a single RAID 5 config. 3 spindles.
I know the disk isnt the best neither is the controller, but I'm wondering why is the memory suffering as well ?
December 14, 2009 at 9:26 pm
Just a hunch, are you seeing contention in tempdb?
http://blogs.technet.com/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx
http://msdn.microsoft.com/en-us/library/ms190730.aspx
Also, setting up tempdb properly can ease some of the performance problems you are seeing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 18, 2009 at 6:12 am
It might be worth setting minumum server memory as well. That way, if anything is trying to grab memory back, it won't be able to. Also, are you using 64-bit SQL Server? If you are, then you don't need AWE. If you're not, it may be worth investigating whether reinstalling with 64-bit may give any performance improvements.
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply