SQL Express 2005 query times lagging
I execute some simple queries on my own system's sql server express 2005, the executions time is under 1 second but when I execut the same queries on our server's sql server express 2005, the queries execution time is about 3 seconds or more and I don't know why. both of the systems have the same OS (win 2003 server), but our server have 1Gig ram and my system have 2 Gig.
This difference in RAM can be the major reason for query response time?
Please help me in this case if you know the reason.
Best regards
Reza
Software/Hardware used:
Software/Hardware used:
ASKED:
September 4, 2010 7:40 AM
UPDATED:
September 15, 2010 6:27 PM
Answer Wiki:
Odds are the problem is because the production server already has the data in cache and your workstation's SQL Express doesn't have the data loaded into the cache. If you run the same query a second time on the SQL Express instance does it run quickly then? If it does this is the problem.
The buffer cache is the space in memory which is allocated to the SQL Server which the SQL Server instance uses to hold data so that it can access it more quickly than if it needed to access it from the hard drive.
Another reason might be that your SQL Express instance doesn't have much RAM allocated to it. Because it is on your workstation or laptop (I'm assuming that it is) you have other things running which aren't on the server so there is less memory available for SQL Server.
To see all answers submitted to the Answer Wiki: View Answer History.
Just to share if you run SQL 2005 Enterprise, it will be better if your Raid is 1+0 as it will perform faster.
Windows 2003 itself takes quite a lot of ram and if installed with SQL the server will for sure lag as there is not enough memory. currently my side we have 4gb of ram and a single cpu and running on raid 5 and the performance is quite bad too..when executing a query on server or from SQL Express the reply will be quite slow too…once running the amount of ram will only have a balance about 512mb left too..
this is just to share what i know
Hi,
thanks for your answers. I increased the server’s RAM up to 2 Gig, and set the minimum server memory used by sql server 1024 MB.
still I have this problem, what am I should to do?
Lots of possibilities here…
1. Processors – what are the differences between the two systems (CPU speed, # of cores, # of sockets).
2. Disks – size, speed (RPM), connection type, and even fragmentation?
3. Contention – is the “server” running other processes? Backups? Log shipping?
4. Anti-virus scanning – turn off AV scanning on the data files used by SQL Server. This is often a performance killer.
Also – as MrDenny notes, have you run the queries multiple times to ensure that this isn’t just an initial caching problem?
As I Mr Denny said, when I executed the same query some seconds later, it runs quickly.
also, I checked the buffer cache hit ratio from administrative tool -> performance, and the average of buffer cache hit ratio is 99.860.
The buffering cache is the main problem? and if yes what am I should to do?
Thanks for your answers
All databases will have performance issues with queries that run against infrequently accessed data. The database engines cache information as it is accessed, on the assumption that information previously accessed is likely to be accessed again.
If you infrequently run a query that is accessing information not otherwise being accessed, then it is likely that none of that information (or very little of it) is currently in the database engine’s cache buffers. That means that the database engine needs to do physical disk reads of the index data as well as the data records.
If your tables aren’t too large, you could try running a simple “select * from table” command to preload the cache with data. Older versions of SQL Server supported the ability to “pin” a table in the cache, but that was discontinued in SQL Server 2005.
You could just live with the initial slow query – it will be faster after the first run. If it is critical that the first query is fast, then you probably need to run something periodically to refresh the data in the cache.