Learn how to solve reoccurring performance problems during data migration to SQL Server.
A Performance engineering engagement has been conducted for one of the leading automobile insurance companies in Canada. As per the customer decision to reduce the operational and maintenance expenditure, the data residing in different data stores such as DB2 and Oracle is transferred to a consolidated repository maintained in SQL Server 2008. The existing Java client applications communicating with either DB2/Oracle 10g are remediated to communicate with SQL Server.
Though the modified environment could meet the functional expectations, there were some setbacks in the application behavior in terms of performance and scalability.
It was observed that most of the performance issues addressed as part of this exercise could be quite common across any data migration project to SQL Server. The basic idea of preparing this artifact is to address these reoccurring performance issues, thus saving the potential risks & execution effort.
Performance Sensitive Areas in Migration
The points mentioned below are the performance sensitive areas that need to be monitored on data migration to SQL server.
* If the client is Java related, communication between client and SQL Server
* Row level triggers in SQL Server
* Referential integrity through triggers in SQL Server
* Multiple after triggers and rollbacks
* Recursive and nested triggers in SQL Server
* Extended stored procedures
* Nested cursors
* Case-sensitivity and collation
Each of these topics will be discussed in detail.
Communication Between Java Client and SQL Server
The end to end transaction from java client to SQL server was taking more than 20 min to respond. Though Java client has inline queries (dynamic SQL), the same transaction was taking around 5 sec in the existing application.
Below are the observation made:
* The SQL trace captured depicted that most of the queries execution time was higher along with the high disk reads and writes.
* The queries execution time was higher even when fired from the backend.
* Table scans were occurring for most of the queries.
On remediating the necessary indexes manually on SQL Server, table scans and query execution times triggered at the back end are minimized. This could reduce the transaction time from 20 min to around 8 min, still way ahead of the SLA.
Apart from the missing index issue identified, the transaction was still taking more time to execute when triggered from the front-end. The pattern below has been noticed in the SQL trace when queries are being fired using JDBC drivers for the time consuming transactions.
From this pattern, it is obvious that communication between the Java application and SQL Server is happening using Unicode (NVARCHAR in red)
There could be a performance problem with respect to communication between a Java client and the SQL Server using Java drivers. Most of the Java drivers pass string parameters to SQL Server as Unicode, by default. The problem here is, if the Unicode parameters reference VARCHAR key columns in an index, SQL Server engine will not use the appropriate index for query resolution, thereby increasing the unwanted table scans.
This can be corrected by resetting one of the default parameters in the Java driver. The parameter name and value to be set might vary from driver to driver, depending on vendor.
The below statistics are captured by running one of the queries used by the transaction, with both "sendStringParametersAsUnicode" as true and false:
By making this change the transaction that was taking around 8 min reduced drastically to 5 sec.
Row Level Triggers in SQL Server
One of the major differences between Oracle and SQL Server triggers is that the most common Oracle trigger is a row-level trigger (FOR EACH ROW) that initiates for each row of the source statement. SQL Server supports only statement-level triggers, which fire only once per statement, regardless of the total number of rows affected. The basic conversion rules used by SSMA for Oracle for triggers conversion are (As per SSMA for Oracle conversion guide):
1. All BEFORE triggers for a table are converted into one INSTEAD OF trigger.
2. AFTER triggers remain AFTER triggers in SQL Server.
3. INSTEAD OF triggers on Oracle views remain INSTEAD OF triggers.
4. Row-level triggers are outdone with a cursor loop.
Each trigger initiated acquires a lock on the table and is released once the action is complete. Even with the least resource intensive cursor configuration (FAST FORWARD, READ ONLY), the performance of the trigger might be affected by the following:
* Number of rows iterated through the cursor
* SQL statements against other tables external to the inserted or deleted tables.
Locking issues or hanging of batch execution has been observed at many instances in the transaction flow, for row level triggers with cursors.
Microsoft also recommends minimizing the cursor usage whereever applicable, due to their high resource consumption nature. The best practice is to keep the trigger logic simple. If the business case is to loop across the modified rows inside the trigger, table variables (temp tables) or row set logic would be preferred over cursors.
Referential Integrity Through Triggers in SQL Server
Referential Integrity is the feature provided by RDBMS to prevent the entry of inconsistent data. Referential Integrity can be imposed in SQL with two mechanisms:
1. Data Referential Integrity (DRI) constraints imposed through foreign keys
Imposing DRI by means of triggers might lead to prolonged actions in triggers and locking issues. The life of the transaction might be extended on any integrity violation that requires rollback. This is explained in the below Customer-Order relationship sample:
On deleting the customer record, locking issues would be detected as depicted below:
Recursive Execution in Nested Triggers
There are two kinds of recursion in SQL Server
* Direct recursion:
This type of recursion occurs when a trigger that is fired accomplishes some action that would cause the same trigger to initiate again.
* In-direct recursion:
This recursion occurs when a trigger that is fired accomplishes some action that would cause the same type (AFTER or INSTEAD OF) to fire. This second trigger executes an action that would cause the original trigger to activate again.
An AFTER trigger does not call itself recursively unless the RECURSIVE_TRIGGERS database option is set. When the RECURSIVE_TRIGGERS database option is set to OFF, only direct recursion of AFTER triggers is prevented.
Multiple concurrent requests and triggers running under the scope of transactions might always lead to locking issues.
Indirect recursive execution for AFTER triggers can be prevented by:
* To disable the "nested triggers" server option should be set to 0. By default, the option is set to 1. This can be checked by running the below query:
</>SELECT * FROM SYS.CONFIGURATONS WHERE CONFIGURATION_ID = 115
* Using "IF UPDATE ()": Returns a Boolean value indicating that an INSERT or UPDATE attempt was made on a stated column of a table or view. UPDATE () can be used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.
Extended Stored Procedures
User-defined functions in SQL Server cannot contain DML statements and cannot invoke stored procedures. Whereas, Oracle functions can do basically the same procedures that SQL Server can. The workaround used by SSMA for Oracle implements a function body as a stored procedure (<>$IMPL) and invokes it within the function by means of an extended procedure, wrapper for calling the $IMPL stored procedure.
The conversion sample is as follows (as per the Microsoft guidelines to migrate data from Oracle to SQL Server 2008, http://www.microsoft.com/sqlserver/2008/en/us/migration.aspx):
Extended stored procedures provide a way to dynamically load and execute a function within a dynamic-link library (DLL) in a manner similar to that of a stored procedure, seamlessly extending SQL Server functionality. Actions outside of SQL Server can be easily triggered and external information returned to SQL Server.
In cases where possible, SSMA will try to control the calling of these functions directly (making a direct call to appropriate ...$IMPL SP instead), but some cases are not supported.
These kinds of cases will result in a direct call to generated wrapper functions (which in turn calls the extended stored procedure), which are quite slow and can lead to dead locks.
One of the suggested ways is to call SQL Server code to use func_name$IMPL stored procedures directly (just using normal EXECs, and not calling wrapper functions).
As discussed earlier in "Row level triggers section", Microsoft recommends minimizing the usage of cursors. Cursors force the database engine to recurrently procure rows, negotiate blocking, manage locks, and transmit results. Due to this, there might be more usage of locks than required, which would have an impact on the tempdb database.
The impact varies according to the type of cursor used. The level of cursors used in the nested cursor would also impact the batch performance. The sample code for the nested stored procedure would be as follows:
There were multiple instances in the code with nested cursor and even with the triggers having cursor implementation. Most of the locking issues in the package flow could be reduced by changing the cursor code to temporary variable code.
Case Sensitivity and Collation
The possible combinations of data types and collation types in SQL Server are depicted below:
The Unicode sorting rules applied by SQL Server engine are much more complex than the rules applied for a non-Unicode SQL sort order. When SQL Server associates Unicode data, the characters are consigned a weight that is dynamically modified depending on the collation's locale. Unicode sorting rules apply to all Unicode data types, defined by using either a SQL collation or Windows collation.
1. SQL Server uses non-Unicode sorting rules when non-Unicode data types are defined by using SQL collation. Though sorts and scans using this collation are generally faster than Unicode rules, they are un-reliable for certain collations.
2. SQL Server performs string comparisons of non-Unicode data types defined with a Windows collation by means of Unicode sorting rules. Since these rules are of higher complexity than non-Unicode sorting rules, they are more resource-intensive. So, even though Unicode sorting rules are frequently more expensive, there is generally a slight difference in terms of performance between Unicode data and non-Unicode data defined with a Windows collation.
3. Unicode data sorting can be slower than non-Unicode because of double bytes storage and is also dependent on the amount of data to be sorted. In addition to this, sorting Asian DBCS (Double Byte Character Set) data in a specific code page is much slower than sorting Asian characters, because DBCS data is actually a mixture of single-byte and double-byte widths, while Unicode characters are fixed-width.
4. There might be other performance issues primarily determined by the issue of converting the encrypting mechanism between the client instance of SQL Server and server instance of SQL Server. The decision on the type of data types to be used for collation might be determined by the amount of sorting, conversion and possible data corruption that might happen during customer interaction with the data. Frequent sorting of lots of data with a Unicode storage mechanism might severely affect performance.
Windows collations provide consistent string comparisons for both non-Unicode & Unicode data types, and are also consistent with string comparisons in the Windows operating system for non-Unicode text in SQL Server. Due to these factors, Windows collations would be most preferred unless there are issues related to backward compatibility or any precise performance issues that entail a SQL collation.
1. Improving SQL Performance (http://msdn.microsoft.com/en-us/library/ff647793.aspx)
2. Exploring SQL Triggers (http://msdn.microsoft.com/en- us/magazine/cc164047.aspx)
3. Trouble shooting performance problems in SQL Server (http://technet.microsoft.com/en-us/library/cc966540.aspx)
4. Comparing SQL collations to Windows collations (http://support.microsoft.com/kb/322112)