????????????? ?????????? ????????????? ???????? Microsoft SQL Server 2005

Published on Март 5, 2009 by Support · Комментариев нет

SQL Server 2005 ????? ????? ????????????? ?????????? ??????????. ????? ?????? ?? ??? — ??? ??????????? ????????????? ????????? ? ????????? ??????????. ???? ???????? ????????????? ?? ????????? ? SQL Server 2005 ? SQL Server 2000. ?????????????? 98 % ??????????? SQL Server 2000 ????????? ??????????????? ???????? ??????????????? ?????????? ??????????, ??? ??????? ??????? ??????? ?????????. ? ??????????? ?????????? ??? ??????, ???????????? ? ?????????????? ????? ?????????? ?? ?????????????? ???????? ? ?????????? ??????????, ??????? ? ??????????? ???? ???????????? ???????????? ? ?????? ?????????? ??????, ?? ??????? ??????????? ???????? SQL Server 2005 ???????? ??????? ????? ??????????, ? ? ?? ?? ????? ????????? ??????? ?? ?????????? ? ?????????????????. ???? ?? ??? ????? ? ??????? ???? ????????? ????????? ? ??????????? ??????????, ????? ???????? ???? ??????????????? ????? ?????? ????? ?????????? ????????, ??? ????? ????? ????????? ?????? ??????????, ?? ?????? ???????????? ?????? ???????? ? ?????????? ??????????.
?????? ?????????? ? ????? ?????? ??????????? ??????? ?????????????????? ?????????? ??? ?????? ???????? ??????????? ???????????? ?????????? ?????????? ? ?????????????? ??????. ??? ???????? ???????? ??????????????? ??????? ??????? ?? ?????? ? ?????????????????????? ????????.
? SQL Server 2005 ??????? ????????? ??????????? ?????? ?? ???????????:

implicitly create and update statistics — ??????? ???????? ? ?????????? ?????????? ? ???????? ?? ????????? ???????? ?????????? (? ???????? SELECT, INSERT, DELETE ? UPDATE, ????????????? ??????? ? ??????? WHERE ??? ? JOIN ???????? ? ???????? ??? ?????????? ??????????, ???? ??? ??????????, ? ??? ???????, ??? ???????? ?????????????? ??????????).
manually create and update statistics — ?????? ?????????? ???????????, ? ???????? ???????? ?????????? ? ???????? (CREATE STATISTICS, UPDATE STATISTICS, DROP STATISTICS, CREATE INDEX, DROP INDEX).
manually create statistics in bulk — ?????? ???????? ?????????? ??? ???? ???????? ?? ???? ???????? ???? ?????? (sp_createstats).
manually update all existing statistics — ?????? ?????????? ?????????? ?? ???? ???? ?????? (sp_updatestats).
list statistics objects — ???????? ???????????? ???????? ?????????? ??????? ??? ???? ?????? (sp_helpstats, ????????????? ???????? sys.stats, sys.stats_columns)
display descriptive information about statistics objects — ???????? ???????? ???????? ?????????? (DBCC SHOW_STATISTICS)
enable and disable automatic creation and update of statistics — ?????????/?????????? ??????????????? ???????? ? ?????????? ?????????? ??? ???? ???? ?????? ??? ??? ???????????? ??????? ??? ??????? ?????????? (????? ALTER DATABASE: AUTO_CREATE_STATISTICS ? AUTO_UPDATE_STATISTICS; sp_autostats; ? ????? NORECOMPUTE: CREATE STATISTICS ? UPDATE STATISTICS)
enable and disable asynchronous automatic update of statistics — ?????????/?????????? ???????????????, ???????????? ?????????? ?????????? (ALTER DATABASE, ????? AUTO_UPDATE_STATISTICS_ASYNC)

????? ????, SQL Server Management Studio ????????? ? ??????????? ?????????? ????????????? ? ????????? ????????? ??????????, ??????? ????? ????????????? ? ?????????? ???????? ? ??????????? ????? ??? ?????? ???????? ???????.

????????? ? ?????????? SQL Server 2005

? SQL Server 2005 ????????? ????????? ???????? ?? ?????????? ????????, ??????? ????????? ???????????? ???????? ???????? ????? ????? ?????????? ??????? ?? ???? ??????? ????? ???????? ????????? ????????, ??? ?? ???????????? ??????????? ????? ????? ????????? ?????? ??????????. ????? ???????? ????????? ?????????:

String summary statistics: ??????? ????????????? ???????? ??? ??????? ?????????? ?????. ???????? ???????????? ????? ????????? ????????????? ??????? ? ?????????? LIKE.
Asynchronous auto update statistics: ???????????, ?????????????? ?????????? ??????????, ? ????????? ALTER DATABASE ????? AUTO_UPDATE_STATISTICS_ASYNC ????????? ? SQL Server 2005 ? ????????? ?? ?????????. ????? ????? ?????????????, SQL Server 2005 ????????????? ????????? ?????????? ? ??????? ??????. ??? ???? ??????, ??????? ?????? ? ?????????? ??????????, ?????? ?? ?????????, ? ???????????? ??? ??????????? ??????????. ??? ??? ????????? ?????????? ??????? ??????????????? ??????? ??????? ??????? ??? ????????? ????? ??????? ????????.
Computed column statistics: ?????????? ?? ??????????? ????? ????? ?????????? ??????? ??? ????????????? (??? ???? ???????? ??????????? ? SQL Server 2000, ?? ???? ?? ???????????????).
Large object support: ????????? ??????? ????????, ????? ??? ??????? ?????: ntext, text ? image, ? ??? ?? ????? ????? ??????: nvarchar(max), varchar(max) ? varbinary(max), ??????? ?????? ????? ????? ???? ?????????? ??? ???????, ?? ??????? ?????????? ??????????.
Improved statistics loading framework: ?????????? ?????????? ??????????? ???????? ????????? ???????????? ?????, ??? ? SQL Server 2000, ???????? ?????????? ?????????? ??????????, ???????? ???????? ??? ??????????? ? ?????????? ???????, ?? ???? ???? ?????????? ???????? ?????????? ? ?????????????? ??????????? ? ??????????????????.
Increased ability to automatically create statistics on computed columns: ?? ???? ????????? ??????????? ??????????????? ???????? ?????????? ?? ??????????? ?????, ???????????? ? SQL Server 2005 ??? ????????????? ? ??? ???????, ??? ?????? ???????? ?????????? ????????? ???????????? ????, ????? ????? ???????? ???????????? ??????? ? ???????? ????? ??????????.
Minimum sample size: ??????????? ?????? ??????? ?????????? ? 8 ???????? ??? ?????????? ??????, ??? ?? ?????????????? ? ??????? ???????, ???? ??? ?????? ????? ???????.
Increased limit on number of statistics: ????????? ?????????? ????? ?????????, ?.?. ????? ???????? ??????????, ???????? ??? ????? ???????, ?????? ??? ????? 2000, ? ??? 249 ????????? ????????? ????? ???? ?????????, ????? ????? ????? ???????? ?????????????? ?????? ?? ??????? ?????? 2249.
Enhanced DBCC SHOW_STATISTICS output: ?????????? ???????????? DBCC SHOW_STATISTICS ????????? ?????? ?????????? ????? ???????? ??????????, ??? ????????? ???????? ???????????????.
Statistics auto update is now based on column modification counters: ?????????????? ?????????? ?????????? ?????? ???????? ?? ???????? column modification counters. ? SQL Server 2000, ?????????? ?????????? ???????????? ?? ?????? ????????? ??????. ??????, ????????? ????????????? ?? ?????? ???????, ? ?????????????? ?????????? ?????????? ????? ????????????? ??? ??? ????????, ??? ??????? ?? ???? ????????????? ?????????? ?????????.
Statistics on internal tables: ?????????? ?? ?????????? ???????? ?????????? ??? ??????, ????????????? ? sys.internal_tables, ??????? XML ? ?????????????? ???????, ??????? ??????? ???????? ? ??????? ? ???????? ??????????.
Single rowset output for DBCC SHOW_STATISTICS: ?????? ????? ?? ?????? ????? ??? DBCC SHOW_STATISTICS ????????????? ??????????? ??????? ?????? ?????????, ?????? ????????? ? ??????????? ??? ?????? ?????. ??? ????????? ????????? ?????????? ????????? ????????? ??????????? ?????????? DBCC SHOW_STATISTICS.
Statistics on up-to 32 columns: ? 16 ?? 32 ???? ????????? ????? ???????? ? ??????? ??????????.
Statistics on partitioned tables: ?????????? ?? ??????? ?????? ?????? ?????????????? ? ??? ???????????????? ??????, ??????????? ? SQL Server 2005. ??????????? ?????????????? ?????????? (?? ???????????).
Parallel statistics gathering for fullscan: ??? ??????????, ????????? ?? ????? ??????? ????????????, ???????? ?????? ??????? ?????????? ????? ?????????????????? ??? ??? ????????????????, ??? ? ??? ??????? ??????.
Improved recompiles and statistics creation in case of missing statistics: ????? ????? ??????????? ????? ???????, ??? ?????????????? ? ???????? ?????????? ? ?????? ?? ??????????, ? ?????? ??????????????? ???????? ??? ??? ???????? ????? ??????????. ??? ??????????? ?????????? ????? ??????????, ?????????? ??? ??????????, ?????????? ????????? ?????????????, ?????? ???????????, ? ???? ?????????????????. ????????? ?????????? ?????????? ?? ????????. ??? ????????? ?????????????? ??????????, ?????????? ? ??????: Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005.
Improved recompilation logic and statistics update for empty tables: ???????? ?????? ???????????? ? ?????????? ?????????? ??? ?????? ??????. ????????? ?? 0 ?? > 0 ????? ? ??????? ???????? ? ???????????? ??????? ? ?????????? ??????????. ??? ????????? ?????????????? ??????????, ?????????? ? ??????: Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005.
Clearer and more consistent display of histograms: ????? ????? ????????? ? ????? ??????????????? ????????? ??????????. ??????? ????????? ? DBCC SHOW_STATISTICS, ??-?? ??????? ??????????? ?????? ?????? ?????????????? ??????????????, ? ??? ????? ??????????? ? ?????????.
Inferred date correlation constraints: ????????? ??????????? ??????????? ?????????? ???, ? ????????, ????? ????? ???? ?????? DATE_CORRELATION_OPTIMIZATION, ????? ????????? SQL Server ????????? ?????????? ? ?????????? ????? ???? datetime ????? ?????? ??????, ????????? ??????? ??????. ??? ?????????? ???????????? ??? ????, ????? ????? ??????????? ?????????? ??? ?????????? ????? ???????? ??????????????? ??? ??? ?????????. ??? ?????????? ?? ???????????? ??????????????? ??? ?????? ????????????? ??? ????????? ????????? ??? ????????????, ??? ??? ??? ?? ???????? ??????????? ? ??????? ??????, ?? ??? ??? ????? ?????? ? ??????????, ??????? ??????????????? ???????????, ?????? ?????????? ???????? ?????? ???? ???????.
sp_updatestats: ? SQL Server 2005 ??? ????????? ????????? ?????? ?? ?????????????? ??????, ??????? ??????? ??????????, ??????????? ??? ???? ?? ?????????? ?? rowmodctr ? ????????? ????????????? sys.sysindexes, ????????, ????? ???????, ???????? ?????????? ??? ?? ?????????? ?????????. ??? ??? ??????, ? ??????? ??????? ????????????? ?????????? ? 90 ? ????, sp_updatestats ?????????? ??? UPDATE STATISTICS ????????? ??????????????? ??????????????? ?????? ??? ????? ???????? ??? ?????????.

????? ? ????? ?????? ???? ? ????????? ??????, ????? ???????????? ????????? ? ????????? ?????????? ????? ??????????. ? ?????????, ???? statblob ? sys.sysindexes ?????? ?????? ??????????????? ? NULL, ? ??? statblob ???????? ? ???????, ?????????? ??????? ????????.

??????????? ? ????????????

? ???? ????? ???????????? ???????, ??????????? ??? ???????? ?????????? SQL Server 2005:

statblob: ?????????????? Binary Large Object (BLOB), ?.?. ???????, ???????? ?????????????? ??????. ???? ?????? ???????? ?? ?????????? ????????????? ???????? sys.sysobjvalues.
String Summary: ?????? ?????? — ??? ????? ????? ??????????, ??????? ????????? ??????? ????????????? ???????? ? ???? ??????. ???????????? ??? ?????? ????????????? ?????????? LIKE. ???????? ? statblob ??? ???? ??????.
sysindexes: ????????? ????????????? ???????? sys.sysindexes, ??????? ???????? ?????????? ? ???????? ? ????????.
Predicate: ???????? — ??? ???????, ??????? ??????????? ??? ?????? ??? ????. ????????? ???????????? ? ??????????? WHERE ??? ? JOIN ???????? ? ???? ??????.
Selectivity: ????????????? — ??? ???? ????? ? ?????????? ?????????? ?????? ??????, ??????? ????????????? ??????? ????? ?????????. ????? ??????????? ????? ??????? ??????????? ?????????????, ??????????? ??? ?????? ????? ?????, ??????????? ? ???????????, DISTINCT ? ?????? ?????????. ????????, SQL Server 2005 ????????? ????????????? ????????? «Sales.SalesOrderHeader.OrderID = 43659» ? ???? ?????? AdventureWorks ??? 1/31465 = 0.00003178.
Cardinality estimate: ?????? ????? ?????????, ????????? ?????????? ????? ??????????????? ??????. ????????, ???? ??????? T ????? 100000 ?????, ? ?????? ???????? ???????? ??????: T.a = 10, ? ??????????? ?????????? ????????????? T.a = 10 — 10 %, ?? ?????? ?????????? ????????? ? ??? ???? ????? T, ??????? ????? ?????????? ???????? ?????: 10 % * 100000, ? ????? 10000 ?????.
LOB: ??????? ??????, ?????? ????? ????: image, text, ntext, varchar(max), nvarchar(max), varbinary(max).

?????????????? ????????? SQL Server 2005

SQL Server 2005 ???????? ?????????????? ???? ????????? ?????????????? ?????????? ?????? ??????, ??????? ???????? ?????? ??????? ??????????, ?? SQL Server 2005 ?????? ?????????? ?? ? ??? ?????? ????????? ???????:

????? ????? ? ??????? ??? ??????? (???? rows ? sys.sysindexes).
????? ???????, ??????? ???????? ??? ???????? (???? dpages ? sys.sysindexes).

SQL Server 2005 ???????? ????????? ?????????? ?? ???????? ??????? ? ????????? ?? ? ??????? ?????????? (statblob):

?????, ????? ???? ??????? ?????????????? ??????.
????? ?????, ???????????? ??? ???????? ??????????? ? ?????????? ? ????????? (??????? ????).
??????? ????? ?????.
??????????? ?????????? ???????, ??????? ?????? ?????.
?????? ?? ??????, ???? ???? ???????? ?????????? ??????. ?????????, ????????? DBCC SHOW_STATISTICS, ???????? ??????? «String Index», ??????? ????????? ???????? YES, ???? ?????? ?????????? ???????? ?????? ??? ??????.

??????????? — ??? ????? ???????? ??????? ????, ???????????? ?? 200 ????????. ??? ???????? ????, ??? ??????? ?? ???, ?????????????, ? ??? ????????????? ?????????????????? ????? ???? ????????? ?? ?? ????? ??? 199 ?????????? ???, ????? ????????????? ???????? ????????????? ?????? ??????????. ??? ???????, ??? ????????? ????? ?????? ???????. ???? ???????????? ???????? ??? ??????????, ??????????? ??? ????????? ????? ??????????, ? ??????????? ??? ??????? ???? ? ???????????.

RANGE_HI_KEY — ???????? ?????, ???????????? ??????? ??????? ???? ???????????.
RANGE_ROWS — ??????????, ??????? ????? ?????? ????????? (??? ?????? ????? ???????? ????? ????????, ??? ? ?????? RANGE_HI_KEY, ?? ??????, ??? ??????? ???????? RANGE_HI_KEY ? ??????????? ?????????).
EQ_ROWS — ??????????, ????? ????? ????? ? ???????? ????? RANGE_HI_KEY.
AVG_RANGE_ROWS — ??????? ????? ????? ? ??????? ?????????? ? ?????????.
DISTINCT_RANGE_ROWS — ?????????? ????? ?????? ???????? ????? ?????? ????? ????????? (??, ??????? ???????? ????? ??????????? ????????? ??????RANGE_HI_KEY).

??????????? SQL Server 2005 ??????????? ?????? ?? ?????? ???????, ??????? ???????? ?????? ? ?????? ???????? ????? ??????? ??????????. SQL Server 2005 ????????? ??????????? ?? ???????????????? ?????? ???????? ??????? ? ??? ????:

Histogram initialization: ????????????? ??????????? ???????? ?????? ?????, ?? ??????? ???? ?????? ?? ????? ?????????????????? ????????, ???????????? ? ?????? ???????????????? ??????, ? ?? 200 ???????? RANGE_HI_KEY, EQ_ROWS, RANGE_ROWS ? DISTINCT_RANGE_ROWS (RANGE_ROWS ? DISTINCT_RANGE_ROWS ?? ???? ???? ?????? ????? ????). ?????? ??? ?????????????, ???? ???? ???????? ??? ?????????? ?? ????? ????????, ??? ???? ???? ??????? ?????? 200 ????????.
Scan with bucket merge: ???????????? ?? ???????? ? ????????? ???????? ?????? ?????, ?? ???????, ? ??????? ??????????, ?????????????? ?????? ?????????????? ???????? ??????? ??????? ????? ??????????. ?????? ???????? ? ?????????????????? ????? ???? ????????? ? ?????????? ????????? ??? ? ????? ????????, ??????????? ? ????? ???????????? ?????????? (??? ???????? ??????, ??? ??????? ???????? ?????????????). ???? ??? ?????? ????? ????????, ?? ???? ???? ?? ????????????, ???????? ?????????? ????? ?????????? ? ?????? ????????. ??? ???? ?????????? ?????????? ?? ??? ???????????, ????? ????????????? ?????? ??????????. ????? ????? ????? ??????? ?????????? ???????? ? ???????? 200. ???? ????? ??????? ?? ???????? maxdiff ???????????.
Histogram consolidation: ???????????? ??????????? ?????????? ?????? ???, ?? ??????? ????? ???? ?????????? ??????? ??? ?????? ????? ??????????, ???? ??? ???? ?? ????? ??????? ???????????? ????? ??????????. ???????, ???? ???? ??????? ????? ????? 200 ?????????? ????????, ????? ????? ??????????? ????? ???? ?????? 200.

???? ??????????? ???? ???????????? ? ?????????????? ???????, ?? ???????? RANGE_ROWS, EQ_ROWS, DISTINCT_RANGE_ROWS ? AVG_RANGE_ROWS ????? ????? ??????, ? ??????? ??? ?? ????? ????????? ?????? ???????.
????????? — ??? ?????????? ? ????? ?????????? ? ????????????? ??????? ??? ?????????? ????????, ? ??? ???????????, 1 / (????? ????????????? ????????). ????? ??????? ???????????? ? ????????? ?????????, ????? ????? ????????????????? ????? ????? ??????? ? ?????????????? ???????? ?????????, ??????????? ?? ???????????. ??????????? ????? ???????????? ??? ?????? ????????????? ?????????? ? ???????? ? ?????????????, ????????????? ? ??????? ???????????.
? ?????????? ? timestamp (????????????? ?????, ????? ???? ??????? ?????????????? ??????), ????? ????? ? ???????, ????? ?????????? ??? ???????? ??????????? ?????, ?????????, ?????????????? ? ??????? ????? ?????, ? ??????????????? ????? ???????????, ?????????????? ?????????? ?? ?????? ??????? ???????? ??? ???????? All density, ??????????? ??? ??????? ?????? ????????, ? ???????????? ??????? ?????? ?????????? ???????. ??? ???????? ????? ??????? ?? ?????? ????? ????, ????????? ???????? DBCC SHOW_STATISTICS. All density — ???????????? ?? ???? ??????: 1 / (????? ????????????? ???????? ? ?????????? ?????? ???????). ? ????????? ????? ???? ??????????? ??????????????? ????? ????? ???????? ??????.
???????? ????????: ??????????? ? ?????? ?????? ????????????? dbcc show_statistics ?????????? ???????? Density — ???????? ?????????? ???? ????????, ? ????? ?????? ?????, ??? ???????? RANGE_HI_KEY. ???????? RANGE_HI_KEY ?????? ????? ????? ??????????? ? ?????????????. ?????????????, ??? Density ????????????? ???????? ?????????? ? ????????? ?? ????? ????????????? ????????.
?????????? ?? ?????????? ???????? ?????? ?????? ??????? ??: ????? ??????????? ??? ??????? ??????? (?????????? ? ??????????? ??????????), ?????? ???????? ????????? ??? ??????? ???????, ? ???????? All Density ??? ?????? ?????????? ?????????? ???????? (??????? ???? ?????? ???????). ?????? ????? ?????????? ?? ?????????? ???????? (??????????? ? ??? ??? ????? ???????? ?????????) ???????? ? ????? statblob ?????? ? timestamp ?????????? ?????????? ??????????, ?????? ????? ? ???????? ??? ????? ?????????? ???????, ?????? ????? ? ???????????, ? ??????? ??????? ?????. ?????? ?? ?????? ????????? ?????? ??? ??????? ???????, ???? ?? ???????? ?????????? ??????.
??????????? sp_helpindex ? sp_helpstats ??? ??????????? ?????? ??????, ????????? ??? ????????????? ???????. sp_helpindex ?????????? ??? ??????? ???????, ? sp_helpstats ?????? ???? ????????? ?? ???????. ?????? ?????? ????? ????? ?????????????? ?????????? ??? ?? ????????. ??????????? ? ?????????????? ??????? CREATE STATISTICS ?????????????? ?????????? ???????????? ??????????, ?????????????? ???????? CREATE INDEX, ???? ?????? ????????? ?? ??? ?? ????????. ???????????? ??????? ??? ??, ??? ??? ????????????? ??????? CREATE STATISTICS ????? ????????????? ???????????? ?? ????????? ??????, ? ?? ????? ??? ??? ??????? CREATE INDEX ???? ?????????? ????? ?????????????? ?????? ????????????? ???????, ??? ??? ? ????? ?????? ??? ?????????? ??????? ????? ?????????? ??? ?????? ???????.

?????? ???????? ? ???????????? ??????????

?????????????? ? ???? ????? ?????? ???????????? ??, ??? ????????? ?????????? ????????????? ? ???????, ? ????? ??? ???????? ?????? ????????? ? ???? ?????????????? ??????????. ?????????? ?????????? ????? ???????? ?? ??? ???? ??????, ? ?????? ????? ??? ??????????. ?? ?????? ????????? ???? ?????? ??????????????, ????? ??????? ?????? ????? ???????????.

USE tempdb 
GO
-- ??????????? ?? ????????, ????????? ??? ?????????? ???????? ??????? ???????.
IF object_id(N'Person.Contact','U') IS NOT NULL
DROP TABLE Person.Contact
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Person')
DROP SCHEMA Person
GO
- ??????? ??????? ????? ? ???????.
CREATE SCHEMA Person
GO
CREATE TABLE Person.Contact(
FirstName nvarchar(60), 
LastName nvarchar(60), 
Phone nvarchar(15), 
Title nvarchar(15)
)
GO
-- ????????? ??????? ??????????? ????????.
INSERT INTO Person.Contact 
   VALUES(N'James',N'Smith',N'425-555-1234',N'Mr')
INSERT INTO Person.Contact 
   VALUES(N'James',N'Andersen',N'425-555-1111',N'Mr')
INSERT INTO Person.Contact 
   VALUES(N'James',N'Andersen',N'425-555-3333',N'Mr')
INSERT INTO Person.Contact 
   VALUES(N'Christine',N'Williams',N'425-555-0000',N'Dr')
INSERT INTO Person.Contact 
   VALUES(N'Susan',N'Zhang',N'425-555-2222',N'Ms')
GO
-- ???????????? ????, ??? ??? ?????????????? ?????? ??? ?????? ? ??????? ?????????.
sp_helpstats N'Person.Contact', 'ALL'
GO
-- ?????? ??????? ?????????? ?? LastName.
SELECT * FROM Person.Contact WHERE LastName = N'Andersen'
GO
-- ???????????? ????, ??? ?????????? ???? ????????????? ??????? ??? LastName.
sp_helpstats N'Person.Contact', 'ALL'
GO

?????????:

statistics_name	          statistics_keys
-----------------------------------------------
_WA_Sys_00000002_1B29035F   LastName

-- ???????? ???????, ??? ??? ????? ????????? ? ??????????.
CREATE NONCLUSTERED INDEX Phone on Person.Contact(Phone)
GO
-- ?????????, ??? ???????? ??????? ??????? ????????? ?????? ??????????.
sp_helpstats N'Person.Contact', 'ALL'
GO

?????????::

statistics_name	          statistics_keys
-----------------------------------------------
_WA_Sys_00000002_1B29035F   LastName
Phone	                   Phone

-- ??????? ?????? ?????????? ??? ?????????? ?????: ??? ? ???????.
CREATE STATISTICS FirstLast ON Person.Contact(FirstName,LastName)
GO
-- ??????????, ??? ?????? ? ??????? ???? ??? ??????? ??????????.
sp_helpstats N'Person.Contact', 'ALL'
GO

?????????::

statistics_name	          statistics_keys
-----------------------------------------------
_WA_Sys_00000002_1B29035F   LastName
FirstLast                   FirstName, LastName
Phone	                   Phone

-- ?????????? ?????????? ??? LastName.
DBCC SHOW_STATISTICS (N'Person.Contact', LastName)
GO

?????????::

?????????? ?? ????????? ?????????:
Name                        Updated	Rows        Rows Sampled Steps Density Average key length String Index
----------------------------------------------------------------------------------------------------------
_WA_Sys_00000002_1B29035F   Mar 25 2005 11:21AM  5   5       4     0       13.6                YES

??????? ?????? ????? ? ????????? ? ??? ????????? ? ?????:
All Density Average Length Columns
-----------------------------------
0.25        13.6           LastName

???? ???????????:
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
------------------------------------------------------------------
Andersen     0          2       0                   0
Smith        0          1       0                   1
Williams     0          1       0                   1
Zhang        0          1       0                   1

-- ???? ?? ?????? ??? ??????? ??????????, ????????????? ?????????? ???? ????????, 
-- ? ??? subsitute ? ???????? ??????? ????????? ??? DBCC SHOW_STATISTICS, 
-- ?? ?????? ???????????? ???????? ?????????? ???? ??????? 
-- (?????? ??? ????????????? ?????????? ??????? ?????????? 
-- ????????? ????? ????????? ?? ??????).

DBCC SHOW_STATISTICS (N'Person.Contact', _WA_Sys_00000002_2D7CBDC4)

-- ?????????? ?????????? ???? ??????? ????????????, 
-- ??? ?? ????? ?????????? ?????????? ?? ????? ??????? ??? ??????? ??????????.
GO
-- ????????? ????????? ????????? ????????????? ?????????? ?? ?????????? ?????.
-- ???????? ???????? ?? ??? ?????? ?????? ????????? ??? ??????? rowset.
DBCC SHOW_STATISTICS (N'Person.Contact', FirstLast)

????????? (?????? ?????? rowset)::

??????? ?????? ????? ? ????????? ? ??? ????????? ? ?????:
All density Average Length Columns
----------------------------------------------
0.3333333   11.6           FirstName
0.25        25.2           FirstName, LastName

????? ??????? ????????? ??????????? ??????????? ??? ??????? ???????, ????????? ????????? ???????:

USE AdventureWorks
-- ??????? ????????? ????? ???????.
IF EXISTS (SELECT * FROM sys.stats 
           WHERE object_id = object_id('Sales.SalesOrderHeader')
           AND name = 'TotalDue')
DROP STATISTICS Sales.SalesOrderHeader.TotalDue
GO
CREATE STATISTICS TotalDue ON Sales.SalesOrderHeader(TotalDue)
GO
DBCC SHOW_STATISTICS(N'Sales.SalesOrderHeader', TotalDue)

???????? ?????????? ??? SQL Server 2005

?? ?????? ??????? ?????????? ??? SQL Server 2005 ??????????? ?????????:

??????????? ????????????? ??????? ??????????, ??? ???????? ?????? ??????????? ?????????? SELECT, INSERT, UPDATE ? DELETE, ????? ???????? ????? AUTO_CREATE_STATISTICS, ??????? ????????????? ?? ?????????.
? SQL Server 2005 ????????? ????????????, ??????? ???? ???????? ?????????????? ??????????, ????????: CREATE INDEX — ??????? ??????????? ??????, ? ????? ??????? ??? ???? ????? ?????????? ?? ??? ?????????? ?????, ???????????? ????????? ???? (?? ?? ??????, ???????? ? ?????? ????). CREATE STATISTICS — ?????????? ?????? ????????? ?????????? ?? ????????? ???? ??? ?????????? ?????.
????? ??????????? ???????, ???? ????????? ?????? ???????? ???????? ?????????? ??? ????????. ? ???????? ?????, ?????? ?? ????? ???????? ????????? ???? ?? ????????? ???? ???? ??????. ??????????? sp_createstats ??? ???????? ?????????? ?? ???? ????????? ????? (????? ????? ? ????? XML) ?? ???? ???????????????? ???????? ??????? ???? ??????. ??? ???? ????? ??????? ?????????? ?? ????? ??????????? ??? ??? ?????, ??????? ??? ????? ??????? ??????????.
?????????? dbcc dbreindex ??? ?????????????? ?????? ??? ?????????? ???????? ? ??????? ? ????????? ???? ??????.
????? ??????????????? Management Studio, ????????? ??????????????? ????? ??? ???????? Table, ???????? ?????? ??????? ???? ?? ????? Statistics, ? ?????? ????? New Statistics.
????? ???????????? ??? ???????? ???????? ??????? Database Tuning Advisor (DTA).

? ???????? ??????? ??????????? ??????? CREATE STATISTICS ??? ??????? AdventureWorks.Person.Contact:

CREATE STATISTICS FirstLast2 ON Person.Contact(FirstName,LastName)
WITH SAMPLE 50 PERCENT

??????, ?????????????? ??????, ?????????? ?? ??????? ???????, ??????????, ????? ????????? ??????? ???? ?????????? ???????. ??????, ????? ???? ??????, ????? ?????????? ?? ??????? ??????? ????? ?????? ????? ?????????????? ??????, ????????, ????? ???????? ? ??????? ?? ??????? ??????? ?? ????? ????????? ????????. ?? ????????? ???????? ??????? ????? ????, ???? ?????? ????????????? ??? ??????????????. ?????????? ??? ????????????? ????? ???? ??????????? ???????? ????????, ??? ???????? ?????? ? ???, ??????? ??? ???????????? ??? ?????????????. ??????, ??????? ??????? ? ?????? ????????????? ???????????? ??????, ??? ??? ???? ????? ?????? ??????????. ????? ????? ??????????? ?????????? ??????? ???????? ????? ??????????.
?????? ??????? ??????? ???? ?????????????? ?????? ?? ???? ?????. ? ???? ??????, SAMPLE 50 PERCENT ????????????, ? ????? ?????????????? ??? ???????, ?????? ??? ??? ??????? ?????????. ?????????? ??????? ????? ??? ?????????????? ???????? ???????????? ?????? ? ??????????? ?????? ?? ??????? ? ???????, ? ??????? ????? 1024 ??????? (8 ?????).
? SQL Server 2005 ?????????????? ?????? ????????? ??? ???? ???????? ?? ????? ?? ????????. ??? ?????????? ???????? SQL Server ????????????? ??????? ?????????? ?? ?????? ????. ??? ?????????? ????????? ?? ?????, ??? ??????? ??????????? ????? ?????? ??? ?? ????????? ??????????????? ????????? ??? ?????????????. ?? ???? ? ?????????? ?? ????? ???????. ?????????? ?? ????? ???? ?????? ?????????????, ????? (1) ???? ?????? ? ?????? — read-only, (2) ?????????? ????? ?? ??????????? ? ????????????? ??????????, ? (3) ??? ?????? ???? ?? ?????????????? ?????????????? ???????????.
??????? ??????????????? ???????? ?????????? ????? ???? ????????????? ?? ?????? ???? ??????, ??? ???? ????? ?????????:

ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS OFF

????? ??? ?? ????? ????????? ?? ?????? ???? ?????? ?????????????? ???????? ??????????:

ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS ON

?????????????, ????? ?? ???????? ???????? ON. ????????? ?????????????? ???????? ?????????? ?????, ???? ?? ?????? ???? ?????? ????????? ? ??? ???????? ??????????????????, ? ????? ?????????? ???????? ?? ???????? ?? ????????? ??????? ??????? ??? ????????? ??????.
?? ?????????, ?????????????? ?????? ?????????? ?? ??????? ?? ???????? ?????? ??????, ????? ??????????? ??????? CREATE STATISTICS ??? ????? ?????????? ????????? ?????????????. CREATE INDEX, ??? ??? ?????, ????????????? ???? ????? ??????. ???????, ?????? ????? ?????????????? ?????? ??? ???????? ??????? ?????????? ?? ?? ???????? ?????? (???????????? ???????????? ???? ???????). ??????? CREATE STATISTICS ????????? ???????? ? ??????????? WITH ?????? ??????? ???????, ? ????? ???????????? ???????????? ???? ??????? ??? ???????? ??????? ?? ????????????? ?????? ??? ????? ??????????? ????? (??????? ???????????????? ??????????????). ????? ????? ???????????? ?????????? ?????? ???????? ??????, ???? ? ??????? UPDATE STATISTICS ?????? WITH RESAMPLE. ??? ???????? ???????, ????? ???? ??????? ?? ????????? ????? ??? ??????? ????? (? ?????? ?????? ?????????? ?????????? ? ?????????? ??????? ????????????), ? ???? ???? ?????????? ?? ?????? — ?????? ??????? ???? ??? ?????????? ????? (?????????? ????????? ?? ???????? ??????). ????? ????????????? ????? RESAMPLE ? UPDATE STATISTICS ??????? ?????????? ????????????? ??????? ??? ????????, ? ?? ??????? ??????? ??? ????????? ?????.
??? ????????? ?????? ??????? ??????? ?????????? ??????? 8 ????? ??????. ???? ??????? ? ?????? ???? ?????????, ? ??????? ?????????????? ? ???????? ?? ????????? ????????, ? ????? ????? ??? ?????????? ?????????? ???????????? ????? RESAMPLE, ?? ???????? ?????????? ??????? ???????????? ???????, ?? ?????? ?? ??, ??? ?????? ??????? ??? ??? ????????? 8 ?????. ???????, ????? ???????? ????????????? RESAMPLE, ???? ?? ?????? ????????? ???????????? ???????? ?? ????????? ???????, ? ?? ???????? ?? ????????? ??????? ???????.
??????? ??? RESAMPLE ???????????, ??? ??????? ?? ????? ?????????? ????? ? ?????? ????? ????? ? ???????, ?????????? ?? ????? ??????????? ?????????? ??????????. ??? ??? ???????? ???????? ??????? ??????? (sampling rate) ????? ?????????? ??-?? ?????????? ????????? ????? ???????, ??????? RESAMPLE ???????? ?????? ???????????? ?????????????? ??????? ??? ??????? ??? ??????? ????????????. ??? ??????????????? ????????????? ??????? ????? ???? ?????????? ?? ?? ????? ??????? ???????, ??????? ?????????????? ?? ????? ? UPDATE STATISTICS, ???????????, ????? ???????, ?? ????????????? RESAMPLE.
??????? dbcc show_statistics ?????????? ???????? ??????, ????????????? Rows Sampled. ????????????? ????????? ??? ??????????? ?????????? (? ??????? ?? ????????? ? ????????? ?????) ?????? ???????????? ? ?????????????? ???????? ?? ????????? ???????. ???????? ?? ????????? ??????? ??????? ???????? ???????? ???????????? ???????? ?? ??????? ???????, ??? ????????? ???????? ?????????? ???????????? ?????? ???? ??? ????? ??????? ??????.
????? ?????????????? ?????? ??????? ? ?????????, ??????????? ?????? ??????? ???? ??????? ??? ?????? ??????????. ???? ??????? ????? ???????? ???, ?????????? ?????? ??? ?? ?????????? ??????. ??? ?????????????? ???????, ??????????? ????????? ?? ???????? ?? ???? ???????, ? ??????? ?????? ????????? ????????????? ?? ??????? ????????? ???? ??????????. ??? ?????? ??????? ????? ?????????, ? ???? ?????????? ????? ??????. ??? ??? ????? ???????, ??????? ?? ????????????? ?? ????? ?????????? (???? ????? ???? ??????? ??????????), ?????????? ????? ??????? ?? ????????? ??????? ????. ??? — ????? ???????? ?????? ??? ???. ??? ?????????? ? ?????? ?????????????, ??????? ?????????? ???????? ????? ??????? ?? ???????????, ? ?? ?????? ?? ???????? ??????????, ??? ??? ????? ?????? ???? ??????? ? ????? ????????? ??????????.
?????????????? ???????? ?????????? ????? ?????????????? ? ??????? SQL Server Profiler. ????????? Auto Stats ????????? ? ?????? ??????? ???????????: Performance. ????? ??? ??????????? ?????? ??? ????????? Auto Stats ????? ??????? ???????: IntegerData, Success ? ObjectID. ????? ????? ???????? ????????? AutoStats, ? ??????? Integer Data ????? ?????????? ????????? ??? ??????????? ????????? ??? ?????? ???????, ? ObjectID ????? ????????????? ???????, ? ? ??????? TextData (?????????? ? ?????? ?? ?????????) ????? ????? ?????, ??? ??????? ???? ????????? ???????? ??? ?????????? ??????????, ??????????? ???????????????? ?????????? Created: ??? Updated:. ? ???? Success ????? ??????????? ?????????? ?? ?????????? ??? ?????? ???????? Auto Stats. ?????????? ????? ??? ????????? ????????:

????????????	????????	????????
FAILED	0	????????? ???????? ??? ?????????? Auto Stats, ?? ????? — ???? ????????, ????? THROTTLED (??. ????). ????????, ???? ?????? ???? ? ?????? read-only.
SUCCESS	1	???????? ???????? ??? ?????????? Auto Stats.
THROTTLED	2	????????? ???????? ??? ?????????? Auto Stats, ?????? ??? ??????????? ??????? ????? ???????????.

? ????????? ??????? ????? ????????? ????????? AutoStats, ? ??????? ?? ???? ??????? ??? ????????? ??????? ?????????????? ??????. ????? ????????? ??????????, ????? ????????? ????? auto update statistics ??? ????? ???? ????????????? ???????????? ?????????? ????????? ? ???????, ? ??????? ?????????? ??????, ? ??????????? ?? ???? ??????? ??? ?????? ? ???? ??????? ?? ????? ?????????? ??????? ??-?? ????????? ????? ??????? ? ??????????? ??????????? ???????? ?????.
??????? DROP STATISTICS ???????????? ??? ???????? ??????????, ?? ?? ???????? ??????? ?????????????? ??????, ??????? ???????? ???????? ????????? ???????? ???????. ????? ?????????????? ?????? ????????? ?????? ?????? ? ????????? ???????.

????????? ?????????? ? SQL Server 2005

????? ?????? ???????? INSERT, DELETE ?/??? UPDATE, ??????????? ? ????????, ?????????? ??? ?? ????? ???????? ?????????????? ????????????? ?? ??????????? ???? ??? ???????. ???? ???????????? ???????? SQL Server ??????????? ?????????? ??? ?????? — ?????? ???? ???????, ??????? ???? ????? ???? ?????????? ???????????? ????????????, ??????, ??? ????? ????, ??? ?????????????? ?????? ???? ??????? ??? ?????????; ? ????? ?????? SQL Server ????????????? ??????? ?????????? ?? ??????? ??????? ???????? ????? ???? (??? ??? auto update statistics). ?????????????? ?????????? ?????????? ????? ?????????????? ???????????? ??????? ??? ??????????? ????? ??????????, ? ???????? ?????? ??????????? ? ??????? ????. ?????????? ????? ????????? ????? ??????????? ???????, ???? ????? AUTO_UPDATE_STATISTCS_ASYNC ????????? ???????? OFF, ? ???? ???????? ON, ?? ?????????? ?????????? ????? ????????? ??????????.
???? ?????? ????????????? ?????? ???, ? ???????????? ????? ??????????????? ?????? ??????????, ? ??? ???? ???? ?????? ??????????, ?????, ? ??????, ???? ??????????? ?????? ?????????? ???????, ?? ????? ????????. ????? ?????????? ??????? ? ????????? ??? ????? ? ???, ??? ?????????? ???????????, ????? ??????????, ?? ???????? ?? ??? ??????????. ???? ??? ????????, ???? ????????? ?? ????, ? ?? ????? ?????????? ??????? ?????????? ????? ?????????. ???? ????? ????????? ?? ????, ???? ?????????? ????? — ???? ?????????????? ??????, ?? ??????? ?? ???????.
SQL Server 2005 ????????? ??????? ?? ?????????? ??????????, ??????????? ?? ?????????? ? ???????? column modification counters (colmodctrs).
??????? ?????????? ????????? ??????????? ? ????????? ???????:

????? ?????????? ?????????? ??? ?????????? ???????, ??? ?????????? ????:
1. ?????? ??????? ? ????????? ?? 0 ?? > 0 ?????.
2. ????? ????? ? ??????? ????? ????? ?????????????? ?????? ???? ?? ????? 500, ? colmodctr ??? ??????? ???? ??????? ?????????? ????????? ? ??? ??? ????? ??? ?? 500.
3. ???????, ? ?????? ????? ?????????????? ??????, ????? ????? 500 ?????, ? colmodctr ??????? ???? ??????? ?????????? ????????? ????? ??? ?? 500 + 20 % ?? ????? ????? ? ??????? ?? ?????? ????? ?????????????? ??????.
???? ?????? ?????????? ????????? ? ????????? ???????, ?? ????? ????????? ?????????? ?? ????????? ???? ????????, ?? ??????????? ????, ??? ????????? ????? ??????? ????? ??? ?????????? ??????????, ?????? 6 ???????, ?????? 500, ??? ??? ???? ??????? ? ?????? 2 ??????????? ????????.

????????? ??????? ?????? ?? ????? ??????????.
????????? ???? ???????? auto update statistics ????? ???? ???????? ?? ????????? ???????.

?? ?????? ???? ??????, ????????? ????????????? auto update statistics ????????:
ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF
?? ?????? ???????, ????????? auto update statistics ????? ????????? ????? NORECOMPUTE ??????? UPDATE STATISTICS ??? ??????? CREATE STATISTICS.
??????????? sp_autostats, ??????? ?????????? ????????? ????????? auto update statistics ??? ??????, ???????? ??? ???????? ??????????.

????????? ????????? ????????? ??????????????? ?????????? ?????????? ????? ???? ??????? ??????? ????, ??? ??? ?????????????? ? ALTER DATABASE, UPDATE STATISTICS ??? sp_autostats.
SQL Server 2005 ????? ????????????? ????????? ?????????? ?? ???? ?????, ??????????, ?????????? ??? ?? ?????? ???????? ??????????. ?? ?????? ?? ??, ??? ????? ???????? ??? ????????? ? ??????? ????? ????? ??????? sp_autostats ??? ???? ?????????? ? ????? ???????, ????????? ?????????? ????? ???????? ????????????? ??? ??????? ??????? ?????????? ? ??????? ???? ???????. ?? ?????????? ??????????, ??????? ?? ???? ??????? ?????????? ? ???, ??? auto update statistics ????????? ???????? ON ??? OFF ??? ????????? ???????.
? ?????????????? ???? ??????? ??????? ????????? ?????? ?? ?????? ????????? ??? ???? ??????, ??????? ? ?? ???????? ?? ??????????? ????????? ??? ???????:

????????? ??? ???? ??????	????????? ??? ??????? ??? ??????? ??????????	Auto Update Statistic ?? ?????? ???????
ON	ON	ON
ON	OFF	OFF
OFF	ON	OFF
OFF	OFF	OFF

?? ???????? ???????? ????????????? ???? ?????? ??? auto update statistics ???????? OFF, ???????????? ??? ? ON ??? ?????????? ????? ??????? ?????? ????????.
?????????????? ?????????? ?????????? ?????? ??????????? ?? ??????? ??????? ??????? ??? ???????, ? ???????? ?? ????????? ????????. ????? ???? ?????????? ??? ???????, ????????? ??????? CREATE ??? UPDATE STATISTICS.
?????????? ?????????? ?????????????? ? ??????? ??? ?? ????????? ? SQL Profiler, ??? ? ??? ???????? ??????????.

?????? ?????????? ?? ??????

SQL Server 2005 ?????????? ????????????? ?????????? ?????? ????????????? ??? ??????? ? LIKE. ?? ????????? ?????? ?????????? ?? ??????????? ????????????? ????????? ? ?????????? ????? (?????? ?? ??????). ??? ????????? ? ????? ????????? ?????: text, ntext, char, varchar ? nvarchar. ????????????? ?????? ?? ?????? ???????? SQL Server ????? ??????? ????????????? ??????? ? LIKE, ??? ??????? ?????? ????? ????? ????? ?????????? ?????? ??????????? ? ? ????? ?? ??????????. ????????, SQL Server ????? ????????? ????????????? ?????????? ????????? ?????:


Column LIKE 'string%'
Column LIKE '%string'
Column LIKE '%string%'
Column LIKE 'string'
Column LIKE 'str_ing'
Column LIKE 'str[abc]ing'
Column LIKE '%abc%xy'

???? ? ??????? LIKE ???????????? ????????? ????????????? ?????? ?????? (?? ????, ??????? ?????? ????? ????? LIKE ??????? ESCAPE escape_character), ?? ?????? ? ???? ?????? SQL Server 2005 ????????? ?????????????.
??? ??? ???????? ????????? ????????????, ???????????????? ? SQL Server 2000, ??????? ??????????? ?????????? ??? ????????????? ?? ???? ?????? ???????????, ????? ?????????? ? ?????? ????? ??????????? %, ????????????? ? ??????? ?????? LIKE, ??? ???????????? ???????? ??????. ???? ? ?????? ?????? ????????????? DBCC SHOW_STATISTICS ?????????? ???? String Index ????? ???????? YES, ??? ????????, ??? ?????? ?????????? ???????? ? ???? ?????? ?? ??????. ???? ?????????? ?????? ?? ?????? ?? ????????????. ?????? ?? ?????? ???????? ? ???? ?????????????? ??????????, ??????? ?????????? ?? ???, ??????? ?????????? ???????????.
??? ????? ??????????? 80 ????????, ?? ?????? ??????????? ?????? ? ????????? 40 ????????, ?????????? ?? ????????????, ????? ???? ????????? ???????????? ??? ???????? ?????? ?? ??????. ?????????????, ?????? ?????? ??????????? ??? ??? ????????, ??????? ????????? ? ???????????? ????? ??????, ?? ????????.

?????????? ?? ??????????? ?????

SQL Server 2005 ???????????? ????????, ?????????? ? ????????????? ?????????? ?? ??????????? ?????, ???? ? ??? ???????, ????? ??? ?????? ?? ???????? ????? ???????????? ????, ? ???????? ??? ????????? (???????). SQL Server 2000 ??? ?????????, ????????? ? ???????????? ?????????? ?? ??????????? ????? ?????? ? ?????????????? ??????, ? ?????? ???? ??????????? ???? ??????????? ? ??????? ????.
?? ??????? ???????????? ?????????????? ???????? ?????????? ? SQL Server 2005 ??? ???????????? ???? ? ??????? Sales.SalesOrderHeader.TotalDue ???? ?????? AdventureWorks, ???? ????????? ?????????????? ???? Transact-SQL ??????:


USE AdventureWorks
GO
-- ??????? ??? ?????????? ??? Sales.SalesOrderHeader
DECLARE c CURSOR FOR
SELECT name FROM sys.stats
WHERE object_id = object_id('Sales.SalesOrderHeader')
AND auto_created  0 AND user_created  0

DECLARE @name NVARCHAR(255)

OPEN c

FETCH next FROM c INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('drop statistics Sales.SalesOrderHeader.' + @name)
FETCH NEXT FROM c INTO @name
END

CLOSE c
DEALLOCATE c

