April 13, 2009 at 1:06 pm
We have a SQL Server-based web application, which is in use on one server. The specs there are:
Microsoft Windows Server 2003, Standard Edition, Service Pack 2
AMD Athlon 64 X2 Dual Core Processor 4600+
2.41 GHz, 1.98 GB of RAM
SQL Server 2005
The sqlserver.exe process generally stays between 1.3 and 1.4 Gigs of memory usage, and performs well.
We have installed the same application on a new server, which is utilizing SQL Server Express (SSE). The specs on that system are:
Microsoft Windows Server 2003 R2 Enterprise Edition, Service Pack 2
Dual-Core AMD Opteron Processor 2218 HE
2.60 GHz, 1.66 GB of RAM
We are aware of the memory limitations for SSE and of the downgrade in system capabilities, and were expecting some performance degradation, but not as much as we've been experiencing.
The memory usage by sqlserver.exe on the new box does not go above 400 MB - i thought that SSE could/would use up to 1 GB? And at times, when a request appears to be severely lagging, i check out PerfMon and see that the CPU is being dominated by sqlserver.exe, perhaps suffering increased execution because of not enough memory?
If anyone could provide a bit of insight, and/or some guidance on how to better use PerfMon to diagnose what's happening (i have Total Server Memory in my counter set but am a bit out of my league with all of the PerfMon options), and/or some strategies to improve the situation it would be greatly appreciated!
Thanks for your time.
April 13, 2009 at 11:02 pm
How big is the DB?
SSE has a DB size limit of 4GB.
April 16, 2009 at 5:36 am
May be the TempDB. It's better to put it in another hard drive or partition.
April 16, 2009 at 5:41 am
Hi Jesse,
Sorry to hear of your difficulties.
I would suggest you take a look at the Performance Tuning area of Brent Ozar's site. It has some excellent tutorials on Performance Monitoring and Troubleshooting SQL Server, including detailed guidlines on using Perfmon and which counters you should be looking to investigate.
http://www.brentozar.com/sql-server-performance-tuning/[/url]
Let me know how you get on or if you need any further assitance.
Cheers,
April 16, 2009 at 9:18 am
How big is the DB?
The database that is primarily in use for the application referenced above is currently 2.47GB. But it looks to me like the issue is present on other applications on the server where the database size is much smaller.
Did you use 64 bit version of SQL Server on your 64 bit OS, or there is some other configuration?
I guess i just assumed it was 64 bit, it came installed on the server. Where can i check this? In Server Properties, it says:
Product: Microsoft SQL Server Workgroup Edition
Platform: NT INTEL X86
Version: 9.00.1399.06
May be the TempDB. It's better to put it in another hard drive or partition.
I'm not sure what to say about this, never heard anything like this before. It is probably worth mentioning, though, that the system dbs (including TempDB) are on the system (C:) drive, where the application databases are on different drives.
Thanks very much to John Sansom, that looks like an invaluable link that we're going to explore excessively!
Since the original post, we haven't had a chance to do much on this and it looks like MSSQL$SQLEXPRESS:Memory Manager in PerfMon is showing a use of about 625MB, which is up from before. I think this is good. We're going to continue to monitor - including hopefully more effective monitoring with the link provided by John Sansom above - and we'll report back here when there's something interesting.
In the meantime, any further thoughts are much appreciated!
April 17, 2009 at 12:53 am
You have not mentioned how you created the database on the SSE machine. If you used scripts generated by SQL Server against the original DB then you may have an indexing problem.
These scripts fail to set the Fill Factor which, when you create an index defaults to 90%, but in the scripts is not set at all. for more info
http://msdn.microsoft.com/en-us/library/aa933139(SQL.80).aspx
I know this is version8 not 9 but I believe the same issue exists. Hope this helps
Ian.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply