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)
 SP takes long time but reboot fixes it

Author  Topic 

mistux
Yak Posting Veteran

90 Posts
Posted - 2005-12-06 : 13:09:33
I have a stored procedure that normally takes 1-2 seconds to run.

However, after a couple of days, it then takes 7-8 minutes to run.

The tables are not being heavily modified.

If I stop the SQL server and its service then start it again and run the same stored procedure, it runs again in under 2 seconds.

I am doing a nightly "Reorginize data and index pages" with the option checked for "Change free space per page percentage to" set to 10%.

Any thoughts?

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2005-12-06 : 14:16:49
Gauge it with another sp to see if all sp's are affected or if it's just that one. Also post your code.

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com
Go to Top of Page

mistux
Yak Posting Veteran

90 Posts
Posted - 2005-12-06 : 14:25:10
Only seems to be happening to this one (so far):
SELECT dbo.T_SetupSheetHistoryCombinationsDetail.QuoteID, dbo.T_SetupSheetHistoryCombinationsDetail.WOID, 
               dbo.T_SetupSheetHistoryCombinationsDetail.WOIDSub, dbo.T_SetupSheetHistoryCombinationsDetail.ECN, 
               dbo.T_SetupSheetHistoryCombinationsDetail.ComboCustPartNum, dbo.T_SetupSheetHistoryHeader.ParWOID, 
               dbo.T_SetupSheetHistoryHeader.ParWOIDSub, dbo.T_SetupSheetHistoryCombinationsDetail.StatusID, 
               dbo.T_SetupSheetHistoryCombinationsDetail.ComboCompleteDate, dbo.vShipping_WOIDItem_Balance_KIT.BalLng
FROM  dbo.T_SetupSheetHistoryCombinationsDetail INNER JOIN
               dbo.vShipping_WOIDItem_Balance_KIT ON 
               dbo.T_SetupSheetHistoryCombinationsDetail.ComboCustPartNum = dbo.vShipping_WOIDItem_Balance_KIT.Item AND 
               dbo.T_SetupSheetHistoryCombinationsDetail.WOID = dbo.vShipping_WOIDItem_Balance_KIT.WOID INNER JOIN
               dbo.T_SetupSheetHistoryHeader ON dbo.T_SetupSheetHistoryCombinationsDetail.ECN = dbo.T_SetupSheetHistoryHeader.ECN AND 
               dbo.T_SetupSheetHistoryCombinationsDetail.WOIDSub = dbo.T_SetupSheetHistoryHeader.WOIDSub AND 
               dbo.T_SetupSheetHistoryCombinationsDetail.WOID = dbo.T_SetupSheetHistoryHeader.WOID AND 
               dbo.T_SetupSheetHistoryCombinationsDetail.QuoteID = dbo.T_SetupSheetHistoryHeader.QuoteID
WHERE (dbo.T_SetupSheetHistoryCombinationsDetail.StatusID = 9 OR
               dbo.T_SetupSheetHistoryCombinationsDetail.StatusID = 15 OR
               dbo.T_SetupSheetHistoryCombinationsDetail.StatusID = 16) AND (dbo.T_SetupSheetHistoryCombinationsDetail.ComboCompleteDate IS NOT NULL) 
               AND (dbo.vShipping_WOIDItem_Balance_KIT.BalLng <= 0)
GROUP BY dbo.T_SetupSheetHistoryCombinationsDetail.QuoteID, dbo.T_SetupSheetHistoryCombinationsDetail.WOID, 
               dbo.T_SetupSheetHistoryCombinationsDetail.WOIDSub, dbo.T_SetupSheetHistoryCombinationsDetail.ECN, 
               dbo.T_SetupSheetHistoryCombinationsDetail.ComboCustPartNum, dbo.T_SetupSheetHistoryHeader.ParWOID, 
               dbo.T_SetupSheetHistoryHeader.ParWOIDSub, dbo.T_SetupSheetHistoryCombinationsDetail.StatusID, 
               dbo.T_SetupSheetHistoryCombinationsDetail.ComboCompleteDate, dbo.vShipping_WOIDItem_Balance_KIT.BalLng


