Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PracticalSQLDba

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.

SQL Server : Part 4 :Explaining the Non Clustered Index Structure

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…

Read more

3 comments, 4,898 reads

Posted in PracticalSQLDba on 14 March 2013

SQL Server : Index Part 3 :Explaining the Clustered Table Structure

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…

Read more

0 comments, 599 reads

Posted in PracticalSQLDba on 12 March 2013

SQL Server : Understanding the IAM Page

In my earlier post, we have discussed about Data,GAM,SGAM and PFS pages. In this post, let us try to understand about the IAM (Index Allocation Map) page.

In SQL server 2005 and later, there are three types of allocation units.
  1. IN_ROW_DATA (btree and heap) allocation unit

Read more

8 comments, 662 reads

Posted in PracticalSQLDba on 7 March 2013

SSRS: Report Server Usage Report

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…

Read more

0 comments, 438 reads

Posted in PracticalSQLDba on 6 March 2013

SQL Server : Index Part 2 : Structure of Heap Tables

In the earlier post we 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 
  • A table which does not…

Read more

0 comments, 389 reads

Posted in PracticalSQLDba on 6 March 2013

Windows Server 2008 Cluster: Understanding the Cluster Log Behavior

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…

Read more

0 comments, 276 reads

Posted in PracticalSQLDba on 17 October 2012

SQL Server : Index Part 1 : Basics of index



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…

Read more

6 comments, 280 reads

Posted in PracticalSQLDba on 11 October 2012

SQL Server:Understanding The Page Free Space (PFS) Page

In my earlier post, we have discussed about GAM and SGAM Page and Data Page. In this postlet us discuss about the Page Free Space (PFS) page.

PFS page is the second  page (Page Number 1) in the data file followed by file header (Page Number 0). GAM…

Read more

0 comments, 283 reads

Posted in PracticalSQLDba on 10 October 2012

SQL Server: Sending Email in HTML Table Format Using TSQL

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…

Read more

0 comments, 634 reads

Posted in PracticalSQLDba on 4 October 2012

SQL Server : Setting up Server Side Trace

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…

Read more

2 comments, 705 reads

Posted in PracticalSQLDba on 2 October 2012

SQL SERVER : How to List All Email Subscription configured in SSRS ?

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…

Read more

5 comments, 2,303 reads

Posted in PracticalSQLDba on 27 September 2012

SQL Server: Part 4 : Approaching Database Server Performance Issue

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.

The below query will help us to list the CPU intensive queries…

Read more

0 comments, 768 reads

Posted in PracticalSQLDba on 26 September 2012

SQL Server: Part 3 : Approaching Database Server Performance Issue

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.  

******************************************************************************************/
CREATE FUNCTION [dbo].dba_GetStatementForSpid
(  
   @spid SMALLINT  
)  
RETURNS NVARCHAR(

Read more

0 comments, 475 reads

Posted in PracticalSQLDba on 24 September 2012

SQL Server: Part 2 : Approaching Database Server Performance Issues

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…

Read more

2 comments, 2,624 reads

Posted in PracticalSQLDba on 21 September 2012

SQL Server: Part 1 : Approaching Database Server Performance Issues

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…

Read more

0 comments, 580 reads

Posted in PracticalSQLDba on 18 September 2012

How to Connect PerfMon to a Remote Server ?

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…

Read more

2 comments, 1,440 reads

Posted in PracticalSQLDba on 17 September 2012

SQL Server: String Pattern Matching

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…

Read more

2 comments, 2,035 reads

Posted in PracticalSQLDba on 13 September 2012

SQL Server: Understanding GAM and SGAM Pages

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…

Read more

5 comments, 3,357 reads

Posted in PracticalSQLDba on 12 September 2012

SQL Server : Usage of OVER Clause

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…

Read more

8 comments, 4,974 reads

Posted in PracticalSQLDba on 10 September 2012

SQL Server:Output Clause

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…

Read more

0 comments, 620 reads

Posted in PracticalSQLDba on 9 September 2012

Newer posts

Older posts