Performance issue ,PageIOlatch

Question

Performance issue ,PageIOlatch

aslamnepanagar

SSC-Addicted

Points: 488
More actions
February 4, 2011 at 4:33 am

#234567

Infra
Multi Instance Server -4 instances
8 CPU
16 GB memory—around 9 GB is always available
Disk Structure:-
C Drive:- OS files
D Drive: - All MDF files
E Drive: - All LDF files plus Tempdb LDF file
H: - Tempdb MDF file
OS
Microsoft(R) Windows(R) Server 2003, Enterprise Edition
Version5.2.3790 Service Pack 2 Build 3790
Sql Server
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Problem:-
Performance issue encountered in one instance only. Website operating on a 42 GB database on this instance becomes slow at times. Pageiolatch observed in activity monitor.
Not a daily problem but frequent one. Frequency twice/month.
Workaround:-
Rebuild a composite index on a heavily uses table and statistics update.
It takes 2 mins to complete the above rebuild but it resolves the issue.
Notes:- I don’t know ,who/why scheduled this job to rebuild this composite non cluster index.
There are around 196 statistics and index created on this table.
namerowsreserveddataindex sizeunused
JOBS12024674500328 KB1232832 KB3254976 KB12520 KB
We have checked for below using performance counters
a) CPU bottleneck
b) Memory bottleneck
c) IO bottleneck
Normally the disk system is working fine and within the threshold limits but this threshold is crossed and bottleneck appears in the Disk system at the time of issue.
Can anyone guide me where to look for the cause and resolutions? Let me know if any other info is required.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

It sounds like you have the issue isolated to a particular table. What queries are running against the table. I'd focus there. Most of the time, problems exist either in the database design, the indexes, the statistics, or the code. I'd say the majority of that time, it's in the code.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

aslamnepanagar SSC-Addicted Points: 488 More actions · Answer 2

Thanks Scary DBA.

I too suspect the Table.It is the max used table. Hence the earlier team has overloaded it with lots of indexes and statistics.

The surprise here is building the NC index immediately resolves the probelm. Many time we face this isssue in Production day time and we have to do this.I understand,this is not acceptable for anyone.

I have captured the execution plans for some frequently used queries and statistics for this NC index too(before and after the issue). I did not find much help there.

Also we have this task(NC index build) scheduled twice in a day, one being immedately at the start of the business day.I traced the sessions also at the time of issue.

Conclution can be drawn as after running the jobs , new stats are created,new execution plans are created as per the stats and this resolves the issue.

but I am not able to find what is causing the stats to deteriorate so fast ?

Is there any other possibiity of the issue?

Any help will be appreciated.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

How big is the table?

What's it's definition? (CREATE TABLE statement)

What are the queries that are affected?

Do you really need to rebuild the index? Does update statistics with full scan have the same effect? Does sp_recompile <table name> have the same effect?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aslamnepanagar SSC-Addicted Points: 488 More actions · Answer 4

Thanks Gail.

Please find the information.

namerowsreserveddataindex_sizeunused

JOBS1207018 4301464 KB1235384 KB3042224 KB23856 KB

The tables has 1207018 rows and Using SP_SPACEUSEd , I found the size as above.

Definition:-

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[JOBS](

[JOBREF] [int] NOT NULL,

[CONTRACT] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[PROPERTYREF] [int] NOT NULL,

[CREATEDATETIME] [datetime] NOT NULL CONSTRAINT [DF_JOBS_CREATEDATETIME] DEFAULT (getdate()),

[TAKENOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TYPEOFJOB] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[GIVENBY] [int] NULL,

[REQUIRED] [datetime] NULL,

[COMPLETED] [datetime] NULL,

[COMPLETEDOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[COMPLETEDBY] [int] NULL,

[CANCELLED] [datetime] NULL,

[CANCELLEDREASON] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CANCELLEDOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CANCELLEDBY] [int] NULL,

[PASSEDOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[PASSEDDATE] [datetime] NULL,

[PASSEDTO] [int] NULL,

[CLOSED] [datetime] NULL,

[CLOSEDOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CLOSINGJOBREF] [int] NULL,

[KEYSCOLLECTED] [datetime] NULL,

[KEYSCOLLECTEDOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[KEYSCOLLECTEDFROM] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[KEYCOLLECTEDBY] [int] NULL,

[KEYSRETURNED] [datetime] NULL,

[KEYSRETURNEDOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[KEYSRETURNEDTO] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[KEYSRETURNEDBY] [int] NULL,

[OPERATIONALNOTES] [nvarchar](1044) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CALLREF] [int] NULL,

[CALLHOW] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[COLLECTKEYSFROM] [nvarchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[RETURNKEYSTO] [nvarchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ABORTEDDATE] [datetime] NULL,

[ABORTEDOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ABORTEDBY] [int] NULL,

[CANCELABORTREASONS] [nvarchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[REGION] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DEPOT] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SERVICELEVEL] [int] NULL,

[ANALYSIS] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CUSTPHONE] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CUSTFAX] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CUSTMAIL] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OFFICEREF] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TAKENDATETIME] [datetime] NULL,

[TAKENBY] [int] NULL,

[CUSTSMS] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OPERATIONALCONTACT] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CUSTOMERREF] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[JOBSTATUS] [int] NULL,

[CONFIRMED] [datetime] NULL,

[CONFIRMEDOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CONFIRMEDBY] [int] NULL,

[ORDERNO] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[UPDATED] [datetime] NULL,

[UPDATEDOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DEFAULTSERVICELEVEL] [int] NULL,

[ABORTEDJOBCHARGE] [float] NULL,

[QUOTEDPRICE] [float] NULL,

[QUOTEDRATE] [float] NULL,

[QUOTEDRATEPERIOD] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[QUOTEDNARRATIVE] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[QUOTEDRATENARRATIVE] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ORDERNOVALIDTO] [datetime] NULL,

[ORDERNOVALIDFROM] [datetime] NULL,

[ORIGINSTALLREF] [int] NULL,

[FINALREMOVALREF] [int] NULL,

[INVOICESTART] [datetime] NULL,

[INVOICEEND] [datetime] NULL,

[COMPLETEDDATE] [datetime] NULL,

[ABORTED] [datetime] NULL,

[CANCELLEDDATE] [datetime] NULL,

[INVOICESTARTPERIOD] [int] NULL,

[INVOICEPERIOD] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ISMIN] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[REPORT] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[NOTIONALWEEKLYRENT] [float] NULL,

[REQUIREDDATE] [datetime] NULL,

[AM_COMPLETED] [datetime] NULL,

[PASSEDOUT_TO] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CONFIDENTIAL] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[COMPLETEABORTPERSON] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[PONUMBER] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SUBCONTRACTED] [int] NULL,

[SUBCONTRACTCOST] [float] NULL,

[SUBCONTRACTCENTRE] [int] NULL,

[INSPECTDEPOT] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[INSPECTCENTRE] [int] NULL,

[SUBCONTRACTNOTES] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[INSPECTCONFIRMED] [datetime] NULL,

[INSPECTCONFIRMEDBY] [int] NULL,

[VPSDOORCODE] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[JOBMINHIREPERIOD] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[JOBDISCOUNT] [float] NULL,

[MANHOURS] [float] NULL,

[REMOVALREASON] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CARRYFWDREASON] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ROLLOVERREASON] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[STARTTIME] [datetime] NULL,

[SCHEDULEDTIME] [datetime] NULL,

[SCHEDULEDTIMEOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TEAMALLOCATED] [int] NULL,

[TEAMALLOCATEDTIME] [datetime] NULL,

[TEAMALLOCATEDOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ONHOLDTO] [datetime] NULL,

[ONHOLDTOOP] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[JOBSTATUSDATE] [datetime] NULL,

[REMOVALDATE] [datetime] NULL,

[SOURCEREF] [int] NULL,

[COMPLETED_WHEN] [datetime] NULL,

CONSTRAINT [PK_JOBS] PRIMARY KEY CLUSTERED

(

[JOBREF] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object: Statistic [_dta_stat_542624976_1_51_68_72_69_3] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [_dta_stat_542624976_1_51_68_72_69_3] ON [dbo].[JOBS]([JOBREF], [CUSTOMERREF], [ORIGINSTALLREF], [COMPLETEDDATE], [FINALREMOVALREF], [PROPERTYREF])

GO

/****** Object: Statistic [_dta_stat_542624976_1_80] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [_dta_stat_542624976_1_80] ON [dbo].[JOBS]([JOBREF], [REQUIREDDATE])

GO

/****** Object: Statistic [_dta_stat_542624976_2_6_52_3] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [_dta_stat_542624976_2_6_52_3] ON [dbo].[JOBS]([CONTRACT], [TYPEOFJOB], [JOBSTATUS], [PROPERTYREF])

GO

/****** Object: Statistic [_dta_stat_542624976_51_1_68_72_69_3] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [_dta_stat_542624976_51_1_68_72_69_3] ON [dbo].[JOBS]([CUSTOMERREF], [JOBREF], [ORIGINSTALLREF], [COMPLETEDDATE], [FINALREMOVALREF], [PROPERTYREF])

GO

/****** Object: Statistic [_dta_stat_542624976_80_52_1] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [_dta_stat_542624976_80_52_1] ON [dbo].[JOBS]([REQUIREDDATE], [JOBSTATUS], [JOBREF])

GO

/****** Object: Statistic [JOBS_REF_STATISTICS] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_REF_STATISTICS] ON [dbo].[JOBS]([JOBREF], [PROPERTYREF], [CUSTOMERREF], [OFFICEREF])

GO

/****** Object: Statistic [JOBS_STAT_CANCELABORTREASONS_FINALREMOVALREF_JOBREF_TYPEOFJOB] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_CANCELABORTREASONS_FINALREMOVALREF_JOBREF_TYPEOFJOB] ON [dbo].[JOBS]([CANCELABORTREASONS], [FINALREMOVALREF], [JOBREF], [TYPEOFJOB])

GO

/****** Object: Statistic [JOBS_STAT_CONTRACT_TYPEOFJOB_CUSTOMERREF_JOBREF_SERVICELEVEL_REQUIRED] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_CONTRACT_TYPEOFJOB_CUSTOMERREF_JOBREF_SERVICELEVEL_REQUIRED] ON [dbo].[JOBS]([CONTRACT], [TYPEOFJOB], [CUSTOMERREF], [JOBREF], [SERVICELEVEL], [REQUIRED])

GO

/****** Object: Statistic [JOBS_STAT_CONTRACT_TYPEOFJOB_JOBREF_JOBSTATUS] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_CONTRACT_TYPEOFJOB_JOBREF_JOBSTATUS] ON [dbo].[JOBS]([CONTRACT], [TYPEOFJOB], [JOBREF], [JOBSTATUS])

GO

/****** Object: Statistic [JOBS_STAT_CUSTOMERREF_CONTRACT] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_CUSTOMERREF_CONTRACT] ON [dbo].[JOBS]([CUSTOMERREF], [CONTRACT])

GO

/****** Object: Statistic [JOBS_STAT_CUSTOMERREF_JOBREF_CONTRACT] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_CUSTOMERREF_JOBREF_CONTRACT] ON [dbo].[JOBS]([CUSTOMERREF], [JOBREF], [CONTRACT])

GO

/****** Object: Statistic [JOBS_STAT_JOBREF_CONTRACT_TYPEOFJOB_CUSTOMERREF_PROPERTYREF] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_JOBREF_CONTRACT_TYPEOFJOB_CUSTOMERREF_PROPERTYREF] ON [dbo].[JOBS]([JOBREF], [CONTRACT], [TYPEOFJOB], [CUSTOMERREF], [PROPERTYREF])

GO

/****** Object: Statistic [JOBS_STAT_JOBREF_JOBSTATUS_CUSTOMERREF_CONTRACT_TYPEOFJOB] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_JOBREF_JOBSTATUS_CUSTOMERREF_CONTRACT_TYPEOFJOB] ON [dbo].[JOBS]([JOBREF], [JOBSTATUS], [CUSTOMERREF], [CONTRACT], [TYPEOFJOB])

GO

/****** Object: Statistic [JOBS_STAT_JOBREF_SERVICELEVEL_CONTRACT_TYPEOFJOB_CUSTOMERREF_PROPERTYREF] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_JOBREF_SERVICELEVEL_CONTRACT_TYPEOFJOB_CUSTOMERREF_PROPERTYREF] ON [dbo].[JOBS]([JOBREF], [SERVICELEVEL], [CONTRACT], [TYPEOFJOB], [CUSTOMERREF], [PROPERTYREF])

GO

/****** Object: Statistic [JOBS_STAT_JOBREF_SERVICELEVEL_JOBSTATUS_CUSTOMERREF_CONTRACT_TYPEOFJOB] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_JOBREF_SERVICELEVEL_JOBSTATUS_CUSTOMERREF_CONTRACT_TYPEOFJOB] ON [dbo].[JOBS]([JOBREF], [SERVICELEVEL], [JOBSTATUS], [CUSTOMERREF], [CONTRACT], [TYPEOFJOB])

GO

/****** Object: Statistic [JOBS_STAT_JOBSTATUS_CONTRACT_TYPEOFJOB_CUSTOMERREF_PROPERTYREF] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_JOBSTATUS_CONTRACT_TYPEOFJOB_CUSTOMERREF_PROPERTYREF] ON [dbo].[JOBS]([JOBSTATUS], [CONTRACT], [TYPEOFJOB], [CUSTOMERREF], [PROPERTYREF])

GO

/****** Object: Statistic [JOBS_STAT_PROPERTYREF_CONTRACT_TYPEOFJOB_CUSTOMERREF] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_PROPERTYREF_CONTRACT_TYPEOFJOB_CUSTOMERREF] ON [dbo].[JOBS]([PROPERTYREF], [CONTRACT], [TYPEOFJOB], [CUSTOMERREF])

GO

/****** Object: Statistic [JOBS_STAT_PROPERTYREF_CUSTOMERREF] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_PROPERTYREF_CUSTOMERREF] ON [dbo].[JOBS]([PROPERTYREF], [CUSTOMERREF])

GO

/****** Object: Statistic [JOBS_STAT_PROPERTYREF_JOBREF_CONTRACT_TYPEOFJOB] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_PROPERTYREF_JOBREF_CONTRACT_TYPEOFJOB] ON [dbo].[JOBS]([PROPERTYREF], [JOBREF], [CONTRACT], [TYPEOFJOB])

GO

/****** Object: Statistic [JOBS_STAT_PROPERTYREF_JOBREF_JOBSTATUS_CUSTOMERREF_CONTRACT_TYPEOFJOB_SERVICELEVEL] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_PROPERTYREF_JOBREF_JOBSTATUS_CUSTOMERREF_CONTRACT_TYPEOFJOB_SERVICELEVEL] ON [dbo].[JOBS]([PROPERTYREF], [JOBREF], [JOBSTATUS], [CUSTOMERREF], [CONTRACT], [TYPEOFJOB], [SERVICELEVEL])

GO

/****** Object: Statistic [JOBS_STAT_PROPERTYREF_REQUIRED_JOBREF_SERVICELEVEL_CONTRACT_TYPEOFJOB] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_PROPERTYREF_REQUIRED_JOBREF_SERVICELEVEL_CONTRACT_TYPEOFJOB] ON [dbo].[JOBS]([PROPERTYREF], [REQUIRED], [JOBREF], [SERVICELEVEL], [CONTRACT], [TYPEOFJOB])

GO

/****** Object: Statistic [JOBS_STAT_REQUIRED_JOBREF_SERVICELEVEL_CONTRACT_TYPEOFJOB] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_REQUIRED_JOBREF_SERVICELEVEL_CONTRACT_TYPEOFJOB] ON [dbo].[JOBS]([REQUIRED], [JOBREF], [SERVICELEVEL], [CONTRACT], [TYPEOFJOB])

GO

/****** Object: Statistic [JOBS_STAT_SERVICELEVEL_CONTRACT_TYPEOFJOB_CUSTOMERREF_JOBSTATUS_PROPERTYREF_REQUIRED_JOBREF] Script Date: 02/07/2011 13:32:04 ******/

CREATE STATISTICS [JOBS_STAT_SERVICELEVEL_CONTRACT_TYPEOFJOB_CUSTOMERREF_JOBSTATUS_PROPERTYREF_REQUIRED_JOBREF] ON [dbo].[JOBS]([SERVICELEVEL], [CONTRACT], [TYPEOFJOB], [CUSTOMERREF], [JOBSTATUS], [PROPERTYREF], [REQUIRED], [JOBREF])

Affected queried.

Queries are large.

Some details of the queries

Using Temp objects,Table variables , cursors, 2-3 levels of nesting. Finally taking data from Jobs table.

Do you really need to rebuild the index?

No. The table is not getting fargmented fast . But this step is the only quick workaround to resolve the issue.

The table is getting fragmented 1-2% in a week .

Does update statistics with full scan have the same effect?

We are running SP_UPDATESTATS daily along with the reindex job.

Details of the job.

ALTER INDEX [IX_JOBS_MULTIPLE_2] ON [dbo].[JOBS]

REBUILD;

GO

EXEC SP_UPDATESTATS

GO

Index Detail:-

IX_JOBS_MULTIPLE_2 nonclustered located on PRIMARY JOBSTATUS, CONTRACT, TYPEOFJOB, CUSTOMERREF, PROPERTYREF, SERVICELEVEL, REQUIRED, JOBREF

Does sp_recompile <table name> have the same effect?

Never tried it. Will try and update you.

Could you suggest if any other data need to be collected for analysis?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

aslamnepanagar (2/7/2011)
Affected queried.
Queries are large.
Some details of the queries
Using Temp objects,Table variables , cursors, 2-3 levels of nesting. Finally taking data from Jobs table.

I'd suggest optimising those. They don't sound at all efficient.

Honestly, looking at the large number of user-created statistics and the DTA created statisics (and lack of indexes, unless you just didn't post those), I would seriously consider getting a performance tuning specialist in to look at this. It may be more that we can manage on a forum thread.

I was actually asking if the specific queries (not procedures) that show problems had anything in common w.r.t. what columns they filter on.

Do you know what individual queries are affected? To fix this kind of problem, you have to narrow down the scope to just what's causing the problem.

Does update statistics with full scan have the same effect?
We are running SP_UPDATESTATS daily along with the reindex job.

sp_updatestats does not do the same thing as update statistics with full scan. It does samples updates unless you tell it not to, it won't update stats if they're not out of date.

You said that rebuilding the index during the day fixes the problem. What I'm asking is whether just updating the statistics (without rebuilding the index) also fixes it.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aslamnepanagar SSC-Addicted Points: 488 More actions · Answer 6

Affected queried.

Queries are large.

Some details of the queries

Using Temp objects,Table variables , cursors, 2-3 levels of nesting. Finally taking data from Jobs table.

I'd suggest optimising those. They don't sound at all efficient.

Agreed.

Honestly, looking at the large number of user-created statistics and the DTA created statisics (and lack of indexes, unless you just didn't post those), I would seriously consider getting a performance tuning specialist in to look at this. It may be more that we can manage on a forum thread.

Yes there are many indexes too. Create statement does not captured them but using sp_helpindex I can see 118 indexes.

index_nameindex_descriptionindex_keys

_dta_index_JOBS_10_542624976__K1nonclustered, hypothetical located on PRIMARYJOBREF

_dta_index_JOBS_10_542624976__K1_9987nonclustered, hypothetical located on PRIMARYJOBREF

_dta_index_JOBS_10_542624976__K1_K51nonclustered, hypothetical located on PRIMARYJOBREF, CUSTOMERREF

_dta_index_JOBS_10_542624976__K1_K51_K68_K72_K69_K3nonclustered, hypothetical located on PRIMARYJOBREF, CUSTOMERREF, ORIGINSTALLREF, COMPLETEDDATE, FINALREMOVALREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K1_K52nonclustered, hypothetical located on PRIMARYJOBREF, JOBSTATUS

_dta_index_JOBS_10_542624976__K1_K68_K51_K72_K69_K3nonclustered, hypothetical located on PRIMARYJOBREF, ORIGINSTALLREF, CUSTOMERREF, COMPLETEDDATE, FINALREMOVALREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K1_K68_K69_K51_K72_K3nonclustered, hypothetical located on PRIMARYJOBREF, ORIGINSTALLREF, FINALREMOVALREF, CUSTOMERREF, COMPLETEDDATE, PROPERTYREF

_dta_index_JOBS_10_542624976__K1_K68_K69_K72_K3nonclustered, hypothetical located on PRIMARYJOBREF, ORIGINSTALLREF, FINALREMOVALREF, COMPLETEDDATE, PROPERTYREF

_dta_index_JOBS_10_542624976__K1_K68_K72_K69_K3nonclustered, hypothetical located on PRIMARYJOBREF, ORIGINSTALLREF, COMPLETEDDATE, FINALREMOVALREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K1_K72nonclustered, hypothetical located on PRIMARYJOBREF, COMPLETEDDATE

_dta_index_JOBS_10_542624976__K1_K72_K51_K68_K69_K3nonclustered, hypothetical located on PRIMARYJOBREF, COMPLETEDDATE, CUSTOMERREF, ORIGINSTALLREF, FINALREMOVALREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K1_K80nonclustered, hypothetical located on PRIMARYJOBREF, REQUIREDDATE

_dta_index_JOBS_10_542624976__K1_K80_K52nonclustered, hypothetical located on PRIMARYJOBREF, REQUIREDDATE, JOBSTATUS

_dta_index_JOBS_10_542624976__K3_K51_K72_1_68_69nonclustered, hypothetical located on PRIMARYPROPERTYREF, CUSTOMERREF, COMPLETEDDATE

_dta_index_JOBS_10_542624976__K3_K69_K72_1_68nonclustered, hypothetical located on PRIMARYPROPERTYREF, FINALREMOVALREF, COMPLETEDDATE

_dta_index_JOBS_10_542624976__K3_K69_K72_K1_K68nonclustered, hypothetical located on PRIMARYPROPERTYREF, FINALREMOVALREF, COMPLETEDDATE, JOBREF, ORIGINSTALLREF

_dta_index_JOBS_10_542624976__K3_K69_K72_K1_K68_K51nonclustered, hypothetical located on PRIMARYPROPERTYREF, FINALREMOVALREF, COMPLETEDDATE, JOBREF, ORIGINSTALLREF, CUSTOMERREF

_dta_index_JOBS_10_542624976__K3_K72_1_68_69nonclustered, hypothetical located on PRIMARYPROPERTYREF, COMPLETEDDATE

_dta_index_JOBS_10_542624976__K3_K72_K69_K1_K68nonclustered, hypothetical located on PRIMARYPROPERTYREF, COMPLETEDDATE, FINALREMOVALREF, JOBREF, ORIGINSTALLREF

_dta_index_JOBS_10_542624976__K3_K72_K69_K1_K68_K51nonclustered, hypothetical located on PRIMARYPROPERTYREF, COMPLETEDDATE, FINALREMOVALREF, JOBREF, ORIGINSTALLREF, CUSTOMERREF

_dta_index_JOBS_10_542624976__K3_K72_K69_K51_1_68nonclustered, hypothetical located on PRIMARYPROPERTYREF, COMPLETEDDATE, FINALREMOVALREF, CUSTOMERREF

_dta_index_JOBS_10_542624976__K51_1nonclustered, hypothetical located on PRIMARYCUSTOMERREF

_dta_index_JOBS_10_542624976__K51_K1nonclustered, hypothetical located on PRIMARYCUSTOMERREF, JOBREF

_dta_index_JOBS_10_542624976__K51_K1_K68_K72_K69_K3nonclustered, hypothetical located on PRIMARYCUSTOMERREF, JOBREF, ORIGINSTALLREF, COMPLETEDDATE, FINALREMOVALREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K51_K72_K1_K68_K69_K3nonclustered, hypothetical located on PRIMARYCUSTOMERREF, COMPLETEDDATE, JOBREF, ORIGINSTALLREF, FINALREMOVALREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K51_K72_K69_K1_K68_K3nonclustered, hypothetical located on PRIMARYCUSTOMERREF, COMPLETEDDATE, FINALREMOVALREF, JOBREF, ORIGINSTALLREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K52_K1nonclustered, hypothetical located on PRIMARYJOBSTATUS, JOBREF

_dta_index_JOBS_10_542624976__K68_K69_K72_K1nonclustered, hypothetical located on PRIMARYORIGINSTALLREF, FINALREMOVALREF, COMPLETEDDATE, JOBREF

_dta_index_JOBS_10_542624976__K68_K69_K72_K1_K51_K3nonclustered, hypothetical located on PRIMARYORIGINSTALLREF, FINALREMOVALREF, COMPLETEDDATE, JOBREF, CUSTOMERREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K68_K72_K69_K1nonclustered, hypothetical located on PRIMARYORIGINSTALLREF, COMPLETEDDATE, FINALREMOVALREF, JOBREF

_dta_index_JOBS_10_542624976__K68_K72_K69_K1_K51_K3nonclustered, hypothetical located on PRIMARYORIGINSTALLREF, COMPLETEDDATE, FINALREMOVALREF, JOBREF, CUSTOMERREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K69_K51_K1_K68_K72_K3nonclustered, hypothetical located on PRIMARYFINALREMOVALREF, CUSTOMERREF, JOBREF, ORIGINSTALLREF, COMPLETEDDATE, PROPERTYREF

_dta_index_JOBS_10_542624976__K69_K72_K1_K68nonclustered, hypothetical located on PRIMARYFINALREMOVALREF, COMPLETEDDATE, JOBREF, ORIGINSTALLREF

_dta_index_JOBS_10_542624976__K69_K72_K1_K68_K3nonclustered, hypothetical located on PRIMARYFINALREMOVALREF, COMPLETEDDATE, JOBREF, ORIGINSTALLREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K69_K72_K1_K68_K3_K51nonclustered, hypothetical located on PRIMARYFINALREMOVALREF, COMPLETEDDATE, JOBREF, ORIGINSTALLREF, PROPERTYREF, CUSTOMERREF

_dta_index_JOBS_10_542624976__K72_1nonclustered, hypothetical located on PRIMARYCOMPLETEDDATE

_dta_index_JOBS_10_542624976__K72_K1nonclustered, hypothetical located on PRIMARYCOMPLETEDDATE, JOBREF

_dta_index_JOBS_10_542624976__K72_K1_K51_K68_K69_K3nonclustered, hypothetical located on PRIMARYCOMPLETEDDATE, JOBREF, CUSTOMERREF, ORIGINSTALLREF, FINALREMOVALREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K72_K1_K68_K69_K3nonclustered, hypothetical located on PRIMARYCOMPLETEDDATE, JOBREF, ORIGINSTALLREF, FINALREMOVALREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K72_K51_K1_K68_K69_K3nonclustered, hypothetical located on PRIMARYCOMPLETEDDATE, CUSTOMERREF, JOBREF, ORIGINSTALLREF, FINALREMOVALREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K72_K69_K1_K68nonclustered, hypothetical located on PRIMARYCOMPLETEDDATE, FINALREMOVALREF, JOBREF, ORIGINSTALLREF

_dta_index_JOBS_10_542624976__K72_K69_K1_K68_K3nonclustered, hypothetical located on PRIMARYCOMPLETEDDATE, FINALREMOVALREF, JOBREF, ORIGINSTALLREF, PROPERTYREF

_dta_index_JOBS_10_542624976__K72_K69_K1_K68_K3_K51nonclustered, hypothetical located on PRIMARYCOMPLETEDDATE, FINALREMOVALREF, JOBREF, ORIGINSTALLREF, PROPERTYREF, CUSTOMERREF

_dta_index_JOBS_10_542624976__K80_K1nonclustered, hypothetical located on PRIMARYREQUIREDDATE, JOBREF

_dta_index_JOBS_10_542624976__K80_K1_K52nonclustered, hypothetical located on PRIMARYREQUIREDDATE, JOBREF, JOBSTATUS

_dta_index_JOBS_10_542624976__K80_K52_K1nonclustered, hypothetical located on PRIMARYREQUIREDDATE, JOBSTATUS, JOBREF

_dta_index_JOBS_11_542624976__K3_K52_K2_K6_1_8_9_40_42_46_53nonclustered located on PRIMARYPROPERTYREF, JOBSTATUS, CONTRACT, TYPEOFJOB

IX_ABORTEDBYnonclustered located on PRIMARYABORTEDBY

IX_cancelledbynonclustered located on PRIMARYCANCELLEDBY

IX_completedbynonclustered located on PRIMARYCOMPLETEDBY

IX_CONFIRMEDBYnonclustered located on PRIMARYCONFIRMEDBY

IX_depotnonclustered located on PRIMARYDEPOT

IX_givenbynonclustered located on PRIMARYGIVENBY

IX_INSPECTCENTREnonclustered located on PRIMARYINSPECTCENTRE

IX_INSPECTCONFIRMEDBYnonclustered located on PRIMARYINSPECTCONFIRMEDBY

IX_JOBS_MULTIPLE_1nonclustered located on PRIMARYTAKENOP, TAKENDATETIME, SERVICELEVEL, CUSTOMERREF

IX_JOBS_MULTIPLE_2nonclustered located on PRIMARYJOBSTATUS, CONTRACT, TYPEOFJOB, CUSTOMERREF, PROPERTYREF, SERVICELEVEL, REQUIRED, JOBREF

IX_JOBS_MULTIPLE_3nonclustered located on PRIMARYPROPERTYREF, CUSTOMERREF, JOBREF

IX_jobstatusnonclustered located on PRIMARYJOBSTATUS

IX_keycollectedbynonclustered located on PRIMARYKEYCOLLECTEDBY

IX_keysreturnedbynonclustered located on PRIMARYKEYSRETURNEDBY

IX_passeddatenonclustered located on PRIMARYPASSEDDATE

IX_passedtononclustered located on PRIMARYPASSEDTO

IX_SUBCONTRACTCENTREnonclustered located on PRIMARYSUBCONTRACTCENTRE

IX_takenbynonclustered located on PRIMARYTAKENBY

IX_typeofjobnonclustered located on PRIMARYTYPEOFJOB

JOBS_ABORTEDDATEnonclustered located on PRIMARYABORTEDDATE

JOBS_ABORTEDOPnonclustered located on PRIMARYABORTEDOP

JOBS_AM_COMPLETEDnonclustered located on PRIMARYAM_COMPLETED

JOBS_ANALYSISnonclustered located on PRIMARYANALYSIS

JOBS_CALLREFnonclustered located on PRIMARYCALLREF, JOBREF

JOBS_CANCELLEDnonclustered located on PRIMARYCANCELLED

JOBS_CANCELLEDBYnonclustered located on PRIMARYCANCELLEDBY

JOBS_CANCELLEDOPnonclustered located on PRIMARYCANCELLEDOP

JOBS_CLOSINGJOBREFnonclustered located on PRIMARYCLOSINGJOBREF

JOBS_COMPLETEDnonclustered located on PRIMARYCOMPLETED

JOBS_COMPLETEDBYnonclustered located on PRIMARYCOMPLETEDBY

JOBS_COMPLETEDDATEnonclustered located on PRIMARYCOMPLETEDDATE

JOBS_COMPLETEDOPnonclustered located on PRIMARYCOMPLETEDOP

jobs_completedwhennonclustered located on PRIMARYCOMPLETED_WHEN

JOBS_CONFRIMEDnonclustered located on PRIMARYCONFIRMED

JOBS_CONTRACTnonclustered located on PRIMARYCONTRACT

JOBS_CREATEDATETIMEnonclustered located on PRIMARYCREATEDATETIME

JOBS_CUSTOMERREFnonclustered located on PRIMARYCUSTOMERREF

JOBS_DEPOTnonclustered located on PRIMARYDEPOT

JOBS_FINALREMOVALREFnonclustered located on PRIMARYFINALREMOVALREF

JOBS_GIVENBYnonclustered located on PRIMARYGIVENBY

JOBS_INSPECTCENTREnonclustered located on PRIMARYINSPECTCENTRE

JOBS_INSPECTDEPOTnonclustered located on PRIMARYINSPECTDEPOT

JOBS_INVOICESTARTnonclustered located on PRIMARYINVOICESTART

JOBS_INVOICESTARTPERIODnonclustered located on PRIMARYINVOICESTARTPERIOD

JOBS_JOBSTATUSnonclustered located on PRIMARYJOBSTATUS

JOBS_JOBSTATUSDATEnonclustered located on PRIMARYJOBSTATUSDATE

JOBS_KEYCOLLECTEDBYnonclustered located on PRIMARYKEYCOLLECTEDBY

JOBS_KEYSRETURNEDBYnonclustered located on PRIMARYKEYSRETURNEDBY

JOBS_OFFICEREFnonclustered located on PRIMARYOFFICEREF

JOBS_ONHOLDTOnonclustered located on PRIMARYONHOLDTO

JOBS_ORDERNOnonclustered located on PRIMARYORDERNO

JOBS_ORIGINSTALLREFnonclustered located on PRIMARYORIGINSTALLREF

JOBS_PASSEDDATEnonclustered located on PRIMARYPASSEDDATE

JOBS_PASSEDTOnonclustered located on PRIMARYPASSEDTO

JOBS_PONUMBERnonclustered located on PRIMARYPONUMBER

JOBS_PROPERTYREFnonclustered located on PRIMARYPROPERTYREF

JOBS_QUOTEDPRICEnonclustered located on PRIMARYQUOTEDPRICE

JOBS_QUOTEDRATEnonclustered located on PRIMARYQUOTEDRATE

JOBS_REGIONnonclustered located on PRIMARYREGION

JOBS_REMOVALDATEnonclustered located on PRIMARYREMOVALDATE

JOBS_REQUIREDnonclustered located on PRIMARYREQUIRED

JOBS_REQUIREDDATEnonclustered located on PRIMARYREQUIREDDATE

JOBS_SCHEDULEDTIMEnonclustered located on PRIMARYSCHEDULEDTIME

JOBS_STARTTIMEnonclustered located on PRIMARYSTARTTIME

JOBS_TAKENBYnonclustered located on PRIMARYTAKENBY

JOBS_TAKENDATETIMEnonclustered located on PRIMARYTAKENDATETIME

JOBS_TAKENOPnonclustered located on PRIMARYTAKENOP

JOBS_TEAMALLOCATEDTIMEnonclustered located on PRIMARYTEAMALLOCATEDTIME

JOBS_TYPEOFJOBnonclustered located on PRIMARYTYPEOFJOB

NON_CL_IDX_JOBS_FINALJOBREF_TYPEJOB_CANABRESnonclustered located on PRIMARYFINALREMOVALREF, JOBREF, TYPEOFJOB, CANCELABORTREASONS

PK_JOBSclustered, unique, primary key located on PRIMARYJOBREF

I was actually asking if the specific queries (not procedures) that show problems had anything in common w.r.t. what columns they filter on.

Do you know what individual queries are affected? To fix this kind of problem, you have to narrow down the scope to just what's causing the problem.

There are procedures and functions which are observed as showing PageIOlatch.I guess the code is written in such way.Table variables,cursor and nesting is the common feature I came across in many queries.

There are a couple of queries related to this pageIOlatch . I am in the process of analysis them.

Does update statistics with full scan have the same effect?

I will check this.

sp_updatestats does not do the same thing as update statistics with full scan. It does samples updates unless you tell it not to, it won't update stats if they're not out of date.

You said that rebuilding the index during the day fixes the problem. What I'm asking is whether just updating the statistics (without rebuilding the index) also fixes it.

Yes I have tried this.

a)Only running rebuild index resolves the issue.

b)Only running the sp_updatestats does not help.

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 7

just my 2 ct:

Can you check if auto update statistics have been kicked off at the time you experience your issues ?

You have quit a number of indexes and stats, so it has much to maintain.

Of course, optimizing your queries will still be needed.

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

aslamnepanagar SSC-Addicted Points: 488 More actions · Answer 8

Thanks All.

One more point to add.

Even after restarting the Server/Services does not help. We have to execute this job after that.

Most of the day it is working fine say 28 days out of a month ,it is okay.

I also checked for any patterns related to this issue.

It is happining on random days(Sometime Monday, sometime friday etc)

It is happining on random time.

The insert/update(No delete) on the table ,is also not helping in finding any pattern.

Fill factor=100 , Pad index =off.

Normal days:

Disk --okay

CPU--okay

Memory --okay.

Same queries working fine.

Blocking --none found at the time of issue .

(Please note the we have tried rebooting the server also)

So what is there in this job which is resolving the issue.

Probably two things

a) New execution plans are created after rebuild.(If yes , I have to found what is causing the deterioration of the stats/execution plan)

