Использование фильтрованной статистики с секционированными таблицами.

?????? ???????? ??? ??????????? ?? https://sqlcat.com/msdnmirror/archive/2009/10/20/using-filtered-statistics-with-partitioned-tables.aspx, ?? ? ???????, ??? ???????????? ?????? ???? ????? ???? ??????? ??? ????????????? ?????????, ?????????? ? SQL Server

 ????????

??????? ?? ???????? ??? ???????????? ??????????? ??????? ?????? ?? ???????? ? ??????????? ???????? ????? ??????? (??????? ????????? ??????) ? SQL Server 2005 ?? SQL Server 2008. ???????????? ?? ?????? ?????????? ???????, ???? ???????? ?????????? ????? ?????????? ????????, ??????? ?? ??? ??????? ?? SQL Server 2008. ???? ?? ?????????? ???? ???????? ? ??????????? ????????????? ???????? ?????????.

???????? ???????? ??????? ????????? ?????? – ??? ???????? ??? ?????????? Call Data records. CDR – ??? ?????? ? ?????????? ???????. ????????? ????????????? ?????????? ??? ?????????? ???????, ???????????? ? ????????????? ?????????? ??????????????? ????????. ??????? ????????: ??? ? ???? ? ???????????????? ??????? ????????? ??????? ????????? ?????? , ????? ??????-?????????? ? ??????? ???????????? ????????? ?????? ???? ?????? ???????, ??????? ???????? ?? ???? ????????. ????????? ???????????? ????????? ???????, ??? ?? ?????, ??????????? ?????????? ?????. ????? ?????????????? ?????? ????????????? ???????, ??????????, ??? ??????? ????? ??????? ????????????? ???????? ? ??????? ???????????? ???? ?????? ????????! ?????? ???????? ??? ?????????? 10 ???????? ??????, ? ?? ????? ??? ??????? ??????????? 18 ????????.

?? ?????? ????????? Resource Governor ? ??????? ???????? ???? ????????. ???? ????? ????, ?? ??? ?? ?????? ???????? ?????????? ???????????? ??????????? ??????? ????????. ?????? ??? ?? ???? ?????? ?? ??????: ?????? ??? ??????? ????????? ??? ????? ???????

?????????? ????? ????????, ?? ????????, ??? ??????? ?????????? ?? ???????? ???? ?????????. ???????? ????????????? ? ??????? ??????? ???? ?????????: ??????? ?????????? ?????? ??????????? ? ?????? ??????? ?????? ???? ????????? ? «???????????» ?????????. ?????????? ????? ???? ?????????. ?????????? ?????????? ?? ??????????? ??????? ???????? ????? ????? ??????? ? ????????????? ? ???? ???????, ????? ????????? ?????? ???????? ?????????. ?????????? ??? ?? ??????????? «??????????». ????? ?????????? ????????? ??????? ??????????. ? ?.?. ? ?.?. ?????????????? ??????, ??? ????? ???????????? ??????????? ? ????????? ?? ??????.

??, ????? ????????? – ??????????? ??? ?????? ? ?????? ??????? ??????????? ?????? ????? ?????? ??????. ?.?., ????????, ?????? ???????? ?????? ?? ???????????? ???? ? ?????? ???????????? ?? ??????? ?? ??????? ?? ??????? ?????? ???.

???? ????? ?????? ??????, ??????? ? ?????? ?? ???? ?????? AdventureWorks ? ??????? SalesOrderHeader . ?????? ?????? ????????? ??, ??? ??????????? ? ?????? ???????.

????, ? ?????? ???????????????? ??????? SalesOrderHistory ? ???????????????? ?? ???? (OrderDate) ? ?????????? ?????? ?????? ?????? (2008-07).

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

CREATE TABLE [SalesOrderHistory](

[SalesOrderID] [int] NOT NULL,

[RevisionNumber] [tinyint] NOT NULL,

[OrderDate] [datetime] NOT NULL,

[DueDate] [datetime] NOT NULL,

[ShipDate] [datetime] NULL,

[Status] [tinyint] NOT NULL,

[SalesOrderNumber] [nvarchar](25) NOT NULL,

[CustomerID] [int] NOT NULL,

[ShipToAddressID] [int] NULL,

[BillToAddressID] [int] NULL,

[CreditCardApprovalCode] [varchar](15) NULL,

[SubTotal] [money] NOT NULL,

[TaxAmt] [money] NOT NULL,

[Freight] [money] NOT NULL,

[TotalDue] [money] NOT NULL,

[Comment] [nvarchar](max) NULL,

[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,

[ModifiedDate] [datetime] NOT NULL)

ON day_scheme ([OrderDate])

???????? «????????» ?????? ???????? ????????? ???????:

Select b.Name, a.SalesorderId, a.OrderDate from SalesOrderHistory a

Inner Join Store b on a.CustomerID = b.CustomerID

WHERE a.OrderDate = '2008-07-13 00:00:00.000'and a.SalesOrderID = 43659

Group by b.Name, a.SalesorderId, a.OrderDate

????? ?????????? ??????? 5 ???.

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

????, ?? ????? ?????????, ??? ???????????? ?????????? ???????????. ???????? ?????????? ?????, ??????? ?????? «?????» ??????? – 393 ?????.

???? ?? ???????? ??????????? DMV, ????? ??????????, ??????? ?????? ???? ??????????? ???????? ? ????????, ?? ?????? ????????? ???????::

select session_id,requested_memory_kb,required_memory_kb,granted_memory_kb,query_cost

from sys.dm_exec_query_memory_grants

where session_id >50

session_id

requested_memory_kb

required_memory_kb

granted_memory_kb

query_cost

52

1360

1280

1360

5,737777

?????? ????????? ??? ???? ??????? ?????????. ? SQL 2008 ???????? ????? ??? ????????? – ????????????? ??????????. ????? ???????? ??? ??????? ????? ??????? ? BOL. ??????, ?????, ??? ????? 80% ???????? ??????? ??????????? ?????? ???? ??????, ????????? ????????? ????????????? ??????????, ????? ?????????? ???????? ?????????????????? ????????.

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

CREATE STATISTICS Day0713_SalesOrderID on SalesOrderHistory (SalesOrderID ) where OrderDate = '2008-07-13 00:00:00.000'

CREATE STATISTICS Day0713_OrderDate on SalesOrderHistory (OrderDate) where OrderDate = '2008-07-13 00:00:00.000'

CREATE STATISTICS Day0713_CustomerID on SalesOrderHistory (CustomerID ) where OrderDate = '2008-07-13 00:00:00.000'

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

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

select session_id,requested_memory_kb,required_memory_kb,granted_memory_kb,query_cost

from sys.dm_exec_query_memory_grants

where session_id >50

session_id

requested_memory_kb

required_memory_kb

granted_memory_kb

query_cost

52

1024

512

1024

5,678132

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

????? ?? ????? ?????? ?????????? ????????????? ????????? ???????? ??? ????, ??? ????? ??????????? ????? ?????? ??????????, ????????? ??? ?????? ?????? ?? «??????????» . ??????????????, ??? ????????????? ?? ?????????. ??? ?????? ??????????? ??????? ?????????? ??? ????? ??????, ??? ???????? ??????????? ?????? ???????, ?????? ?????????? ??? ???????? ?????????? ??? ???? ???????.

??????? ? ????????? ??????? ? ???? ?? ?????? ???????? ???????????? ??????? ?????????????????? ?? ?????????? ??????? ???????. ???? ??? ?????, ??????????? ????? ???????? ????? ?? ??, ? ??? ???????? ?? – ??????? ? ???????? ?????????? ? ????? ??????, ??? ?????????? ??????????? ????????????? ??????????. ????????????? ?? ????? ?????? ???????? ?????????????????? ???????.