主页 / dba / 问题 / 153159
Accepted
Ivan McA
Asked: 2016-10-25 00:59:24 +0800 CST

为使用完全相同的查询重复访问数据库的应用程序优化 SQL Server？

  • 772

我正在处理后端使用 SQL Server Express (2014) 的 Windows 应用程序的性能问题。

我主要通过检查索引 SQL Server 端设法使它运行得更好，但是有一个特定的报告仍然运行得非常慢。

查看它在做什么，它似乎在应用程序中循环并SELECT *针对一个表查询出数千个非常简单的查询WHERE = Primary Key，因此在每种情况下只检索一条记录。当我说 IDENTICAL 时，我的意思是相同，它甚至没有改变主键来获得不同的东西，它显然每次需要时都要求从数据库中返回完全相同的记录，仅在少数情况下多达一百次秒。

这是一个示例报告，在服务器安静时运行大约需要 10-15 秒——查询运行了多少次我添加为评论：

SELECT * FROM "Patient" WHERE "_Recno" = 35051  -- (runs 106 times)
SELECT * FROM "Client" WHERE "_Recno" = 15607   -- (99 times)
SELECT * FROM "SpeciesEntry" WHERE "_Recno" = 180   -- (97)
SELECT * FROM "Table" WHERE "_Recno" = 9    -- (97)
SELECT * FROM "DefaultEntry" WHERE "_Recno" = 2615  -- (96)
SELECT * FROM "Table" WHERE "_Recno" = 34   -- (96)
SELECT * FROM "DefaultEntry" WHERE "_Recno" = 2562  -- (84)
SELECT * FROM "Table" WHERE "_Recno" = 33   -- (84)
SELECT * FROM "Treatment" WHERE "_Recno" = 1682 -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 1819 -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 927  -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 934  -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 935  -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 940  -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 942  -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 944  -- (33)
SELECT * FROM "OptionWP" WHERE "_Recno" = 103   -- (3)
SELECT * FROM "OptionWP" WHERE "_Recno" = 54    -- (1)
SELECT * FROM "PatientEstimate" WHERE "_Recno" = 8928   -- (1)
SELECT * FROM "Phrase" WHERE "_Recno" = 9718    -- (1)
SELECT * FROM "Table" WHERE "_Recno" = 4    -- (1)
SELECT * FROM "BreedEntry" WHERE "_Recno" = 3283    -- (1)

查询后的数字是执行确切查询的次数，例如，使用 _RecnoSELECT * FROM "Patient" WHERE "_Recno" = 35051执行查询 106 次。实际上有 1,031 个查询正在执行以构建此报告（在本例中，它有所不同）- 以上 23 个左右是不同的查询。

现在上面的每个查询都运行得非常非常快，在每种情况下我们都在谈论几十微秒。事实上，如果将用于制作此报告的所有 1,031 个查询加起来，所有这些查询所用的总时间仅为 59,193 微秒，或仅 59 毫秒。

所以问题和延迟似乎是开销——尽管在此报告中只有大约 59 毫秒的实际数据库时间，但报告需要大约 10-15 秒才能为客户端运行，因为它来回处理超过 1,000 个查询。

请注意，在大多数情况下，客户端应用程序和 SQL Server 在同一台机器上，并且客户端的多个实例通过 RDP 访问。在少数情况下，客户端位于 LAN 上的另一台机器上，我认为那里的性能会更差。但在大多数情况下，您可以认为不应该存在网络问题，因为客户端应用程序和 SQL Server 都在同一个物理盒子上。

十秒甚至可以接受，问题是在繁忙的时间可能会增加到一分钟或更长时间。

关于如何处理优化这个的任何想法？如果它是一个我可以访问源代码的应用程序，显然我会用一个或几个使用连接的查询替换所有这些，但这不是一个选项，该应用程序是一个黑盒子——我所能做的就是从 SQL Server 端优化。

与客户交谈时，虽然无论他们是通过 RDP 还是远程客户端应用程序安装使用它，性能都很差，但远程客户端应用程序的性能要差得多，这对他们来说更像是一个问题。因此，任何有关我可以查看以提高那里的性能的建议，关于网络或其他方面的建议，将不胜感激。需要注意的一件事是，这个 SQL 2014 框现在已经虚拟化了，以前他们使用的是我认为是 2008 年或 2012 年，但它没有虚拟化——他们说这份报告那时更快。他们有其他原因希望将其虚拟化，将其从虚拟化中移除不是一种选择。

它使用 Windows 身份验证和（我很确定）TCP/IP 进行连接。我认为我无法改变这一点。据我所知，它并没有断开和重新建立连接，它似乎至少在使用连接池。

我在日常工作中使用 Hibernate，之前我遇到过这种情况，ORM 生成了数千个查询，而我通常的解决方案是查看代码中的获取策略（惰性加载与急切加载）或者实际上在报告的情况下，通常用 SQL 重写整个事情。在这种情况下，虽然软件就是它的本质，一个 Windows 可执行文件，对此我无能为力，但我只能解决 SQL 方面的问题。

我的理解是供应商不再支持这个特定版本，并且已经回到使用平面文件而不是 SQL 的版本。这对客户不起作用——他们将这个数据库与其他各种东西集成在一起。它是小众软件，就像大多数此类软件一样，它在后端技术上很糟糕，但具有小众用户需要的功能。无论如何，我无法更改软件，只能更改 SQL Server 上的内容。它无法使用，但我已经可以使用了，因此在这些限制范围内取得了进展。

没有任何阻塞或锁定，我已经检查过了。这实际上是我解决的主要性能问题，但在过去一个月左右的时间里，根本没有任何阻塞足够长的时间来记录。内存并不是真正的问题，因为 SQL Server Express 无论如何都限制为 1GB。虽然我不认为它有内存问题，但从它的角度来看，磁盘似乎是最大的硬件瓶颈。

sql-server sql-server-2014

2 个回答

  1. Best Answer

    相同的查询不是问题，并且在某些方面有所帮助，因为执行计划已缓存并且查询所需的数据页仍应缓存。那么问题往往是身份验证和初始化会话的每个连接开销。

    首先要研究的是：是否使用了“连接池”？您可以使用 SQL Server Profiler 对此进行测试，在“存储过程”类别中选择“RPC:Completed”事件，确保选中该事件的“TextData”、“ClientProcessID”和“SPID”（在至少，您可以根据需要选择其他列）。然后，转到“列过滤器”，选择“TextData”，并在“喜欢”条件中添加以下条件：exec sp[_]reset[_]connection。现在运行该跟踪。如果您看到exec sp_reset_connection的实例通过，那是因为使用了连接池。但这并不一定意味着是这个应用程序在使用它。所以看看其中一个“SPID”

    SELECT sssn.login_time,
       DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time)
                  AS [MillisecondsBetweenConnectionAndSessionStart],
       conn.*,
       sssn.[program_name],
       sssn.host_process_id,
       sssn.client_interface_name,
       sssn.login_name,
       qry.[text]
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sssn
        ON sssn.session_id = conn.session_id
OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) qry
WHERE conn.session_id <> conn.most_recent_session_id
OR    DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time) > 50
ORDER BY conn.connect_time;

    最右边/末端的字段执行了最近的查询。这应该确认会话是有问题的应用程序（通过它正在做什么）。

    如果您找不到任何连接池被普遍使用或至少用于此应用程序的证据，则需要更新该应用程序使用的连接字符串以启用连接池。

    ...在大多数情况下，客户端应用程序和 SQL Server 在同一台机器上，并且客户端的多个实例通过 RDP 访问。在少数情况下，客户端位于 LAN 上的不同机器上......

    根据问题中的上述信息，似乎有多个客户端正在连接，对吗？如果是这样，那么连接池虽然可能仍然是一个好主意和帮助，但效果会降低，因为每个客户端都维护自己的连接池。意思是，5 个实例（或任意数量的实例）仍会为连接创建 5 个单独的池，每个实例都会减少其各自应用程序的连接启动开销，但不能将开销减少到超过该开销/减少到单个共享连接）。另请记住，即使使用连接池，如果应用程序在尝试打开新连接之前未正确关闭其连接，您仍将有多个连接/会话来自应用程序的给定实例。

    在这种情况下，并且在单个应用程序未使用连接池且无法更新其连接字符串以使用连接池的情况下，如果可能根本更新应用程序，将非常有助于实现缓存层，例如 Redis 或 memcache（我相信 AWS 和 Azure 都提供基于云的缓存解决方案）。这些重复的命中，通常可以被缓存并完全绕过 RDBMS（当然是指定的时间），这就是这些东西存在的很大一部分原因。

    现在我刚刚了解了最近对该问题的评论，看来很可能无法修改连接字符串或应用程序的任何部分。在这种情况下，除了可能检查与 SQL Server 在同一台服务器上运行的应用程序建立的连接是否正在使用共享内存或 TCP/IP 进行连接，以及它是否是 TCP 之外，我们无能为力。 /IP 用于同一服务器连接，然后检查是否为 SQL Server 启用了共享内存协议，如果没有，则启用它。这不是一个保证的改进，因为连接字符串可能强制协议为 TCP/IP（例如使用语法：）server=tcp:{something}，但仍然值得尝试，因为这种语法可能没有被使用。

    更新

    来自对此答案的评论：

    [上面的查询] 什么也没给我，但寻找sys.dm_exec_connections.connect_time有问题的应用程序是几个小时到几天前的事情。...在任何情况下...连接和会话开始之间的差异始终低于 50 毫秒（3 到 16 之间）

    这可以很好地说明问题，或者至少说明问题的很大一部分。如果连接是在几个小时到几天前建立的，并且会话在之后立即开始，那么该应用程序是一个桌面应用程序，它建立一个单一的连接和会话，它执行所有查询（类似于 SSMS 查询选项卡的工作方式），或者应用代码每次都错误地没有关闭连接对象，在这种情况下，您可能会看到很多并发连接，其中许多实际上已被放弃但仍在占用内存（在 SQL Server Express 上，可能有一个连接无论如何限制）。

    尝试以下查询，改编自上面的原始查询：

    SELECT conn.connect_time,
       sssn.login_time,
       CASE WHEN conn.last_read > conn.last_write THEN conn.last_read
            ELSE conn.last_write END AS [LastActivity],
       GETDATE() AS [Now],
       DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time)
                  AS [MillisecondsBetweenConnectionAndSessionStart],
       DATEDIFF(MILLISECOND, sssn.login_time, CASE WHEN conn.last_read > conn.last_write
                                               THEN conn.last_read ELSE conn.last_write END)
                  AS [MillisecondsBetweenSessionStartAndLastActivity],
       DATEDIFF(MILLISECOND, CASE WHEN conn.last_read > conn.last_write
                         THEN conn.last_read ELSE conn.last_write END, GETDATE())
                  AS [MillisecondsBetweenLastActivityAndNow],
       sssn.[program_name],
       sssn.host_process_id,
       sssn.client_interface_name,
       sssn.login_name,
       qry.[text],
       conn.*
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sssn
        ON sssn.session_id = conn.session_id
OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) qry
WHERE  sssn.is_user_process = 1
ORDER BY [MillisecondsBetweenLastActivityAndNow] DESC;

    如果正在使用连接池，那么您将看到该字段具有高值MillisecondsBetweenConnectionAndSessionStart但该字段的值低得多的行MillisecondsBetweenSessionStartAndLastActivity。原因是建立并重新使用了连接。每次重新使用连接时，login_time重置为最近的SqlConnection.Open事件，然后立即执行查询。

    如果您有一个具有稳定连接的桌面应用程序（如 SSMS），您将看到与连接池相反的行为：您将拥有MillisecondsBetweenConnectionAndSessionStart字段值较低但字段值高得多的行MillisecondsBetweenSessionStartAndLastActivity。原因是连接一旦建立，就永远不会关闭，只是继续对其执行查询。

    如果您有一个应用程序没有关闭其连接，但仍在打开新连接（由于错误或误解了连接池的工作方式——无论是否启用了池），那么您不仅会有很多行，但它们的MillisecondsBetweenConnectionAndSessionStart字段值较低，但字段值较高MillisecondsBetweenLastActivityAndNow。如果建立连接，使用一次，然后SqlConnection.Open()在调用SqlConnection.Close()或之前调用SqlConnection.Dispose()（Dispose()将调用，如果对象是在构造中创建的Close()，将自动调用），就会发生这种情况。SqlConnectionusing()

    • 4

  2. 如果您无法更改查询，则无法对其进行优化。从您发布的数字来看，几乎所有时间都没有花在查询执行上。

    现在上面的每个查询都运行得非常非常快，在每种情况下我们都在谈论几十微秒。事实上，如果将用于制作此报告的所有 1,031 个查询加起来，所有这些查询所用的总时间仅为 59,193 微秒，或仅 59 毫秒。

    因此，即使您将查询执行优化为零，也不会有太大影响。

    （实际上，我不太相信 10us 的数字。即使是SELECT NULL在开放连接的共享内存传输上使用 ADO.NET 执行时，端到端也需要 ~150us。但从质量上讲，这很可能是正确的。）

    • 2

相关问题