1660220 - Microsoft SQL Server: Common misconceptions

Some widely accepted information about the Microsoft SQL Server database is shown not to be entirely correct upon closer examination. This Knowledge Base Article lists widespread incorrect assumptions about Microsoft SQL Server and explains why they are wrong.

1. The Microsoft SQL Server Agent job Update_Tabstats updates the database statistics which are used by the database optimizer to calculate execution plans and therefore it is critical for performance if the job fails.
2. When using recover model simple the transaction log cannot run full.
3. The result set of database accesses is always ordered by the primary key even if no ORDER BY clause is used explicitly.
4. If database accesses hang for a long time, the problem is caused by a deadlock.
5. Updating Microsoft SQL Server database statistics manually (with a SQL Server Agent job, with a SQL Server Maintenance Plan or by other means) is part of maintenance and therefore required on a regular basis.
6. Reorganizing some or all database objects is a required maintenance task and should therefore be carried out on a regular basis.
7. DBCC CHECKDB and DBCC CHECKTABLE with option repair_allow_data_loss allow you to repair database inconsistencies and will not cause any data loss.
8. After archiving or deleting data from a table the table and its indexes will occupy less space in the database and the database itself will also occupy less space.
9. If the autogrow option is configured for all datafiles Microsoft SQL Server will grow all files in a balanced way.
10. High memory paging may cause the database to go in SUSPECT status.

• SAP Systems based on SAP NetWeaver;
• Microsoft SQL Server;

corruption, DB02, execution path, update statistics, repair_allow_data_loss, autogrow, paging, page out, paged, suspect status, suspect mode , KBA , BC-DB-MSS , SQL Server in SAP NetWeaver Products , BW-SYS-DB-MSS , BW on Microsoft SQL Server , How To