The 13th article on typical DBA errors MS SQL Server

Foreword

This concise article will provide an overview of the errors, the consequences of which were very tangible and with which I had to face.

The article is written with the aim of non-repetition of these errors. And, as they say, negative experience is also experience and sometimes even more valuable than positive.

Errors

1. Percentage increment of database files (database)
   
   T to the growth of a file (whether data or a transaction log) of a DB is a very resource-intensive operation, then it might be well-intentioned to set this growth in percentages. I agree, many of the recommendations say that it is better to exhibit not a percentage, but a fixed increase, expressed in MB. However, it is not disclosed why. On the basis of practice, it is not recommended to set a database file increment higher than 1 GB, t to MS SQL Server will allocate 1 GB 2 times faster than 2 GB at once. Also, if you allocate less than 32 MB (based on practice again), then sooner or later the database itself just starts to hang. Well, we decided that incrementing the database files is fixed from 32 to 1024 MB. But why still it is impossible to indicate the percentage of database files as a percentage? It turns out that as soon as the file becomes less than 1 MB, the DBMS rounds this value to 0 MB and stops increasing this file. The result is a simple system. To find out how much to increase the file, it is enough to do the analysis in a day-how much each file grows in MB, and set the corresponding number, but in the range from 32 to 1024 MB. Collecting statistics on the growth of database files can be obtained as follows .
2. A lot of foreign keys on the table
   ')
   Have you ever tried to look at a plan when deleting at least one row from a table that almost hundreds of other tables refer to? You'd be surprised how many nested loops there are. And all of them are checks on foreign keys. Therefore, if the tables are large (one millionths), then it is better to turn off the foreign keys on the table in which the data will be deleted, then delete all the necessary and associated data, and then turn on the foreign keys. The situation is similar with cascading updates and deletes. If there are a lot of external connections (hundreds), then even deleting 1 line can lead to a long and very extensive blocking.
3. A lot of extra indexes
   
   You can often find in the recommendations that when creating foreign keys, it is necessary to build indexes for them, especially when using these keys for connections. It is necessary to check that the indices are used, otherwise these extra indices will only slow down any data modification operations. You can check the use of indexes with the following query:
   
   
   Code

   select DB_NAME(t.database_id) as [DBName] , SCHEMA_NAME(obj.schema_id) as [SchemaName] , OBJECT_NAME(t.object_id) as [ObjectName] , obj.Type as [ObjectType] , obj.Type_Desc as [ObjectTypeDesc] , ind.name as [IndexName] , ind.Type as IndexType , ind.Type_Desc as IndexTypeDesc , ind.Is_Unique as IndexIsUnique , ind.is_primary_key as IndexIsPK , ind.is_unique_constraint as IndexIsUniqueConstraint , t.[Database_ID] , t.[Object_ID] , t.[Index_ID] , t.Last_User_Seek , t.Last_User_Scan , t.Last_User_Lookup , t.Last_System_Seek , t.Last_System_Scan , t.Last_System_Lookup from sys.dm_db_index_usage_stats as t inner join sys.objects as obj on t.[object_id]=obj.[object_id] inner join sys.indexes as ind on t.[object_id]=ind.[object_id] and t.index_id=ind.index_id where (last_user_seek is null or last_user_seek <dateadd(year,-1,getdate())) and (last_user_scan is null or last_user_scan <dateadd(year,-1,getdate())) and (last_user_lookup is null or last_user_lookup <dateadd(year,-1,getdate())) and (last_system_seek is null or last_system_seek <dateadd(year,-1,getdate())) and (last_system_scan is null or last_system_scan <dateadd(year,-1,getdate())) and (last_system_lookup is null or last_system_lookup <dateadd(year,-1,getdate())) and t.database_id>4 and t.[object_id]>0 --   

   
   
   
   
4. Irrational use of resources
   
   You can often find in the recommendations that you need to transfer the transaction log and the database data file to different data carriers. If you use RAID 10 with 4 or more SSD-drives, then there is no point in isolating files from each other. For even greater speed, if necessary, the tempdb database can be placed on a disk that was formed from RAM. Also, too large amounts of RAM, which are provided by the DBMS, will lead to the fact that the latter will fill the entire memory with irrelevant query plans.
5. Broken backups
   
   It may sound trivial, but you should always not only check the created backup copies, but also transfer them to the test bench and restore them. And all this needs to be automated with the subsequent notification to administrators about both problematic and successful restorations.
6. False failover
   
   Before you make a cluster of two or more servers, you need to make sure that the storage system is also fault tolerant, that is, if the latter fails, all fault tolerance will be nullified.
7. Difficult diagnostics without simple checks
   
   If a simple system occurs, then you must first check the MS SQL Server logs, and then dig in more detail, that is, often all the problems are written there. Not carrying out simple checks is the same as not measuring the patient's temperature, but carrying out complex diagnostics immediately.
8. Forgotten tables
   
   Tables may swell with unnecessary old data that must either be archived into a separate database or deleted. Tables may also cease to be used. It is necessary to remember this.

These are all 8 negative experiences that I have had to face.
Do not repeat the above errors.

Sources:

» SQL documentation
» Automation to collect data on the growth of tables and files of all databases