Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2007-08-02 : 16:43:02
|
| Hi allI'm just testing some of the query from various clients machine from different location to see the performance of this query. I used the same query when I tested to make it consistent. First testing on Location A with the same SPID (through the same query analyzer), I got the 50s, then 12s, 6s, 7s, and 6s.Open up another instance of query analyzer, the result is around 6s.My question is the first query that it run, it needs somehow it took longer time cause there is no cache. How does it work anyway? Obviously, the cache also doesn't looked SPID more like query itself.Thanks |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-02 : 17:53:55
|
| >>My question is the first query that it run, it needs somehow it took longer time cause there is no cache. How does it work anyway? Obviously, the cache also doesn't looked SPID more like query itself.Yes when you run a query, SQL Server first looks int he cache to see if there is an existing queryplan that it can use, If it doesnt find it, then it computes the query plan at that time with the parameters provided and cache's it. So any subsequent calls the proc/TSQL will prbly utilize the same query plan unles the paramerers are so significantly different (in terms of logic in your T-SQL) that necessitates the creation of another query plan. SQL Server also cache's data. so your subsequent calls could simply be reads from the data in the cache and not even involve executing the query.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
dewacorp.alliances
452 Posts |
Posted - 2007-08-02 : 18:08:07
|
quote:
Originally posted by dinakar
>>My question is the first query that it run, it needs somehow it took longer time cause there is no cache. How does it work anyway? Obviously, the cache also doesn't looked SPID more like query itself.Yes when you run a query, SQL Server first looks int he cache to see if there is an existing queryplan that it can use, If it doesnt find it, then it computes the query plan at that time with the parameters provided and cache's it. So any subsequent calls the proc/TSQL will prbly utilize the same query plan unles the paramerers are so significantly different (in terms of logic in your T-SQL) that necessitates the creation of another query plan. SQL Server also cache's data. so your subsequent calls could simply be reads from the data in the cache and not even involve executing the query.
So is there any setting in SQLServer that can control this cache? Or more like internal process stuff. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-02 : 18:22:42
|
| What do you intend to do..?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
|
|
|
|
Query Question