Novice SQL Server database DBA needs to be aware of the small details

Preface: Any optimization and modification are based on the business situation, there may be some wrong or inaccurate writing, welcome you to make bricks.

1. When creating DB, the self-growth proposal is set to grow by MB (M), the step size is set according to the business volume, the general recommendation is to set the 100-200m (see picture), and try not to change the initial sizes, this default is good.

If the data file (MDF) according to the default growth of 1m, while the business of the DB is more frequent, the database will frequently request space to disk, will cause unnecessary IO consumption, but also because the application space caused by resource waiting and so on.

If the log file (LDF) is increased by the default percentage of 10%, the same when the DB business is large or has a large transaction, the log will rub the growth, if the log file size is 100G, then each growth step is 10G, you can think about if the database suddenly like a disk to request 10 g space , then what the IO situation would be like.

2. It is recommended to reserve some memory for the system. Based on memory size reservation (server 64g memory generally I limit SQL Server to 60g, leaving 4 G for the system, or that sentence according to business conditions). Because SQL Server has some operations that do not consume the memory reserved by the system for SQL Server. For example, your server memory is 64G, and now SQL Server process consumes 50g, this time if you do backup operation, this operation consumes not SQL Server Occupy 50g, but the remaining 14g memory. There are other operations that also consume non-SQL Server memory, such as index rebuilds (the memory blurs the accuracy to be verified). If SQL Server now fills up 64g of memory, then you need to back up, where does the memory come from? You can only let SQL Server release some of the objects in the cache to give you backup memory. If you are busy now, you may be having a performance problem due to low memory or memory hits.

3.profiler is a good thing, who knows with whom.

4.windows Performance Monitor is the tool that monitors SQL Server performance essentials (friendly tip: shortcut keys are perfmon.msc). Specific monitoring indicators I will write in other blogs.

5. If the firewall in your room is enough to abuse, the program code is strict enough, the database does not have a public IP, then you can use xp_cmdshell this stored procedure, otherwise please close this config.

There was a SQL Server servers in front of me I did not unified management, and then I forgot the server login password. So I use sa password to log in this database, successfully through the xp_cmdshell added server administrator account, successfully through this administrator account landed on the server. This is supposed to be a happy story, but have you ever thought that if someone knows your database connection string through an illegal program, then you start xp_cmdshell again, then you can prepare your resume.

6. Do not tick the login box above the Save Password option (don't ask me why, wayward, is worried that one day my server was someone else landed on the database can be entered, casually delete my data destroyed my db how!! ）。

7. Try not to use SSMs to log in if you are not in special need (especially when your server is not used by you alone). For example, you 192.168.1.3 this server with SSMS login through the IP account password 192.168.1.4 This server's db, this is no problem, but when you use the end to try to develop the habit of turning off the connection. Otherwise, the next person or you will not notice that the script you want to execute on this computer executes to the far end of the db.

8. The path to the DB physical file, try not to use the default path, you have to build a certain drive letter db or data folders such as storage, or find it is a very painful thing is not easy to manage. Do not build directly in the root directory of the disk, because it may sometimes involve the issue of permissions to make your eggs ache for a long time. If there are 2 disk arrays (such as a raid10 one raid0) or 2 separate physical disks, it is best to separate the MDF files from the LDF files to improve IO performance (MDF is placed on the RAID10 and LDF is placed on the RAID0).

9. When backing up the database, try to select the minimum amount of traffic when the server load is minimal, if you can choose to compress the backup, save disk space (of course, additional IO is generated). Log backups do not need to be too frequent or too long, depending on the amount of traffic and the extent to which data is allowed to be lost.

10. If there is a job other than backup in this instance, try not to select the hour of the hour to run, forward or backward for a few minutes, because the general log backup will be chosen interval of one or more minutes, most of which is the whole point of backup. So the other jobs try to avoid the time of this backup operation.

11. The job monitor can only see the status of the last execution of the job.

12. Check the SQL service startup time: SELECT login_time from MASTER. sysprocesses WHERE spid= 1

do not let the program use SA as the database connection string, sa password as complex as possible, SA weak password of the harm needless to say.

14. The shortcut key to view the current database connection condition is the shortcut key of crtl+1 (sp_who) lock condition is ctrl+2 (sp_lock), the shortcut key to execute is alt+x or F5 (although there are shortcut keys but try not to use), press win+ R key out to run the column input SSMS can open SQL Server Management Studio, check the table by ALT+F1 to view the table properties (sp_help), the New Query window shortcut key is Alt+n, these are more commonly used shortcut keys, It is easy to operate your database.

15. Sometimes when you create or modify a table or a field after you execute a query or write out a new field to find the newly added object below a red line, the mouse will display the column name is invalid, for my mild obsessive-compulsive disorder people very uncomfortable. The reason for this is that the IntelliSense of SQL Server (IntelliSense) does not perceive this change, and we can manually perform shortcut key ctrl+shift+r to re-perceive it.

Big New Year's brain are all kinds of eating and drinking filled up-temporarily think of so many, after the thought of the supplementary, if there is inaccurate or do not understand the place to welcome you to shoot bricks.

Novice SQL Server database DBA needs to be aware of the small details