SQL Server Practical Operation Tips Including the installation, hints, shrinks, shrinks databases, compressed databases, and transfer databases to new users, check backup sets, repair databases, etc. (1) SQL When the SP patch, the system prompts have hangned installation operations, requiring restart, often restarting useless, solution: to HKEY_LOCAL_MACHINE / SYSTEM / CURRENTCONTROLSET / CONTROL / Session Manager Remove PENDINGFILENAMEOPERATIONS (2) Shrinking Database - Reconstruction Index DBCC ReindexDBCC (dbname) (iv) transferring database data and logs shrink INDEXDEFRAG-- DBCC SHRINKDBDBCC SHRINKFILE (c) compressing dbcc shrinkdatabase database to the new user to an existing user rights exec sp_change_users_login 'update_one', 'newname', 'oldname'go (V) check the backup set RESTORE VERIFYONLY from disk = 'E: /dvbbs.bak' (VI) to repair 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 page errors, 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, the two methods of Physical_only SQL Server log clearance In the process of use, you often touch the database logs very large, introduced two processing methods here ... method is general Next, the shrink of the SQL database does not greatly reducing the size of the database. Its main role is to shrink the log size. This action should be performed on a regular basis to prevent the database log excessively 1. Setting the database mode as simple mode: Open SQL Enterprise Manager, Click on the Microsoft SQL Server in the Contents of the Contents -> 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 to select Properties -> 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 default settings Without adjustment, direct point determination 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 an important basis for the restoration of the database in some abnormalities, two set nocount ONDECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT USE tablename - the database name SELECT @LogicalFileName to operate = 'tablename_log', - log file name @MaxMinutes = 10, - Limit on time allowed to wrap log @. NewSize = 1 - you want to set the size of the log file (M) - Setup / initializeDECLARE @OriginalSize intSELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileNameSELECT 'Original size of' db_name () 'lOG is' Convert (VARCHAR (30), @ OriginalSize) '8K Pa ges or ' CONVERT (VARCHAR (30), (@ OriginalSize * 8/1024)) ' MB 'FROM sysfiles WHERE name = @LogicalFileNameCREATE TABLE DummyTrans (DummyColumn char (8000) not null) DECLARE @Counter INT, @StartTime DATETIME @Trunclog 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 = @LogicalFileName) AND (@OriginalSize * 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 Remove several method databases of duplicate data in the database Due to the process of procedures The duplicate data will be repeated, and the duplicate data has caused the database section settings that cannot be set correctly ... Method 1 declare @max integer, @ id integerDeclare cur_rows Cursor Local for SELECT main field, count (*) from the name GROUP BY main field HAVING Count (*)> 1Open cur_rowsFetch Cur_r ows into @ id, @ maxwhile @@ fetch_status = 0beginselect @max = @max -1set rowcount @maxdelete from the main table where field = @idfetch cur_rows into @ id, @ maxendclose cur_rowsset rowcount 0 Method two duplicate on two sense Record, one is a fully repeated record, that is, all fields are repeated records, and the other is a repeated record, such as the Name field, and other fields are not repeated or repeated. 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 in the SELECT clause) to change the two ways of the user's table in the database, you may often touch a database backup restore To another machine result, all the tables can't be opened. The reason is that when the table is built, the database user is used in the time ... - Change a table EXEC SP_CHANGEOBJECTOWNER 'TABLENAME', 'DBO' - Storage Change All Table CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch @OldOwner as NVARCHAR (128), @NewOwner as NVARCHAR (128) AS DECLARE @Name as NVARCHAR (128) DECLARE @Owner as NVARCHAR (128) DECLARE @OwnerName as NVARCHAR (128) DECLARE 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 '.' Rtt rim (@Name) exec sp_changeobjectowner @OwnerName, @NewOwner end-- select @ name, @ NewOwner, @ OldOwner FETCH NEXT FROM curObject INTO @Name, @OwnerEND close curObjectdeallocate curObject GO SQL SERVER cycle write data directly nothing to say Everyone looks, sometimes it is a bit used in declare @i intSet @ i = 1WHILE @i <30begin insert @ i = @ i 1END NuBL log file recovery database method two databases Mistakement or other reason for log files caused by damage to the database log 1. Newly built a database 2. Stop SQL Server (notice to separate the database) 3. Overwrite this newly built database with the original database data file 4. Restart SQL Server 5. At this point, you will be suspicious when you open the Enterprise Manager.
Execute the following statement (pay attention to modify the database name) 6. After the completion, you can access the data in the database. At this time, the database itself should also have problems, the solution is to create a new database with the script of the database. and data guided into the line. USE MASTERGO SP_CONFIGURE 'ALLOW UPDATES', 1 RECONFIGURE WITH OVERRIDEGO UPDATE SYSDATABASES SET STATUS = 32768 WHERE nAME = 'question database name' Go sp_dboption 'question database name', 'single user', ' true'Go DBCC CHECKDB ( 'database name doubt') Go update sysdatabases set status = 28 where name = 'question database name' Go sp_configure 'allow updates', 0 reconfigure with overrideGo sp_dboption' question database name ',' single User ',' False'go method's cause of the second thing 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, additional databases _Rambo speaking that there is no activity log in the deleted log file, can be restored: 1. Separate database, you can use sp_detach_db2, additional database, you can use sp_attach_single_file_db but, regret After execution, SQL Server questioned that the data files and log files do not match, so they cannot 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, can do this: Mode 5, do DBCC CHECKDB 6, if there is no big problem, you can change the database status, remember to turn off the system table to turn off me, remove the data file of the application database, and re-establish A same name's database XXX, 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: -2147467259, Begin Transaction cannot be run in the database 'xxx' because the database is in an evasive recovery mode." Final successfully recovered all steps to set the database to emergency mode Remove the SQL Server service; remove the data file XXX_Data.mdf of the application database; re-establish a database XXX; stop the SQL service; overwrite 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" execution results: 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 SysDatabases Set Status = 32768 WHERE Name = 'XXX'" execution result: (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' "Execute Result: The command has been successfully completed. Ü DBCC Checkdb; run "DBCC Checkdb ('xxx')" execution result: '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 system table; run "sp_resetstatus" XXX "Go sp_configure 'allow Updates', 0 Reconfigure With Override Go" Execute Result: Update the database 'XXX' in sysdatabases before, Mode = 0, status = 28 (Status Suspect_bit = 0), no rows in sysdatabases are updated because modes 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 "Copy objects and data between SQL Server database", try it multiple times, It seems that you can't copy all the table structure, but there is no data, no view and stored procedures, and the DTS wizard finally reports the replication failed; so, "So finally select" From the source database replication table and the view ", but later discovered that this is always Only part of the table record can only be replicated; "then select" Data to be transferred with a query ", which is missing which table record, which; 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, you can use some alternative ways to solve ... - Step: Check if you need maintenance, check whether scan density / scan dens is 100% declare @table_id Intset @ Table_ID = Object_ID ('Name') DBCC ShowContig (@table_id) - Step 2: Reconstruction Table Index DBCC DBREINDEX ('Table ", PK_ Index, 100) - Heavy To make a first step, if you find that scan density / Scan Density is less than 100%, all indexes of the reconstructed table - Yang Wei: It is not necessarily 100%. DBCC DBREINDEX ('Name ",' ', 100) SQL Server Patch Installing FAQ Who will take a look at you :) First, the FAQ during the patch installation If you encounter the following quotes: 1 When the installation process, "the previous program creates a pending file operation. Before running the installer, you must restart", follow these steps: A, restart the machine, then install, if you find there is this error, Step B, enter the reedit c in the beginning -> Run, to the HKEY_LOCAL_MACHINE / SYSTEM / CURRENTCONTROLSET / CONTROL / Session Manager position D, select File -> Pour, save E, right-click PENDINGFILENAMEOPERATIONS, select Delete, Then confirm F, restart installation, problem solving If there is also the same problem, check if there is this value in other registry, if you have deleted.
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 (Connect ()). In fact, this is a small bug of SQL Server SP3. When installing SP3, there is no listening to the TCP / IP port, you can follow the steps below: 1 Open the SQL Server Client Network Utility and Server Network Tool to ensure that the Name Pipe is included in the protocol enabled, 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, install it. This will be installed correctly. Second, the SQL Server patch version checks the patch version of SQL Server is not as direct, a system administrator, if you don't know the patch number corresponding to the SQL Server version, there may also be a bit of trouble, so this shows It is a safe way to discriminate the machine by this method, and there is no 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, enter: select @@ version; Go or SQL query analyzer input (In fact, if you don't want to enter, just open your help, you can :)) 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) The 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 commonly used operations, newcomers look at ... 1. Backup Database 1, open SQL Enterprise Manager, in the control station root directory Open Microsoft SQL Server2, SQL Server Group -> Double click to open your server -> Double-click Open Database Directory 3, select your database name (such as the forum database forum) -> then click Tools above the menu -> Select Backup Database 4, Backup Options Select full backup, backup in the destination to select the name point to delete if the path and name, then add, if there is no path and name, select Add, then specify the path and file name, specify After the backup determination returns the backup window, then determine the backup 2, restore the database 1, open the SQL Enterprise Manager, click Microsoft SQL Server2, SQL Server Group in the Contents Genus directory -> Double click to open your server - > Click on the new database icon, the name of the new database is 3, click the newly entered database name (such as the forum database forum) -> then click the tool in the menu -> Select Recovery Database 4, play out Select from the device -> click Select Device -> Point Add -> and select Your Backup File Name -> Add your backup file Name - "At this time, the device bar should appear. Database Backup file name, backup number defaults to 1 (if you have multiple backups to the same file, you can click on the view next to the backup number, select the latest backup after the check box) -> then Click on the option button next to the normal side 5. Select to force restore on the existing database in the window, and select Options that allow the database to continue running but cannot restore other transaction logs in the recovery completion state.
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 action! 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 Back up, restore restore Database DBNAME from Disk = 'FileName' with one by one Norecovery3, 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 stopat = 'date_time' 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 yourself ... SQL Server2000, if the database file (non-system database file) encountered an error, only the database of non-MASTER, MSDB is only available. Description: 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 ') GO3 full backup, to the file test_1.bak4 in making a point in 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 Edits Database File Test_Data.mdf with UltraEdit, and modifies 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 stored procedure is written in experience and optimization measures, see ... I. Suitable for reader objects: Database development programmer, database data volume involves project developers on SP (stored procedures), have database Things of interest. 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.
IV. When using the index field as a condition, if the index is a federated index, the first field in this index must be used as a condition to ensure that the system can be used to use the index, otherwise the index will not be used. V. Pay attention to the maintenance of the index, periodically reconstruct the index, recompile the stored procedure. C) Tempdb's usage specification: i. Try to avoid using Distinct, Order By, Group By, Having, Join, Cumpute, because these statements will increase the burden of Tempdb. II. Avoid frequently create and delete temporary tables to reduce the consumption of system table resources. III. When new temporary table is new, if the amount of data in one time is large, you can use Select Into instead of CREATE TABLE, avoid log, increase the speed; if the data is not large, in order to mitigate the resources of the system table, it is recommended to first create Table first. Then INSERT. IV. If the data volume of the temporary table is large, the index needs to be established, then the process of creating a temporary table and the establishment index should be placed in a single sub-storage process so that the system can be used very well to the index of the temporary table. . V. If you use a temporary table, you must explicitly delete all temporary tables, first truncate Table, then Drop Table, which avoids longer-time locking of the system table. Vi. Careful use of large temporary tables with other big tables of connection query and modification, reduce system table burden, because this operation will use Tempdb system tables many times in a statement. D) Reasonable Algorithm Use: According to the SQL optimization techniques described above and the SQL optimization content in the ASE Tuning manual, combine the actual application, and use a variety of algorithms to obtain the least resource, the highest efficiency. Specific ASE Tune Command: Set Statistics Io ON, SET STATISTICS TIME ON, SET SHOWPLAN ON, etc. Selection from Longrujun's Blog Author Blog:
http://blog.9cbs.net/longrujun/
