Databases Reference
In-Depth Information
memory just to start. On our test server, Task Manager Mem Usage reports 49,604 KB. Connecting with
SQLCMD and issuing the same command makes that grow to 53,456 KB.
CPU
CORE 0
CPU
CORE 1
L1 Cache
64 KB
L1 Cache
64 KB
L2 Cache
512 KB
L2 Cache
512 KB
L3 Cache 2 MB
+
Figure 6-11
All this increased complexity results in a lot more code being run inside SQL Server to enable you to
do the same operations in SQL Server 2005 than in SQL Server 2000. It's not all bad though; additional
security features, for example, can take some of the stress away from having to build security into SQL
Server yourself and even though you might see an increase in the time it takes to compile a query it is
likely that the execution time will be faster. This means that the first time a new query is run, there is a
chance that it may actually take longer to run than in SQL 2000. However, future executions of the plan
from cache will execute considerably faster.
This is an excellent reason to use any feature that allows you to re-use a plan. After SQL Server has
taken all that time to figure a fast way to get you your data, re-use that plan as much as you can
before it's thrown away. This way you are making use of the time SQL Server took to figure out a great
plan for you.
All this extra code manifests itself as an increase in sensitivity to cache size. So the smaller the cache,
the slower you might run and vice versa. The result of this is that if you have a choice between two
processors running at the same speed opt for the one with the largest cache. Unfortunately, the increased
cache comes at a premium. Whether or not that additional cost is worthwhile is very difficult to quantify;
if you have the ability to run a test on both processors, this is the best way to determine the potential
Search WWH ::
Custom Search