T-SQL ENHANCEMENTS Figure 7-4: Trace Properties Dialog in

T-SQL ENHANCEMENTS Figure 7-4: Trace Properties Dialog in SQL Server 2005 To be certain that you get the correct result, you can clean out the cache before you execute the procedure by executing dbccfreeproccache. When setting up the trace, you will see how the SQL Profiler allows you to trace more events than in SQL Server 2000. Figure 7-4 shows the Events Selection tab from the Trace Properties dialog. As mentioned in the beginning of this chapter, the statement-level recompilation can be seen as a purely infrastructural enhancement. As a developer or DBA, you will not explicitly use it even though you implicitly benefit from it, and it may change the way you develop stored procedures. No longer do recompiles have as much of a negative impact on performance. DDL Triggers A trigger is a block of SQL statements that are executed based on the fact that there has been an alteration (INSERT, UPDATE, or DELETE) to a table or on a view. In previous versions of SQL Server, the statements had to be written in T-SQL, but in version 2005, as we saw in Chapter 3, they can also be written using .NET languages. As we mentioned, the triggers are fired based on action statements (DML) in the database.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

STATEMENT-LEVEL RECOMPILATION 5. Execute the procedure a couple

STATEMENT-LEVEL RECOMPILATION 5. Execute the procedure a couple of times from Query Analyzer and view the trace output. 6. The output from the trace will show a couple of entries in the Event Classcolumn with the value of SP:Recompile, as in Figure 7-3. This indicates that the procedure has been recompiled. As mentioned before, the cost of recompilation can be very high for large procedures, and in the SQL Server 2005 release, Microsoft has changed the model to statement-level re-compilation. At this stage you may worry that performance will suffer if each statement in a procedure is individually recompiled. Rest assured that the initial compilation is still on the module level, so only if a recompile is needed is it done per statement. Another performance benefit in SQL Server 2005 is the fact that when statement recompilation is done, the execution context will not be invalidated. The procedure in Listing 7-1 can be used in SQL Server 2005 to compare the differences between SQL Server 2000 and 2005. In SQL Server 2005, follow the steps listed earlier and notice in the trace how a recompile happens only the first time; for each subsequent execution, there is no recompile. This is due to the fact that an execution plan will be created after the initial recompile. Run the following code after you have executed the procedure a couple of times, and notice that the result you get consists of both a compiled plan and an executable plan. SELECT * FROM syscacheobjects WHERE dbid = db_id( pubs ) AND objid = object_id( test2 ) Figure 7-3: Trace Output

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

T-SQL ENHANCEMENTS //change a set option SET CONCAT_NULL_YIELDS_NULL

T-SQL ENHANCEMENTS //change a set option SET CONCAT_NULL_YIELDS_NULL OFF SELECT after set option To verify that recompilation happens on SQL Server 2000, do the following: 1. Catalog the procedure in Listing 7-1. 2. Open the SQL Server Profiler and from the File menu, select New | Trace. 3. When the Trace Properties dialog comes up, choose the Events tab. 4. In the Stored Procedures event group, choose the SP:Recompile event, click the Add button, as shown in Figure 7-2, and then click Run. Figure 7-2: Trace Properties Dialog for SQL Profiler

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

STATEMENT-LEVEL RECOMPILATION Compiled plan (a.k.a. query plan) A

STATEMENT-LEVEL RECOMPILATION Compiled plan (a.k.a. query plan) A read-only data structure used by any number of users. The plan is reentrant, which implies that all users share the plan and no user context information (such as data variable values) is stored in the compiled plan. There are never more than one or two copies of the query plan in memory one copy for all serial executions and another for all parallel executions. Executable plan A data structure for each user that concurrently executes the query. This data structure, which is called the executable plan or execution context, holds the data specific to each user s execution, such as parameter values. This architecture, paired with the fact that the execution context is reused, has very much improved the execution of not only stored procedures but functions, batches, dynamic queries, and so on. However, there is a common problem with executing stored procedures, and that is recompilation. Examples of things that cause recompilation to occur are as follows: Schema changes Threshold changes in rows Certain SEToptions A recompilation can incur a huge cost especially if the procedure, function, or batch is large, because SQL Server 2000 does module-level recompilation. In other words, the whole procedure is recompiled even if the cause of the recompilation affects only a small portion of the procedure. In addition, if the recompilation happens because a SET option changes, the executable plan will be invalidated and not cached. The code in Listing 7-1 is extremely simple, but it can be used to illustrate the problem. Listing 7-1 is a stored procedure which in the middle of the procedure changes the CONCAT_NULL_YIELDS_NULL option. When this runs against SQL Server 2000, a recompilation happens for each execution of the procedure. Listing 7-1: Procedure That Causes Recompilation CREATE PROCEDURE test2 AS SELECT before set option

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

T-SQL ENHANCEMENTS Monitoring Versioning Allowing versioning to achieve

T-SQL ENHANCEMENTS Monitoring Versioning Allowing versioning to achieve concurrency is a major change. We ve already seen how it can affect monitoring and capacity planning for TEMPDB. Therefore, all the tools and techniques that we ve used in the past must be updated to account for this new concurrency style. Here are some of the enhancements that make this possible. There are the following new T-SQL properties and metadata views: DATABASEPROPERTYEX Tells us if SNAPSHOTis on sys.fn_top_version_generators() Tables with most versions sys.fn_transaction_snapshot() Transaction active when a SNAPSHOTtransaction starts sys.fn_transactions() Includes information about SNAPSHOT transaction (or not), if SNAPSHOTincludes information about version chains and SNAPSHOTtimestamps There are new performance monitor counters for the following: Average version store data-generation rate (kilobytes per minute) Size of current version store (kilobytes) Free space in TEMPDB(kilobytes) Space used in the version store for each database (kilobytes) Longest running time in any SNAPSHOTtransaction (seconds) SNAPSHOT isolation information is also available during event tracing. Because a SNAPSHOTtransaction has to be aware of any updates committed by other users, other users updates appear in SQL Profiler while tracing a SNAPSHOT isolation transaction. Beware, since this can significantly increase the amount of data collected by Profiler. Statement-Level Recompilation The next thing we ll look at is a performance enhancement that is part of the infrastructural improvements in T-SQL: statement recompilation. In SQL Server 2000, the query plan architecture differs from previous versions, and it is divided into two structures: a compiled plan and an executable plan.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

SNAPSHOT ISOLATION Table 7-4: Versioning Database at SERIALIZABLE

SNAPSHOT ISOLATION Table 7-4: Versioning Database at SERIALIZABLE Isolation Concurrent Updates Step User 1 User 2 1 BEGIN TRAN SELECT name FROM tab WHERE id = 1 **value is Name 2 BEGIN TRAN UPDATE tab SET name = Newname WHERE id = 1 3 COMMIT 4 UPDATE tab SET name = Another name WHERE id = 1 ** produces concurrency violation 5 ROLLBACK (and try update again?) using SERIALIZABLEisolation (SQL Server ISOLATIONLEVELSNAPSHOT) in most cases. READCOMMITTEDis a better choice with versioning. Finally, as we said before, in versioning databases reads don t lock writes, which might be what we want. Is this possible with a versioning database? Locking-database programmers, when using versioning, tend to lock too little, introducing subtle concurrency problems. In a versioning database, there must be a way to do insist on a lock on read. Ordinarily this is done by doing a SQL SELECTFORUPDATE. But SQL Server does not support SELECTFORUPDATEwith the appropriate semantic. There is, however, a solution. Even when READ_COMMITTED_SNAPSHOTis on, you can ensure a read lock by using SQL Server s REPEATABLE READ isolation level, which never does versioning. The SQL Server equivalent of ANSI s SELECT FOR UPDATEis SELECTwith(REPEATABLEREAD). Note that this is different from the SQL Server UPDLOCK(update lock), which is a special lock that has similar semantics but only works if all participants in all transactions are using UPDLOCK. This is one place where programs written for versioning databases may have to change their code in porting to SQL Server 2005.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

T-SQL ENHANCEMENTS Drawbacks of Versioning Versioning has the

T-SQL ENHANCEMENTS Drawbacks of Versioning Versioning has the capability to increase concurrency but does come with a few drawbacks of its own. Before you write new applications to use versioning, you should be aware of these drawbacks. You can then assess the value of locking against the convenience of versioning. It can be costly because record versions need to be maintained even if no read operations are executing. This has the capability of filling up TEMPDB. If a database is set up for versioning, versions are kept in TEMPDB whether or not anyone is running a SNAPSHOTisolation level transaction. Although a garbage collector algorithm will analyze the older versioning transaction and clean up TEMPDB eventually, you have no control over how often that cleanup in done. Plan the size of TEMPDBaccordingly; it is used to keep versions for all databases with SNAPSHOT enabled. If you run out of space in TEMPDB, long-running transactions may fail. In addition, reading data will sometimes cost more because of the need to traverse the version list. If you are doing versioning at the READ COMMITTEDisolation level, the database may have to start at the beginning of the version list and read through it to attempt to read the last committed version. There is also the possibility of update concurrency problems. Let s sup pose that in Table 7-1 user 1 decides to update the row also. Table 7-4 shows how this would look. In this scenario, user 1 reads the value Name and may base his update on that value. If user 2 commits his transaction before user 1 commits his, and user 1 tries to update, he bases his update on possibly bad data (the old value he read in step 1). Rather than allowing this to happen, versioning databases produce an error. The error message in this case is as follows: Msg 3960, Level 16, State 1, Line 1. Cannot use snapshot isolation to access table tab in database pubs . Snapshot transaction aborted due to update conflict. Retry transaction. Obviously, retrying transactions often enough will slow down the overall throughput of the application. In addition, the window of time for a concurrency violation to occur increases the longer a transaction reads old values. Because, at the SERIALIZABLE isolation level, the user always reads the old value until he commits the transaction, the window is much bigger that is, concurrency violations are statistically much more likely to occur. In fact, vendors of versioning databases recommend against

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

