Search This Blog & Web

Tuesday, November 27, 2012

Creating schema of XML structure

In previous post we have seen how can we suggest XML structure from a Rational db desgin and how it understand db structrue with XML column in table. http://shamas-saeed.blogspot.com/2012/06/selecting-xml-structure-from-relational.html
 
I have created XML structure successfully but do not know how to create its schema that is required to bind it with XML data type in SQL Server. It is very easy to create schema from XML structure. Lets consider both XML that we have pasted in our last post and follow these steps to create its schema.
 
Open Visual Studio from your system and create a new file, it will pop up following window to confirm which type of file you want to create.
 
 
 

Select XML Schema as file type and new file is created with some sample code. Delete that code and paste your XML in that file as shown below. From tool panel you must select XML Editor to show XML Properties tab.
 
 
 
 

Now click on create schema option from top left cornor of XML Editor pannel. It will create XML schema for your XML file.

 
 
 
Following are the schema's for both XML examples we have discussed in previous posts.
 
========================= User Criteria XML Structure ==================================

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="User">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="Level">
          <xs:complexType>
            <xs:sequence minOccurs="0">
              <xs:element name="Criteria">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="Value" type="xs:string" />
                  </xs:sequence>
                  <xs:attribute name="Type" type="xs:string" use="required" />
                  <xs:attribute name="TypeId" type="xs:unsignedByte" use="required" />
                  <xs:attribute name="FunctionId" type="xs:unsignedByte" use="required" />
                </xs:complexType>
              </xs:element>
            </xs:sequence>
            <xs:attribute name="Id" type="xs:unsignedByte" use="required" />
            <xs:attribute name="Enabled" type="xs:boolean" use="required" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="CampaignType" type="xs:unsignedByte" use="required" />
    </xs:complexType>
  </xs:element>
</xs:schema>

========================= User Criteria XML Structure ===================================

and
========================= Campaign Search XML Structure ==================================

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Campaigns">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="C">
          <xs:complexType>
            <xs:attribute name="CId" type="xs:unsignedShort" use="required" />
            <xs:attribute name="AId" type="xs:unsignedByte" use="required" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

========================= Campaign Search XML Structure ===================================
 
Note: Now we have done our basics to use XML in database. We have selected our XML structure and generate its schema as well. In next post we will discuss how to bind this xml structure with SQL Server 2008 database using SSMS.
 


Tuesday, November 13, 2012

Performance Dashboard to work on Optimization tasks

Question
Most of the time I need to work on any optimization tasks I some steps to find out problem in query but this helps when I was doing SQL Optimization. But when there is any Administration tasks then I need to know some easy way to find my problem.

Answer:
There are a lot of tools available in the market like ApexSQL and RedGate etc. But we need to purchase these tools to get maximum benifit.
I am looking for free tools that works with SQL Server Managment Studio and easy to use and I find SQL Server Performance Dashboard.

It is easy to use and generate enoumrous reports using SSMS. Have a look at it.

 
This is Performance dashboard report main page and we can drill down using links to view detail like.
 
1) CPU bottlenecks (which queries are consuming the most CPU and Plan for this query)
2) IO bottlenecks (which queries are performing the most IO and Plan for this query).
3) Index recommendations generated by the query optimizer (missing index recommendations pulled from sys.dm_db_missing_index_details and sys.dm_db_missing_index_group_stats)
4) Blocking
5) Latch contention and other Wait Types
 
Pre-Requisites to Performance Dashboard
1. You need to have Management Studio installed on the machine where you intend to use the Performance Dashboard Reports (could be either client or server machine).
2. If you are still using SQL Server 2005 the it needs to have at a minimum SP2 applied (build 9.0.3042 or greater) and it is also available for SQL Server 2008

How to Get it:
Please download Performace dashboard msi setup link from here
http://www.microsoft.com/en-us/download/details.aspx?id=22602

If you are using SQL Server 2008 (First install above setup and then download new dashboard from following link)
http://www.cshandler.com/2011/09/performance-dashboard-reports-in-ssms.html

How to Install:
For SQLServer 2005
1. Run the setup exe downloaded from HERE.
2. Once you install the above, go to %\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard and run the setup.sql script against the SQL instance that you want to monitor the performance for.
 
For SQLServer 2008 R2
1. Run the setup exe downloaded from HERE.
2. Once you install the above. Download the attachment from the link at end of this post and extract the files. You’ll find two files there copy them and paste them to %\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard. Overwrite the old files and run the setup.sql script against the SQL instance that you want to monitor the performance for.

Hint: If you are installing this setup on SQL SERVER 2008 R2 Setup.sql file attached setup. It might generate error. You need to change column name



Result:
It is quite usefull for me to get performace and find problems in my daily working. Use it and Comment what you feel about it.