Michael
For all your DNC needs for your CNC machines. www.mis-group.com
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-06 : 16:21:03
Does an sp_recompile, on that Sproc, fix the problem (i.e. WITHOUT a reboot)?

Kristen
Go to Top of Page

mistux
Yak Posting Veteran

90 Posts
Posted - 2005-12-06 : 20:39:53
I'll have to wait untill it breaks again to test that theory.

Michael
For all your DNC needs for your CNC machines. www.mis-group.com
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-07 : 04:42:50
Worth doing. If that fixes it you need to plan to recompile the SProcs periodically.

Didn't spot this before, sorry:

"with the option checked for "Change free space per page percentage to" set to 10%."

That's generally a crap option, and I think really bad that its the default. It will have physically changed your indexes to set the FILLFACTOR to 90%, so now if you want to undo it you've got plenty of work to drop and recreate the indexes .... It would be MUCH better if the default was the other option ("Leave it alone"), as surely you are in a better position to decide if you need 100% or something else.

If you've got an index on an IDENTITY column, for example, there is NO point having 90% fill factor 'coz nothing can get inserted later, only appended at the end, and that will dramatically change the structure of the index when the Maint. Plan runs, which will probably muck up the chosen query plan for the SProc etc.

Clearly there ARE indexes which benefit from "loose" indexes - e.g. things inserting with largely random keys, but you ought to change that setting in your maintenance plan AND look at the indexes which are now set to 90% fill factor

Bummer

P.S. Its a bit of a hobby-horse of mine, just in case you hadn't noticed!

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-12-07 : 05:26:47
In your sp, did you use Begin Trans and forget to commit or Rollback it?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2005-12-07 : 08:18:19
quote:
Originally posted by mistux

Only seems to be happening to this one (so far):
SELECT dbo.T_SetupSheetHistoryCombinationsDetail.QuoteID, dbo.T_SetupSheetHistoryCombinationsDetail.WOID, 
               dbo.T_SetupSheetHistoryCombinationsDetail.WOIDSub, dbo.T_SetupSheetHistoryCombinationsDetail.ECN, 
               dbo.T_SetupSheetHistoryCombinationsDetail.ComboCustPartNum, dbo.T_SetupSheetHistoryHeader.ParWOID, 
               dbo.T_SetupSheetHistoryHeader.ParWOIDSub, dbo.T_SetupSheetHistoryCombinationsDetail.StatusID, 
               dbo.T_SetupSheetHistoryCombinationsDetail.ComboCompleteDate, dbo.vShipping_WOIDItem_Balance_KIT.BalLng
FROM  dbo.T_SetupSheetHistoryCombinationsDetail INNER JOIN
               dbo.vShipping_WOIDItem_Balance_KIT ON 
               dbo.T_SetupSheetHistoryCombinationsDetail.ComboCustPartNum = dbo.vShipping_WOIDItem_Balance_KIT.Item AND 
               dbo.T_SetupSheetHistoryCombinationsDetail.WOID = dbo.vShipping_WOIDItem_Balance_KIT.WOID INNER JOIN
               dbo.T_SetupSheetHistoryHeader ON dbo.T_SetupSheetHistoryCombinationsDetail.ECN = dbo.T_SetupSheetHistoryHeader.ECN AND 
               dbo.T_SetupSheetHistoryCombinationsDetail.WOIDSub = dbo.T_SetupSheetHistoryHeader.WOIDSub AND 
               dbo.T_SetupSheetHistoryCombinationsDetail.WOID = dbo.T_SetupSheetHistoryHeader.WOID AND 
               dbo.T_SetupSheetHistoryCombinationsDetail.QuoteID = dbo.T_SetupSheetHistoryHeader.QuoteID
