Improve SQL Server Performance

Challenges

You may face one, or more, of these time-consuming issues:

• Unloading and loading large tables
• Routine utility operations (reorgs)
• Complex queries
• Database migration or replication
• Test data generation

Specific performance diagnoses and tuning also take time, and may affect other users. Finally, stored SQL procedures may also be programmed inefficiently, require optimization, and then still take too long to run.
 

Solutions

Relieve the database of some processing overhead by leveraging file system I/O and the transformation power of CoSort. Accelerate unloads (and reorgs) by dumping table data through SELECT * statements to flat files without encumbering qualifiers like distinct, order by, and group by.

Once your data are in flat files, use CoSort's SortCL tool to de-dupe, sort, join, group (and report on) the extracts in parallel. You can also leverage SortCL for database replication, table simulation, and field-level protection (for sensitive data at risk).

CoSorting the files can also improve load and index speeds. For performance reasons, almost every SQL Server table should have a clustered index. Microsoft recommends creating the clustered index first, and pre-sorting the input files (on the primary index key) to shorten the create index step. Use the SORTED_DATA option when creating indexes.

CoSort's SortCL program can also transform and reformat data for database migrations and custom (detail, summary and delta) reports.

To rapidly populate SQL Server or SSIS ETL operations with safe data, consider using IRI's high-volume test data generator, RowGen. RowGen uses your data models to automatically generate the test data for an entire database with referential integrity.