Urgent Help !! I'm loosing my business

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Urgent Help !! I'm loosing my business

Author

Topic

roswell
Starting Member

31 Posts

Posted - 2007-06-21 : 14:26:44

Symptoms
Sometimes SQL performance behaves stupid and sometimes works fine. It has been happening with the past month.

- Sometimes SP failed to run which insert data.
- Sometimes in SP only updates command run successfully but insert fail
- CPU rate gets high up to 100 %

Now its getting on my nerves. Last week a view was working perfectly but now my web application seems to fail to fetch the data. I ran the view in sql query analyzer and it took 11 minutes and also when I ran it from the webapplication the CPU rate was high upto 70% !! I clear the SP cache and rebuild the index and then run the view and it took 2 minutes which is long enough too. It shouldn't take more than 40secs. I tried to increase the connection string and command type timeout to even unlimited and it didnt work. says timeout ! However that will not be proper solution. Please help me !!

Please note that my sql database size is 20 GB
SQL 2000(SP4) on Windows 2003 Server
Norton installed

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 14:28:44

You need to post the problematic SQL code in order for us to help.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

roswell
Starting Member

31 Posts

Posted - 2007-06-21 : 14:33:28

Thanks for your reply.

There are not any specific code that I could show you. Several SPs run successfully but only one or two of them fail to run, there are any specific SP I can tell. Any SP can fail to execute suddenly with no reason.

for example in one of my sp, there are three transactions
update
insert
insert

the update run fine but insert fail, please note that it is inserting physical document file...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 14:34:20

We can't help with the information that you have provided.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

roswell
Starting Member

31 Posts

Posted - 2007-06-21 : 14:35:43

What do you need then?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 14:37:57

We need to see all SQL code that you want us to help you optimize or fix. We'll also need to see the CREATE TABLE statements for the tables involved in your queries, plus all indexes on those tables.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-06-21 : 14:39:22

Is your database set to AutoExtend? and is it set to the default 10% extension?

If so worth setting it to, say, 200MB fixed extension size.

10% of 20GB is going to take 30 minutes or so (I would guess) and if the server is busy lots of stuff will start timing out.

I would recommend that you physically defrag the database files. You will need to take them offline to do this (AFAIK). See CONTIG.EXE from Sysinternals. Backup first!!

I presume the database is not set to AutoShrink (nor to AutoClose!!), and that you are not doing any manual shrinks?

Kristen

roswell
Starting Member

31 Posts

Posted - 2007-06-21 : 14:55:16

CREATE PROCEDURE dbo.usp_State7WithPhysProxy
--*************************Incoming Variables
(@Client varchar(50) --1
,@MT varchar(50) --2
,@QA varchar(50) --3
,@PhyName varchar(50) --4
,@RefPhyName varchar(50) --5
,@PatName varchar(50) --6
,@PatBirth DateTime --7
,@PatNumber nvarchar(50) --8
,@ProcDate DateTime --9
,@RptDate DateTime --10
,@RptDesc varchar(50) --11
,@StrtTime datetime --12
,@EndTime varchar(50) --13
,@TBLTotalTime real --14
,@FileName varchar(255) --15
,@CharacterCount bigint --16
,@MTRate int output --17
,@MTFlat int --18
,@QARate int output --19
,@QAFlat varchar(50) --20
,@TBLTotalPages bigInt --21
,@JobID int out --22
,@Info1 varchar(50) --23
,@Info2 varchar(50) --24
,@Info3 varchar(50) --25
,@Info4 varchar(50) --26
,@AprvdBy nvarchar(50) --27
,@AprvlCode nvarchar(255) --28
,@Doc image --29
,@Role Int --30
,@sType varchar(50) --31
,@rtnMessage nvarchar(110) output --32
,@Person int --33
,@eJobID nvarchar(1) output --34
,@tblRevision int --35
,@AssocJobID int --36
)

AS

SET NOCOUNT ON
SET XACT_ABORT OFF

DECLARE @PersonID int
DECLARE @ClientID int
DECLARE @QAID int
DECLARE @PhyID int
DECLARE @RefID int
DECLARE @PatID int
DECLARE @DocID nvarchar
DECLARE @RateType varchar(3)
DECLARE @Rate float
DECLARE @Flat float
DECLARE @Phn varchar(5)
DECLARE @Pri integer
DECLARE @PriKey integer
DECLARE @Priority Int
DECLARE @DocExp DateTime
DECLARE @AddTime int
DECLARE @rtnQAID int
DECLARE @MaxTransNo int
DECLARE @MaxTransNoDoc int
DECLARE @CurrentState int
DECLARE @MaxTransNoDTN int
DECLARE @PriorityIn int
DECLARE @VjobID Integer
DECLARE @FailCode nvarchar(255)
Declare @OriginalPhyID int

