Archives
for this blog

I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.

A table can have only one clustered index as the data rows are stored in the order of the clustered key, but a table can have multiple non clustered indexes.We have discussed about clustered index structure in our earlier post. In this post let us try to understand…

In SQL server, there are two types of table based on the storage.A table with clustered index is called Clustered Table and a table with out a clustered index is called Heap Table. In earlier post, we have discussed about the specialty and the storage structure of the heap…

While working with SSRS (SQL Server Reporting Service) it is very common to look into the Execution log to understand the usage pattern of the report that are deployed or to troubleshoot the performance issues.SSRS stores the detailed execution plan in a table named ExecutionLog which resides in the SSRS…

In the earlier postwe tried to understand the difference between a table having clustered index and does not have a clustered index. A table with clustered index is called clustered table.A table with out clustered index is called a heap table.Heap Table

In any system, logs are very important to troubleshoot the issue. In the case of windows cluster, cluster logs are like a black box which will have all information about the failure of cluster.This is very important information, that we need, to raise a case with Microsoft. In this post let…

While conducting interviews , I have noticed that, many people does not have clear picture about index.Many of them does not have clear picture about the difference between clustered and non clustered index.When people ask about index, it is tough to explain in one go. I feel it is worth…

As part of our DBA life , we have to configure many alert or statistical mails which gives an idea about the state of the database server. Let us discuss how we can send an email in HTML table format using TSQL. I am just going to give a sample…

For a DBA, SQL profiler is a great tool to trouble shoot performance issues. It is a pain to configure a trace and analyse it using the profiler GUI . I usually use server side trace which can be configured by running set of scripts. Server side traces are much lighter as…

Often I used to get request from my business managers to get a list of all subscriptions configured in SSRS (SQL Server Reporting Service) along with email distribution list. I did not find any option in report server report manager interface to achieve this. I usually run the below query on…

In the last three parts, we have discussed about different queries that can be used to list the current state of the database server. In this post let us discuss about listing execution stats from the plan cache.

In the last post, we have discussed the script to list the sessions which are waiting for resource or currently running. In this post let us see how to list the blocking sessions with required information.

In the Part 1, we have seen how quickly we can check the runnable task and I/O pending task on an SQL server instance. This is very light weight script and it will give the result even if the server is under pressure and will give an over all…

When you work as DBA, many people will approach you with a complaint like "Application is taking ages to load the data on a page,could you please check something going wrong with database server?" There might be hundred of other reason for slowness of the page.It might be a Problem…

Till now, we do not have any third party tool to monitor the SQL server, but we have implemented many custom alerts and report to help us in this regards.As our environments are growing, we have decided to evaluate couple of third party monitoring tools. Yesterday we have installed the Red Gate SQL…

It is common scenario that, we might need to extract the data from the SQL server based on some pattern. For example extract all customers information who has a valid PAN card number (XXXXX0000X). SQL server is not very powerful in pattern matching.We can easily implement simple pattern matching but…

We know that SQL server stores the data in 8 KB pages. An extent is made up of 8 physically contiguous pages.When we create a database, the data files will be logically divided into the pages and extents.Later, when user objects are created, the pages are allocated to them to store…

Over clause can be used in association with aggregate function and ranking function. The over clause determine the partitioning and ordering of the records before associating with aggregate or ranking function. Over by clause along with aggregate function can help us to resolve many issues in simpler way. Below is a sample…

Couple of days back, one of my colleague came to me asking for help. He is inserting multiple record from a XML to a table which has identity column . He need those newly generated identity values to insert into one more supporting table. This is a very common scenario and…