Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2 3      [все]
 Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
Добрый день.
Я не администратор БД, эта задача для меня прикладная. Т.е. ее надо решить, но уходить в дебри изучения архитектуры индексов и идеологически верного проектирования БД, для меня непозволительная роскошь. Поэтому прошу не пинать ногами, т.к. беглые поиск в течении трех дней для меня ясности не внес. Только по этому решил написать сюда.
Итак задача:
Сервер MS SQL 2008 R2 64bit, Windows 2008 R2.
Два массива RAID10 (на контроллере LSI MegaRAID SAS 9260-8i), на одном крутится несколько БД, на втором оперативные бекапы+файлы журнала транзакций.
24GB RAM, 24 ядра в диспетчере устройств (XEON X5650).
Есть таблица статистики звонков:
USE [INFDATA_STAT]
GO

/****** Object:  Table [dbo].[I_STATISTICS_CALL_IN]    Script Date: 11/25/2013 08:25:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[I_STATISTICS_CALL_IN](
	[CALLID] [varchar](50) NULL,
	[DATETIME] [float] NULL,
	[DATETIME_D] [float] NULL,
	[DATETIME_T] [float] NULL,
	[ANUMBER] [varchar](100) NULL,
	[ANAME] [varchar](250) NULL,
	[ANUMBERDIALED] [varchar](100) NULL,
	[CALLRESULT] [int] NULL,
	[IVRLENGTH] [float] NULL,
	[IVRNAME] [varchar](250) NULL,
	[B1WAITLENGTH] [float] NULL,
	[B1TALKLENGTH] [float] NULL,
	[B1NUMBER] [varchar](100) NULL,
	[B1NAME] [varchar](250) NULL,
	[B1IDUSER] [int] NULL,
	[B2WAITLENGTH] [float] NULL,
	[B2TALKLENGTH] [float] NULL,
	[B2NUMBER] [varchar](100) NULL,
	[B2NAME] [varchar](250) NULL,
	[B2IDUSER] [int] NULL,
	[B3WAITLENGTH] [float] NULL,
	[B3TALKLENGTH] [float] NULL,
	[B3NUMBER] [varchar](100) NULL,
	[B3NAME] [varchar](250) NULL,
	[B3IDUSER] [int] NULL,
	[EXWAITLENGTH] [float] NULL,
	[EXTALKLENGTH] [float] NULL,
	[EXNUMBER] [varchar](100) NULL,
	[EXNAME] [varchar](250) NULL,
	[EXROUTENAME] [varchar](250) NULL,
	[SUMLENGTH] [float] NULL,
	[SUMIVRLENGTH] [float] NULL,
	[SUMOPERLENGTH] [float] NULL,
	[SUMEXTERNALLENGTH] [float] NULL,
	[SERVER_ID] [int] NULL,
	[GLOBAL_ID] [bigint] NOT NULL,
 CONSTRAINT [PK_I_STATISTICS_CALL_IN] PRIMARY KEY CLUSTERED 
(
	[GLOBAL_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


В ней порядка 24 млн строк. Созданы индексы:
CREATE NONCLUSTERED INDEX [I_STATISTICS_CALL_IN_IDX1] ON [dbo].[I_STATISTICS_CALL_IN] 
([DATETIME] ASC,[B1WAITLENGTH] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [I_STATISTICS_CALL_IN_IDX2] ON [dbo].[I_STATISTICS_CALL_IN] 
([DATETIME] DESC,[SUMOPERLENGTH] ASC,[SERVER_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_ANUMBER] ON [dbo].[I_STATISTICS_CALL_IN] 
([ANUMBER] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_ANUMBERDIALED] ON [dbo].[I_STATISTICS_CALL_IN] 
([ANUMBERDIALED] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_callresult] ON [dbo].[I_STATISTICS_CALL_IN] 
([CALLRESULT] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_DATETIME] ON [dbo].[I_STATISTICS_CALL_IN] 
([DATETIME] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_ServerID_CALLID] ON [dbo].[I_STATISTICS_CALL_IN] 
([CALLID] ASC,[SERVER_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_Sumlenght] ON [dbo].[I_STATISTICS_CALL_IN] 
([SUMLENGTH] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

ALTER TABLE [dbo].[I_STATISTICS_CALL_IN] ADD  CONSTRAINT [PK_I_STATISTICS_CALL_IN] PRIMARY KEY CLUSTERED 
([GLOBAL_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


Для работы с таблицей как обязательно используются поля DATETIME. Чаще всего так же используются поля:
ANUMBER, SERVER_ID,B1IDUSER.
Что меня смущает, это то, что простейший запрос вида:
declare @Globalid_min bigint, @Globalid_max bigint
declare @dt1 float,	@dt2 float


set @dt1	=CONVERT(float,CONVERT(datetime,'20131109 12:00:00'))+2--дата от 
set @dt2	= cast(cast('20131122' as datetime) as float)+2--дата до


Select 
@Globalid_min=min(Global_ID), 
@Globalid_max=MAX(GLOBAL_ID) 
from INFDATA_STAT.dbo.i_statistics_call_in isc  (nolock)
where isc.[datetime_D] between @dt1 and @dt2  
Select @Globalid_min as min, @Globalid_max as max

выполняется от минуты и более, что непозволительно долго для текущих задач. Вопрос к знающим и понимающим, что я могу оптимизировать или перенастроить, для того, чтобы снизить время выполнения запроса хотя бы раза в три.
План запроса:
  |--Nested Loops(Inner Join)
       |--Stream Aggregate(DEFINE:([Expr1002]=MIN([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[GLOBAL_ID] as [isc].[GLOBAL_ID])))
       |    |--Top(TOP EXPRESSION:((1)))
       |         |--Clustered Index Scan(OBJECT:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[PK_I_STATISTICS_CALL_IN] AS [isc]),  WHERE:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[DATETIME_D] as [isc].[DATETIME_D]>=[@dt1] AND [INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[DATETIME_D] as [isc].[DATETIME_D]<=[@dt2]) ORDERED FORWARD)
       |--Stream Aggregate(DEFINE:([Expr1003]=MAX([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[GLOBAL_ID] as [isc].[GLOBAL_ID])))
            |--Top(TOP EXPRESSION:((1)))
                 |--Clustered Index Scan(OBJECT:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[PK_I_STATISTICS_CALL_IN] AS [isc]),  WHERE:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[DATETIME_D] as [isc].[DATETIME_D]>=[@dt1] AND [INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[DATETIME_D] as [isc].[DATETIME_D]<=[@dt2]) ORDERED BACKWARD)


Сейчас есть предположение, что если в кластеризованный индекс по полю GLOBAL_ID включить дату, возможно все будет работать быстрее.
Но т.к. как база в бою, эксперименты особо проводить не хочется. Страшно! :(

К сообщению приложен файл. Размер - 22Kb
25 ноя 13, 08:52    [15183345]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
AlexeyMish
Сейчас есть предположение, что если в кластеризованный индекс по полю GLOBAL_ID включить дату, возможно все будет работать быстрее.
Мда, вы вообще не понимаете, что такое инедкс :-(

Нужно сделать интекс по datetime_D
AlexeyMish
Я не администратор БД, эта задача для меня прикладная. Т.е. ее надо решить, но уходить в дебри изучения архитектуры индексов и идеологически верного проектирования БД, для меня непозволительная роскошь.
Можно нанять специалиста.
25 ноя 13, 09:02    [15183370]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
alexeyvg
Мда, вы вообще не понимаете, что такое инедкс :-(

Нужно сделать интекс по datetime_D
Тьфу, 2 ошибки в однос слове :-)

Нужно сделать индекс по datetime_D
25 ноя 13, 09:03    [15183373]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
1) Прошу прощения, ошибка в посте.
естественно запрос по полю DATETIME.
2) Вариант с наймом специалиста тоже рассматриваю
3) У меня нет уверенности, что в найме будет толк. Т.к. не исключаю, что запрос так долго выполняется просто по причине того, что слишком много данных. Даже по индексированному полю поиск проходит слишком долго.
25 ноя 13, 09:05    [15183383]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
Что такое индекс я поверхностно понимаю.
Я не до конца понимаю, что такое покрывающие индексы, кластеризованные индексы. Когда какие надо использовать. Что лучше куча индексов на каждое поле или один на все поля. В каких случаях что лучше использовать. Имеет ли смысл на таких объемах смотреть в сторону партиционирования или это на производительности не скажется.
Но в эти дебри я лезть не хочу, поэтому прошу помощи в одной маленькой задаче. Для того, чтобы хотя бы понять, имеет смысл заниматься оптимизацией (читай наймом специалиста) или же стоит задуматься над выносом старых данных в некую архивную базу данных. А для оперативных нужд держать данные за последние полгода-год.
25 ноя 13, 09:11    [15183422]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
AlexeyMish
1) Прошу прощения, ошибка в посте.
естественно запрос по полю DATETIME.
Какой запрос:
Select 
@Globalid_min=min(Global_ID), 
@Globalid_max=MAX(GLOBAL_ID) 
from INFDATA_STAT.dbo.i_statistics_call_in isc  (nolock)
where isc.[DATETIME] between @dt1 and @dt2  
?

Тогда должен работать индекс
CREATE NONCLUSTERED INDEX [IDX_DATETIME] ON [dbo].[I_STATISTICS_CALL_IN] 
([DATETIME] ASC)

Можно попробовать сделать индекс
CREATE NONCLUSTERED INDEX [IDX_DATETIME] ON [dbo].[I_STATISTICS_CALL_IN] 
([DATETIME] ASC) INCLUDE (GLOBAL_ID)


AlexeyMish
В ней порядка 24 млн строк
Важна селективность по [DATETIME], т.е. сколько записей будет для значений [DATETIME] between @dt1 and @dt2

Ещё попробуйте выполнить запрос
Select 
@Globalid_min=min(Global_ID), 
@Globalid_max=MAX(GLOBAL_ID) 
from INFDATA_STAT.dbo.i_statistics_call_in isc  (nolock)
where isc.[DATETIME] between 'константа 1' and 'константа 2'
Если будет быстро и будет использоваться индекс [IDX_DATETIME], то нужно решать проблему по другому.
25 ноя 13, 09:19    [15183451]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
AlexeyMish
Имеет ли смысл на таких объемах смотреть в сторону партиционирования или это на производительности не скажется.
Партиционирование используется больше для облегчения обслуживания и загрузки данных, вряд ли вам оно поможет.

AlexeyMish
Для работы с таблицей как обязательно используются поля DATETIME
На эту вашу таблицу ссылки (FK) есть?
Поле [DATETIME] гарантированно будет в любом запросе, в котором хоть как то упоминается эта таблица?
25 ноя 13, 09:25    [15183469]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
alexeyvg
Select 
@Globalid_min=min(Global_ID), 
@Globalid_max=MAX(GLOBAL_ID) 
from INFDATA_STAT.dbo.i_statistics_call_in isc  (nolock)
where isc.[DATETIME] between @dt1 and @dt2  
?

Тогда должен работать индекс
CREATE NONCLUSTERED INDEX [IDX_DATETIME] ON [dbo].[I_STATISTICS_CALL_IN] 
([DATETIME] ASC)


Судя по приведенным планам индекс работает.

alexeyvg
Можно попробовать сделать индекс
CREATE NONCLUSTERED INDEX [IDX_DATETIME] ON [dbo].[I_STATISTICS_CALL_IN] 
([DATETIME] ASC) INCLUDE (GLOBAL_ID)

Попробую, когда нагрузка упадет. Перенапрягать сейчас базу не хочу, в настоящий момент этот сервер очень активно используется.

alexeyvg
Важна селективность по [DATETIME], т.е. сколько записей будет для значений [DATETIME] between @dt1 and @dt2

В сутки порядка 40тыс строк. Т.е. если в запросе выборка за 10 дней, то около 400 тыс уникальных строк попадает в выборку.

alexeyvg
Ещё попробуйте выполнить запрос
Select 
@Globalid_min=min(Global_ID), 
@Globalid_max=MAX(GLOBAL_ID) 
from INFDATA_STAT.dbo.i_statistics_call_in isc  (nolock)
where isc.[DATETIME] between 'константа 1' and 'константа 2'
Если будет быстро и будет использоваться индекс [IDX_DATETIME], то нужно решать проблему по другому.

В первый раз было быстро, я сообразил поменять дату, чтоб не использовался кеш сервера. Время выполнения запроса практически не изменилось. (По прежнему слишком долго)

alexeyvg
AlexeyMish
Имеет ли смысл на таких объемах смотреть в сторону партиционирования или это на производительности не скажется.
Партиционирование используется больше для облегчения обслуживания и загрузки данных, вряд ли вам оно поможет.

Я так и понял, когда в пятницу проводил себе ликбез :).

alexeyvg
AlexeyMish
Для работы с таблицей как обязательно используются поля DATETIME
На эту вашу таблицу ссылки (FK) есть?
Поле [DATETIME] гарантированно будет в любом запросе, в котором хоть как то упоминается эта таблица?

FK нет. Поле [DATETIME] будет использоваться. Даже если сейчас не используется, то дописать запросы возможность есть. К счастью несмотря на то, что продукт полукоробочный, возможность написания запросов у меня сохранилась.
25 ноя 13, 09:43    [15183534]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
AlexeyMish
Судя по приведенным планам индекс работает.
Нет, по приведенным планам видим полное сканирование таблицы:
Clustered Index Scan(OBJECT:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[PK_I_STATISTICS_CALL_IN]
AlexeyMish
В первый раз было быстро, я сообразил поменять дату, чтоб не использовался кеш сервера. Время выполнения запроса практически не изменилось. (По прежнему слишком долго)
ИМХО дело не в кеше, а в постоянном сканировании.

И вообще, точно приводите запросы и планы.
По плану тоже используется поиск по полю [DATETIME_D], а не по [DATETIME]
Такие мелкие опечатки и непонимания могут приводить к многодневным бесплодным поискам проблемы там, где её нет :-)

Вы ещё раз запрустите запрос с константами вместо переменных, выложите сюда запрос и его план.
25 ноя 13, 09:56    [15183612]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
declare @Globalid_min bigint, @Globalid_max bigint
declare @dt1 float,	@dt2 float

set @dt1	=CONVERT(float,CONVERT(datetime,'20131109 12:00:00'))+2--дата от 
set @dt2	= cast(cast('20131122' as datetime) as float)+2--дата до

Select 
@Globalid_min=min(Global_ID), 
@Globalid_max=MAX(GLOBAL_ID) 
from INFDATA_STAT.dbo.i_statistics_call_in isc  (nolock)
where isc.[datetime] between @dt1 and @dt2  
Select @Globalid_min as min, @Globalid_max as max


  |--Nested Loops(Inner Join)
       |--Stream Aggregate(DEFINE:([Expr1002]=MIN([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[GLOBAL_ID] as [isc].[GLOBAL_ID])))
       |    |--Top(TOP EXPRESSION:((1)))
       |         |--Clustered Index Scan(OBJECT:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[PK_I_STATISTICS_CALL_IN] AS [isc]),  WHERE:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[DATETIME] as [isc].[DATETIME]>=[@dt1] AND [INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[DATETIME] as [isc].[DATETIME]<=[@dt2]) ORDERED FORWARD)
       |--Stream Aggregate(DEFINE:([Expr1003]=MAX([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[GLOBAL_ID] as [isc].[GLOBAL_ID])))
            |--Top(TOP EXPRESSION:((1)))
                 |--Clustered Index Scan(OBJECT:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[PK_I_STATISTICS_CALL_IN] AS [isc]),  WHERE:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[DATETIME] as [isc].[DATETIME]>=[@dt1] AND [INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[DATETIME] as [isc].[DATETIME]<=[@dt2]) ORDERED BACKWARD)


К сообщению приложен файл. Размер - 45Kb
25 ноя 13, 10:09    [15183682]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
Пробовал выполнить с константами, картина полностью идентична.
25 ноя 13, 10:13    [15183700]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
С константами.

--SET SHOWPLAN_TEXT ON
--go

declare @Globalid_min bigint, @Globalid_max bigint
Select 
@Globalid_min=min(Global_ID), 
@Globalid_max=MAX(GLOBAL_ID) 
from INFDATA_STAT.dbo.i_statistics_call_in isc  (nolock)
where isc.[datetime] between 41556.5 and 41569  
Select @Globalid_min as min, @Globalid_max as max
--go
--SET SHOWPLAN_TEXT OFF
--go


  |--Nested Loops(Inner Join)
       |--Stream Aggregate(DEFINE:([Expr1002]=MIN([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[GLOBAL_ID] as [isc].[GLOBAL_ID])))
       |    |--Top(TOP EXPRESSION:((1)))
       |         |--Clustered Index Scan(OBJECT:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[PK_I_STATISTICS_CALL_IN] AS [isc]),  WHERE:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[DATETIME] as [isc].[DATETIME]>=(4.155650000000000e+004) AND [INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[DATETIME] as [isc].[DATETIME]<=(4.156900000000000e+004)) ORDERED FORWARD)
       |--Stream Aggregate(DEFINE:([Expr1003]=MAX([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[GLOBAL_ID] as [isc].[GLOBAL_ID])))
            |--Top(TOP EXPRESSION:((1)))
                 |--Clustered Index Scan(OBJECT:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[PK_I_STATISTICS_CALL_IN] AS [isc]),  WHERE:([INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[DATETIME] as [isc].[DATETIME]>=(4.155650000000000e+004) AND [INFDATA_STAT].[dbo].[I_STATISTICS_CALL_IN].[DATETIME] as [isc].[DATETIME]<=(4.156900000000000e+004)) ORDERED BACKWARD)


К сообщению приложен файл. Размер - 25Kb
25 ноя 13, 10:26    [15183767]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
AlexeyMish
В сутки порядка 40тыс строк. Т.е. если в запросе выборка за 10 дней, то около 400 тыс уникальных строк попадает в выборку.
Получается, сервер считает, что выгоднее сканировать, чем использовать индекс.

Попробуйте задать использование инедкса принудительно:
Select 
@Globalid_min=min(Global_ID), 
@Globalid_max=MAX(GLOBAL_ID) 
from INFDATA_STAT.dbo.i_statistics_call_in isc  (nolock, index=IDX_DATETIME)
where isc.[DATETIME] between @dt1 and @dt2  

Ну и потом попробуйте, когда появится возможность, сделать индекс с включённым Global_ID, как я говорил...
25 ноя 13, 10:27    [15183778]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
AlexeyMish
[DATETIME] [float] NULL,
Просто интересно - почему используется тип float вместо datetime?
Это же неудобно.
25 ноя 13, 10:29    [15183795]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
alexeyvg
Попробуйте задать использование инедкса принудительно:
Select 
@Globalid_min=min(Global_ID), 
@Globalid_max=MAX(GLOBAL_ID) 
from INFDATA_STAT.dbo.i_statistics_call_in isc  (nolock, index=IDX_DATETIME)
where isc.[DATETIME] between @dt1 and @dt2  

Ну и потом попробуйте, когда появится возможность, сделать индекс с включённым Global_ID, как я говорил...


Прошу прощения, я такого формата запроса никогда не видел.
Буду искать, но паралельно тут спрошу. Сервер меня не понимает.

Msg 1018, Level 15, State 1, Line 8
Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Если до снижения нагрузки проблема не решится - обязательно попробую. В одиночку бьюсь уже третий день. Если не прислушиваться к советам, боюсь, что сам решить не смогу. =)
25 ноя 13, 10:31    [15183807]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
alexeyvg
AlexeyMish
[DATETIME] [float] NULL,
Просто интересно - почему используется тип float вместо datetime?
Это же неудобно.


Продукт коммерческий. Изначально был взращен на firebird. Потом для нас допилен под MS SQL. Допилен понятно с минимальными затратами времени. Т.е. этот формат можно сказать атавизм, но жить сильно не мешает.
Проще говоря, не виноватый я. Мне такое досталось. =)
25 ноя 13, 10:36    [15183841]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
alexeyvg,
Можно еще посоветоваться?
я тут пытаюсь все таки ликвидировать свою безграммотность в плане MS SQL. И насколько понимаю рекомендации из http://www.gotdotnet.ru/blogs/alexgaas/455/
в моем случае кластерный индекс лучше накладывать на поле не GLOBAL_ID, а сделать покрывающий кластерный индекс (если я правильно понял термины), на поля Global_ID+Datetime.
Суждение сформировано исходя из рекомендации:
автор
4) Ключ должен быть выбран таким образом, чтобы соответствовать большинству запросов выборки по диапазону

и вроде бы не сильно противоречит
автор
2) Создайте как можно более компактный ключ
25 ноя 13, 10:42    [15183871]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
AlexeyMish
Прошу прощения, я такого формата запроса никогда не видел.
Буду искать, но паралельно тут спрошу. Сервер меня не понимает.

Msg 1018, Level 15, State 1, Line 8
Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
Да путаюсь в синтаксисе, старом, новом... :-)

Наверное так:
Select 
@Globalid_min=min(Global_ID), 
@Globalid_max=MAX(GLOBAL_ID) 
from INFDATA_STAT.dbo.i_statistics_call_in isc  WITH (nolock, INDEX(IDX_DATETIME))
where isc.[DATETIME] between @dt1 and @dt2 
25 ноя 13, 10:42    [15183875]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
AlexeyMish
в моем случае кластерный индекс лучше накладывать на поле не GLOBAL_ID, а сделать покрывающий кластерный индекс (если я правильно понял термины), на поля Global_ID+Datetime.
Суждение сформировано исходя из рекомендации:
автор
4) Ключ должен быть выбран таким образом, чтобы соответствовать большинству запросов выборки по диапазону
Global_ID+Datetime будет совершенно бесполезным.

У вас же нету условия по Global_ID, а поиск по одному Datetime по этому индексу не может быть сделан.

Идеальным будет неуникальный кластерный индекс по Datetime или уникальный кластерный индекс по Datetime, Global_ID

Но я вам не рискнул такое предлагать, это всё таки слишком большое изменение для уже работающей системы, и к тому же стороннего производителя.
25 ноя 13, 10:47    [15183898]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
aleks2
Guest
AlexeyMish
CREATE TABLE [dbo].[I_STATISTICS_CALL_IN](
 CONSTRAINT [PK_I_STATISTICS_CALL_IN] PRIMARY KEY [s]CLUSTERED [/s](
	[GLOBAL_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


Для работы с таблицей как обязательно используются поля DATETIME.


Дык, сделай, страдалец, правильный кластерный индекс.

 CONSTRAINT [PK_I_STATISTICS_CALL_IN] UNIQUE CLUSTERED 
(
             [DATETIME]
           , [GLOBAL_ID] ASC
)
25 ноя 13, 10:50    [15183904]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
aleks2
Дык, сделай, страдалец, правильный кластерный индекс.
Рискованно такое предлагать...

ТС может быть не в курсе про все запросы в системе...

Хотя да, такой индекс пока видится идеальным, судя по описанию.
25 ноя 13, 10:56    [15183947]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
alexeyvg
aleks2
Дык, сделай, страдалец, правильный кластерный индекс.
Рискованно такое предлагать...
ТС может быть не в курсе про все запросы в системе...
Хотя да, такой индекс пока видится идеальным, судя по описанию.

Про запросы я достаточно в курсе.
Фактически система представляет из себя набор приложений которые занимаются ТОЛЬКО вставкой в базу.
Все работы по выборке данных и работе с ними лежат на мне.
Учитывая, время и сроки за которые провели допиливание системы с firebird до MS SQL вероятность того, что разрабы занимались тюнингом индексов я оцениваю как предельно малую.
Строго говоря, чуть выше я сам пришел к мысли, что этот индекс надо пересоздать, просто с формулировкой у меня не очень. Ибо не специалист.
автор
в моем случае кластерный индекс лучше накладывать на поле не GLOBAL_ID, а сделать покрывающий кластерный индекс (если я правильно понял термины), на поля Global_ID+Datetime.
Суждение сформировано исходя из рекомендации:


Итак, если я решусь на этот шаг. Мне нужно будет
1)удалить PK
автор
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[I_STATISTICS_CALL_IN]') AND name = N'PK_I_STATISTICS_CALL_IN')
ALTER TABLE [dbo].[I_STATISTICS_CALL_IN] DROP CONSTRAINT [PK_I_STATISTICS_CALL_IN]
GO

2) Создать новый
автор
ALTER TABLE [dbo].[I_STATISTICS_CALL_IN] ADD CONSTRAINT [PK_I_STATISTICS_CALL_IN] PRIMARY KEY CLUSTERED
(
[DATETIME], [GLOBAL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


Предварительно помолившись и создав бекап.
Все верно?
25 ноя 13, 11:04    [15184002]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
AlexeyMish
Все верно?
Да.
25 ноя 13, 11:20    [15184097]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
AlexeyMish,

alexeyvg
AlexeyMish
В сутки порядка 40тыс строк. Т.е. если в запросе выборка за 10 дней, то около 400 тыс уникальных строк попадает в выборку.
Получается, сервер считает, что выгоднее сканировать, чем использовать индекс.

А сколько у вас всего строк в таблице? И как часто вы вручную обновляете статистику?
25 ноя 13, 21:30    [15188054]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
Mind,
около 24 млн строк.
вручную не обновлял. Как я говорил, я не дбошник. Приходится этим заниматься, т.к. больше особо некому.
25 ноя 13, 21:33    [15188066]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
AlexeyMish, вставлю свои пять копеек.
Попробуйте воспользоваться Database Tuning Advisor, насколько я осведомлён, штука очень неплохая. Но честно признаюсь, сам не пользовался, не дошли руки.

И ещё: вы по английски нормально читаете? В msdn всё очень складно по вашей проблеме описано, причём, я всё чаще там же вижу очень неплохие переводы. Это дело одного выходного дня...

PS: После того как местная детвора начала спорить с тем, что зеркальная база лучше копии бекапа, я, ожидая очередной виток подростковой истерии, хочу предупредить, что кормить троллей не намерен. У меня много дел...
26 ноя 13, 01:53    [15188782]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
ZOOKABAKODER
AlexeyMish, вставлю свои пять копеек.
Попробуйте воспользоваться Database Tuning Advisor, насколько я осведомлён, штука очень неплохая. Но честно признаюсь, сам не пользовался, не дошли руки.

И ещё: вы по английски нормально читаете? В msdn всё очень складно по вашей проблеме описано, причём, я всё чаще там же вижу очень неплохие переводы. Это дело одного выходного дня...

PS: После того как местная детвора начала спорить с тем, что зеркальная база лучше копии бекапа, я, ожидая очередной виток подростковой истерии, хочу предупредить, что кормить троллей не намерен. У меня много дел...

Благодарю. Читаю нормально, английский не родной, но техническую документацию перевариваю. :)
Насчет бекапов и зеркалирования - это сравнивать теплое с мягким, я вроде как из юношеского максимализма вырос. Кстати, раз пошла такая пьянка, может быть подскажете по моему второму [url=]https://www.sql.ru/forum/1061533/problemy-s-zerkalirovaniem-mirroring-ms-sql-2008r2[/url] посту что-нибудь?
26 ноя 13, 08:07    [15189046]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AnyKey45
Member

Откуда: Ekaterinburg-Moscow-EU
Сообщений: 219
AlexeyMish,
и все же - что со статистикой?
менять кластерный не спешите.

+
SELECT name AS stats_name, 
    STATS_DATE(object_id, stats_id) AS statistics_update_date
FROM sys.stats 
WHERE object_id = OBJECT_ID('dbo.I_STATISTICS_CALL_IN');
GO
26 ноя 13, 10:59    [15189855]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
AnyKey45,

AnyKey45,

Я пока не стал менять. После явной подсказки оптимизатору какой индекс использовать, скорость работы меня устроила.
Но понять почему он себя так вел, без явного указания было бы неплохо.

PK_I_STATISTICS_CALL_IN	2013-08-15 15:36:01.527
I_STATISTICS_CALL_IN_IDX1	2013-10-31 23:18:03.730
I_STATISTICS_CALL_IN_IDX2	2013-08-21 15:49:04.510
_WA_Sys_00000023_0DAF0CB0	2013-08-15 15:50:46.640
_WA_Sys_00000008_0DAF0CB0	2013-08-21 15:49:25.707
_WA_Sys_0000001F_0DAF0CB0	2013-08-21 15:49:26.330
_WA_Sys_0000001D_0DAF0CB0	2013-08-21 15:49:26.667
_WA_Sys_0000001C_0DAF0CB0	2013-08-15 15:50:49.793
_WA_Sys_00000019_0DAF0CB0	2013-08-21 15:49:26.997
_WA_Sys_00000018_0DAF0CB0	2013-08-21 15:49:27.517
_WA_Sys_00000017_0DAF0CB0	2013-08-21 15:49:28.263
_WA_Sys_00000014_0DAF0CB0	2013-08-21 15:49:28.657
_WA_Sys_00000013_0DAF0CB0	2013-08-21 15:49:29.240
_WA_Sys_00000012_0DAF0CB0	2013-08-21 15:49:30.097
_WA_Sys_0000000F_0DAF0CB0	2013-08-21 15:49:30.770
_WA_Sys_0000000E_0DAF0CB0	2013-08-21 15:49:32.637
_WA_Sys_0000000D_0DAF0CB0	2013-08-21 15:49:34.070
_WA_Sys_00000007_0DAF0CB0	2013-08-21 15:49:06.033
_WA_Sys_00000006_0DAF0CB0	2013-08-21 15:49:34.860
_WA_Sys_00000005_0DAF0CB0	2013-08-21 15:49:36.250
_WA_Sys_0000000B_0DAF0CB0	2013-09-07 11:35:51.427
_WA_Sys_00000004_0DAF0CB0	2013-11-05 08:10:40.387
_WA_Sys_00000003_0DAF0CB0	2013-10-10 08:11:59.853
_WA_Sys_0000000C_0DAF0CB0	2013-08-30 09:29:50.880
_WA_Sys_00000011_0DAF0CB0	2013-11-20 15:04:45.243
_WA_Sys_00000010_0DAF0CB0	2013-08-15 08:26:53.237
_WA_Sys_00000021_0DAF0CB0	2013-09-11 12:46:52.750
_WA_Sys_0000001B_0DAF0CB0	2013-09-20 15:20:33.860
_WA_Sys_0000001A_0DAF0CB0	2012-12-12 12:35:52.727
_WA_Sys_00000016_0DAF0CB0	2013-11-20 15:04:45.843
_WA_Sys_00000009_0DAF0CB0	2013-11-25 17:22:08.607
_WA_Sys_00000001_0DAF0CB0	2013-10-18 12:36:36.897
_WA_Sys_00000015_0DAF0CB0	2013-10-10 08:16:37.337
_WA_Sys_00000020_0DAF0CB0	2013-02-14 13:25:56.590
IDX_DATETIME	2013-10-11 12:41:03.923
IDX_ANUMBERDIALED	2013-10-25 12:26:30.653
IDX_Sumlenght	2013-11-01 07:58:36.690
IDX_callresult	2013-11-01 07:58:37.170
IDX_ServerID_CALLID	2013-11-21 11:27:17.187
IDX_ANUMBER	2013-09-05 14:45:27.907
_WA_Sys_0000001E_0DAF0CB0	2013-09-26 09:57:04.967
_WA_Sys_00000022_0DAF0CB0	2013-09-26 10:40:30.883


Offtop
Как тут оформить текст в виде раскрывающего списка?
что-то не нашел.
26 ноя 13, 11:06    [15189905]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AnyKey45
Member

Откуда: Ekaterinburg-Moscow-EU
Сообщений: 219
AlexeyMish,

Ищи лучше - я же нашел)))

IDX_DATETIME 2013-10-11 12:41:03.923 = статистика по индексу, по которому ты заставил работать запрос хинтом
(WITH (nolock, INDEX(IDX_DATETIME)))
обновлялась 11 октября, оптимизатор создает план на основе статистики, т.е. по сути у него план на 11 октября

сделай джоб, который каждый день будет обновлять тебе статистку во время наименьшей нагрузки.
Я использую решение от http://ola.hallengren.com

еще момент по предложенному ранее INCLUDE(Global_ID)= насколько я знаю - кластерный индекс включается в любой некластерный, т.е. в твоем запросе для поиска в WHERE используется IDX_DATETIME, который для SELECT уже содержит в себе кластерный Global_ID
26 ноя 13, 11:24    [15190016]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AnyKey45
Member

Откуда: Ekaterinburg-Moscow-EU
Сообщений: 219
AnyKey45,

обнови статистику - только учти это не быстрая операция(зависит от глубины сканирования) и попробуй запрос без хинта - оптимизатор должен будет построить самостоятельно нормально работающий план
26 ноя 13, 11:26    [15190035]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
AnyKey45,
Я признаюсь в том, что сделал глупую вещь которую сейчас перепишу, так как стало стыдно. :(

declare @GLOBAL_IDmin (bigint)
declare @GLOBAL_IDmax (bigint)

declare @DT1 float
declare @DT2 float

set @DT1=Convert(float,Convert(datetime,'20131120'))
set @DT2=Convert(float,Convert(datetime,'20131125'))

Select @GLOBAL_IDmin =@min(Global_ID), GLOBAL_IDmax=ax(GLOBAL_ID) from INFDATA_STAT.dbo.I_STATISTICS_CALL_IN
where DATETIME	between @DT1 and @DT2

Select * from from INFDATA_STAT.dbo.I_STATISTICS_CALL_IN
where GLOBAL_ID between GLOBAL_IDmin  and GLOBAL_IDmax
26 ноя 13, 11:32    [15190077]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
AnyKey45,
Прошу прощения, за глупый вопрос.
Но в свойствах БД, Auto Update Statistics чем занимается?
Явно не этим, но называние так и шепчет, "я то что надо!".
26 ноя 13, 11:35    [15190097]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Glory
Member

Откуда:
Сообщений: 104760
AlexeyMish
Но в свойствах БД, Auto Update Statistics чем занимается?

Обновлением статистики. Только обновление это не по расписанию. А тогда, когда сложатся условия для обновления.
26 ноя 13, 11:38    [15190120]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AnyKey45
Member

Откуда: Ekaterinburg-Moscow-EU
Сообщений: 219
AlexeyMish,

тем чем надо - но не так часто, как бы тебе это хотелось(условия можешь загуглить, по-моему при вставке не менее 20% от имеющихся данных), иначе у тебя вообще даты статистик менялись бы только при ребилде индексов
26 ноя 13, 11:41    [15190137]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Azik23
Member

Откуда:
Сообщений: 11
В какие вы уже дебри залезли :(

ИМХО, тут вопрос в корректности целей получения минимального и максимального значениея ПК: далее планируется работа с ними в качестве ограничений в других запросах, но это уже ошибка проектирования... ИМХО явная ошибка алгоритма, за счет чего серъезные потери временных показателей уже на уровне алгоритма...

ПК - этт фактически служебное поле. В идеале ПК сделать некластеризированным, а кластерным сделать индекс по искомому полю DATETIME, как я понимаю большинство запросов будет задействовывать именно ограничение по датам. Кластерный(физически организованный) индекс наиболее сильный, потому он 1 и может быть на таблице. Посему отойти от получения min и max, а вместо них в других запросах использовать именно диапазон дат. В итоге весь остальной огород здесь наваленный практически теряет смысл :)
26 ноя 13, 17:22    [15193146]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Azik23
Member

Откуда:
Сообщений: 11
AlexeyMish
AnyKey45,
Я признаюсь в том, что сделал глупую вещь которую сейчас перепишу, так как стало стыдно. :(

declare @GLOBAL_IDmin (bigint)
declare @GLOBAL_IDmax (bigint)

declare @DT1 float
declare @DT2 float

set @DT1=Convert(float,Convert(datetime,'20131120'))
set @DT2=Convert(float,Convert(datetime,'20131125'))

Select @GLOBAL_IDmin =@min(Global_ID), GLOBAL_IDmax=ax(GLOBAL_ID) from INFDATA_STAT.dbo.I_STATISTICS_CALL_IN
where DATETIME	between @DT1 and @DT2

Select * from from INFDATA_STAT.dbo.I_STATISTICS_CALL_IN
where GLOBAL_ID between GLOBAL_IDmin  and GLOBAL_IDmax


Вот здесь как раз мы наблюдаем бесполезность выполнения 1-го запроса, если поступить как указано мной выше :)
зы пропустил пост при написании предыдущего сообщения :)
26 ноя 13, 17:25    [15193181]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Azik23
Member

Откуда:
Сообщений: 11
И с nolock поаккуратнее(если вздумаете указывать): этт конечно избавит от ожидания снятия Sx блокировок, но и соответственно приведет к dirty read :)
26 ноя 13, 17:30    [15193238]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Azik23
Member

Откуда:
Сообщений: 11
Очередной пример как задача не стоящая выеденного яйца из-за некомпетентности приводит к удорожанию разработки, без обид ТС :)
26 ноя 13, 17:34    [15193270]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Azik23
Member

Откуда:
Сообщений: 11
aleks2
AlexeyMish
CREATE TABLE [dbo].[I_STATISTICS_CALL_IN](
 CONSTRAINT [PK_I_STATISTICS_CALL_IN] PRIMARY KEY [s]CLUSTERED [/s](
	[GLOBAL_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


Для работы с таблицей как обязательно используются поля DATETIME.


Дык, сделай, страдалец, правильный кластерный индекс.

 CONSTRAINT [PK_I_STATISTICS_CALL_IN] UNIQUE CLUSTERED 
(
             [DATETIME]
           , [GLOBAL_ID] ASC
)


Вот этот "правильный" кластерный индекс.... нет слов....
26 ноя 13, 17:42    [15193352]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Azik23
Member

Откуда:
Сообщений: 11
alexeyvg
AlexeyMish
[DATETIME] [float] NULL,
Просто интересно - почему используется тип float вместо datetime?
Это же неудобно.


Вы знаете что физически из себя представляет тип DATETIME в любом языке?
26 ноя 13, 17:44    [15193366]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Azik23
Вы знаете что физически из себя представляет тип DATETIME в любом языке?

В любом языке не знаю, а базе MSSQL - это два целочисленных числа.
А что вы хотели сказать этим вопросом
26 ноя 13, 17:46    [15193386]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Azik23
alexeyvg
пропущено...
Просто интересно - почему используется тип float вместо datetime?
Это же неудобно.


Вы знаете что физически из себя представляет тип DATETIME в любом языке?


прям-таки в любом?
26 ноя 13, 17:46    [15193393]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Azik23
Member

Откуда:
Сообщений: 11
Glory
Azik23
Вы знаете что физически из себя представляет тип DATETIME в любом языке?

В любом языке не знаю, а базе MSSQL - это два целочисленных числа.
А что вы хотели сказать этим вопросом


Этт те что до запятой и после? :)
26 ноя 13, 17:54    [15193489]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Azik23
Этт те что до запятой и после? :)

Не то и не то.
Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
26 ноя 13, 17:56    [15193505]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Azik23
Member

Откуда:
Сообщений: 11
Glory
Azik23
Этт те что до запятой и после? :)

Не то и не то.
Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.


MS SQL я не трогал :) Вы правы, просто для меня datetime = float :)

PS
float [ (n) ]
Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.
nvalue
Precision
Storage size
1-24 7 digits 4 bytes
25-53 15 digits 8 bytes
26 ноя 13, 18:02    [15193567]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Azik23
Вы правы, просто для меня datetime = float :)

И это неправильно
26 ноя 13, 18:07    [15193597]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Azik23
Member

Откуда:
Сообщений: 11
Glory, ок, договорились :)
26 ноя 13, 18:10    [15193616]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
Glory,

может это и неправильно, но к сожалению, как я писал выше в моей БД так и есть. Не моя вина, как я говорил выше - издержки переноса с firebird на MS SQL)
Хотя тренд можно закрывать, пока что использую хинт для индекса.
Позже запущу пересчет статистики и создам джоб.
Я понимаю, что профессионалом я не буду, но базовые вещи хочется понимать. Благодаря топику продвинулся в понимании, за что всем большое спасибо.
26 ноя 13, 20:12    [15194201]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Веrd
Member

Откуда: Лазаревское
Сообщений: 575
alexeyvg
Получается, сервер считает, что выгоднее сканировать, чем использовать индекс.

Обновить статистику уже предлагали?
26 ноя 13, 21:28    [15194526]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Веrd
Member

Откуда: Лазаревское
Сообщений: 575
AlexeyMish
alexeyvg
пропущено...
Рискованно такое предлагать...
ТС может быть не в курсе про все запросы в системе...
Хотя да, такой индекс пока видится идеальным, судя по описанию.

Про запросы я достаточно в курсе.
Фактически система представляет из себя набор приложений которые занимаются ТОЛЬКО вставкой в базу.
Все работы по выборке данных и работе с ними лежат на мне.
Учитывая, время и сроки за которые провели допиливание системы с firebird до MS SQL вероятность того, что разрабы занимались тюнингом индексов я оцениваю как предельно малую.
Строго говоря, чуть выше я сам пришел к мысли, что этот индекс надо пересоздать, просто с формулировкой у меня не очень. Ибо не специалист.
автор
в моем случае кластерный индекс лучше накладывать на поле не GLOBAL_ID, а сделать покрывающий кластерный индекс (если я правильно понял термины), на поля Global_ID+Datetime.
Суждение сформировано исходя из рекомендации:


Итак, если я решусь на этот шаг. Мне нужно будет
1)удалить PK
автор
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[I_STATISTICS_CALL_IN]') AND name = N'PK_I_STATISTICS_CALL_IN')
ALTER TABLE [dbo].[I_STATISTICS_CALL_IN] DROP CONSTRAINT [PK_I_STATISTICS_CALL_IN]
GO

2) Создать новый
автор
ALTER TABLE [dbo].[I_STATISTICS_CALL_IN] ADD CONSTRAINT [PK_I_STATISTICS_CALL_IN] PRIMARY KEY CLUSTERED
(
[DATETIME], [GLOBAL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


Предварительно помолившись и создав бекап.
Все верно?


Создаётся впечатление, что есть проблемы в проектировании системы.

Что важнее: вставка данных или выборка данных?

Важнее вставка - надо меньше индексов, если время вставки критично.
26 ноя 13, 21:31    [15194542]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Веrd
Member

Откуда: Лазаревское
Сообщений: 575
ZOOKABAKODER
AlexeyMish, вставлю свои пять копеек.
Попробуйте воспользоваться Database Tuning Advisor, насколько я осведомлён, штука очень неплохая. Но честно признаюсь, сам не пользовался, не дошли руки.

И ещё: вы по английски нормально читаете? В msdn всё очень складно по вашей проблеме описано, причём, я всё чаще там же вижу очень неплохие переводы. Это дело одного выходного дня...

PS: После того как местная детвора начала спорить с тем, что зеркальная база лучше копии бекапа, я, ожидая очередной виток подростковой истерии, хочу предупредить, что кормить троллей не намерен. У меня много дел...

О чём тут спор? - это разные вещи.
26 ноя 13, 21:33    [15194548]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Веrd
Member

Откуда: Лазаревское
Сообщений: 575
AlexeyMish
AnyKey45,
Я признаюсь в том, что сделал глупую вещь которую сейчас перепишу, так как стало стыдно. :(

declare @GLOBAL_IDmin (bigint)
declare @GLOBAL_IDmax (bigint)

declare @DT1 float
declare @DT2 float

set @DT1=Convert(float,Convert(datetime,'20131120'))
set @DT2=Convert(float,Convert(datetime,'20131125'))

Select @GLOBAL_IDmin =@min(Global_ID), GLOBAL_IDmax=ax(GLOBAL_ID) from INFDATA_STAT.dbo.I_STATISTICS_CALL_IN
where DATETIME	between @DT1 and @DT2

Select * from from INFDATA_STAT.dbo.I_STATISTICS_CALL_IN
where GLOBAL_ID between GLOBAL_IDmin  and GLOBAL_IDmax


Хм, хранить дату во Float...
Всегда думал, что MS SQL по SMALLDATETIME работает быстрее, чем по FLOAT...
26 ноя 13, 21:40    [15194567]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
AlexeyMish
Но в свойствах БД, Auto Update Statistics чем занимается?
Статистика обновляется автоматически при изменении не меньше 20% данных. Это для вашей таблицы произойдет через 4 месяца. (24М*20% = 4.8М и 4.8М/40К=120 дней). Так что я бы посоветовал делать это регулярно вручную.
26 ноя 13, 22:00    [15194628]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
[quot AlexeyMish]
ZOOKABAKODER
Насчет бекапов и зеркалирования - это сравнивать теплое с мягким, я вроде как из юношеского максимализма вырос.

Веrd
О чём тут спор? - это разные вещи.

Забудте, это я о своём...

AlexeyMish
Кстати, раз пошла такая пьянка, может быть подскажете по моему второму [url=]https://www.sql.ru/forum/1061533/problemy-s-zerkalirovaniem-mirroring-ms-sql-2008r2[/url] посту что-нибудь?

не подскажу...

Веrd
Хм, хранить дату во Float...
Всегда думал, что MS SQL по SMALLDATETIME работает быстрее, чем по FLOAT...

[SMALL]DATETIME ничего общего не имеет с FLOAT - их бинарный формат это целые числа. Если надо получить улучшение в работе с датами используйте DATE, TIME и DATETIME2.


AlexeyMish, поищите "MSSQL determine unused indexes" - там запрос есть чтоб это смотреть, "mssql query tuning" - имхо так статья в msdn иназывается.
26 ноя 13, 22:41    [15194819]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
Веrd ,

Эта база статистики поступающих телефонных вызовов.
Время на вставку не особо критично, по крайней мере проблем вызванных слишком долгой вставкой пока не ловил и не похоже, что это вскоре может стать узким местом.
Естественно, статистика нужна не для того, чтобы она просто была, а для того, чтобы по ней работать. Проводить анализ, смотреть результаты, наказывать невиновных, награждать непричастных и прочая. А вот тут у меня не все гладко с производительностью. Ибо запросы работают не слишком быстро и люди которые проводят этот анализ жалуются, что получение результатоы отчетов не слишком быстро. Именно эту проблему я и хочу если не решить, то снизить ее остроту.

ZOOKABAKODER,
Благодарю. Буду смотреть
27 ноя 13, 08:27    [15196082]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
Веrd
Хм, хранить дату во Float...
Всегда думал, что MS SQL по SMALLDATETIME работает быстрее, чем по FLOAT...
Точности SMALLDATETIME может не хватить.

А DATETIME занимает те же 8 байт, как и FLOAT.
В принципе для сервера неважно, DATETIME и FLOAT будут использоваться одинаково эффективно, просто это неудобно, ну и недоступна некоторая функциональность типа DATETIME (не говоря о том, что приходится использовать программирование методом тыка, допустим, прибавляя 1 для прибавления суток).
Azik23
alexeyvg
Просто интересно - почему используется тип float вместо datetime?
Это же неудобно.
Вы знаете что физически из себя представляет тип DATETIME в любом языке?
Думаю, в разных языках по разному, все языки в мире не знаю :-)
Очень (самый?) распространённый вариант - 100-наносекундные тики начиная от Р.Х.

А что?
27 ноя 13, 08:52    [15196144]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31470
AlexeyMish
Select @GLOBAL_IDmin =@min(Global_ID), GLOBAL_IDmax=ax(GLOBAL_ID) from INFDATA_STAT.dbo.I_STATISTICS_CALL_IN
where DATETIME	between @DT1 and @DT2

Select * from from INFDATA_STAT.dbo.I_STATISTICS_CALL_IN
where GLOBAL_ID between GLOBAL_IDmin  and GLOBAL_IDmax
Двойной запрос тут точно лишний, об этом уже написали.
27 ноя 13, 09:01    [15196176]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
AlexeyMish
Member

Откуда:
Сообщений: 71
alexeyvg,

Так я и сам признался, что понял, что эта концепция в корне не верна и уже исправился. )
Я же написал, что стало стыдно. )
27 ноя 13, 09:03    [15196181]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с построением индексов.  [new]
Azik23
Member

Откуда:
Сообщений: 11
alexeyvg
Веrd
Хм, хранить дату во Float...
Всегда думал, что MS SQL по SMALLDATETIME работает быстрее, чем по FLOAT...
Точности SMALLDATETIME может не хватить.

А DATETIME занимает те же 8 байт, как и FLOAT.
В принципе для сервера неважно, DATETIME и FLOAT будут использоваться одинаково эффективно, просто это неудобно, ну и недоступна некоторая функциональность типа DATETIME (не говоря о том, что приходится использовать программирование методом тыка, допустим, прибавляя 1 для прибавления суток).
Azik23
пропущено...
Вы знаете что физически из себя представляет тип DATETIME в любом языке?
Думаю, в разных языках по разному, все языки в мире не знаю :-)
Очень (самый?) распространённый вариант - 100-наносекундные тики начиная от Р.Х.

А что?


Вот, приятно, коллега :)

100 x 10^-9 = 1 x 10^-7

мот все таки 1000-наносекундные? :)
27 ноя 13, 13:11    [15197975]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2 3      [все]
Все форумы / Microsoft SQL Server Ответить