Index

A

• actual execution plan
  • about / Working with actual execution plan
  • generating, steps / How to do it...
  • working / How it works...
• algebraization
  • algebraizationabout / Introduction

B

• blocking transaction
  • determining / How to do it..., How it works...
• bulk data
  • loading / Deleting and loading bulk data by splitting, merging, and switching partitions (sliding window)
  • loading, in sliding fashion / How to do it..., How it works...

C

• classification component, resource governor
  • classification component, resource governorabout / Introduction
• client statistics
  • about / Including and understanding client statistics, Getting ready
• clustered index
  • about / Increasing performance by creating a clustered index
  • heap / Heap
  • table scan / Table and Index Scan/Seek
• command parser
  • about / Introduction
• command prompt
  • -D / How it works...
  • -s / How it works...
  • -S / How it works...
  • -E / How it works...
  • -if / How it works...
  • -of / How it works...
  • -F / How it works...
• conditions
  • conditionsabout / Introduction
• CPU usage
  • monitoring / Monitoring CPU usage
  • monitoring, steps for / How to do it..., How it works...

D

• database objects
  • database objectsrestricting / Restricting database objects
  • database objectsrestricting, prerequisites for / Getting ready
  • database objectsworking / How to do it...
• database properties
  • database propertiesabout / Evaluating database properties
  • database propertiesevaluating, prerequisites for / Getting ready
  • database propertiesevaluating / How to do it...
  • database propertiesworking / How it works..., There's more...
• data columns
  • about / Data column
  • ApplicationName / Commonly-used data columns
  • DatabaseID / Commonly-used data columns
  • DatabaseName / Commonly-used data columns
  • HostName / Commonly-used data columns
  • LoginName / Commonly-used data columns
  • ObjectID / Commonly-used data columns
  • ObjectName / Commonly-used data columns
  • SessionLoginName / Commonly-used data columns
  • SPID / Commonly-used data columns
• dirty pages
  • about / Configuring data file and log file on multiple physical disks
• disk I/O statistics
  • monitoring / Monitoring disk I/O statistics, How to do it..., How it works..., There's more...
  • dm_io_virtual_file_stats (DMF) / dm_io_virtual_file_stats (DMF)
  • dm_io_pending_io_requests (DMV) / dm_io_pending_io_requests (DMV)
• DMVs
  • about / Introduction
  • execution-specific / Introduction
  • index-specific / Introduction
  • database-specific / Introduction
  • I/O-specific / Introduction
  • OS-specific / Introduction
  • transaction-specific / Introduction
• DRI
  • about / Improving performance by Declarative Referential Integrity (DRI)
• DTA
  • about / Introduction, Executing Database Tuning Advisor from command prompt
  • XML files, URL / Executing Database Tuning Advisor from command prompt
• DVM
  • sys.dm_db_missing_index_group_stats / Getting ready
  • sys.dm_db_missing_index_groups / Getting ready
  • sys.dm_db_missing_index_columns(Index_Handle) / Getting ready

E

• estimated execution plan
  • about / Working with estimated execution plan, Introduction
  • effects / How to do it...
  • working / How it works...
• event
  • about / Event
• event category
  • about / Event category
• event class
  • about / Event class
• event classes
  • Audit Login / Commonly-used event classes
  • Audit Logout / Commonly-used event classes
  • RPC$Starting / Commonly-used event classes
  • RPC$Completed / Commonly-used event classes
  • SQL$BatchStarting / Commonly-used event classes
  • SQL$StmtStarting / Commonly-used event classes
  • SQL$StmtCompleted / Commonly-used event classes
  • SQL$BatchCompleted / Commonly-used event classes
  • SP$Starting / Commonly-used event classes
  • SP$StmtStarting / Commonly-used event classes
  • SP$StmtCompleted / Commonly-used event classes
  • SP$Completed / Commonly-used event classes
• events
  • filtering / Filtering events
  • filtering, steps / Getting ready, How to do it...
  • working / How it works...
• execution plan
  • graphical execution plan / Introduction
  • text execution plan / Introduction
  • XML execution plan / Introduction

F

• facets
  • facetsabout / Introduction
• files
  • about / Using files and filegroups, How to do it..., How it works...
• Fill Factor
  • about / Playing with Fill Factor
  • value, finding / Getting ready
  • of index / How to do it...
• filter
  • about / Filter
• fragmentation
  • about / Finding fragmentation
  • of index, deciding / Getting ready
  • of index, information gathering / How to do it..., How it works...

H

• hints
  • hintsabout / Introduction
  • hintsquery hint / Introduction
  • hintstable hint / Introduction
  • hintsjoin hint / Introduction

I

• index
  • basic structure / Introduction
  • performance improving, clustered index used / Increasing performance by creating a clustered index, Getting ready, How to do it..., How it works...
  • performance improving, covering index used / Increasing performance by covering index, How to do it..., How it works...
  • performance increasing, by including columns in index / Increasing performance by including columns in an index, How to do it..., How it works...
  • performance improving, columnstore index used / Improving performance by a columnstore index, Getting ready, How to do it..., How it works...
• indexes
  • about / Introduction
  • missing indexes, finding / How to find missing indexes, Getting ready, How it works...
  • unused indexes, finding / How to find unused indexes, How to do it..., How it works...
  • disk space, determining / Determining disk space consumed by indexes, How it works...
• index on computed columns
  • performance, enhancing / Enhancing performance with index on Computed Columns, Getting ready, How to do it..., How it works...
• index performance
  • monitoring / Monitoring index performance, How to do it..., How it works...
  • prerequisites / Getting ready
  • sys.dm_db_missing_index_details (DMV) / sys.dm_db_missing_index_details (DMV)
  • sys.dm_db_missing_index_groups (DMV) / sys.dm_db_missing_index_groups (DMV)
  • sys.dm_db_missing_index_group_stats (DMV) / sys.dm_db_missing_index_group_stats (DMV)
  • sys.dm_db_index_usage_stats (DMV) / sys.dm_db_index_usage_stats (DMV)
  • sys.dm_db_index_physical_stats (DMF) / sys.dm_db_index_physical_stats (DMF)
  • enhancing, indexed view used / Enhancing performance by creating an indexed view, Getting ready, How to do it..., How it works..., There's more...
• INDEX table hint
  • INDEX table hintusing / Using FORCESEEK and INDEX table hint, Getting ready
  • INDEX table hintsteps / How to do it...
• intermediate node
  • about / Introduction

J

• join operator
  • about / Understanding Hash, Merge, and Nested Loop Join strategies
  • hash join operator / Understanding Hash, Merge, and Nested Loop Join strategies
  • physical join operator, in execution plan / Getting ready
  • execution plan, enabling / How to do it...
  • clustered index, creating / How to do it...
  • SELECT query, executing / How to do it...
  • nested loop join operator / How to do it...
  • working / How it works...

K

• key lookups
  • about / Introducing Key Lookups, finding them in execution plans, and resolving them
  • clustered index, need for / Getting ready
  • non-clustered index, need for / How to do it...
  • execution plan, details in text format / How to do it...
  • clustered index scan / How to do it...
  • removing, from non-clustered index / How to do it...
  • working / How it works...
• key lookups
  • about / Introduction

L

• lazy writer
  • about / Introduction
• log space usage statistics
  • monitoring, with DBCC command / Monitoring log space usage statistics with DBCC command, Getting ready, How it works...
• long-running transactions
  • prerequisites / Getting ready
  • sys.dm_tran_session_transactions / How it works...
  • sys.dm_tran_active_transactions / How it works...
  • sys.dm_tran_database_transactions / How it works...

M

• memory
  • memoryconfiguring, in 32 bit versus 64 bit / Configuring memory in 32 bit versus. 64 bit, How to do it..., How it works...
• memory (RAM) usage
  • about / Monitoring memory (RAM) usage
  • monitoring / Getting ready
  • Memory | Available MBytes / Getting ready
  • Memory | Pages/sec / Getting ready
  • Paging File | % Usage / Getting ready
  • monitoring, steps for / How to do it...
• merge join operator
  • about / Understanding Hash, Merge, and Nested Loop Join strategies
