SQL Server MVP Deep Dives- P21

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:40

Thêm vào BST

Báo xấu

77
lượt xem 5
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

SQL Server MVP Deep Dives- P21: Each year Microsoft invites all the MVPs from every technology and country to Redmond for an MVP Summit—all top secret—“don’t tweet what you see!” During the MVP Summit, each product team holds a series of presentations where they explain their technologies, share their vision, and listen to some honest feedback.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: SQL Server MVP Deep Dives- P21

754 CHAPTER 59 Incremental loads using T-SQL and SSIS SQL Server 2008 are the Change Tracking and Change Data Capture fea- tures which, as their names imply, automatically track which rows have been changed, making selecting from the source database much easier. Now that we’ve looked at an incremental load using T-SQL, let’s consider how SQL Server Integration Services can accomplish the same task without all the hand-coding. Incremental loads in SSIS SQL Server Integration Services (SSIS) is Microsoft’s application bundled with SQL Server that simplifies data integration and transformations—and in this case, incre- mental loads. For this example, we’ll use SSIS to execute the lookup transformation (for the join functionality) combined with the conditional split (for the WHERE clause conditions) transformations. Before we begin, let’s reset our database tables to their original state using the T-SQL code in listing 8. Listing 8 Resetting the tables USE SSISIncrementalLoad_Source GO TRUNCATE TABLE dbo.tblSource INSERT INTO dbo.tblSource Insert unchanged row (ColID,ColA,ColB,ColC) VALUES(0, 'A', '1/1/2007 12:01 AM', -1) -- insert a "changed" row INSERT INTO dbo.tblSource Insert changed row (ColID,ColA,ColB,ColC) VALUES(1, 'B', '1/1/2007 12:02 AM', -2) INSERT INTO dbo.tblSource Insert new row (ColID,ColA,ColB,ColC) VALUES(2, 'N', '1/1/2007 12:03 AM', -3) USE SSISIncrementalLoad_Dest GO TRUNCATE TABLE dbo.tblDest INSERT INTO dbo.tblDest Insert unchanged row (ColID,ColA,ColB,ColC) VALUES(0, 'A', '1/1/2007 12:01 AM', -1) INSERT INTO dbo.tblDest Insert changed row (ColID,ColA,ColB,ColC) VALUES(1, 'C', '1/1/2007 12:02 AM', -2) With the tables back in their original state, we’ll create a new project using Business Intelligence Development Studio (BIDS). Creating the new BIDS project To follow along with this example, first open BIDS and create a new project. We’ll name the project SSISIncrementalLoad, as shown in figure 1. Once the project loads, open Solution Explorer, right-click the package, and rename Package1.dtsx to SSISIn- crementalLoad.dtsx. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Incremental loads in SSIS 755 Figure 1 Creating a new BIDS project named SSISIncrementalLoad When prompted to rename the package object, click the Yes button. From here, fol- low this straightforward series: 1 From the toolbox, drag a data flow onto the Control Flow canvas. Double-click the data flow task to edit it. 2 From the toolbox, drag and drop an OLE DB source onto the Data Flow canvas. Double-click the OLE DB Source connection adapter to edit it. 3 Click the New button beside the OLE DB Connection Manager drop-down. Click the New button here to create a new data connection. Enter or select your server name. Connect to the SSISIncrementalLoad_Source database you cre- ated earlier. Click the OK button to return to the Connection Manager configu- ration dialog box. 4 Click the OK button to accept your newly created data connection as the con- nection manager you want to define. Select dbo.tblSource from the Table drop-down. 5 Click the OK button to complete defining the OLE DB source adapter. Defining the lookup transformation Now that the source adapter is defined, let’s move on to the lookup transformation that’ll join the data from our two tables. Again, there’s a standard series of steps in SSIS: 1 Drag and drop a lookup transformation from the toolbox onto the Data Flow canvas. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
756 CHAPTER 59 Incremental loads using T-SQL and SSIS 2 Connect the OLE DB connection adapter to the lookup transformation by click- ing on the OLE DB Source, dragging the green arrow over the lookup, and dropping it. 3 Right-click the lookup transformation and click Edit (or double-click the lookup transformation) to edit. You should now see something like the exam- ple shown in figure 2. When the editor opens, click the New button beside the OLE DB Connection Manager drop-down (as you did earlier for the OLE DB source adapter). Define a new data con- nection—this time to the SSISIncrementalLoad_Dest database. After setting up the new data connection and connection manager, configure the lookup transformation to connect to dbo.tblDest. Click the Columns tab. On the left side are the columns currently in the SSIS data flow pipeline (from SSISIncrementalLoad_Source. dbo.tblSource). On the right side are columns available from the lookup destination you just configured (from SSISIncrementalLoad_Dest.dbo.tblDest). We’ll need all the rows returned from the destination table, so check all the check boxes beside the rows in the destination. We need these rows for our WHERE clauses and our JOIN ON clauses. We don’t want to map all the rows between the source and destination—only the columns named ColID between the database tables. The mappings drawn between the Available Input columns and Available Lookup columns define the JOIN ON clause. Multi-select the mappings between ColA, ColB, and ColC by clicking on them while holding the Ctrl key. Right-click any of them and click Delete Selected Mappings to delete these columns from our JOIN ON clause, as shown in figure 3. Figure 2 Using SSIS to edit the lookup transformation Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Incremental loads in SSIS 757 Figure 3 Using the Lookup Transformation Editor to establish the correct mappings Add the text Dest_ to each column’s output alias. These rows are being appended to the data flow pipeline. This is so that we can distinguish between source and destina- tion rows farther down the pipeline. Setting the lookup transformation behavior Next we need to modify our lookup transformation behavior. By default, the lookup operates similar to an INNER JOIN —but we need a LEFT (OUTER) JOIN. Click the Con- figure Error Output button to open the Configure Error Output screen. On the Lookup Output row, change the Error column from Fail Component to Ignore Fail- ure. This tells the lookup transformation that if it doesn’t find an INNER JOIN match in the destination table for the source table’s ColID value, it shouldn’t fail. This also effectively tells the lookup to behave like a LEFT JOIN instead of an INNER JOIN. Click OK to complete the lookup transformation configuration. From the toolbox, drag and drop a conditional split transformation onto the Data Flow canvas. Connect the lookup to the conditional split as shown in figure 4. Right- click the conditional split and click Edit to open the Conditional Split Transformation Editor. The Editor is divided into three sections. The upper-left section contains a list of available variables and columns. The upper-right section contains a list of available operations you may perform on values in the conditional expression. The lower sec- tion contains a list of the outputs you can define using SSIS Expression Language. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
758 CHAPTER 59 Incremental loads using T-SQL and SSIS Expand the NULL Functions folder in the upper-right section of the Conditional Split Transformation Editor, and expand the Columns folder in the upper-left section. Click in the Output Name column and enter New Rows as the name of the first output. From the NULL Functions folder, drag and drop the ISNULL( ) func- tion to the Condition column of the New Rows condition. Next, drag Dest_ColID from the Columns folder and drop it onto the text in the Condition col- umn. New rows should now be defined by the condition ISNULL( [Dest_ColID] ). This defines the WHERE clause for new rows—setting it to WHERE Dest_ColID Is NULL. Type Changed Rows into a second output name column. Add the expression(ColA != Dest_ColA) || (ColB != Dest_ColB) || (ColC != Dest_ColC) to the Condition col- umn for the Changed Rows output. This defines our WHERE clause for detecting changed rows—setting it to WHERE ((Dest_ColA != ColA) OR (Dest_ColB != ColB) OR (Dest_ColC != ColC)). Note that || is the expression for OR in SSIS expressions. Change the default output name from Conditional Split Default Output to Unchanged Rows. It’s important to note here that the data flow task acts on rows. It can be used to manipulate (transform, create, or delete) data in columns in a row, but the sources, destinations, and transformations in the data flow task act on rows. In a conditional split transformation, rows are sent to the output when the SSIS Expression Language condition for that output evaluates as true. A conditional split transformation behaves like a Switch statement in C# or Select Case in Visual Basic, in that the rows are sent to the first output for which the condition evaluates as true. This means that if two or more conditions are true for a given row, the row will be sent to the first output in the list for which the condition is true, and that the row will never be checked to see whether it meets the second condition. Click the OK button to com- plete configuration of the conditional split transformation. Drag and drop an OLE DB destination connection adapter and an OLE DB com- mand transformation onto the Data Flow canvas. Click on the conditional split and connect it to the OLE DB destination. A dialog box will display prompting you to select a conditional split output (those outputs you defined in the last step). Select the New Rows output. Next connect the OLE DB command transformation to the conditional split’s Changed Rows output. Your Data Flow canvas should appear similar to the example in figure 4. Configure the OLE DB destination by aiming at the SSISIncremental- Load_Dest.dbo.tblDest table. Click the Mappings item in the list to the left. Make sure the ColID, ColA, ColB, and ColC source columns are mapped to their matching destination columns (aren’t you glad we prepended Dest_ to the destination col- umns?). Click the OK button to complete configuring the OLE DB destination con- nection adapter. Double-click the OLE DB command to open the Advanced Editor for the OLE DB Command dialog box. Set the Connection Manager column to your SSISIncrementalLoad_Dest connection manager. Click on the Component Proper- ties tab. Click the ellipsis (...) beside the SQLCommand property. The String Value Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Incremental loads in SSIS 759 Figure 4 The Data Flow canvas shows a graphical view of the transformation. Editor displays. Enter the following parameterized T-SQL statement into the String Value text box: UPDATE dbo.tblDest SET ColA = ? ,ColB = ? ,ColC = ? WHERE ColID = ? The question marks in the previous parameterized T-SQL statement map by ordinal to columns named Param_0 through Param_3. Map them as shown here—effectively altering the UPDATE statement for each row: UPDATE SSISIncrementalLoad_Dest.dbo.tblDest SET ColA = SSISIncrementalLoad_Source.dbo.ColA ,ColB = SSISIncrementalLoad_Source.dbo.ColB ,ColC = SSISIncrementalLoad_Source.dbo.ColC WHERE ColID = SSISIncrementalLoad_Source.dbo.ColID As you can see in figure 5, the query is executed on a row-by-row basis. For perfor- mance with large amounts of data, you’ll want to employ set-based updates instead. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
760 CHAPTER 59 Incremental loads using T-SQL and SSIS Figure 5 The Advanced Editor shows a representation of the data flow prior to execution. Click the OK button when mapping is completed. If you execute the package with debugging (press F5), the package should succeed. Note that one row takes the New Rows output from the conditional split, and one row takes the Changed Rows output from the conditional split transformation. Although not visible, our third source row doesn't change, and would be sent to the Unchanged Rows output—which is the default Conditional Split output renamed. Any row that doesn’t meet any of the predefined conditions in the conditional split is sent to the default output. Summary The incremental load design pattern is a powerful way to leverage the strengths of the SSIS 2005 data flow task to transport data from a source to a destination. By using this method, you only insert or update rows that are new or have changed. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Summary 761 About the author Andy Leonard is an architect with Unisys corporation, SQL Server database and integration services developer, SQL Server MVP, PASS regional mentor (Southeast US), and engineer. He’s a coauthor of several books on SQL Server topics. Andy founded and manages VSTeamSystemCentral.com and main- tains several blogs there—Applied Team System, Applied Data- base Development, and Applied Business Intelligence—and also blogs for SQLBlog.com. Andy’s background includes web application architecture and development, VB, and ASP; SQL Server Integration Services (SSIS); data warehouse develop- ment using SQL Server 2000, 2005, and 2008; and test-driven database development. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
index A active queries 390 AdventureWorks 30, 47, 87, ActiveSync-connected 111, 178, 182, 185, 188, abstraction 6 device 301 541–542, 547, 585–586 abstraction layer 7 ad hoc full backup 447 SQL Server 2005 access control lists 464 ad hoc queries 399, 598, 600 version 541 Access databases 715 largest 598 AdventureWorks2008 access roles 298 ad hoc reports 687 database 111–112, 177, Access. See Microsoft Access ad hoc SQL 7, 217, 554 189, 201, 376, 646 Access/JET database 272 ad hoc workloads 452 AdventureWorksDW ACLs. See access control lists AddDevice method 355 database 235 ACS. See Audit Collection Ser- administrative AdventureWorksDW2008 691 vice considerations 258 AdventureWorksLT2008 action groups 673 ADO.NET 213, 227, 262, database 225, 228, 230 AUDIT_CHANGE_GROUP 264–267, 273, 299, 307, ADW 673 346, 351 aggregations 705 DBCC_GROUP 673 ADO.NET 2.0 259, 263, 274 designing aggregations 702 SCHEMA_OBJECT ADO.NET 3.5 210, 226, 268 query patterns 701 _CHANGE_GROUP 673 ADO .NET 3.5 SP1 210 Specify Object Counts 703 Action on Audit Failure 365 code 210, 228 AES 298 Action property 355 connection manager 715 AFTER triggers 20, 558, 561 actions 621–623, 625, 673 conversions 227 Age in Cache column 593 binding 626 data providers 302 Age of Empires 269 T-SQL stack 625 data table 208 Agent jobs 330–331, 338, 342 types 625 factory-based objects 228 Agent schedule 331 Active Directory 345, 498, 500, factory-method-based Aggregate component. 517 objects 227 See asynchronous compo- configuring 503 objects 226 nents domains 500–501, 503 OLE DB data provider 663 aggregate queries 601 Domains and Trust 502–503 provider 303 aggregated levels 700 forest 501, 503 provider model 210 aggregation requirements 503, 508 SqlClient data provider 272 candidates 702–703 trees 501 ADO.NET Entity aggregation design 700, User and Computers 504 Framework 210, 216, 219 702–703, 705, 707 Active Domain authentication Advanced Schedule high-performance credentials 270 Options 468 cubes 708 763 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
764 INDEX aggregation design (continued) Analysis Services 260, 636, 638, ApplicationLog server influencing 702 645, 692, 700 audit 372 partition level 707 ADW 701 appname 553 Aggregation Design aggregations 701 architects 6–7 Wizard 701 attribute relationships 704 architectural performance benefit 701 candidate attributes for considerations 258 aggregation designs aggregation 702 architecture 298 other considerations 707 designing hierarchies 707 archiving 322–323, 480 resource utilization 707 fact table 708 ARM 298 Aggregation Management hierarchy types 708 array index 122 tool 707 leveraging aggregations 707 arrays 221 Aggregation Manager 706 query log 705 artificial intelligence 688 aggregations 247, 558, 639, user-defined hierarchy 704 ASCII format 662 700 Analysis Services 2005 703, ASP 263, 642, 654, 658 Aggregation Manager 706 706 ASP applications 263 Analysis Services 701 Aggregation Management ASP.NET 509, 512 cache 700 tool 707 applications 263, 265, comparing with Analysis Services 2005 cubes 267–268, 270–271, 273 indexes 701 aggregations 706 service 270 cost of designing 707 Analysis Services 2008 703 ASP.NET site, average connec- designing 701 aggregations 706 tions used 274 disk space 707 core concept 704 ASP-type application 263 fact table 708 usage-based connection 263 influencing 702 optimization 707 Assembly object 354 leveraging 706–707 Analysis Services ADW 707 associated entities 216 lower level 701 Analysis Services cubes 634, Association Rules 690 OLAP solution 701 637, 639 associative database tables 170 partition level 701 Analysis Services engine associative entities 155, 163, partitions 707 UBO 706 167, 169 Profiler 705 Analysis Services UDM asynchronous 457 query log 705 cubes 698 audit 371 target number 707 anonymous rowsets 216, 218 bucketizing target 625 AggregationUsage anonymous types 213, 216, components 745 property 703 218 database mirroring 462 aggregators 743 ANSI SQL standards 217 file target 625 agility 526 ranking functions 217 mode. See high-performance alerts 293, 455 windowing functions 217 mode low free disk space 521 ANSI standard operations 265, 267 algorithms 688 specification 45 ring buffer target 625 parameters 688 windowing extensions 45 targets 629 aliases 86, 92, 268 ApexSQL Audit 672 Attribute Discrimination alloc_unit_type_desc 405 application architecture 276 viewer 695 allocated area 245 application code 25 attribute relationships allocation order scan 614 application connection 704–705, 708 allocation unit 405 strings 461 designing 705 allocation unit type 386, 404 See also connection strings attribute-by-attribute basis 31 ALTER 179–181, 224 application data 359 attributes 153, 688 ALTER DATABASE 292, 454 application developers 25 combinations of ALTER INDEX 334–335 application ecosystem 323 dependencies 40 ALTER INDEX REBUILD 439 application integration 322, functional dependencies 28 ALTER PARTITION 326 functionally dependent FUNCTION 418 Application Log 673, 675 attributes 34 ALTER TABLE 15, 408 Application Log Server many-attribute relation 39 Amazon.com 634 Audit 374 audit action groups 366, AMO. See Analysis Manage- application profile 4 372–373, 376, 379 ment Objects application server 222 audit actions 366 Analysis Management application testing 460 audit application 498 Objects 353 application virtualization 519 Audit Collection Service 370 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
INDEX 765 audit events 365–366, average seek time 607–609, BASIC 7 370–371, 374 611 basic form 46 viewing 374 average worker time 594 concurrency 46 audit files 371, 374 axis step 120 exponential performance audit folder 374 scaling 46 Audit GUID 367, 371 locking 46 B performance 46 audit information 674 audit infrastructure 365 transaction log 46 audit log 498 backup compression 452–453, batch commands, 461 replicating 490 audit name 369 audit objects 365, 372 native backup batch file 468 audit output types 366 compression 453 creating 466 Audit Policy 367 BACKUP DATABASE 384, location 464 audit records 673 436 batch updates 485, 487, 746 audit row 674 backup database 331–332, network latency 491 audit specifications 366, 676 338 batch-by-batch basis 219 audited events 673 backup device 339 BatchCommitSize setting 486 auditing 322, 365, 381, 394, backup drives 451 BatchCommitThreshold 671, 673 backup file name 356 setting 486 database level 365 backup files 451, 453 batches 74, 214, 244, 488, 490, DDL statements 365 removal 331 576 DML events 376 backup history 331 batch size 247 filtering 378 Backup object 355 deletes 486 filters 376 backup routine 451 DML 487 instance level 365, 372 backup script 465 variables 247 auditing activity 675 backup strategy 356 batching 246 information 469 backup type 338 BCP 662 audits 350, 673 BackupDeviceItem 358 data 477 authentication 269 BackupDevices 350 files 235, 477 BackupDirectory process 480 credentials 270 BCP partitioning 480 methods 384, 510 property 355 backups 322, 326–327, 330, benefits 480 protocol 498 bcp utility 104 Auto Close 559 355–356, 432, 450–451, 461, 518 command line 104 auto create statistics 564 command window 104 AUTO option 179 backup files 355 backup sizes 432 error 104 Auto Shrink 559 query window 104 auto update statistics 564 backup time 432 compressing 432 xp_cmdshell 104 Auto_Open 665 before and after values 685 AutoComplete 382 disaster recovery testing 358 benchmark results 544 automated job 468 benchmarking tool 609 automatic data protection 12 eliminating index data 432, Bertrand, Aaron 404 automatic failover 449–451, 447 best practices 323, 359 457, 461–462 energy consumption 432 BI 3 automatic range energy-efficient 432 application 635 management 478 filegroup backups 432 developer 741 automatic redundancy 462 frequency 467 functionality 645 Automatically Detect Settings full backup 453, 454 suite 698 option 506 full database backups 433, tools 645 automation engine 344, 347 435 See also business intelligence auto-parameterization restoration 433, 447 suite 211–212 restore 357 BI-based applications 328 Auto-Regression Trees 691 routine backups 432 bi-directional link 262 Auto-Regressive Integrated BackupSetDescription BIDS 693, 696, 698, 703, 713, Moving Average 691 property 355 717, 754 auxiliary table 283 BackupSetName property 355 See also Business Intelligence average page density 617 bad indexes 601 Development Studio average page space 404 base tables 213, 242, 424, 681 BIDS 2008, Attribute Relation- average reads 398 baseline value 679 ships tab 705 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
766 INDEX BIDS project, creating 754 buffer cache 236, 564, 572 C big bitmask 253 Buffer Cache Hit Ratio 597 Big Four PerfMon buffer pool 383, 564, 597, 613 C 206 counters 577 buffer space 598 C language 204, 727 CPU 577 buffering 626 C# 45, 80, 89, 244, 257, 299, IO 577 buffers 383, 626 315, 321, 344, 346, 348, 758 Memory 577 BufferSizeTuning 749 C++ 299 Network 577 Build 3215 459 C2 Audit Mode 670 big table built-in functions 201 C2 Auditing feature 673 manageability benefits 424 bulk copy methods 12 CAB files 300 partitioning 424 bulk import 104 CAB-based installations 298 performance 424 bulk import file 104 cache, multiple plans 211 binaries 623 bulk insert 103, 109 cache hit 624 binary circular file 577 BULK INSERT 106, 108 Cache Hit Ratios 385 collation 237 Bulk Insert Task 108 cacheability difference 212 column 4 Bulk Insert Task Editor 109 cached stored procedures 593 elimination 491 bulk loading 685 caching application 677 Binary Large Object 643 bulk copying 477 calendar table 283 binlist_to_table 248 bulk-logged recovery 108, 480 calling program 80 bin-packing problem 51 business cycle 394 candidate key attributes 39 declarative code 52 business data 634, 641 Candidate Key profile 712 iterative code 52 business domain 163 canonical problem 222 set-based iteration 55, 58 Business Intelligence capacity planning 323 set-based solution 52 development toolkit 645 capture host name 685 speed and packing business intelligence 322, 328, capture instance table 682 efficiency 52 633–634, 687, 709, 726 captured data 684 SQL Server 2005 55 applications 322 cardinality 17, 19 SQL Server 2008 55 difference with legacy OLTP cascading operations 20, 21 BIOS 456 applications 641 case 688 bit flags 381 project 636 attributes 688 bitmasks 253 reporting 633 table 690 attributes 4 RS reports 687 variables 688 column 252 specialist 328 CASE statement 251 index 252 terminology 634 catalog 177, 181, 199 values 251, 254 traditional OLTP name 179 BLOB 643 approach 640 offline 177 BLOBTempStoragePath 749 Business Intelligence Develop- tables 644 blocked process reports 619 ment Studio 109, 692, views 232, 375, 382, 620 blocking 701, 713, 754 Catalog Name 194 detecting 603 Business Intelligence catalogs 192–193 issues 603–604 Projects 645 advanced queries 194 bookmark lookups 544, 610 business intelligence queries to retrieve 192 bookmarks 541 solution 635 catastrophic cluster 462 Bouche, Sylvain 250 back-end tools 640 catastrophic loss 525 Bound Trees 598 dimensional model 636 CD 527 boundary range 416 front-end analytics 640 CDC 685 boundary time value 429 overall strategy 636 functions 682 boundary values 416, 418, 429 relational data warehouse instance 682 bracketing 92 store 640 table 683 branching 727 subject areas 636 See also change data capture task 729 tools 640 Central Management brittle code 303 business intelligence suite 687 Server 450 broken mirror 457 business logic 6 certificate store 345 broker activation task 624 business problems 689 Change Data Capture 378, BS Analysis Services 258 Business Scorecard 754 b-tree 425 Manager 640 change data capture 402, 670, level 386 By Day report 661 681 structure 421 BY REF 232 function 683 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
INDEX 767 change management 323 synchronization Column Null Ratio profile Change Tracking 754 providers 306, 308 711, 714, 719, 723 change tracking 179–180, tables 306 Column Pattern checking 723 670–671, 676, 679–681, client-side executable 263 Column Pattern profile 711, 684–685 clock drift 518, 525 713, 715, 720, 722 AUTO 179, 181 clogged network 273 flattened structure 722 data 685 CLR 23, 239, 244, 253, 327 hierarchical structure 722 DISABLE 181 aggregate function 282 column statistics 336 enabling 677 code 235 Column Statistics Only 336 MANUAL 179 executables 645 Column Statistics mode 180, 192 language 244 profile 712 NO POPULATION 179 See also Common Language Column Value Distribution OFF 179 Runtime profile 711 CHANGE_RETENTION 677 cluster failover 462 ColumnMappings 307 CHANGE_TRACKING cluster service 450 columns, full-text AUTO 179 clustered index 15, 213, 247, indexed 193 MANUAL 180 407, 411, 421, 546, COM legacy 266 CHANGE_TRACKING_ 583–585, 588 COM-based ADO 262, 266 CURRENT_VERSION clustered indexes 5, 445, command object 228 678 541, 600, 614 comma-separated lists 243 change_tracking_state_desc 1 correlation 583 CommitBatchSize 493–495 92–193 distribution statistics 588 CommitBatchThreshold changed data 681 key 425 493–495 updating 753 pages 584 commodity hardware 461 changed rows, isolating 753 scan 571, 573, 586–587 commodity level servers 456 Changed Rows output 758, storage 437 commodity-level hardware 449 760 query plan 587 common code 288 CHANGETABLE 678 clustered instance 461 state tax rates 288 function 680 clustered key lookups 544 StateTaxRates table 288 char 176 clustered remote Common Language CHARINDEX function 126 distributor 492 Runtime 235, 327, 401 Chart report element 650 clustered servers 275 common table expressions 64, CHECK constraints 223 clustered tables 407, 411 146, 214, 240, 247 check constraints 13, 17, 18, clustered unique index 16 queries 560 19, 24–26 ClusteredIndexes common version store 558 reporting 19 property 363 commutative 87 check database integrity 333 Clustering 690 compatibility level 459 checkpoints 594, 609–610, clustering 324, 328 compatibility mode 459 613 clustering index 542, 544 compile errors 74 checkpoint I/O requests 613 cmdlets 345, 348 compiled assemblies 244 checkpoint sleep 390 SQL Server 2008 350 compiler 204 CHECKSUM 339, 564 COALESCE 112 Component Properties 758 child entity 216 COBOL 7, 45 composite foreign keys 5 child partition 519, 523 Codd 10 composite indexes 569, 574 child tables 690 code errors 75 composite primary keys 5 Citrix 344 code module 221–224, 226, compressed backup 458 Citrix XenApp streaming 519 233 computed columns 223 classic DLL version 208 code modules 206 COM-style registration 300 cleanup script 465 CodePlex 225, 364, 376, 706 concurrency 9, 298, 436 client 256, 262, 265 coding practices 20 issues 541 application 222, 299 coding-standards 89 concurrent snapshots 480 connections 450 cold cache 583 conditional split 757–758, 760 data access interfaces 256 collation 201, 237 Conditional Split Default database 306, 308 collection objects 716 Output 758 machines 299 Column Length Distribution Conditional Split Editor network library 257 profile 711, 714, 723 757 proxy class 315 column list 281 Conditional Split output synchronization agent 310 column mappings 756 760 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
768 INDEX conditional split output 758 connection strategy 263, 268, core database engine 299 Conditional Split 276 corporate datacenters 526 transformation 723, 735 connect-query-disconnect corporate environment 526 Conditional Split Transforma- 263 corporate policy 465 tion Editor 757 just-in-time connection correlated subqueries 23, 218 conditional split strategy 276 correlation 583–584 transformations 754, 758 connection strings 268, 269, determining 588 configuration 758 270, 274, 455–456, 461, high correlation 585, Select Case 758 643, 662 588–589 Switch statement 758 failover partner 455 low correlation 585, 587 Configuration Manager 655 modified connection when to expect Configuration property 308 string 455 correlation 588 Configuration tool 648 Connection switch 716 corruption problem 333 configurations 716 Connection Timeout 267 COUNT_ROWS 386 Configure Error Output 757 connection-scoped server covered query 543 conflict detection 478 state 264 covering indexes 213, column-level 478 ConnectionString property 541–544, 545 row-level 478 267, 646, 716, 737 definition 213 conflict viewer 478 connectivity 263, 455 disadvantages 541 congested networks 491 connect-query-disconnect 263 modifications 545 connection 265 consonants 200, 206 performance 213 agent 264 Constant Scan operator 426, update performance 546 closing 275 429 CPU 383–384, 451, 480, 525, definition 261 constrained delegation 502, 576–577, 594 establishing 266 504, 508 cores 563, 602 exception handling 262 ConstraintOptions property cycles 262, 543 issues 258 731 resource availability 523 management 264 constraints 18, 44 resources 523, 524 constraint violations 163 time 398 managers 712, 726, 737, pitfalls in finding 32 usage 523 756 container objects 232 utilization 564, 592 paradigm 263 containers 726–727 CPU pressure 591–593 problems 255 CONTAINS 181–184, 200, cached stored pooler 267 202 procedures 593 resources 263–264 contains function 126 DMV queries 592 settings 264 CONTAINS how to detect 592 scope 264 INFLECTIONAL performance issue 592 server-side state 275 203 runnable tasks count 593 state 268, 274 CONTAINSTABLE 183–184, CPU queries strategies 265 203 expensive 594 techniques 255 RANK column 184 crash recovery 607 time 384 continuous variables 690–691 CREATE AGGREGATE 245 Connection Lifetime 275 control flow 726, 743 CREATE DATABASE 433 Connection Manager 755, components 729, 734 Create Database Audit 758 configuration 730 Specification 379 Connection object 263, 275 logic 730 CREATE FULLTEXT Connection Pool 263 performance 743 INDEX 178 connection pool 263, 268, 273 XML task 720 CREATE INDEX 435, 585 connection string 273 Control Flow elements 743 DDL 399 performance 276 Controller method 261 WITH DROP_ Connection Pooling 267 conventional disk drive 606 EXISTING 438–439 connection pooling 264, 273, CONVERT function 137–139 Create Server Audit 662 Convert-UrnToPath 350 Specification 373 mechanism 274 copy 453, 459 CREATE TABLE 404 releasing resources 275 Copy Column CreateNewTableOrFail 309 Connection Pooling tab 663 transformation Credentials 350 connection pools, client 274 744 credentials 502 Connection Reset 268, 275 SSIS variable 744 See also user credentials Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
INDEX 769 CRISP-DM 689 overhead 65 data collection See also Cross-Industry static-driven 560 asynchronous mode 293 Standard Process for custom data arrays 654 frequency 293 Data Mining custom error messages 76 synchronous mode 293 Crivat, Bogdan 698 custom error table 79 data connection 755–756 CRM 688 custom keyboard shortcuts data containers 221 customers relationship 277, 279, 281–282 data definition language 265, management 688 productivity 282 421 CROSS 86 custom log shipping 473 data design 28 APPLY 238 custom logging 385 data destination 743 JOIN 88 custom objects 743 aggregate 745 cross-database references 21 custom profiles 490 flat file 746 cross-database referential custom scripts 743 multiple 745 integrity 20 custom shortcuts 282 SQL Server destination 747 Cross-Industry Standard Pro- custom stoplist 191 data distribution 44 cess for Data Mining 689, custom stored procedures 279 data domain 156 698 custom sync objects 492 data dumps 5, 642 Cross-platform 298 custom thesaurus 186, 188 data element 156 CRUD 307 custom update stored data encryption 326 CSV 644, 653 procedure 494 data exclusion 719 format 662, 666 customers relationship data exploration 710 CTEs 66, 71, 240, 247 management 688 data exports 171, 326 See also common table data extraction 436 expression data extraction tool 662 cube D data files 451–452 data 639 data flow 722, 726, 741, 744, design 636, 707 DAC 476 748 designer 703 remote access 476 Column Pattern profile 722 loading 634 See also Dedicated Adminis- component properties 734 partitions 640 trator Connection data acquisition processing 639 DAC connection 476 performance 744 store 635 DAI. See data access interfaces data acquisition rate 744 cube-processing data access, regulations 670 destination loading 744 overhead 701 developers 265 destination updating 744 time 707 mode 745 lookups 744 cubes 638, 700, 703 providers 263, 265 performance 748 aggregation 700 stacks 210 source acquisition 744 analyzing disk space 641 technologies 455 transforming data 744 data store 639 data access XML source 720–721 data warehouse 639 interfaces 262–263, 266 Data Flow elements 743 large cubes 707 Data Access Mode property data flow performance larger cubes 701 736 BLOB data 749 smaller cubes 701 data acquisition performance BLOBTempStoragePath T-SQL 638 744 property 749 validating 707 Copy Column BufferSizeTuning cumulative update 462 transformation 744 event 749 cumulative waits 592 Row Count DefaultBufferMaxRows current baseline version 678 transformation 744 property 748 current context node 126 data adapter 307 DefaultBufferSize current database 280 data architect 12 property 748 Current Disk Queue data archiving 322, 421–422 data flow pipeline 737, 757 Length 608 data attributes 30 data flow records 735 current_tasks_count 603 data availability 421 data flow task 726, 729, 734, current-next pairs 63 data backup strategy 526 738, 740, 743, 744, 758 cursor-based code 45 data cache server 671 average baseline 744 cursors 3, 7–8, 20, 45, 65, 69, data caches 303, 676 destinations 758 558 data centers 297 executables 744 keyset-driven 560 migration 325 execution trees 744 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
770 INDEX data flow task (continued) Data Mining Viewers 693 Data Profiling Task sources 758 Data Mining Wizard 692 Editor 713, 716, 718 transformations 758 data model 30, 155, 157, 636 Data Profiling task data flow transformations 734 data store 160 output 721 data formatting 20 natural keys 155 data protection 15, 26 data fragments 222 primary key constraints 155 data providers 654 data growth 322, 435 surrogate keys 155 data purging 322, 421–422 monitoring 323 unique constraints 155 data quality 12–13, 712 data images 378 data modeler ratio 5 decisions 719 data imports 326 data modelers 5, 28, 30 data queries 654 data inconsistency 615 domain experts 28 data records 305 data integration 710, 754 data modeling 28–29, 36, 323 data redirecting 20 data integrity 7, 11–13, 244, missing attributes 36 data redundancy 155, 157 298, 305, 326, 751 data objects 270 data retrieval, optimizing 215 data issues 305 data overview 689, 698 data sampling 716 data loading 421 pivot graphs 690 data size 322 data loss 356, 457 pivot tables 690 Data Source 267 data management 174 data pages 405, 407–411, 574, Data Source key 269 strategy 305 584–585, 587 Data Source View Wizard 690, data management operations space used 405 692 160, 162, 169, 174 data patterns 709 data sources 644, 649, 651, outbound operations 160 data pipeline 726 663, 709–710, 735, 743 data manipulation data points 639 data storage 327 language 146, 422 data preparation 689, 698 data store 293 Data Manipulation Language Data Processing data structure allocations 439 commands 485 Extension 644 data synchronization 298, 305, data marts 633 data processing extension 644 316 See also data warehouse data profile 709 data transformation 745 data mining 687, 698 information 714 best practices 745 business questions 688 results 715 Lookup transformation 747 data overview 690 data profile output 715, 721, performance 745 data preparation 690 723 single Aggregate definition 688 filtering 721 transformation 745 patterns 688 Data Profile Viewer 709, data trends 671 rules 688 714–715 data type casting 745 solutions 633 XML 715 data types 12, 24, 26, 156 data mining algorithms 688, data profiles 711–712, 715 data validation 24, 636 690 types 711 data visualization 650 case 688 data profiling 709–710, 719 data warehouse 326, 328, 636, statistics 688 tools 709 638, 671, 687, 720 Data Mining Data Profiling task 709, administrators 633 Designer 693–694, 711–714, 719 cube partitions 640 696–697 ADO.NET connection design 635 Data Mining Extensions manager 715 field 751 language 694 applying XML 718 incremental loads 636 data mining models 689, 697 connection managers 716 initial loading 636 creating 691 constraints 715 modeling 635 evaluating 695 Data Profiling Task project 641 test sets 695 Editor 716 quality 637 training sets 695 data quality 719 relational 637 viewing 694 data sources types 715 tables 634 data mining project 689 dynamic 716 data warehousing 322, 633, Data Mining Query task 697 ETL flow 719 635–636, 709 Data Mining Query script tasks 723 dimensional modeling 633 transformation 697 SQL Server 2000 715 methodologies 633 data mining techniques transforming output with relational design 633 directed algorithms 688 XSLT 721 data-access layer 6 undirected algorithms 688 XML output 720 database administration 633 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
INDEX 771 database administrators 192, high-performance database tuning 355, 421–422, 633, 670 mode 454 techniques 594 database application high-safety mode 454 database updates 178 development 323 monitoring 454 database users 271 database applications 9, 576 moving data seamlessly 459 DATABASE_MIRRORING Database Audit Specification multiple mirrored 455 object 365 databases 456 DATABASEPROPERTY Database Audit non-traditional uses 462 counter 292 Specifications 379 orphaned mirror 457 DATABASEPROPERTY database audit page repair 451 function 292 specifications 370, 376, performance 455 DATABASEPROPERTYEX 378, 673 preparing the mirror 453 function 292 multiple 376 prerequisites 450 Databases 350 database audits 378 routine maintenance 456 data-binding 213 database availability 449 session 455, 459 datacenter license 518 database backups 331, 451, superhero solution 460 data-centric 726 453 Database Mirroring DataContext 216 database best practices 323 Monitor 454–456, 461 data-driven database 6 database boundaries 20 alerts 455 DataLoadOptions 216 database collation 235 database status 455 data-quality checking 723 database context 395 database mirroring data-quality issue 723 database cursor 6 session 455 DataReader object 264 database design 3, 5, 7, 321, trial run 455 DataSet object 221, 264, 649 323, 327, 576 database mirrors 460 datasets 297 art of 5 database model 219 DataTable 221, 231 database designers 5 database network traffic 214 object 227, 654 database developers 5, 192 database normalization 626 data-tier 456 database diagram 29 database objects 230, 280–282, date boundaries 429 database enforced keys 4 359, 439 DATE data type 429 Database Engine 365, 656 scripting 438 Date dimension 637 database engine 113, 200, database operations 323 date format 289 298, 541, 543, 660 database optimization 219 date formatting function 289 events 673 database options 559 DATE_CORRELATION_ performance 661 database patterns 29, 31 OPTIMIZATION 583 Database Engine Tuning database performance 214, DATEADD function 63–65, Advisor 385 325, 590 67, 284 database failover 455 problems 576 negative values 285 database files 458 database programmer 213 past dates 285 free space 334 database programming 545 DateCreated attribute 471 database free space 421 database protection 24 DATEDIFF function 63–65 database integrity 331 database recovery model 356 dates result set 285 database level 376, 449 database restores 452 datetime data type 429 audit 365 Windows Instant File Davidson, Tom 382 Database Mail 471 Initialization 452 DB2 745 database metadata 217 database schemas 7, 323, 327 DBA 16, 210–211, 260, 262, database migration 458 database server 222, 229, 456, 267, 271, 321, 330, 343, database mirroring 324, 367, 497, 594 347, 383, 421, 435, 450, 401, 433, 449, 459, 559, database snapshots 559, 671 461, 475, 480–481, 484, 671 database source name 664 590, 594, 613, 633 advantages 449 database status 455 application developers 323 automatic failover 449 alerts 455 auditing 322 best practices 450 Database Mirroring disaster recovery 323 business constraints 459 Monitor 455 hardware configuration 323 case study 459 Principal 455 managing any connection strings 455 Suspended 455 applications 324 down time 457 Synchronized 455 managing test failover 456 database transaction environments 324 fast failover 449 logging 609 meetings 322 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
772 INDEX DBA (continued) Deadlock 550 DefaultBufferMaxRows mentoring 325 deadlock 549, 555 748–749 monitoring 325 condition 550 DefaultBufferSize 748–749 people management 324 definition 549 deferred analysis 680 performance detector 550 deferred loading 216 monitoring 325 graph event 550–551 DeferredLoadingEnabled PowerShell 326 handler 25 property 216 project management 325 priority 550 defragmentation 330–331, relational 635, 640 processes 552 334 replication 326 resources 552 indexes 335 roles 321, 327 victim 550 degree of parallelism 383 specialties 327 deadlock graph 550–552, Delaney, Kalen 26 tasks 321, 327 554–556, 619 DelegConfig 512 troubleshooting 326 graphical view 551 DELETE 242, 727 See also database administra- Management Studio 551 triggers 23 tors Profiler 551 DeleteCommand 307 DBA business intelligence XML format 551 deleted tables 21, 23 specialist 328 deadlocks 25, 541, 628 delimited string 221 DBA database architect 327 debugging information 490 delimiter, pipe symbol 287 DBA database designer 327 decision support system 452 demo database 238 DBA developers 327 Decision Trees 691, 694 denormalization 3 DBA manager 327 model 696 denormalized data 4, 21 DBA project manager 327 declarative code 44 DENSE_RANK function 64, DBA report writer 328 development cost 44 68 DBA system maintenance 44 dependencies 277 administrators 327 declarative coding 43–44 combinations 40 DBCC CHECKDB 333, 341, amount of data 45 See also functional 559–560 benefits 44 dependencies DBCC CHECKDB drawbacks 44 Dependency Network operations 558 learning curve 44 viewer 695 DBCC limitations 43 dependency tracking 387 CLEANTABLE 410–411 performance 45 dependent attributes 37–38 DBCC commands 381, 402, declarative constraints 20 dependent column 712 410, 619 declarative dependent entities 160 DBCC DBREINDEX 439 languages 218–219 dependent services 261 DBCC DROPCLEAN- optimizing 219 deployment 299 BUFFERS 236, 612 declarative query 43 deprecated features 291 DBCC INPUTBUFFER 566 declarative referential benefits of tracking 291 DBCC PAGE 554, 555 integrity 481 deprecated functionality 291 DBCC SHOWCONTIG 386, declarative solutions 43 Deprecation 616 Decode-SqlName 350 Announcement 293 DBCC decryption 564 event class 291 SHRINKDATABASE 334 Dedicated Administrator deprecation events 294 DBCC SHRINKFILE 445, 562 Connection 437, 439, 441 Deprecation Final DBCC UPDATEUSAGE 386 dedicated administrator Support 293 DBCC_GROUP 673 connection 476 event class 291 DbDataReader object 228 dedicated filegroup 442, 445 deprecation policy 291 DB-Library 256 disk space 445 DEPRECATION_ DBMS 265 dedicated SQL Server ANNOUNCEMENT 294 engine 643 login 604 DEPRECATION_FINAL_ systems 262 dedicated system 269, 643 SUPPORT 294 dbo schema 290, 404, 553 default catalog 178–179 Derived Column DDL 18, 265, 365, 424, 450 default database 267 transformation 723, 735 code generations 7 default filegroup 360 derived tables 63, 67, 92 queries 262 default instance 257, 268–269 derived variables 690 triggers 365, 380 default partition 414 Description 307 See also data definition default table 433 Designer 332, 338, 341–342 language default trace 619 desktop platforms 297 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
INDEX 773 desktop server 317 disconnected data operation 21 desktop virtualization 519 problem 305 queries 674 destination data 751 discrete variables 690 See also data manipulation destination files 750 disk access speed 607 language destination performance 746 disk contention 523 DMO clustered index 746 disk failures 355 scripting 362 commit size 746 disk I/O 217, 574, 576, 606 See also Distributed Manage- heap table 746 activities 581, 608 ment Objects OLE DB Destination 746 counters 606 DMV categories 401 Raw File destination 746 latency counters 608 DMV measurements 592 destination rows 757 performance 520, 521, 523, DMV metrics 592 destination table 751, 607 DMV queries 405–406, 408, 756–757 performance counters 606, 410–411, 590–591, 597, destinations 726 607, 608, 609, 616, 618 600 See also data destination requests 610 average worker time 594 destructive loads 751 subsystem 609 indexes 600 advantage 751 throughput 613, 617 performance problems 604 determinant columns 712 Disk I/O latency 608 security 590 Developer Edition Disk I/O size 608 See also Dynamic Manage- CDC 681 disk read throughput 615 ment View queries developer-centric 316 disk speed 179 DMV structure 382 development disk storage subsystem 613 DMVs 382–383, 390, 402, 404, environment 456 disk usage 640 575 development tools 727 DISKPERF 578 missing index DMVs 399 Devices collection 358 dispatchers 623 See also Dynamic Manage- dicing 634 dispatching 622 ment Views DIFFERENCE 200–201 Display Term 197 DMVStats CodePlex DISTINCT 238, 244 project 382 differencing disk 522 distinct values 712 DMX language 697 differencing vhds 521 Distributed Management See also Data Mining Exten- dimension attribute Objects 353 candidates 701 sions language distributed transactions 685 dimension attributes 702 DMX prediction Distribution Agent 486–490, aggregations 701 493–495 query 697–698 dimension key 704 command line 490 DMX query 698 dimension level 707 distribution agent 480–481, DNS 504, 668 dimension structures 640 485 See also Windows Directory dimension tables 634, 637, Distribution Agent Name Service 747 metrics 485 DNS name 468 dimensional designs 636–637 Distribution Agents 495 DNS service 268 dimensional model 636, 638, tuning 492 Document Count 197 641 distribution database 486–488, Document ID 198 Analysis Services 636 492 document order 129 dimensional modeling 634, distribution statistics 588 document types 195 637, 641 distributor 492 document_type 195 dimensions 634, 637–639 hardware 485, 492 documentation Analysis Services 636 .DLL files 300 maintaining 324 directed algorithms 688 DLLs 354 domain 33, 235, 464 direct-port operations 269 DM model 687–688 account 464, 473 dirty pages 610, 613 DM techniques 688 expert 28–34, 36, 38–40 DISABLE 181 DM. See data mining group 269 disaster recovery 323, DMFs 196, 382, 402 user 663 327–328, 479, 518 table-valued DMF 386 DOP 383 plans 326, 463 See also Dynamic Manage- See also degree of parallelism testing 358 ment Functions dormant connection 274–275 discography 150, 152, 154, DML 214–215, 422, 450 Dorr, Bob 609 156 activities 377 double insert 4 data-centric solution 150 events 376 double update 4 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.