DBCC SHRINKDATABASE
Most of SQL Server developers and even some DBAs think that we can shrink size of a database by executing DBCC SHRINKDATABASE. You can just reacquire unused space of database and it’s never going to compress your database and change magically low size. Advantage is very very low as compared to performance loss. Why DBCC SHRINKDATABASE is so awful SQL Server Guru Pinal Dave has explained it here.
Query hint to force some index usage
Index of your own choice can be forced to use for a query BUT never ever do this on production servers. Query Optimizer is intelligent enough to select, which index is better to use for a query. Let optimizer work.
Most of SQL Server developers and even some DBAs think that we can shrink size of a database by executing DBCC SHRINKDATABASE. You can just reacquire unused space of database and it’s never going to compress your database and change magically low size. Advantage is very very low as compared to performance loss. Why DBCC SHRINKDATABASE is so awful SQL Server Guru Pinal Dave has explained it here.
SET AUTO_CREATE STATISTICS OFF
SQL Sever by default SET this option to ON and create statistics for all the columns used in join and filters, for even those columns on which no index exists. SQL Server is made quite intelligent to create statistics on required columns only and even drop statistics which are no more in use. So always keep AUTO_CREATE STATISTICS ON.
SQL Sever by default SET this option to ON and create statistics for all the columns used in join and filters, for even those columns on which no index exists. SQL Server is made quite intelligent to create statistics on required columns only and even drop statistics which are no more in use. So always keep AUTO_CREATE STATISTICS ON.
RECOMPILE hint in Stored Procedures
You can provide RECOMPILE hint for stored procedure to recompile it every time it is executed. RECOMPILE hint for SPs is nothing but a performance overhead.
You can provide RECOMPILE hint for stored procedure to recompile it every time it is executed. RECOMPILE hint for SPs is nothing but a performance overhead.
Query hint to force some index usage
Index of your own choice can be forced to use for a query BUT never ever do this on production servers. Query Optimizer is intelligent enough to select, which index is better to use for a query. Let optimizer work.