I would greatly appreciate if someone could kindly help me in this matter:
I am running a large OLAP database with the biggest table of 255 million+
records. Recently I've added two autocomputed and persisted columns to this
table. Ever since then, I'm not able to update this table, although I am abl
e
to INSERT into the table.
When I attempt to UPDATE, there's no error, but the update command takes
forever and nothing is happening. In Microsoft SQL Server Management Studio,
this is accompanied by a nice hourglass icon.
The table is properly indexed, so the problem is not there. The data is
accessible, the column that I am updating is not autocomputed nor is there
any restriction on that column whatsoever. It's of type "bit".
It worked before I added the two autocomputed columns. As I am updating this
OLAP database once a week, something else might have happened and this issue
is maybe not related to those autocomputed columns at all. But who knows.
Just for the sake of perfect clarity, let's say I'm going to run this comman
d:
SELECT * FROM Tick WHERE TickID = 123456789
It completes immediately and returns the desired record. But the following
similar command:
UPDATE Tick WHERE TickID = 123456789 SET UniqueTick = 1
is a "permawaiter"
Am I missing something?
Thank you very much for any input!
Michal Kreslik
michal.kreslik(at)kreslik.comThis is how the table looks like when scripted:
-- begin script
---
USE [FXdataDB]
GO
/****** Object: Table [dbo].[Tick] Script Date: 02/08/2007 23:04
:50
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tick](
[TickID] [int] IDENTITY(1,1) NOT NULL,
[SourceFileID] [smallint] NOT NULL,
[FXPairID] [tinyint] NOT NULL,
[Year] [smallint] NOT NULL,
[Month] [tinyint] NOT NULL,
[Day] [tinyint] NOT NULL,
[DayOfWeekID] [tinyint] NOT NULL,
[Hour] [tinyint] NOT NULL,
[Minute] [tinyint] NOT NULL,
[Second] [tinyint] NOT NULL,
[Bid] [real] NOT NULL,
[Ask] [real] NOT NULL,
[UniqueTick] [bit] NULL,
[IsWithinSession] AS
([dbo].& #91;GetIsWithinSessionWithSchemaBinding]
([DayOfWeekID],[
Hour],[Minute],[Second])) PERSISTED,
[MinsFromSStart] AS
([dbo].& #91;GetMinsFromSStartWithSchemaBinding](
[DayOfWeekID],[H
our],[Minute],[Second])) PERSISTED,
[SecsFromSStart] AS
([dbo].& #91;GetSecsFromSStartWithSchemaBinding](
[DayOfWeekID],[H
our],[Minute],[Second])) PERSISTED,
[TickTime] AS
((((([Second]+(100)*[Minute])+(10000)*[Hour])+(1000000)*[Day
])+(100000000)*[Month])+(10000000000.)*[Year]) PERSISTED
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'' ,
@.level0type=N'SCHEMA',@.level0name=N'dbo'
,
@.level1type=N'TABLE',@.level1name=N'Tick'
,
@.level2type=N'COLUMN',@.level2name=N'Sour
ceFileID'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Holds the
number of minutes from Forex session start (17:00:00 EST Sunday)' ,
@.level0type=N'SCHEMA',@.level0name=N'dbo'
,
@.level1type=N'TABLE',@.level1name=N'Tick'
,
@.level2type=N'COLUMN',@.level2name=N'Mins
FromSStart'
---
-- end of script
As stated above, I am attempting to UPDATE the UniqueTick column here.
Thanks,
Michal Kreslik|||Do you have any indexes on the table? I expect that a table scan of 255
million rows to take a while, even if only 1 row is updated.
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:55499C83-6ACB-4AE1-8319-A2D5F05473E6@.microsoft.com...
> This is how the table looks like when scripted:
> -- begin script
> ---
> USE [FXdataDB]
> GO
> /****** Object: Table [dbo].[Tick] Script Date: 02/08/2007 23:
04:50
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[Tick](
> [TickID] [int] IDENTITY(1,1) NOT NULL,
> [SourceFileID] [smallint] NOT NULL,
> [FXPairID] [tinyint] NOT NULL,
> [Year] [smallint] NOT NULL,
> [Month] [tinyint] NOT NULL,
> [Day] [tinyint] NOT NULL,
> [DayOfWeekID] [tinyint] NOT NULL,
> [Hour] [tinyint] NOT NULL,
> [Minute] [tinyint] NOT NULL,
> [Second] [tinyint] NOT NULL,
> [Bid] [real] NOT NULL,
> [Ask] [real] NOT NULL,
> [UniqueTick] [bit] NULL,
> [IsWithinSession] AS
> ([dbo].& #91;GetIsWithinSessionWithSchemaBinding]
([DayOfWeekID],
1;Hour],[Minute],[Second]))
> PERSISTED,
> [MinsFromSStart] AS
> ([dbo].& #91;GetMinsFromSStartWithSchemaBinding](
[DayOfWeekID],[
;Hour],[Minute],[Second]))
> PERSISTED,
> [SecsFromSStart] AS
> ([dbo].& #91;GetSecsFromSStartWithSchemaBinding](
[DayOfWeekID],[
;Hour],[Minute],[Second]))
> PERSISTED,
> [TickTime] AS
> ((((([Second]+(100)*[Minute])+(10000)*[Hour])+(1000000)*[D
ay])+(100000000)*[Month])+(10000000000.)*[Year])
> PERSISTED
> ) ON [PRIMARY]
> GO
> SET ANSI_PADDING OFF
> GO
> EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'' ,
> @.level0type=N'SCHEMA',@.level0name=N'dbo'
,
> @.level1type=N'TABLE',@.level1name=N'Tick'
,
> @.level2type=N'COLUMN',@.level2name=N'Sour
ceFileID'
> GO
> EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Holds
> the
> number of minutes from Forex session start (17:00:00 EST Sunday)' ,
> @.level0type=N'SCHEMA',@.level0name=N'dbo'
,
> @.level1type=N'TABLE',@.level1name=N'Tick'
,
> @.level2type=N'COLUMN',@.level2name=N'Mins
FromSStart'
> ---
> -- end of script
>
> As stated above, I am attempting to UPDATE the UniqueTick column here.
> Thanks,
> Michal Kreslik|||"Dan Guzman" wrote:
> Do you have any indexes on the table? I expect that a table scan of 255
> million rows to take a while, even if only 1 row is updated.
Yes, I have 6 indexes on the table. But before adding the extra two
autocomputed columns, UPDATE worked fine even with all those indexes in
place. It took about 5 ms to update.
So there's something fishy about that.
What this behaviour could be caused by?
Thanks,
Michal Kreslik|||> What this behaviour could be caused by?
Have you looked at the execution plan to ensure indexes are used as
expected? These symptoms can be due to blocking - have you checked activity
during the update?
I can try to repro if you provide the script for the user-defined function
and all indexes too. I didn't see anything obvious in the scripts you
posed. I assume your actual UPDATE statement is:
UPDATE Tick
SET UniqueTick = 1
WHERE TickID = 123456789
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:15AECAEA-A24D-496D-8101-80B21DAF63EC@.microsoft.com...
> "Dan Guzman" wrote:
>
> Yes, I have 6 indexes on the table. But before adding the extra two
> autocomputed columns, UPDATE worked fine even with all those indexes in
> place. It took about 5 ms to update.
> So there's something fishy about that.
> What this behaviour could be caused by?
> Thanks,
> Michal Kreslik|||"Dan Guzman" wrote:
> Have you looked at the execution plan to ensure indexes are used as
> expected? These symptoms can be due to blocking - have you checked activi
ty
> during the update?
> I can try to repro if you provide the script for the user-defined function
> and all indexes too. I didn't see anything obvious in the scripts you
> posed. I assume your actual UPDATE statement is:
> UPDATE Tick
> SET UniqueTick = 1
> WHERE TickID = 123456789
>
Thanks very much for your swift reply.
Yes, obviously I execute the statement with the correct syntax, sorry.
I have just checked the execution plan and everything seems to be ok.
>- have you checked activity during the update?
You mean, whether SQL server is using CPU time? No, there's almost no CPU
activity during this strange UPDATE.
I have uploaded the CREATE script for the entire database here:
http://kreslik.com/files/FXdataDB_Scripted_All.zip
Also, ever since I added those two autocomputed columns, the SQL server
seems to be "caching" something to disk all the time (using up to 50% of
CPU). It always starts a couple of minutes after the SQL server starts and
the only way to stop it (although I guess there's no reason why SQL server
should be "recomputing" persisted columns) was to restart the SQL server.
Today it seems it is no longer "caching", but I'm not sure why. Could this b
e
related to my problem? How do I find out what exactly what is SQL server
doing and eventually how do I put and end to such a nonsensical "caching"?
Thanks much for any help.
Michal Kreslik|||> You mean, whether SQL server is using CPU time? No, there's almost no CPU
> activity during this strange UPDATE.
What about blocking and disk activity?
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:A49FAE08-3EDD-42D1-9EA9-6E0A3FD139DE@.microsoft.com...
> "Dan Guzman" wrote:
>
> Thanks very much for your swift reply.
> Yes, obviously I execute the statement with the correct syntax, sorry.
> I have just checked the execution plan and everything seems to be ok.
>
> You mean, whether SQL server is using CPU time? No, there's almost no CPU
> activity during this strange UPDATE.
> I have uploaded the CREATE script for the entire database here:
> http://kreslik.com/files/FXdataDB_Scripted_All.zip
> Also, ever since I added those two autocomputed columns, the SQL server
> seems to be "caching" something to disk all the time (using up to 50% of
> CPU). It always starts a couple of minutes after the SQL server starts and
> the only way to stop it (although I guess there's no reason why SQL server
> should be "recomputing" persisted columns) was to restart the SQL server.
> Today it seems it is no longer "caching", but I'm not sure why. Could this
> be
> related to my problem? How do I find out what exactly what is SQL server
> doing and eventually how do I put and end to such a nonsensical "caching"?
> Thanks much for any help.
> Michal Kreslik|||"Dan Guzman" wrote:
> What about blocking and disk activity?
No, there doesn't seem to be any disk activity.
Michal Kreslik|||Hi, Michal. I haven't been able to repro your problem on this end. I
created the database (without the last 2 computed columns) and loaded the
Tick table with 10M rows of test data. I then added the computed columns
and index and the update statement ran quickly.
Can you post the output of the following query against the hung spid?
Perhaps this and/or the other dm views will provide a clue as to what's
going on.
SELECT *
FROM sys.dm_exec_requests
WHERE session_id = ?
FOR XML PATH ('dm_exec_requests')
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:8564911C-2F36-4938-9B4A-3829152D03C6@.microsoft.com...
> "Dan Guzman" wrote:
>
> No, there doesn't seem to be any disk activity.
> Michal Kreslik|||Hello, Dan,
thanks for this idea. Quite suprisingly, the UPDATE command started working
all by itself out of the blue.
But anyway, this strange behavior should not be left unexplained as it might
happen again.
I ran the SQL command you suggested against the (now running ok) UPDATE
spid. If you can see something that might have been causing the UPDATE
getting stuck, please let me know.
XML result:
<dm_exec_requests>
<session_id>58</session_id>
<request_id>0</request_id>
<start_time>2007-02-13T17:42:35.430</start_time>
<status>suspended</status>
<command>UPDATE</command>
<sql_handle>AgAAANT0Yi4+B5OkY6CYr8CIw0pT0XYp</sql_handle>
<statement_start_offset>30</statement_start_offset>
<statement_end_offset>-1</statement_end_offset>
<plan_handle>BgAHANT0Yi64oVkEAAAAAAAAAAAAAAAA</plan_handle>
<database_id>7</database_id>
<user_id>1</user_id>
<connection_id>0E8F7597-F1AC-44D5-AE5A-48F3081915E7</connection_id>
<blocking_session_id>0</blocking_session_id>
<wait_type>WRITELOG</wait_type>
<wait_time>0</wait_time>
<last_wait_type>WRITELOG</last_wait_type>
<wait_resource></wait_resource>
<open_transaction_count>1</open_transaction_count>
<open_resultset_count>1</open_resultset_count>
<transaction_id>1633798</transaction_id>
<context_info></context_info>
<percent_complete>0.0000000e+000</percent_complete>
<estimated_completion_time>0</estimated_completion_time>
<cpu_time>0</cpu_time>
<total_elapsed_time>2</total_elapsed_time>
<scheduler_id>0</scheduler_id>
<task_address>AGiKeA==</task_address>
<reads>135</reads>
<writes>325</writes>
<logical_reads>413692</logical_reads>
<text_size>2147483647</text_size>
<language>us_english</language>
<date_format>mdy</date_format>
<date_first>7</date_first>
<quoted_identifier>1</quoted_identifier>
<arithabort>0</arithabort>
<ansi_null_dflt_on>1</ansi_null_dflt_on>
<ansi_defaults>0</ansi_defaults>
<ansi_warnings>1</ansi_warnings>
<ansi_padding>1</ansi_padding>
<ansi_nulls>1</ansi_nulls>
<concat_null_yields_null>1</concat_null_yields_null>
<transaction_isolation_level>2</transaction_isolation_level>
<lock_timeout>-1</lock_timeout>
<deadlock_priority>0</deadlock_priority>
<row_count>1</row_count>
<prev_error>0</prev_error>
<nest_level>0</nest_level>
<granted_query_memory>0</granted_query_memory>
<executing_managed_code>0</executing_managed_code>
</dm_exec_requests>
Thank you very much, Dan,
Michal Kreslik
No comments:
Post a Comment