VB.Net应用程序和Sql Server 2005最佳实践 [英] VB.Net Application and Sql Server 2005, Best Practice

问题描述

大家好

我有一个具有一台服务器计算机和大约150个客户端的应用程序.服务器计算机既是域控制器，又是sql服务器.

应用程序中使用两个连接字符串来创建连接对象.

用于正常查询
(1)数据源=<服务器名> ;;初始目录=<数据库名> ;;用户ID = admin;池=真;最小池大小= 5;最大池大小= 200

对于耗时(仅3个报告使用此连接字符串)的查询
(2)数据源=<服务器名称> ;;初始目录=<数据库名称> ;;集成安全性= False;用户ID = admin;异步处理= True; MultipleActiveResultSets = True; Connect Timeout = 0

问题:
有时，例如每月一次，应用程序变得非常缓慢，甚至显示超时.在此期间(所有sps)，存储过程在服务器计算机上以正常速度执行.只要重新启动MS SQL Server 2005，即可解决此问题.

这是我通常用来选择数据的代码示例.
(在adapter.Fill(dt)执行应用程序暂停后不久，因为相同的存储过程很容易在其中运行.)

Hello everyone

I have an application having a server machine and around 150 clients. server machine is the domain controller as well as sql server.

Two connection strings are used in the application to create connection objects.

For normal quering
(1) Data Source=<servername>;Initial Catalog=<databasename>;User ID=admin;Pooling=True;Min Pool Size=5;Max Pool Size=200

For queries that take large time (only 3 reports are using this connection string)
(2) Data Source=<servername>;Initial Catalog=<databasename>;Integrated Security=False;User ID=admin;Asynchronous Processing=True;MultipleActiveResultSets=True;Connect Timeout=0

Problem:
Some times like once in a month, the application becomes very slow, even showing time-out. The stored procedures execute at normal speed at server machine during this time(all sps). This will solved as soon as the MS SQL Server 2005 is restarted.

This is a sample of code which i generally use to select data...
(soon after adapter.Fill(dt) is executing the application halts, where as the same stored procedure runs with ease.)

public DataTable CollectionByTerminal(DateTime from, DateTime to)
      {
          SqlCommand command = new SqlCommand("sp_name", connection);
          command.CommandType = CommandType.StoredProcedure;
          command.Parameters.AddWithValue("@From", from);
          command.Parameters.AddWithValue("@To", to);

          SqlDataAdapter adapter = new SqlDataAdapter(command);
          DataTable dt = new DataTable();

          try
          {
              adapter.Fill(dt);
              return dt;
          }
          catch (Exception error)
          {
              throw new Exception(error.Message);
          }
          finally
          {
              adapter.Dispose();
              CloseConnection();
          }
      }

请在以下方面帮助我
将同一台计算机用于数据库服务器和域控制器是否存在任何问题?
连接字符串有任何问题吗?
使用连接池，异步处理或MultipleActiveResultSets是否有任何问题?
调用函数有什么问题吗?

我相信这些问题的答案将对我以及成千上万的新程序员有所帮助

非常感谢您
Anoop

Please help me in following areas
Is there any problem by using the same machine for database server as well as domain controller?
Is there any problem with the connection strings?
Is there any problems of using connection pooling, Asynchronous Processing or MultipleActiveResultSets?
Is there any problem in the calling function?

I believe answers to these questions will help me as well as thousands of new programmers

Thank you very much
Anoop

推荐答案

如果重新启动后您的sql server变得更好，则您存在内存限制问题.

为服务器购买更多的RAM.如果您无法再每隔几周的午夜重新启动服务.

If your sql server gets better after a restart then you have memory limit issues.

Buy more RAM for the server. If you can''t then restart the service at midnight every couple of weeks.

这篇关于VB.Net应用程序和Sql Server 2005最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！