August 30, 2006 at 9:26 am
Have any of our forum member experienced SQL Express specific performance problems?
We recently ran into a stored procedure performance problem on SQL Express.
We have a long running stored procedure (encapsulated business logic) which takes about 10-15 seconds to run on SQL Server Standard, but up to 3 minutes on SQL Express. The SP code is exactly the same on both servers.
Both SQL Standard and SQL Express have pretty much the same setup and configuration and both ran on a local laptop. We have set the minimum server memory to the same amount on both servers.
On perfmon, I noticed extensive contentions on cursor memory, average disk waits, and locks (the latter two might be the consequence of the first) from SQL Express, but not from SQL Server Standard.
I noticed that this level of performance difference is closely related to the way SQL Express uses database cache.
I ran the following:
-----------------
dbcc cachestats
go
select * from syscacheobjects
go
dbcc proccache
go
----------------
On SQL Server Standard, cache is used extensively. Syscacheobjects shows thousands of entires, including lots of compiled plans for the cursors (used in the stored procedure). On SQL Express, no cursor is cached as complied plan. Also, you basically only have less than 10 entries in Syscacheobjects.
proccache result also shows big difference.
On SQL Server Standard:
num proc buffs num proc buffs used num proc buffs active proc cache size proc cache used proc cache active
-------------------- -------------------- --------------------- -------------------- -------------------- -------------------
18962 29 29 2291 6 6
On SQL Express:
num proc buffs num proc buffs used num proc buffs active proc cache size proc cache used proc cache active
-------------------- -------------------- --------------------- -------------------- -------------------- -------------------
240 158 158 8 3 3
My question:
1. Why SQL Express uses cache in a different way? In this case, it is clear that the insufficient use of cache is the main cause of performance problem.
3. Is it likely a “designed” feature of SQL Express or likely a bug?
4. I read from Microsoft web site that SQL Express has a limitation of 10 concurrent connections. What if the number of connections exceeds10? Will additional connection be rejected? (Open multiple monitoring tools against SQL Express could quick bump the concurrent connection number to 10)
Thanks a lot!
February 1, 2008 at 4:04 am
Hey,
Yea we are experiencing timeouts now as a result of an upgrade from MSDE to SQL Express. As a result of this we are seriously considering putting off our upgrade of 1000+ laptops. On the laptops there are 2 instances of SQL express running currently with a total of 2 databases. I am increasing the command timeout value on one of our applications to prevent this happening. I havent seen any suggestions elsewhere on how to overcome this problem. Maybe creating more indexes or something. Ideas welcome!
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply