Identified and tuning your Query to improve Performance

As a part of my current job I have got opportunity to work with production server with so much work load on database. There were million records in a table and Query tooks too much time to execute. I engaged to find out performance tuning of your query. That time I was little confusion how we can identified improve our query performance. Finally I have come with one solution.

SQL have tool called SQL Profiler tool have tuning template to calculate work load for database. SQL profiler tool capture event that were performing of current database.

From Trace property --> Generate template we select “tuning” template and save it as .trc file.  With help of this we can create work load of our query.  Now all of us have question how to improve our query performance till now we only generate .trc file. How it will used to improve our query? Now be attention SQL having one tool called Database Engine Tuning Advisor – that can help you determine, tune and monitor your indexes.

In this article, I will explain how to use these tools to get answers to the following questions:

• Which indexes do I need for my queries?
• How do I monitor index usage and their effectiveness?
• How do I identify redundant indexes that could negatively impact performance of my DML queries (insert, updates and deletes)
• As workload changes, how I do I identify any missing indexes that could enhance performance for my new queries?

Find the right indexes for your work load

Determining exactly the right indexes for your system can be quite a taxing process. For example, you have to consider:

• Which columns should be indexed (based on your knowledge of how the data is queried)
• Whether to choose a single-column index or a multiple column index
• Whether you need a clustered index or a non-clustered index
• Whether or not (in SQL 2005) you could benefit from an index with included columns to avoid bookmark lookups
• How to utilize indexed views (which the optimizer might access instead of the underlying tables to retrieve a subset of your data)

.DTA can analyze both OLTP and OLAP workloads. You can either tune a single query or the entire workload to which your server is subjected. Based on the options that you select, you can use the DTA to make recommendations for several Physical Design Structures (PDS), which include:

• Clustered indexes
• Non-clustered indexes
• Indexes with included columns (to avoid bookmark lookups)
• Indexed views
• Partitions

Following step to import trace file into DETA tool

1)    Create New Session

2)    Import .trc file as per following screen shot

3)    Select database for work load analysis

4)    Press “Start Analysis” button to identified improvement of your work load.

Shrinking Truncate Log File – Log Full

Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO 

you can get availability space, file name, physical location using following query.

SELECT name AS 'File Name' , physical_name AS 'Physical Name',
        size/128 AS 'Total Size in MB', size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *
FROM sys.database_files;

Regulare Expression for Comma Separated Email

Regular Expression Validation Expression for Comma Separated Email Address

\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*([,]\s*\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*)*

Change Authentication Mode into SQL Server 2008

This topic describes how to change the server authentication mode in SQL Server 2008 by using SQL Server Management Studio.

To Change security authentication mode

1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
4. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To Enable the "sa login

1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
2. On the General page, you might have to create and confirm a password for the login.
3. On the Status page, in the Login section, click Enabled, and then click OK

Client ID mode in asp.net

We have been using ClientID’s in ASP.NET 2.0/3.5 that makes each control to generate the unique client side id attribute to that page or browser. But these ID’s were long and randomly generated. Developers who work on Client-Side programming that uses Java Script, JQuery or Ajax have been suffering a lot spending considerable amount of time when it they need to reference those ClientID’s in the client side scripts.

Now the good news is that, ASP.NET 4.0 comes with new ClientIDMode property, gives full control to the developer on the ClientID’s generated by ASP.NET controls.

ClientIDMode can take the following four possible values

• AutoID - ASP.NET generate IDs as it does in v3.5 and earlier versions.
• Static - ASP.NET use exactly the same ID given to the server control for its client-side ID.
• Predictable - ASP.NET try to generate IDs that are guessable from looking at the structure of the page.
• Inherit - ASP.NET generate a client-side ID for the page or control using the same ClientIDMode as its parent. i.e. the client ID gets inherited from the parent control.

You can set this property in 3 ways

1.Control Level

2.Page Level

3.Application Level

Setting ClientIDMode at Control Level

Each and every server control in ASP.NET 4.0 has this property and the default value is inherit.

1.<asp:panel id="pnl" runat="server" cssclass="newStyle1"

2.ClientIDMode ="Static"> </asp:panel>

Setting ClientIDMode at Page Level

1.<%@ Page Language="C#" ClientIDMode ="Inherit"

2.AutoEventWireup="true"

3.CodeBehind="Category.aspx.cs"

4.Inherits="WebApplication3.Cat" %>

Setting ClientIDMode at Application Level

You need to set it at System.Web section of Web.config

1.<system.web>

2.<pages clientIDMode="Predictable">

3.</pages> 

4.</system.web>

ClientIDRowSuffix

Another interesting feature of the ClientID improvement in ASP.NET 4.0 is the ClientIDRowSuffix .This can be applied to DataBound or List controls. This is used to take control on how ID values for template controls in databound controls are generated. This requires that the ClientIDMode is set to Predictable.

1.<asp:GridView runat="server" 

2.ID="gvEmp" AutoGenerateColumns="False" 

3.ClientIDMode ="Predictable" ClientIDRowSuffix="EMPID">

4.</asp:GridView>

Best Practices

• Add ClientIDMode = "Static" in application level web.config
• Using ClientIDMode = "AutoId" will work the best even in worst cases.
• Add ClientIDMode = "Predictable" to each List Control Children Item Template of Databound Controls.
• When ever naming conflicts occurs Override ClientIDMode to Predictable
• When working with Web Server Control Development leave at default behavior i.e. Inherit from parent
• Override if and only if necessary that might be in individual sub controls