SNAPSHOT ISOLATION The steps in Table 7-3 do

SNAPSHOT ISOLATION The steps in Table 7-3 do the following: 1. We start a transaction under SNAPSHOTisolation and update one column in one row. This causes SQL Server to store a copy of the original value in TEMPDB. Notice that we do not commit or roll back at this stage, so locks are held. If we were to run sp_lock, we would see an exclusive lock on the primary key. 2. We start a new transaction under a new session and try to read from the same row that is being updated at the moment. This is the row with an exclusive lock. If this had been previous versions of SQL Server (running under at least READ COMMITTED), we would be locked out. However, running in SNAPSHOT mode, SQL Server looks in the version store in TEMPDB to retrieve the latest committed value and returns Niels . 3. We commit the transaction, so the value is updated in the database and another version is put into the version store. 4. User 2 does a new SELECT(from within his original transaction) and will now receive the original value, Niels . 5. User 2 finally commits the transaction. 6. User 2 does a new SELECT (after his transaction commits) and will now receive the new value, NewNiels . SNAPSHOT isolation is useful for converting an application written for a versioning database to SQL Server. When an application is developed for a versioning database, the developer does not need to be concerned with locking. Converting such an application to SQL Server may result in diminished performance because more locking is done than is required. Prior to SQL Server 2005, this sort of conversion may have required rewriting the application. In version 2005, in many cases the only thing that will have to be done is to enable SNAPSHOT isolation and READ_COMMITTED_ SNAPSHOT. SNAPSHOT isolation is also beneficial for applications that mostly read and do few updates. It is also interesting to note that when SQL Server 2005 is installed, versioning is enabled in the MASTER and MSDB databases by default.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

T-SQL ENHANCEMENTS CREATE TABLE snapTest ([id] INT IDENTITY,

T-SQL ENHANCEMENTS CREATE TABLE snapTest ([id] INT IDENTITY, col1 VARCHAR(15)) insert some data INSERT INTO snapTest VALUES(1, Niels ) Table 7-3: Example of SNAPSHOT Isolation Step User 1 User 2 1 SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN UPDATE snapTest SET col1 = NewNiels WHERE id = 1 2 SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT col1 FROM snapTest WHERE id = 1 ** receives value Niels 3 COMMIT TRAN 4 SELECT col1 FROM snapTest WHERE id = 1 ** receives value Niels 5 COMMIT TRAN 6 SELECT col1 FROM snapTest WHERE id = 1 ** receives value NewNiels

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

SNAPSHOT ISOLATION transactions using versioning instead of locking.

SNAPSHOT ISOLATION transactions using versioning instead of locking. Both transactions shown in the SQL batch that follows will be executed as READ COMMITTED using versioning. alter the database ALTER DATABASE pubs SET ALLOW_SNAPSHOT_ISOLATION ON SET READ_COMMITTED_SNAPSHOT ON GO USE pubs GO SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN SQL expression will be executed as READ COMMITTED using versioning END TRAN SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SQL expression will be executed as READ COMMITTED using versioning END TRAN Whether ALLOW_SNAPSHOT_ISOLATIONis ONor not can be checked for a particular database by the DATABASEPROPERTYEX command. This command returns the current database option or setting for a particular database. The setting to check is the SnapshotIsolationFrameworksetting, as in following code for the pubsdatabase: SELECT DATABASEPROPERTYEX ( pubs , SnapshotIsolationFramework ) As stated earlier, SQL Server does not actually make a copy of a database when a SNAPSHOT transaction is started. Whenever a record is updated, SQL Server stores in TEMPDB a copy (version) of the previously committed value and maintains these changes. All the versions of a record are marked with a timestamp of the transactions that made the change, and the versions are chained in TEMPDB using a linked list. The newest record value is stored in a database page and linked to the version store in TEMPDB. For read access in a SNAPSHOT isolation transaction, SQL Server first accesses from the data page the last committed record. It then retrieves the record value from the version store by traversing the chain of pointers to the specific record version of the data. The code in Table 7-3 shows an example of how SNAPSHOT isolation works. The example uses a table, snapTest, looking like this. it is necessary to run SET ALLOW_SNAPSHOT_ISOLATION ON if that s not done already

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services