Index
A
Ad hoc workload
adhocl.sql
ad hoc query
cacheobjtype value
compiled plan stub
definition
forced parameterization
procedure cache
simple parameterization
BETWEEN operator
limits
simpleparameterization.sql
SQL Server analyzes
sys.dm_exec_cached_plans
T-SQL
AdventureWorks2008R2
Affinity Mask setting
ALTER DATABASE command
ALTER INDEX REBUILD statement
ALTER INDEX REORGANIZE statement
B
BatchCompleted event
BETWEEN vs. IN/OR clause
!<Condition vs. >= Condition
execution plan details
LIKE condition
STATISTICS I0 and QUERY TIME output
BIT data type columns
blocked_process_report
Blocking
atomicity
blocking information
automation, detect and collect
cause of
extended events and blocked_process_report event
with SQL
consistency
durability
fundamentals
isolation (see also Isolation levels)
locks(see Locks)
recommendations
resolution
contended data partitioning
isolation level reduction
query optimization
Bookmark lookups. See Lookups
Bulk update (BU) mode
C
CHECK constraint
Client-side cursor location
Clustered index
CREATE INDEX statement
data access
DROP INDEX statement
frequently updatable columns
heap tables
index creation
narrow index vs. nonclustered index
benefits
blocking and deadlock issues
bookmark lookup
data page
dbo.DatabaseLog table
index page with clustered index on c2
index page with no clustered index
Index Seek operation
row locator
UPDATE operation
presorted data retrieval
primary key constraint
wide keys
Column data type
Columnstore indexes
Column uniqueness
bookmark lookup key
column selection
execution plan
FORCESEEK query
HumanResources.Employee table
WHERE clause
!<Condition vs. >= Condition
Cost-based query optimizer
COUNT(*) technique
Covering index
DBCC SHOWSTATISTICS
execution plan
INCLUDE columns
INCLUDE operator
Index Seek operation
index storage
I/O and execution time
pseudoclustered index
query
recommendations
unexpected covering index
WHERE clause
CREATE INDEX statement
Cursor cost analysis
client cursors
client-side cursor location
default result set type
ADO.NET environment
benefits
disadvantages
MARS
dynamic cursor type
events
fast-forward-only cursor
forward-only cursor type
keyset-driven cursor type
optimistic concurrency model
overhead analysis, T-SQL
application requirement
CASE statement
extended event output
Production.Products table
SELECT statement
SQL queries
RBAR process
read-only concurrency model
recommendations
scroll locks concurrency model
server-side cursor location
static cursor type
T-SQL cursor process
D
Database administration
AUTO_CLOSE and AUTO_SHRINK
minimum index defragmentation
SQL error log file
up-to-date statistics
Database backup compression
Database backup distribution
Database design
adopting index-design
domain and referential integrity constraints
entity-integrity constraints
sp_ prefix
triggers
under-and overnormalization balance
Database Engine Tuning Advisor
advanced tuning options
description
limitations
Management Studio
merits and demerits
Microsoft SQL Server 2012
plan cache
query tuning
Apply Recommendations
general settings
recommendations
successful tuning
Tuning Options tab
reports
server and database
SQL Profiler
tables for tuning
trace file
creation
manual statistics recommendation
workload analysis
tuning progress
Database performance testing
distributed replay
administrator
architecture
client
controller
database testing
hardware installation
parameterized queries
stress mode
synchronization mode
TSQL code
XML configuration files
playback mechanism
query capture mechanism
repeatable process
server side trace
@DateTime
distributed replay trace template
event and column
legally audited data
SQL Server 2005-SQL 11
TSQL profiler
TSQL_Replay template
SQL profiler
SQL server 2012
Database workload optimization
AdventureWorks2008R2 database
ALTER EVENT SESSION command
Cartesian join
costliest query identification
detailed resource use
OLTP database
overall resource use
SQL workload
SSMS/query technique
worst-performing queries
CountDuration
database application designs
errors/warnings
extended events output
external factors analysis
clustered index scan operation
connection options
cost reduction
defragmentation
index modification
internal behavior
joint hint application
procedure modification
processing strategy
query execution plan
statistics effectiveness
in-depth analysis
INSERT statement
optimizing effect
query optimization process
query types
SELECT statement
server resources
SLA
SQL queries
SQL Server performance
SumDuration
UPDATE statement
XML field data
DATEPART function
DBCC SHOW_STATISTICS
covering index using INCLUDE
original index
wide key covering index
DBCC SQLPERF() function
DBlock
Deadlock analysis
avoiding methods
covering index, SELECT statement
lock contention minimization
nonclustered index–clustered index conversion
resource access, physical order
DBCC TRACEON statement
deadlock error
deadly embrace
default system_health
lock_deadlock_chain
lock monitor
owner mode
profiler tool
SET statement
sqlhandle
SQL Server
startup parameter
trace flag 1204
trace flag 1222
TRY/CATCH method
T-SQL
uPurchaseOrderDetail
victim
XML deadlock data
Xml_deadlock_report
Deadlocks/sec counter
Declarative referential integrity (DRI)
WITH CHECK and NOCHECK option
execution plan
DRI defined
DRI not defined
FOREIGN KEY replacement
parent-child table integrity
SELECT statement
Deferred object resolution
execution plan
local temporary table
regular table
Dirty read
Disk bottleneck resolution
application workload
disk alignment
faster I/O path
log files
multiple files and filegroup creation
configuration
disk subsystem
join operation performance
RAID array
configurations
RAID 0
RAID 1+0 (RAID 10)
RAID 1
RAID 5 and RAID 6
SAN system
SSD disks
system memory
table and index placement
table partition
Distributed replay administrator
Distributed replay client
Distributed replay controller
Domain integrity
DRController
DReplay service
Dreplay.exe command
DReplay.Exe.Preprocess.config file
DReplayClient.config file
DROP INDEX statement
DROPEXISTING clause
Dynamic cursor type
E
Entity-integrity constraints
Exclusive (X) mode
Execution plan cache analysis
aging
binding
components
cost-based optimization technique
multiple optimization phase
index variations
nontrivialquery
Optimization Level property
SELECT operator property sheet
sys. Dm_exec_query_optimizer_info view
transaction, quick plan and full optimization
parallel plan optimization
affinity mask setting
factors
INSERT, UPDATE and DELETE statement
max degree of parallelism setting
MAXD0P query
parallelismthreshold
parser
query hash and query plan hash
hash values
ProductSubcategory.Name
query_plan_hash differences
SELECT criteria
sys.dm_exec_query_stats
sys.dm_exec_requests
query optimization
query reusability
ad hoc workload(see Ad hoc workload)
CPU cycles
parameter sniffing
prepared workload(see Prepared workload)
recommendations
ad hoc queries
code with sp_executesql
implicit resolution
OPTIMIZE FOR value query
parameterization feature
prepare/execute model
sp_executesql over EXECUTE
stored procedure creation
SQL Server techniques
sys.dm_exec_cached_plans
trivial plan match
EXISTS technique
External fragmentation
EXTlocks
F
Fast-forward-only cursor
Fill factor
B-tree structure
database-wide level
default value of 0
INSERT/UPDATE query
maintenance
set to 75
table data page
test table
Filtered indexes
Forward-only cursor type
Fragmentation
ALTER INDEX REBUILD statement
blocking
fragmentation table
large object
PAD_INDEX
resolved fragmentation
transaction rollback
ALTER INDEX REORGANIZE statement
automatic maintenance
database analysis
Output.txt file
script actions
SQL Server job
data modification
defragmentation tool
DROP_EXISTING clause
dropping/re-creating index
dynamic management function
extents
fill factor
B-tree structure
database-wide level
default value of 0
INSERT/UPDATE query
maintenance
set to 75
table data page
test table
INSERT statement
leaf pages layout
mixed extent
OBDECTID function
out-of-order leaf pages
OLTP database
overhead
I/O operation
point query
SELECT statements
SQL Server
sys.dm_db_index_physical_stats
avg_page_space_used_in_percent
avg_record_size_in_bytes
detailed index scan
fragment_count
fragmented statistics
page_count
recordcount
small clustered index
types
uniform extent
UPDATE statement
clustered index
DBCC IND
leaf page
SELECT
Full Scans/sec function
G
Globally unique identifiers (GUID)
H
Hardware resource bottlenecks
Hash join strategy
HOLDLOCK
I
Implicit data type conversion
Index analysis
benefits
B-tree structure
heap table
scan process
search process
single-column table
characteristics
BIT data type columns
computed columns
CREATE INDEX statement
Database Engine Tuning Advisor
different column sort order
online index creation
parallel index creation
clustered index (see also Clustered index)
columnstore index
compression
covering index
INCLUDE operator
Index Seek operation
I/O and execution time
pseudoclustered index
query
recommendations
data manipulation queries
definition
design recommendations
column data type
column order
column uniqueness(see Column uniqueness)
narrow index usage
types
WHERE clause and JOIN criteria columns
filtered index
full-text type
indexed views
benefits
CREATE VIEW statement
execution plan
logical reads
net performance improvement
overheads
PurchaseOrderDetail benefit
Queryl.sql
Query2.sql and Query3.sql
intersections
joins
manufacturer index structure
nonclustered index
Production.Product table
spatial data type
StandardCost order
UPDATE statement
XML
Index hints
lookups
optimizer hints
Index join
Index Seek operation
INSERT statement
Intent exclusive (IX) mode
Intent shared (IS) mode
Internal fragmentation
I/O and CPU
ISNULL function
Isolation levels
Read Committed
Read Uncommitted
Repeatable Read
serializable
snapshot
J
Join effectiveness analysis
hash join
merge join
nested loop join
JOIN hint
execution plan
LOOP JOIN
optimization strategy
reads and execution time
SQL Server 2012 supported types
STATISTICS IO and TIME outputs
K
KEEPFIXED PLAN option
KEYlock
Keyset-driven cursor type
L
Leading edge column
LIKE clause
Lock-management process
Locks
bulk update mode
compatibility
database-level lock
exclusive mode
extent-level lock
granularity
heap/B-tree lock
intent shared and intent exclusive mode
key-level lock
key-range mode
lock escalation
locking behavior
clustered index effect on
nonclustered index effect on
serializable isolation level, indexes effect
on table with no index
page-level lock
row-level lock
schema modification and schema stability modes
shared mode
shared with intent exclusive mode
table-level lock
update mode
Lock timeouts/sec function
Lock Wait time (ms)
Lookups
cause of
clustered index
covering index
M
Memory bottleneck analysis
Available Bytes counter
Buffer cache hit ratio
Checkpoint Pages/sec counter
CPU cycles
Lazy writes/sec counter
Memory Grants Pending counter
Page Life Expectancy
Pages/sec and Page Faults/sec counters
Paging File and Page File %usage
SQL Server memory management
buffer pool
max server memory
memory pressure analysis
min server memory
OVERRIDE flag
RECONFIGURE statement
RECONFIGURE WITH OVERRIDE statement
Server properties dialog box
sp_configure system
SSMS
Target Server Memory and Total Server Memory
Memory bottleneck resolutions
application workload
32-bit to 64-bit processor change
data compression
flowchart
memory allocation
process address space, 3GB
system memory requirement
Merge join strategy
Multiple active result sets (MARS)
N
Nested loop join strategy
Network bottleneck analysis
NOLOCK
Nonclustered index
index analysis
locking behavior
lookup(see Lookups)
Nonsargable search conditions
Non-uniform memory access (NUMA)
NOT NULL constraint
ISNULL function
IS NULL option effect
query cost
table scans
test indexes
NULL value
O
Online index creation
Optimistic concurrency model
Optimizer hints
INDEX hint
JOIN hint
execution plan
LOOP JOIN
optimization strategy
reads and execution time
SQL Server 2012 supported types
STATISTICS IO and TIME outputs
Overnormalization
P
PAGlock
Parallel index creation
Parameter sniffing
Performance Monitor counters
Performance tuning
connectivity
data access layer
database design
iteration
performance analysis
performance baseline
performance killers
cursors
database log, improper configuration
excessive blocking and deadlocks
excessive fragmentation
inaccurate statistics
nonreusable execution plans
non-set-based operations
poor database design
poor execution plans
poor indexing
poor query design
query recompilation
tempdb, excessive use/improper configuration vs. price
priority
queries
SQL Server configuration
Person.Address table
Prepared workload
definition
prepare/execute model
SELECT statement
sp_executesql
stored procedures
administration point
business logic
combined plan
data columns
data structure changes
DENY and GRANT commands
Extended Events tool
first execution compilation
network traffic
profiler trace output
security
spBasicSalesInfo.sql
sp_cache_hit extended event
sp_cache_miss event
spMarkDeleted procedure
sys.dm_exec_cached_plans
Processor bottleneck resolution
application workload
efficient controllers/drivers
excessive compiles/recompiles elimination
faster processor usage
large L2/L3 cache
virus checking software
Productld column
Q
Query analysis
AdventureWorks2008R2
ALTER DATABASE command
cost-effective processing strategy
CREATE STATISTICS statement
execution plan
graphical plan
outdated/incorrect statistics
actual and estimated number, rows
output
SELECT statement
Table Scan operator
SET STATISTICS IO/TIME
test table creation
XML plan
Query design
aggregate and sort conditions
arithmetic expressions
avoiding optimizer hints(see Optimizer hints)
database cursors
database transactions
design recommendations
domain and referential integrity
DRI(see Declarative referential integrity (DRI))
NOT NULL constraint(see NOT NULL constraint)
execution plan reuse
EXISTS over COUNT(*) approach
explicit owner definition
implicit conversion
implicit data type conversion
local variable, batch query
clustered index seek details
execution plan
missing index alert
parameter sniffing
relative cost
scans and reads
STATISTICS IO and TIME
lock overhead reduction
logging overhead
multiple queries
naming stored procedures
execution result
extended events output
prefix of sp_.
SP:CacheMiss event
nesting views
nonsargable search conditions
optimizer hints
select_list, minimum columns
added cost, columns
benefits
effective indexes
BETWEEN vs. IN/OR(see BETWEEN vs. IN/OR clause)
WHERE clause usage(see WHERE clause)
SET NOCOUNT
SET NOCOUNT ON
small data sets
UNION ALL over UNION
Query optimization
Query processor tree
Query recompilation
advantages and disadvantages
execution plan
information retrieval
nonbeneficial recompilation
overhead reduction
SELECT statement
SQL Server
statement level
stored procedure
avoidance
DDL and DML statements
OPTIMIZE FOR query hint
plan guide usage
SET options
statistical change
table variables
WHERE/JOIN clause
causes, analysis
cause data column value
compile process
CREATE PROCEDURE statement
deferred object resolution (see also Deferred object resolution)
EXECUTE statement
execution plan aging
explicit call, sp_recompile
individual statement control
performance improvement
schema/bindings
SET options changes
statistical changes
statement identification
events
execution plans
extended events output
sp_statement_starting event
R
Read-only concurrency model
Recompilation threshold (RT) value
Referential integrity
Remote Procedure Call (RPC) mechanism
Repeatable process
REPEATABLEREAD
RID lock
ROLLBACKstatement
Roll forward database
Row By Agonizing Row (RBAR) process
S
Sargable predicate
Schema modification (Sch-M) mode
Schema stability (Sch-S) mode
Scroll locks concurrency model
SELECT statement
Server-side cursor location
SET NOCOUNT statement
SET STATISTICS I0
SETXACT_ABORTON
Shared (S) mode
Shared with intent exclusive (SIX) mode
SP:CacheMiss event
SQL command
SQL profiler
SQL query performance analysis
costly queries
cpu_time field
logical_reads/physical_reads fields
multiple execution
performance improvement
query optimizer
single execution
slow-running queries
system function
execution plans
actual vs. estimated execution plans
client statistics
clustered index scan properties
execution time
graphical execution plan
index effectiveness analysis
join effectiveness analysis (see Join effectiveness analysis)
operation
plan cache
query cost
query optimizer
SET SH0WPLAN_XML
STATISTICS IO
techniques
tool-tip sheet
types
extended events automation
session definition, stored procedure
session script creation, GUI
extended events recommendations
extended events wizard
data storage
event, definition
event filters
execution category, Event library
global fields
GUI
live output
New Session Wizard
query completion monitoring
resource stress
RPC event
Select Events to Capture window
statement column
Success screen
system procedures
T-SQL batch
sys.dm_exec_query_stats output
SQL Server alert
SQL Server Enterprise Manager
SQL Server job
SQL Server Management Studio (SSMS)
SQL server optimization
configuration settings
ad hoc workloads
affinity mask
blocked process threshold
cost threshold
database file layout and compression
fill factor
max degree of parallelism
memory configuration
query governor cost limit
database administration
AUTO_CLOSE and AUTO_SHRINK
minimum index defragmentation
SQL error log file
up-to-date statistics
database backup
compression
distribution
transaction log backup frequency
database design(see Database design)
query design(see Query design)
SQL Server’s query optimizer
SQL statement
SQL trace filters
Static cursor type
Statistics analysis
create_t3.sql
data-retrieval strategies
DBCC SHOW_STATISTICS command
density
filtered index
histogram, definition
indexed column
AdventureWorks2008R2
benefits
data modification
data-retrieval mechanism
disadvantage of
execution plan
SQL Server’s cost-based query optimizer
maintenance
asynchronously updation
auto creation
auto updation
CREATE STATISTICS
manage setting statistics
manual maintenance
sys.sp_createstats
sys.sp_updatestats
UPDATE STATISTICS
multicolumn index
nonindexed column
ALTER DATABASE command
AUTO_CREATE_STATISTICS ON
cost optimization
CPU cycles
DATABASEPROPERTYEX function
data distribution
disadvantages, missing statistics
Index Seek operation
query analysis(see Query analysis)
recommendations
asynchronous update
auto creation
auto updation
backward compatibility
data modification
RID Lookup
SEEK/SCAN operation
small/large result set query
troubleshooting performance
SUBSTRING vs. LIKE function
sys.dm_db_index_physical_stats
Sys.dm_io_virtual_file_stats function
Sys.dm_os_wait_stats function
sys.indexes system table
System performance analysis
baseline creation
counter log creation
performance monitor considerations
reusable list creation
system behavior analysis
disk bottleneck analysis
Avg. Disk Sec/Read and Avg. Disk Sec/Write
current disk queue length
Disk Bytes/sec counter
disk counters
% Disk Time counter
disk transfers/sec
disk bottleneck resolution (see Disk bottleneck resolution)
dynamic management objects
hardware resource bottlenecks
I/O monitoring tools
memory bottleneck analysis (see Memory bottleneck analysis)
memory bottleneck resolutions (see Memory bottleneck resolutions)
memory monitoring tools
DBCC MEMORYSTATUS command
Sys.dm_os_memory_brokers
Sys.dm_os_memory_clerks
Sys.dm_os_ring_buffers
network bottleneck analysis
network bottleneck resolutions
performance monitor tool
processor bottleneck analysis
batch requests/sec
context switches/sec
CPU pressure analysis
% Privileged Time
processor queue length
% Processor Time
SQL compilations/sec
SQL recompilations/sec
Sys.dm_os_wait_stats function
Sys.dm_os_workers and Sys.dm_os_schedulers
processor bottleneck resolution (see Processor bottleneck resolution)
SQL Server
batch requests/sec
database blocking
generic SQL pressure analysis
incoming request analysis
missing indexes
nonreusable execution plans
user connections
virtual machines
T
TABlock
Total latch wait time (ms)
TRY/CATCH statement
U
Undernormalization
UNION ALL clause
UNION clause
UNIQUE constraint
Update (U) mode
UPDATE statement
W
WHERE clause
arithmetic operators
DATEPART function
benefit of not using
detrimental effect
Sales.SalesOrderHeader
SETSTATISTICS IO and TIME output
highly selective clause
SUBSTRING vs. LIKE function
X
XML configuration file
