使用我的网站时 Microsoft SQL 逐渐增加 RAM 消耗

Question

我正在开发一个托管在 Apache 8.5 上的网站，该网站使用 Hybernate 和 entity/dao/bean 结构连接到 Microsoft SQL 服务器。

当我启动 MS SQL 所在的服务器并打开任务管理器时，我可以看到它消耗了大约 500 Mb 的 RAM。然后我开始使用我的网站（一些公司客户已经访问过该网站），该网站经常关闭并重新打开，结果就是 MS SQL 服务器的 RAM 消耗开始增长（Apache 本身仍然很好）。这种情况一直持续到出现 Java 堆空间错误，我不得不重新启动机器（实际上是一台云服务器）。

我的问题是：什么可能导致 SQL Server 逐渐增加其 RAM 消耗？

如果是 Apache 遇到该问题，我会怀疑发生了一些内存泄漏，尽管我的网站是基于 Java 的，并且垃圾收集器应该在 JHS 错误发生前的 3-4 天内运行过几次。但这不是 Apache，而是 SQL 服务器本身！如果我不必在代码中放置对象 = null，因为 Java 的 GC 会负责释放内存，我是否需要在实体/dao/bean 代码中实现类似的东西？

下面是我关于数据库的代码示例（注意：为了减小文本大小，删除了一些小细节）：

实体：

@Entity
@Table(name = "BANDEIRAS", schema="dbo", catalog=Global.DATABASE_NAME
)
public class Bandeiras implements java.io.Serializable {

    private BigDecimal id;
    private String descricao;
    private Double valorKwh;

    public Bandeiras() {
    }

    public Bandeiras(BigDecimal id) {
        this.id = id;
    }

    public Bandeiras(BigDecimal id, String descricao, Double valorKwh) {
        this.id = id;
        this.descricao = descricao;
        this.valorKwh = valorKwh;
    }

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    public BigDecimal getId() {
        return this.id;
    }

    public void setId(BigDecimal id) {
        this.id = id;
    }

    @Column(name = "DESCRICAO", length = 30)
    public String getDescricao() {
        return this.descricao;
    }

    public void setDescricao(String descricao) {
        this.descricao = descricao;
    }

    @Column(name = "VALOR_KWH", precision = 126, scale = 0)
    public Double getValorKwh() {
        return this.valorKwh;
    }

    public void setValorKwh(Double valorKwh) {
        this.valorKwh = valorKwh;
    }
}

道：

public class BandeirasDao implements Serializable {

    private Session session = null;
    private List<Bandeiras> list;

    public BandeirasDao() {
    }

    public List<Bandeiras> getListBandeiras() {
        try {
            session = HibernateUtil.getSessionFactory().openSession();

            session.beginTransaction();

            Criteria cri = session.createCriteria(Bandeiras.class);        
            this.list = cri.list();
        }
        catch (Exception e)
        {
            if (session.isOpen())
                session.close();
        }   

        return list;
    }

    public boolean edit(Bandeiras b) {
        boolean editado;
        try {
            session = HibernateUtil.getSessionFactory().openSession();
            session.beginTransaction();
            session.update(b);
            session.getTransaction().commit();
            editado = true;

            session.close();
        } catch (Exception e) {
            editado = false;

            if (session.isOpen())
                session.close();
        }
        return editado;
    }

    public boolean create(Bandeiras b) {
        try {
            session = HibernateUtil.getSessionFactory().openSession();
            session.beginTransaction();
            session.save(b);
            session.getTransaction().commit();

            session.close();

            return true;
        } catch (Exception e) {
            session.getTransaction().rollback();

            if (session.isOpen())
                session.close();

            return false;
        }
    }

    public boolean remove(Bandeiras b) {
        try {
            session = HibernateUtil.getSessionFactory().openSession();
            session.beginTransaction();
            session.delete(b);
            session.getTransaction().commit();

            session.close();

            return true;
        } catch (Exception e) {
            session.getTransaction().rollback();

            if (session.isOpen())
                session.close();

            return false;
        }
    }
}

豆角，扁豆：

@ManagedBean(name = "bandBean")
@SessionScoped
public class BandeirasBean implements Serializable {

    private Bandeiras bandeira;
    private Bandeiras novaBandeira;
    private Bandeiras auxBandeira;
    private BandeirasDao bandeiraDao;
    private ArrayList<Bandeiras> ListBandeira;
    UsuariosBean usrBean;

    public Bandeiras getAuxBandeira() {
        return auxBandeira;
    }

    public void setAuxBandeira(Bandeiras auxBandeira) {
        this.auxBandeira = auxBandeira;
    }

    public BandeirasBean() {
        this.bandeira = new Bandeiras();
        this.novaBandeira = new Bandeiras();
        this.bandeiraDao = new BandeirasDao();
    }

    public Bandeiras getBandeira() {
        return bandeira;
    }

    public void setBandeira(Bandeiras b) {
        this.bandeira = b;
    }

    public Bandeiras getNovaBandeira() {
        return novaBandeira;
    }

    public void setNovaBandeira(Bandeiras nB) {
        this.novaBandeira = nB;
    }

    public BigDecimal getId() {
        return this.bandeira.getId();
    }

    public ArrayList<Bandeiras> getListBandeiras() {
        ListBandeira = null;
        ListBandeira = new ArrayList(this.bandeiraDao.getListBandeiras());
        return ListBandeira;
    }

    public ArrayList<SelectItem> getComboListTarifas(BigDecimal empr) {
        ArrayList<Bandeiras> tmp = getListBandeiras();
        ArrayList<SelectItem> itemCombo = new ArrayList<>();

        for (Bandeiras bd : tmp) {
            SelectItem si = new SelectItem();
            si.setLabel(bd.getDescricao());
            si.setValue(bd.getId());
            itemCombo.add(si);
        }

        return itemCombo;
    }

    public Bandeiras getDadosEditBandeiras() {
        return auxBandeira;
    }

    public void getBandeirasEdit(BigDecimal id) {
        auxBandeira = null;
        auxBandeira = new Bandeiras();

        getListBandeiras();

        for (Bandeiras b : ListBandeira) {
            if (b.getId().equals(id)) {
                auxBandeira = b;
                break;
            }
        }
    }

    public void removeBandeira(BigDecimal id) {
        auxBandeira = null;
        auxBandeira = new Bandeiras();

        getListBandeiras();

        for (Bandeiras b : ListBandeira) {
            if (b.getId().equals(id)) {
                auxBandeira = b;
                break;
            }
        }

        if (this.bandeiraDao.remove(auxBandeira)) {
            String correctText = "Bandeira excluída com sucesso";

            FacesContext.getCurrentInstance().addMessage(
                    "datatable_form:hiddenInput",
                    new FacesMessage(FacesMessage.SEVERITY_INFO,
                            correctText, ""));

            auxBandeira = null;
        } else {
            String correctText = "Erro na remoção da bandeira";

            FacesContext.getCurrentInstance().addMessage(
                    null,
                    new FacesMessage(FacesMessage.SEVERITY_WARN,
                            correctText, ""));

            auxBandeira = null;
        }
    }

    public void editBandeira(String hidden_info)
    {
        try{
            if(new BandeirasDao().edit(this.auxBandeira)){
                String correctText;

                FacesContext.getCurrentInstance().addMessage(
                        hidden_info,
                        new FacesMessage(FacesMessage.SEVERITY_INFO,
                        correctText, " "));
            }
            else{
                String correctText;

                FacesContext.getCurrentInstance().addMessage(
                        hidden_info,
                        new FacesMessage(FacesMessage.SEVERITY_INFO,
                        correctText, " "));
            }
        }
        catch(Exception e){
            //this.auxBandeira = new Bandeiras();
        }    
        finally{
            //this.auxBandeira = new Bandeiras();
        }
    }

    public String criaTarifa(String page) {
        //BigDecimal emp = new BigDecimal(idEmp);
        //EmpresasDao eprsa = new EmpresasDao();
        //novaBandeira.setEmpresas(eprsa.getUser(emp));
        if (this.bandeiraDao.create(novaBandeira)) {
            String correctText;

            FacesContext.getCurrentInstance().addMessage(
                    "datatable_form:hiddenInput",
                    new FacesMessage(FacesMessage.SEVERITY_INFO,
                            correctText, ""));
            novaBandeira = new Bandeiras();
            return page;
        } else {
            String correctText;

            FacesContext.getCurrentInstance().addMessage(
                    null,
                    new FacesMessage(FacesMessage.SEVERITY_WARN,
                            correctText, ""));
            novaBandeira = new Bandeiras();

            return page;
        }
    }

    public String editTarifa(Bandeiras b, String page) {
        if (this.bandeiraDao.edit(b)) {
            String correctText;

            FacesContext.getCurrentInstance().addMessage(
                    "datatable_form:hiddenInput",
                    new FacesMessage(FacesMessage.SEVERITY_INFO,
                            correctText, ""));
            return page;
        } else {
            String correctText;

            FacesContext.getCurrentInstance().addMessage(
                    null,
                    new FacesMessage(FacesMessage.SEVERITY_WARN,
                            correctText, ""));
            return page;
        }
    }

    public UsuariosBean getUsrBean() {
        return usrBean;
    }

    public void setUsrBean(UsuariosBean usrBean) {
        this.usrBean = usrBean;
    }
}

Answer 1

这是 MS SQL Server 引擎的正常行为：它使用我们允许其消耗的所有 RAM 进行缓存。

您可以通过一个简单的配置来限制这一点：

• 使用 SQL Management Studio 连接到实例
• 右键单击实例名称/特性
• 在记忆选项卡上，您可以设置最大服务器内存值为系统和应用程序提供足够的内存

步骤很详细这里

问候，