What’s NEW in 15.7++
In Version 15.7 ESD #2
1.
Deferred Table
Creation
create table...with deferred_allocation allows you defer the page allocation for a table. The with deferred_allocation parameter for the create table command lets you defer page allocation for a table. Deferred tables help applications that create numerous tables, but use only a small number of them. Tables are called “deferred” until Adaptive Server allocates their pages.
System tables include entries for deferred tables. These entries allow you to create objects associated with deferred tables such as views, procedures, triggers, and so on..
(It’s like the create database for load option but here the
table is online and accessible)
2.
Online Utilities
Adaptive Server versions 15.7 ESD # 1 and later include
an online parameter for reorg
rebuild that lets you reorganize data and perform maintenance on tables
without blocking users data from users.
3.
Merging and
Splitting Partitions
Over time, a partition’s data distribution may become
skewed, or the manner in which the data was originally partitioned may not suit
current business requirements. Use alter table to
merge, split, or move partitions to redistribute the data and revive the
performance benefits of using partitions.For example:
·
Splitting partitions – a
company divides data into four partitions according to regions —North, South,
East and West— so customer representatives have fast and efficient access to
their regions’ customers, independent of other regions. If sales increase in
the Southern region and the customer base has expanded significantly, frequent
queries involving partition scans and maintenance operations may cause the
South partition to be slow and inefficient, losing out on the benefits of
partitioning the customer data. In this situation, splitting the data in the
South partition into two partitions, South-East and South-West, may revive
performance without affecting the data in other partitions.
·
Merging partitions – a
company’s sales data is partitioned into the four yearly quarters—Q1, Q2, Q3,
and Q4. At the end of the year, the company merges the data for the year and
archives it. Merging partitions that represent a closed financial year is
efficient because sales’ data for a past year is accessed infrequently, and the
older data is most likely to be read but not updated.
4.
Maximum Size of Query in the Statement Cache
Adaptive Server versions 15.7 ESD #2 and later allow you
to store very large SQL statements. You can save individual statements of up to
2MB (for a 64-bit machine) in the statement cache.Versions of Adaptive Server earlier than 15.7 ESD #2 had a 16K limit for individual statements stored in the statement cache, even if statement cache size was configured with a larger size.
5.
Fast-Logged Bulk
Copy
Adaptive Server version 15.7 ESD #2 and later allows you
to fully log bcp in fast mode, which provides faster
data throughput and full data recovery. Earlier versions logged only page
allocations.
Use the set logbulkcopy {on | off }
command to configure fast-logged bcp for the
session. You may include the set logbulkcopy {on | off }
with the --initstring 'Transact-SQL_command' parameter, which
sends Transact-SQL commands to Adaptive Server before transferring the data. For
example, to enable logging when you transfer the titles.txt data into the pubs2..titles table, enter:
bcp pubs2..titles in titles.txt --initstring 'set logbulkcopy on'
You must enable select
into/bulkcopy/pllsort on the database before issuing fast-logged bcp; otherwise, bcp uses slow mode.
6.
Concurrent dump
database and dump transaction Commands
Adaptive Server versions 15.7 ESD #2 and later allow a dump transaction command to run concurrently with a dump database command, reducing the risk of losing
database updates for a longer period than that established by the dump policy.
7.
Hash-Based Update
Statistics
Adaptive Server versions 15.7 ESD #2 and later allow you
to gather hash-based statistics on minor index attributes and unindexed columns
instead of using sort-based statistics, significantly reducing elapsed time and
resource usage. Using hash-based statistics improves performance by reducing
the number of required scans, and avoiding disk-based sorting.
Hash-based
statistic allow greater flexibility than sort-based statistics:
·
Running hash-based statistics should require less time,
increasing the amount you can accomplish during a maintenance window.
·
Because hash-based statistics require less procedure
cache, you may be able to run update statistics on a data-only-locked
table outside a maintenance window, since the Adaptive Server tempdb buffer cache (which
typically uses the default data cache) is typically much larger than the
procedure cache, reducing the impact of update statistics.
·
Hash-based statistics do not generally require large tempdb disk allocations. If you
previously increased the size of tempdb to accommodate large sorts from update statistics, you may be able to
redeploy this space.
·
update [index | all] statistics with hashing may run faster
than update [index | all] statistics with sampling. However, an
exception may be update statistics table_name(col_name).
·
update statistics table_name (col_name1),
(col_name2) . . . with hashing allows you to collect statistics on several
columns with a single scan instead of several scans.
8.
Enhancements to
dump and load
Adaptive Server 15.7 ESD #2 includes enhancements to the dump
and load commands, which make it easier for you to back up and restore
your databases.
The
enhancements include:
·
The dump configuration command allows you to back
up the Adaptive Server configuration file, the dump history file, and the
cluster configuration file.
·
Dump configurations define options to create a database
dump. Backup Server then uses the configuration to perform a database dump. You
can use:
o The dump configuration to
create, modify, or list dump configurations, then use dump database or dump
transaction with the configuration.
o The enforce dump
configuration configuration parameter to enable dump operations to use a
dump configuration.
o The configuration group
"dump configuration," which represents user-created dump
configurations.
·
Dump history:
o Preserve the history of dump
database and dump transaction commands in a dump history file that
Adaptive Server can later use to restore databases, up to a specified point in
time.
o Read the dump history file
and regenerate the load sequence of SQL statements necessary to restore the
database.
o Use sp_dump_history
to purge dump history records.
o Use the dump history
update configuration parameter to disable default updates to the dump
history file at the end of every dump operation.
o Use the dump history filename
configuration parameter to specify the name of the dump history file.
·
Dump header – New options to the dump with listonly
command:
o create_sql – lists the sequence of disk
init, sp_cacheconfig, create database, and alter database
commands required to create a target database with the same layout as the
source database.
o load_sql – uses the dump history
file to generate a list of load database and load transaction
commands required to repopulate the database to a specified point in time.
9.
alter table drop
column without datacopy
Adaptive Server versions 15.7 ESD #2 and later add the no datacopy parameter to the alter
table ... drop column command, which allows you to drop columns from a
table without performing a data copy, reducing the amount of time required for alter table ... drop column to run.
10. User-Defined
Optimization Goal
Adaptive Server versions 15.7, ESD #2 and later allow you
to create user-defined optimization goals.
User-defined
optimization goals allow you to:
·
Create a new optimizer goal
·
Define set of active criteria
·
Activate the goal at the server, session, procedure, and
query level
·
Dynamically change the goal content, without
disconnecting and reconnecting the client session
Once you create the user-defined
optimization goals, you can invoke them at the server level or for a user
session.
11. Shared
Query Plans
Adaptive Server versions 15.7 ESD #2 and later allow you
to share query plans, which are cloned from primary query plans, avoiding the
need for Adaptive Server to create or recompile query plans that are identical
to existing plans.You should see a performance improvement as Adaptive Server shares query plans instead of reusing or recompiling them. You may see a slight change to procedure cache memory usage as primary query plans are pinned in the cache while Adaptive Server uses their shared query plans.
12. In-Row
Large Object Compression
Adaptive Server versions 15.7 ESD #2 and later support
in-row large object (LOB) compression.
In Version 15.7
Compressing Data in Adaptive Server
You can compress large object (LOB) and regular data.
After you create a compressed table or partition, Adaptive Server compresses any subsequently inserted or updated data (that is, existing data is not already compressed). If Adaptive Server cannot efficiently compress the inserted data, the original row is retained. If newly inserted or updated LOB data occupies space that is smaller than or equal to a single data page, Adaptive Server does not compress this data.
You need not uncompress data to run queries against it. You can insert, update, and delete compressed data; running select or readtext statements on the compressed column returns decompressed rows. Because there is less data for Adaptive Server to search, there are fewer I/Os, improving the efficiency of data storage.
