SQL Express 2005 query times lagging

25 pts.
Tags:
SQL Express 2005
SQL queries
SQL Server
SQL Server performance
SQL Server Query
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • jinteik
    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
    17,985 pointsBadges:
    report
  • Reza8383
    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?
    25 pointsBadges:
    report
  • Kccrosser
    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?
    3,830 pointsBadges:
    report
  • Reza8383
    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
    25 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following