Vadivel's blog

In SQL Server, one of the Database Properties options is Auto Close . This is an option to be used (auto close = True) if our intention is to shut down cleanly and free the resources once the last user accessing that database exits. If after the last user exits we still want to keep the database alive without shutting down then set auto close as FALSE. But having this setting enabled in a Production environment will end up with performance issues majority of the times. Why? Because once the DB is closed all cached items (data / procedure cache, execution plans) will be flushed out as well. So it has to work from the scratch again when the next user connects in. How to know what is it set to? Option 1: Try this query which will return all databases in that server where Auto Close is set to TRUE. SELECT [name] AS [DatabaseName] FROM SYS.databases  WHERE is_auto_close_on = 1 --To change AUTO CLOSE option to FALSE USE [master] GO ALTER DATABASE [TEST]...

Often I find people aren't making use of the benefit of SET STATISTICS IO and SET STATISTICS TIME while trying to tune their queries. Bottom-line is we want our queries to run as fast as possible. One of the challenges we face is not all environments which we would be working on are similar. The configuration, loads et al would be different between our Development box, Staging box, Production box etc., So how can we measure whether the  changes which we do really improves the performance and it would work well in other environmentts as well? Let's try to understand few basics before seeing some code in action. For any query to be executed by SQL Server it uses many server resources. One such is "Amount of CPU resources it needs to run the query". This information would remain almost the same (There might be minimal changes in milliseconds) between executions. Another SQL resource which it needs for executing a query is IO . It would first check the Memory/Dat...