SQL Server 2000 Preview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Storage-Engine

• Distributed partitioned views – can be declared over different servers. (Used to get world record tpcc figures). Only in Enterprise Edition. Updateable and DTC handles the two-phase commit. Can now have disjoint constraints on them. Run-time as well as compile time parameters are now used to decide which underlying tables to go against.
• Sort-order/collation down to column level. Easier for server consolidation as sort-orders don't dictate separate boxes. Can be declared at the database level and down at the column level. Comparison semantics are done as per ANSI (SQL-99) standards.
• Descending indexes on a per column basis
• Indexes on computed columns as long as the computed column is deterministic. E.g. a datepart for month. Getdate() is an example of a non-deterministic function as it will return different results depending on when you run it.
• Indexes on views (otherwise called materialized views or summary tables). Updates to base tables will be updated in these automatically – the contents are persisted. Views can have joins, summarization etc. The index is maintained automatically like any other. The optimizer can use the index on the view even if the view itself is not directly accessed. It is also possible to use an index hint. Indexed view may be quite large.
• Only deterministic functions may be used in an indexed view. Note that an implicit string to date conversion is non-deterministic as it is dependent on the language.
• SET options – e.g. arithabort, concat_null_yields_null can make most expressions deterministic. Therefore arithabort, concat_null_yields_null, quoted_identifier, ansi_nulls, ansi_padding, ansi_warning must all be on. Numeric_roundabort must be off. If these are not set then index won’t create. If you try to update/delete/insert the view and these options are not set identically then it will also fail. If the options aren’t set for a select then the index won’t be used.
• Arithabort is not set correctly by OLE-DB/ODBC etc. automatically. Therefore a good idea to set it on globally if using indexed views using sp_configure ‘user options’, 64.
• Because a view is persisted, columns in the underlying table cannot be dropped/modified. Therefore a view to be indexed must be created with the WITH SCHEMABINDING option. Then table can’t be altered/dropped without the view being removed first.
• All tables and the view must be in the same database. To create the view the user must have at least REFERENCES permission to all the underlying tables. The view creator must own all the underlying tables.
• The first index on the view with schemabinding must be clustered and unique.
• View cannot have subqueries, outer joins, self join, distinct, union. Any of these will cause the index creation to fail.
• Use NOEXPAND hint if you want to force the use of an index on a view. If the index can’t be used then the query will fail.
• OPTION (EXPAND VIEWS) as a hint means that no indexes on a view will ever be used.
• Max Async IO auto-tunes itself, increasing itself for both reads and writes as the workload requires it.
• Transaction log will truncate immediately if possible. Same with shrinkfile for data. Will either shrink straight away or tell you why it can’t. When shrinks writes no-ops to end of virtual log and removes the ones after and moves it to the front of the log. Then if you do a backup and shrink again it will get rid of the rest.
• Add/delete file from filegroup is now a recoverable operation
• Multiple logging levels – no more select into/bulkinsert etc. type parameters.
• DBCC will not take any blocking locks – just takes a "schema stability lock". Only DDL changes will be blocked. Can still create non-clustered indexes. Can still take tablocks if you ask as an option. Takes a 15% hit to a medium workload. For a full dbcc check needs tempdb space – use ESTIMATE_ONLY options to see how much tempdb it will use.
• DBCC added ‘physical_only’ check – doesn’t check catalog info and runs even faster. Picks up most hardware related errors.
• Small blobs/text can be held in the data-row giving extra performance and reducing the need for another set of i/o’s to retrieve. If it cannot be held in the row then the root text pointer is held there instead to boost performance. This is settable per table – default off. Also the max size of the text field to be held in-row is settable – default 256 bytes. If you rarely access the text field then don’t turn this option on as the rows being longer will mean larger indexes and longer scan times for accessing the non-text information.
• Generic deadlock detection implemented. Deadlocks on internal resources like locks, threads and memory will also return 1205 errors. Allows more queries to be parallelised.
• User interface to lock manager for user locking schemes. Sp_getapplock with parameters. Can be set to release locks at end of transaction or end of session if not explicitly freed.
• Recovery performance much faster – uses read-ahead on the log.
• Group inserts can now be done in parallel
• Deferred commit option – like in tempdb – to reduce overhead of logging (if you want to do it)
• There could be a few more virtual tables telling us a lot more of what is going inside SQL Server like I/O per file, per filegroup, per table, per index, CPU usage per thread (user) in SQL Server, locks per object, current activity pictures, operations per object (# of inserts,updates,deletes,selects) etc - more detailed goings on for high-end database
• Address Window Extensions (AWE) in Windows 2000 allows addressing of 64GB ram. Pages in the windowed cache are mapped into the 32-bit 4GB address space.
• On-line reorg. Within a page and page-ordering. Does both data and index pages. Re-aligns pages in logical key order (helps with scan speed). Moves rows to re-establish desired fill factor. Fully logged and recovered. Improved tool to analyze and report fragmentation. < 20% overhead when running. Doesn’t move pages between files. Only does one index at a time.
• Partitioned View (multi-node) – query and update transparency. Have to create tables etc. on each node manually.
• Scans – can share scan i/o to reduce contention. Called "merry-go-round" scans.
• Some reduction in unnecessary locking to reduce deadlocks.
• Expression push. QP pushes down some search criteria into the storage engine and it evaluates it against the rows as it returns them. Improves performance.
• Improved parallel scan. Feeds 16 pages at a time.
• Read-ahead more scalable. Uses 1% of available memory or 2000 buffers whichever is smaller.
• Checkpoint processing improved. Fewer checkpoint i/o’s so faster and less overhead on busy systems.
• Similar Bucket Merge (SBM) algorithm for index stats gives dynamic bucket boundaries based on variances. Better handles data skew and captures cardinality and density stats in one pass improving performance for gathering statistics. Optimizer still understands 7.0 stats for upgraded databases but will then gradually auto-update them to the new format.
• DBCC Showcontig has a new "fast" option to run a bit faster – avoids reading leaf level pages. DBCC indexdefrag – defrags indices in the background.
• DBCC showcontig now has a tableresults option to get it back in table format. Optional check of call index levels. Optional check of all indexes.
• All DBCC’s all show/accept real object names not just object id’s.
• Create Index no longer blocks a backup from happening. Nothing interferes with backup running.

SQL Server 2000 Preview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19