Notes for new SQL Server database DBAs

Notes for new SQL Server database DBAs

Any optimization or modification is based on the business situation. You are welcome to make a picture of what may be wrong or inaccurate.

1. we recommend that you set auto-growth when creating a database to increase by MB (M), and set the step size based on the business volume. Generally, we recommend that you set it to 100-200 MB (see the image ), also, try not to change the initial size. This is the default value.

If data files (mdf) increase by 1 MB by default and db business writes are frequent, the database will frequently request disk space, resulting in unnecessary io consumption, it may also cause resource waiting and other problems due to the application space.

If you increase the log file (ldf) by 10% by default, and when the db business volume is large or there are large transactions, the log will grow, if the current log file size is 100 GB, The step size for each increase is 10 Gb. You can think about it if the database suddenly applies for 10 Gb space like a disk, then what will happen to I/O at that time.

2. We recommend that you reserve some memory for the system. Reserved by memory size (64 GB memory on the server is generally limited to 60 GB memory on sqlserver, and 4 GB memory is reserved for the system, which is determined based on the business situation ). Some SQL server operations do not consume the memory reserved by the system for SQL Server. For example, if your server memory is 64 GB and the SQL server process occupies 50 GB, this operation does not consume 50 GB of SQL Server during Backup, but the remaining 14 GB memory. Some other operations also occupy non-sqlserver memory, such as index reconstruction (this memory blur the accuracy to be verified ). If sqlserver has occupied 64 GB of memory, and you need to back up the memory at this time, where does the memory come from? You can only let sqlserver release some objects in the cache to provide you with backup memory. If the current business is busy, it may cause performance problems due to insufficient memory or memory hit.

3. profiler is a good thing. Who knows it.

4. Windows Performance Monitor is an essential tool for monitoring sqlserver performance (Tip: the shortcut key is perfmon. msc ). For specific monitoring metrics, see.

5. If the firewall in your data center has enough disadvantages, the program code is strictly written, and the database does not have a public ip address, you can use the xp_mongoshell stored procedure. Otherwise, close the config.

I did not manage an sqlserver server in front of me. I forgot my login password. So I used the sa password to log on to this database. I successfully added the server administrator account through xp_mongoshell and successfully logged on to the server through this administrator account. This should have been a happy story, but have you ever thought that if someone else knows your database connection string through an illegal program, you happen to have started xp_mongoshell again, then you can prepare your resume.

6. do not check the Save Password option on the login box (don't ask me why, just worry that my server will be accessible to the database if someone else logs on to it one day, just delete my data and destroy my db !!).

7. Try not to use ssms for cross-machine login if you do not have special requirements (especially when your server is not used by yourself ). For example, if you log on to the server 192.168.1.3 with ssms and use the ip account password 192.168.1.4 to access the database of this server, this is okay, but when you use it, try to turn it off. Otherwise, the next person or you will execute the script you want to execute on the local machine to the remote database.

8. try not to use the default path for storing db physical files. just create a folder named db or data under a drive letter for storage, otherwise, it is difficult to manage it. Do not directly create it in the root directory of the disk, because sometimes permission issues may cause you to suffer for a long time. If there are two disk arrays (for example, one raid 10 and one RAID 0) or two independent physical disks, it is best to separate the mdf file and the ldf file to improve io performance (the mdf is placed on the raid 10, ldf is placed on raid0 ).

9. When backing up the database, try to select the minimum service volume when the server load is minimal. If you can select compression backup, save disk space (of course, additional io will also be generated ). Log backup is neither too frequent nor too long, depending on the business volume and the extent to which data is allowed to be lost.

10. if there are other jobs in this instance except backup, try not to choose the whole time to run, forward or backward staggered for a few minutes, because the general log backup will choose the interval of one hour or how many minutes, most of them are full-point backups. Therefore, try to avoid the backup time for other jobs.

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

12. view the SQL Service start time: SELECT LOGIN_TIME FROM MASTER .. SYSPROCESSES WHERE SPID = 1

13. Do not let the program use sa as the database connection string. The sa password should be as complex as possible. Do not talk about the dangers of sa weak passwords.

14. the shortcut for viewing the current database connection status is the crtl + 1 (sp_who) Lock. The shortcut key is ctrl + 2 (sp_lock ), the shortcut keys are alt + x or f5 (although there are shortcut keys, try not to use them). Press win + r to bring out the running column and enter ssms to open the management studio of sqlserver, press alt + f1 to view the table attributes (sp_help). The shortcut key for creating a new query window is alt + n, which are common shortcuts for your database operations.

15. sometimes, after you create or modify a table or field, you can execute a query or write a new field to find a red line under the newly added object. If you move the cursor over it, the column name is invalid, it is quite uncomfortable for people with mild obsessive-compulsive disorder. The cause is that the SQL Server's intelliisense (Intelligent Sensing function) does not perceive this modification. We can manually execute the shortcut key ctrl + shift + r to perceive it again.

This article permanently updates the link address: