Hardware optimization with sqlserver(part 1)
Just as a plant can reach its normal size and bear mature fruit until the land and weather conditions are suitable, SQL Server is totally dependent on the quality of the hardware on which it is installed.
SQL Server is by nature a major consumer of natural resources. Management system database needs good performance storage subsystems , with whom he works a lot, not only to write and read data sometimes in large volumes , but also to maintain transaction logs and tempdb the database that collects temporary tables , internal as well as versions of rows in row versioning features worktables. RAM is also sought. SQL Server must keep a few things in the virtual address space (the virtual address space , or VAS) operating system . In the VAS , SQL Server raises the memory users sessions, locks , cache execution plans , memory query execution and data cache. The query execution can be very consuming of RAM: the runtime must pass an operator implementation plan to the other with rows fetched , and sometimes a set of rows for processing in once ( to perform a sorting or a hash table , for example ) . These operations are also consuming processor.
All components are therefore to be considered. In a system handling a reasonable volume of data and provided enough RAM, the speed of disk subsystem is not the most decisive criterion because most data will be read from the data cache in RAM. In all cases, the greater the amount of RAM is large, the better the performance. Regarding the CPU, a multiprocessor system is now indispensable. SQL Server uses extensively multi-processing, distributing its requests to worker threads, or by parallelizing the same query if the activity of the machine is light enough at execution time.Processo(s)
It is obvious, SQL Server actively using multi-processing. Do not hesitate to build a machine for eight or more processors. Standard Edition supports only four CPUs. This is four physical processors (four sockets). If you use multi-core processors (dual or quad), it increases the number of processors supported by this edition. Similarly, the mode license per processor of SQL Server is meant by socket. For four quad-core, so sixteen logical processors, you pay a license of four processors.Parallelism
Operations databases, including the generation of the execution plan and data retrieval are usually processor-intensive. The servers are now increasingly multiprocessor and processors (including Intel) Modern architectures include multi-threading and multi-core, enabling them to work in parallel. Two scenarios are possible: parallel execution of different applications or parallel execution of different parts of the same query. This second scenario is called parallelization of a query. The decision to parallelize an application is made in real time by the relational engine, according to the current system status: If the activity is low, and the query is estimated cost (long), SQL Server will be more inclined to parallelize that if the application is simple and the system is responsible for applications of small queries.