September 10, 2008 at 7:43 am
I am having a strange issue with a table. We moved about a million records into a table and now it does not seem to perform. I can select a specific record by the clustered key and it runs in about .5 seconds. If I try to delete that same record it does nothing. It will not run. the activity monitor says suspended. If I try to select Top 1 it does not run either. so I ran showcontig and the table is not fragmented. I did a checkdb and the database and the tables are fine. I am not sure what it can be. This table has a self referencing FK, and other FK's referencing other tables. I am running this local on developer version SQL SP2
September 10, 2008 at 7:45 am
Maybe there is a Delete trigger that is doing a Rollback?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 10, 2008 at 7:50 am
Have you updated stats on that table following the large data load? I have seen stats cause things to go out to lunch like that.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 10, 2008 at 8:30 am
When you delete, it does nothing, or runs forever?
If you have foreign keys to this table (it being the PK table) every other table that may reference the record you are deleting needs to be checked to ensure you are not creating orphans. This can be pretty slow if you have not indexed the fields everywhere.
September 10, 2008 at 8:38 am
I am currently running
UPDATE STATISTICS Pieces WITH ROWCOUNT=1000000
Go
Not sure if that will fix this or not. It has been already running for 10 minutes. Which makes me think there is an issue there. I do the same on another table and that command runs quickly.
As for the FK's I have done a conversion of a smaller db into this one and the delete only took milliseconds. Sure there were less then 10K but it will not let me delete at all. It just suspends and sits there. Last night I tried to create another table to put these records in and did an
insert into XXX
Select * from XXX
It ran for over 16 hours without getting out of suspended mode
Here is the table maybe this will help.
CREATE TABLE [dbo].[Pieces](
[PiecesID] [uniqueidentifier] NOT NULL,
[ItemsID] [uniqueidentifier] NOT NULL,
[ComponentPiecesID] [uniqueidentifier] NULL,
[ItemsInComponentItemsID] [uniqueidentifier] NULL,
[MaterialsID] [uniqueidentifier] NULL,
[CostPer] [uniqueidentifier] NULL,
[Cost] [decimal](19, 4) NULL,
[Usage] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StockLength] [decimal](19, 4) NULL,
[UnitCost] [decimal](19, 4) NULL,
[PieceType] [tinyint] NULL,
[ParentPiecesID] [uniqueidentifier] NULL,
[Quantity] [decimal](10, 0) NULL,
[ReferencedItemsID] [uniqueidentifier] NULL,
[CreatedDate] [smalldatetime] NOT NULL CONSTRAINT [def_Pieces_CreatedDate] DEFAULT (getdate()),
[LastUpdated] [smalldatetime] NULL,
[TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [pk_Pieces] PRIMARY KEY NONCLUSTERED
(
[PiecesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Statistic [_dta_stat_532196946_8] Script Date: 09/10/2008 09:36:33 ******/
CREATE STATISTICS [_dta_stat_532196946_8] ON [dbo].[Pieces]([Usage])
GO
ALTER TABLE [dbo].[Pieces] WITH NOCHECK ADD CONSTRAINT [fk_Pieces_ComponentPieces] FOREIGN KEY([ComponentPiecesID])
REFERENCES [dbo].[ComponentPieces] ([ComponentPiecesID])
GO
ALTER TABLE [dbo].[Pieces] NOCHECK CONSTRAINT [fk_Pieces_ComponentPieces]
GO
ALTER TABLE [dbo].[Pieces] WITH NOCHECK ADD CONSTRAINT [fk_Pieces_ItemsInComponentItems] FOREIGN KEY([ItemsInComponentItemsID])
REFERENCES [dbo].[ItemsInComponentItems] ([ItemsInComponentItemsID])
GO
ALTER TABLE [dbo].[Pieces] NOCHECK CONSTRAINT [fk_Pieces_ItemsInComponentItems]
GO
ALTER TABLE [dbo].[Pieces] WITH CHECK ADD CONSTRAINT [fk_Pieces_Pieces] FOREIGN KEY([ParentPiecesID])
REFERENCES [dbo].[Pieces] ([PiecesID])
GO
ALTER TABLE [dbo].[Pieces] CHECK CONSTRAINT [fk_Pieces_Pieces]
GO
ALTER TABLE [dbo].[Pieces] WITH CHECK ADD CONSTRAINT [fk1_Pieces_Items] FOREIGN KEY([ItemsID])
REFERENCES [dbo].[Items] ([ItemsID])
GO
ALTER TABLE [dbo].[Pieces] CHECK CONSTRAINT [fk1_Pieces_Items]
GO
ALTER TABLE [dbo].[Pieces] WITH CHECK ADD CONSTRAINT [fk2_Pieces_Items] FOREIGN KEY([ReferencedItemsID])
REFERENCES [dbo].[Items] ([ItemsID])
GO
ALTER TABLE [dbo].[Pieces] CHECK CONSTRAINT [fk2_Pieces_Items]
[/Code]
September 10, 2008 at 9:25 am
Suspended indicates that the query's waiting for a lock (or other resource). Query sys.dm_exec_requests, see if the query is blocked, see if it's waiting on a resource.
If it's blocked, check what the connection that's blocking it is doing. If it's waiting, see what the wait type and wait resource are.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2008 at 11:58 am
So I ended up running DBCC Checktable and that fixed the select issue. I was still having trouble on the delete. It was the self reference making the issue. I put an index on the parent FK and it started to perform the way it should. Thanks for the help
September 10, 2008 at 12:39 pm
proving once again one should provide index on a FK column,
unless actually tested that it hurts that particular system.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply