After setting up change data capture (CDC), there are a few configuration options that allow for optimal performance based upon applications load.
Using the default values, the most number of transactions that can be grabbed every 5 seconds is 5000. Remember, a transaction is not the same as a database row. One transaction can [...]
Archive of posts filed under the SQL Server 2008 category.
Configure Change Data Capture Settings
Activity Monitor
The Activity Monitor is a new feature in SQL Server 2008 that provides high-level and drill down information giving good insight into the performance of SQL Server allowing DBA’s to quickly identify the source of slowdowns.
To launch the activity monitor, right click on the instance name in SSMS and select ‘Activity Monitor’.
This launches [...]
The Merge Statement
Examples, best practices, uses, and benefits of the SQL Server 2008 MERGE statement.
Find All Queries Run Against a Table
Dynamic management view to find all queries that have been run against a table since the last reboot.
SQL Server 2005 vs SQL Server 2008
Outlines some new features of SQL Server 2008 and provides the reasons to upgrade instead of 2005.
MetaData Change Data Capture Solution (CDC)
Change Data Capture, CDC is a feature that is revolutionizing SQL Server in respects to data flow. Previous to change data capture, triggers or high water marks needed to be used in order to perform delta transfers of data to data warehouses or disparate systems.
This example shows the use of a custom CDC solution that [...]
SQL Server 2008 Minimally Logged Inserts
SQL Server 2008 has now introduced minimally logged inserts into tables that already contain data and a clustered index. What happens is the initial inserts may be fully logged if the data pages they are filling already contain data. However any new data pages added to the table will be minimally logged if [...]
Custom Pagination with Dynamic ORDER BY
A common solution needed for the front end is to paginate records prior to sending them to the webserver. More frequently now, we are seeing demormalized data sets being stored in the WebServer’s or a middle tiers cache mechanism. Those solutions however are more difficult to maintain, persist and synchronize. Enter the [...]
Find Dependent Objects
One new feature that SQL Server 2008 offers is more reliable dependency information. SQL Server 2005 offered a DMV (dynamic management view) called sys.sql_dependencies. It is now replaced by a more reliable sys.sql_expression_dependencies.
The following script will show all the procedures that reference a given table name, along with the columns the procedure references [...]
Using the Merge Statement to populate a historical table with Effective Dating
One of my favorite uses for the MERGE statement introduced in SQL Server 2008 is the updating of a historical table. With versions prior to 2008 this operation had to be performed in two separate statements. Merge helps us to streamline the process.
The advantage to the database engine when using a Merge statement [...]