April 23, 2014 at 9:29 am
Hi all,
We have a sql server with the following resources:
Processor: INtel(R) Xeon(R) 1 CPU X5690 @3.47 6 cores
RAM: 20.0GB
SQL Server Max server memory (MB): 16000
SQL Server Min mem per query: 1024
Disk space: Loads
SQL server version: 2008R2 (Non enterprise)
We have been running a number of queries daily which use the except function across a linked server. (Not ideal I know, but required)
This usually takes about 8 mins but completes ok.
We have recently installed IBM Cognos on the same box which has been set to use no more the 700MB ram and we can see from Task Manager that this the case. (Java.exe)
Cognos is rarely used as it is a dev box so the cpu is normally 0% and the the ram around 17-18 GB.
When running this query (with nothing else running) it is now timing out and due to the resource limit, which when changed to 0, is now taking over 30 mins!! (we cancelled it at this point)
My question is how can losing 1gb of Ram have such a detrimental affect!!????
Is there anything I can do to manage the resources better?
Are there any best practices I can adhere to?
Any help would be greatly appreciated!
Many thanks
Phil
April 23, 2014 at 12:25 pm
The best thing to do is not share resources with anything. sQL Server just doesn't play well with others. But, it doesn't sound right that just reducing the amount of memory available by 1gb resulted in such a huge change in behavior. Do you have the execution plans from before and after? When you installed Cognos, did it need to have access to SQL Server itself, meaning, is it running a database on the server too? It could just be resource contention within SQL Server as it supports both Cognos and the older queries (assuming Cognos has added to the SQL Server load).
"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
April 24, 2014 at 4:31 am
Many thanks for the response.
The executions plans are the same, but because it is across a linked server we cannot see the remote query cost.
I think I'll just go with a work around.
Cheers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply