February 27, 2008 at 4:00 am
Hi All,
Queries response in our application is taking some time on first request only (usually 3 seconds) but if I re-run the same request within 5 minutes it replies in milliseconds (even if i change some query parameter it replies very fast on 2nd time execution). I can't show the entire schema as I am facing this behavior in all reports. The database is not very large only 13 GB, on average 300,000 records per table and all have sufficient indexes (I have verified them all by their estimation plans wrt all reports). All queries have WITH (NOLOCK) clause to bypass any locking since its a 24hr online database and have frequent data modification operations.
Server specification is as:
Windows 2000 SP4, SQL Server Std 2000 SP4, 2 Ghz XEON, 1.5 GB memory and 2 GB free space on all drives. Average free physical memory is 10-5 MB (as per Task Manager). Can anyone please help me what action should I do to get the same response which I am getting on every 2nd execution. Should we increase some RAM?
Regards,
Govind
February 27, 2008 at 8:24 pm
In the first run, an estimated execution plan is saved in the cache of your machine. When you have a second run, it will be faster.
February 27, 2008 at 9:09 pm
You'll also be caching data.
does this happen for all reports all the time? Like after every server restart or every new report or xx reports? It's possible that you don't have enough memory if this is moving across reports.
Once they're fast, are they fast for a long time?
February 27, 2008 at 10:42 pm
Many thanks for replies,
The 2nd time response for all raw queries + SPs are very fast for about 10-15 minutes. For stored procedures I can understand that SQL caches the estimation plan but why I am getting the same behavior for RAW queries (dynamically constructed queries from client side)? I have checked after 30 minutes SQL server is clearing its estimation plan's cache because all report are taking the same time as running 1st time. Is there any hardware related issue?
February 27, 2008 at 11:21 pm
Ad-hoc queries are also compiled and their plans cached.
The increased time may also be due to SQL populating the data cache. On the first read, data must be read from disk. On subsequent executes, it can come from the data cache, which is much faster.
You can look at the following perf mon counters:
SQL COmpilations/sec
SQL RE-compilations/sec
(both under SQL Statistics)
Buffer cache hit ratio
Page life expectency
(under memory management)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2008 at 1:00 am
Many thanks for quick reply, but I want to resolve this case as my clients are complaining about this.
Regards,
Govind
February 28, 2008 at 7:13 am
As Gail mentioned, you need to dig into what is happening. Run some counters, check the ones Gail mentioned and get their values before the first one, when they're fast, and up until they slow again.
February 29, 2008 at 1:48 am
As mentioned, once the query has been ran, the data that it retrieves is in the buffer cache, hence subsequent queries that retrieve the same data completes much faster, until such time that the data is aged out. Data retrieval is always faster from memory than it is from disk.
You can test this by first running your report. Then run DBCC MEMUSAGE (names, 50). That shows you the 50 largest objects in the buffer cache. Run the report again. Response time should be fast. Now run DBCC DROPCLEANBUFFERS. That clears the buffer cache. Confirm by running DBCC MEMUSAGE again. Now run the report again. The response time should be close to the time taken to run the report the first time.
If my reports were responding within 3 seconds, I would actually be quite happy. Then again, I don't know what your app does, so maybe the 3 seconds is critical. Here are some suggestions on how you can improve the first run response time:
- pre-fetch the most commonly used objects (tables and indexes) into the cache when the database starts up
- increase the memory on your machine, as a large buffer cache holds more data, and data is aged out less frequently
- look into using solid state disks to hold the database (http://www.mssqltips.com/tip.asp?tip=1389)
- look into improving your queries. Identify the queries with the largest logical reads, and see if you can improve them. Look at the actual execution plans, not just the estimated one. Statistics may be out of date, which causes SQL Server to choose a sub-optimal plan.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
February 29, 2008 at 3:10 am
Great tips, I will check the DBCC command for confirmation, have never used this command for memory related issues. Will update you soon
Thanks you very much for replying
Govind
March 17, 2008 at 4:57 pm
You could eventually pin the table in cache (DBCC PINTABLE) but be sure first there'is enough RAM available for this... (all the advices above)
Virgil Rucsandescu
March 17, 2008 at 10:49 pm
Thanks dear,
Unfortunately I'm running out of memory on server that's why the server is pulling the tables in its cache but this command is certainly helpful in other case was not aware of this command 🙂 Thank you but I am in process to increase some RAM that will solve my problem Many many thanks for giving your reply.
Regards,
Govind
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply