Friday, November 18, 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 AUTO_CLOSE OFF;

Option 2: In SQL Server Management Studio, right-click on the database of interest, click on “Properties”, and choose the “Options” tab to see a dialogue box similar to the one shown below


Change that value to False and click OK.

Best practice is to keep this set to FALSE. Still not convinced? my suggestion would be if you’re not too sure whether this setting is helping you or not then have it set to FALSE and/or seek help from experienced DBAs.

Thursday, November 17, 2016

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:


Monday, September 19, 2016

Enable thumbnail preview in Windows 10

By default "Always show icons, never thumbnails" option would be enabled in Windows 10. Just follow these two steps to change that setting.

1. Go to windows explorer and click on "Options"




2. Uncheck the "Always show icons, never thumbnails" option


That's it just click on OK and you should be able to see the thumbnail of images.

Monday, September 12, 2016

To run dex in process, the Gradle daemon needs a larger heap. It currently has approximately 910 MB

Warning / Error: To run dex in process, the Gradle daemon needs a larger heap. It currently has approximately 910 MB

Solution which worked for me:

Android Studio 2.1.* has a feature named Dex In Process. To make use of it we need to update our gradle.properties file and increase the amount of memory allocated to the Gradle Daemon VM to a minimum of 2 GB.

So just added the below line in my project gradle.properties

org.gradle.jvmargs=-Xmx2048m

Friday, August 12, 2016

List all stored procedures which are modified recently in a SQL Server database

In SQL Server Management Studio:

  1. Navigate to your database > Programmability 
  2. Press F7 (or) View > Object Explorer Details
  3. In the Object Explorer Details window right click anywhere on the header row and select "Date Last Modified" field (in case it isn't selected already)




T-SQL Script to achieve the same result:

Option 1:

SELECT
    name,
    create_date,
    modify_date
FROM sys.procedures
WHERE modify_date > '2016-08-09'
ORDER BY modify_date DESC;

Option 2:

SELECT
specific_name,
created,
last_altered
FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_type = N'PROCEDURE'
and last_altered > '2016-08-09' 
ORDER BY last_altered DESC;

Thursday, May 12, 2016

Transform your fear to success

During my days as an MVP (Microsoft Most Valuable Professional), I used to mentor few juniors from the community. One of them used to be very low on confidence and fear of failure was hurting him more than anything. Recently while cleaning up my mailbox found few of the old emails which I have sent him - cleaned it up a bit and just compiled all those into this article.

Important Life Lessons from Cycling

Over the past two years, I acquired many life lessons from cycling. However, after careful thought, I have decided to share the most significant life lessons I have learned in this article

Thursday, July 30, 2015

#ComeBackHome - Join hands to find missing children

In continuation to my earlier post Finding Missing Children in India I have been looking out for more ways by which we can help track and find a missing child. In that process came to know from my friend about comebackhome.org

On the first look it definitely sounded like a great idea. The concept of this plugin is by just adding one single line of code in all websites we can get the entire internet search for the missing children. When the visitor click on the banner it will show the missing children in their geographical region. This plugin is integrated with multiple missing persons databases including Interpol, NCMEC (The National Center for Missing & Exploited Children) and NAMUS (National Missing and Unidentified Persons System). That said, if we know of any other countries authentic databases the code can be integrated with that too.

If a user of a website navigates to a page that does not exist, either because the user has mistyped an URL or trying to open an old bookmarked link which is not valid anymore it throws an error message: "HTTP 404 - Page Not Found". Why not make use of these pages to locate missing people? That's exactly what comebackhome.org does. It adds an unintrusive banner with photos of missing people to any website's 404 error pages, with a hope that someone who visits the site might recognize the missing people.

"Saving one life is all it takes to make it worth the effort!"

Refer the below images to see how it would look for example in Google.com


If clicked on the banner this is how it would look like


There were few obvious questions which I wanted to get it clarified from the creators of this plugin - Bipin Suresh & Yang Su. So reached out to the contacts listed in their site and Bipin responded back with his detailed response. Thought would write about couple of those Q&A here.

1. Why are we targeting only "404 page not found" page alone? On production environment the probability of seeing this is very minimal isn't it?

When they built this plugin, they knew that the only way to make this an easy sell would be to make it as easy and painless as possible for website owners. Any obstacle (like the potential loss of traffic) would add to the inertia of not integrating. The reason they chose to promote this on 404 pages are:

  • These pages are high volume but low value
  • Convincing website owners to add this to their main pages is a harder sell
  • '404 - Person Not Found' is catchy and memorable

That said, yes, if we could convince people to add this to other pages, that would work well too, especially since, as we observed, those pages offer a magnitude of order more traffic.

2. How do we validate the authenticity of data?

As mentioned earlier the plugin is integrated with national / international databases of missing people. All vetting of records is done by these authorities.

So as a reader of this blog post what can you do?

A. CSR - My humble request is as part of CSR (Corporate Social Responsibility) if organizations integrate this in their website it would be a great help to the society. This will definitely show your company as progressive, socially very responsible and more importantly will create a social pressure for your competitors / others to follow suit. If the entire internet is searching for missing people I strongly believe they would be found. Can you please try to champion it in your organization? No harm in trying isn't it?

Think about this - somebody kidnaps a kid, based on the complaint it gets logged in the countries authentic database, from the next minute it will be flashed to users in appropriate geographical region - this will effectively restrict kidnappers movement to a very small circle & help find the kids quickly.

B. Have an Idea - Do you think you have an idea to make this better? The source code can be found here - https://github.com/bipsandbytes/comebackhome please help them improve and make it better OR send it to the contact details mentioned in their site.

C. Help find databases in non US region - As we know this may not be the most current or up-to-date datasource for non US region. Reason being most of the international data is taken from Interpol which might not be up-to-date. The results are ranked based on recency and geography, but the underlying datasource should be rich and plentiful for relevancy. Please help find authentic databases of missing people in your country: especially in places like India, where data is sparse, and distributed.

Everybody in the world lead their life with hope and with this little effort from our side we can hope that there is a chance that someone who visits our site might recognize them. At the end of the day, even if one child is found with this effort, that's the end of one parent's nightmare.

If God knows everything that happens on Earth, and he can communicate with us, why doesn't he tell us the whereabouts of children that go missing? - Geronimo Jones