Including the installation prompts the hang operation, shrink the database, compressed database, and transfer the database to new users to check the backup set, repair the database, etc.
(1) Suspending operation When the system prompts before installing SQL or SP patch, there is a hangup installation operation, which requires restart, often restarted, solution: to HKEY_LOCAL_MACHINE / SYSTEM / CURRENTCONTROLSET / CONTROL / SESSION Manager Remove PendingFileerenameOperations
(2) Shrinking Database - Reconstruction Index DBCC Reindexdbcc Indexdefrag - Shrink Data and Log DBCC Shrinkdbdbcc ShrinkFile
(3) Compressed Database DBCC ShrinkDatabase (DBNAME)
(4) Transferring database to new users with existing user rights EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'NewName', 'OldName'go
(5) Checking the backup set RESTORE VERIFYONLY from Disk = 'E: /DVBBS.BAK'
(6) Repairing the database ALTER DATABASE [DVBBS] Set Single_Usergodbcc Checkdb ('DVBBS', REPAIR_ALLOW_DATA_LOSS) with Tablockgoalter Database [DVBBS] Set Multi_Usergo
- CHECKDB has 3 parameters: - Repair_allow_data_loss - Perform all fixes completed by Repair_Rebuild, including allocation and pages to allocate and unassign to correct allocation errors, structural rows or pages, and delete corrupted text objects . These repairs may result in some data loss. Repair operations can be done under user transactions to allow users to roll back. If rollback fixes, the database still contains errors and should be recovered from backup. If an error is missing, any repair depending on the repair is missing because the repair level is provided. After the repair is complete, the database is backed up. --RePair_fast is small, not time consuming, such as repairing additional keys in the non-aggregated index. These repairs can be completed quickly and will not have the risk of losing data. --Repair_Rebuild Performs all fixes completed by Repair_Fast, including repair required for a longer period of time (such as rebuilding indexes). Do not have the risk of losing data when performing these fixes.
--DBCC CHECKDB ('DVBBS') with no_infomsgs, physical_only
Two methods of SQL Server log clearance are in the process of use, often encountering the status of database logs, here, introduced two processing methods ...
method one
Under normal circumstances, the shrinkage of the SQL database does not greatly reduce the size of the database. The main role is to shrink the log size. This action should be performed regularly to prevent the database log excessively 1. Setting the database mode as simple mode: Open SQL Enterprise Management In the control station root directory, click Microsoft SQL Server -> SQL Server Group -> Double click to open your server -> Double click to open the database directory -> Select your database name (such as the forum database forum) -> Then click Right click Select Properties -> Select Options -> Select "Simple" in the fault restore mode, then press OK Save 2, right-click on the current database, see the contraction database in all tasks, generally The default setting is not adjusted, the direct point is determined 3, after the shrink database is completed, it is recommended to reset your database properties to standard mode, the operation method is the first point, because the log is often the important basis method for restoring the database in some abnormalities.
Set NoCount OnDeclare @LogicalFileName Sysname, @Maxminutes Int, @newsize Int
Use tablename - Database name to be operated SELECT @LogicalFileName = 'Tablename_log', - log file name @maxminutes = 10, - limit on time allowed to wrap log. @Newsize = 1 - You want to set the log file you want to set Size (m)
- Setup / initializeDECLARE @OriginalSize intSELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileNameSELECT 'Original Size of' db_name () 'LOG is' CONVERT (VARCHAR (30), @ OriginalSize) '8K pages or' Convert (VARCHAR (30), (@ OriginalSize * 8/1024)) 'MB' from sysfiles where name = @LogicalFileNamecreate Table DummyTrans (DummyColumn Char (8000) Not null
Declare @counter int, @strunclog varchar (255) select @starttime = getdate (), @TrunClog = 'backup log' db_name () 'with truncate_only'
DBCC SHRINKFILE (@LogicalFileName, @NewSize) EXEC (@TruncLog) - Wrap the log if necessary.WHILE @MaxMinutes> DATEDIFF (mi, @StartTime, GETDATE ()) - time has not expired AND @OriginalSize = (SELECT size From sysfiles where name = @LogicalSize * 8/1024> @newsize begin - Outer loop. SELECT @counter = 0 while ((@counter <@originalsize / 16) and (@counter <50000))) BEGIN - update INSERT DummyTrans VALUES ( 'Fill Log') DELETE DummyTrans SELECT @Counter = @Counter 1 END EXEC (@TruncLog) END SELECT 'Final Size of' db_name () 'LOG is' CONVERT (VARCHAR ( 30), size) '8K Pages Or' Convert (VARCHAR (30), (Size * 8/1024)) 'MB' from sysfiles where name = @LogicalFileNameDrop Table DummyTransset NoCount Off Deletes several times of repeating data in the database Method Database During the use of programs, due to programs, the repetition data has caused the database part settings that cannot be set correctly ...
method one
declare @max integer, @ id integerdeclare cur_rows cursor local for select primary field, count (*) from table group by the main field having count (*)> 1open cur_rowsfetch cur_rows into @ id, @ maxwhile @@ fetch_status = 0beginselect @max = @max -1set rowcount @maxdelete from table name Where main field = @idfetch cur_rows INTO @ ID, @ MAXENDCLOSE CUR_ROWSSET ROWCOUNT 0
Method Two
There are two sense recording, one is a full repeated record, that is, all fields are repeated records, and the other is a repeated record of some of the key fields, such as the Name field repeat, and other fields are not necessarily duplicated or repeated. ignore. 1. For the first repetition, it is easier to solve, using the Select Distinct * from TableName, you can get the result set without duplicate records. If the table needs to delete repetitive records (repeated record reserved 1), you can delete Select Distinct * Into #TMP from Tablename DROP TABLENAME SELECT * INTO TABLENAME from #tmp DROP TABLE #TMP is the reason for this repetition. The design is not yaw, and the unique index will be addressed. 2, this type of repetition problem typically requires retention of the first record in the repeated record, the operation method is assumed to have a repeated field is Name, address, requiring the unique result of these two fields SelectiTITY (Int, 1, 1) as autoID, * into #Tmp from tableName select min (autoID) as autoID into # Tmp2 from #Tmp group by Name, autoID select * from #Tmp where autoID in (select autoID from # tmp2) to obtain the last select Name, Address does not repeat the result set (but there is a more autoid field, you can write this column in the SELECT clause when you are actually written)
Two ways to change the users of the database in the database may often encounter a database backup to restore to another machine results that cause all the tables that cannot be opened, the reason is that the database users at the time ...
- Change a table EXEC SP_CHANGEOBJECTOWNER 'TABLENAME', 'DBO'
- Storage Change All Table Create Procedure DBo.user_changeObjectownerbatch @oldowner as nvarchar (128), @newource as nvarchar (128) AS
Declare @name as nvarchar (128) Declare @owner as nvarchar (128) Declare @Ownername as nvarchar (128)
DECLE CUROBJECT CURSOR for SELECT 'Name' = Name, 'Owner' = User_name (UID) from sysobjects where user_name (uid) = @ Oldowner Order by Name
OPEN curObjectFETCH NEXT FROM curObject INTO @Name, @OwnerWHILE (@@ FETCH_STATUS = 0) BEGIN if @ Owner = @ OldOwner begin set @OwnerName = @OldOwner '.' Rtrim (@Name) exec sp_changeobjectowner @OwnerName, @NewOwner end - Select @ name, @ newowner, @ Oldowner
Fetch next from curobject Into @name, @OWNERENDCLOSE CUROBJECTDEACATE CUROBJECT
Go
There is no good to write directly to the data in SQL Server. Everyone looks, sometimes it is a bit used.
Declare @i intSet @ i = 1WHILE @i <30begin insert @ i = @ i 1nd
Nothing database log file recovers database method two database log files, erroneous deletions or other reasons for database logs
method one
1. Create a newly named database
2. Stop SQL Server again (Be careful not to separate the database)
3. Overwate this newly built database with the original database data file
4. Restart SQL Server
5. At this time, you will be suspicious when you open the Enterprise Manager. Whether you do this, you will do the following statement (pay attention to the database name)
6. The data in the database can be accessed after completion. At this time, the database itself is generally issued, the solution is to create a new database using the database's script and will pass the data.
Use mastergo
SP_Configure 'Allow Updates', 1 Reconfigure with Overridego
Update sysdatabases set status = 32768 where name = 'quoted database name' Go
SP_DBOPTION 'Troubleshooting Database Name,' Single User ',' True'go
DBCC CHECKDB ('Troubleshooting Database Name) GO
Update sysdatabases set status = 28 where name = 'quoted database name' Go
sp_configure 'allow updates', 0 Reconfigure with Overridego
SP_DBOPTION 'Troubleshooting Database Name', 'Single User', 'False'go
Method Two
The cause of things yesterday, the system administrator told me that the disk space where we have internal application database is inadequate. I noticed that the database event log file XXX_DATA.LDF file has grown to 3GB, so I decrease the log file. After the failure of the contracting database, I made a maximum and fooling error since I have entered the industry: I accidentally deleted this log file! Later, I saw all the discussions and database recovery articles: "If you have to ensure that the database log file exists, it is critical, even Microsoft even has a KB article how to recover only by log files. I really don't know what I think at that time? !
This is broken! This database can't be connected, and the enterprise manager is written next to its "(quenus)". And the most important, this database never backed up. I found some of the database server for half a year ago, it can be used, but there are fewer records, tables, and stored procedures. I really hope this is just a nightmare!
Recovery steps that have no effect, attached database _Rambo speechless when there is no activity log in the deleted log file, can be recovered:
1. Separate the quenched database, you can use sp_detach_db2, additional database, you can use sp_attach_single_file_db
However, unfortunately, after execution, SQL Server questioned that the data files and log files did not match, so they were unable to attach the database data file. The DTS data export cannot be read, and the XXX database cannot be read. The DTS Wizard reports "Initialization context has an error".
Emergency mode Yihong Bani said that there is no log for recovery, you can do this:
1. Set the database to Emergency Mode
2, re-establish a log file
3, restart SQL Server
4, set the application database to single user mode
5, do DBCC Checkdb
6. If there is no big problem, you can change the status of the database. Remember to turn off the modification options for the system table.
I practiced, remove the data file applying the database, re-establish a database XXX of the same name, then stop the SQL service, and then override the original data file back. Thereafter, walk according to the steps of the Yihong Mon.
However, it is also unfortunate that other steps are very successful in addition to step 2. Unfortunately, after restarting SQL Server, this application database is still quilted!
However, let me be pleased, after doing this, it is able to select data, let me take a breath. However, when the component uses the database, the report says: "An error occurred: -2147467259, Begin Transaction cannot be run in the database 'xxx' because the database is in an evasive recovery mode."
Finally, all steps successfully set up the database to stop the SQL Server service in the emergency mode;
Remove the data file XXX_DATA.MDF of the application database;
Re-establish a database xxx;
Stop SQL service;
Cover the original data file back;
Run the following statement, set the database to emergency mode;
Run "Use Master
Go
sp_configure 'allow updates', 1
Reconfigure with override
Go "
Results of the:
DBCC is executed. If DBCC outputs an error message, contact your system administrator.
The configuration option 'allow updates' has been changed from 0 to 1. Run the Reconfigure statement to install.
Then run "update sysdatabase set status = 32768 where name = 'xxx'"
Results of the:
(The number of rows affects is 1 line)
Restart SQL Server service;
Run the following statement, set the application database to Single User mode;
Run "sp_dboption 'XXX', 'Single User', 'True'"
Results of the:
The command has been successfully completed.
ü make DBCC CHECKDB;
Run "DBCC Checkdb ('XXX')"
Results of the:
'Xxx' DBCC results.
'sysObjects' DBCC results.
Object 'sysObjects' has 273 rows, which are located in page 5.
'sysindexes' DBCC results.
Object 'sysIndexes' has 202 rows, which are located in page 7.
'syscolumn' 's DBCC results.
.........
ü Run the following statement to turn off the modification options for the system table;
Run "sp_resetstatus" xxx "
Go
sp_configure 'allow updates', 0
Reconfigure with override
Go "
Results of the:
Update the entry of the database 'xxx' in sysdatabases, mode = 0, status = 28 (status Suspect_bit = 0),
No rows in sysdatabases are updated because the mode and status have been properly reset. There is no error, no changes are made.
DBCC is executed. If DBCC outputs an error message, contact your system administrator.
The configuration option 'allow updates' has been changed from 1 to 0. Run the Reconfigure statement to install.
Re-establish another database xxx.lost;
DTS Export Wizard Run the DTS Export Wizard;
Copy Source Select EmergencyMode's database XXX, import to XXX.LOST;
Select "Replication objects and data between SQL Server Database", trying multiple times, it seems that you can't copy all table structure, but there is no data, no view and stored procedures, and the DTS wizard finally reports the replication;
So finally select "Source Database Copy Table and View", but later discover, this always can only copy a part of the table record;
So select "Data to be transferred with a query", which is missing which table record, which is
The view and stored procedures are added to the SQL statement.
Maintaining the index of the table in SQL Server often encounters some problems in use and creating a database index, where some alternative methods can be used here ...
- Step 1: Check if you need to maintain, check if scan density / scan dens is 100% declare @table_id int set @ Table_ID = Object_ID ('Name') DBCC ShowContig (@table_id)
- Step 2: Reconstruction of the table index DBCC DBREINDEX ('Name ", PK_ index name, 100)
- Take the first step, if you find that the scan density / scan density is still less than 100%, all indexes of the reconstructed table - Yang Wei: not necessarily 100%. DBCC DBREINDEX ('Name ",' ', 100)
SQL Server patch installation FAQ Who will take a look at you :)
First, the common problem during the patch installation
If you encounter the following matches when installing the patch:
1. "The previous program creates a suspended file operation during the installation process. Before running the installer", follow these steps:
a, restart the machine, then install, if there is still this error, press Step B, enter RegeDit C in Start -> Run to HKEY_LOCAL_MACHINE / SYSTEM / CURRENTCONTROLSET / Control / Session Manager Location D, Select File -> Pour out, save E, right-click PendingFileerenameOperation on the right window, select Delete, then confirm F, restart the installation, problem solving
If there is also the same problem, please check if there is this value in other registry, if you have any deletion. 2, in installing SQL Server SP3, sometimes appears: Whether you use Windows certification or mixing authentication, you have a password error. At this time, you will find the following description: [TCP / IP Sockets] Specified SQL Server Not Found. [TCP / IP Sockets] ConnectionOpen ()). In fact, this is a small bug of SQL Server SP3. When installing SP3, there is no listening to the TCP / IP port, and you can follow the steps below:
1. Open the SQL Server Client Network Utility and Server Network Tool to ensure that the enabled protocol contains Name Pipe, and the location is in the first bit. 2. Make sure [HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLServer / Client / ConnectTo] "DSQuery" = "DBNetLib". If not, build 3 itself, stop MSSQL. 4, and install it.
This will be installed correctly.
Second, SQL Server patch version check
SQL Server patch is not as good as the Windows patch version check, a system administrator, if you don't understand the patch number corresponding to the SQL Server version, may also encounter a little trouble, so in this explanation, through such a way to determine the machine is Safety methods do not have any impact on the system. 1. Log in to SQL Server with the ISQL or SQL query analyzer. If you use ISQL, enter ISQL -U SA in the CMD window, then enter your password, enter; if you use the SQL query analyzer, start, enter, enter SA and passwords (you can also verify using Windows). 2, type in isql: select @@ Version; Go
Or input in the SQL query analyzer (in fact, if you don't want to enter, just open your help about you :)) Select @@ version; then press execution; then return SQL version information, as follows: Microsoft SQL Server 2000 - 8.00 .760 (Intel x86) DEC 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3) 8.00.760 is the version and patch number of SQL Server . The correspondence is as follows:
8.00.194 ------- SQL Server 2000 RTM 8.00.384 ------- (SP1) 8.00.534 ------- (SP2) 8.00.760 ------- (SP3)
This way we can see the correct version and patch number of SQL Server.
We can also use XP_MSVER to see more detailed information SQL Server database backup and recovery measures the most common operation, novices ...
First, back up the database 1, open the SQL Enterprise Manager, click on the Microsoft SQL Server2, SQL Server Group in the Contents Genus directory -> Double click to open your server -> Double click to open the Database 2, select your database name (Such as the forum database forum) -> Then click on the tool in the menu -> Select Backup Database 4. The Backup Options Select full backup, the backup in the destination is to select the name point to delete if the original path and name are selected, then add some additions If there is no path and name, you will choose to add, then specify the path and file name, the specified point determination returns the backup window, then determine the backup 2, restore the database 1, open the SQL Enterprise Manager, in the root directory of the control station Point Microsoft SQL Server2, SQL Server Group -> Double click to open your server-> Click on the new database icon, new database name yourself 3, click on the newly entered database name (such as the forum database forum) -> Then click on the tool in the menu -> Select Recovery Database 4. Select from Devices in the restore option in the pop-up window -> Point Add-> and select your backup file Name -> Add the point OK to return, at this time, the device bar should appear the name of the database backup file, the backup number is 1 (if you have multiple backups for the same file, you can click on the backup number) Content, select the latest backup after the check box) -> Then click on the option button next to the above regular, select to force the restore on the existing database in the window, and select in the recovery completion status Enable the database to continue running but cannot restore other transaction logs.
Restoring the database file for the intermediate part of the window To set up the installation of your SQL installation (you can also specify your own directory), the logical file name does not need to be changed, move to the physical file name to do according to your recovery machine situation Change, such as your SQL database in D: / Program Files / Microsoft SQL Server / MSSQL / DATA, then follow the directory of your recovery machine to change, and the last file name is best to change your current database name (If it is bbs_data.mdf, now the database is forum, change to forum_data.mdf), log and data files must be related to the related changes in this way (the file name of the log is * _log.ldf), Here you can freely set, provided that this directory must exist (if you can specify D: /SQLDATA/BBS_DATA.MDF or D: /SQLDATA/BBS_LOG.LDF), otherwise recovery will report an error 6, after the modification is complete, click The following determination is restored, then a progress bar, prompting the progress of recovery, the system will automatically prompt after the recovery is completed, such as the intermediate prompt error, please record the relevant error content and ask the person familiar with SQL operation, General errors are nothing more than directory error or file name duplicate or file name error or space is not enough or the database is in use, and the database is using an error. You can try to close all the SQL windows and reopen the recovery operation, if you still prompt you Using an error can stop the SQL service and then restarted, as for other errors, it can be restored after the error content is changed accordingly. In general, the contraction of the SQL database is not largely contracted. Reduce the size of the database, the main role is to shrink the log size, should periodically do this to prevent the database log excessively 1, set the database mode as simple mode: Open SQL Enterprise Manager, in the control station root directory, click Microsoft SQL Server -> SQL Server Group -> Double click on your server -> Double click to open the Database directory -> Select your database name (such as the forum database forum) -> then click Right click Select Properties -> Select Options - > Select "Simple" in the mode of failure, then press OK 2, right-click on the current database, see the contraction database in all tasks, the default settings in all tasks are not adjusted, direct point determination 3, the shrink database is completed It is recommended to reset your database properties to standard mode, and the operation method is the first point, because the log is often an important basis for recovering the database in some abnormalities, setting a daily automatic backup database strongly recommended that conditional users This operation! 1. Open Enterprise Manager, click on the Microsoft SQL Server -> SQL Server Group -> Double click on your server 2 in the root directory of the Control Station -> Select the Database Maintenance Planner above the menu 3, Next Select data to be automatically backed up -> Next update data optimization information, here usually do not do the choice -> Next check data integrity, it is generally not selected 4, the next specified database maintenance plan, default One week backup once, click Change Select Once After you back up, you can determine 5. Next Specify the backup disk directory, select the specified directory, such as you can create a directory in D: DATABAK, then choose to use here This directory, if your database is better to choose to build a subdirectory for each database, then select Remove the backup of the pre-presumere, generally set 4-7 days, see your specific backup requirements,
Backup file extensions are generally BAK to use the default 6, the next specified transaction log backup plan, see your needs to do the choice -> The report to generate, generally do not choose -> Next Maintenance plan history Record, it is best to use the default option -> Next step 7, the system is probably prompting the SQL Server Agent service is not started, first determine the completion plan setting, then find the SQL green icon in the rightmost status bar of the desktop Double-click on, select SQL Server Agent in the service, then click the Run arrow, select Automatically start the service 8 when the OS is started, this time the database plan has been successfully run, he will follow the settings you will automatically Backup Modification Program: 1. Open Enterprise Manager, click on Microsoft SQL Server in the Control Bengen Category -> SQL Server Group -> Double click to open your server -> Management -> Database Maintenance Plan -> After opening, you can see the scheduled plan, you can modify or delete the operation 5. Data transfer (new database or transfer server) is generally, it is best to use backup and restore operations to transfer data, in special cases, Transfer can be used in the way in which it is introduced. This is an imported export method. The imported export mode Transfer data is that one function can be used to reduce (shrink) the size of the database in the case where the shrink database is invalid, this operation is default to you SQL's operation has a certain understanding, if you don't understand the part of these operations, you can consult the network related personnel or query online information 1, export all the tables, stored procedures of the original database into a SQL file, pay attention to the option at the option Select to write an index script and write primary keys, foreign keys, defaults, and check constraint script options 2. New databases, implement the SQL file 3 established in the first step for the new database, imported the new database to import the new database All table content in the original database utilize database log recovery data to time point operations due to abnormal data loss, and do not want to use backup data restore, as long as there is backup and current logs are in good condition, you can use this method to try, say Unequestable to recover the loss ... 1. If there is a full storage backup (or have multiple differential backups or incremental backups) before mistaken, the first thing to do is to enter a log backup (if you don't make the log file Large Trunc. Log on chkpt. Option 1 Then you will die) Backup log dbname to disk = 'filename'2, restore a full storage backup, pay attention to use with Norecovery if there are other differences or increments Backup, restore RESTORE DATABASE DBNAME from Disk = 'FileName' with N ORECOVERY3, restoring the last log backup, just made log backup, specifying the time of recovery time point to misoperating the time RESTORE log dbname from disk = 'filename' with stopt = 'Date_time' above these operations can be in SQL Server Enterprise Manager It is completed in it, it is not difficult. . . Of course, if the misoperation is some operations such as TRUNCATE TABLE, SELECT INTO, so it is more serious when the above method is not possible to recover the data when the data is damaged. If you can fix your luck ...
In the SQL Server2000, if the database file (non-system database file) is incorrect, only the database of non-MASTER, MSDB is applied. described as follows:
1 Construction of a test database TEST (Database type is complete) 2 built a table, insertion point record Create Table A (C1 varchar (2)) Go Insert Into a Values ('AA') Go Insert Into a Values ('bb') Go 3 full backup, to the file test_1.bak4 in making something insert into a values ('cc') Go Create Table B (C1 Int) Go Insert Into B Values (1) Go Insert Into B Values (2) Go5 Shutdown Database Server 6 edit database file test_data.mdf with UltraEdit, and then modify the point byte content, which is equivalent to a deadly damage. 7 Start the database, and run the Enterprise Manager, click on the database, see TEST to become gray, and display it. 8 Run isql -slocalhost -usa -p1> backup log test to disk = 'd: program filesmicrosoft sql servermssqlbackup est_2.bak' with no_truncate2> Go has processed 2 pages, these pages belong to the database 'test' file 'test_log' (in Document 1). The Backup log operation has been successfully handled 2 pages, which took 0.111 seconds (0.087 MB / sec).
9 Recover the oldest full backup 1> Restore Database test from disk = 'd: program filesmicrosoft SQL ServerMssqlbackup Est_1.bak' with Norecovery2> Go has processed 96 pages, these pages belong to the database 'test' file 'test_data' (in Document 1). Process 1 pages, these pages are file 'test_log' (on file 1). Restore Database Successfully handles 97 pages, spent 0.107 seconds (7.368 MB / sec).
10 Restore the nearest log 1> Restore log test from disk = 'd: program filesmicrosoft SQL ServerMssqlbackup est_2.bak' with recovery2> Go has processed 2 pages, these pages belong to the database 'test' file 'test_log' (on file 1 ). Restore log operations have been successfully processed by 2 pages and spent 0.056 seconds (0.173 MB / sec).
The memory process is written in experience and optimization measures, see ...
First, suitable for the reader object: Database development programmer, database data volume involves an optimized project developer for SP (stored procedures), people with strong interest in the database. Second, the introduction: In the development of the database, complex business logic and the database are often encountered, this time will be encapsulated with the database operation. If the project's SP is more, there is no specification, and it will affect the future system maintenance difficulties and the difficult to understand of the large SP logic. In addition, if the data volume of the database is large or the project's performance requirements of the project, it will encounter Optimization issues, otherwise the speed may be very slow, after personal experience, an optimized SP is more than one hundred times higher than the efficiency of the Poor SP. Third, content: 1. If the developer uses other library Table or View, be sure to create a view in the current library to implement cross-library operations, it is best not to use "Database.dbo.Table_name" directly, because sp_depends cannot display it The span Table or View used by SP is inconvenient to check. 2. Developers must have used SET SHOWPLAN ON to analyze the query plan, and have been used to optimize inspection. 3, high program operation efficiency, optimization applications, should pay attention to the following: a) SQL usage specification: i. Try to avoid big transaction operations, use the Holdlock clause to increase the system. II. Try to avoid repeated access to the same or several tables, especially those with large data volume, can consider extracting data into the temporary table according to the conditions, and then connect. III. Try to avoid using a cursor, because the efficiency of the cursor is poor, if the data of the cursor exceeds 10,000, then it should be rewritten; if a cursor is used, it is necessary to avoid the operation of the table connection in the cursor cycle. IV. Note that WHERE Form Writings must consider the order of statement, and should determine the front-rear order of the condition clauses according to the index order and range size, as much as possible to make the field order consistent with the index sequence, and the range is from large to small. V. Do not perform functions, arithmetic operations, or other expression operations on the "=" on the WHERE clause, otherwise the system may not use indexes correctly. Vi. Try to use exists instead of Select Count (1) to determine whether there is a record, the count function is only used when all the number of rows in the statistics table, and count (1) is more efficient than count (*). VII. Try to use "> =", do not use ">". VIII. Note that some OR clauses and UNION clauses are replaced between IX. Note that the data type connected is connected to avoid connections between different types of data. X. Note the relationship between parameters and data type during storage. Xi. Note the amount of data for INSERT, UPDATE, prevent conflicts with other apps. If the amount of data exceeds 200 data pages (400K), the system will be upgraded, and the page lock will be upgraded to a table-level lock. B) Normative specification of the index: i. Creation of the index To consider the application, it is recommended that the big OLTP table should not exceed 6 indexes. II. Use the index field as a query condition, especially the cluster index, can force the specified index III by index index_name if necessary. Avoiding a Table Scan for a large table query, consider a new index when necessary.
