I was wondering if anyone has some general sys admin questions on how best to setup MS SQL server for speed. I have placed the log files on a different drive with its own controller and moved the indexes to their drive but on the same controller as the main data files.
我想知道是否有人有一些关于如何最好地设置MS SQL服务器以获得速度的一般系统管理员问题。我已将日志文件放在具有自己的控制器的不同驱动器上，并将索引移动到其驱动器，但与主数据文件位于同一控制器上。
What other way can a system admin setup MS SQL server to speed up disk access, query times?
系统管理员还有什么其他方法可以设置MS SQL服务器以加快磁盘访问，查询时间？
6 个解决方案
#1
17
The question is quite general, so a general answer will fit:
问题很笼统，所以一般的答案都适合：
- Use fast hardware. Have as much RAM as possible.
- 使用快速硬件。拥有尽可能多的RAM。
- If using a 32 bit OS, have AWE working, especially if the machine is a dedicated DB server.
- 如果使用32位操作系统，请使AWE正常工作，尤其是在计算机是专用数据库服务器的情况下。
- If using 64 bit OS - even better. Much more RAM could be put to good use.
- 如果使用64位操作系统 - 甚至更好。可以充分利用更多的RAM。
- Analyze indexes and application performance regularly. When needed, rebuild indexes, create new ones, delete old ones, etc.
- 定期分析索引和应用程序性能。需要时，重建索引，创建新索引，删除旧索引等。
- Learn the different types of indexes - Clustered, Partitioned, etc.
- 了解不同类型的索引 - 群集，分区等。
- When necessary, use indexed-views.
- 必要时，使用索引视图。
- DB Tuning advisor could help.
- DB Tuning顾问可以提供帮助。
- Use a LoadBalancing solution to have multiple servers running the DB.
- 使用LoadBalancing解决方案让多个服务器运行数据库。
- Understand the nature of the application - OLAP apps has other needs than DataWarehousing apps. This would affect the structure of the tables, disk spanning, etc.
- 了解应用程序的本质 - OLAP应用程序还有其他需求，而不是DataWarehousing应用程序。这会影响表的结构，磁盘跨度等。
#2
2
In order of impact on performance, from most important to least:
为了对性能产生影响，从最重要到最不重要：
- Write faster code using appropriate data definitions and indexes
- 使用适当的数据定义和索引编写更快的代码
- Use faster hardware
- 使用更快的硬件
- Configuration tricks of the kind you're asking about here.
- 你在这里询问的那种配置技巧。
While a poorly-configured or under-powered server can kill performance, the typical setup should be good enough that this kind of thing is now pretty far down the list of what you should worry about from a performance perspective.
虽然配置不当或服务不足的服务器可能会降低性能，但典型的设置应该足够好，以至于从性能角度来看，这类事情现在已经远远落后于您应该担心的问题。
Of course, if you have a specific unexplained bottleneck then maybe you need to configure something. But you should share that information as well.
当然，如果你有一个特定的无法解释的瓶颈，那么你可能需要配置一些东西。但是你也应该分享这些信息。
#3
1
Use the Database Engine Tuning Advisor
使用数据库引擎优化顾问
#4
1
First of all, I would try to identify the bottleneck; what is the 'thing' where you can improve most / What is the slowest part of your system ?
首先，我会尝试找出瓶颈;什么是你可以提高最多的“事物”/你系统中最慢的部分是什么？
Is it your code ? (Appropriate indexes, set-based processing, avoid cursors as much as possible, ... ) Is it hardware - related ? Is it configuration-related ? - logs on a separate filegroup / separate disk - can you move some tables to another filegroup , so that tables that are often joined together in queries are in separate filegroups - do you rebuild the indexes often ?
这是你的代码吗？ （适当的索引，基于集合的处理，尽可能避免游标，......）与硬件有关吗？它与配置有关吗？ - 登录单独的文件组/单独磁盘 - 是否可以将某些表移动到另一个文件组，以便通常在查询中连接在一起的表位于不同的文件组中 - 您是否经常重建索引？
#5
0
Speedy RAID configs and loads of memory.
快速的RAID配置和内存负载。
#6
0
Be careful of the DTA (tuning advisor). It all depends on the workload.
小心DTA（调整顾问）。这一切都取决于工作量。
Quick things: Index better. Look for long running queries, or slow ones, and add indexes to support here. If this is OLTP, beware of adding too many indexes.
快速的事情：索引更好。查找长时间运行的查询或慢速查询，并在此处添加索引以支持。如果这是OLTP，请注意添加太多索引。
Write better code, post slow queries somewhere (here, or SQLServerCentral.com) and get ideas for speeding them
编写更好的代码，在某处（这里，或SQLServerCentral.com）发布慢查询，并获得加速它们的想法
More memory
更多的记忆
Separation of logs from data physically helps, but you have to be IO bound for this to make a difference.
从数据中分离日志在物理上有所帮助，但是您必须通过IO绑定才能发挥作用。
#1
17
The question is quite general, so a general answer will fit:
问题很笼统，所以一般的答案都适合：
- Use fast hardware. Have as much RAM as possible.
- 使用快速硬件。拥有尽可能多的RAM。
- If using a 32 bit OS, have AWE working, especially if the machine is a dedicated DB server.
- 如果使用32位操作系统，请使AWE正常工作，尤其是在计算机是专用数据库服务器的情况下。
- If using 64 bit OS - even better. Much more RAM could be put to good use.
- 如果使用64位操作系统 - 甚至更好。可以充分利用更多的RAM。
- Analyze indexes and application performance regularly. When needed, rebuild indexes, create new ones, delete old ones, etc.
- 定期分析索引和应用程序性能。需要时，重建索引，创建新索引，删除旧索引等。
- Learn the different types of indexes - Clustered, Partitioned, etc.
- 了解不同类型的索引 - 群集，分区等。
- When necessary, use indexed-views.
- 必要时，使用索引视图。
- DB Tuning advisor could help.
- DB Tuning顾问可以提供帮助。
- Use a LoadBalancing solution to have multiple servers running the DB.
- 使用LoadBalancing解决方案让多个服务器运行数据库。
- Understand the nature of the application - OLAP apps has other needs than DataWarehousing apps. This would affect the structure of the tables, disk spanning, etc.
- 了解应用程序的本质 - OLAP应用程序还有其他需求，而不是DataWarehousing应用程序。这会影响表的结构，磁盘跨度等。
#2
2
In order of impact on performance, from most important to least:
为了对性能产生影响，从最重要到最不重要：
- Write faster code using appropriate data definitions and indexes
- 使用适当的数据定义和索引编写更快的代码
- Use faster hardware
- 使用更快的硬件
- Configuration tricks of the kind you're asking about here.
- 你在这里询问的那种配置技巧。
While a poorly-configured or under-powered server can kill performance, the typical setup should be good enough that this kind of thing is now pretty far down the list of what you should worry about from a performance perspective.
虽然配置不当或服务不足的服务器可能会降低性能，但典型的设置应该足够好，以至于从性能角度来看，这类事情现在已经远远落后于您应该担心的问题。
Of course, if you have a specific unexplained bottleneck then maybe you need to configure something. But you should share that information as well.
当然，如果你有一个特定的无法解释的瓶颈，那么你可能需要配置一些东西。但是你也应该分享这些信息。
#3
1
Use the Database Engine Tuning Advisor
使用数据库引擎优化顾问
#4
1
First of all, I would try to identify the bottleneck; what is the 'thing' where you can improve most / What is the slowest part of your system ?
首先，我会尝试找出瓶颈;什么是你可以提高最多的“事物”/你系统中最慢的部分是什么？
Is it your code ? (Appropriate indexes, set-based processing, avoid cursors as much as possible, ... ) Is it hardware - related ? Is it configuration-related ? - logs on a separate filegroup / separate disk - can you move some tables to another filegroup , so that tables that are often joined together in queries are in separate filegroups - do you rebuild the indexes often ?
这是你的代码吗？ （适当的索引，基于集合的处理，尽可能避免游标，......）与硬件有关吗？它与配置有关吗？ - 登录单独的文件组/单独磁盘 - 是否可以将某些表移动到另一个文件组，以便通常在查询中连接在一起的表位于不同的文件组中 - 您是否经常重建索引？
#5
0
Speedy RAID configs and loads of memory.
快速的RAID配置和内存负载。
#6
0
Be careful of the DTA (tuning advisor). It all depends on the workload.
小心DTA（调整顾问）。这一切都取决于工作量。
Quick things: Index better. Look for long running queries, or slow ones, and add indexes to support here. If this is OLTP, beware of adding too many indexes.
快速的事情：索引更好。查找长时间运行的查询或慢速查询，并在此处添加索引以支持。如果这是OLTP，请注意添加太多索引。
Write better code, post slow queries somewhere (here, or SQLServerCentral.com) and get ideas for speeding them
编写更好的代码，在某处（这里，或SQLServerCentral.com）发布慢查询，并获得加速它们的想法
More memory
更多的记忆
Separation of logs from data physically helps, but you have to be IO bound for this to make a difference.
从数据中分离日志在物理上有所帮助，但是您必须通过IO绑定才能发挥作用。