Set @Rate = '0'
Set @Flat = '0'
Set @Phn = 'External Source'
Set @PriKey = '0'

/*Insert Into MsgTable VALUES ('1')*/
/* + CONVERT(varchar , @CurrentState) + ' RA= ' + CONVERT (varchar , @RA ) + ' Phs Id ' + CONVERT (varchar ,@PhysID) + ' JOBId ' + CONVERT (varchar , @JobId ) ))*/
Select @ClientID = [ClientID] from [dbo].[tblclient] where [Client] = @Client
Select @ClientID = IsNull(@ClientID,1)

Select @PhyID = IsNull(PersonID,1) from tblPeople where FullName = @PhyName

Select @OriginalPhyID = IsNull(PersonID,1) from tblPeople where FullName = @RefPhyName

Select @VjobID = JobID from tbl_Job where @JobID = JobID
Select @VjobID = IsNull(@VjobID,1)

Select @CurrentState = CurrentState
From tbl_Job
Where JobID = @JobID

If (@ClientID = 1)
Begin

If @FailCode is Not Null
Begin
Set @FailCode = @FailCode + 'Client ID Not Found,'
End

If @FailCode is Null
Begin
Set @FailCode = 'Client ID Not Found,'
End
End

If (@PhyID = 1)
Begin

If @FailCode is Not Null
Begin
Set @FailCode = @FailCode + 'Physician ID Not Found,'
End

If @FailCode is Null
Begin
Set @FailCode = 'Physician ID Not Found,'
End
End

If @CurrentState <> 6 and @PhyID > 1
Begin

If @FailCode is not null
Begin
Set @FailCode = @FailCode + 'A Document Cannot be Changed After it is Approved.'
End
Else
Begin
Set @FailCode = 'A Document Cannot be Changed After it is Approved.'
End

End

If (@VjobID = 1)
Begin

If @FailCode is Not Null
Begin
Set @FailCode = @FailCode + 'Job ID does not match, Upload Terminated'
End

If @FailCode is Null
Begin
Set @FailCode = 'Job ID does not match, Upload Terminated'
End
End

If (@FailCode is Not Null)
Begin
Select @rtnMessage = RTrim(Convert(nvarchar(6),@JobID)) + '!' + @FileName + '#Failed - ' + @FailCode
Insert Into MsgTable VALUES ('7')
/*RETURN 0*/
End

Select Distinct @MaxTransNoDTN = Max(TransNo)
From tbl_TransDTN
Where JobID = @JobID

Select Distinct @MaxTransNo = Max(TransNo) + 1
From tbl_Transaction
Where JobID = @JobID

Update [tbl_Job]
Set [CurrentState] = 7,[TransNo] = @MaxTransNo,ApprvdTransNo = @MaxTransNo
Where [JobID] = @JobID

Insert into [tbl_TransApprvd]
(JobID,FileName,[File],TransNo,PersonID,AppCode,AppBy)
Values
(@JobID, @FileName,@Doc,@MaxTransNo,@OriginalPhyID,@AprvlCode,@AprvdBy)

Insert into [tbl_Transaction]
([JobID],AssocJobID,[State],[Description],[TransNo],[PersonID],[TransactionDT],[Type])
Values
(@JobID,@JobID,'7','Document Approved Upload.',@MaxTransNo,@PhyID,GetDate(),'3')

if @@error = 0 and @rtnMessage = '0'
Begin
Set @rtnMessage = RTrim(Convert(nvarchar(6),@JobID)) + '!' + @FileName + '#Success'
End
RETURN 0
GO

In the bold code, only update runs and insert fails, but it happens SOMETIMES not EVERTIme which is seems to be confusing. .NET Application does not throw any error.
I'm also pasting the code for one of the table, and please note that all the tables have clustered indexing and clustered indexing enabled on each column but the primary key !! Is this a good approach?

Also please note that most of the time above SP runs perfect but then some other views failed to run, actually taking too much time if run from the query analyzer, my DB size is 20GB does it affects on performance too?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_TransApprvd]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_TransApprvd]
GO

CREATE TABLE [dbo].[tbl_TransApprvd] (
[JobID] [int] NULL ,
[FileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[File] [image] NULL ,
[TransNo] [int] NULL ,
[PersonID] [int] NULL ,
[AppCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AppBy] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PriKey] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 14:59:48

It is not possible to have more than one clustered index on a table, so please clarify what you mean by clustered indexing enabled on each column. And no it's not good to index every column. This negatively impacts performance on updates, inserts, and deletes as SQL Server has to constantly update the indexes.

In order for us to help debug the code, we would need to see the error. If it is failing, then an error is being thrown, so if your application isn't showing it then the application code is probably missing something.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 15:01:15

Also, @@ERROR changes each time a statement runs. Your current code is only grabbing the error value on the statement right above it, the second insert. So if the first update fails and the other two succeed, @@ERROR will show 0 since the last statement was successful.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

roswell
Starting Member

31 Posts

Posted - 2007-06-21 : 15:03:25

Hi Kristen,
thanks for your reply

Is your database set to AutoExtend? and is it set to the default 10% extension?

Yes. unrestricted growth 10 %

10% of 20GB is going to take 30 minutes or so (I would guess) and if the server is busy lots of stuff will start timing out.

I would recommend that you physically defrag the database files. You will need to take them offline to do this (AFAIK). See CONTIG.EXE from Sysinternals. Backup first!!

30 minutes? for ?

I presume the database is not set to AutoShrink (nor to AutoClose!!), and that you are not doing any manual shrinks?

Yes auto shirnk is off
however yes there is a job that optimize tables , rebuild indexes and shrink the database but its disabled since I getting this behaviour

roswell
Starting Member

31 Posts

Posted - 2007-06-21 : 15:04:58

auto create statistics = on
auto update statistics = on
torn page detection = on

roswell
Starting Member

31 Posts

Posted - 2007-06-21 : 15:08:00

quote:
Originally posted by tkizer

It is not possible to have more than one clustered index on a table, so please clarify what you mean by clustered indexing enabled on each column. And no it's not good to index every column. This negatively impacts performance on updates, inserts, and deletes as SQL Server has to constantly update the indexes.

There is this option cluster index while managing indexing on a table, there is no indexing on primary key and it was off. however the rest of the column are set on indexing Ill give you the screen shots.

quote:

In order for us to help debug the code, we would need to see the error. If it is failing, then an error is being thrown, so if your application isn't showing it then the application code is probably missing something.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Thats the problem, application says time out and when I run the query in analyzer it took too much time. and then everything backs to normal after few hours. :(

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 15:19:27

No screen shots, please.

Indexing every column is very bad for this stored procedure since it is doing an update and two inserts.

Why was every column indexed? Do you have a primary key constraint on these tables?

On tbl_Job, you definitely need an index on Job_Id. If it is the primary key, then check if a constraint is on that column. If there is, then you automatically get an index with it.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

roswell
Starting Member

31 Posts

Posted - 2007-06-21 : 15:24:04

on tbl_Job table, yes there is a primary key that is jobId however unfortunately no indexing on primary key but on other columns. I dont know why the DB made so.

there are primary keys on other tables as well however similarly every column is indexed but the primary key.

Well, I noticed, there are hundreds of table and in every table, this index thingy is there

.
Similar to this SP fail, few views that fetches the data are also failing, but sometimes.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 15:34:06

Yes there is an index on jobid if you have a primary key constraint on that column. You get one automatically in SQL Server with PKs.

You need to fix the situation where you have indexes on every column. You will receive poor performance on updates/inserts/deletes until it is corrected. We can't tell you which columns should be indexes as we aren't familiar with your environment. But you'll need to review your code to see which columns are accessed frequently in where clauses, order bys, group bys, join conditions to know which columns to index.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 15:34:45

quote:
Originally posted by roswell

Tara?
anyone? Please help.

You've got to be kidding me! I give up on this thread.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

roswell
Starting Member

31 Posts

Posted - 2007-06-21 : 15:38:16

What else could be fix? why CPU rate gets high? I'm inserting and updating image binary data, does it affect too? and it is not specific to update/insert/delete, a view that is fetching information taking longer time too as I mentioned in my first post..

roswell
Starting Member

31 Posts

Posted - 2007-06-21 : 15:39:51

quote:
Originally posted by tkizer

quote:
Originally posted by roswell

Tara?
anyone? Please help.

You've got to be kidding me! I give up on this thread.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

No please do not give up for God sake. I'm just eager to know whats happening and the more opinions I get the more closer to the problem. Please don't give up

I'm so sorry.

roswell
Starting Member

31 Posts

Posted - 2007-06-21 : 15:43:45

I did not mean not to take any assistance from you Tara, please don't give up you have been a great assistance so far and I will remove all indexing in fact i'll do anything if u tell me to change. please let me know what else could I do? I will be highly glad if you assist me.

roswell
Starting Member

31 Posts

Posted - 2007-06-21 : 16:11:37

Please........................Tara

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources