Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SELECT query with "Writes" ?! Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 10:23 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:13 AM
Points: 32, Visits: 1,223
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
Post #1398539
Posted Wednesday, December 19, 2012 10:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 40,432, Visits: 36,887
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

Post #1398563
Posted Wednesday, December 19, 2012 2:13 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:13 AM
Points: 32, Visits: 1,223
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
Post #1398667
Posted Wednesday, December 19, 2012 2:31 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 40,432, Visits: 36,887
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

Post #1398675
Posted Friday, December 21, 2012 12:35 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:13 AM
Points: 32, Visits: 1,223
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
Post #1399223
Posted Friday, December 21, 2012 12:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 40,432, Visits: 36,887
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

Post #1399230
Posted Friday, December 21, 2012 12:48 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:13 AM
Points: 32, Visits: 1,223
We already have a complete statistic update up running every day ...

Jörg A. Stryk
MVP - MS Dynamics NAV
Post #1399231
Posted Friday, December 21, 2012 12:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 40,432, Visits: 36,887
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

Post #1399235
Posted Friday, December 21, 2012 2:35 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:13 AM
Points: 32, Visits: 1,223
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
Post #1399286
Posted Friday, December 21, 2012 3:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 40,432, Visits: 36,887
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

Post #1399306
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse