CODEWITHSUNDEEP
sql-server-2008sql-update
Erdem Şekerci
Erdem Şekerci

Reputation: 51

MsSql Server , update query execution took too much time

I have a sql server db keeping our text document datas.

A specific table is keeping thousands of text paragraphs themself, This table suddenly started to respond so lately to update queries abnormally. Sometimes non-complicated basic update query takes 30 - 40 secs. to execute and asp.net application uses this table throws timeout exception.

Select queries last normal as before but update.

Why this happens and what should I do?

Server system is Windows 2008 R2 Standard and MsSql Server 2008 R2

Below's my table meta data.

CREATE TABLE [dbo].[MetinIcerikTablosuCari](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [MetinIsimID] [int] NOT NULL,
    [MetinIsimID_Eski_1] [int] NULL,
    [MetinIsimID_Eski_2] [int] NULL,
    [MetinIsimID_Eski_3] [int] NULL,
    [MetinIsimID_Eski_4] [int] NULL,
    [MetinIsimID_Eski_5] [int] NULL,
    [MetinIcindekilerID] [int] NOT NULL,
    [SilmeTalebi] [bit] NULL,
    [SiraNo] [int] NOT NULL,
    [ParagrafMetin] [text] NOT NULL,
    [RevizeParagraf] [text] NULL,
    [RevizeVarMi] [bit] NULL,
    [RevizasyonOnayi] [bit] NULL,
    [RevizasyonTarihi] [datetime] NULL,
    [RevizasyonSahibiID] [int] NULL,
    [RevizasyonOnayTarihi] [datetime] NULL,
    [RevizasyonOnaylayanID] [int] NULL,
    [RevizasyonSonOnay] [bit] NULL,
    [RevizeTuru] [int] NULL,
    [ParagrafRevizeIslemJetonu] [nvarchar](50) NULL,
    [BaglantiliOrtakRevizeSeriNo] [int] NULL,
    [SonOnayTarihi] [datetime] NULL,
    [SonOnaylayanID] [int] NULL,
    [ParagrafMetin_Eski_1] [text] NULL,
    [ParagrafMetin_Eski_2] [text] NULL,
    [ParagrafMetin_Eski_3] [text] NULL,
    [ParagrafMetin_Eski_4] [text] NULL,
    [ParagrafMetin_Eski_5] [text] NULL,
    [GirisTarihi] [nvarchar](50) NULL,
    [YayinTarihi] [nvarchar](50) NULL,
    [ParagrafNot] [nvarchar](max) NOT NULL,
    [DipNotTag] [nvarchar](max) NOT NULL,
    [MedyaURL] [nvarchar](100) NULL,
    [MedyaUrlNot] [nvarchar](max) NOT NULL,
    [VersiyonMetin] [int] NULL,
    [EtkileyenMetinID] [int] NULL,
    [tarih] [nvarchar](max) NULL,
    [GuncellemeTarihi] [datetime] NULL,
    [NotParagrafi] [bit] NULL,
    [YeniParagraf] [bit] NULL,
    [MedyaParagrafi] [bit] NULL,
    [EslestirmeSilmeOnayID] [int] NULL,
    [YargiOnBilgiSayfasi] [bit] NULL,
    [BolumParagrafi] [bit] NULL,
    [UstParagrafID] [int] NULL,
 CONSTRAINT [PK_MetinIcerikTablosuCari] PRIMARY KEY CLUSTERED 
(
    [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] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_MetinIsimID]  DEFAULT ((0)) FOR [MetinIsimID]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_MetinIcindekilerID]  DEFAULT ((0)) FOR [MetinIcindekilerID]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_SiraNo]  DEFAULT ((0)) FOR [SiraNo]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_ParagrafMetin]  DEFAULT ('') FOR [ParagrafMetin]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_RevizeTuru]  DEFAULT ((1)) FOR [RevizeTuru]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_ParagrafNot]  DEFAULT ('') FOR [ParagrafNot]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_DipNotTag]  DEFAULT ('') FOR [DipNotTag]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_MedyaUrlNot]  DEFAULT ('') FOR [MedyaUrlNot]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_NotParagrafi]  DEFAULT ((0)) FOR [NotParagrafi]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_YeniParagraf]  DEFAULT ((0)) FOR [YeniParagraf]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_MedyaParagrafi]  DEFAULT ((0)) FOR [MedyaParagrafi]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_EslestirmeSilmeOnayID]  DEFAULT ((0)) FOR [EslestirmeSilmeOnayID]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_YargiOnBilgiSayfasi]  DEFAULT ((0)) FOR [YargiOnBilgiSayfasi]
GO

ALTER TABLE [dbo].[MetinIcerikTablosuCari] ADD  CONSTRAINT [DF_MetinIcerikTablosuCari_BolumParagrafi]  DEFAULT ((0)) FOR [BolumParagrafi]
GO

I checked out and executed the update stats sp, but it seems the problem keeps going.

A simple update query like this: update MetinIcerikTablosuCari set ParagrafMetin = '<p>SPK Bültenleri - 2002/34: #media#93640#media#</p>' where ID = 35606 takes 20 - 30 secs or more.

I checked the CPU usage while the query executing, it suddenly picks to appr. 75 - 80%.

I could not determine if it's about lack of server memory or sql uses memory abnormally.

Below is my server hardware specifications:

Windows 2008 R2 Standard - Service Pack1
Processor: Intel Core i5-2400 CPU @ 3.1 Ghz
RAM: 8GB
System Type: 64-bit

In addition, could this be kind of lack of updates for Windows Server or MsSql Server 2008 r2

Upvotes: 1

Views: 76

Answers (1)

Anuj Tripathi
Anuj Tripathi

Reputation: 2281

As you haven't provided the execution plan and other necessary details, my assumption is that you might have indexes which are causing slow updates. Indexes helps running SELECT fast but slow down insert and update statements. List out all the indexes (Clustered & Non-Clustered) and then remove unnecessary indexes from your table. Also, how often do you update statistics? If that is not recently done then run following SP

EXEC Sp_Updatestats;

If above two ways doesn't solve your problem then I recommend you to post table metadata and execution plan.

Upvotes: 2

Related Questions