SQL Server faster after a restart.

[fredp1]

Hi all,

I have a test installation of SQL 2K sp3a on NT 2KServer sp4.
Its actually a copy of my production server on anther pc.

I find that some queries/stored procs appear to hang(more than 20min) until the server is restarted. Then the same sp runs faster, approx 2min.
The enviroment is a series of steps run from SQl Server agent. Its usually the same step that appears to hang. After the restart it runs fine.

My production machine doesn't have this problem. It has a similar enviroment, i.e. same database/size.

Any clues where to start looking.
Its the restart of the server that 'fixes' the problem.

The test box is a P4HT, 3.0Ghz, 1Gb Ram, 160Gb ide drive.
The prod box is a Dual P3 1Ghz, 1g ram, 270Gb raid 5 drives(by 5).
The software is very similar on the both test and prod machines.

Thanks for any assistance.
Fred

 

[rsinj]

when you say "I find that some queries/stored procs appear to hang(more than 20min)" are you saying it completes after 20 minutes, or you give up waiting after 20 minutes and restart the server?

are the resources available to the sql server the same on both machines? are you giving the sql server the same amount of memory on both? for example, you don't have the production box giving 640 MB memory to sql server and your test box only giving it 64 MB. also, tempdb is the same size on both?

when you run the sp which causes the 20 minute hang, what was done prior to the sp? just having it up and running for a while? if you run the sp successfully after the restart in 2 minutes, if you were to run it again, would it run in 2 minutes again, or would it then hang for 20 minutes?

just trying to get a better perspective on the situation.

 

[fredp1]

Thanks for your reply rsinj,

I stopped the job from running after 20 minutes.
(it will take about 30minutes to run end to end if I let it go)
The SQL server is then stopped then restarted. (NT is never restarted).

AS far as memory resources, the test box has more memory, 913mb(test) vs 705 mb(prod)

tempdb was left alone, it grows as needed.
I've just set them the same and will try again.

This sp is part of a series of overnight steps which updates a datamart. Its about 4 hrs total processing time.
There are seqential steps that run one at a time and a couple of parallel steps. (I don't think there is a complict with locks as i haven't seen any locking in the 'Current activity' complaining of locks)
It stops at about the 3hr mark daily for the last week since the test box has been built.

The test box is a very quite box.

I'm also perplexed why some queries take a long time to run, a handful run for 30 minutes consistantly while the prod box takes about <2 minutes.
Most of the other sp's run faster on the test box than the prod server.
i'll test and repost.

Thanks

 

[SQLSister]

Do you have the same indexes on both machines? Is anything else running on the test machine? Is something else scheduled to happen at the 3 hr mark since it seems to stop there every day? Have you angered the pixies and gremlins that live in the machine;-)?

Questions about posting. See faq183-874

 

[fredp1]

I wish it was only angry pixies to deal with... that would be much more fun to deal with

As far as any other processing, there is nothing that I can see thats causing a problem.

i then ended up changing the sp slightly.
I re-ran the query after the 'overnight' job finished at 11am, and the sp took 8 minutes.
Then I installed mdac 2.8
When I restarted SQL server, the same sp takes 28secs repeated 4 times.
I suppose I'll wait and see what happens again tomorrow with the new mdac installed and making peace with the pixies.

Thanks for hanging in there.
Fred

 

[StevenK]

What else do you have running on the server in question ?
We've seen similar issues of late with a server that had SQL Server running alongside a copy of Microsoft Exchange - which was hogging a lot of memory on the server.
Following some tweaking of Exchange the issue with SQL Server seemed to relieve itself somewhat [probably jinxed it now .... ]
Steve

 

[fredp1]

HI all,

The server 'stoppped' today.

I restarted SQL server and the query run in about 25secs.
(It was running for 2 hours before I stoppped it)

There was no table locks, but there was another query running as well.
I'll make sure there is no other query running tomorrow... just to be sure its not a external query.

StevenK:
The server doesn't run any other app, just SQL2K.
Hope all is well with your server.

I'll keep ya posted.
Fred

 

[SQLSister]

If another query was running as well, you might be getting a deadlock.

