SQL Server MVP Deep Dives- P23

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

Thêm vào BST

Báo xấu

48
lượt xem 5
download

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

SQL Server MVP Deep Dives- P23: 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- P23

INDEX 795 SQL Server community 221 SQL Server CSS team 577 SQL Server Management SQL Server Compact 3.5 SQL Server data 323 Studio 73, 76, 178, deploying on a desktop 299 SQL Server database engine 270–271, 277, 292, 298, deploying on a device 300 622, 687 300–301, 330, 349, 353, design goals 298 SQL Server Database 362, 365, 382, 453, 476, development tools 300 Mirroring 458 490, 571, 656, 701 SQL Server Management SQL Server databases 745 Database Mirroring Studio 300 compacting 403 Monitor 454 SQL Server Management scaling down 297 database mirroring Studio 2008 302 sharing over network 256 wizard 454 Visual Studio 2008 302 SQL Server destination 747 Graphical User XCOPY deployment 300 SQL Server developers 282 Interface 673 SQL Server Compact 3.5 for SQL Server engine 76 Keys branch 178 Devices 300 SQL Server errors 76 Log File Viewer utility 673 SQL Server Compact error handling 84 maintenance plan 330 Edition 255, 265, 297, SQL Server events 576 Object Explorer 455 305 SQL Server exceptions 80, 84 PRIMARY filegroup 433 ADO.NET data SQL Server Express Edition query window 728 provider 302 258, 262, 268, 272, 276, SQL Server Memory advantage 303 450, 471 Manager 597 architecture 298 SQL Server Extended Events SQL Server messages 82 connecting to from SQL engine 622–623 SQL Server Mobile 478 Server Management architecture 622 SQL Server Native Client 266, Studio 300 SQL Server Failover 692 deployment 299 Cluster 456 SQL Server performance 324 design goals 298 SQL Server Full Text performance issues 590 developer tool Search Thesaurus, SQL Server plan caches 211 integration 302 custom entries 189 SQL Server Policy-Based Man- development tools 298 SQL Server index 243 agement policies 351 DLLs 299 SQL Server installation 271 SQL Server PowerShell 346 SQL Server Profiler 590, 619 extensions 302 SQL Server instances 257, 259, event data 580 merge replication 298 261, 269, 273, 321, 464, performance mobile environments 303 558–559, 561 information 590 multi-user support 298 finding 259 SQL Server Program Files 440 OLE DB provider 302 IP address 269 SQL Server Query programming operating system 325 Analyzer 277 support 302 production 327 SQL Server query redistribution license 299 upgrading 324 optimizer 211 remote data access 298 SQL Server Integration SQL Server relational scenarios of use 303 Services 108, 321, 326, engine 510 single file 299 330, 575, 687, 726, 743, SQL Server replication 326 SQL Server Management 750 SQL Server Reporting Studio 301 data flow task 744 Services 258, 266, 326, transition path from data profiling tools 709 328, 645, 687 SQL Server Express expressions 726 architecture 643 Edition 304 incremental load 754 Catalog 643 Visual Studio 301 SQL Server Integration Configuration Manager 655 Windows service 298 Services 2008 709 Configuration utility 652 XCOPY-style SQL Server job 180 report catalog 643 deployment 298 SQL Server log 77 SQL Server Reporting Services SQL Server SQL Server logins 452 2008, configuration 274, 323 SQL Server management 353 RDL rendering engine 658 SQL Server Configuration long-term management SQL Server resources 257, 265 Manager 256–257 362 SQL Server service 262 SQL Server connection 262 operations team 353 SQL Server Service mechanism 255 PowerShell 364 Account 452 SQL Server counters 613 problems 364 Lock Pages in Memory 452
796 INDEX SQL Server Service Account SqlClient .NET 256 package 330–331, 716–717, (continued) Data Provider 260 719 Perform Volume Mainte- data provider 663 Package Designer 697 nance Tasks 452 SqlClient package Designer 330 SQL Server Service account connection 266 precedence constraints mirror instance 453 Data Provider 259 732 SQL Server Service namespace 266 SQL Server 2005 734 Manager 510 SQLCLR code 227 SQL Server 2008 SQL Server Service pack 462 SqlConnection class 82 version 734 SQL Server services 257 SqlConnection object 262 tasks 331 SQL Server statistics 430 SqlError class 81 XML 721 index statistics 430 SqlError objects 81–82 See also SQL Server Integra- SQL Server Surface Area Con- SqlException class 81 tion Services figuration Utility 256 SqlInfoMessageEventArgs 82 SSIS 2005 data flow task 760 SQL Server system SQLIO 609 SSIS 2008, Data Profiling administrator 269 SQLMail 471 task 711 SQL Server terminology 124 SQLPS 347–348 SSIS data flow 716, 726, 735, SQL Server thesaurus 203 configuration 348 737 SQL Server tools remoting system 349 expressions 734–735 best practices 24 See also SQL Server Power- SSIS data processing data integrity 11 Shell performance 744 performance benefits 24 SQL-related performance best practices 744 SQL Server untrusted counters 385 SSIS expressions 726, 729, authentication 271 SqlRestore method 358 737, 741, 758 SQL Server version 7.0 SQLServer:Deprecated SQL injection attacks 737 code base 575 Features 291–293 SSIS packages 726–727, 737, development team 575 SQLServer:Deprecated Fea- 741 SQL Server version store 558 tures counters 292 connection managers 737 SQL Server virtualization 324 SqlStatementSource expressions 726 SQL Server Workgroup property 728 SSIS variables 732 instance 268 SQLWMIManagement.dll 354 containers for SQL Server workload 590, 594 SSAS expressions 732 SQL Server XML parser 137, algorithms 690 expressions 732 140 data mining 698 SSL 655 SQL Server-based XML 133 data mining engine 688 certificate 267, 648 SQL ServerBuffer Manager- database 688 encryption 267 Buffer Cache Hit UDM cube 687, 698 SSL Web traffic Ratio 597 See also SQL Server Analysis encryption 647 SQL ServerBuffer Manager- Services SSMS 259, 277, 279, 298, Page Life Expectancy 597 SSAS 2008 698 330–331, 353, 368, 370, SQL Service account 368 data mining algorithms 690 373–374, 379, 455, 476, SQL statements 210 data mining analysis 688 482, 542, 701 efficiency 582 DM Model 688 connection 441 tuning 582 SSCM 258, 268 Log Viewer 374 SQL Trace 293, 385, 619, 670, See also SQL Server Configu- MP 342 673 ration Manager Object Explorer 369 overhead 673 SSIS 326, 328, 330, 575, 662, See also SQL Server Manage- SQL trace 380 709, 713, 715, 748, 750 ment Studio SQL Trace Collector 294 control flow 729, 734 SSPI 267, 646 SQL tracing tool 575 data flow pipeline 756 stack dump 622 SQL transaction log 681 Data Profiling task 724 stacked bar chart 714 sql_handle 554 developers 726, 737, 743 staging table 415, 418, 430 SqlBackup method 355 development 741 standalone database SQLCE Expression server 456 SQLCE database 298, 310 Language 757–758 standalone queries 541 See also SQL Server Compact expressions 719, 732 standard actions 330 Edition Lookup transformation 754 Standard template 579
INDEX 797 standby databases 446 stored procedures 12, 20, SUCCESSFUL_LOGIN_ initialization 446 210–211, 213, 217, 223, GROUP 675 standby server 463, 468, 264, 270, 281–283, 299, superhero solution 460 472–473 302, 307, 327, 347, 384, supertypes 6 star schemas 637 393–394, 439, 550, 596, surrogate keys 5, 15, 155, 637 START FULL 663, 668, 680 Suspended 455 POPULATION 181 data containers 221 svcutil.exe 315 Startup Parameters 440–441 generic stored SWITCH 414, 418, 490 state values 712 procedures 387 switches 469 static analysis phase 125 parameter limits 221 switching data 422 static partition productivity 282 benefits 422 elimination 425 quality 40 Sybase 663 static reports 639 system 402 sync agent 310 static SQL 211, 668 table 221 Sync Services security 211 temperory 558 for ADO.NET 306 static statement 729 under-utilized for Feed Sync 306 static tables 179 procedures 394 SyncAdapter 308 statistical information 712 stored subquery 95 SyncDirection 309 statistics 239, 241, 688 string concatenation 211 synchronization 297 collection 335 String Value Editor 759 adapter 306–307 operating system 606 string variables 738 agent 306, 308 table 242 strings 200 agent class 308 tools 606 case sensitive strings 208 methodologies 316 STATISTICS IO 571 null strings 208 objects 626 status 70–71 similar strings 208 providers 306 segment 71 strongly typed 217, 222 technologies 298, 306 stemmer 183–184 subject areas 634, 636 method 308 stemming 182 Finance 634 Synchronization agent activity 184 Human Resources 634 properties 308 Synchronization stock-keeping units 257 Sales 634 architecture 306 stoplists 189 subjects 153 synchronization group 308 adding words 190 SubmitChanges 214 synchronize method 308 advanced queries 191 suboptimal dynamic SQL 211 SYNCHRONIZED 456–457 creating 190 suboptimal plan 218 Synchronized 455 dropping 191 suboptimal query plans 587, synchronized copy 449 removing words 191 589 synchronous 457 user-defined 191 subplans. See Agent jobs bucketizing target 625 stopwords 189 subqueries 66–67, 86 counter target 625 advanced queries 191 efficiency 62 database mirroring 462 custom set 191 performance 63 mode. See high-safety mode customizing 186 subquery 100, 113 targets 625, 629 system set 191 subreport 664 SyncStatistics 309 turning off 191 Subscriber 484, 486–487, 489, synonyms 200 Storage 415 492–493, 495 synthetic drivers 519 storage array 560 Subscriber indexes 485, 491 hardware emulation 519 storage devices 451, 459 Subscriber triggers 485, 491 synthetic network storage engine 298, 584 Subscriber hardware 485, 491 adapters 524 storage failure 462 hardware impedance sys.columns 196 storage format 253 mismatch 491 sys.dm_fts_index_keywords storage overhead 24 subscription database 487 196 storage redundancy 461 transaction log 487 sysmaintplan_log 342 storage subsystem 584 SubscriptionStreams 495 sysmaintplan_logdetail 342 storage system 449 subsets 421 SysMon. See Windows System stored procedure 74, 76, 79, See also partitions Monitor 84, 286–288, 290, 554 subtasks 325 sysobjects table 104 execution replication 487 subtypes 6 SYSTEM 191 layer 6 Success audits 374 system architects 297
798 INDEX system catalog queries 454 table valued parameters 598 TDE. See transparent data system catalogs 439, 447 TABLE variable encryption updating 438 default value 232 TDS 262 System Center Operations not null 232 packets 265, 275 Manager 370 table variables 558, 560 See also Tabular Data Stream System Center Virtual TableAdapter 646 temp database 290 Machine Manager 526 TableDirect mode 303 permissions 290 system error messages 76 TableLock 109 temp directory 580 system errors 75 TableName 308 temp tables 560, 663 system failure 355–356 tables 270 TempDB 264, 579 system health session 628–629 backup 435 tempdb 9, 451, 493, 523 system messages 75 most reads 599 activity 559, 560–561 system object technique 396 most writes 599 ALTER DATABASE 561 system processes 550 normalization rules 28 autogrowth 561–562 system seeks 385 normalizing 28 average size 561 system stoplist 191 PRIMARY filegroup 434 behaviors 559 system stored procedures 278, resetting 754 best practices 560 381 Tablet PC 297–298 capacity issues 635 system tables 409, 436 table-valued functions 393 database 233, 404, 558 system update 438 multi-statement 217 internals 559 System.Xml namespace 723 table-valued I/O contention 563 SystemDataTypes 350 parameters 221–222 I/O subsystem 563 SystemMessages 350 benefits 223 initial size 561 system-provided functions 74 stored procedure 223 LDF file 563 systems engineers 594 Tablix 649 MDF and LDF files 561 Tablix control 658 MDF file 563 T TABLOCK hint 102–103, 108 normal size 563 log file size 103, 108 optimal size 561–562 TABLE 222 performance 102 optimization 561 table tabular data 222 optimized logging full-text index 176 Tabular Data Stream 256 method 563 hint INDEX 587 tabular format 31 optimizing 559 level 449 Tabular report 646 performance 558, 559, 563 normal form 30 talk mode 262 physical disk files 563 page lock 102 Tang, ZhaoHui 698 physical files 562 row lock 102 target database 729 restrictions 559 table lock 102 target partitions 422 sizing 561 types of locks 102 target server 268, 355–356 templates 526 TABLE data type target table 102, 683 temporal sequence 63–65, parameter to a stored triggers 102 67–68 procedure 222 target variable 688 temporal intervals 63, 68 Table Lock 108 targets 621–624 temporary objects 558–559 TABLE object variable 232 task 624 temporary storage 170, 175 table partitioning 413, 419, trace file 624 temporary table 223, 226, 233, 421 types 625 264, 381, 564, 746 architecture 413 usage 625 Temporary tables 264 resources 419 TargetServerURL 651, 655 Teradata 745 storage 414, 416 tasks 726, 729 Terminal Services 519 See also partitioning naming 467 test database 455 Table report 649 properties 727, 734 endpoints 455 table scan 206, 240, 385, 571, types 332 test environment 324, 521, 573, 610, 614 Task Manager 523–524 526, 551, 707 table statistics 602 Performance tab 592 change tracking 179 table storage 445 Task Scheduler 467 test server 518 TABLE type 223, 225–229 TCP/IP 668 test sets 695 table type 222 address 257 test string 239 TABLE type object port 256–257 tested procedure 236 permissions 232 stack 668 testing 326
INDEX 799 text 176, 292 top waits 591 backups 356, 446, 453, 460, text mining DMV query 591 463, 465, 467 transformations 690 wait types 592 communications text replication 488 Top waits query 591 problem 455 text search engine 176 cumulative waits 592 file 272, 559, 595 text-based data types 176 ToString 308 logging 559, 607 text-oriented queries 203 total worker time transaction logs 451, 465 textual form, disadvantages Age in Cache column 593 transactional activity 357 122 CPU cost 593 transactional boundaries 492 The SQL Server Compact 3.5 expensive queries 594 transactional context 485 runtime components 299 trace 590 transactional THESAURUS 183 procedures 551 replication 478–479, 484 thesaurus 182, 184, 188, 203 trace events batch updates 485 activity 184 Deprecation category 291 deferred update 479 customizing 186 end time 580 large number of rows 487 registry entry 186 filtering 579 minimum latency 484 thesaurus file 188 start time 580 reporting environment 491 thesaurus file 188 trace flag 479, 550–551, 619 solution 484 customized 189 1204 550 topology 484 restrictions 188 1222 551 See also replication third normal form 5, 34, 637 3604 555 transactional table 243 third-generation languages 45 3605 551 transactions 222, 401, 461, third-generation program- 652 615 472, 484, 486, 550 ming language 45 8202 479 Transact-SQL 73–74, 161, 163, third-party applications 322, 8207 479 170, 172, 175, 321 325 TRACEON command 551 demo 751 third-party conversion tools query 161 traces 580 526 UNION 164 third-party tools 671 blank trace 580 transform step 689 third-party vendors 324 tracing 381 Transformation Editor 735 Thornthwaite, Warren 637 trace file 580 transformations 326, 726, 754 Threads table 555–556 TRACK_COLUMNS_ transitive dependencies 33, three-and-more-attribute UPDATED 677 34, 35 dependencies 39 tracking deprecated features transitively dependent three-tier distributed methods 293 attribute 34 application 297 track-to-track seek transparent client three-valued-logic 63 time 607–609, 611 redirect 455 throughput 484 traditional OLTP transparent data TIFF 644, 653 approach 640 encryption 564 Tiffany, Rob 305 training sets 695 transport agnostic 306 tightly-coupled database 7 transaction 553 trial run 455 Time dimension 704 committed 553 Triggers 350 Time Series 691 context 267 triggers 8, 13, 20, 25–26, 241, Timesheet Audit Report 89, 92 history 46 299, 302, 671, 684 timestamp columns 681 isolation 9 CLR 23 timestamp data type 421 rolled back 553 data validation 24 timestamps 468, 525 processing 79 execution 21 TMP/TEMP environment scope 274 logic 491 variables 749 transaction identifier 487 overhead 20 tools database 283, 290 last transaction performance 21 benefits 283 identifier 487 side-effects 21 common code 288 transaction log 46, 332, 415, stopping 23 creating 284 419, 451, 455, 463, 480, template 21 TOP clause 46 488, 559, 684 T-SQL 20 top wait types 592 activity 456 types 20 ASYNCH_IO_ analysis tool 486 uses 20 COMPLETION 592 backup 331, 355–356, 455 Trim Namespaces XML WRITELOG 592 backup files 460 task 721
800 INDEX Trojan attacks 270–271 T-SQL scripts 209, 326–327, types of locks troubleshooting 326 362, 466, 481 page lock 102 truly partitioned table 414 database objects 359 row lock 102 TruncateExistingOrCreate- location 464 table lock 102 NewTable 309 TSQL-Locks template 579 truncation 751 T-SQL-specific extensions 218 U trusted authentication 646 tuning 399 Trusted Connection Distribution Agents 492 UBO Wizard 705 option 273 Tuning SSIS 749 UBO. See usage-based opti- trusted connections 269–270 tuple streams 127, 128 mizer trusted security 269–271 definition 127 UDA TrustServerCertificate 267 tuples 128 CLR 244 TRY CATCH 73 binding 128 size limitations 246 tsenu.xml file 186, 188 let clause 128 T-SQL 244 T-SQL 5–6, 13, 45, 52, 89, 129, TVF. See table-valued UDFs. See user defined func- 203, 232, 302–303, 307, function tions 347, 368, 370, 374, TVPs 222 UDM cubes 689–690 376–377, 380, 403, 405, ADO.NET 227 See also Unified Dimensional 453–454, 479, 576, 668 ADO.NET 3.5 226 Model cubes commands 332, 342 ADO.NET client UDM dimensions 689 cursor 6, 49 applications 226 UDP message 257 database application ADO.NET code 228 UDP port 257 development 323 ADO.NET objects 226 Unchanged Rows 758 data type 143 ASP.NET website output 760 DDL statements 620 application 227 uncommitted isolation declarative language 218 client applications 226 level 553 developers 58, 112, 116 client sample application undirected algorithms 688 extensions 217 231 UNDO files 464 functions 415 concurrency issues 230 Unicode 237 incremental loads 750, 751 container objects 232 format 662 in-memory tables 221 creating and using 222 rules 237 operations 264 DataTable object 228, 231 Unicode-enabled type 13 performance counters 292 DbDataReader object 228 Unified Dimensional Model programmer 212 default value 225 cubes 687 programming 343 definition 226 UNION queries 558 queries 117, 212 example 225 UNIQUE constraints 15 query constructs 217 limitations 224, 232 unique constraints 100, 155, query hints 218 order entry system 229 547 query language 217 orders sample 231 unique index 15, 24, 178, 547 set-based objects 221 passed by reference 232 non-nullable 179 set-based operations 232 primary benefit 232 unique index key 425 Stack 622 production unique key 14, 176 statements 223 environment 230 Unique Key Count 194 string handling 221 productivity feature 233 uniqueness constraints 4, 12, TVPs 227 READONLY 224 14, 24–26 workarounds to pass data sample client 227 unit testing 40 containers as a structure 233 unmixed workloads 495 parameter 221 table-valued unnamed instance 268 T-SQL 4.21 3 parameters 222 unstructured documents 177 T-SQL code 23, 210, 277–279, T-SQL 229 untrusted connections 271 282, 323, 326, 343, 374, variable scoping 232 untrusted constraints 19 560 Visual Studio 2008 226 untrusted security 269 generating frameworks 210 two-attribute dependencies 36 UPDATE FROM statement 55 generating programs 210 testing 38 Update lock 553 T-SQL code generation two-phase processing 125 update resources 243 database performance 210 TYPE declaration 172 UPDATE statement 264 T-SQL coding 330 types 621–623, 625 UPDATE STATISTICE 335
INDEX 801 update statistics 332, 335 V virtual SCSI bus 523 UpdateCommand 308 virtual server 668 updated data 681 V2V conversion 526 Virtual Server 2005 R2 SP1 updates Value comparison operators 520 database roundtrips 214 130 virtual SQL Server overhead 242 Value Inclusion profile 712 multiple 526 set-based updates 759 value method 125, 127 virtual tables 20, 242 text file 491 vanilla T-SQL 210 virtualization 518, 523 upgrade 458 varbinary(max) 176, 195, 292 application 520 upgrade problems 291 varchar 176 architecture 518 upgraded database 459 varchar(max) 292 benefits 518 UploadExistingOrCreateNew variables 688, 727, 743 disaster recovery 518 Table 309 continuous variables 690 hypervisor layer 519 usage patterns 689 discrete variables 690 performance 519 usage-based optimization 705, VB. See Visual Basic provisioning 518 707 VB.NET 45, 206, 299 strategy 527 usage-based optimizer 705 VBA 664 technologies 519 use counts 598 VBScript 346, 465, 473 tools 518 UseExistingTableOrFail 309 location 464 virtualized SQL Server 523 User and Token permissions Veerman, Erik 698 performance monitoring cache 598 vendors 326 523 user credentials 270 verb conjugations 203 Vista 187 user database 449 verb-noun naming Visual Basic 758 user database level 452 convention 345 Visual Basic.NET 257–258, user defined functions 283 versioned schema 624 353 user dump 626 vertical filtering 489 visual charting activity 578 generating 627 vhd. See virtual hard disk visual designer 705 User Instance 268 VIA 256 Visual Studio 259, 298, 316, VIA protocol 256 645, 649–651, 654 User Instance connection 273 viable connections 262 BI designer 658 User Instance option 272 data access code user login credentials 272 View Audit Logs 374 VIEW DATABASE STATE 591 generators 272 user login dialog box 272 debugging applications 274 View History 341–342 user lookups 601 View Name Variable 736 MicrosoftReportViewer user objects 362 VIEW SERVER STATE 591 control 653 user processes 550 view services 210 report development user rights level 272 views 6, 20, 211, 270, 283, 299, tools 642 user scans 399, 599, 601 302, 307, 439, 690 Team System 2010 527 user seeks 385, 399, 599, 601 virtual copy 526 Visual Studio BI project 649, user_seeks 600 virtual directory 509, 647 653, 656 user-defined aggregate 244 Virtual Directory Creation Visual Studio BI Report user-defined functions 223, Wizard 509 Project 655 252, 288, 439 virtual disks 521 Visual Studio BI tools 649 user-defined hierarchies 701 virtual hard disk 521, 526 Visual Studio Business Intelli- user-defined hierarchy 704, dynamically expanding 521 gence tools 645 708 virtual IDE bus 523 Visual Studio Data user-defined stoplists 191 virtual machine 518–520, 522, Connection 266 stopwords 191 524, 526 Visual Studio UserDefinedMessages 350 converting to physical environment 354 Users table 555–556 machine 526 Visual Studio IDE 301 utilities 279, 282 design patterns 527 Visual Studio ReportViewer See also custom stored proce- memory allocation 525 control 653 dures multiprocessor 525 Visual Studio SP1 653 utility database 387 NT 4 525 Visual Studio 2003 658 utility stored procedure 439, templates 527 Visual Studio 2005 645 442 test environments 526 MicrosoftReportViewer utility-related objects 394 virtual network adapters 521 control 653 utils. See utilities virtual processors 521 Report Designer 658
802 INDEX Visual Studio 2008 226, 645 web server 517 Windows Event Viewer 374 Local Data Cache Web Service application 263 Windows Forms 263, 654, 658 classes 265 web services 743 applications 273, 642 Report Designer 658 well performing query 111 connection 267 Visual Studio 2008 BI WHERE 92–93, 100, 111, 112, Windows Instant File toolset 649 113, 117, 184, 194, 394, Initialization 452 Visual Studio 2008 SP1 645 398, 545, 568–569, 571 Windows login 342, 452 Report Designer 658 filter 444 Windows Mobile 298, 300, ReportViewer control 659 subquery 112 305 Visual Studio Table Adapter WHILE loops 54, 57, 404, 406 PDA 297 Configuration Wizard 654 whole objects 213 Windows NT 4.0 176 VM. See virtual machine Wi-Fi interface 258 Windows PerfMon VMware Wigley, Andy 305 counter log 580 Server 519 wildcard searches 201, 243 invoking 580 ThinApp 519 wildcards 200–202 resource consumption 580 virtual machine 526 Win32 module 623 Windows Performance Virtual Server 519 Win32 process 623 Monitor 575, 581 Workstation 519 windowing functions 217 Windows platform Voellm, Tony 524 Windows 576 PowerShell 354 Volume Shadow Copy Windows 2000 Windows PowerShell 473 Service 525 Kerberos delegation 505 Windows Presentation VSS 526 Windows 2000 Foundation 642 Volume Shadow Copy Professional 464 Windows process 623 Service 525 Windows 2003 Server 520 Windows Resource Kit VSS Writer Integration Windows 2008 tools 464 Component 525 child partition 519 Windows Security Event VSS-aware applications 525 parent partition 519 Log 366 Windows 2008 Server 523 Windows Security Log 365, Datacenter Edition 518 367–368, 370 Hyper-V role 519 Windows Server 187 W Standard Edition 518 Windows Server Windows 7, 187, 344, 464 2000 497–498, 578 W3C 122 Windows account 468 Kerberos delegation 497 See also World Wide Web Windows ACL rights Windows Server 2003 367, Consortium protections 272 458, 499, 502, 504, 597 W3C XML Windows Application Event constrained delegation 502 recommendation 136 Log 77, 366 Windows Server 2003 R2 460 wait state analysis 575 Windows Application Windows Server 2003 R2 Ser- wait types Log 365, 370 vice Pack 2 525 sys.dm_os_wait_stats 592 Windows Authentication 452, Integration Services 525 WaitForStatus method 261 675 Windows Server 2008 344, waitresource 553 Windows authentication 267, 367, 451, 453, 458–459, waits 626 467, 692 461, 499, 520 WAN 262, 490–491 Windows Authentication R2 344, 451 links 461 Mode 271 Windows Server NT 4.0 578 performance 492 Windows CE 298 Windows service 298 wasted space 247 Windows cluster 461 Windows Support Tools 499 WCF Windows collation 237 Windows System Monitor 575 .NET CF version 315 Windows Communication Windows Task Scheduler 471 service 310, 314 Foundation service 310 Windows Update 300, 457 Services 315 Windows credentials. See user Windows updates 456 subset 315 credentials Windows user credentials 269 WCF Components, Windows Directory Name Windows Vista 464, 467 remote 315 Service 268 Windows Word 644 Web form 228 Windows DLL 206 Windows XP 366, 464 web queries 664–665 Windows Embedded CE 300 Professional 473 URL 665 Windows Event Log 341 WITH CHANGE_ web query wizards 666 Windows event log 673 TRACKING 179
INDEX 803 WITH CHANGE_ binary representations 123 XML DOM 135 TRACKING_CONTEXT CDATA sections 138–139 XML elements 119, 158, 679 Chinese characters 139–140 170 WITH DROP_EXISTING 442 comment node 134 XML entities 137–138 WITH NOCHECK 19 construction 128 XML fragment 136 WithDependencies content indexing 133 XML indexes 138 property 363 data domain 156 XML Infoset 135 witness 450 data element 161 XML namespace 144 witness instance 451 data exchange 151 XML Schema 135, 151, 156 WMI queries 294 data storage 151 XML-based standards 145 work surface 743 document handle 143 XPath 135 work tables 558 document node 135 XQuery 135, 141 worker time 484, 494 element node 134 xml 176 worker time query 593 encoding specifier 136 XML Data Manipulation workload characteristics 602 entitized 135 Language 119 workloads 324 Entitizing 137 xml data type 119, 123–125, World Wide Web extracting data 161 127, 133, 138–139, 143, Consortium 119, 151 full-text indexing 176 149 WPF 642, 658 hierarchical filesystem 121 column 126 See also Microsoft Windows hierarchical structure 134 DOCUMENT 123 Presentation Foundation hierarchical tree exist method 125–126 write activity structure 135 methods 119 stored procedures 596 inbound data flow 160 nodes method 126 write metrics 398 insignificant query method 124, 127 write operation 607 whitespace 139 value method 125, 127 WRITELOG 592 ISO-8859-1 136, 139 XML DML 132 markup language 133 See also XML Data Manipula- X markup tags 134 tion Language Microsoft SQL Server XML document 120–121, 713 XCOPY deployment 2005 150 filesystem 120 disadvantage 300 .NET 723 hierarchical structure 125 XCOPY-style deployment 298 namespace node 135 XML Document Object XDM 123 nodes 134, 161 Model 143 attributes 123 predeclared XML XML DOM. See XML Docu- conceptual root node 123 entities 137 ment Object Model hierarchical tree preserving whitespace 139 XML for Analysis 707 structure 123 processing instruction 134 XML fragments 123 logical nodes 123 prolog 134, 136, 141 XML indexes 132 node hierarchy 123 relational data 150 on-the-fly shredding 132 processing instructions 123 relational form 126 primary XML index 132 See also XQuery/XPath Data relational format 132 relational indexes 132 Model root element 135 secondary XML XDM representation 123 root-level markup tag 134 indexes 132 hierarchical 123 shredding 142 XML integration 133 XEVENT 619, 629 text node 134 XML namespaces 145, 162, architecture 622 transporting data 150 163, 175 elements 622 T-SQL data type 141 declaration 167, 172–173 objects 622 Unicode 136 local name 147 scalability 620 Unicode encoding 140 namespace prefix 146 services 622 user-defined entities 137 XML namespace URI 147 sessions 622 UTF-16 136, 139–140 XML objects 558 usage 626 UTF-8 136, 139 XML output XEvents. See extended events version number 136 package variable 720 XML 119, 133, 150, 175, 221, well-formed 123, 135 profile requests 720 653 WINDOWS-1252 136 XML Path Language 119 attribute node 134 XML data 119 See also XPath attributes 158, 170 XML data type 123 XML processor 161
804 INDEX XML Query Language 119, XPath 1.0 XML indexes 132 151 recommendation 129 XQuery statement 161 See also XQuery XPath Data Model 138 See also XML Query Lan- XML retrieval methods 163 XPath expression 161 guage XML Schema 123, 133, 138, See also XML Path Language XQuery comparison 151, 156, 175, 713 XPath expression 161, 163, operators 130, 132 data export 171 175 data redundancy 157 union 164 equivalent nodes 131 InfoPath forms 174 XPath predicate 161 general comparison redundancy elimination XQuery 119, 123, 133, 138, operators 131 159 145–146, 149 node comparison shared types 159 atomic scalar value 130 operators 131 structure 158 current context node 126 Value comparison XML namespace 162 execution phase 125 operators 130 XML source 720, 722 FLWOR expressions 127 XQuery functions 126 XML source component 721 matching elements 120 contains function 126 XML sources 160 path expression 120, 125, XQuery operators 129–130 dataset 161 132 extracting entities 160 paths 120 XQuery comparison XML standard 151 performance efficiency 132 operators 130 XML task 720 predicates 121–122 XQuery sequences 129–130 XML validity 155 processor 130 empty sequence 129 XMLA. See XML for Analysis queries 132 homogenous sequences 129 XmlDocument class 723 sequences 129 ordered 129 xp_cmdshell 104 static analysis phase 125 subsequences 130 enabling 105 statement 161 XPath 151 textual form 122 XQuery/XPath Data data profile XML 721 two-phase processing 125 Model 122 functions 163 wildcard character 121 XSL Transformations 715 node sets 129 wildcard querying 146 XSLT 715, 721 predicate 161, 175 XML construction 128 data profile XML 721
SQL SERVER/DATABASE SQL SERVER MVP DEEP DIVES EDITORS: Paul Nielsen Kalen Delaney Greg Low Adam Machanic Paul S. Randal Kimberly L. Tripp TECHNICAL EDITOR: Rod Colledge his is no ordinary SQL Server book. In SQL Server MVP Deep Dives, the T world’s leading experts and practitioners offer a masterful collection of techniques and best practices for SQL Server development and administra- tion. 53 MVPs each pick an area of passionate interest to them and then share their insights and practical know-how with you. SQL Server MVP Deep Dives is organized into five parts: Design and Architec- ture, Development, Administration, Performance Tuning and Optimization, and Business Intelligence. In each, you’ll find concise, brilliantly clear chap- ters that take on key topics like mobile data strategies, Dynamic Management Views, or query performance. What’s Inside Topics important for SQL Server pros Accessible to readers of all levels New features of SQL Server 2008 Whether you’re just getting started with SQL Server or you’re an old master looking for new tricks, this book belongs on your bookshelf. The authors of this book have generously donated 100% of their royalties to support War Child International. About War Child International War Child works in conflict areas around the world, advancing the cause of peace by helping hundreds of thousands of children every year. Visit www.warchild.org for more information. For online access to the authors go to manning.com/SQLServerMVPDeepDives. For a free ebook for owners of this book, see insert. ISBN 13: 978-1-935182-04-7 ISBN 10: 1-935182-04-8 55999 9 781935 182047 MANNING $59.99 / Can $74.99 [INCLUDING eBOOK]