How to make SQL Server efficient-difficult (itput discussion summary)

4. What performance problems are confusing to you during your SQL server usage?

Discussion Summary-Comprehensive

L tempdb Problems

A) Row-level and transaction-level snapshots are stored in tempdb (I don't know why the architecture is designed like this). Undo \ redo is naturally inconvenient.

B) tempdb has put too many functions, leading to performance bottlenecks.

Personal Opinion: Tempdb seems to be a bottleneck. Almost every version puts more things into tempdb. More and more things are undertaken by tempdb.

L configuration problems

A) there are not many tasks that can be done in DBA adjustment. There are only two parameters for memory adjustment.

B) I/O adjustment, in addition to file-level separation, What Can DBA do?

C) It seems that the performance adjustment that mssql dba can make is maintenance plan, index optimization and Statistics fragmentation, and SQL statement level adjustment.

D) In terms of memory, the controllability is too low. After a large block of memory is allocated, other DBAs cannot do the same.

E) Performance adjustment. MSSQL provides relatively small DBA Space

Personal Opinion: It is not easy to say how many configuration parameters are configured. Many other configuration items of SQL Server are not publicly stated in the official documentation. In actual application, I feel that there are not many configuration items to be adjusted, without configuration, it can basically adapt to most situations (automatic configuration is still good). As for the daily work of DBA, As long as appropriate methods can be used to ensure the server performance and stability, in addition, appropriate measures should be taken to prevent repeated failures. As to whether the database provides the corresponding support, this can only be analyzed in detail, either not provided or not found.

L load balancing. It seems that there is no good solution on sqlserver.

Personal Opinion: Always on should be a huge step forward, but it does not have a complete solution.

L dB link, which does not seem to be indexed. It is difficult to deal with big data when it involves cross-database data.

Personal Opinion: The query will take the index, but the restriction is higher than that in the instance. In the instance, the query optimizer tries its best to parse the object to the basic object and obtain the cost-related information for evaluation and query; for dB link, the object for obtaining the query cost evaluation information is the query object. If you are querying a table, you can naturally obtain the information that you encounter. If it is not a non-index view, the index is naturally unavailable because no index or statistical information is available for information that is helpful for cost evaluation.

L images and copies coexist in the production environment, while mirror does not seem to have high performance and is often delayed. Viewing wait events always accounts for a large number of waiting events in the image.

Personal Opinion: You can use the image monitor or image-related dynamic views and performance indicators to further determine the latency points to determine feasible improvements.

L sorting and aggregation of tens of millions of large tables has a particularly low performance, which is difficult to optimize. There is no better way to create an independent report server in addition to reasonable indexes, large table partitions, and read/write splitting.

Personal Opinion: Aggregation of a large amount of data should be avoided in regular queries. For example, you can use a job to periodically aggregate historical data into an aggregate table. In this way, the query only needs to aggregate unaggregated parts, then unionall can aggregate the data. Creating an index view containing the aggregated data is also a feasible solution (which will affect the data change efficiency ). In addition, you should consider separating OLTP and OLAP tasks.

L high concurrency of publishing and subscription. Large amounts of DML can easily lead to deadlocks. Simple read/write splitting is a pain in my mind for years.

Personal Opinion: Contains released databases. If a large number of data changes have been made to tables in a short period of time, some problems may occur. The copied Log Reader takes a lot of time to read the logs and extract and generate the commands to be released. If the modified table is the published table, if distribution and publishing share one instance, the distribution pressure will also put pressure on the server, and the copied commands will be recorded (Row-level, therefore, the synchronization cycle of a large number of data changes is also relatively long. If a large number of data changes are feasible, you can consider the method of storing the data by copying them. If the data changes have been implemented, restarting the tables that have produced huge data changes may be a solution to the problem.

L in the past, the result is returned within 1 second after the forceorder is added. It takes 15 seconds to remove the Force Order.

Personal Opinion: The statistical information may be inaccurate, or the condition may contain uncertain values such as variables, resulting in inaccurate cost evaluation by the query optimizer.

L in an SQL instance, the database is constantly increasing, and every database is not very big ...... Will there be a performance bottleneck when 3000 databases are estimated to be reached in the long term?

Personal Opinion: It is easy to see performance bottlenecks. The most common possibility is the silver usage of tempdb resources. After all, each instance has only one tempdb, and many internal stuff will use tempdb more or less; 2nd resources that may compete for use may be connected to resources. In addition, management may also be affected. For example, jobs and login may be too many resources and cannot be easily managed.

L The most frequently queried table is the most frequently updated table. The most updated field is also the most queried field. A typical example is the order table. The Order table is being inserted, and the Order Status field is being updated. On the other hand, order tables are constantly being queried, such as order details that have been placed but not delivered under the current date, order details that have been delivered but not paid, etc. In our system, this order query interface refreshes the latest order status every five seconds as long as it is opened. Note that each client is refreshed every 5 seconds. If there are thousands of online users at the same time, this table is refreshed every second. In this case, do you have any suggestions?

Personal Opinion: Read/write splitting. In addition, consider whether the program design is reasonable. For example, the frequency of a 5-second refresh is to meet the user's needs by 100%, and whether a 1-minute Refresh can meet 90% of the needs. If yes, must it meet 100%? In terms of data refresh methods, can access by DB data be centrally controlled by a single service? If five clients call the service at the same time, the service only needs to fetch data once and distribute the data to five clients. It is not necessary for each client to access the database independently. Further, can I set a flag for Data Change and use incremental data pulling methods?

L when performing the partitioning merge operation or re-indexing on a large table, the CPU usage of the server is very low, and the waiting type of the Process queried from sysprocesses is sos_scheduler_yield, I don't understand why.

Personal Opinion: Check the task allocation of each CPU to see if the allocation difference is too large (one or two CPU usage is high, other idle) Select scheduler_id, current_tasks_count, runnable_tasks_countfrom SYS. dm_ OS _schedulers where scheduler_id <255

L some other factors that affect SQL Server include

A) The new version consumes memory, the CPU processing capability cannot keep up, and the disk size is not large enough, which makes the performance of the old device a bottleneck. The result is only one: retiring and replacing the device, resulting in increased costs, equipment cannot be used properly

B) although the new SQL Server version adds column indexes, there are also bottlenecks in index performance.

C) performance bottleneck of database jobs

D) inefficient SQL statements can reduce the overall performance of the database.

E) bottleneck of T-SQL Performance

F) High concurrency and unstable demand. abrupt peak trading has the greatest impact on databases and applications

G) The CPU usage is high, and the CPU usage will go up again

Personal Opinion: New things do have requirements for hardware updates. After all, the design basis is different from that of the old version. Select the appropriate version as needed, not the latest, it must be the most suitable. Performance problems require a process of tracking and analysis to identify the cause to find an effective solution

 

Discussion post Previous topics:1. What factors do you consider when designing SQL Server objects to avoid performance problems? 2. What factors do you think will affect SQL Server efficiency in writing T-SQL (including stored procedures, functions, and views? 3. What do you think should you pay attention to when designing database operation programs to ensure effective use of the database?