• multiple physical disks
  • log file, configuring / Configuring data file and log file on multiple physical disks, How to do it...

N

• nested loop join operator
  • about / Understanding Hash, Merge, and Nested Loop Join strategies
• NOLOCK table query hint
  • NOLOCK table query hintusing / Using NOLOCK table query hint
  • NOLOCK table query hintprerequisites / Getting ready
  • NOLOCK table query hintsteps / How to do it...
  • NOLOCK table query hintworking / How it works...
• non-clustered index
  • need for / Getting ready
  • used, for increasing performance / Increasing performance by creating a non-clustered index, Getting ready, How to do it..., How it works...
• non-clustered index scan
  • versus clustered index scan / How to do it...

O

• @Options parameter
  • predefined values / How it works...
  • 2$ TRACE_FILE_ROLLOVER / How it works...
  • 4$ SHUTDOWN_ON_ERROR / How it works...
  • 8$ TRACE_PRODUCE_BLACKBOX / How it works...
• object plan guide
  • object plan guideabout / Optimizing a query using an object plan guide
• Optimize for Ad hoc Workloads
  • Optimize for Ad hoc Workloadsconfiguring / Configuring "Optimize for Ad hoc Workloads"
  • Optimize for Ad hoc Workloadsconfiguring, steps / Getting ready
  • Optimize for Ad hoc Workloadsworking / How to do it..., How it works...
• out-of-date statistics
  • finding / Find out-of-date statistics and get it correct, How to do it..., How it works..., There's more...

P

• partition switching
  • used, for loading bulk data / Deleting and loading bulk data by splitting, merging, and switching partitions (sliding window)
• PBM
  • PBMabout / Introduction
• PDS
  • to use, in database / There's more...
  • to keep, in database / There's more...
• performance
  • improving, filtered index used / Improving performance by a filtered index, How to do it..., There's more...
• performance counters
  • about / Monitoring CPU usage
• performance monitor
  • about / Introduction
• physical disk
  • non-clustered indexes, moving / Moving non-clustered indexes on separate physical disk, How to do it..., How it works...
• physical table
  • large table, moving to / Moving the existing large table to separate physical disk, How to do it..., How it works...
• plan cache
  • about / Introduction
• plan guide
  • plan guideabout / Introduction
• policies
  • policiesabout / Introduction
• procedure cache
  • procedure cacheabout / Introduction
• processing power
  • processing powerusing, by configuring SQL Server / How it works...

Q

• queries
  • slow running queries, detecting / Detecting slow running and expensive queries, Getting ready, How to do it..., How it works...
  • analyzing, Database Engine Tuning Advisor used / Analyzing queries using Database Engine Tuning Advisor, How to do it..., How it works...
  • performance improving, arithmetic operator used / Using arithmetic operator wisely in predicate to improve performance, How to do it..., How it works...
  • performance improving, by unusing functions on predicate columns / Improving query performance by not using functions on predicate columns, How to do it..., How it works...
  • performance improving, DRI used / How to do it...
  • performance improving, with foreign key / "Trust" your foreign key to gain performance, How to do it..., There's more...
• query
  • queryoptimizing, plan guide used / Optimizing a query using an object plan guide, Getting ready, How to do it...
• query, performance monitoring
  • SET STATISTICS XML used / How to do it...
  • SET STATISTICS IO used / How it works...
• query compilation
  • query compilationabout / Introduction
• query execution statistics
  • monitoring / Monitoring current query execution statistics, How to do it......, How it works...
  • prerequisites / Getting ready
  • sys.dm_exec_connections (DMV) / sys.dm_exec_connections (DMV)
  • sys.dm_exec_sessions (DMV) / sys.dm_exec_sessions (DMV)
  • sys.dm_exec_requests (DMV) / sys.dm_exec_requests (DMV)
  • sys.dm_exec_sql_text (DMF) / sys.dm_exec_sql_text (DMF)
• query optimizer
  • about / Introduction
• query recompilation
  • query recompilationabout / Introduction
  • query recompilationfactors / Introduction
• query selectivity
  • about / Introduction
• query statistics
  • about / Introduction

R

