February 25, 2009 at 1:44 am
Hi,
in one of our servers, when we try a select query on a table, which has 5700 rows, it takes around 20 seconds to return the result.
there's much free space on the server and tempdb size is big enough besides, it has 3GB RAM.
What may cause such low performance?
February 25, 2009 at 2:55 am
- size
- distribution
- index organization
- memory usage
- paging activity
- other processes on the server
- locking
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 25, 2009 at 3:06 am
peace2007 (2/25/2009)
What may cause such low performance?
Is this the only poor-performing query or you are seeing poor performance all across the board?
Check execution plan, be sure query takes advantage of available indexes.
Trace the query and check for wait events.
_____________________________________
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.February 25, 2009 at 3:31 am
peace2007 (2/25/2009)
Hi,in one of our servers, when we try a select query on a table, which has 5700 rows, it takes around 20 seconds to return the result.
post the query
there's much free space on the server and tempdb size is big enough besides, it has 3GB RAM.
What may cause such low performance?
Before looking into this first lets tune the query, to check whether you are returning unwanted rows, to check whether the query is using indexes or out of date, to check whether the statistics are up to date. Fine tune your query as much as possible before worrying about the hardware resources.
February 25, 2009 at 4:13 am
Hi,
when we try a select query on a table, which has 5700 rows, it takes around 20 seconds to return the result.
Hi,
The table has only 5700 rows.so Its not very big.
Don't always take the Query running time only. Because (Some heavy Traffic,Locking,Blocking....)something happen in ur server the query running slow.
Before u running the select statement Set statistics io on and see the Scan count , logical reads , physical reads , read-ahead reads.
(As per MS Small Values are better)
Muthu,
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 25, 2009 at 4:31 am
Check the performance in below sequence.
> Is joins are correct in the query
> check for subquery or corelated query in your query
>what are the other process running at that point . use sp_who or sysprocesses in master
>other processes or application in the server
February 25, 2009 at 5:21 am
Is it actually a table... or is it actually a view?
--Jeff Moden
February 25, 2009 at 6:05 am
Also, are there any user defined functions in the query?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply