SQL Server architecture and performance optimization

There's nothing new under the sun

I. SQL Server framework Structure

1. Three stages of query optimizer

1), find the plan cache if you find a direct use

2), simple statement generation 0 overhead execution plan

3), formal optimization in general, the optimization to the cost of less than 1.0 will stop

Defined...

Select * from sys.dm_os_memory_cache_entries where type= ' Cachestore_phdr '

2. Write to Data page

1), the lazy writer (periodically check the buffer neighbor L lead threshold, if too small will go to test for a long time some cache (aging pages) directly kill, dirty pages written to the hard disk, and then the memory space marked as idle)

2), Checkpoint checkpoint process only writes the dirty page to the data page, if the power outage restarts will read the recovery from the log file

3), the basis of memory configuration

Select COUNT (*) *8/1024 as ' Buffer cached Size (MB) '

, Case database_id while 32767 then ' resourcedb ' else db_name (database_id)

End as ' Database '

From Sys.dm_os_buffer_descriptors GROUP by db_name (database_id), database_id

Order BY ' Buffer cached Size (MB) ' desc

Second, log backup and recovery

1. Ordered file transaction log (transaction commit IX Write Transaction day to)

The description record includes the Change data page and page number. Added or deleted data, the transaction information to which the change belongs, and the date and time information of the transaction

2. Data Recovery for transaction logs

Data that occurs after the last checkpoint is used as a recovery

LDF,

Issue: The transaction log has been growing to take the approach

Recovery mode

Simple, complete, bulk-logged

1, the full mode only the transaction log will be truncated

2, can be changed to simple mode of direct truncation (strongly not recommended) Simple recovery model as long as the checkpoint will be truncated

3. Search

Fully prepared

Differential backup

Transaction log Backups

Iii. in-depth understanding of the index

Mssql has only one index mode b-tree mode

1. Clustered index

2, non-clustered cable index

Action Description	Using Clustered Indexes	Using Nonclustered indexes
Columns are often sorted by grouping	Should	Should
Return data in a range	Should	should not
One or very few different values	should not	should not
A small number of different values	Should	should not
A large number of different values	should not	Should
Columns that are frequently updated	should not	Should
FOREIGN key columns	Should	Should
Primary key columns	Should	Should
Frequently modifying index columns	should not	Should

SELECT * FROM dbo. Pce_admin_info WHERE admin_account= ' Admina ' and admin_id = 1

Iv. Optimization Tool Use

1. Implementation plan--EXPLAIN

2. Query Optimization Consultant

3. Activity Monitor

4. SQL Analyzer

Index lookups and index scans

Use db_30014

SELECT * FROM dbo. Pce_site_mobile_region_bind

SELECT * FROM dbo. Pce_site_mobile_region_bind WHERE Bind_mobileno >1000

Cache representation

SELECT * FROM dbo. Pce_site_mobile_region_bind WHERE Bind_mobileno >1900000

SELECT * FROM dbo. Pce_site_mobile_region_bind