Hi
Some relation exists that improve the searches in xml (xquery) utilizing
schema colletion or they are independent?
Thank
Cristián
Hello sqlextreme,
> Hi
> Some relation exists that improve the searches in xml (xquery)
> utilizing
> schema colletion or they are independent?
There's three performance related benefits of using schema-bound XML.
a.) it prevents the query processor from doing an impossible yet potentially
expensive query. The query is checked against schema first to see if even
possible.
b.) The query processor better understands the structure of the underlying
XML which may result in more effecient query plans.
c.) Since the schema can providing typing information, ranging and comparsion
operations can be more efficient.
Does it make a big difference? It can, but in general practice, I've seldom
seen it amount to more than a 5% difference in actual query costs.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hi Kent
Thank you so much for your time, this helps me a lot.
Cristián
"Kent Tegels" wrote:
> Hello sqlextreme,
>
> There's three performance related benefits of using schema-bound XML.
> a.) it prevents the query processor from doing an impossible yet potentially
> expensive query. The query is checked against schema first to see if even
> possible.
> b.) The query processor better understands the structure of the underlying
> XML which may result in more effecient query plans.
> c.) Since the schema can providing typing information, ranging and comparsion
> operations can be more efficient.
> Does it make a big difference? It can, but in general practice, I've seldom
> seen it amount to more than a 5% difference in actual query costs.
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
Showing posts with label schema. Show all posts
Showing posts with label schema. Show all posts
Wednesday, March 28, 2012
Performance relation of xquery with Schema
Hi
Some relation exists that improve the searches in xml (xquery) utilizing
schema colletion or they are independent?
Thank
CristiánHello sqlextreme,
> Hi
> Some relation exists that improve the searches in xml (xquery)
> utilizing
> schema colletion or they are independent?
There's three performance related benefits of using schema-bound XML.
a.) it prevents the query processor from doing an impossible yet potentially
expensive query. The query is checked against schema first to see if even
possible.
b.) The query processor better understands the structure of the underlying
XML which may result in more effecient query plans.
c.) Since the schema can providing typing information, ranging and comparsio
n
operations can be more efficient.
Does it make a big difference? It can, but in general practice, I've seldom
seen it amount to more than a 5% difference in actual query costs.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent
Thank you so much for your time, this helps me a lot.
Cristián
"Kent Tegels" wrote:
> Hello sqlextreme,
>
> There's three performance related benefits of using schema-bound XML.
> a.) it prevents the query processor from doing an impossible yet potential
ly
> expensive query. The query is checked against schema first to see if even
> possible.
> b.) The query processor better understands the structure of the underlying
> XML which may result in more effecient query plans.
> c.) Since the schema can providing typing information, ranging and compars
ion
> operations can be more efficient.
> Does it make a big difference? It can, but in general practice, I've seldo
m
> seen it amount to more than a 5% difference in actual query costs.
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
Some relation exists that improve the searches in xml (xquery) utilizing
schema colletion or they are independent?
Thank
CristiánHello sqlextreme,
> Hi
> Some relation exists that improve the searches in xml (xquery)
> utilizing
> schema colletion or they are independent?
There's three performance related benefits of using schema-bound XML.
a.) it prevents the query processor from doing an impossible yet potentially
expensive query. The query is checked against schema first to see if even
possible.
b.) The query processor better understands the structure of the underlying
XML which may result in more effecient query plans.
c.) Since the schema can providing typing information, ranging and comparsio
n
operations can be more efficient.
Does it make a big difference? It can, but in general practice, I've seldom
seen it amount to more than a 5% difference in actual query costs.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent
Thank you so much for your time, this helps me a lot.
Cristián
"Kent Tegels" wrote:
> Hello sqlextreme,
>
> There's three performance related benefits of using schema-bound XML.
> a.) it prevents the query processor from doing an impossible yet potential
ly
> expensive query. The query is checked against schema first to see if even
> possible.
> b.) The query processor better understands the structure of the underlying
> XML which may result in more effecient query plans.
> c.) Since the schema can providing typing information, ranging and compars
ion
> operations can be more efficient.
> Does it make a big difference? It can, but in general practice, I've seldo
m
> seen it amount to more than a 5% difference in actual query costs.
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
Tuesday, March 20, 2012
Performance problem on a XML data type column
I have problems querying a Table with a XML typed column, someone could help
me?
Notable things are: the original schema is very large and is divided in
several other files (using the XML include instruction). Putting it all
together, the file is about 5000 lines (187 Kbytes).
A simple query like the following, consumes all te memory until the process
dies for 'not enough memory':
SELECT info.query('//patient') FROM test;
We are using SQL Server 2005, and the SQL Server Management Studio to run
the query.
Best regards,
Javier Diaz
javier.diaz@.dvc.es
Hi there.
I'm working with Javier on this and I would like to add a couple of things
that might be important.
When the query is executed, SQLServer process uses all of the CPU power till
the "not enough memory" error.
The schemas we are using are HL7 standard ones, so there's no room for any
modification to them.
If the schema is unregistered from the database, the query works perfectly.
With the schema registered there's no problem when inserting new rows or
updating existing ones.
Any help will be really appreciated, as this is proving to be a big show
stopper for us.
Many thanks in advance.
Best regards,
Ivan
"Javier Diaz" wrote:
> I have problems querying a Table with a XML typed column, someone could help
> me?
> Notable things are: the original schema is very large and is divided in
> several other files (using the XML include instruction). Putting it all
> together, the file is about 5000 lines (187 Kbytes).
> A simple query like the following, consumes all te memory until the process
> dies for 'not enough memory':
> SELECT info.query('//patient') FROM test;
> We are using SQL Server 2005, and the SQL Server Management Studio to run
> the query.
> Best regards,
> Javier Diaz
> javier.diaz@.dvc.es
>
|||Do you have SP1 or SP2 of SQL Server 2005 installed. A lot of work went into
SP1 to improve processing of large schemas such as HL7.
Regards,
Galex Yen
"Ivan Clavijos" wrote:
[vbcol=seagreen]
> Hi there.
> I'm working with Javier on this and I would like to add a couple of things
> that might be important.
> When the query is executed, SQLServer process uses all of the CPU power till
> the "not enough memory" error.
> The schemas we are using are HL7 standard ones, so there's no room for any
> modification to them.
> If the schema is unregistered from the database, the query works perfectly.
> With the schema registered there's no problem when inserting new rows or
> updating existing ones.
> Any help will be really appreciated, as this is proving to be a big show
> stopper for us.
> Many thanks in advance.
> Best regards,
> Ivan
> "Javier Diaz" wrote:
|||I second Galex. There was a lot of work done on perf and memory consumption in
SP1. There was significant improvement in particular for queries like yours that
use the descendant-or-self axis.
I had written a post on my blog at the time SP1 was released and it contains
some examples of queries where the improvements were quite impressive . Here is
the link. http://blogs.msdn.com/denisruc/archive/2006/04/20/580288.aspx
If you are using either SP1 or SP2 but are still experiencing perf/memory
consumption issues, please use the contact form on my blog to send me more info:
http://blogs.msdn.com/denisruc/contact.aspx
Thank you
Denis Ruckebusch
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Galex Yen [MSFT]" <Galex Yen [MSFT]@.discussions.microsoft.com> wrote in message
news:5C868B95-C4C5-45C2-ABBB-35CD5F264AD0@.microsoft.com...[vbcol=seagreen]
> Do you have SP1 or SP2 of SQL Server 2005 installed. A lot of work went into
> SP1 to improve processing of large schemas such as HL7.
> Regards,
> Galex Yen
> "Ivan Clavijos" wrote:
|||Thanks for your help, Galex and Denis
Installing SP2 allowed us to execute the query but using the full path. When
trying to execute it using the descendant-or-self path, the same problem was
happening.
Denis, I don't know if you need any additional information about this
problem (I forgot to mention that we are using MS SQL 2005 Express Edition)
as, at the moment, it's fine for us. As we are just doing a Proof of Concept,
it doesn't matter if we cannot use descendant-or-self.
But, as I said, if you are interested in gathering some additional
information that we may provide to you, do not hesitate to ask.
Thanks a lot again.
Best regards,
Ivan
|||Denis and Galex,
As Ivan said before the problem persists in SP2. Queries with descendatn,
like //Patient, result in poor performance. Queries like /A/B/C, worked fine.
Thanks in advance for your help,
Javier
"Denis Ruckebusch [MSFT]" wrote:
> I second Galex. There was a lot of work done on perf and memory consumption in
> SP1. There was significant improvement in particular for queries like yours that
> use the descendant-or-self axis.
> I had written a post on my blog at the time SP1 was released and it contains
> some examples of queries where the improvements were quite impressive . Here is
> the link. http://blogs.msdn.com/denisruc/archive/2006/04/20/580288.aspx
> If you are using either SP1 or SP2 but are still experiencing perf/memory
> consumption issues, please use the contact form on my blog to send me more info:
> http://blogs.msdn.com/denisruc/contact.aspx
> Thank you
> Denis Ruckebusch
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Galex Yen [MSFT]" <Galex Yen [MSFT]@.discussions.microsoft.com> wrote in message
> news:5C868B95-C4C5-45C2-ABBB-35CD5F264AD0@.microsoft.com...
>
>
|||I'd like to see the schema and the query and find out if we can fix this. Can I
contact you through the addresses listed on your posts?
If you want me to use another address send me an email through this contact
form:
http://blogs.msdn.com/denisruc/contact.aspx
Thank you
Denis Ruckebusch
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ivan Clavijos" <IvanClavijos@.discussions.microsoft.com> wrote in message
news:355717F2-35D4-4E01-8F24-421E5D017EAA@.microsoft.com...
> Thanks for your help, Galex and Denis
> Installing SP2 allowed us to execute the query but using the full path. When
> trying to execute it using the descendant-or-self path, the same problem was
> happening.
> Denis, I don't know if you need any additional information about this
> problem (I forgot to mention that we are using MS SQL 2005 Express Edition)
> as, at the moment, it's fine for us. As we are just doing a Proof of Concept,
> it doesn't matter if we cannot use descendant-or-self.
> But, as I said, if you are interested in gathering some additional
> information that we may provide to you, do not hesitate to ask.
> Thanks a lot again.
> Best regards,
> Ivan
>
me?
Notable things are: the original schema is very large and is divided in
several other files (using the XML include instruction). Putting it all
together, the file is about 5000 lines (187 Kbytes).
A simple query like the following, consumes all te memory until the process
dies for 'not enough memory':
SELECT info.query('//patient') FROM test;
We are using SQL Server 2005, and the SQL Server Management Studio to run
the query.
Best regards,
Javier Diaz
javier.diaz@.dvc.es
Hi there.
I'm working with Javier on this and I would like to add a couple of things
that might be important.
When the query is executed, SQLServer process uses all of the CPU power till
the "not enough memory" error.
The schemas we are using are HL7 standard ones, so there's no room for any
modification to them.
If the schema is unregistered from the database, the query works perfectly.
With the schema registered there's no problem when inserting new rows or
updating existing ones.
Any help will be really appreciated, as this is proving to be a big show
stopper for us.
Many thanks in advance.
Best regards,
Ivan
"Javier Diaz" wrote:
> I have problems querying a Table with a XML typed column, someone could help
> me?
> Notable things are: the original schema is very large and is divided in
> several other files (using the XML include instruction). Putting it all
> together, the file is about 5000 lines (187 Kbytes).
> A simple query like the following, consumes all te memory until the process
> dies for 'not enough memory':
> SELECT info.query('//patient') FROM test;
> We are using SQL Server 2005, and the SQL Server Management Studio to run
> the query.
> Best regards,
> Javier Diaz
> javier.diaz@.dvc.es
>
|||Do you have SP1 or SP2 of SQL Server 2005 installed. A lot of work went into
SP1 to improve processing of large schemas such as HL7.
Regards,
Galex Yen
"Ivan Clavijos" wrote:
[vbcol=seagreen]
> Hi there.
> I'm working with Javier on this and I would like to add a couple of things
> that might be important.
> When the query is executed, SQLServer process uses all of the CPU power till
> the "not enough memory" error.
> The schemas we are using are HL7 standard ones, so there's no room for any
> modification to them.
> If the schema is unregistered from the database, the query works perfectly.
> With the schema registered there's no problem when inserting new rows or
> updating existing ones.
> Any help will be really appreciated, as this is proving to be a big show
> stopper for us.
> Many thanks in advance.
> Best regards,
> Ivan
> "Javier Diaz" wrote:
|||I second Galex. There was a lot of work done on perf and memory consumption in
SP1. There was significant improvement in particular for queries like yours that
use the descendant-or-self axis.
I had written a post on my blog at the time SP1 was released and it contains
some examples of queries where the improvements were quite impressive . Here is
the link. http://blogs.msdn.com/denisruc/archive/2006/04/20/580288.aspx
If you are using either SP1 or SP2 but are still experiencing perf/memory
consumption issues, please use the contact form on my blog to send me more info:
http://blogs.msdn.com/denisruc/contact.aspx
Thank you
Denis Ruckebusch
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Galex Yen [MSFT]" <Galex Yen [MSFT]@.discussions.microsoft.com> wrote in message
news:5C868B95-C4C5-45C2-ABBB-35CD5F264AD0@.microsoft.com...[vbcol=seagreen]
> Do you have SP1 or SP2 of SQL Server 2005 installed. A lot of work went into
> SP1 to improve processing of large schemas such as HL7.
> Regards,
> Galex Yen
> "Ivan Clavijos" wrote:
|||Thanks for your help, Galex and Denis
Installing SP2 allowed us to execute the query but using the full path. When
trying to execute it using the descendant-or-self path, the same problem was
happening.
Denis, I don't know if you need any additional information about this
problem (I forgot to mention that we are using MS SQL 2005 Express Edition)
as, at the moment, it's fine for us. As we are just doing a Proof of Concept,
it doesn't matter if we cannot use descendant-or-self.
But, as I said, if you are interested in gathering some additional
information that we may provide to you, do not hesitate to ask.
Thanks a lot again.
Best regards,
Ivan
|||Denis and Galex,
As Ivan said before the problem persists in SP2. Queries with descendatn,
like //Patient, result in poor performance. Queries like /A/B/C, worked fine.
Thanks in advance for your help,
Javier
"Denis Ruckebusch [MSFT]" wrote:
> I second Galex. There was a lot of work done on perf and memory consumption in
> SP1. There was significant improvement in particular for queries like yours that
> use the descendant-or-self axis.
> I had written a post on my blog at the time SP1 was released and it contains
> some examples of queries where the improvements were quite impressive . Here is
> the link. http://blogs.msdn.com/denisruc/archive/2006/04/20/580288.aspx
> If you are using either SP1 or SP2 but are still experiencing perf/memory
> consumption issues, please use the contact form on my blog to send me more info:
> http://blogs.msdn.com/denisruc/contact.aspx
> Thank you
> Denis Ruckebusch
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Galex Yen [MSFT]" <Galex Yen [MSFT]@.discussions.microsoft.com> wrote in message
> news:5C868B95-C4C5-45C2-ABBB-35CD5F264AD0@.microsoft.com...
>
>
|||I'd like to see the schema and the query and find out if we can fix this. Can I
contact you through the addresses listed on your posts?
If you want me to use another address send me an email through this contact
form:
http://blogs.msdn.com/denisruc/contact.aspx
Thank you
Denis Ruckebusch
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ivan Clavijos" <IvanClavijos@.discussions.microsoft.com> wrote in message
news:355717F2-35D4-4E01-8F24-421E5D017EAA@.microsoft.com...
> Thanks for your help, Galex and Denis
> Installing SP2 allowed us to execute the query but using the full path. When
> trying to execute it using the descendant-or-self path, the same problem was
> happening.
> Denis, I don't know if you need any additional information about this
> problem (I forgot to mention that we are using MS SQL 2005 Express Edition)
> as, at the moment, it's fine for us. As we are just doing a Proof of Concept,
> it doesn't matter if we cannot use descendant-or-self.
> But, as I said, if you are interested in gathering some additional
> information that we may provide to you, do not hesitate to ask.
> Thanks a lot again.
> Best regards,
> Ivan
>
Performance problem on a XML data type column
I have problems querying a Table with a XML typed column, someone could help
me?
Notable things are: the original schema is very large and is divided in
several other files (using the XML include instruction). Putting it all
together, the file is about 5000 lines (187 Kbytes).
A simple query like the following, consumes all te memory until the process
dies for 'not enough memory':
SELECT info.query('//patient') FROM test;
We are using SQL Server 2005, and the SQL Server Management Studio to run
the query.
Best regards,
Javier Diaz
javier.diaz@.dvc.esHi there.
I'm working with Javier on this and I would like to add a couple of things
that might be important.
When the query is executed, SQLServer process uses all of the CPU power till
the "not enough memory" error.
The schemas we are using are HL7 standard ones, so there's no room for any
modification to them.
If the schema is unregistered from the database, the query works perfectly.
With the schema registered there's no problem when inserting new rows or
updating existing ones.
Any help will be really appreciated, as this is proving to be a big show
stopper for us.
Many thanks in advance.
Best regards,
Ivan
"Javier Diaz" wrote:
> I have problems querying a Table with a XML typed column, someone could he
lp
> me?
> Notable things are: the original schema is very large and is divided in
> several other files (using the XML include instruction). Putting it all
> together, the file is about 5000 lines (187 Kbytes).
> A simple query like the following, consumes all te memory until the proces
s
> dies for 'not enough memory':
> SELECT info.query('//patient') FROM test;
> We are using SQL Server 2005, and the SQL Server Management Studio to run
> the query.
> Best regards,
> Javier Diaz
> javier.diaz@.dvc.es
>|||Do you have SP1 or SP2 of SQL Server 2005 installed. A lot of work went into
SP1 to improve processing of large schemas such as HL7.
Regards,
Galex Yen
"Ivan Clavijos" wrote:
> Hi there.
> I'm working with Javier on this and I would like to add a couple of things
> that might be important.
> When the query is executed, SQLServer process uses all of the CPU power ti
ll
> the "not enough memory" error.
> The schemas we are using are HL7 standard ones, so there's no room for any
> modification to them.
> If the schema is unregistered from the database, the query works perfectly
.
> With the schema registered there's no problem when inserting new rows or
> updating existing ones.
> Any help will be really appreciated, as this is proving to be a big show
> stopper for us.
> Many thanks in advance.
> Best regards,
> Ivan
> "Javier Diaz" wrote:
>|||I second Galex. There was a lot of work done on perf and memory consumption
in
SP1. There was significant improvement in particular for queries like yours
that
use the descendant-or-self axis.
I had written a post on my blog at the time SP1 was released and it contains
some examples of queries where the improvements were quite impressive . Here
is
the link. http://blogs.msdn.com/denisruc/arch.../20/580288.aspx
If you are using either SP1 or SP2 but are still experiencing perf/memory
consumption issues, please use the contact form on my blog to send me more i
nfo:
http://blogs.msdn.com/denisruc/contact.aspx
Thank you
Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Galex Yen [MSFT]" <Galex Yen [MSFT]@.discussions.microsoft.com> wrote in message
news:5C868B95-C4C5-45C2-ABBB-35CD5F264AD0@.microsoft.com...
> Do you have SP1 or SP2 of SQL Server 2005 installed. A lot of work went in
to
> SP1 to improve processing of large schemas such as HL7.
> Regards,
> Galex Yen
> "Ivan Clavijos" wrote:
>|||Thanks for your help, Galex and Denis :)
Installing SP2 allowed us to execute the query but using the full path. When
trying to execute it using the descendant-or-self path, the same problem was
happening.
Denis, I don't know if you need any additional information about this
problem (I forgot to mention that we are using MS SQL 2005 Express Edition)
as, at the moment, it's fine for us. As we are just doing a Proof of Concept
,
it doesn't matter if we cannot use descendant-or-self.
But, as I said, if you are interested in gathering some additional
information that we may provide to you, do not hesitate to ask.
Thanks a lot again.
Best regards,
Ivan|||Denis and Galex,
As Ivan said before the problem persists in SP2. Queries with descendatn,
like //Patient, result in poor performance. Queries like /A/B/C, worked fine
.
Thanks in advance for your help,
Javier
"Denis Ruckebusch [MSFT]" wrote:
> I second Galex. There was a lot of work done on perf and memory consumptio
n in
> SP1. There was significant improvement in particular for queries like your
s that
> use the descendant-or-self axis.
> I had written a post on my blog at the time SP1 was released and it contai
ns
> some examples of queries where the improvements were quite impressive . He
re is
> the link. http://blogs.msdn.com/denisruc/arch.../20/580288.aspx
> If you are using either SP1 or SP2 but are still experiencing perf/memory
> consumption issues, please use the contact form on my blog to send me more
info:
> http://blogs.msdn.com/denisruc/contact.aspx
> Thank you
> Denis Ruckebusch
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Galex Yen [MSFT]" <Galex Yen [MSFT]@.discussions.microsoft.com> wrote in message
> news:5C868B95-C4C5-45C2-ABBB-35CD5F264AD0@.microsoft.com...
>
>|||I'd like to see the schema and the query and find out if we can fix this. Ca
n I
contact you through the addresses listed on your posts?
If you want me to use another address send me an email through this contact
form:
http://blogs.msdn.com/denisruc/contact.aspx
Thank you
Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ivan Clavijos" <IvanClavijos@.discussions.microsoft.com> wrote in message
news:355717F2-35D4-4E01-8F24-421E5D017EAA@.microsoft.com...
> Thanks for your help, Galex and Denis :)
> Installing SP2 allowed us to execute the query but using the full path. Wh
en
> trying to execute it using the descendant-or-self path, the same problem w
as
> happening.
> Denis, I don't know if you need any additional information about this
> problem (I forgot to mention that we are using MS SQL 2005 Express Edition
)
> as, at the moment, it's fine for us. As we are just doing a Proof of Conce
pt,
> it doesn't matter if we cannot use descendant-or-self.
> But, as I said, if you are interested in gathering some additional
> information that we may provide to you, do not hesitate to ask.
> Thanks a lot again.
> Best regards,
> Ivan
>
me?
Notable things are: the original schema is very large and is divided in
several other files (using the XML include instruction). Putting it all
together, the file is about 5000 lines (187 Kbytes).
A simple query like the following, consumes all te memory until the process
dies for 'not enough memory':
SELECT info.query('//patient') FROM test;
We are using SQL Server 2005, and the SQL Server Management Studio to run
the query.
Best regards,
Javier Diaz
javier.diaz@.dvc.esHi there.
I'm working with Javier on this and I would like to add a couple of things
that might be important.
When the query is executed, SQLServer process uses all of the CPU power till
the "not enough memory" error.
The schemas we are using are HL7 standard ones, so there's no room for any
modification to them.
If the schema is unregistered from the database, the query works perfectly.
With the schema registered there's no problem when inserting new rows or
updating existing ones.
Any help will be really appreciated, as this is proving to be a big show
stopper for us.
Many thanks in advance.
Best regards,
Ivan
"Javier Diaz" wrote:
> I have problems querying a Table with a XML typed column, someone could he
lp
> me?
> Notable things are: the original schema is very large and is divided in
> several other files (using the XML include instruction). Putting it all
> together, the file is about 5000 lines (187 Kbytes).
> A simple query like the following, consumes all te memory until the proces
s
> dies for 'not enough memory':
> SELECT info.query('//patient') FROM test;
> We are using SQL Server 2005, and the SQL Server Management Studio to run
> the query.
> Best regards,
> Javier Diaz
> javier.diaz@.dvc.es
>|||Do you have SP1 or SP2 of SQL Server 2005 installed. A lot of work went into
SP1 to improve processing of large schemas such as HL7.
Regards,
Galex Yen
"Ivan Clavijos" wrote:
> Hi there.
> I'm working with Javier on this and I would like to add a couple of things
> that might be important.
> When the query is executed, SQLServer process uses all of the CPU power ti
ll
> the "not enough memory" error.
> The schemas we are using are HL7 standard ones, so there's no room for any
> modification to them.
> If the schema is unregistered from the database, the query works perfectly
.
> With the schema registered there's no problem when inserting new rows or
> updating existing ones.
> Any help will be really appreciated, as this is proving to be a big show
> stopper for us.
> Many thanks in advance.
> Best regards,
> Ivan
> "Javier Diaz" wrote:
>|||I second Galex. There was a lot of work done on perf and memory consumption
in
SP1. There was significant improvement in particular for queries like yours
that
use the descendant-or-self axis.
I had written a post on my blog at the time SP1 was released and it contains
some examples of queries where the improvements were quite impressive . Here
is
the link. http://blogs.msdn.com/denisruc/arch.../20/580288.aspx
If you are using either SP1 or SP2 but are still experiencing perf/memory
consumption issues, please use the contact form on my blog to send me more i
nfo:
http://blogs.msdn.com/denisruc/contact.aspx
Thank you
Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Galex Yen [MSFT]" <Galex Yen [MSFT]@.discussions.microsoft.com> wrote in message
news:5C868B95-C4C5-45C2-ABBB-35CD5F264AD0@.microsoft.com...
> Do you have SP1 or SP2 of SQL Server 2005 installed. A lot of work went in
to
> SP1 to improve processing of large schemas such as HL7.
> Regards,
> Galex Yen
> "Ivan Clavijos" wrote:
>|||Thanks for your help, Galex and Denis :)
Installing SP2 allowed us to execute the query but using the full path. When
trying to execute it using the descendant-or-self path, the same problem was
happening.
Denis, I don't know if you need any additional information about this
problem (I forgot to mention that we are using MS SQL 2005 Express Edition)
as, at the moment, it's fine for us. As we are just doing a Proof of Concept
,
it doesn't matter if we cannot use descendant-or-self.
But, as I said, if you are interested in gathering some additional
information that we may provide to you, do not hesitate to ask.
Thanks a lot again.
Best regards,
Ivan|||Denis and Galex,
As Ivan said before the problem persists in SP2. Queries with descendatn,
like //Patient, result in poor performance. Queries like /A/B/C, worked fine
.
Thanks in advance for your help,
Javier
"Denis Ruckebusch [MSFT]" wrote:
> I second Galex. There was a lot of work done on perf and memory consumptio
n in
> SP1. There was significant improvement in particular for queries like your
s that
> use the descendant-or-self axis.
> I had written a post on my blog at the time SP1 was released and it contai
ns
> some examples of queries where the improvements were quite impressive . He
re is
> the link. http://blogs.msdn.com/denisruc/arch.../20/580288.aspx
> If you are using either SP1 or SP2 but are still experiencing perf/memory
> consumption issues, please use the contact form on my blog to send me more
info:
> http://blogs.msdn.com/denisruc/contact.aspx
> Thank you
> Denis Ruckebusch
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Galex Yen [MSFT]" <Galex Yen [MSFT]@.discussions.microsoft.com> wrote in message
> news:5C868B95-C4C5-45C2-ABBB-35CD5F264AD0@.microsoft.com...
>
>|||I'd like to see the schema and the query and find out if we can fix this. Ca
n I
contact you through the addresses listed on your posts?
If you want me to use another address send me an email through this contact
form:
http://blogs.msdn.com/denisruc/contact.aspx
Thank you
Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ivan Clavijos" <IvanClavijos@.discussions.microsoft.com> wrote in message
news:355717F2-35D4-4E01-8F24-421E5D017EAA@.microsoft.com...
> Thanks for your help, Galex and Denis :)
> Installing SP2 allowed us to execute the query but using the full path. Wh
en
> trying to execute it using the descendant-or-self path, the same problem w
as
> happening.
> Denis, I don't know if you need any additional information about this
> problem (I forgot to mention that we are using MS SQL 2005 Express Edition
)
> as, at the moment, it's fine for us. As we are just doing a Proof of Conce
pt,
> it doesn't matter if we cannot use descendant-or-self.
> But, as I said, if you are interested in gathering some additional
> information that we may provide to you, do not hesitate to ask.
> Thanks a lot again.
> Best regards,
> Ivan
>
Performance problem - can't identify cause
Hi
We host a series of databases (SQL Server 2000 sp4) for customers; they are
all of identical schema. Recently, one of them became extremely slow. We
tried to find out what was wrong, but couldn't. I'll give you a quick
account of what we did in the hope that you might be able to suggest
something we overlooked.
After a bit of investigative work, we found that there were loads of locks
present and that CPU on the server was maxed out.
1 - we know that the DB schema is very optimised, so used SQL Compare to
compare this database's schema with another of our customer's in case an
index had been dropped. SQL Compare showed that the schemas were identical.
2 - we rebooted the Windows server in case there were any environmental
problems
3 - we updated the usage/statistics for the database (though these are set
to update automatically I believe) and rebuilt all the indexes.
4 - we ran a script to increase the free-space in the tables (normally set @.
10% free space - I think this script set this to 15% and forced that to
implement there and then)
5 - we ran the DBCC scripts for checking the database etc. This reported
that everything was fine.
6 - we moved the database onto another server (backup & restore) - the
performance problems exhibited themselves on this server too so we knew that
the problem (whatever it was) was specific to this database.
We then built a brand new DB using t-SQL statements and then migrated the
data from the old DB into this new one using DTS packages (only about 4 GB
of data). SQL Compare showed that the new DB has exactly the same schema as
the original DB but thankfully the new DB has NONE of the performance
problems.
So - there evidently was a problem with the database but none of the tools
we used showed up the problem. What did we miss?
Thanks everyone for any suggestions
GriffDid u Run the DBCC SHOWCONTIG or similar to check for Index
Fragmentation that could be one of the issues:
you said you recreated the schema and Copied the Data, well this is
most likely the cause of the Problem.
now these several similar databases, are they accessed in a Similar
pattern by the application, are they in a High availability scenario
etc.
Resting doubt, the TOP Issue (might) be index fragmentation:
Maninder
MCDBA
On Jan 29, 12:21 pm, "Griff" <griffithsj_...@.hotmail.com> wrote:
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff|||You've tried a bunch of different things without first identifying the cause
of the problem. Now that it's gone, it'll be pretty tough trying to figure
it out.
If this happens again, check to see what's using up the CPU resources, disk
IO activity and whether you're having memory pressure (at SQL Server and/or
OS level in case you locked SQL Server memory settings). Also, check for
blocking and recompilation activity.
Perf tuning is a lot of very invovled work.
http://msdn2.microsoft.com/en-us/sql/aa336291.aspx is a good place to start.
joe.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:euJVgn8QHHA.4188@.TK2MSFTNGP06.phx.gbl...
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they
> are all of identical schema. Recently, one of them became extremely slow.
> We tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were
> identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set
> @. 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew
> that the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema
> as the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>|||Griff (griffithsj_520@.hotmail.com) writes:
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
You did a lot of things, but not the things I would have done. I would
have set up a trace, preferably server-side, and traced for statements
with a duration over some limit, say 100 ms, and then analysed that
trace to find out where the main bottlenecks are. I would then have looked
at the query plans for these queries and compared with the other databases.
I would also have looked at blocking.
My guess is that statistics for the data in some places led the optimizer
astray for one or more queries. It was resolved when you reloaded the data
through DTS, because data may have been loaded in slightly different order.
It may sound crazy, but if the optimizer has a close call between two plans,
small differences in statistics may matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I don't know, Erland, if he updated the stats and rebuilt with more
free space, that should tickle the recompiles.
Remember, he said he had a lot of blocks, presumably more than the
similar systems, though these could be a secondary sign of slow
performance. So, it could just be the data, but then rebuilding it
onto a clean server wouldn't help.
Of course I agree I'd do a profiler/plan based analysis, but just
speculating, it's hard to guess what it would turn up.
So I have no idea, but I wonder if the problems will recur in short
order, if they are related to data mal-distributions causing bad
plans, or different traffic patterns.
J.
On Tue, 30 Jan 2007 08:20:08 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>Griff (griffithsj_520@.hotmail.com) writes:
>> So - there evidently was a problem with the database but none of the tools
>> we used showed up the problem. What did we miss?
>You did a lot of things, but not the things I would have done. I would
>have set up a trace, preferably server-side, and traced for statements
>with a duration over some limit, say 100 ms, and then analysed that
>trace to find out where the main bottlenecks are. I would then have looked
>at the query plans for these queries and compared with the other databases.
>I would also have looked at blocking.
>My guess is that statistics for the data in some places led the optimizer
>astray for one or more queries. It was resolved when you reloaded the data
>through DTS, because data may have been loaded in slightly different order.
>It may sound crazy, but if the optimizer has a close call between two plans,
>small differences in statistics may matter.|||Hi Erland & "J" (and everyone else on this thread)
I did mention briefly that we performed "a bit of investigative work". This
included using the performance analyser and getting traces for all stored
procedures and any dynamic SQL. There were some very slow running
queries....
We used the index tuning wizard and it suggested no new indexes were
required. As mentioned, the schema is identical for other customers (some
much larger DBs) and they have great performance. We also checked the
execution plan for one or two queries and they was the same on our problem
database and it was on another customer's database. So, we were fairly
confident that things were okay as far as the schema was concerned.
That's when we started running out of ideas. Are there any other symptoms
that we should have checked for - things that should have shouted to us to
look at the SHOWCONTIG output?
Griff|||One place to start would be the query plans. Compare the actual
execution plans for the problematic queries from the old database and the
newly built database. This will provide clues as to what might be wrong.
Before doing this, please make sure that the stats are updated and the
databases are identical in all respects (indexes, constraints, data etc). In
SQL Server 2000, you can use the SET STATISTICS PROFILE command to get the
actual execution plan. You can also use the "Show Plan Statistics" profiler
event.
--
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Griff wrote on Mon, 29 Jan 2007 17:21:10 -0000:
G> Hi
G> We host a series of databases (SQL Server 2000 sp4) for customers;
G> they are all of identical schema. Recently, one of them became
G> extremely slow. We tried to find out what was wrong, but couldn't.
G> I'll give you a quick account of what we did in the hope that you
G> might be able to suggest something we overlooked.
G> After a bit of investigative work, we found that there were loads of
G> locks present and that CPU on the server was maxed out.
G> 1 - we know that the DB schema is very optimised, so used SQL Compare
G> to compare this database's schema with another of our customer's in
G> case an index had been dropped. SQL Compare showed that the schemas
G> were identical.
G> 2 - we rebooted the Windows server in case there were any
G> environmental problems
G> 3 - we updated the usage/statistics for the database (though these
G> are set to update automatically I believe) and rebuilt all the
G> indexes.
G> 4 - we ran a script to increase the free-space in the tables
G> (normally set @.
G> 10% free space - I think this script set this to 15% and forced that
G> to implement there and then)
G> 5 - we ran the DBCC scripts for checking the database etc. This
G> reported that everything was fine.
G> 6 - we moved the database onto another server (backup & restore) -
G> the performance problems exhibited themselves on this server too so
G> we knew that the problem (whatever it was) was specific to this
G> database.
G> We then built a brand new DB using t-SQL statements and then migrated
G> the data from the old DB into this new one using DTS packages (only
G> about 4 GB of data). SQL Compare showed that the new DB has exactly
G> the same schema as the original DB but thankfully the new DB has
G> NONE of the performance problems.
G> So - there evidently was a problem with the database but none of the
G> tools we used showed up the problem. What did we miss?
G> Thanks everyone for any suggestions
G> Griff|||Griff (griffithsj_520@.hotmail.com) writes:
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
If the particular queries you looked at were slow due to blocking, it is
not surprising the plans were the same. Else it sounds very strange.
Difference in execution time is usually due to bad plans or looking. But
really bad fragmentation can play in as well.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
You said that you reindexed all tables, and in such case you should have
resolved such problems. Provided that all your tables has clustered indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||OK. You really need to do a trace of your system and find the
slow-running queries. Make a list of them. I will bet that there's a
lot of table scanning or hashing going on. Do you have a sample of one
of the queries that you can post? Can you post the DDL of the base
tables being queried, as well as some distributions of key data? The
Index Tuning Wizard is just not something that I'd run or trust for
telling me how to run my production system...
-Dave
Griff wrote:
> Hi Erland & "J" (and everyone else on this thread)
> I did mention briefly that we performed "a bit of investigative work". This
> included using the performance analyser and getting traces for all stored
> procedures and any dynamic SQL. There were some very slow running
> queries....
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
> Griff
>
-Dave Markle
http://www.markleconsulting.com/blog|||Are you in a hyperthreading mode ? If yes, try to turn it off. See Slava
Ocks paper.
A +
Griff a écrit :
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
We host a series of databases (SQL Server 2000 sp4) for customers; they are
all of identical schema. Recently, one of them became extremely slow. We
tried to find out what was wrong, but couldn't. I'll give you a quick
account of what we did in the hope that you might be able to suggest
something we overlooked.
After a bit of investigative work, we found that there were loads of locks
present and that CPU on the server was maxed out.
1 - we know that the DB schema is very optimised, so used SQL Compare to
compare this database's schema with another of our customer's in case an
index had been dropped. SQL Compare showed that the schemas were identical.
2 - we rebooted the Windows server in case there were any environmental
problems
3 - we updated the usage/statistics for the database (though these are set
to update automatically I believe) and rebuilt all the indexes.
4 - we ran a script to increase the free-space in the tables (normally set @.
10% free space - I think this script set this to 15% and forced that to
implement there and then)
5 - we ran the DBCC scripts for checking the database etc. This reported
that everything was fine.
6 - we moved the database onto another server (backup & restore) - the
performance problems exhibited themselves on this server too so we knew that
the problem (whatever it was) was specific to this database.
We then built a brand new DB using t-SQL statements and then migrated the
data from the old DB into this new one using DTS packages (only about 4 GB
of data). SQL Compare showed that the new DB has exactly the same schema as
the original DB but thankfully the new DB has NONE of the performance
problems.
So - there evidently was a problem with the database but none of the tools
we used showed up the problem. What did we miss?
Thanks everyone for any suggestions
GriffDid u Run the DBCC SHOWCONTIG or similar to check for Index
Fragmentation that could be one of the issues:
you said you recreated the schema and Copied the Data, well this is
most likely the cause of the Problem.
now these several similar databases, are they accessed in a Similar
pattern by the application, are they in a High availability scenario
etc.
Resting doubt, the TOP Issue (might) be index fragmentation:
Maninder
MCDBA
On Jan 29, 12:21 pm, "Griff" <griffithsj_...@.hotmail.com> wrote:
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff|||You've tried a bunch of different things without first identifying the cause
of the problem. Now that it's gone, it'll be pretty tough trying to figure
it out.
If this happens again, check to see what's using up the CPU resources, disk
IO activity and whether you're having memory pressure (at SQL Server and/or
OS level in case you locked SQL Server memory settings). Also, check for
blocking and recompilation activity.
Perf tuning is a lot of very invovled work.
http://msdn2.microsoft.com/en-us/sql/aa336291.aspx is a good place to start.
joe.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:euJVgn8QHHA.4188@.TK2MSFTNGP06.phx.gbl...
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they
> are all of identical schema. Recently, one of them became extremely slow.
> We tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were
> identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set
> @. 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew
> that the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema
> as the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>|||Griff (griffithsj_520@.hotmail.com) writes:
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
You did a lot of things, but not the things I would have done. I would
have set up a trace, preferably server-side, and traced for statements
with a duration over some limit, say 100 ms, and then analysed that
trace to find out where the main bottlenecks are. I would then have looked
at the query plans for these queries and compared with the other databases.
I would also have looked at blocking.
My guess is that statistics for the data in some places led the optimizer
astray for one or more queries. It was resolved when you reloaded the data
through DTS, because data may have been loaded in slightly different order.
It may sound crazy, but if the optimizer has a close call between two plans,
small differences in statistics may matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I don't know, Erland, if he updated the stats and rebuilt with more
free space, that should tickle the recompiles.
Remember, he said he had a lot of blocks, presumably more than the
similar systems, though these could be a secondary sign of slow
performance. So, it could just be the data, but then rebuilding it
onto a clean server wouldn't help.
Of course I agree I'd do a profiler/plan based analysis, but just
speculating, it's hard to guess what it would turn up.
So I have no idea, but I wonder if the problems will recur in short
order, if they are related to data mal-distributions causing bad
plans, or different traffic patterns.
J.
On Tue, 30 Jan 2007 08:20:08 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>Griff (griffithsj_520@.hotmail.com) writes:
>> So - there evidently was a problem with the database but none of the tools
>> we used showed up the problem. What did we miss?
>You did a lot of things, but not the things I would have done. I would
>have set up a trace, preferably server-side, and traced for statements
>with a duration over some limit, say 100 ms, and then analysed that
>trace to find out where the main bottlenecks are. I would then have looked
>at the query plans for these queries and compared with the other databases.
>I would also have looked at blocking.
>My guess is that statistics for the data in some places led the optimizer
>astray for one or more queries. It was resolved when you reloaded the data
>through DTS, because data may have been loaded in slightly different order.
>It may sound crazy, but if the optimizer has a close call between two plans,
>small differences in statistics may matter.|||Hi Erland & "J" (and everyone else on this thread)
I did mention briefly that we performed "a bit of investigative work". This
included using the performance analyser and getting traces for all stored
procedures and any dynamic SQL. There were some very slow running
queries....
We used the index tuning wizard and it suggested no new indexes were
required. As mentioned, the schema is identical for other customers (some
much larger DBs) and they have great performance. We also checked the
execution plan for one or two queries and they was the same on our problem
database and it was on another customer's database. So, we were fairly
confident that things were okay as far as the schema was concerned.
That's when we started running out of ideas. Are there any other symptoms
that we should have checked for - things that should have shouted to us to
look at the SHOWCONTIG output?
Griff|||One place to start would be the query plans. Compare the actual
execution plans for the problematic queries from the old database and the
newly built database. This will provide clues as to what might be wrong.
Before doing this, please make sure that the stats are updated and the
databases are identical in all respects (indexes, constraints, data etc). In
SQL Server 2000, you can use the SET STATISTICS PROFILE command to get the
actual execution plan. You can also use the "Show Plan Statistics" profiler
event.
--
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Griff wrote on Mon, 29 Jan 2007 17:21:10 -0000:
G> Hi
G> We host a series of databases (SQL Server 2000 sp4) for customers;
G> they are all of identical schema. Recently, one of them became
G> extremely slow. We tried to find out what was wrong, but couldn't.
G> I'll give you a quick account of what we did in the hope that you
G> might be able to suggest something we overlooked.
G> After a bit of investigative work, we found that there were loads of
G> locks present and that CPU on the server was maxed out.
G> 1 - we know that the DB schema is very optimised, so used SQL Compare
G> to compare this database's schema with another of our customer's in
G> case an index had been dropped. SQL Compare showed that the schemas
G> were identical.
G> 2 - we rebooted the Windows server in case there were any
G> environmental problems
G> 3 - we updated the usage/statistics for the database (though these
G> are set to update automatically I believe) and rebuilt all the
G> indexes.
G> 4 - we ran a script to increase the free-space in the tables
G> (normally set @.
G> 10% free space - I think this script set this to 15% and forced that
G> to implement there and then)
G> 5 - we ran the DBCC scripts for checking the database etc. This
G> reported that everything was fine.
G> 6 - we moved the database onto another server (backup & restore) -
G> the performance problems exhibited themselves on this server too so
G> we knew that the problem (whatever it was) was specific to this
G> database.
G> We then built a brand new DB using t-SQL statements and then migrated
G> the data from the old DB into this new one using DTS packages (only
G> about 4 GB of data). SQL Compare showed that the new DB has exactly
G> the same schema as the original DB but thankfully the new DB has
G> NONE of the performance problems.
G> So - there evidently was a problem with the database but none of the
G> tools we used showed up the problem. What did we miss?
G> Thanks everyone for any suggestions
G> Griff|||Griff (griffithsj_520@.hotmail.com) writes:
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
If the particular queries you looked at were slow due to blocking, it is
not surprising the plans were the same. Else it sounds very strange.
Difference in execution time is usually due to bad plans or looking. But
really bad fragmentation can play in as well.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
You said that you reindexed all tables, and in such case you should have
resolved such problems. Provided that all your tables has clustered indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||OK. You really need to do a trace of your system and find the
slow-running queries. Make a list of them. I will bet that there's a
lot of table scanning or hashing going on. Do you have a sample of one
of the queries that you can post? Can you post the DDL of the base
tables being queried, as well as some distributions of key data? The
Index Tuning Wizard is just not something that I'd run or trust for
telling me how to run my production system...
-Dave
Griff wrote:
> Hi Erland & "J" (and everyone else on this thread)
> I did mention briefly that we performed "a bit of investigative work". This
> included using the performance analyser and getting traces for all stored
> procedures and any dynamic SQL. There were some very slow running
> queries....
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
> Griff
>
-Dave Markle
http://www.markleconsulting.com/blog|||Are you in a hyperthreading mode ? If yes, try to turn it off. See Slava
Ocks paper.
A +
Griff a écrit :
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Monday, February 20, 2012
Performance monitor question
I'm trying to setup logging of my perfmon data into and MSSQL database, but
what I can't seem to find is the schema definition for the SQL Table(s) that
I need to create. Anyone know where I can find this at?
Thanks,
Tom
The schema will be created automatically by the Performance Logs and Alerts
service as long as the account has CREATE TABLE permissions in the target
database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'm trying to setup logging of my perfmon data into and MSSQL database,
but
> what I can't seem to find is the schema definition for the SQL Table(s)
that
> I need to create. Anyone know where I can find this at?
> Thanks,
> Tom
>
|||Thanks for the quick reply. I'll give it a try.
Tom
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ewG1puOgEHA.4040@.TK2MSFTNGP10.phx.gbl...
> The schema will be created automatically by the Performance Logs and
Alerts
> service as long as the account has CREATE TABLE permissions in the target
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
> news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> but
> that
>
what I can't seem to find is the schema definition for the SQL Table(s) that
I need to create. Anyone know where I can find this at?
Thanks,
Tom
The schema will be created automatically by the Performance Logs and Alerts
service as long as the account has CREATE TABLE permissions in the target
database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'm trying to setup logging of my perfmon data into and MSSQL database,
but
> what I can't seem to find is the schema definition for the SQL Table(s)
that
> I need to create. Anyone know where I can find this at?
> Thanks,
> Tom
>
|||Thanks for the quick reply. I'll give it a try.
Tom
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ewG1puOgEHA.4040@.TK2MSFTNGP10.phx.gbl...
> The schema will be created automatically by the Performance Logs and
Alerts
> service as long as the account has CREATE TABLE permissions in the target
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
> news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> but
> that
>
Performance monitor question
I'm trying to setup logging of my perfmon data into and MSSQL database, but
what I can't seem to find is the schema definition for the SQL Table(s) that
I need to create. Anyone know where I can find this at?
Thanks,
TomThe schema will be created automatically by the Performance Logs and Alerts
service as long as the account has CREATE TABLE permissions in the target
database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'm trying to setup logging of my perfmon data into and MSSQL database,
but
> what I can't seem to find is the schema definition for the SQL Table(s)
that
> I need to create. Anyone know where I can find this at?
> Thanks,
> Tom
>|||Thanks for the quick reply. I'll give it a try.
Tom
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ewG1puOgEHA.4040@.TK2MSFTNGP10.phx.gbl...
> The schema will be created automatically by the Performance Logs and
Alerts
> service as long as the account has CREATE TABLE permissions in the target
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
> news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> > I'm trying to setup logging of my perfmon data into and MSSQL database,
> but
> > what I can't seem to find is the schema definition for the SQL Table(s)
> that
> > I need to create. Anyone know where I can find this at?
> >
> > Thanks,
> > Tom
> >
> >
>
what I can't seem to find is the schema definition for the SQL Table(s) that
I need to create. Anyone know where I can find this at?
Thanks,
TomThe schema will be created automatically by the Performance Logs and Alerts
service as long as the account has CREATE TABLE permissions in the target
database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'm trying to setup logging of my perfmon data into and MSSQL database,
but
> what I can't seem to find is the schema definition for the SQL Table(s)
that
> I need to create. Anyone know where I can find this at?
> Thanks,
> Tom
>|||Thanks for the quick reply. I'll give it a try.
Tom
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ewG1puOgEHA.4040@.TK2MSFTNGP10.phx.gbl...
> The schema will be created automatically by the Performance Logs and
Alerts
> service as long as the account has CREATE TABLE permissions in the target
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
> news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> > I'm trying to setup logging of my perfmon data into and MSSQL database,
> but
> > what I can't seem to find is the schema definition for the SQL Table(s)
> that
> > I need to create. Anyone know where I can find this at?
> >
> > Thanks,
> > Tom
> >
> >
>
Subscribe to:
Posts (Atom)