Skip to main content

Posts

Showing posts from November, 2016

Is Auto Close Enabled in your SQL Server database?

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] SET

Creating & Executing SQL Queries in Visual Studio Code

Visual Studio Code is a modern, lightweight (but powerful) source code editor which runs on your desktop and is available for Windows, Mac, and Linux. A new extension has been announced which would support creating and executing SQL queries from within Visual Studio code editor. Setting up the environment: 1. Download and install Visual Studio code from here -  https://code.visualstudio.com/Download 2. Next you need to install the extension "mssql" on it. Refer the steps here in GitHub  or Microsoft Documentation As explained in the above link bare minimum these steps should have been completed: Installation of Visual studio code and mssql extension Creating the SQL Server Connection Profile Changing the language of the file to "SQL" Executing the query: 1. It's simple, just type the query you want and to execute it press "CTRL + SHIFT + e" 2. Results can also be saved as a .csv, .json file Sample output: