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

?????? ???????? ??? ??????????? ?? 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

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

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

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