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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 25, 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

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date