Novice SQL Server database DBA need to pay attention to some small details _mssql

Preface: Any optimization and modification are based on the business situation as a prerequisite, may have written wrong or inaccurate place, welcome you to shoot bricks.

1. When creating DB, the growth proposal is set to increase in MB (M), the step size is set according to the volume of business, generally recommended to set 100-200m (see picture), there is to try not to change the initial size, this default is good.

If you follow the default 1m growth in data files (MDF), and DB's business writes are more frequent, the database will frequently request space for disk, causing unnecessary IO consumption, as well as resource waiting due to application space.

If you increase the log file (LDF) by the default percent of 10%, also when the DB business volume is very large or have a large transaction, the log will rub against the growth, if the log file size is now 100G, then each growth step is 10G, you can think about if the database suddenly like a disk request 10 g space , then what the IO situation would be like.

2. It is recommended to reserve some memory for the system. Reserved according to memory size (server 64G memory generally I limit SQL Servers to 60g, leave 4 G to the system, or the sentence according to the business situation to decide). Because SQL Server has some operations that do not consume the memory reserved for SQL Server by the system. For example, your server memory is 64G, and now the SQL Servers process is occupied 50g, this time if you do backup operation, this operation consumes not SQL Server occupied 50g, but the remaining 14g memory. There are other operations that also take up non-SQL Server memory, such as index rebuilds (this memory blurs the accuracy to be verified). If SQL Server is now full of 64g of RAM, you need to back up, where does the memory come from? You can only have SQL Server free some of the objects in the cache to provide you with the memory to back up. If your business is busy now, you may have a performance problem due to low memory or memory hits.

3.Profiler is a good thing, who knows.

4.Windows Performance Monitor is a necessary tool for monitoring SQL Server performance (friendly tip: shortcut key is Perfmon.msc). Specific monitoring indicators I will write in other blogs.

5. If the firewall of your room is serious enough, the program code write is rigorous enough, the database has no public network IP, then you can use xp_cmdshell this stored procedure, otherwise please close this config.

Once there was a SQL Server servers in front of me I did not unified management, then I forgot the server login password. So I use the sa password to log in this database, successfully through the xp_cmdshell added server administrator account, successfully through this Administrator account log on the server. It should have been a happy story, but have you ever thought that if someone else had known your database connection strings through illegal procedures, you would have opened the xp_cmdshell again, so you could have prepared your resume.

6. do not check the top of the landing box to save the password option (don't ask me why, self-willed, is worried about one day my server is someone else landed casually can enter the database, just delete my data destroyed my db how!! ）。

7. try not to use ssms across machines if you don't have special requirements (especially if your server is not used by you alone). For example, you are in 192.168.1.3 this server with SSMS login via IP account password 192.168.1.4 the server's db, this is no problem, but when you use it as much as possible to turn off the habit of the connection. Otherwise the next person or yourself will not be aware of the script you want to execute on the computer to the remote db.

8. the path of storing db physical files as far as possible do not use the default path, oneself under a certain disk character to build a life called db or Data folder storage is good, otherwise find it is a very painful thing also inconvenient management. Do not build directly in the root directory of the disk, because this may sometimes involve the issue of permissions to your egg pain for a long time. If you have 2 disk arrays (such as one raid10 a raid0) or 2 separate physical disks, it is a good idea to store the MDF file and LDF file separately for increased IO performance (MDF on RAID10, LDF on RAID0).

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

If there are other jobs in this instance other than backup, try not to choose the hour, forward or back for a few minutes, because the general log backup will choose between an hour or a few minutes, most of which are backed up on the whole hour. So other jobs try to avoid the time of this backup operation.

One . the job monitor sees only the state of the last execution of the job.

View SQL Service startup time: SELECT login_time from MASTER. sysprocesses WHERE spid= 1

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

The shortcut key to view the current database connection is crtl+1 (sp_who) lock is ctrl+2 (sp_lock), the shortcut key is alt+x or F5 (although there are shortcuts but try not to use), press win+ R Key call up to run the column input SSMS can open SQL Server's Management Studio, select the table press ALT+F1 to view the table's properties (sp_help), the New Query window's shortcut key is Alt+n, these are the more commonly used shortcut keys, can be convenient for your database operation.

Sometimes when you create or modify a table or field, you execute a query or write out a new field to find a red line underneath the newly added object, and the mouse will display the column name as invalid, which is very difficult for me who has mild obsessive-compulsive disorder. The reason for this is that the IntelliSense (IntelliSense) of SQL Server does not perceive this modification, and we can manually perform the shortcut key ctrl+shift+r to regain awareness.

The New Year's brain is filled with a variety of eating and drinking--temporarily remembered so much, after the thought of the additional, if there is inaccurate or do not understand the place to welcome you to pat Bricks.

PS: This blog hand to play original reproduced please indicate the source.