• REBUILD index
  • used, for enhancing index efficiency / Enhance index efficiency by using the REBUILD index, Getting ready, How to do it..., There's more...
  • offline mode / Getting ready
  • using, online mode / How to do it...
  • using, offline mode / How to do it...
  • of table / How to do it...
  • DROP_EXISTING used / How to do it...
  • DBCC DBREINDEX used / How to do it...
  • working / How it works...
• relational engine
  • about / Introduction
• reliability
  • reliabilityused for monitoring query recompilation, at instance level / Monitoring compilations and recompilations at instance level using Reliability and Performance Monitor
• reliability and performance monitor
  • about / Introduction
• reliability monitor
  • about / Introduction
• REORGANIZE index
  • used, for enhancing index efficiency / Enhance index efficiency by using the REORGANIZE index, How to do it..., How it works...
  • without specifying online option / How to do it...
  • DBCC INDEXDEFRAG used / How to do it...
• resource governor
  • resource governorcomponents / Introduction
  • resource governorfunctional diagram / Introduction
  • resource governorconfiguring, with SQL Server Management Studio / Getting ready
  • resource governorparameters, for configuring / There's more...
  • resource governorconfiguring, with T-SQL script / Getting ready
  • resource governormonitoring / Monitoring Resource Governor
• resource governor, components
  • resource governor, componentsclassification / Introduction
  • resource governor, componentsworkload group / Introduction
  • resource governor, componentsresource pool / Introduction
• resource governor, configuring with SQL Server Management Studio
  • resource governor, configuring with SQL Server Management Studioabout / Configuring Resource Governor with SQL Server Management Studio
  • resource governor, configuring with SQL Server Management Studioprerequisites / Getting ready
  • resource governor, configuring with SQL Server Management Studiosteps / How to do it...
  • resource governor, configuring with SQL Server Management Studioworking / How it works..., There's more...
• resource governor, configuring with T-SQL script
  • resource governor, configuring with T-SQL scriptabout / Configuring Resource Governor with T-SQL script
  • resource governor, configuring with T-SQL scriptsteps / How to do it..., How it works...
• resource governor, monitoring
  • resource governor, monitoringprerequisites for / Getting ready
  • resource governor, monitoringsteps / How to do it...
  • resource governor, monitoringworking / How it works...
• resource monitor
  • about / Introduction
  • overview tab / How it works...
  • CPU tab / How it works...
  • memory tab / How it works...
  • disk tab / How it works...
  • network tab / How it works...
• resource pool component, resource governor
  • resource pool component, resource governorabout / Introduction
• rows
  • limiting, to improve query performance / Improving performance by limiting the number of columns and rows, Getting ready, How to do it...

S

• sargable conditions
  • used, to improve query performance / Improving performance by using sargable conditions, How to do it..., How it works...
• seek
  • about / Introduction
• server performance
  • about / Monitoring of server performance
  • prerequisites / Getting ready
  • monitoring, steps for / How to do it...
• SET SHOWPLAN_XML
  • about / Monitoring performance of a query by SET SHOWPLAN_XML
  • usage, steps for / How to do it...
  • working / How it works...
  • StmtSimple element / How it works...
  • Statement SetOption element / How it works...
  • QueryPlan element / How it works...
  • RelOp element / How it works...
• SET STATISTICS IO
  • about / Monitoring performance of a query by SET STATISTICS IO
  • usage, steps for / How to do it...
  • working / How it works...
• SET STATISTICS TIME
  • about / Monitoring performance of a query by SET STATISTICS TIME, Getting ready
  • usage, steps for / How to do it...
  • working / How it works...
• SET STATISTICS XML
  • about / Monitoring performance of a query by SET STATISTICS XML
  • usage, steps for / How to do it...
  • working / How it works...
• SLA
  • about / Introduction
• sliding window
  • about / Deleting and loading bulk data by splitting, merging, and switching partitions (sliding window)
• sp_trace_create, system stored procedures
  • @traceid OUTPUT parameter / How it works...
  • @options parameter / How it works...
  • @tracefile parameter / How it works...
