My first introduction to the APPLY operator was using the DMVs. For quite a while after first being introduced, I didn’t understand it or see a use for it. While it is undeniable that it is has some required uses when dealing with table valued functions, it’s other uses evaded me for a while. Luckily, I started seeing some code that used it outside of table valued functions. It finally struck me that it could be used as a replacement for correlated sub queries and derived tables. That’s what we’ll discuss today. I never liked correlated subqueries because it always seemed like adding full blown queries in the select list was confusing and improper. [cc lang=”sql”] SELECT SalesOrderID = soh.SalesOrderID ,OrderDate = soh.OrderDate ,MaxUnitPrice = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID) FROM AdventureWorks.Sales.SalesOrderHeader AS soh [/cc] It always seemed to me that these operations should go below the FROM clause. So to get around this, I would typically create a derived table. Which didn’t completely feel right either, but it was still just a bit cleaner: [cc lang=”sql”] SELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_price FROM AdventureWorks.Sales.SalesOrderHeader AS soh JOIN ( SELECT max_unit_price = MAX(sod.UnitPrice), SalesOrderID FROM Sales.SalesOrderDetail AS sod GROUP BY sod.SalesOrderID ) sod ON sod.SalesOrderID = soh.SalesOrderID [/cc] What made this ugly was the need to use the GROUP BY clause because we could not correlate. Also, even though SQL almost always generates the same execution plan as a correlated sub query, there were times when the logic […]
Continue reading ...
Change Data Capture (CDC) is a powerful feature included in SQL Server 2008. Using change data capture, it is possible to determine which values have been added, changed, or deleted within a table. Setting up CDC is done by specifying tables to monitor. Under the hood, CDC is written using the same repl logreader function that transactional replication uses. Don’t worry too much though. If you are like me, then when you hear the word replication, you start running for the hills. Well, CDC is not as much maintenance. The biggest thing you have to worry about with CDC, is that the disk that contains the log never gets full. When that does happen, and it should be rarely, then yes it is a pain. What you have to do in that case is flip the do not truncate bit for the log file to ‘no’. That aside, let’s get started. First thing you want to do is enable change data capture. This is done at the database level. [cc lang=”sql”] USE AdventureWorks GO DECLARE @ReturnCode int EXEC @ReturnCode = sys.sp_cdc_enable_db SELECT @ReturnCode SELECT name ,is_cdc_enabled FROM sys.databases [/cc] Even though we performed the above action, nothing will happen until we define the tables and columns that it should monitor. When defining a table to monitor, there are a few parameters that need to be passed: CDC Parameters Parameter Description @source_schema The schema name of the table to monitor (ex: dbo) @source_name The name of the table to monitor @role_name […]
Continue reading ...
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 update “n” number of rows. In order to determine latency for you CDC solution, you will need to query a DMV view: [cc lang=”sql”] select latency, * from sys.dm_cdc_log_scan_sessions [/cc] The latency will be shown in seconds, if it is getting too high, start increasing maxtrans, maxscans, and decrease the polling interval. polling interval – (default 5 seconds) the amount of time to wait between log scans maxtrans – (default 500) the number of transactions to grab with each scan maxscans – (default 10) the number of scans performed between each polling interval In order to change these settings, use the system stored procedure (sys.sp_cdc_change_job) after changing the database which contains your capture instance. The CDC job needs to be restarted after making these changes. [cc lang=”sql”] — Change to the database that contains your capture instance use YourDatabaseHere go exec sys.sp_cdc_change_job @job_type = ‘capture’ ,@maxtrans = 501 ,@maxscans = 10 ,@continuous = 1 ,@pollinginterval = 5 [/cc] Changing the latency between the cleanup is done using the same stored procedure: [cc lang=”sql”] exec sys.sp_cdc_change_job @job_type = ‘cleanup’ ,@retention = 4320 — Number of minutes to retain (72 hours) ,@threshold = 5000 [/cc] To view the […]
Continue reading ...
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 activity monitor in the right hand workspace window. There are 5 main sections to help provide monitoring: Overview – Graphical overview of Processor, Wait Tasks, I/O, and Batch Requests. Processes – Provides an overview similar to sp_who2, however is sortable by category and contains context driven actions. Resource Waits – High level overview of the wait time statistics aggregated from sys.dm_os_wait_stats Data File I/O – Provides latency and read/write statistics for each data and log file in the system. Recent Expensive Queries All statistics provided are from the SQL Server DMVs in a very convenient easy to use UI. This covers all potential SQL Server bottlenecks except possibly Network IO. And gives you a great initial overview in what you should be drilling down to in order to find a performance issue. Processes The next section is Processes. This is similar to sp_who2 however it is sortable on every column, provides lead blocker information and also has a context menu providing additional options of details (dbcc inputbuffer), kill, and trace in profiler. Resource Waits The resource wait section displays aggregated information on the wait statistics from the DMV sys.dm_os_wait_stats. Resource waits relate to the subsystems […]
Continue reading ...
Examples, best practices, uses, and benefits of the SQL Server 2008 MERGE statement.
Continue reading ...