-- ????????? ?????? ? Sales.SalesOrderHeader, ?????????? ?????????, 
-- ????????????? ???????????? ???? TotalDue: 
-- ((isnull(([SubTotal]+[TaxAmt])+[Freight],(0))).

SELECT *
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
AND (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) > 200000.00
ORDER BY TotalDue DESC

-- ??????? ????????? ??????????. 
-- ????????, ??? ?????????? ????????? ??? ???? TotalDue ???? ?? ?????? ?? ??, 
-- ??? ??? ??? ???? ?? ?????????? ? ???????.

sp_helpstats 'Sales.SalesOrderHeader'

SQL Server 2005 ?? ???????????? ?????????? ?? ?? ??????????? ??????????? ?????, ??????? ?????????? ????????? CLR (common language runtime), ????????, ?????????? ? ????????? ???????????? ????????????? CLR — ???????. ??? ????, ??? ?? ???????? ?????????? ?? ????????????? CLR ???????????? ????, ??? ???? ?????? ???? ????????, ???: PERSISTED.

?????????? ?? ?????, ?????????? ?? ???????????? ????????????? CLR — ????? ??????

SQL Server 2005 ???????????? ????????, ?????????? ? ????????????? ?????????? ?? ?????, ?????????? ?? ???????????? ????????????? CLR — ?????, ???? ????? ???????????????? ??? ?????? ???????????? ???????? ??????????????, ?????, ?????????? ?????????????? ?? ?????. ??? ?????? ???????????? ???????? ??????????????, ???? ? ???????? SqlUserDefinedType, ??????? ???????? ?????? ???????????? ??????????? ????? ??????, ????? IsByteOrdered ??????????? ? «true». ????? ??? ???????????? ???????? ??????????????, ??? ????????, ??? ??????????? ???????? ?????????? ????????????? ?????? ? ???????????? ?????????? ??????? ??? ????? ????.

?????????? ? ??????????????? ?????????????

??????, ?????????? ?? ????????? ??? ??????????????? ?????????????. ??? ?????????? ??????, ??? ??????????? ??????????????? ????????????? ? ???? ?????????? ??????? ?????????? ?????? ????? ????, ??? ??? ?????????????? ?????? ??? ???????????? ? ??????? ?????? ? ???????? ????? ???????????? ? ????? ???????. ???? ???? ??????????: ?????????? ????? ?????????????? ? ??? ??????, ????? ? ???????????? FROM ???????????? ?????? ??????????????? ?? ?????????????, ? ??????????? ????????? ???????????? NOEXPAND. ???????? ????????, ??? ????????? ?????? ? ???? ?? ????? ??????, ???? NOEXPAND ????? ??? ?????????????, ??????? ?? ????? ???????.
??-?? ?????????????? ??????????, ?????????? ?? ??????????????? ?????????????? ?? ????????? ????? ?????????? sp_createstats ? ?? ??????????? ? ??????? sp_updatestats. ???? ?????????????? ?????????? ? ???????? ?????????? ????????? ? ? ??????????????? ??????????????. ??, ??? ?????????? ????, ????? ?????????? ????? ???? ???????????? ????????????? ? ??????? ?????? ? ?????? ????????????? ? ??????? ????? NOEXPAND, ????? ? ?????????? ?????? ??????????????? ??????????/???????? ?????????? (ON). ????? ????, ?????????? ??????????? ??????? ????? ?????????? ??? ????? ?????????????? ?????????????, ??? ???? ????? ???????????? ??????? CREATE STATISTICS ??? UPDATE STATISTICS.

??????? ???????? ? ?????????? ???????????

????? ????????????? ?????????? ? SQL Server ???????? ??????????? ??????????? ????????? ??????? ?????? ????? ????????? ??? ???????????? ????????, ??? ?? ?? ???? ????????? ??????? ????? ?????????? ????????, ? ? ?? ?? ????? ?? ??????? ? ??????????????????, ??????? ???????? ????? ??????????. ???? ?? ?????????? ???????? ???????? ???????? ?? ?????????? ??????????? ? SQL Server, ??????????? ????? ??????? ? ??????? ????????? ???????.

????????????? ??????????????? ???????? ? ?????????? ??????????

??? ???????????? ??????????? ????????? SQL Server, ???????? ??????, ? ??????? ?????????, ???????? ????????????? ?????? ??????????????? ???????? ? ?????????? ?????????? ??? ???? ??? ??????. ???? ????? ??????? ?? ?????????. ???? ?? ????? ?????????? ??????? ?????? ?????? ?????????? ???????? ? ????????????, ??? ??? ?????????? ??-?? ?????????? ??? ??????????? ?????????????? ??????, ? ?????? ??????? ?????????, ??? ??????? ????? ??????????????? ????? ??????????.

??? ??? ??????????, ??????????? ???? ?????????? ? ?????? ???????????? ???????

???? ?? ??????????? ????? ??????????????? ????? ??????????, ?? ????????? ?????? ????? ?????????? ????????? ????????, ?????? ??? ?????????????? ?????? ?? ????? ??? ?? ?????, ???????? ?????????:

???????? ?????????? ????? ??????????????? ????? ??????????.
??? ??? ??????????, ???????? ??????? ??? ?? ?????????????, ???????????:
CREATE STATISTICS ? WITH FULLSCAN, NORECOMPUTE
????? ??????? ??????????? ?? ?????????? ???????, ??????? ????? ???????????? ?????? ?????????:
UPDATE STATISTICS ? WITH FULLSCAN, NORECOMPUTE

??????? ?????????? ?????????? ??????? ?? ???????????? ?????? ?????????? ? ????? ???? ?????????? ???????????. ??????? ????????? ?????? ??? ????????????? ? ??????????? ??????? ??????? ????????????, ???? ??? ??????? ??????????? ????? ? ??????????, ??????? ??????? ?????????? ?? ?????. ???? ??????? ??????????? ?????, ????????? ?????????? ?????????? ? ?????? ???????????? ??? ? ??????.

???????? ????????????? ????????? ?????????? ? ????????

???? ?? ??????????? ????????? ?????????? ? ????????? ??????? ?????? ????????? ??? ?????????, ??????????? ????? ???????????? ??????????? ???????????? ?????? ??? ????????????? ?? ????????????? ?????????. ??????????? ?????? ????????? ?????????? ? ??????? ????????? ??? ?????????, ? ???????????? ????? ????? ??????? ?????? ???? ???????. ????????, ?????????? ?????????????? ???? ??????, ? ??????? ???????????? ????????? ??????????:


declare @StartOrderDate datetime
set @StartOrderDate = '20040731'
select * from Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= @StartOrderDate

????? ????? ? Sales.SalesOrderHeader, ?? ?????? ???????????? ????? ??????????????? ???????? ??????? h.OrderDate> = @StartOrderDate, ? ???????? 9439.5, ??? ?????????? ????? 30% ?? ??????? ???????. ?? ?????? ??????????????? ??????????? ?????? ?????????? ???????, ????????? ?????????? ?? ???? Sales.SalesOrderHeader, ? ??????? ?????????? ?????? ????? ?????????. ? ???? — ?????? SQL Server 2005, ??????? ?????????????? ??? ????????? ???? ??????, ? ????? ????????????? MERGE JOIN (??????????? ???? ????? ?????????? ???? ?? ??? ?? ?????? SQL Server 2005, ??-?? ????????? ?????? ????????? ??????, ? ?.?.). ??????, ?????????? ????????????? ??????, ??????? ?? ?????????? ????????? ??????????:


SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= '20040731'

? ??????????? ????? ?????????? ???????, ?????????? ????????? ??????????????? ?????? ??? ????????? «h.OrderDate> = ‘20040731’» ???????? ??????, ?????? 40, ??? ????????????? ????????????? 0.13%. ????, ????????? ??? ????? ???????, ?????????? NESTED LOOP JOIN ?????? MERGE JOIN, ??-?? ???? ?????? ?????????? ????? ????????????.
???? ???? ? ??????? ???????????? ????????? ??????????, ????? ???????? ????? ???????????? ??????, ??? ? ?????????? ?? ????????? ????????? ??????????????. ????????????? ??????? ????: «@local_variable = column_name» ????? ??????????? ?? ?????? ??????? ??????? ???????? ?? ??????????? ??? column_name. ????????, ???? ???? column_name ???????? ?????? ?????????? ????????, ?? ????? ?????????????? ?????? ?????????????: 1/(????? ?????????? ???????? ????), ??? ???????? ?????? ??????.
????? ???????? ????????????? ????????? ??????????, ?????????? ???????? ?? ? ???????? ?? ?????????; ??????????? sp_executesql ? ???????????, ??????? ??????? ????????? ??????????; ??????????? ???????? ????????? ? ??????????? ?????? ??????????. ???????????? SQL ????? EXEC ????? ????? ???? ??????? ??? ???? ?????, ?.?. ????????? ?????????? ?? ????????? ??????????, ?? ?????? ?? ???????? ? ?????????????? ???????? ?? ??????????.

?????????? ???????? ????????????? ??????? TVF ? ????????? ??????????

?????????? ????? ?????????? ? ???????????? ????????? ???????? ??????? (table-valued functions, TVF) ?? ????? ??????????. ??????? ??????????? ???????? ????????? ?????? ?? ??????????????? ??????. ??????????, ????????? ?????????? ???? ?? ????? ??????????, ? ??????????? ????? ?????? ???????????? ? ?????????? ?? ?????????. ???? ? ?????????? ????????????? ????????? ?????????? ??? ????????? ??????? ?? ????????? ?????? ???? ?????????? ???????, ??????????? ??????????? ?????? ?? ?? ??????? ??????? ??? ?? ????????? ???????, ????????? ?? ??? ????????? ????????? ??? ???????????, ???????? ????????? ????? TVF. ??? ???????? ???????????? ???????????? ????? ???????????? ?????? ????? ?????????.

?? ????????????? ????????? ? ?????????? ????????? ??????? ????? ???????? ??????????? ???????????? ???????

SQL Server ???????? ?????? ????????? ?????????, ??????? ?? ????? ?????????? ?????????? ?????? ?????????. ????? ????????? ???????? constant folding. ?????????, ??????? ????? ???? ?????????? ???????, ?????????????? ?? ????? ?????? ????????????? ??? ?????????. ?????????, ??????? ?? ????? ???? ???????? (Non-foldable), ????????? ??????????? ???????????? ??????? ??? ?????? ?????????????. ????????, ?????????? ?????????????? ???? Transact-SQL ??????, ??????? ????????? ??????? UserLog ????? ??????? ?????, ???????? ?? ??????? ????? ?????????? ???????? UserName, ? ?????? ???????? ????? ???? ? ?? ?? ???????? UserName, ???????? ?????????? ?????????????.

Смотрите также:

Tags: mpd, optimization, SQL, SQL Server, tun

Readers Comments (Комментариев нет)

Comments are closed.

Exchange 2007

Проведение мониторинга Exchange 2007 с помощью диспетчера System Center Operations Manager 2007 (часть 3)

Если вы хотите прочитать предыдущие части этой серии статей, перейдите по ссылкам: Проведение мониторинга Exchange 2007 с помощью диспетчера System ... [+]

Практическое рассмотрение перехода с Exchange 2003 на Exchange 2007 (часть 1)

Введение В этой статье из нескольких частей я хочу показать вам процесс, который недавно использовал для перехода с существующей среды Exchange 2003 ... [+]

Использование инструмента Exchange Server Remote Connectivity Analyzer Tool (часть 2)

Если вы пропустили первую часть этой серии, пожалуйста, прочтите ее по ссылке Использование инструмента Exchange Server Remote Connectivity Analyzer Tool (Часть ... [+]

Мониторинг Exchange 2007 с помощью диспетчера System Center Operations Manager 2007 (часть 2)

Если вы пропустили предыдущую часть этой серии статей, перейдите по ссылке Мониторинг Exchange 2007 с помощью диспетчера System Center Operations ... [+]

Подробное рассмотрение подготовки Active Directory для Exchange 2007 (часть 5)

Если вы пропустили предыдущие части этой серии статей, перейдите по ссылкам: Подробное рассмотрение подготовки Active Directory для Exchange 2007 (часть 1) ... [+]

Установка и настройка Exchange 2007 из командной строки (Часть 3)

If you missed the previous parts in this article series please read: Exchange 2007 Install and Configuration from the command line (Part ... [+]

Использование инструмента Exchange Server Remote Connectivity Analyzer Tool (часть 1)

Инструмент ExRCA Текущий выпуск инструмента предоставляется только в целях тестирования и оснащен 5 опциями: Тест подключения Outlook 2007 Autodiscover Тест подключения Outlook 2003 RPC ... [+]

Развертывание сервера Exchange 2007 Edge Transport (часть 5)

Если вы хотите прочитать предыдущие части этой серии статей, перейдите по ссылкам: Развертывание сервера Exchange 2007 Edge Transport (часть 1) Развертывание ... [+]

Установка и настройка Exchange 2007 из командной строки (часть 2)

Если вы пропустили первую статью данного цикла, пожалуйста, перейдите по ссылке: Exchange 2007 Install and Configuration from the command line (Part ... [+]

Использование интегрированных сценариев Using Exchange Server 2007 – часть 2: генерирование отчетов агента Transport AntiSpam Agent

Если вы пропустили предыдущую часть этой серии статей, перейдите по ссылке Использование интегрированных сценариев Using Exchange Server 2007 – часть ... [+]