|
|
|
Get Your Own Blog
If you would like to blog on SQLServerCentral.com then send an email to
webmaster@sqlservercentral.com.
Contact the author
All Blogs
Feeds
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.
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
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
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
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
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
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
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
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
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
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.
- IN_ROW_DATA (btree and heap) allocation unit
Read more
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
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
Read more
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
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
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
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
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
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
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
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
Older posts
|
Subscribe to this blog