WHERE (dbo.T_SetupSheetHistoryCombinationsDetail.StatusID = 9 OR
               dbo.T_SetupSheetHistoryCombinationsDetail.StatusID = 15 OR
               dbo.T_SetupSheetHistoryCombinationsDetail.StatusID = 16) AND (dbo.T_SetupSheetHistoryCombinationsDetail.ComboCompleteDate IS NOT NULL) 
               AND (dbo.vShipping_WOIDItem_Balance_KIT.BalLng <= 0)
GROUP BY dbo.T_SetupSheetHistoryCombinationsDetail.QuoteID, dbo.T_SetupSheetHistoryCombinationsDetail.WOID, 
               dbo.T_SetupSheetHistoryCombinationsDetail.WOIDSub, dbo.T_SetupSheetHistoryCombinationsDetail.ECN, 
               dbo.T_SetupSheetHistoryCombinationsDetail.ComboCustPartNum, dbo.T_SetupSheetHistoryHeader.ParWOID, 
               dbo.T_SetupSheetHistoryHeader.ParWOIDSub, dbo.T_SetupSheetHistoryCombinationsDetail.StatusID, 
               dbo.T_SetupSheetHistoryCombinationsDetail.ComboCompleteDate, dbo.vShipping_WOIDItem_Balance_KIT.BalLng


Michael
For all your DNC needs for your CNC machines. www.mis-group.com


I aliased your tables (makes it easier for me to read) and also replaced the "or"'s with an "in". See if that improves the overall performance. It may be very little but every bit helps but on that same note it's not going to fix your problem. See if it helps. Test it out using the execution plans or whatever you choose. Also make sure it returns the same results, which it will .

What's in the view (vShipping_WOIDItem_Balance_KIT)?

SELECT HCD.QuoteID, HCD.WOID, 
               HCD.WOIDSub, HCD.ECN, 
               HCD.ComboCustPartNum, SHH.ParWOID, 
               SHH.ParWOIDSub, HCD.StatusID, 
               HCD.ComboCompleteDate, SBK.BalLng
FROM  dbo.T_SetupSheetHistoryCombinationsDetail HCD
	INNER JOIN dbo.vShipping_WOIDItem_Balance_KIT SBK ON (HCD.ComboCustPartNum = SBK.Item) AND (HCD.WOID = SBK.WOID) 
	INNER JOIN dbo.T_SetupSheetHistoryHeader SHH ON (HCD.ECN = SHH.ECN) AND (HCD.WOIDSub = SHH.WOIDSub) AND (HCD.WOID = SHH.WOID) AND (HCD.QuoteID = SHH.QuoteID)
WHERE HCD.StatusID in (9, 15, 16) AND (HCD.ComboCompleteDate IS NOT NULL) AND (SBK.BalLng <= 0)
GROUP BY HCD.QuoteID, HCD.WOID, 
               HCD.WOIDSub, HCD.ECN, 
               HCD.ComboCustPartNum, SHH.ParWOID, 
               SHH.ParWOIDSub, HCD.StatusID, 
               HCD.ComboCompleteDate, SBK.BalLng
Go to Top of Page

mistux
Yak Posting Veteran

90 Posts
Posted - 2005-12-07 : 19:37:54
quote:
Originally posted by madhivanan

In your sp, did you use Begin Trans and forget to commit or Rollback it?


What you see is what I got in it. (I'm open for any and all suggestions)

Michael
For all your DNC needs for your CNC machines. www.mis-group.com
Go to Top of Page

mistux
Yak Posting Veteran

90 Posts
Posted - 2005-12-07 : 19:41:05
quote:
Originally posted by Kristen

Didn't spot this before, sorry:

"with the option checked for "Change free space per page percentage to" set to 10%."

That's generally a crap option, and I think really bad that its the default. It will have physically changed your indexes to set the FILLFACTOR to 90%, so now if you want to undo it you've got plenty of work to drop and recreate the indexes .... It would be MUCH better if the default was the other option ("Leave it alone"), as surely you are in a better position to decide if you need 100% or something else.

If you've got an index on an IDENTITY column, for example, there is NO point having 90% fill factor 'coz nothing can get inserted later, only appended at the end, and that will dramatically change the structure of the index when the Maint. Plan runs, which will probably muck up the chosen query plan for the SProc etc.

OK, now you ahve me worried! I thought doing that was a good thing! Now what do I need to do to fix that? And what should the setting be?

Michael
For all your DNC needs for your CNC machines. www.mis-group.com
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-08 : 00:23:26
"I thought doing that was a good thing!"

Yeah, its the wrong default IMHO

"Now what do I need to do to fix that?"

Drop and recreate the indexes without the "FILLFACTOR 90%" - you could Script out just the indexes, and remove the "FILLFACTOR 90%" stuff and then run the script ...

"And what should the setting be?"

Ah ... that's the $64k question ... I'll send you my consultancy invoice!

As a simple rule: for an index where you only add ascending sequence keys then make it 100% (which is expressed as 0, or the absence of any FILLFACTOR statement, but CANNOT (IIRC) be set using DBCC REINDEX - which would otherwise be your get-out-of-jail card).

If you have random keys (alphanumeric, GUID, etc.) then you can use a looser FILLFACTOR. The idea is that there is some spare space in every block of the index tree to allow new keys to be added without needing to split the block. However, because each block will start off more sparsely populated the "find" performance is not as good - i.e. more blocks have to be read to process the same number of keys.

So ... if you will insert VERY FEW records its probably better to have a more dense index and suffer some index block splits. Likewise if you will have a burst of inserts all in the same part of the index.

But ... if you have a high number of inerts into your table, and they are fairly randomly spread, then having a looser FILL FACTOR will help with INSERT performance.

Either way, unless you are inserting ascending sequence numbers [and even then too, but less frequently will do], you need to rebuild the index reasonably often (might as well do it every day if you have a quiet slot when any performance hit will not be a problem, and you can cope with the large transaction logs that it tends to generate. Update the Statistics whilst you are at it. The standard maintenance plan will do the trick - just don't use the "10% free" option!)

Kristen
Go to Top of Page

mistux
Yak Posting Veteran

90 Posts
Posted - 2005-12-15 : 12:36:10
quote:
Originally posted by Kristen

Does an sp_recompile, on that Sproc, fix the problem (i.e. WITHOUT a reboot)?


Just did the sp_recompile and it did not help.

Michael
For all your DNC needs for your CNC machines. www.mis-group.com
Go to Top of Page

mistux
Yak Posting Veteran

90 Posts
Posted - 2005-12-15 : 12:40:35
Just for my education, if the tables used in my sp are locked by other users, could that cause my long execution time? Or would they just never work?

If, so how do I go about finding who or what other sp's has those tables locked?

I've looked in SQL Ent Mgr under locked but I don't see where it tells me what tables are locked or what sp's have locks etc. just Sesion IDs.

Michael
For all your DNC needs for your CNC machines. www.mis-group.com
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-12-15 : 12:50:01
run the proc when it is performing well with:
set showplan_all on

save the plan in a text file

run it again when it does badly
and compare the plans.

See if anything changed, or if you can locate some trouble area.

post the plans here if you want to.

rockmoose
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-15 : 13:09:05
We have a funny thing here. We have some Sprocs that are used masses of times a day [300,000 times a day, say]. They query static data, so will give the same answer each time (for given parameters).

A specific example Sproc queries a "caching table" [warehoused from some core data]. Sometimes we change the core data - usually something very minor. We then "empty" the caching tables, and it gets re-populated "just in time" until it has all the necessary caching data again. At this time 99.9% of the data will be the same, and it is very unlikely that there will be any new rows.

However, the order in which the rows are added to the caching table will depend on the order they are requested, so will be bound to be different to before (but the PK order will be the same etc.)

The indexes will be REINDEXED during the night IF there is any fragmentation. And the STATS will be UPDATED every night (regardless).

After a DELETE of the caching table the Sprocs will perform about 3 times slower than before. This is consistent - a couple of days later [when the caching table is fully repopulated] they will be performing at the same, slower, speed.

An sp_recompile fixes it.

Question: why doesn't the original query plan work as efficiently with the caching table once its been repopulated and reindexed and stats. updated? Isn't the job going to be identical at that point in time?

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-12-15 : 13:25:47
>> Question: why doesn't the original query plan work as efficiently with the caching table once its been repopulated and reindexed and stats. updated? Isn't the job going to be identical at that point in time?

Since the cache table is filled up jit, the original query plan might not bother to utilize indexes because the amount of data is small,
and some scan is done?

I would compare the plans before and after a recompilation.

rockmoose
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-15 : 14:50:12
"I would compare the plans before and after a recompilation"

Stupidly I hadn't thought to do that until it was proposed above, but I will when I have a moment to test it carefully. Thanks. "I'll be back ..."

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-12-15 : 14:58:29
"I'll be back ..."
There is soon a vacancy for governor of California, you're not running are you?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-15 : 15:17:54
"you're not running are you"

Not even walking ...
Go to Top of Page

mistux
Yak Posting Veteran

90 Posts
Posted - 2005-12-17 : 16:42:14
Well, now when I reboot the server it still takes 8 min to run, so now that is not even fixing things!!

It can't be a locking thing, because after I rebooted I did not let anyone log on, so I was the only one logged in to the server and the only one logged into SQL on any tables.

Aaagghh!! What is it!

Is there a way for me to completely rebuild the indexes and statistics and whatever else on all my tables all at once.

If so how?

Michael
For all your DNC needs for your CNC machines. www.mis-group.com
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2005-12-19 : 08:17:14
quote:
Originally posted by mistux

Well, now when I reboot the server it still takes 8 min to run, so now that is not even fixing things!!

It can't be a locking thing, because after I rebooted I did not let anyone log on, so I was the only one logged in to the server and the only one logged into SQL on any tables.

Aaagghh!! What is it!

Is there a way for me to completely rebuild the indexes and statistics and whatever else on all my tables all at once.

If so how?

Michael
For all your DNC needs for your CNC machines. www.mis-group.com


quote:
Originally posted by SQLServerDBA_Dan

What's in the view (vShipping_WOIDItem_Balance_KIT)?


quote:
Originally posted by SQLServerDBA_Dan

See if it helps. Test it out using the execution plans or whatever you choose. Also make sure it returns the same results, which it will .

SELECT HCD.QuoteID, HCD.WOID, 
               HCD.WOIDSub, HCD.ECN, 
               HCD.ComboCustPartNum, SHH.ParWOID, 
               SHH.ParWOIDSub, HCD.StatusID, 
               HCD.ComboCompleteDate, SBK.BalLng
FROM  dbo.T_SetupSheetHistoryCombinationsDetail HCD
	INNER JOIN dbo.vShipping_WOIDItem_Balance_KIT SBK ON (HCD.ComboCustPartNum = SBK.Item) AND (HCD.WOID = SBK.WOID) 
	INNER JOIN dbo.T_SetupSheetHistoryHeader SHH ON (HCD.ECN = SHH.ECN) AND (HCD.WOIDSub = SHH.WOIDSub) AND (HCD.WOID = SHH.WOID) AND (HCD.QuoteID = SHH.QuoteID)
WHERE HCD.StatusID in (9, 15, 16) AND (HCD.ComboCompleteDate IS NOT NULL) AND (SBK.BalLng <= 0)
GROUP BY HCD.QuoteID, HCD.WOID, 
               HCD.WOIDSub, HCD.ECN, 
               HCD.ComboCustPartNum, SHH.ParWOID, 
               SHH.ParWOIDSub, HCD.StatusID, 
               HCD.ComboCompleteDate, SBK.BalLng
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site