b) The job is forcing some data into cache eliminating the use of disk IO.

Let me know if you agree with this.

Once again , thanks all for you kind help.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Oh look, another entity out there who threw a workload (or multiple workloads) at the Database Tuning Advisor and accepted everything!! Outside of using Scalar UDFs that is just about the worst thing you can do in a SQL Server database (even worse than using cursors). I have spent HUNDREDS of manhours helping clients clean up their use of DTA. It will crush your concurrency, IO, index maintenance, etc, etc. I have seen systems become literally UNUSABLE due to DTA usage.

As someone else mentioned - you need a professional tuning expert and you need one ASAP.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

aslamnepanagar SSC-Addicted Points: 488 More actions · Answer 10

Hi All,

I have also observed the 4 instances(multi instance server) , sqlserver.exe using around 1.7 GB memory.

They are never crossing 2 GB.

As mentioned above we are using a 32 bit OS with 32 bit Sql server.

Are we facing memory issues which is resulting in disk issues like PageIOlatches.

We have captured counters related to memory like available memory ,pages/sec but they are not pointing to it.

I am planning to add some more counters related to memory like pagelige expectency..

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 11

aslamnepanagar (2/8/2011)
Hi All,
I have also observed the 4 instances(multi instance server) , sqlserver.exe using around 1.7 GB memory.
They are never crossing 2 GB.
As mentioned above we are using a 32 bit OS with 32 bit Sql server.
Are we facing memory issues which is resulting in disk issues like PageIOlatches.
We have captured counters related to memory like available memory ,pages/sec but they are not pointing to it.
I am planning to add some more counters related to memory like pagelige expectency..

IMO your PLE will be low ....

Multi Instance Server -4 instances

8 CPU

16 GB memory—around 9 GB is always available

Disk Structure:-

C Drive:- OS files

D Drive: - All MDF files

E Drive: - All LDF files plus Tempdb LDF file

H: - Tempdb MDF file

OS

Microsoft(R) Windows(R) Server 2003, Enterprise Edition

Version 5.2.3790 Service Pack 2 Build 3790

Sql Server

Problem:-

Performance issue encountered in one instance only. Website operating on a 42 GB database on this instance becomes slow at times. Pageiolatch observed in activity monitor.

Not a daily problem but frequent one. Frequency twice/month.

By default your instances will only consume 1.7GB, unless you open AWE and add the /PAE in your boot.ini for windows. Maybe just adding -3GB could already be enough to destress your instances.

Keep in mind, your instances will be competing for CPU, unless you dedicated some per instance.

I advise using Windows System Resource Monitor ( MS tool ) to take care of CPU availability. Don't let it handle RAM for SQLServer.