SQL Server 2000 to 2005 Upgrade

There are various methods in place for the SQL Server upgradation from 2000 to 2005 and 2005 to 2008.  Microsoft itseld provides a well tuned ‘Tuning Adviser’ which compares the various mismathches and the future non-compatability issues when you upgrade to the latest versions.

1. Microsoft SQL Server 2005 Upgrade Advisor is available at http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
2. Microsoft SQL Server 2008 Upgrade Advisor is available at http://www.microsoft.com/downloads/details.aspx?familyid=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en

The following links provide you excellent information about the upgradation process and hick-ups.

• http://www.sqlservercentral.com/columnists/ssamuel/2987.asp
• http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
• http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en

 

XML format to SQL Server Execution Plan and vice versa

This post gives a brief overview about

1. How the execution plan can be saved in a XML format and 
2. How the saved XML file about query plan be seen in a GUI format.

1. SHOWPLAN_XML generates compile-time query execution plan information for a query, but does not execute the query. STATISTICS XML generates run-time query execution plan information for a query, and executes the query.

So set the necessary setting on.

SET SHOWPLAN_XML ON

<< Query >>

SET SHOWPLAN_XML OFF

In the Results pane, right-click the Microsoft SQL Server 2005 XML Showplan that contains the query plan, and then click Save Results As and also select ‘All Files’ in the type column. Save the plan with extension, ‘.sqlplan’

2. Loading the plan is as simple as that,

Just open SQL Server management studio, File -> Open. Select all types in the type column. Look out for the plan which is saved in the .sqlplan format.

You can now see the graphical format of the plan which you have in XML.

Other references:

http://technet.microsoft.com/en-us/library/ms190646(SQL.90).aspx

http://www.johnsansom.com/index.php/2009/05/how-to-identify-the-most-costly-sql-server-queries-using-dmvs/

- This is a good post about identifying Top 20 CPU utilising queries in your system and their query plans via various DMVs. Once you get a graphical query plan, you can easily find the real culprit from the cost, where the maximum percentage lies. 

 

SQL Server High Memory Usage Alert

Hi DBA folks,

This is a post especially for helping the DBA community which always gets bogged down by windows admin people and alerts which crop down daily..  

Memory reached threshold limit < 75% or 85%. Or free memory less than 100 MB. Please look after. I have been bogged down by lot of alerts like these atleast twice or thrice a week. 

Here are some possible solutions you can make up here is that,

1. You can collect the evidence from Microsoft technet and knowledge base. I have a good knowledge base article,
   http://support.microsoft.com/kb/321363 
2. The link explains how the memory consumption of SQL Server is dynamic and it consumes memory even if the activity is low. This is a property of SQL Server. However if the operating system or other applications* need memory SQL Server releases the un used memory. Else it is hel up in buffer pool cache.In the words of the KB article itself,
   “SQL Server memory usage may continue to steadily increase and not decrease, even when activity on the server is low. Additionally, the Task Manager and Performance Monitor may show that the physical memory available on the computer is steadily decreasing until the available memory is between 4 to 10 MB. 

   This behavior alone does not indicate a memory leak. This behavior is normal and is an intended behavior of the SQL Server buffer pool.” 

3. You can then request the windows admin team personnel or automation team (The team which sets the alerts in memory limit) to reduce the threshold limit.  Dont forget to give the proper justification stating the Microsoft article and provide the link.The limit I suggest is to have an alert if free memory is < 30 MB (50MB if the memory available is high in your case).

One more point to be noted here is that, the server which hosts the SQL Server should not host other applications. This is a Microsoft recommended standard. I could not find any official article for this, but any Microsoft consultant would suggest the same. So try to host and plan the other applications on a seperate server if your budget permits.

 

Understanding SQL Server Data Storage Internals

I had just put this blog off to my archives, since long time. A sudden selfish interest has come now, I have decided to update this blog often atleast to enable me to ponder more things in SQL Server.

Let me look into the SQL Server Data Storage internals now. The most of the following descriptions are taken from the Microsoft technet. I have just organised it in a better manner to understand

We will split into three sections,

1. Heap storage

2. Clustered index storage

3. Non-clustered index storage

Though there are lot of sites to explain this (Esp. Books Online) , I ll try to explain this to understand easily.

Heap Storage

SQL Server uses the IAM pages to move through the heap. The first IAM page will be normally located in the starting part of the mdf file. An IAM (Index Allocation Map) page tracks approximately 4GB worth of space in a single file, aligned on a 4GB boundary. For the next 4GB, another IAM page will be allocated. This need not again be allocated in the start of the mdf file, this can be allocated anywhere in the mdf and IAM chains are formed with the previous IAM page. An IAM page has a header that indicates the starting extend of the range of extends covered by the IAM page. The IAM page then contains a large bit map in which eat bit represents a extend. The first bit represents the first extend covered in the 4GB pile (also called ‘GAM interval’) , second bit represents the second interval and so on.

Accessing the data in the Heap:

————————————–

The data pages and the rows within them are not in any specific order and are not linked. The only logical connection between data pages is the information recorded in the IAM pages.  Table scans or serial reads of a heap can be performed by scanning the IAM pages to find the extents that are holding pages for the heap.

Clustered Index Storage

In SQL Server, indexes are organized as B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list. 

The pages in the data chain and the rows in them are ordered on the value of the clustered index key. All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows. SQL Server moves down the index to find the row corresponding to a clustered index key. To find a range of keys, SQL Server moves through the index to find the starting key value in the range and then scans through the data pages using the previous or next pointers. To find the first page in the chain of data pages, SQL Server follows the leftmost pointers from the root node of the index.

Non Clustered Indexe Storage

Non clustered indexes also have the structure of a B tree and the difference is that the leaf node of the index has index pages and not data as in clustered index.  

Nonclustered indexes can be defined on a table or view with a clustered index or a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value.

The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

• If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).
• If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. 
   

I have tried to sort out the various data storage and access methods of SQL Server from various sources for best understanding. Hope it is useful for the reader.

Love,

CK.

References:

————–

http://msdn.microsoft.com/en-us/library/ms180978.aspx

https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/24/645803.aspx

 

MCDBA Dumps

Hi friends,

I found really hard to get the dumps of MCDBA, so I share the dumps of the exams, 70-228, 70–29 and 70-290 here. Click the links below.

70-228

http://karthikeyanc.wordpress.com/2007/10/12/sql-2000-server-70-228-dumps/

70-229

http://karthikeyanc.wordpress.com/2007/11/12/70-229-dumps/

70-290

http://karthikeyanc.wordpress.com/2007/11/12/70-290-dumps/

All the Best.

CK

 

A mild Start

Hi people.,

As I heard from a collegue of mine, ” There s always a first time “. And I am having a little knowledge in SQL Server to start with. And I am greatly supportive for the concept of Knowledge sharing. I start my blog from the basic concepts of SQL Server.

There are number of ways we can start and stop the SQL Server. Here are few.

Starting SQL Server:                                                        

 i.      net start mssqlserver                                                            

 ii.      Prog Files -> MS SQL Server -> MSSQL -> Binn -> sqlservr.exe                                                          

iii.      Right Click Server Icon in System Tray                                                         

 iv.      Start Menu                                                           

v.      ‘Start if Stopped’ Option in QA                                                         

vi.      Services.msc Start Service

Stopping SQL Server

 i.      ‘Shutdown’ Command                                                            

 ii.      Right Click Server Icon in System Tray                                                          

 iii.      Services.msc Stop Service

iv.      In Enterprise Manager

To Check the various properties of server, following commands can be used.

Check Edition  -  select serverproperty(‘Edition’)

Check Instance name – select serverproperty(‘InstanceName‘) -Returns NULL if default instance

Check Failover Clustering  - select serverproperty(‘IsClustered’)

Check if in Single User Mode –  select serverproperty(‘IsSingleUser’)

Check License Type of Server – select serverproperty(‘LicenseType’)

Check Processor Count, Processor Type, Physical Memory, WindowsVersion, ProductName, ProductVersion –  Xp_msver

Check Server Name

i.       Select serverproperty(‘servername’)

 ii.      Select @@servername

Continued in part II.

Squirrel will play more. Ck.

 

A Hello from Squirrel

Hi People.

This is my first post. I ve dedicated this blog for the techie stuffs of SQL Server.

This blog is a Silent bubble over Sea.

Hippo. CK.