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.

 

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.
 

Finding failed Job history using SQL SERVER

I have used following query to find scheduled job history in sql server Agent. This query returns last 7 days failed jobs detail with max fail date for each job.

-- Variable Declarations

 DECLARE

@PreviousDate datetime

DECLARE
@Year VARCHAR(4)

DECLARE
@Month VARCHAR(2)

DECLARE
@MonthPre VARCHAR(2)

DECLARE
@Day VARCHAR(2)

DECLARE
@DayPre VARCHAR(2)

DECLARE
@FinalDate INT

-- Initialize Variables


SET
@PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 1 days

SET
@Year = DATEPART(yyyy, @PreviousDate)

SELECT
@MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))

SELECT
@Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)

SELECT
@DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))

SELECT
@Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)

SET
@FinalDate = CAST(@Year + @Month + @Day AS INT)

-- Final Logic


SELECT
j.[name],

max(h.run_date) run_date,

max(h.run_time) run_time,

h.message,

h.server

FROM
msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j

ON h.job_id = j.job_id

INNER JOIN msdb.dbo.sysjobsteps s

ON j.job_id = s.job_id

AND h.step_id = s.step_id

WHERE
h.run_status = 0 -- Failure

AND h.run_date > @FinalDate

group
by

j.[name],

h.message,

h.server

--ORDER BY h.instance_id DESC

What is SQL SERVER Profiler, Custom Templates & Default Templates

What:

SQL Server Profiler is a rich interface to create and manage traces and analyze and replay trace results of SQL Trace for monitoring an instance of the Database Engine or Analysis Services. Results can be saved in trace files or table for further investigation.

When:

SQL Server Profiler is used for activities such as:

·         Stepping through problem queries to find the cause of the problem.
·         Finding and diagnosing slow-running queries.
·         Capturing the series of Transact-SQL statements that lead to a problem.
·         Monitoring the performance of SQL Server to tune workloads.
·         Correlating performance counters to diagnose problems.
·         Finding problems for table locks and dead lock information
·         Group all slow running queries for applications.
·         Use results in Tuning Advisor for optimization
·         Finding XML and deadlock execution plan to understand where exactly it is occurring.
·         Audits record and security-related actions

How:

There are predefined trace templates that allow you to easily configure the event classes that you will most likely need for specific traces.

SQL Server Profiler can trace a variety of server types. For example you can trace Analysis Services and SQL Server 2005 (and later versions). Therefore, SQL Server Profiler maintains different templates for different servers, and makes available the specific template that matches the selected server type.

Templates:

Some templates and there output are as follows;

Template name	Template purpose
SP_Counts	Captures stored procedure execution behavior over time.
Standard	Generic starting point for creating a trace. Captures all stored procedures and Transact-SQL batches that are run. Use to monitor general database server activity.
TSQL	Captures all Transact-SQL statements that are submitted to SQL Server by clients and the time issued. Use to debug client applications.
TSQL_Duration	Captures all Transact-SQL statements submitted to SQL Server by clients, their execution time (in milliseconds), and groups them by duration. Use to identify slow queries.
TSQL_Grouped	Captures all Transact-SQL statements submitted to SQL Server and the time they were issued. Groups information by user or client that submitted the statement. Use to investigate queries from a particular client or user.
TSQL_Locks	Captures all of the Transact-SQL statements that are submitted to SQL Server by clients along with exceptional lock events. Use to troubleshoot deadlocks, lock time-out, and lock escalation events.
TSQL_Replay	Captures detailed information about Transact-SQL statements that is required if the trace will be replayed. Use to perform iterative tuning, such as benchmark testing.
TSQL_SPs	Captures detailed information about all executing stored procedures. Use to analyze the component steps of stored procedures. Add the SP:Recompile event if you suspect that procedures are being recompiled.
Tuning	Captures information about stored procedures and Transact-SQL batch execution. Use to produce trace output that Database Engine Tuning Advisor can use as a workload to tune databases.

 Custom Templates:

You can also define your own custom template to capture data of your own needs.

Step1:  I have opend Sql server Profiler from SSMS Sql server management studio-- > tools tab.
Step2: From Pic1 you can view how to create New template
                You can save your new template with your own name as I have set name to [My Template] using default sp_lock profiler template. If You want to create your own uncheck existing profiler check box and in Event Selection, select class events of your own requirement.

Step3:  Once Template saved, it is listed in other templates as you can see in pic2. There are severl other usefull options for profiler as I have enables all

• You can save profiler results into trace file
• You can save profiler results into database table
• Trace can be stopped at given time
• Trace can be set in table as how many rows you want to store and many others