|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Thursday, March 14, 2013 9:22 AM
Points: 32,
Visits: 1,213
|
|
What's the definition of the pk?
PK and Clustered Index is on this field "No_" (varchar(20))
Jörg A. Stryk MVP - MS Dynamics NAV
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 37,725,
Visits: 29,982
|
|
stryk (12/19/2012)
What's the definition of the pk? PK and Clustered Index is on this field "No_" (varchar(20))
Please post the definition of the pk (the SQL statement to create it)
Gail Shaw Microsoft Certified Master: SQL Server 2008, MVP 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
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Thursday, March 14, 2013 9:22 AM
Points: 32,
Visits: 1,213
|
|
Taken from SSMS script generator:
ALTER TABLE [dbo].[XYZ$Customer] ADD CONSTRAINT [XYZ$Customer$0] PRIMARY KEY CLUSTERED ( [No_] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1] GO
Jörg A. Stryk MVP - MS Dynamics NAV
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 37,725,
Visits: 29,982
|
|
Then that order by shouldn't need a sort. Unless there's maybe parallelism. Really need to see the exec plan
Gail Shaw Microsoft Certified Master: SQL Server 2008, MVP 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
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Thursday, March 14, 2013 9:22 AM
Points: 32,
Visits: 1,213
|
|
Update:
So far my customer could not provide the QEP and currently I cannot logon to the system (holidays!).
But they ran the Index Rebuild on that table which seems to help a lot! So, for now we will schedule this maintenance daily ...
But this also means, that - for now - we cannot reproduce the problem anymore, so I owe you the QEP ...
Probably we should close this thread - for now. Once the problem returns I'll make sure to get the actual QEP and come back on this again ...
Thanks all for your support!
Happy Holidays, Jörg
Jörg A. Stryk MVP - MS Dynamics NAV
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 37,725,
Visits: 29,982
|
|
stryk (12/21/2012) But they ran the Index Rebuild on that table which seems to help a lot! So, for now we will schedule this maintenance daily ...
No, don't. Run an Update Statistics on that table daily.
Gail Shaw Microsoft Certified Master: SQL Server 2008, MVP 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
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Thursday, March 14, 2013 9:22 AM
Points: 32,
Visits: 1,213
|
|
We already have a complete statistic update up running every day ...
Jörg A. Stryk MVP - MS Dynamics NAV
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 37,725,
Visits: 29,982
|
|
stryk (12/21/2012) We already have a complete statistic update up running every day ...
Fullscan or sampled?
Index rebuilds seldom fix performance problems. The stats updates they do however fix many problem
Gail Shaw Microsoft Certified Master: SQL Server 2008, MVP 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
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Thursday, March 14, 2013 9:22 AM
Points: 32,
Visits: 1,213
|
|
We do a Full Scan - using the MP - every weekend (because we have plenty of time to do this); every day we just do a sp_updatestats (because we don't have the time for a full run) plus a sp_createstats 'indexonlx' ...
But in case of this special table I wonder how these stats should get "outdated"- the affected "Customer" table is just master data, so once a record was inserted (sequentially, ordered by that "No_") there are not many changes ... for the same reason IMHO "fragmentation" should not be a problem here ... especially not on the "Clustered Index" ...
It seems like this query was fired from a NAV GUI (so called "Form") and it was declaring a "Fast Forward Cursor" to load all that data ... I don't no for sure how big that table currently is, so I could guess "memory pressure" might be the problem ... but if that's the case: why does this problem not happen ALL the times? It's still the same 16GB of RAM (SQL Server Max. 14GB) ...
The more I think about it, the more I have to scratch my head ...
I'm afraid we are stalled here now, since my customer just went to X-Mas vacation. I almost hope this problem returns , because I'm really curious about the QEP and to find out what happens here ...
Jörg A. Stryk MVP - MS Dynamics NAV
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 37,725,
Visits: 29,982
|
|
stryk (12/21/2012) We do a Full Scan - using the MP - every weekend (because we have plenty of time to do this); every day we just do a sp_updatestats (because we don't have the time for a full run) plus a sp_createstats 'indexonlx' ...
So full scan on the weekend, sampled during the week. I would suggest this table gets a full scan daily.
If an index rebuild fixed the problem, 99% chance it's either stale stats or a bad exec plan getting into cache somehow.
Gail Shaw Microsoft Certified Master: SQL Server 2008, MVP 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
|
|
|
|