Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 26, 1998

General Information - Page 9

By Sharon Dooley

25 Jan 98


Statistics handling is completely reworked. The optimizer can see that the statistics are getting out of date and will recompile them using the sampling technique I mentioned earlier. It is possible to compute statistics on a non-indexed column. The most common reason for doing this would be for composite key indexes. The statistics gathering is still only on the high-order (left-most) column of a composite key.

There are some significant changes to SQL Processing. First of all, there is no longer a separate procedure cache. The usage pattern dictates which pages are used for data and which for procedures. Plans are now shared only one copy is needed for many users. [I did not get a chance to ask what happens if the plans for two different users should be different based on input parameters or whatever.] There is a scheduler that can grant lots of memory to big queries. Internally the QP uses a PREPARE and EXECUTE strategy and caches SQL Statements. This is a big improvement over the current implementation of this feature in ODBC queries that result in filling up tempdb with temporary stored procs. There is a cache for ad hoc SQL. There can be some reuse of statements even if there isnt an exact match (parameter values, for example). This is expected to give a performance boost to ADO, RDO, and DAO apps that dont use stored procs.

Storage Architecture

The goal has been to completely separate the relational engine (query processor) from the storage engine and to have the QP communicate strictly through the storage engines OLE DB layer. This has not been perfectly achieved; there are still some calls to the storage engine directly from the QP.

The storage engine handles disk storage, memory management, I/O, concurrency control and logging and recovery.


Many thanks to Sharon for providing these notes - drop her a note at sharond@compuserve.com and tell her thanks!


MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM