September 3, 2009 at 2:43 pm
I've been having an issue with query speed that our IT dept can not solve.
I ran a simple query (select count(*) from table), and the query took over 8 minutes to complete. When I ran the query 15 minutes later, the results were returned in under 1 second. There were no other users running any other queries on the server at the same time. And I was only running the one query.
Our server has 3 gigabytes of memory available. This time delay happens periodically. Sometimes it is solved by rebooting the server.
Thoughts?
September 3, 2009 at 4:25 pm
I'd suggest capturing the execution plans to see what is occurring.
However, it sounds like the data was being moved into cache. You can pretty frequently see a query run much faster the second time it's called because the data needed has been moved off the disc and into memory.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2009 at 7:43 pm
run following commands to clear your cache
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
Run your query and inform us how much time does this take? Also use task manager and job monitor to see what is running in the backdrop. Sometimes we don't realize but windows backup, sql jobs, sql backup etc is running and our query execution time is impacted.
-LK
September 4, 2009 at 3:59 am
luckysql.kinda (9/3/2009)
run following commands to clear your cacheDBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
Run your query and inform us how much time does this take? Also use task manager and job monitor to see what is running in the backdrop. Sometimes we don't realize but windows backup, sql jobs, sql backup etc is running and our query execution time is impacted.
-LK
But not on the production box, right. This will cause serious problems on a production machine. It'll be like rebooting. All the execution plans will be cleared and all the cached data will go away. You'll notice this situation on most production servers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2009 at 4:14 am
Yep be very careful before you run that on a production box. It can help in extreme circumstances but like Grant has mentioned it impacts on performance considerably.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 6, 2009 at 6:48 am
im quite sure that you server has performance problems releted with slow IO or not enough memory. you shoud check the following perfmon counter: SQL Server : Buffer Manager : Page Life Expectancy (average during business hours should be greater then 300)
September 7, 2009 at 3:15 pm
1) did you check to make sure that no agent jobs were running, like backups, checkdb, index maint, etc?
2) what else was happening on the server? on the IO subsystem?
3) did you do sp_who2 active to VERIFY no blocking?
4) it could be just that pulling the data from disk was very slow because it is a large table without a non-clustered index and you have a slow IO subsytem. 3GB memory is a very small amount these days. Pulling the same data from RAM can be virtually instantaneous, even for very large amounts of data
5) did you verify that no one ADDED a non-clustered index between the first and second runs?? 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 7, 2009 at 3:32 pm
lallen (9/3/2009)
I've been having an issue with query speed that our IT dept can not solve.I ran a simple query (select count(*) from table), and the query took over 8 minutes to complete. When I ran the query 15 minutes later, the results were returned in under 1 second. There were no other users running any other queries on the server at the same time. And I was only running the one query.
Our server has 3 gigabytes of memory available. This time delay happens periodically. Sometimes it is solved by rebooting the server.
Thoughts?
This is not a problem, this is expected behavior.
Welcome to the wonders of buffering 😉
When you reboot the server you are clearing memory cache.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 9, 2009 at 9:08 am
Thanks for the good responses. I will still be battling this issue, but have some hints on how to solve it.
Our company is small and we have only 2 people using the server at any one time (no production box). There is some other issue to solve.
Thanks again.
September 10, 2009 at 8:09 am
SELECT top 1 rows FROM SYSINDEXES WHERE Id = OBJECT_ID('TableName')
its return the row count of the table which is same as select count(*) from table.
AshokKumar
September 10, 2009 at 8:52 pm
please try this
select count(1) from table With(Nolock)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply