I/O Read/Write Performance Problem

We have an ETL job occasionally encounters performance issue. Today, for
example, it creates several locks in SQL Server, and its I/O Read Bytes (from
Task Manager) are > 3,746,000,000,000 and I/O Write Bytes are >
2,268,000,000,000 and still increasing. The ETL has run for over 4 hours (as
opposed to 40 minutes usually). If the similar scenario happens to you, what
would you do immediately?
I am thinking of implemeting table hints when reviewing the queries in it,
what else can we further improve for long term solutions?
Hi,
I would suggest that you place a Sql Trace and a Performance monitor on the
job to determine why it is taking so long. There could be a multitude of
reasons that the job is taking so long from an improperly joined table to a
disk failure in the RAID array. I have posted a couple of links to help you
set up the perfmon and the profiler to aid in your diagnosis.
Hope this helps.
http://support.microsoft.com/default...b;EN-US;224453
http://support.microsoft.com/default...;en-us;Q224587
http://support.microsoft.com/default...;en-us;Q243589
sql

I/O Read/Write Performance Problem

We have an ETL job occasionally encounters performance issue. Today, for
example, it creates several locks in SQL Server, and its I/O Read Bytes (from
Task Manager) are > 3,746,000,000,000 and I/O Write Bytes are >
2,268,000,000,000 and still increasing. The ETL has run for over 4 hours (as
opposed to 40 minutes usually). If the similar scenario happens to you, what
would you do immediately?
I am thinking of implemeting table hints when reviewing the queries in it,
what else can we further improve for long term solutions?Hi,
I would suggest that you place a Sql Trace and a Performance monitor on the
job to determine why it is taking so long. There could be a multitude of
reasons that the job is taking so long from an improperly joined table to a
disk failure in the RAID array. I have posted a couple of links to help you
set up the perfmon and the profiler to aid in your diagnosis.
Hope this helps.
--
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224453
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224587
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243589

I need to produce 5 reports from SQL Server 2005 XML

Hi. I have been given the job of producing 5 reports from a SQL Server
2005 (beta) XML database. I barely have an idea what to do. There are
existing reports that don't work properly, produced by an employee who
will no longer be working for his manager (for various reasons, these
reports being one), the same manager who has tasked me to do these
reports for him. I thought I might start with the existing reports and
see if I can make them work. The existing reports are some kind of SQL
but I don't know what sort. Can someone identify what sort of SQL the
code is and point me to where I might learn about it, please? Following
is an example of one of the reports copied from Visual Studio:
--Purchase Orders--
SELECT
--document type
[Type],
--document id
[Message].value('(/Order/OrderHeader/OrderNumber/BuyerOrderNumber)[1]',
'nvarchar(50)') AS id,
--issue date
[Message].value('(/Order/OrderHeader/OrderIssueDate)[1]',
'nvarchar(50)') AS issue_date,
--agency name
[Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/NameAddress/Name1)[1]',
'nvarchar(50)') AS agency_name,
--department name
[Message].value('(/Order/OrderHeader/OrderParty/ShipToParty/Party/NameAddress/Name2)[1]',
'nvarchar(50)') AS department_name,
--buyer name
[Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/Contact/ContactName)[1]',
'nvarchar(50)') AS buyer_name,
--seller name
[Message].value('(/Order/OrderHeader/OrderParty/SellerParty/Party/NameAddress/Name1)[1]',
'nvarchar(50)') AS seller_name,
--total
[Message].value('(/Order/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
'decimal(18,2)') AS total
FROM [test].[dbo].[TableXML]
WHERE Type='Purchase Order'
UNION ALL
--Purchase Orders Changes--
SELECT
--document type
[Type],
--document id
[Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderNumber/BuyerChangeOrderNumber)[1]',
'nvarchar(50)') AS id,
--issue date
[Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderIssueDate)[1]',
'nvarchar(50)') AS issue_date,
--agency name
[Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/NameAddress/Name1)[1]',
'nvarchar(50)') AS agency_name,
--department name
[Message].value('(/ChangeOrder/ChangeOrderHeader/ShipToParty/Party/NameAddress/Name2)[1]',
'nvarchar(50)') AS department_name,
--buyer name
[Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/OrderContact/Contact/ContactName)[1]',
'nvarchar(50)') AS buyer_name,
--seller name
[Message].value('(/ChangeOrder/ChangeOrderHeader/SellerParty/Party/NameAddress/Name1)[1]',
'nvarchar(50)') AS seller_name,
--total
[Message].value('(/ChangeOrder/ChangeOrderSummary/RevisedOrderSummary/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
'decimal(18,2)') AS total
FROM [test].[dbo].[TableXML]
WHERE Type='Purchase Order Change'
Thanking you now for your responses.
Regards,
Clueless.
The T-SQL here uses the various methods supported by the new xml data type
in SQL Server 2005 (your "Message" column is obviously a column of type
"xml". Try http://msdn.microsoft.com/SQL/learn/...l/default.aspx as a
starting point for learning about this. It's also covered quite well in SQL
Server Books Online.
Cheers,
G
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1133836493.347432.246890@.f14g2000cwb.googlegr oups.com...
> Hi. I have been given the job of producing 5 reports from a SQL Server
> 2005 (beta) XML database. I barely have an idea what to do. There are
> existing reports that don't work properly, produced by an employee who
> will no longer be working for his manager (for various reasons, these
> reports being one), the same manager who has tasked me to do these
> reports for him. I thought I might start with the existing reports and
> see if I can make them work. The existing reports are some kind of SQL
> but I don't know what sort. Can someone identify what sort of SQL the
> code is and point me to where I might learn about it, please? Following
> is an example of one of the reports copied from Visual Studio:
> --
> --Purchase Orders--
> --
> SELECT
> --document type
> [Type],
> --document id
> [Message].value('(/Order/OrderHeader/OrderNumber/BuyerOrderNumber)[1]',
> 'nvarchar(50)') AS id,
> --issue date
> [Message].value('(/Order/OrderHeader/OrderIssueDate)[1]',
> 'nvarchar(50)') AS issue_date,
> --agency name
> [Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/NameAddress/Name1)[1]',
> 'nvarchar(50)') AS agency_name,
> --department name
> [Message].value('(/Order/OrderHeader/OrderParty/ShipToParty/Party/NameAddress/Name2)[1]',
> 'nvarchar(50)') AS department_name,
> --buyer name
> [Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/Contact/ContactName)[1]',
> 'nvarchar(50)') AS buyer_name,
> --seller name
> [Message].value('(/Order/OrderHeader/OrderParty/SellerParty/Party/NameAddress/Name1)[1]',
> 'nvarchar(50)') AS seller_name,
> --total
> [Message].value('(/Order/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
> 'decimal(18,2)') AS total
> FROM [test].[dbo].[TableXML]
> WHERE Type='Purchase Order'
> UNION ALL
> --
> --Purchase Orders Changes--
> --
> SELECT
> --document type
> [Type],
> --document id
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderNumber/BuyerChangeOrderNumber)[1]',
> 'nvarchar(50)') AS id,
> --issue date
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderIssueDate)[1]',
> 'nvarchar(50)') AS issue_date,
> --agency name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/NameAddress/Name1)[1]',
> 'nvarchar(50)') AS agency_name,
> --department name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ShipToParty/Party/NameAddress/Name2)[1]',
> 'nvarchar(50)') AS department_name,
> --buyer name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/OrderContact/Contact/ContactName)[1]',
> 'nvarchar(50)') AS buyer_name,
> --seller name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/SellerParty/Party/NameAddress/Name1)[1]',
> 'nvarchar(50)') AS seller_name,
> --total
> [Message].value('(/ChangeOrder/ChangeOrderSummary/RevisedOrderSummary/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
> 'decimal(18,2)') AS total
> FROM [test].[dbo].[TableXML]
> WHERE Type='Purchase Order Change'
> Thanking you now for your responses.
> Regards,
> Clueless.
>
|||Graeme,
Thank you for your response. Yes, you are completely correct, the
"Message" column is a column of type "xml". Actually, I am in "SQL
Server Management Studio" and in the "Object Explorer" I see
"Databases", and under this there are "AdventureWorks (Offline)",
"AdventureWorksDW (Offline)", and "test", among others. Under "test" I
have one familiar thing and a whole bunch of (to me) unfamiliar things:
Familiar- Tables, Unfamiliar- Database Diagrams, Views, Synonyms,
Programmability, Service Broker, Storage and Security. Under "Tables" I
have "System Tables" and "dbo.TableXML". Under "dbo.TableXML" I have
Columns, Keys, Constraints, Triggers, Indexes and Statistics. And,
finally, under "Columns" I have "Type (char(50), null)" and
"Message(XML(.), null)". Most of this is an alien world to me. At this
stage, I still don't know if I will be able to produce the reports I
require.
Thank you again. At least now I know this is T-SQL.
Regards,
Clueless.

I need to produce 5 reports from SQL Server 2005 XML

Hi. I have been given the job of producing 5 reports from a SQL Server
2005 (beta) XML database. I barely have an idea what to do. There are
existing reports that don't work properly, produced by an employee who
will no longer be working for his manager (for various reasons, these
reports being one), the same manager who has tasked me to do these
reports for him. I thought I might start with the existing reports and
see if I can make them work. The existing reports are some kind of SQL
but I don't know what sort. Can someone identify what sort of SQL the
code is and point me to where I might learn about it, please? Following
is an example of one of the reports copied from Visual Studio:
--Purchase Orders--
--
SELECT
--document type
[Type],
--document id
[Message].value('(/Order/OrderHeader/OrderNumber/BuyerOrderNumber)[1]',
'nvarchar(50)') AS id,
--issue date
[Message].value('(/Order/OrderHeader/OrderIssueDate)[1]',
'nvarchar(50)') AS issue_date,
--agency name
[Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/NameAddress/Name1)[1
]',
'nvarchar(50)') AS agency_name,
--department name
[Message].value('(/Order/OrderHeader/OrderParty/ShipToParty/Party/NameAddress/Name2)[
1]',
'nvarchar(50)') AS department_name,
--buyer name
[Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/Contact
/ContactName)[1]',
'nvarchar(50)') AS buyer_name,
--seller name
[Message].value('(/Order/OrderHeader/OrderParty/SellerParty/Party/NameAddress/Name1)[
1]',
'nvarchar(50)') AS seller_name,
--total
[Message].value('(/Order/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
'decimal(18,2)') AS total
FROM [test].[dbo].[TableXML]
WHERE Type='Purchase Order'
UNION ALL
--Purchase Orders Changes--
--
SELECT
--document type
[Type],
--document id
[Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderNumber/BuyerChangeOrderNu
mber)[1]',
'nvarchar(50)') AS id,
--issue date
[Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderIssueDate)[1]',
'nvarchar(50)') AS issue_date,
--agency name
[Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/NameAddress/Name1)[
1]',
'nvarchar(50)') AS agency_name,
--department name
[Message].value('(/ChangeOrder/ChangeOrderHeader/ShipToParty/Party/NameAddress/Name2)
[1]',
'nvarchar(50)') AS department_name,
--buyer name
[Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/OrderContact/Contac
t/ContactName)[1]',
'nvarchar(50)') AS buyer_name,
--seller name
[Message].value('(/ChangeOrder/ChangeOrderHeader/SellerParty/Party/NameAddress/Name1)
[1]',
'nvarchar(50)') AS seller_name,
--total
[Message].value('(/ChangeOrder/ChangeOrderSummary/RevisedOrderSummary/OrderSummary/To
talAmount/MonetaryValue/MonetaryAmount)[1]',
'decimal(18,2)') AS total
FROM [test].[dbo].[TableXML]
WHERE Type='Purchase Order Change'
Thanking you now for your responses.
Regards,
Clueless.The T-SQL here uses the various methods supported by the new xml data type
in SQL Server 2005 (your "Message" column is obviously a column of type
"xml". Try http://msdn.microsoft.com/SQL/learn...ml/default.aspx as a
starting point for learning about this. It's also covered quite well in SQL
Server Books Online.
Cheers,
G
--
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1133836493.347432.246890@.f14g2000cwb.googlegroups.com...
> Hi. I have been given the job of producing 5 reports from a SQL Server
> 2005 (beta) XML database. I barely have an idea what to do. There are
> existing reports that don't work properly, produced by an employee who
> will no longer be working for his manager (for various reasons, these
> reports being one), the same manager who has tasked me to do these
> reports for him. I thought I might start with the existing reports and
> see if I can make them work. The existing reports are some kind of SQL
> but I don't know what sort. Can someone identify what sort of SQL the
> code is and point me to where I might learn about it, please? Following
> is an example of one of the reports copied from Visual Studio:
> --
> --Purchase Orders--
> --
> SELECT
> --document type
> [Type],
> --document id
> [Message].value('(/Order/OrderHeader/OrderNumber/BuyerOrderNumber)[1]',
> 'nvarchar(50)') AS id,
> --issue date
> [Message].value('(/Order/OrderHeader/OrderIssueDate)[1]',
> 'nvarchar(50)') AS issue_date,
> --agency name
> [Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/NameAddress/Name1)
[1]',
> 'nvarchar(50)') AS agency_name,
> --department name
> [Message].value('(/Order/OrderHeader/OrderParty/ShipToParty/Party/NameAddress/Name2
)[1]',
> 'nvarchar(50)') AS department_name,
> --buyer name
> [Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/Conta
ct/ContactName)[1]',
> 'nvarchar(50)') AS buyer_name,
> --seller name
> [Message].value('(/Order/OrderHeader/OrderParty/SellerParty/Party/NameAddress/Name1
)[1]',
> 'nvarchar(50)') AS seller_name,
> --total
> [Message].value('(/Order/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]'
,
> 'decimal(18,2)') AS total
> FROM [test].[dbo].[TableXML]
> WHERE Type='Purchase Order'
> UNION ALL
> --
> --Purchase Orders Changes--
> --
> SELECT
> --document type
> [Type],
> --document id
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderNumber/BuyerChangeOrder
Number)[1]',
> 'nvarchar(50)') AS id,
> --issue date
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderIssueDate)[1]',
> 'nvarchar(50)') AS issue_date,
> --agency name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/NameAddress/Name1
)[1]',
> 'nvarchar(50)') AS agency_name,
> --department name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ShipToParty/Party/NameAddress/Name
2)[1]',
> 'nvarchar(50)') AS department_name,
> --buyer name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/OrderContact/Cont
act/ContactName)[1]',
> 'nvarchar(50)') AS buyer_name,
> --seller name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/SellerParty/Party/NameAddress/Name
1)[1]',
> 'nvarchar(50)') AS seller_name,
> --total
> [Message].value('(/ChangeOrder/ChangeOrderSummary/RevisedOrderSummary/OrderSummary/
TotalAmount/MonetaryValue/MonetaryAmount)[1]',
> 'decimal(18,2)') AS total
> FROM [test].[dbo].[TableXML]
> WHERE Type='Purchase Order Change'
> Thanking you now for your responses.
> Regards,
> Clueless.
>|||Graeme,
Thank you for your response. Yes, you are completely correct, the
"Message" column is a column of type "xml". Actually, I am in "SQL
Server Management Studio" and in the "Object Explorer" I see
"Databases", and under this there are "AdventureWorks (Offline)",
"AdventureWorksDW (Offline)", and "test", among others. Under "test" I
have one familiar thing and a whole bunch of (to me) unfamiliar things:
Familiar- Tables, Unfamiliar- Database Diagrams, Views, Synonyms,
Programmability, Service Broker, Storage and Security. Under "Tables" I
have "System Tables" and "dbo.TableXML". Under "dbo.TableXML" I have
Columns, Keys, Constraints, Triggers, Indexes and Statistics. And,
finally, under "Columns" I have "Type (char(50), null)" and
"Message(XML(.), null)". Most of this is an alien world to me. At this
stage, I still don't know if I will be able to produce the reports I
require.
Thank you again. At least now I know this is T-SQL.
Regards,
Clueless.

I need to execute a procedure at a spesific hour

Hello, i need some help on this...

I need to make a job that execute a procedure at 7 am , and other at 8 pm, can any one give a hand with this?

Regards

What version of SQL Server? If you are using a sku with agent, it does a great job and is pretty easy to set up.

If not, you might just consider using sqlcmd or isql and using the AT facility built into Windows. It is easy enough to do once you get the command you want to run.

|||Im working with SQL 2005|||Set up a SQL Agent Job to execute a Transact-SQL command to execute the stored procedure, and schedule it for when you want it to run.|||

open sql server management studio 2005

connect to your database server.

in the object explorer. click sql server agent.

right click jobs. then new jobs.

place your codes

|||Which version? Unless it is express, then Agent will be quite easy to set up (as long as whoever set up the server installed/enabled it.) Books online covers this pretty well, or some of the other posts outline the basics.