SQLTeam.com | Weblogs | Forums

Free Used Memory and Configure TEMPDB

sql2012

#1

I am using Sql Server 2012 64 Bit Standard Version
I am facing one problem that our database is about 500 GB.
basically some of queries are taking much time to select and update data
the problem is that Sql Allocates much memory and Processor mostly 90 to 100 percent as a result system got hang.

my question is how can i configure sql that after using memory SQL Should free the used memory.
furthermore TEMPDB is getting much high day by day it is around of 60GB i do not know why TEMPDB is increasing day by day. as per my knowledge it donot have physical data present.

Please help ASAP.
Thanks in Advance.


#2

You have given very little information to answer the question "How long is a piece of string?".

If you server has 64GB of memory, or less, the first thing I would check is whether Windows has enough memory to run.
(SQL Server will, by default use as much memory as it can grab. This can stop Windows from running efficiently.)

If Max Memory is is at it's default value in SQL, and your server has 64GB of memory, I would first see what happens when it is set to 56GB.

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'max server memory', 57344;  
GO  
RECONFIGURE;  
GO 

#3

It may also be worth installing something like the following as it can give indications as to what is wrong with a server setup:

https://www.brentozar.com/blitz/


#4

Dear Ifor thanks for your kind responce.

Our Server Memory is 60 GB with Hard Drive of 1.3 TB i have set max server memory to 50 GB but System Allocates 50 GB memory at a glance.

My Server do not have any other Application running or installed but Sql Server 2012 Standard Version 64 Bit.

Processors are also using around 90 percent as i check in Windows Task Manager.


#5

I do not think your very open ended problem is easily answered on a forum.
There could be problems with almost anything; poor configuration, inadequate hardware, poor code etc

Free health check code may be a good place to start but ultimately someone is just going to have to pick their way through a lot of possibilities.


#6

SQL should allocate all available / configured memory, that is normal. (It may take a little while after startup, but "normal running state" is with all appropriate memory allocated.

As a broad-brush answer set SQL Max Memory usage to total installed memory LESS 4GB (to reserve that for O/S). In practice you probably should use a figure taking into account number of processors, and it might be that that level of finesse is no longer needed in SQL2012

My guess is that that has nothing, or very little, to do with your hardware and is caused by the queries not being optimised or there being no maintenance on the server / databases. e.g. indexes that would help do not exist, or the statistics are out of date, or the indexes have grown and become fragmented and have not being rebuilt/reorganised. Might also be that SHRINK has been used on the database regularly and that has badly fragmented the database.

I don't think that is "huge", but "it depends"; it is certainly "quite big". TEMPDB growing every day is not a good sign, but I would expect it to grow initially and then maintain that large size.

We create a TEMPDB of "sufficient size" taking care to ensure that the VLBs are kept to a minimum and optimised. if you go with the defaults you probably have a 10MB TEMPDB which grows in 10MB pieces and is horribly inefficient as a consequence. If your site needs a 60GB TEMPDB then make sure it is created initially at that size (i.e. when SQL Service starts)

That's not a good sign, but might be a symptom of some of the other issues.


#7

to add..

  1. Is this a virtual or physical machine?
  2. Is OS installed on same drive as where the data resides?
  3. How many databases on this server?

etc...


#8

Dear yosiasz,
My Machine is a Virtual Machine.
OS is installed in drive C and Data files are located in drive D
TempDB is Configured in Drive C.
here we have only one Database not more.


#9

Separate drives for Data MDF and Log NDF files would help.

Beyond that a separate drive for backups

Beyond that a separate drive to TEMPDB

I would not put any database files, at all, on C:(i.e. the "operating system drive")

If you are only able to do Step-1 then i could be careful to put Log backups on the Data drive, and Full/Differential backups on the Log drive to provide some redundancy (and perhaps some improvement to performance, but backup files on another drive is better)

Make sure that the drives are separate physical media and, even better, separate disk controllers. Otherwise you still have all-eggs-in-one-basket.


#10

THAT'S the real problem here. You need to chase down the queries that are doing that and fix them. That may mean anything from tweaking the queries (possibly with a well constructed index or making criteria "SARGable") to a total rewrite. You're also working with a half terabyte database. Invest in some more memory. It's one of the best investments you can make but don't think that it'll be a panacea of performance. Bad queries will rip right through that and TempDB. Performance is IN THE CODE.

AND, make sure that statistics are being rebuilt on a regular basis. Without up to date statistics, even good code will appear to be performance challenged because the wrong execution plans will be built.


#11

Ahem! AND SOME DISKS. A C: and D: setup is unlikely to be suitable for a decent sized database :slight_smile:

We code to be tight and perform well and THEN we add hardware if we need to. NOT doing it that way means that Hardware is thrown at the problem, and that runs out really soon ... and its much harder to go back and fix badly performing queries later - at the very least you have to do all the testing and QA all over again because the system is, by then Live and Critical ..., so you can't just chuck code changes at it without proper code management


#12

Sql Server 2012 64 Bit Standard Version only supports 64GB of memory.
You might have to look at upgrading to at least SQL Server 2014/2016 Standard which support 128GB of memory.
If you need more memory you will have to upgrade to the Enterprise Edition.

Before you do anything I think you should actually try and work out what is wrong. Look at your problem queries, run health checks etc.


#13

I agree. work from bottom up. bottom being server setup. no database without a server :wink:
Problematic Virtual setup will be exposed with

  • problematic database setup
  • problematic tables setup
  • problematic stored procedures. etc

Infrastructure cannot be ignored. Ignorance is not bliss.

In this case you have control of all. In some cases you do not have control on the server setup so you focus on what have control on.