Index is sorted based on the index keys. In order to keep the indexes sorted, some rearranging of data constantly occurs. Under normal conditions adding a new entry to the index involves simply adding rows to the leaf pages of the indexes. When there is no more space available in these pages, a new page is created and approximately half of the rows from the existing page are moved into this new index page. This is known as a page split. Page split is quite an expensive operation due to high IOs and page allocations required. If you know that your index will have constant updates or insertions, you can reduce the number of page splits that occur by leaving some extra space in the leaf pages of the index. This is done via the index fill factor.
Parameterizing SQL queries enables the database optimizer to reuse a previously compiled query plan. By this way it eliminates the need for recompiling it for next invocations of the same query with differing parameter values. If a non parameterized SQL statement is executed, SQL Server internally tries to parameterize the statement to increase the possibility of matching it against an existing execution plan. This mode of parameterization is referred to as simple parameterization.
SQL Server 2005 also introduces a new parameterization mode called forced parameterization. With forced parameterization, all non parameterized SQL statements, subject to certain limitations, are force parameterized, and unlike simple parameterization the likelihood of SQL Server 2005 parameterizing those statements is far higher.
This option can be used to control whether the queries in the database will be simple or forced parameterized. When PARAMETERIZATION is set to SIMPLE, SQL Server will try to parameterize queries using the simple scheme unless a query hint has been specified for a particular query to force parameterize it. Conversely, when PARAMETERIZATION is set to FORCED, all queries will be force parameterized unless a query hint has been specified for a particular query to parameterize it using the simple scheme.
ANSI Warnings option is used to determine the behavior of certain exception conditions. When set to TRUE in SQL Server Management Studio or ON when using the ALTER DATABASE command, errors or warnings are issued when conditions such as divide-by-zero occur or null values appear in aggregate functions.
When set to FALSE, no warning is raised, and instead a NULL value is returned. It is important to note that connection-level settings that are set by using the SET statement override the default database setting.
Address Windowing Extensions (AWE) is a set of extensions that allows an application to quickly manipulate physical memory greater than 4GB. The process of mapping an application’s virtual address space to physical memory under Address Windowing Extensions (AWE) is known as “windowing,” and is similar to the “overlay” concept of other environments. Certain data-intensive applications, such as database management systems and scientific and engineering software, need access to very large caches of data. Using the Address Windowing Extensions (AWE) API, the application can map the virtual window to any one of the physical regions. The application can reserve more than one virtual address space and map it to any of the allocated regions of physical memory, as long as the number of bytes reserved in the virtual address space matches that of the physical memory region. Address Windowing Extensions (AWE) usage is coded into the application itself. An application must have the Lock Pages in Memory privilege to use Address Windowing Extensions (AWE). To use AWE, you must enable the physical address extensions by adding the /PAE switch to your boot.ini file. Starting with Windows 2003 SP1, Windows automatically enables PAE on boot when more than 2 GB of RAM is installed. Some major benefits of AWE are:
- A small group of new functions is defined to manipulate AWE memory.
- AWE provides a very fast remapping capability. Remapping is done by manipulating virtual memory tables, not by moving data in physical memory.
- AWE provides page size granularity appropriate to the processor (for example, 4K on x86), which is more useful to applications than large pages (for example, 2MB or 4MB on x86).
Question: Why would you create more than one spatial index on the same spatial column in SQL Server 2008?
Answer: To set different tessellation parameters
Explanation: The primary reason for creating multiple spatial indexes on the same spatial column is to apply different tessellation parameters to the index. The tesselation process fits the data in the column into a grid area, allowing faster queries. Different parameters might result in grosser or finer grids applied.
Note: The Question is taken from SQLServercentral.com.
Microsoft had provided 64-bit support since SQL Srever 2000. So for SQL Server 2000 and SQL Server 2005 DBA always have a choice to choose the 32-bit version and 64-bit version. 64-bit supported hardware is primary requirement. As 32-bit SQL Server can be used on 64-bit architecture machine, so it becomes a matter of choice to choose between the two SQL Server architectures. There are some very specific scenarios in which it becomes highly recommended to use SQL Server 64-bit
- When number of concurrent users and sessions are in thousands
- Database is over 100 GB
- Database is heavily growing
- If going to buy new system, its better to migrate on 64-bit platform rather to adopt it later with more changes required.
Click here to read all posts related to Hardware and OS related to SQL Server