SQL Query performance - SQL Server Forums

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 Administration (2000)

SQL Query performance

Author

Topic

jeanyves72
Starting Member

4 Posts

Posted - 2005-08-22 : 10:01:35

I am using MS SQL Server 2000.
With SQL Profiler I can see that :
at 17:41:21,627 following SQL Request takes 165586 ms :
INSERT INTO STATJOB (UDOMID,UNITID,JDOMID,JOBID,STATUS,STCOUNT,STQCOUNT,STDATE,STELAPSE,STTEXT,STREV,STFLAG) VALUES (1098439303,8,1098439303,247,1,2203,0,1124200909,7,'Collect Job Performed',0,0)

But a similar SQL request executed before and after this one takes only 15 ms.

And there are a lot of similar situation in my SQL trace profile.
How could I find the cause of problem (long execution time of SQL query DELETE or INSERT).

THanls

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-22 : 10:08:33

What are the index(es) and constraints on this table?

jeanyves72
Starting Member

4 Posts

Posted - 2005-08-22 : 10:19:42

Here is the SQL statement for index and constraint for this table :
ALTER TABLE [dbo].[STATJOB] WITH NOCHECK ADD
CONSTRAINT [STATJOB_PK] PRIMARY KEY NONCLUSTERED
(
[UNITID],
[JOBID],
[UDOMID],
[JDOMID]
) ON [PRIMARY]
GO

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-08-22 : 10:31:52

quote:
Originally posted by jeanyves72

Here is the SQL statement for index and constraint for this table :
ALTER TABLE [dbo].[STATJOB] WITH NOCHECK ADD
CONSTRAINT [STATJOB_PK] PRIMARY KEY NONCLUSTERED
(
[UNITID],
[JOBID],
[UDOMID],
[JDOMID]
) ON [PRIMARY]
GO

Also triggers. You need to look at triggers.
Do you not have any indexes?

To check for triggers run this query:

SELECT * FROM sysobjects 
WHERE type = 'TR' and parent_obj = object_id('STATJOB')

To check for indexes run this query:

SELECT indid, name, groupid FROM sysindexes
WHERE id = OBJECT_ID('STATJOB') and name not like '_WA%'

Daniel
SQL Server DBA
www.dallasteam.com

jeanyves72
Starting Member

4 Posts

Posted - 2005-08-22 : 10:53:10

There is no trigger attached to table STATJOB.

SELECT * FROM sysobjects
WHERE type = 'TR' and parent_obj = object_id('STATJOB')
return no row.

Following SQL request :
SELECT indid, name, groupid FROM sysindexes
WHERE id = OBJECT_ID('STATJOB') and name not like '_WA%'

returns 2 rows :
0 STATJOB 1
2 STATJOB_PK 1

I have this problem with other tables.

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-08-22 : 11:02:24

quote:
Originally posted by jeanyves72

There is no trigger attached to table STATJOB.

SELECT * FROM sysobjects
WHERE type = 'TR' and parent_obj = object_id('STATJOB')
return no row.

Following SQL request :
SELECT indid, name, groupid FROM sysindexes
WHERE id = OBJECT_ID('STATJOB') and name not like '_WA%'

returns 2 rows :
0 STATJOB 1
2 STATJOB_PK 1

I have this problem with other tables.

Ok so looks like there are 2 indexes. Please post the code for those indexes. Also you may want to look at your profile and see if any other queries were attempting to access the STATJOB table during that time. Be sure to use (NOLOCK) when doing selects. Also, was this a one time problem or is it repeatable?

Daniel
SQL Server DBA
www.dallasteam.com

jeanyves72
Starting Member

4 Posts

Posted - 2005-08-22 : 11:21:59

There is only one index in table STATJOB : STATJOB.
It seems that SQL request
SELECT indid, name, groupid FROM sysindexes WHERE id = OBJECT_ID('STATJOB') and name not like '_WA%'
returns 1 row for the table name.
I am going to check if there are others SQL queries which may lock the table STATJOB.

The problem occurs several times.

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-22 : 11:28:41

Have you been monitoring blocking? Be weary of NOLOCK if you require accurate results.

====================================================
Regards,
Sean Roussy

Thank you, drive through

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources