Monday, November 30, 2015

Azure Active Directory Authentication with roles

I got the requirement to implement Azure Active directory authentication in our project. When i searched for this then i came to know following articles which helped me to understand the how this authentication works and how to implement it.

Source Code is available at following location :

Brief introduction, how it works is mentioned at following links: 

Monday, October 5, 2015

Power BI: How to consume shared Content pack

Step 1: Click on Get Data

Step2 : Select “Get” from My Organization

Step 3: Search Content package name in search box

Step 4: Click on requested content package
4.1 Click on Connect
4.2 It will show the requested content packs report/datasets in workspace

Happy Coding!!

Power BI: Share your reports and data set with another developer

My Dashboard

I have following Reports and dataset which I want to share with another developer

Step 1: Click on setting button as shown in screen

1.1.  Select “Create content pack”

Step 2: Select Specific group
2.1 Enter end user’s email id. Whom you want to share dataset and reports

Step 3: Select your Dashboard, as following I have selected my created dashboard. Select which reports/Datasets you want to share with the user.
3.1 Click on publish

Happy Coding!!

Monday, July 27, 2015

Microsoft Azure Webjobs

Microsoft Azure Webjobs

The long awaited webjobs is now in preview for Windows Azure!  How many times have you deployed a website, then after a couple months requirements change and they want you to run a daily or continuous job that processes files or re-size images or works a queue, now with web jobs in Windows Azure it is easy. 

What is webjob?

Azure WebJobs enables you to run programs or scripts in your website as background processes. It runs and scales as part of Azure Web Sites. It is kind of a lightweight Worker Role associated with web site. Process life cycle is strictly connected with web sites, so restarting a web site will cause a Web Job restart.

Each Web job can be configured to run in one of the following modes:
1. Continuous – runs all the time, analogy to Windows Service configured with auto start option is in place. Hosting environment monitors the job status and brings it up when process is down. (NOTE: With the current preview, this works properly on standard web sites with ‘always on’ setting. Caution is required when we scale the site up from Standard mode – Web Jobs will be terminated when there is no request for 20 minutes)
2. Scheduled – runs at particular times.
3. On Demand – runs when started manually.

Azure WebJobs can be created using the following scripts: 
  1. .cmd, .bat, .exe (using windows cmd)
  2. .ps1 (using powershell)
  3. .sh (using bash)
  4. .php (using php)
  5. .py (using python)
  6. .js (using node)

Creating WebJobs:
A simple console application will work for you as a webjob.

Deploying Webjobs:
We can deploy webjob in either two ways -
1. Automatic deployment - When you publish web application, webjobs get deployed automatically.
2. Manual deployment - Manually deploy code file to portal.

Manual Deployment:

1. To manually deploy our application as new Web Job – we need to create a zip file that contains our project output (in our case standalone exe file is enough). It is important to remember that the main executable file needs to be on the root level of that zip file. Name of the file is irrelevant. The current limit size for one zip file is 100 MB.

2. Open Azure Management Portal and select destination web site.
3. On the web site details page go to Web Jobs tab and click New
4. Now we can configure our new Web Job.I want to run this one every 5 minutes:


5. Now, our job is up and running. To verify it, we can check log file produced by Web Job (link in Logs columns) – this file contains diagnostic information about run and application output that was redirected automatically to that file (the same goes for error stream, so if, by any chance, program throws exception that is unhandled it will also be logged in that file and job run status will be set as Failed)


And the output:


Automatic Deployment:
1. To deploy the WebJob, right-click on the project and select Publish as Azure WebJob.

2. Select when the WebJob shall run and click OK. In this example, the job shall be run on demand.


3. Select a publish target, in this example the target shall be an Azure WebSite

You shall be required to sign-in and enter your Microsoft Azure credentials

Once signed in, you may select in which WebSite the Job Shall runs.

4. Click on OK, then select Publish.
Now, if you go the the Microsoft Azure portal, and navigate to your WebSite, you shall see a new WebJob created and deployed.
Make webjob settings configurable:
It attempts to read configuration settings in this prioritized order:
  1. Try to find the configuration value in the Web Site's online configuration (see below).
  2. Try to find the configuration value in the .cscfg file.
  3. Try to find the configuration value in the app.config file or web.config file.
By the Web Site's online configuration, I mean the configuration settings you can edit on a deployment-by-deployment basis using the Azure management portal, under the Configure 'tab':

Online configuration for an Azure Web Site
Exception in webjobs which I have faced:
Azure WebJob that fails after only 20 minutes of running with the exception "Thread was being aborted".
Cause: By default, web apps are unloaded if they are idle for some period of time. This lets the system conserve resources.
Resolution: In Basic or Standard mode, you can enable Always On to keep the app loaded all the time. If your app runs continuous web jobs, you should enable Always On, or the web jobs may not run reliably.
Also add few settings to your webjob which are mentioned below -
                                   The time is specified in seconds

Web Job: Azure web job aborted some time

Issue: If deployed azure webjob running for long time, it will get aborted after some time.

Issue cause: Sometimes there are interruptions which can stop your WebJob abruptly without notice aborting your process, and get time out.
These interruptions could be due to: stopping your site, restarting your site, some configuration change to your site which causes your site to

restart, Azure maintenance (version update for example) or even the VM simply crashing for some reason.

Make confuguration on for - ALWAYS ON = ON.

Go to Configure menu under APP settings add follwing:
- SCM_COMMAND_IDLE_TIMEOUT = 90000 (in seconds)
- WEBJOBS_IDLE_TIMEOUT = 90000 (in seconds)

- stopping_wait_time = 90000 (in seconds)

Happy Coding!!

Thursday, June 18, 2015

ServiceConfiguration transformation

ServiceConfiguration transformation:

This will help us to build the package immediately. You just need to add different configuration files, and give different values.

Add following sections in cloud solution ccproj
<ServiceConfiguration Include="ServiceConfiguration.production.cscfg" />
<ServiceDefinition Include="ServiceDefinition.csdef" />
<ServiceConfiguration Include="ServiceConfiguration.Dev.cscfg" />
<ServiceConfiguration Include="ServiceConfiguration.Int.cscfg" />
<ServiceConfiguration Include="ServiceConfiguration.Staging.cscfg" />
<ServiceConfiguration Include="ServiceConfiguration.Local.cscfg" />
<ServiceConfiguration Include="ServiceConfiguration.Cloud.cscfg" />


<PropertyGroup Condition=" '$(Configuration)' == 'Dev' ">
<PropertyGroup Condition=" '$(Configuration)' == 'Int' ">
<PropertyGroup Condition=" '$(Configuration)' == 'Staging' ">
<PropertyGroup Condition=" '$(Configuration)' == 'Production' ">

<Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v12.0\Web\Microsoft.Web.Publishing.targets" />
<EnvironmentDefinition Include="ServiceDefinition.Debug.csdef">
<EnvironmentDefinition Include="ServiceDefinition.Production.csdef">
<EnvironmentDefinition Include="ServiceDefinition.Int.csdef">
<EnvironmentDefinition Include="ServiceDefinition.Local.csdef">
<EnvironmentDefinition Include="ServiceDefinition.Dev.csdef">
<EnvironmentDefinition Include="ServiceDefinition.Cloud.csdef">
<EnvironmentDefinition Include="ServiceDefinition.Staging.csdef">
<None Include="@(EnvironmentDefinition)" />
<Target Name="ValidateServiceFiles" Inputs="@(EnvironmentDefinition);@(EnvironmentDefinition->'%(BaseConfiguration)')" Outputs="@(EnvironmentDefinition->'%(Identity).transformed.csdef')">
<Message Text="ValidateServiceFiles: Transforming %(EnvironmentDefinition.BaseConfiguration) to %(EnvironmentDefinition.Identity).tmp via %(EnvironmentDefinition.Identity)" Importance="High" />
<TransformXml Source="%(EnvironmentDefinition.BaseConfiguration)" Transform="%(EnvironmentDefinition.Identity)" Destination="%(EnvironmentDefinition.Identity).tmp" />
<Message Text="ValidateServiceFiles: Transformation complete; starting validation" Importance="High" />
<ValidateServiceFiles ServiceDefinitionFile="%(EnvironmentDefinition.Identity).tmp" ServiceConfigurationFile="ServiceConfiguration.$(Configuration).cscfg" />
<Message Text="ValidateServiceFiles: Validation complete; renaming temporary file" Importance="High" />
<Move SourceFiles="%(EnvironmentDefinition.Identity).tmp" DestinationFiles="%(EnvironmentDefinition.Identity).transformed.csdef" />
<Target Name="MoveTransformedEnvironmentConfigurationXml" AfterTargets="AfterPackageComputeService">
<Copy SourceFiles="ServiceDefinition.$(Configuration).csdef.transformed.csdef" DestinationFiles="$(OutDir)ServiceDefinition.csdef" />

<Target Name="PublishVirtualApplicationsBeforeCSPack" BeforeTargets="CorePublish;CsPackForDevFabric" Condition="'$(PackageForComputeEmulator)' == 'true' Or '$(IsExecutingPublishTarget)' == 'true' ">
<Message Text="Start - PublishVirtualApplicationsBeforeCSPack" />
<PropertyGroup Condition=" '$(PublishDestinationPath)'=='' and '$(BuildingInsideVisualStudio)'=='true' ">
<!-- When Visual Studio build -->
<PropertyGroup Condition=" '$(PublishDestinationPath)'=='' ">
<!-- When TFS build -->
<Message Text="Publishing '%(VirtualApp.Identity)' to '$(PublishDestinationPath)%(VirtualApp.PhysicalDirectory)'" />
<MSBuild Projects="%(VirtualApp.Identity)" ContinueOnError="false" Targets="PublishToFileSystem" Properties="Configuration=$(Configuration);PublishDestination=$(PublishDestinationPath)%(VirtualApp.PhysicalDirectory);AutoParameterizationWebConfigConnectionStrings=False" />
<!-- Delete files excluded from packaging; take care not to delete xml files unless there is a matching dll -->
<CreateItem Include="$(PublishDestinationPath)%(VirtualApp.PhysicalDirectory)\**\*.dll">
<Output ItemName="DllFiles" TaskParameter="Include" />
<FilesToDelete Include="@(DllFiles -> '%(RootDir)%(Directory)%(Filename).pdb')" />
<FilesToDelete Include="@(DllFiles -> '%(RootDir)%(Directory)%(Filename).xml')" />
<Message Text="Files excluded from packaging '@(FilesToDelete)'" />
<Delete Files="@(FilesToDelete)" />
<Message Text="End - PublishVirtualApplicationsBeforeCSPack" />

Happy Coding!!

Tuesday, April 28, 2015

Azure : The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.

Problem: The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.

In azure, while running the project in temporary folder it creates your azure project and its related files. some dll names are large so in result whole path is large and which results in above error.

There are 2 solutions to solve this issue:

Solution 1:
Step 1: Open .ccproj project
Step 2: Under PropertyGroup Add following line

Source :

Solution 2:
Step 1: Right click on "My Computer" -> Select Properties
Step 2: Click on "Remote Setting" -> Select Advanced -> Select Environment variables -> Click on "New" -> Add Variable name as "_CSRUN_STATE_DIRECTORY"
and Add Variable value as whatever you want. ex: C:\A -> click on save

Happy Coding !! 

Friday, April 24, 2015

The underlying connection was closed: An unexpected error occurred on a receive.System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host

I get following error while working with REST API and i do following code change to resolve this issue.

The underlying connection was closed: An unexpected error occurred on a receive.System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)

To resolve above error add following properties to webrequest object
HttpWebRequest webRequest = (HttpWebRequest) base.GetWebRequest(uri);
            webRequest.KeepAlive = false;            webRequest.ProtocolVersion=HttpVersion.Version10;

Happy Coding !!  

Friday, April 10, 2015

JSON to Dataset conversion

Sometimes we need to convert json to dataset in that case following code snippet will help us.

Code snippet :

public DataSet ConvertJsonToDataSet(string jsonString)
                XmlDocument xd = new XmlDocument();
                jsonString = "{ \"myrootNode\": {" + jsonString.Trim().TrimStart('{').TrimEnd('}') + "} }";
                xd = (XmlDocument)JsonConvert.DeserializeXmlNode(jsonString);
                DataSet ds = new DataSet();
                ds.ReadXml(new XmlNodeReader(xd));
                return ds;

Happy Coding !!

Tuesday, March 31, 2015

Filename convenventions while uploading files.


When you attempt to create, save, or rename a file, folder, or shortcut, you may receive one of the following error messages:
A filename cannot contain any of the following characters:\ / : * ? " < > | or This filename is not valid

Resolution :
To create, save, or rename a file, folder, or shortcut, use a valid character, dont use following charachters:
 \ / : * ? " < > |
Characters that are valid for naming files, folders, or shortcuts include any combination of letters (A-Z) and numbers (0-9),
plus the following special characters:
   ^   Accent circumflex (caret)
   &   Ampersand
   '   Apostrophe (single quotation mark)
   @   At sign
   {   Brace left
   }   Brace right
   [   Bracket opening
   ]   Bracket closing
   ,   Comma
   $   Dollar sign
   =   Equal sign
   !   Exclamation point
   -   Hyphen
   #   Number sign
   (   Parenthesis opening
   )   Parenthesis closing
   %   Percent
   .   Period
   +   Plus
   ~   Tilde
   _   Underscore

Happy Coding !! 

Encode Web Output for ASP.NET code that generates HTML using some input

If ASP.NET code that generates HTML using some input, we need to evaluate appropriate action for application.
Encoding output methods:

  •    Encode HTML output.
  •    Encode URL output.
  •    Filter user input.

Encode HTML Output:

If you write text output to a Web page and you do not know if the text contains HTML special characters (such as <, >, and &), pre-process the text by using the HttpUtility.HtmlEncode method as shown in the following code example.
Do this if the text came from user input, a database, or a local file.

Encode URL Output:

If you return URL strings that contain input to the client, use the HttpUtility.
UrlEncode method to encode these URL strings as shown in the following code example.


Filter User Input:

If you have pages that need to accept a range of HTML elements, for example through some kind of rich text input field, you must disable ASP.NET request validation for the page.
If you have several pages that do this, create a filter that allows only the HTML elements that you want to accept.
A common practice is to restrict formatting to safe HTML elements such as bold (<b>) and italic (<i>).
To safely allow restricted HTML input Disable ASP.NET request validation by the adding the ValidateRequest="false" attribute to the @ Page directive.
Encode the string input with the HtmlEncode method.
Use a StringBuilder and call its Replace method to selectively remove the encoding on the HTML elements that you want to permit.

<%@ Page Language="C#" ValidateRequest="false"%>
StringBuilder sb = new StringBuilder(HttpUtility.HtmlEncode(htmlInputTxt.Text));
    sb.Replace("&lt;b&gt;", "<b>");
    sb.Replace("&lt;/b&gt;", "");
    sb.Replace("&lt;i&gt;", "<i>");

New <%: %> Code Nugget Syntax:

With ASP.NET 4 we are introducing a new code expression syntax (<%:  %>) that renders output like <%= %> blocks do – but which also automatically HTML encodes it before doing so.  This eliminates the need to explicitly HTML encode content like we did in the example above.  Instead, you can just write the more concise code below to accomplish the exact same thing:
<%: Model.Content %>

Happy Coding !! 

Defending Against XML Bombs

Using System.Xml.XmlDocument/XmlDataDocument LoadXml() method is potentially unsafe, replace with the Load().The easiest way to defend against all types of XML entity attacks is to simply disable altogether the use of inline DTD schemas in your XML parsing objects.

In .NET Framework versions 3.5 and earlier, DTD parsing behavior is controlled by the Boolean ProhibitDtd property found in the System.Xml.XmlTextReader and System.Xml.XmlReaderSettings classes. Set this value to true to disable inline DTDs completely:

XmlReaderSettings settings = new XmlReaderSettings();
settings.ProhibitDtd = true;
XmlReader reader = XmlReader.Create(xmlstream, settings);
The default value of ProhibitDtd in XmlReaderSettings is true, but the default value of ProhibitDtd in XmlTextReader is false, which means that you have to explicitly set it to true to disable inline DTDs.
In .NET Framework version 4.0 DTD parsing behavior has been changed. The ProhibitDtd property has been deprecated in favor of the new DtdProcessing property. You can set this property to Prohibit (the default value) to cause the runtime to throw an exception if a <!DOCTYPE> element is present in the XML:

XmlReaderSettings settings = new XmlReaderSettings();
settings.DtdProcessing = DtdProcessing.Prohibit;
XmlReader reader = XmlReader.Create(xmlstream, settings);

Happy Coding!! 

AES 256 bits Encryption & Decryption

To Encrypt or decrypt value using AES algorithm use following functions. Following are generalize function which you can use it directly into your project.

1. Encryption:

        /// <summary>
        /// encrypt string using AES algo
        /// </summary>
        /// <param name="data">data string to encrypt</param>
        /// <returns>encrypted string</returns>
        public static string GetEncryptedDataAES(string data)
            AesManaged aesManaged = new AesManaged();
            UTF8Encoding utf8 = new UTF8Encoding();
            aesManaged.Key = utf8.GetBytes("<Encrypttionkey>");
            aesManaged.IV = utf8.GetBytes("<initializationvectorKey>");
            // Encrypt the string to an array of bytes.
            byte[] encrypted = EncryptStringToBytesAES(data, aesManaged.Key, aesManaged.IV);
            data = Convert.ToBase64String(encrypted);
            data = HttpContext.Current.Server.UrlEncode(data);
            return data;
        /// <summary>
        ///  encrypt string to byte using AES algo
        /// </summary>
        /// <param name="plainText">sring to encrypt</param>
        /// <param name="Key"> encrypt key</param>
        /// <param name="IV">Initialization vector</param>
        /// <returns>byte array of encrypt string</returns>
        static byte[] EncryptStringToBytesAES(string plainText, byte[] Key, byte[] IV)
            // Check arguments.
            if (plainText == null || plainText.Length <= 0)
                throw new ArgumentNullException("plainText");
            if (Key == null || Key.Length <= 0)
                throw new ArgumentNullException("Key");
            if (IV == null || IV.Length <= 0)
                throw new ArgumentNullException("IV");
            // Declare the stream used to encrypt to an in memory
            // array of bytes.
            MemoryStream msEncrypt = null;
            // Declare the AesManaged object
            // used to encrypt the data.
            AesManaged aesAlg = null;
                // Create a AesManaged object
                // with the specified key and IV.
                aesAlg = new AesManaged();
                aesAlg.Key = Key;
                aesAlg.IV = IV;
                // Create an encryptor to perform the stream transform.
                ICryptoTransform encryptor = aesAlg.CreateEncryptor(aesAlg.Key, aesAlg.IV);
                // Create the streams used for encryption.
                msEncrypt = new MemoryStream();
                using (CryptoStream csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write))
                    using (StreamWriter swEncrypt = new StreamWriter(csEncrypt))
                        //Write all data to the stream.
                // Clear the AesManaged object.
                if (aesAlg != null)
            // Return the encrypted bytes from the memory stream.
            return msEncrypt.ToArray();


2. Decryption:

        /// <summary>
        /// decrypt the encrypted string using AES
        /// </summary>
        /// <param name="data">string to decrypt</param>
        /// <returns>decrypted string </returns>
        public static string GetDecryptedDataAES(string data)
            byte[] dataBytes = Convert.FromBase64String(data);
            AesManaged aesManaged = new AesManaged();
            UTF8Encoding utf8 = new UTF8Encoding();
            aesManaged.Key = utf8.GetBytes("<Encrypttionkey>");
            aesManaged.IV = utf8.GetBytes("<initializationvectorKey>");
            data = DecryptStringFromBytesAES(dataBytes, aesManaged.Key, aesManaged.IV);
            return data;
        /// <summary>
        /// decrypt encrypted string from byte using AES algo
        /// </summary>
        /// <param name="cipherText">byte array to decrypt</param>
        /// <param name="Key"> encrypt key</param>
        /// <param name="IV">Initialization vector</param>
        /// <returns>decrypted string </returns>
        private static string DecryptStringFromBytesAES(byte[] cipherText, byte[] Key, byte[] IV)
            // Check arguments.
            if (cipherText == null || cipherText.Length <= 0)
                throw new ArgumentNullException("cipherText");
            if (Key == null || Key.Length <= 0)
                throw new ArgumentNullException("Key");
            if (IV == null || IV.Length <= 0)
                throw new ArgumentNullException("IV");
            // Declare the RijndaelManaged object
            // used to decrypt the data.
            AesManaged aesAlg = null;
            // Declare the string used to hold
            // the decrypted text.
            string plaintext = null;
                // Create a AesManaged object
                // with the specified key and IV.
                aesAlg = new AesManaged();
                aesAlg.Key = Key;
                aesAlg.IV = IV;
                // Create a decrytor to perform the stream transform.
                ICryptoTransform decryptor = aesAlg.CreateDecryptor(aesAlg.Key, aesAlg.IV);
                // Create the streams used for decryption.
                using (MemoryStream msDecrypt = new MemoryStream(cipherText))
                    using (CryptoStream csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Read))
                        using (StreamReader srDecrypt = new StreamReader(csDecrypt))
                            // Read the decrypted bytes from the decrypting stream
                            // and place them in a string.
                            plaintext = srDecrypt.ReadToEnd();
                // Clear the AesManaged object.
                if (aesAlg != null)
            return plaintext;

Happy Coding

Redirecting non-www requests to www requests

Redirection of Non-WWW domain to WWW (301 Redirection – permanent) is very important for ranking on search engine like Google.

The Problem is that the Google search consider to be a different domain than so it makes the difference in search engine back links because you have made it with and without www prefix.

It is better to have every link use exactly the same form of your domain. for this purpose, it is common to redirect request from non-www to www.

We can do it in by many ways but I mentioned 2 solutions below -

1. web.config : 

       It is most common practice to do the redirection from non-www to www because we don’t have the IIS access on shared hosting environment.

Make sure you replace with the name of your domain.

        <rule name="Redirect to WWW" stopProcessing="true">
          <match url=".*" />
            <add input="{HTTP_HOST}" pattern="^$" />
          <action type="Redirect" url="{R:0}"
                  redirectType="Permanent" />

2. Global.asax.cs  : 

          If you wish to do it from within your application, use the Application_BeginRequest in Global.asax.cs to intercept the request and do a 301 (permanent) redirection on the url.

 NOTE - you will need to set up the bindings of the site in IIS to accept both the and host names.

protected void Application_BeginRequest(object sender, EventArgs ev)
   string FromHomeURL = "";
   string ToHomeURL = "";
       HttpContext.Current.Response.Status = "301 Moved Permanently";
       Request.Url.ToString().ToLower().Replace(FromHomeURL, ToHomeURL));

Happy Coding !! 

Tuesday, February 17, 2015

Read all keys and values from resource file

I have added Resource1.resx file in my project. This reosurce file contains some key - value entries.

Scenario : Suppose you have localized country list and you have stored localized country list in resource file for performance reason. You can store localized list in sql table but if you want to avoid database hit and read it on server side.

This function reads Resource1.resx file and reads all keys of that resource file.

 public static List<string> GetAllResourceKeyStrings()
            List<string> resourceKeyStrings = new List<string>();
             ResourceSet resourceSet = null;
            resourceSet = Resource1.ResourceManager.GetResourceSet(CultureInfo.CurrentCulture, true, true);
            IDictionaryEnumerator Myenumerator = resourceSet.GetEnumerator();
            while (Myenumerator.MoveNext())
            return resourceKeyStrings;

Happy Coding!!

Wednesday, January 28, 2015

Change SQL Server user login account password

Change SQL Server user login account password script:
Sometimes you need to change the password for a particular user in that case following script will help you to change the password.

in following Example :
Login account user name is : dbdevwebuser
Current password is : MyCurrentPwd
New password is : MyNewCurrentPwd

We can use following script to change the users login password.
ALTER LOGIN dbdevwebuser WITH PASSWORD = N'MyNewCurrentPwd' OLD_PASSWORD = 'MyCurrentPwd'
Happy Coding !! 

Friday, January 16, 2015

Reseeding/reset the identity of table.

Generally, on inserting the new data into table we get next seed value for identity column. When a row is deleted, we cannot use that identity value again. We can resolve it by reseeding the identity of table as follows:

1. SELECT * FROM Sample_Data

2. DELETE FROM Sample_Data WHERE Number > 4

SELECT @MaxID=MAX(Number) FROM Sample_Data


Checking identity information: current identity value '16'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
4. INSERT INTO Sample_Data(Value) VALUES('Second')
   Go 3


Happy Coding!!

Monday, January 12, 2015

Find Most Expensive Queries Using DMV

Find Most Expensive Queries Using Dynamic Management Views(DMV)

When you try to find out the performance of an sql server, this query will help us.
It records how many times that execution plan get re-used by following execution. This will give us the count of the execution. It records the number of logical reads, logical writes, elapsed time etc. all this counter will help to find poor performer while monitoring SQL Server queries.
The query will use the Dynamic Management Views. DMV returns server state information that can be used to monitor the health of the server instance, diagnose problems and performance.
Following is the query which finds out top 10 most expensive queries

SELECT TOP 10 SUBSTRING(qt.TEXT, (qstat.statement_start_offset/2)+1,

  ((CASE qstat.statement_end_offset


  ELSE qstat.statement_end_offset

  END - qstat.statement_start_offset)/2)+1),


  qstat.total_logical_reads, qstat.last_logical_reads,

  qstat.total_logical_writes, qstat.last_logical_writes,



  qstat.total_elapsed_time/1000000 total_elapsed_time_in_S,

  qstat.last_elapsed_time/1000000 last_elapsed_time_in_S,



FROM sys.dm_exec_query_stats  qstat

CROSS APPLY sys.dm_exec_query_plan(qstat.plan_handle) qplan

CROSS APPLY sys.dm_exec_sql_text(qstat.sql_handle)  qt

ORDER BY qstat.total_logical_reads DESC -- logical reads

-- ORDER BY qstat.total_logical_writes DESC -- logical writes

-- ORDER BY qstat.total_worker_time DESC -- CPU time

Happy Coding !!