我们有一个Web应用程序(dot net 2008和sql server 2005),总共有1000个用户和大约350个在线用户,用于记录每日收据. 最终用户comapany有700个分支机构,可通过Internet与系统配合使用. 问题在于,大多数报告都很沉重,使系统非常慢. 现在我们有2个服务器 1)IBM Xseries-3650 Intel(R),CPU:X5365 @ 3GHz(8处理器),RAM:16 Gb 2)Intel®Xeon®,cpu:X5650 @ 2.67GHz(24处理器),ram:15.9 Gb 当前,我们在服务器1上托管应用程序,在服务器2上托管数据库(已启用报告服务器) 但应用程序运行缓慢 什么是提高性能和速度的最佳方案和体系结构? 两个服务器都是32位,并且sql AWE是启用的.
解决方案我相信您没有足够的数据来定义最佳架构.性能从来都不是一种适合所有事物的尺寸.您已经有一个系统,所以我要...
- 检查硬件以确保其没有任何故障.
- 运行性能监视器,查看随着时间的推移磁盘/内存/CPU使用情况,交换空间大小/使用情况等,并确保服务器足够胜任这项工作.
- 假设1& 2正常,开始查看哪个组件(webserver/x-tier/database)是瓶颈.基本上是分而治之".
- 性能监视器应包括用于IIS的钉子.随着时间的推移,请对其进行审查.
- 您将必须弄清楚如何衡量中间的任何业务/数据层
- 使用SQL Server Management Studio和SQL Server Profiler来识别SQL服务器瓶颈. Management Studio会显示任何检测到的死锁,因此请先将其检出.然后,我将像这样使用Profiler ...找出速度较慢的报告,然后确定其使用的存储过程/查询.使用Profiler的过滤器来尽可能缩小跟踪范围... 正在寻找一个使用了参数的实际语句.将其返回到Management Studio.打开一个新窗口,粘贴查询,添加包括实际执行计划"(在查询"菜单下),然后运行."会花费更长的时间,但是SQL Server会告诉您瓶颈(以%表示).
- 显然已修复和/或升级了所有硬件问题.
- 重新编码在网页或中间层中发现的所有瓶颈.
- 数据库调优有点棘手...
- 确保SQL Server不执行任何表扫描.您需要索引查找或索引扫描.根据需要添加/重做索引. (可以对临时表和表变量进行表扫描,可以...仔细选择.)
- 如果联接变得过于紧张,可以根据需要对数据进行非规范化.
- 某些表仅具有太多数据无法有效查询.
- 将表分区作为可能的解决方法.
- 考虑预汇总数据.示例:假设您的系统报告过去一周,一个月和/或一年中的收据...,然后执行一个每晚处理,以将所有收据汇总到单独的每周,每月和每年表格中.所有报告均基于这些新表.
- 我实际上会推荐第三台服务器.将数据库放在最大的盒子上,并具有2个相同的负载平衡的Web服务器.通常,我们没有获得所需/想要的硬件,因此,如果您使用两台服务器,则Web/数据库服务器配置可能是最好的选择.始终将数据库放在功能最强大的盒子上.通常这是瓶颈,而且不一定总是可以很好地扩展.
非常感谢代码猴子 我们的问题是报告. 如果我们排除它,一切都会好的. 我们在开发应用程序时使用了严格的标准和性能提示,就像: 1-没有索引扫描 页面上的2视图状态已禁用 3页和脚本非常小 4-项目上没有图像 在列表页面上的5个页面上,我们仅使用前100条记录 6 -... 但是报告查询涉及更多的表和列,我们无法为所有已填充的索引,某些报告返回更多的记录,例如1000或以上. 您能否解释更多有关如何将报告传输到另一个数据库以及如何制作自定义非规范化表以避免多次连接和繁重查询的信息? sql是否具有任何嵌入式功能 还是我们自己应该做? 您能解释一下您的最后几段吗? (我实际上建议使用第3台服务器.将数据库放在最大的盒子上,并有2个相同的负载均衡的Web服务器.通常我们没有获得所需/想要的硬件,因此如果您受困于两台服务器Web/数据库服务器配置可能是您最好的配置.请始终将数据库放在功能最强大的盒子上.这通常是瓶颈,并不总是可以很好地扩展.我只是想帮助你. 首先,您可以检查这篇文章,因为我怀疑您可能有一些系统体系结构"问题您可能要检查.其次,您可能想了解更多,或者至少了解其他人对更好的缓存技术的看法.由于这两个原因,我邀请您查看这两篇文章: 99.99%的ASP.NET和SQL Server SaaS生产体系结构 [ ^ ] 以下是获奖者: 破坏应用程序的十大缓存错误 [ ^ ]
we have a web application(dot net 2008 and sql server 2005) with totally 1000 users and about 350 online users which be used for recording daily receipts. the end user comapany has 700 branches that works with system via internet. the problem is that,the most of reports are heavy and make the system very slow. now we have 2 server 1) IBM Xseries-3650 Intel(R) ,CPU: X5365@ 3GHz (8 processor), RAM: 16 Gb 2) Intel(R) Xeon(R) ,cpu : X5650 @ 2.67GHz (24 processor) , ram: 15.9 Gb currently we host the app at server 1 and the database(report server enabled) at server 2 but the app is slow what is the best scenario and architecture for improving performance and speed? both servers are 32 bit and sql AWE is anabled
解决方案 I believe you don''t have enough data to define the best architecture. Performance is never a one size fits all kinda thing. You have an existing system, so I''d...- Check out the hardware to make sure it''s not having any failures.
- Run the Performance Monitor to look at disk/memory/CPU usage, swap space size/usage, etc... over time and make sure the servers are beefy enough for the job.
- Assuming 1 & 2 are ok, start looking at which component (webserver/x-tier/database) is the bottleneck. Basically "divide and conquer".
- Performance Monitor should include pegs for IIS. Review them over time.
- You''ll have to figure out how to measure any business/data tier in the middle
- Use SQL Server Management Studio and SQL Server Profiler to identify SQL Server bottlenecks. Management Studio shows any detected deadlocks, so check it out first. I"d then use Profiler like this... identify a report that''s slow, then identify which stored procedure/query it uses. Use Profiler''s filters to narrow down the trace as much as possible... you''re looking for an actual statement WITH the parameters used. Take that and go back to Management Studio. Open a new window, paste in the query, add "Include Actual Execution Plan" (under the Query menu), and run. It''ll take longer, but SQL Server will tell show you the bottleneck (as a %).
- Obviously fix and/or upgrade all hardware issues.
- Recode any bottlenecks found in webpages or middle tier(s).
- Database tuning is a bit trickier...
- Make sure SQL Server does not do any table scans. You want either an index seek or index scan. Add/rework indexes as needed. (Table scans on temp tables and table variables MIGHT be ok... choose carefully.)
- De-normalize data as needed if the joins get too intense.
- Some tables have simply too much data to query effectively.
- Look at table partitioning as a possible fix.
- Consider pre-aggregating data. Example: assuming your system reports on receipts in the past week, month, and/or year... then have a nightly process to aggregate all receipts into separate weekly, monthly, and yearly tables. Base all reports on these new tables.
- I actually would recommend a 3rd server. Have the database live on the largest box and have 2 identical, load balanced webservers. Usually we don''t get the hardware we need/want, so if you''re stuck with two servers the web/database server config is probably your best. Always have your database on the most powerful box possible. It''s usually the bottleneck and doesn''t always scale well.
更多推荐
在两台服务器上托管高流量Web应用程序的最佳体系结构和方案?
发布评论