Questions about posting. See faq183-874

 

[fredp1]

This is a summary of the problem todate.
There are a number of stored proc, at least 2, that never complete.
If I restart SQl Server, they take about 30 secs to run.

The sp's are in a job that updates a datawarehouse overnight.
Its approx 3 hours into the processing that these 2 sp never complete.
There is no other activity at the time the job is running.
There is no deadlocks.
There are no othere apps running

The same queries run on the prod server fine.

Today I changed the sp, by removing a redundant 'where not exists' statement from the sp's, and re ran the query(without restarting sql server) and they ran in about 30 sec.

My issues is whats going on with sql server that the sp will not complete after a long run, however the same sp will complete when SQL Server is restarted.

Sure I can modify the query to make it always work, but its not behaving consistantly.

Any ideas where to start looking?

Thanks
Fred

Someone is bound to ask about the sp, and here is a snapshot of the code.
Look at the last couple of lines for the redundant "where not exists" clause. Its redundant because the table gets truncated at the begining of the sp.
(This code is autogenerated from a datawarehouse design tool that I use, BPW. The new version of this software doesn't generate the redundant "where not exists" clause)

I'm trying to focus on SQL server, not the sp, as to why after a restart the query runs fine unaltered?

	SET NOCOUNT ON

	----------------------------------------------------------
	-- Declare local variables to store statistics information
	----------------------------------------------------------

	DECLARE	@RowsInserted	Integer,
		@RowsUpdated	Integer,
		@StartTime	datetime,
		@FinishedTime	datetime

	SET	@StartTime = getdate()


	-------------------------------------------------------------
	-- Full Refresh of [TTemp_MCHEAD_Costing_Tmpl3] - All data will be replaced
	-------------------------------------------------------------

	TRUNCATE TABLE [LiveBPW_Catalog].[dbo].[TTemp_MCHEAD_Costing_Tmpl3]

	----------------------------------------------------
	--            UPDATE existing rows                --
	----------------------------------------------------

	UPDATE	DestObject
	SET
		DestObject.[Product structure type] = T3.[Product structure type], 
		DestObject.[Configuration identity] = T3.[Configuration identity]
--abreviated code
	WHERE
	-- Natural key(s) --

		DestObject.[Company] = T3.[Company] AND
		DestObject.[Facility] = T3.[Facility] AND
		DestObject.[Item number] = T3.[Item number] 

	SET	@RowsUpdated = @@Rowcount


	
	----------------------------------------------------
	--                INSERT new rows                 --
	----------------------------------------------------

	INSERT	[LiveBPW_Catalog].dbo.[TTemp_MCHEAD_Costing_Tmpl3]
		(
		[Company], 
		[Facility], 
		[Item number], 
		[Product structure type], 
--**abbreviated code
)	
	SELECT
		T3.[Company], 
		T3.[Facility], 
		T3.[Item number], 
		T3.[Product structure type], 
--*** abreviated code
	FROM
		[LiveBPW_Catalog].[dbo].[TTemp_MCHEAD_Costing_Tmpl2] AS T3
		LEFT OUTER JOIN [LiveBPW_Catalog].[dbo].[Temp_MITBAL_MITFAC2] AS T1 ON
			T3.[Company] = T1.[Company]   AND
			T3.[Facility] = T1.[Facility]   AND
			T3.[Item number] = T1.[Item number]   
		LEFT OUTER JOIN [LiveBPW_Catalog].[dbo].[TTemp_MCHEAD_Costing_Tmpl1] AS T2 ON
			T3.[Company] = T2.[Company]   AND
			T3.[Facility] = T2.[Facility]   AND
			T3.[Item number] = T2.[Item number]   AND
			T2.[Company] = T1.[Company]   AND
			T2.[Facility] = T1.[Facility]   AND
			T2.[Item number] = T1.[Item number]   AND
			T3.[Company] = T1.[Company]   AND
			T3.[Facility] = T1.[Facility]   AND
			T3.[Item number] = T1.[Item number]   
--When this code is deleted, the sp runs fine
		WHERE NOT EXISTS -- Check that the rows not already exist
	(SELECT 1 FROM [LiveBPW_Catalog].[dbo].[TTemp_MCHEAD_Costing_Tmpl3] AS DestObject
		WHERE
		DestObject.[Company] = T3.[Company] AND
		DestObject.[Facility] = T3.[Facility] AND
		DestObject.[Item number] = T3.[Item number]
	)

 

[rsinj]

have you tried turning statistics and trace on both machines and see how they compare when running the sp?

I agree that the problem is probably with the sql server/machine, but, you might not find where the holdup is until you see where the time is being monopolized on your test box when the sp is run.

 

[fredp1]

Thanks for your reply!

Can you walk we through it or point me in the right direction?

I'm not really sure what i'm looking it at, or what to look for when I see Query Execution plans.

When you mention statistics, I know how to recreate stats on a table, but i'm not sure what you mean.

When turning tracing on, I assume your talking about Profiler.
Is there any specific events I should be tracking.

I'm very green in this area, but I'm very keen to get an understanding on whats going on.
Thanks for any assistance.

Fred

 

[zhavic]

At first, instead of your where clause use this:

SELECT
        T3.[Company],
        T3.[Facility],
        T3.[Item number],
        T3.[Product structure type],
--*** abreviated code
    FROM
        [LiveBPW_Catalog].[dbo].[TTemp_MCHEAD_Costing_Tmpl2] AS T3
        LEFT OUTER JOIN [LiveBPW_Catalog].[dbo].[Temp_MITBAL_MITFAC2] AS T1 ON
            T3.[Company] = T1.[Company]   AND
            T3.[Facility] = T1.[Facility]   AND
            T3.[Item number] = T1.[Item number]   
        LEFT OUTER JOIN [LiveBPW_Catalog].[dbo].[TTemp_MCHEAD_Costing_Tmpl1] AS T2 ON
            T3.[Company] = T2.[Company]   AND
            T3.[Facility] = T2.[Facility]   AND
            T3.[Item number] = T2.[Item number]   AND
            T2.[Company] = T1.[Company]   AND
            T2.[Facility] = T1.[Facility]   AND
            T2.[Item number] = T1.[Item number]   AND
            T3.[Company] = T1.[Company]   AND
            T3.[Facility] = T1.[Facility]   AND
            T3.[Item number] = T1.[Item number]   
		LEFT JOIN [LiveBPW_Catalog].[dbo].[TTemp_MCHEAD_Costing_Tmpl3] AS DestObject ON
	        DestObject.[Company] = T3.[Company] AND
	        DestObject.[Facility] = T3.[Facility] AND
	        DestObject.[Item number] = T3.[Item number]
	-- Check that the rows not already exist
	WHERE DestObject.[Company] IS NULL

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[donutman]

I've noticed a similar problem, but not of such an extreme. My SP takes about a minute after a reboot and about 6 if I don't. I only need to have it run that SP once a week, so I schedule a reboot of the server once a week in the early morning on the same day that the user usually runs that SP.
I'm sorry that this doesn't really help.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[fredp1]

Thanks for the input eveyone.
Zhavic- Since the code is autogenerated, and the prod box runs fast, I wanted to understand why the test server is slower for those 2 sp's. I have a newer version of the datawarehouse software that will generate similar code to the code that you showed. Thanks for your help.

I have managed to improve the sp from 30 minutes exec time to 40 seconds without changing the code!

I've been doing some research and finally had a look at the Estimate Execution plan (Ctrl+L) in query analyzer.
It was showing some tables that had no statistics on them (The tables were shown in a red font)

I created statitics by right clicking on the table(in query Analyser) and went from there.
Now the query is running at the same speed as the prod server. Very simple.

I pays to get a better understanding of statistics and how it influences the query optimiser.

I hope this helps someone.

Fred.

P.S.
When I was originally trying to look at the execution plan, I was getting frustrated by the long amount of time it was taking to run the query, because I pressed Show Execution plan(Ctrl+K), which shows the actual QEP after the sp ran. By selecting Display Estimated Execution plan(Ctrl+L), the QEP came up straight away.