SQL Server Performance Optimization

Today, a netizen came to me to give him the original system database optimization query speed, personal summary of several points to the optimization of SQL Server

1, SQL query statement optimization, such as: can use external connection query out as far as possible do not use the internal connection ..., these are not nonsense, if I use this to optimize for him, then I will have to understand his system of business or something, spend more time, it is not worth. So plan to optimize it by the following 2 points:

2, to add an index to the table, according to the actual system

3, after the system test passed, the foreign KEY constraint delete (because he is already running the system, if not using foreign keys to perform some triggers, this will certainly work)

SQL allows you to query the foreign key name of the specified table, eg. querying foreign keys in the SalesOrder table in db

Select Name

From Sys.foreign_key_columns F joins sys.objects O on f.constraint_object_id=o.object_id
where f.parent_object_id=object_id (' [dbo].[ SalesOrder] ')

This allows you to see the foreign keys in the SalesOrder table (if any)

But if so, I do not have to query every table, Google to find a solution, with a SQL statement to do all, as follows:

--using Sysreferences--Delete foreign keyDECLARE @SQL VARCHAR( -)DECLARECur_fkCURSORLOCAL forSELECT 'ALTER TABLE ['+ object_name(Fkeyid)+ '] Drop constraint' + object_name(ConstID) fromsysreferences--Delete all foreign keysOPENCUR_FKFETCHCur_fk into @SQL while @ @FETCH_STATUS =0BEGINEXEC(@SQL)FETCHCur_fk into @SQLENDCLOSECUR_FKdeallocateCur_fk

Doing this directly will delete all foreign key constraints in the DB

SQL Server Performance Optimization