• SQL plan guide
  • SQL plan guideabout / Optimizing a query using an object plan guide
  • SQL plan guideused, for implementing fixed execution plan / Implementing a fixed execution plan using SQL plan guide, How to do it..., How it works...
• SQL Server
  • Buffer Manager | Buffer cache hit ratio / Getting ready, How it works...
  • Buffer Manager | Page life expectancy / Getting ready, How it works...
  • Memory Manager | Memory Grants Pending / Getting ready, How it works...
  • SQL Serverabout / Introduction
  • SQL Serverconfiguring, to use more processing power / Configuring SQL Server to use more processing power, Getting ready
  • SQL Serverinstance configuration, optimizing / How to do it...
• SQL Server instance configuration
  • SQL Server instance configurationoptimizing / Optimizing SQL Server instance configuration, How it works..., There's more...
• SQL Server Profiler
  • about / Introduction, SQL Server Profiler
  • deadlocks, detecting / Detecting deadlocks with SQL Server Profiler, Getting ready, How to do it..., How it works...
• SQL server profiler
  • SQL server profilerused, for monitoring query recompilation / Monitoring recompilations using SQL Server Profiler
• statistics
  • creating / Creating and updating statistics
  • manual creation / Creating and updating statistics
  • automatic creation / Creating and updating statistics
  • updating / How to do it...
  • working / How it works...
  • histogram / There's more...
  • effects, on non-key column / Effects of statistics on non-key column, How to do it..., How it works...
  • effects, on filtered index / Effect of statistics on a filtered index, How to do it..., How it works...
• storage engine
  • about / Introduction
  • and relational engine, differences / Introduction
• system statistical function
  • used, for monitoring system health / Monitoring system health using system statistical functions, Getting ready, How to do it..., How it works..., There's more...
• system stored procedure
  • parameters / How it works...
  • monitoring with / Monitoring with system stored procedure, How to do it..., There's more...
• system stored procedures
  • sp_trace_create / Getting ready
  • sp_trace_setevent / Getting ready
  • sp_trace_setfilter / Getting ready
  • sp_trace_setstatus / Getting ready

T

• T-SQL
  • about / Introduction
• table
  • partitioning, RANGE LEFT used / Partitioning a table with RANGE LEFT, How to do it..., How it works...
  • partitioning, RANGE RIGHT used / Partitioning a table with RANGE RIGHT, How to do it..., How it works...
• table partitioning
  • uses / Introduction
  • RANGE RIGHT / Partitioning a table with RANGE LEFT
• table scans
  • execution plan, fixing / Finding table/index scans in execution plan and fixing them, How to do it...
  • working / How it works...
• tempdb database
  • configuring, on separate physical disk / Configuring the tempdb database on separate physical disk
• TempDB performance
  • monitoring, with database related dynamic management views / Monitoring performance of TempDB database, How to do it..., How it works...
• template plan guide
  • template plan guideabout / Optimizing a query using an object plan guide
• trace
  • creating, steps / Getting ready, How to do it...
  • working / How it works...
  • about / Some background of SQL Trace, SQL Trace, Trace
  • and concepts / SQL Trace terms and concepts
  • architecture / Architecture of SQL Trace
  • and workload / Trace and workload
  • templates / Trace templates
  • creating, system stored procedures used / Creating trace with system stored procedures, How to do it..., How it works...
• trace file
  • about / Trace file
• Trace Flag 1204
  • deadlocks, detecting / Detecting deadlocks with Trace Flag 1204
• trace properties
  • about / Trace properties and Trace definition
• trace table
  • about / Trace table
• trace template
  • about / Trace template
• transaction
  • blocking transaction / Detecting blocked and blocking queries
  • REPEATABLE READ transaction / How it works...
• transactions
  • about / Introduction
  • long-running transactions, determining / Determining long-running transactions, How to do it..., How it works...

V

• view
  • about / Enhancing performance by creating an indexed view
  • benefits / Enhancing performance by creating an indexed view

W

• workload
  • about / Trace and workload
  • Database Engine Tuning Advisor, running / Running Database Engine Tuning Advisor for workload, How to do it..., How it works...
• workload group component, resource governor
  • workload group component, resource governorabout / Introduction