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

Bookmark the permalink.

Comments are closed.