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 1 : Architecture of Transaction Log

In our earlier post, we have discussed in detail about the architecture of the data file and different types of pages. In this post, we will be discussing about the architecture of log file.

Logging is a mechanism used in RDBMS to support various ACID (Atomicity,Consistency,Isolation and Durability) properties of transactions. A transaction…

Read more

0 comments, 1,432 reads

Posted in PracticalSQLDba on 30 September 2013

SQL Server: Data File Structure

In our earlier posts , we have discussed about various types pages in details. In this post let us discuss about the organization of the data file.In other words how these pages are organized in the data file.

As you know, SQL server divides the data file into 8 KB…

Read more

0 comments, 367 reads

Posted in PracticalSQLDba on 2 September 2013

SQL Server : Understanding Bulk Change Map (BCM) Page and Bulk Logged Recovery Model

In the last post , we have discussed about the Differential Change Map pages and how it is utilized in the differential backup process.In this post, let us try to understand the bulk change map page and how the bulk logged recovery model works.


Bulk Change Map(BCM): SQL Server uses…

Read more

1 comments, 510 reads

Posted in PracticalSQLDba on 24 July 2013

SQL Server : Understanding Differential Change Map (DCM) Page and Differential Backups

In our earlier posts, we have discussed about different types of pages.Data Pages, GAM and SGAM,PFS page and IAM page. In this post, we will try to understand the Differential Change Map (DCM) pages and differential backups.

Differential Change Map(DCM)SQL Server uses Differential Changes…

Read more

0 comments, 340 reads

Posted in PracticalSQLDba on 23 July 2013

SQL Server: Performance Tuning :Understanding Set Statistics Time output

In the last post we have discussed about Set Statistics IO and how it will help us in the performance tuning. In this post we will discuss about the Set Statistics Time which will give the statistics of time taken to execute a query.

Let us start with a example.

Read more

6 comments, 2,224 reads

Posted in PracticalSQLDba on 17 July 2013

SQL Server: Performance Tuning :Understanding Set Statistics IO output

Performance tuning is one of the important task of DBA. Many times people comes to us with different types of performance issue.To handle all these, we should have through knowledge in SQL server. The common question people used to ask me,this procedure used to complete in a reasonable time in…

Read more

0 comments, 2,113 reads

Posted in PracticalSQLDba on 16 July 2013

SQL Server: All About SQL Server Statistics : Consolidated post

Statistics is the one of the important factor which helps the optimizer to choose the right execution plan.At the same time, many of us (so called DBA) will not give enough attention to statistics while troubleshooting the performance issues.It might be because of lack of knowledge in statistics or lack…

Read more

0 comments, 388 reads

Posted in PracticalSQLDba on 10 July 2013

SQL Server: Part 5 : All About SQL Server Statistics : How to Detect outdated Statistics ?

In the last post, we have discussed about the auto update statistics threshold and concluded that auto updated statistics threshold are good enough to get optimal performance in some workload. In many cases,a manual update of statistics will help to obtain better performance. In this post let us discuss, how to…

Read more

0 comments, 567 reads

Posted in PracticalSQLDba on 8 July 2013

SQL Server: Part 4 : All About SQL Server Statistics : Auto Update Statistics Threshold - Importance of Manual statistics maintenance

In the last post, we have discussed about the auto create and auto update property of the statistics. Do we really need to do manual statistics maintenance to keep the performance optimal? The answer is yes, depends on your work load. SQL server will do auto update statistics only…

Read more

0 comments, 2,048 reads

Posted in PracticalSQLDba on 2 July 2013

SQL Server: Part 3 : All About SQL Server Statistics :Who Create and Manage the Statistics ? Role of Statistics in Performance Tuning

In the last posts, we have discussed about the histogram ,density vector and how SQL server use the statistics information in cardinality estimation. In this post let us discuss about how the statistics are getting created and the importance of statistics in performance tuning.

There are two types of statistics,…

Read more

0 comments, 408 reads

Posted in PracticalSQLDba on 1 July 2013

SQL Server: Part 2 : All About SQL Server Statistics :Histogram

In the Part 1 about SQL server Statistics, we have discussed about density vector information stored in the the statistics. In this post, let us discuss about the histogram. Let us create a copy of SalesOrderDetail table and two indexes on top of that as we did in our first…

Read more

6 comments, 2,549 reads

Posted in PracticalSQLDba on 27 June 2013

SQL Server: Identifying Edition Specifc features Used in a Database

I had a situation which forced me to do a feasibility study to move SQL server 2008 enterprise edition environment to standard edition. It is hard to check is there any edition specific features are implemented in any of the databases. It will be more complex process if the environment contain many…

Read more

0 comments, 307 reads

Posted in PracticalSQLDba on 26 June 2013

SQL Server: Part1 : All About SQL Server Statistics :Density Vector

In our daily life, we use the statistics to take the decision. In the same way SQL server optimizer  use the statistics to choose the right query plan. if the statistics are wrong or outdated , SQL server might choose a wrong query plan. In this post, let us try…

Read more

1 comments, 616 reads

Posted in PracticalSQLDba on 25 June 2013

SQL Server : Index at a glance

In the last ten posts, we have discussed in details about the SQL server index and its storage structure. In this post I will consolidate all the ten post for easy reference.

What is index ?
In the first post, we have discussed about index in general. We tried to…

Read more

1 comments, 475 reads

Posted in PracticalSQLDba on 16 April 2013

SQL Server : Part 10: Importance of Key Column Position While Creating Index

Now we have discussed about different types of indexes in the last posts. In this post, let us discuss about the key column order (order of the column of indexes). The order of the key column of the index is decided based on the data access pattern and how do…

Read more

1 comments, 2,866 reads

Posted in PracticalSQLDba on 16 April 2013

SQL Server : Part 9 : Filtered Index : A new way for Performance Improvemnt

Filtered index is a new feature introduced in SQL Server 2008. All the indexes that we have discussed till now were always on the entire table. In other words, Index and table will have same number of records.With filtered index, it is possible to create an index for a subset…

Read more

3 comments, 1,691 reads

Posted in PracticalSQLDba on 8 April 2013

SQL Server : Part 8 : Explaining The Covering Index or Included Columns

In our earlier discussion about non clustered index ,we have seen that, the leaf level of a non clustered index contain only the non clustered index key column and clustered index key (if the table is a clustered table). To fetch the remaining column from the clustered index structure or…

Read more

9 comments, 1,191 reads

Posted in PracticalSQLDba on 25 March 2013

SQL Server : Part 7 : Non clustered index on non unique column

In our earlier  post, we have discussed about the non clustered index, but there we were always discussing  about unique non clustered index to make the discussion simple.As we understood the general structure of the non clustered index, let us discuss the storage structure of a non clustered…

Read more

1 comments, 485 reads

Posted in PracticalSQLDba on 24 March 2013

SQL Server : Part 6 : Design consideration of Clustered Index

In our earlier post, we have discussed about the structure and storage of clustered index. In this post we will discuss about the design consideration of clustered index.There are couple of points that need to be considered while selecting clustered index key.There is no hard rule in selecting the clustering…

Read more

0 comments, 574 reads

Posted in PracticalSQLDba on 21 March 2013

SQL Server : Part 5 : Explaining Non clustered Index on Heap

In the last post, we have discussed about non clustered index on a clustered table.In this post we will discuss about the non clustered index on a heap table. 

Non clustered index can be created on clustered table as well as heap table.While creating a non clustered index on…

Read more

0 comments, 1,297 reads

Posted in PracticalSQLDba on 17 March 2013

Older posts