This may be a "how long's a piece of string"-type question but I'm trying to
get a feel for the performance of the IN statement.
Broadly, we've some software which generates SQL for counting but we've hit
a situation where we could generate either re-engineer the SQL or simply
wrap an existing Select generated into a subselect and counting using an IN.
It's a minor change whereas re-engineering would be much more significant
piece of work
As a rule of thumb - Is IN slow?
I know it's probably a bit vague but I'm just after thoughts or perhaps a
link or 2 discussing this.
Thanks
SimonIN can be slower than using one of the alternatives, either a JOIN or an
EXISTS clause:
You can rewrite:
WHERE column IN (SELECT column FROM ...)
as either
INNER JOIN (SELECT column FROM ...) a
ON t.column = a.column
or
WHERE EXISTS (SELECT NULL FROM ... WHERE a.column = t.column)
You can then test which solution performs the best in your situation.
Note that the inner join will return multiple rows if the values in column
are not unique in the derived table.
Jacco Schalkwijk
SQL Server MVP
"Simon Woods" <simonDELETECAPSjwoods@.hotmaiIl.com> wrote in message
news:eq3oGjECFHA.4028@.TK2MSFTNGP15.phx.gbl...
> This may be a "how long's a piece of string"-type question but I'm trying
> to
> get a feel for the performance of the IN statement.
> Broadly, we've some software which generates SQL for counting but we've
> hit
> a situation where we could generate either re-engineer the SQL or simply
> wrap an existing Select generated into a subselect and counting using an
> IN.
> It's a minor change whereas re-engineering would be much more significant
> piece of work
> As a rule of thumb - Is IN slow?
> I know it's probably a bit vague but I'm just after thoughts or perhaps a
> link or 2 discussing this.
> Thanks
> Simon
>|||You're right - it is a bit "how long's a piece of string"!
Something that you may find useful is that positive criteria is much faster
than negative, so IN (1,2,3,6,7,8,9,10) should always be quicker than NOT IN
(4,5).
IN (1,2,3) should not be any slower than =1 OR =2 OR =3, in fact, it should
actually be quicker!
Key to quick querying with criteria is your indexing.
Hope this helps
Paula
"Simon Woods" wrote:
> This may be a "how long's a piece of string"-type question but I'm trying
to
> get a feel for the performance of the IN statement.
> Broadly, we've some software which generates SQL for counting but we've hi
t
> a situation where we could generate either re-engineer the SQL or simply
> wrap an existing Select generated into a subselect and counting using an I
N.
> It's a minor change whereas re-engineering would be much more significant
> piece of work
> As a rule of thumb - Is IN slow?
> I know it's probably a bit vague but I'm just after thoughts or perhaps a
> link or 2 discussing this.
> Thanks
> Simon
>
>|||Paula,
IN (1,2,3) is exactly the same as =1 OR =2 OR =3. IN is just short hand for
multiple OR statements. You can see that if you create a table with a CHECK
constraint that contains an IN clause. When you generate the script from
Query Analyzer, the IN clause will be changed into multiple OR statements.
Jacco Schalkwijk
SQL Server MVP
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:ECC51DC8-3BB3-45B3-A73B-954F9642D157@.microsoft.com...
> You're right - it is a bit "how long's a piece of string"!
> Something that you may find useful is that positive criteria is much
> faster
> than negative, so IN (1,2,3,6,7,8,9,10) should always be quicker than NOT
> IN
> (4,5).
> IN (1,2,3) should not be any slower than =1 OR =2 OR =3, in fact, it
> should
> actually be quicker!
> Key to quick querying with criteria is your indexing.
> Hope this helps
> Paula
> "Simon Woods" wrote:
>
Showing posts with label type. Show all posts
Showing posts with label type. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
Performance Problems On Recursive Table
I have a table that has a parent-child relation to itself. (see SQL at end)
Basically, records of type 0 are related to type 1, and type 1 to type 2.
This table has about 2 million records right now. When I do this:
INNER JOIN
ExpSrvLog I ON E.ExpSrvID = I.ExpSrvID AND I.ItemType = 2
LEFT OUTER JOIN
ExpSrvLog B ON I.LogID = B.ParentID AND B.ItemType = 1
It seems to consume an inordinate amount of time. Any way to optimize this
behavior? Thanks.
CREATE TABLE [ExpSrvLog] (
[LogID] [bigint] IDENTITY (1, 1) NOT NULL ,
[ExpSrvID] [int] NOT NULL ,
[CalendarDate] [smalldatetime] NOT NULL ,
[ItemType] [tinyint] NOT NULL ,
[VendorInvoiceID] [int] NULL ,
[ClientInvoiceID] [int] NULL ,
[PaymentID] [int] NULL ,
[BillingAmount] [money] NULL ,
[ActualQty] [decimal](8, 2) NULL ,
[ActualRate] [money] NULL ,
[ParentID] [bigint] NULL ,
CONSTRAINT [PK_ExpSrvLog] PRIMARY KEY CLUSTERED
(
[LogID] DESC
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_ExpSrvLog_ClientInvoice] FOREIGN KEY
(
[ClientInvoiceID]
) REFERENCES [ClientInvoice] (
[InvoiceID]
),
CONSTRAINT [FK_ExpSrvLog_ExpSrvLog] FOREIGN KEY
(
[ParentID]
) REFERENCES [ExpSrvLog] (
[LogID]
),
CONSTRAINT [FK_ExpSrvLog_VendorInvoice] FOREIGN KEY
(
[VendorInvoiceID]
) REFERENCES [VendorInvoice] (
[InvoiceID]
)
) ON [PRIMARY]
ENDApart from the primary key, are there any other indexes on that table?
Foreign key columns should at least be indexed.
ML|||"ML" wrote:
> Apart from the primary key, are there any other indexes on that table?
Yes. One over ExpSrvID, CalendarDate and Itemtype. One over ParentID. (The
VendorInvoiceID and CLientInvoiceID fields are also indexes as they are FKs.
)
I've also tried an index over ParentID and LogID, to no help.
- alphadog|||Paul, While recursive joins are elegant, they are notoriously slow on
recursive tables with > 1 million rows. You might want try to use mutiples
queries and(or) tables to accomplish your task instead of the recursive join
s.
"Paul Tiseo" wrote:
> "ML" wrote:
> Yes. One over ExpSrvID, CalendarDate and Itemtype. One over ParentID. (The
> VendorInvoiceID and CLientInvoiceID fields are also indexes as they are FK
s.)
> I've also tried an index over ParentID and LogID, to no help.
> - alphadog|||Really? Damn. It works so well that way. Oh well, thanks for the info, Frank
.
So, what are all my options? Physically splitting the table is something I
have planned, but at this point in developement is not an option. I guess
it'll have to be temp tables.
"frank chang" wrote:
> Paul, While recursive joins are elegant, they are notoriously slow on
> recursive tables with > 1 million rows. You might want try to use mutiples
> queries and(or) tables to accomplish your task instead of the recursive jo
ins.
> "Paul Tiseo" wrote:
>|||Maybe this example can help you find a way to navigate the
ancestor/descendant axes in your hierarchy:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML|||>> I have a table that has a parent-child relation to itself. (see SQL at end) Basical
ly, records [sic] of type 0 are related to type 1, and type 1 to type 2. This table h
as about 2 million records [sic] right now. When I do this: <<
No relational key, IDENTITY, too many NULL-able columns, money and
bigint proprierary datatypes and you don't know that rows and records
are not the same. .And there are not specs.
behavior? <<
Change the DDL. Does this hierarchy only go down three levels? Can
level 0 have more than one level 1 subordinate? Can a level 1 have
more than one level 2 subordinate? If I assume not, then:
CREATE TABLE ExpsrvLog
(expsrv_id INTEGER NOT NULL,
hierarchy_level INTEGER DEFAULT 0 NOT NULL
CHECK (hierarchy_level IN (0, 1, 2)),
payment_date DATETIME NOT NULL,
vendor_invoice_nbr INTEGER NOT NULL,
REFERENCES VendorInvoices (invoice_nbr)
ON UPDATE CASCADE,
client_invoice_nbr INTEGER NOT NULL
REFERENCES ClientInvoices (invoice_nbr)
ON UPDATE CASCADE,
payment_nbr INTEGER NOT NULL,
billing_amount DECIMAL(8,2) NOT NULL,
actual_qty DECIMAL(8,2) NOT NULL,
actual_rate DECIMAL(8,2) NULL,
PRIMARY KEY (expsrv_id, hierarchy_level));
If the asumption was wrong, we can move onto the nested sets model.
You might want to get a copy of TREES & HIERARCHIES IN SQL along with a
basic data modeling book.|||"--CELKO--" wrote:
> No relational key,
Of what type? I have a primary key (see first post), an alternate key (not
in the original DDL, but basically CREATE UNIQUE INDEX AK_ExpSrvLog ON
dbo.ExpSrvLog([ExpSrvID], [CalendarDate] DESC , [ItemType]) ) and some
primary-foreign key relationships. What exactly is missing? Do you mean a
natural key? If so, it's the AK that I did not include.
> IDENTITY
Why? How does IDENTITY affect my performance question?
> too many NULL-able columns,
The Real World intrudes into the World of Relational Model Puritans
sometimes. <shrug> Again, how do the NULLable columns affect my performance
problem?
> money and bigint proprierary datatypes
Not a problem for this project.
> and you don't know that rows and records are not the same.
Do you know what they say about "ASS-U-ME"? I use common idioms. Forgive me
for not abiding by your stricter one.
> And there are not specs.
I'm sorry, but I don't understand this comment. What specs do you need?
> Change the DDL. Does this hierarchy only go down three levels? Can
> level 0 have more than one level 1 subordinate? Can a level 1 have
> more than one level 2 subordinate? If I assume not, then:
Yes (three and only three levels), yes and yes.
> CREATE TABLE ExpsrvLog
> (expsrv_id INTEGER NOT NULL,
> hierarchy_level INTEGER DEFAULT 0 NOT NULL
> CHECK (hierarchy_level IN (0, 1, 2)),
> payment_date DATETIME NOT NULL,
> vendor_invoice_nbr INTEGER NOT NULL,
> REFERENCES VendorInvoices (invoice_nbr)
> ON UPDATE CASCADE,
> client_invoice_nbr INTEGER NOT NULL
> REFERENCES ClientInvoices (invoice_nbr)
> ON UPDATE CASCADE,
> payment_nbr INTEGER NOT NULL,
> billing_amount DECIMAL(8,2) NOT NULL,
> actual_qty DECIMAL(8,2) NOT NULL,
> actual_rate DECIMAL(8,2) NULL,
> PRIMARY KEY (expsrv_id, hierarchy_level));
> If the asumption was wrong, we can move onto the nested sets model.
So, you changed the structure of the table in a way that doesn't address my
original problem (in fact you seem to have removed the recursive
relationship) but makes it more purist-friendly in terms of datatypes and
nullability. Your "SQL standardization" and "Relational Model" efforts are
appreciated (although your zeal blinded you to my actual question) but it's
not a solution to my immediate problem.
I don't need to know *how* to model or query the hierarchy. I need to know
*why* it isn't performing properly when I use the self-join.
Thanks.sql
Basically, records of type 0 are related to type 1, and type 1 to type 2.
This table has about 2 million records right now. When I do this:
INNER JOIN
ExpSrvLog I ON E.ExpSrvID = I.ExpSrvID AND I.ItemType = 2
LEFT OUTER JOIN
ExpSrvLog B ON I.LogID = B.ParentID AND B.ItemType = 1
It seems to consume an inordinate amount of time. Any way to optimize this
behavior? Thanks.
CREATE TABLE [ExpSrvLog] (
[LogID] [bigint] IDENTITY (1, 1) NOT NULL ,
[ExpSrvID] [int] NOT NULL ,
[CalendarDate] [smalldatetime] NOT NULL ,
[ItemType] [tinyint] NOT NULL ,
[VendorInvoiceID] [int] NULL ,
[ClientInvoiceID] [int] NULL ,
[PaymentID] [int] NULL ,
[BillingAmount] [money] NULL ,
[ActualQty] [decimal](8, 2) NULL ,
[ActualRate] [money] NULL ,
[ParentID] [bigint] NULL ,
CONSTRAINT [PK_ExpSrvLog] PRIMARY KEY CLUSTERED
(
[LogID] DESC
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_ExpSrvLog_ClientInvoice] FOREIGN KEY
(
[ClientInvoiceID]
) REFERENCES [ClientInvoice] (
[InvoiceID]
),
CONSTRAINT [FK_ExpSrvLog_ExpSrvLog] FOREIGN KEY
(
[ParentID]
) REFERENCES [ExpSrvLog] (
[LogID]
),
CONSTRAINT [FK_ExpSrvLog_VendorInvoice] FOREIGN KEY
(
[VendorInvoiceID]
) REFERENCES [VendorInvoice] (
[InvoiceID]
)
) ON [PRIMARY]
ENDApart from the primary key, are there any other indexes on that table?
Foreign key columns should at least be indexed.
ML|||"ML" wrote:
> Apart from the primary key, are there any other indexes on that table?
Yes. One over ExpSrvID, CalendarDate and Itemtype. One over ParentID. (The
VendorInvoiceID and CLientInvoiceID fields are also indexes as they are FKs.
)
I've also tried an index over ParentID and LogID, to no help.
- alphadog|||Paul, While recursive joins are elegant, they are notoriously slow on
recursive tables with > 1 million rows. You might want try to use mutiples
queries and(or) tables to accomplish your task instead of the recursive join
s.
"Paul Tiseo" wrote:
> "ML" wrote:
> Yes. One over ExpSrvID, CalendarDate and Itemtype. One over ParentID. (The
> VendorInvoiceID and CLientInvoiceID fields are also indexes as they are FK
s.)
> I've also tried an index over ParentID and LogID, to no help.
> - alphadog|||Really? Damn. It works so well that way. Oh well, thanks for the info, Frank
.
So, what are all my options? Physically splitting the table is something I
have planned, but at this point in developement is not an option. I guess
it'll have to be temp tables.
"frank chang" wrote:
> Paul, While recursive joins are elegant, they are notoriously slow on
> recursive tables with > 1 million rows. You might want try to use mutiples
> queries and(or) tables to accomplish your task instead of the recursive jo
ins.
> "Paul Tiseo" wrote:
>|||Maybe this example can help you find a way to navigate the
ancestor/descendant axes in your hierarchy:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML|||>> I have a table that has a parent-child relation to itself. (see SQL at end) Basical
ly, records [sic] of type 0 are related to type 1, and type 1 to type 2. This table h
as about 2 million records [sic] right now. When I do this: <<
No relational key, IDENTITY, too many NULL-able columns, money and
bigint proprierary datatypes and you don't know that rows and records
are not the same. .And there are not specs.
behavior? <<
Change the DDL. Does this hierarchy only go down three levels? Can
level 0 have more than one level 1 subordinate? Can a level 1 have
more than one level 2 subordinate? If I assume not, then:
CREATE TABLE ExpsrvLog
(expsrv_id INTEGER NOT NULL,
hierarchy_level INTEGER DEFAULT 0 NOT NULL
CHECK (hierarchy_level IN (0, 1, 2)),
payment_date DATETIME NOT NULL,
vendor_invoice_nbr INTEGER NOT NULL,
REFERENCES VendorInvoices (invoice_nbr)
ON UPDATE CASCADE,
client_invoice_nbr INTEGER NOT NULL
REFERENCES ClientInvoices (invoice_nbr)
ON UPDATE CASCADE,
payment_nbr INTEGER NOT NULL,
billing_amount DECIMAL(8,2) NOT NULL,
actual_qty DECIMAL(8,2) NOT NULL,
actual_rate DECIMAL(8,2) NULL,
PRIMARY KEY (expsrv_id, hierarchy_level));
If the asumption was wrong, we can move onto the nested sets model.
You might want to get a copy of TREES & HIERARCHIES IN SQL along with a
basic data modeling book.|||"--CELKO--" wrote:
> No relational key,
Of what type? I have a primary key (see first post), an alternate key (not
in the original DDL, but basically CREATE UNIQUE INDEX AK_ExpSrvLog ON
dbo.ExpSrvLog([ExpSrvID], [CalendarDate] DESC , [ItemType]) ) and some
primary-foreign key relationships. What exactly is missing? Do you mean a
natural key? If so, it's the AK that I did not include.
> IDENTITY
Why? How does IDENTITY affect my performance question?
> too many NULL-able columns,
The Real World intrudes into the World of Relational Model Puritans
sometimes. <shrug> Again, how do the NULLable columns affect my performance
problem?
> money and bigint proprierary datatypes
Not a problem for this project.
> and you don't know that rows and records are not the same.
Do you know what they say about "ASS-U-ME"? I use common idioms. Forgive me
for not abiding by your stricter one.
> And there are not specs.
I'm sorry, but I don't understand this comment. What specs do you need?
> Change the DDL. Does this hierarchy only go down three levels? Can
> level 0 have more than one level 1 subordinate? Can a level 1 have
> more than one level 2 subordinate? If I assume not, then:
Yes (three and only three levels), yes and yes.
> CREATE TABLE ExpsrvLog
> (expsrv_id INTEGER NOT NULL,
> hierarchy_level INTEGER DEFAULT 0 NOT NULL
> CHECK (hierarchy_level IN (0, 1, 2)),
> payment_date DATETIME NOT NULL,
> vendor_invoice_nbr INTEGER NOT NULL,
> REFERENCES VendorInvoices (invoice_nbr)
> ON UPDATE CASCADE,
> client_invoice_nbr INTEGER NOT NULL
> REFERENCES ClientInvoices (invoice_nbr)
> ON UPDATE CASCADE,
> payment_nbr INTEGER NOT NULL,
> billing_amount DECIMAL(8,2) NOT NULL,
> actual_qty DECIMAL(8,2) NOT NULL,
> actual_rate DECIMAL(8,2) NULL,
> PRIMARY KEY (expsrv_id, hierarchy_level));
> If the asumption was wrong, we can move onto the nested sets model.
So, you changed the structure of the table in a way that doesn't address my
original problem (in fact you seem to have removed the recursive
relationship) but makes it more purist-friendly in terms of datatypes and
nullability. Your "SQL standardization" and "Relational Model" efforts are
appreciated (although your zeal blinded you to my actual question) but it's
not a solution to my immediate problem.
I don't need to know *how* to model or query the hierarchy. I need to know
*why* it isn't performing properly when I use the self-join.
Thanks.sql
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
After a disk failure ( disk was in raid 1 configuration ) SQL Server
start to work very slow.
SqlServer start to raise following event
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 7/3/2006
Time: 8:54:12 AM
User: N/A
Computer: SRVCA2
Description:
17052 :
This SQL Server has been optimized for 8 concurrent queries. This limit
has been exceeded by 19 queries and performance may be adversely
affected.
SQL Server worked fine before this. I replaced hard disk and rebuild of
configuration was sucesfull. In SQL events i saw that SQL Server has
performed recovery of databases.
I have SQL Server Personal edition installed.
Can someone help me with this one please.The verion of the db engine shipped with the Personal Edition has an inbuilt
restriction , that once more than a certain number of activities pass the
restriction.
There are a number of different activities which contribute to the count.
Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
the limit. Maybe you could upgrade
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sale" <aleksandar.stancic@.gmail.com> wrote in message
news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> After a disk failure ( disk was in raid 1 configuration ) SQL Server
> start to work very slow.
> SqlServer start to raise following event
> Event Type: Information
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 7/3/2006
> Time: 8:54:12 AM
> User: N/A
> Computer: SRVCA2
> Description:
> 17052 :
> This SQL Server has been optimized for 8 concurrent queries. This limit
> has been exceeded by 19 queries and performance may be adversely
> affected.
>
> SQL Server worked fine before this. I replaced hard disk and rebuild of
> configuration was sucesfull. In SQL events i saw that SQL Server has
> performed recovery of databases.
> I have SQL Server Personal edition installed.
> Can someone help me with this one please.
>|||"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:TI-dnbeidMt4RzXZnZ2dnUVZ8s-dnZ2d@.bt.com...
> The verion of the db engine shipped with the Personal Edition has an
inbuilt
> restriction , that once more than a certain number of activities pass the
> restriction.
> There are a number of different activities which contribute to the count.
> Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
> the limit. Maybe you could upgrade
Most likely the slower diskI/O during the failure was the cause here.
You may want to upgrade to something like SQL 2005 Express which removes
this particular restriction. (but has others).
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sale" <aleksandar.stancic@.gmail.com> wrote in message
> news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
>|||I Upgrade Personal edition to Standard, and after this everything work
fine.
Thanks for your help people
start to work very slow.
SqlServer start to raise following event
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 7/3/2006
Time: 8:54:12 AM
User: N/A
Computer: SRVCA2
Description:
17052 :
This SQL Server has been optimized for 8 concurrent queries. This limit
has been exceeded by 19 queries and performance may be adversely
affected.
SQL Server worked fine before this. I replaced hard disk and rebuild of
configuration was sucesfull. In SQL events i saw that SQL Server has
performed recovery of databases.
I have SQL Server Personal edition installed.
Can someone help me with this one please.The verion of the db engine shipped with the Personal Edition has an inbuilt
restriction , that once more than a certain number of activities pass the
restriction.
There are a number of different activities which contribute to the count.
Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
the limit. Maybe you could upgrade
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sale" <aleksandar.stancic@.gmail.com> wrote in message
news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> After a disk failure ( disk was in raid 1 configuration ) SQL Server
> start to work very slow.
> SqlServer start to raise following event
> Event Type: Information
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 7/3/2006
> Time: 8:54:12 AM
> User: N/A
> Computer: SRVCA2
> Description:
> 17052 :
> This SQL Server has been optimized for 8 concurrent queries. This limit
> has been exceeded by 19 queries and performance may be adversely
> affected.
>
> SQL Server worked fine before this. I replaced hard disk and rebuild of
> configuration was sucesfull. In SQL events i saw that SQL Server has
> performed recovery of databases.
> I have SQL Server Personal edition installed.
> Can someone help me with this one please.
>|||"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:TI-dnbeidMt4RzXZnZ2dnUVZ8s-dnZ2d@.bt.com...
> The verion of the db engine shipped with the Personal Edition has an
inbuilt
> restriction , that once more than a certain number of activities pass the
> restriction.
> There are a number of different activities which contribute to the count.
> Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
> the limit. Maybe you could upgrade
Most likely the slower diskI/O during the failure was the cause here.
You may want to upgrade to something like SQL 2005 Express which removes
this particular restriction. (but has others).
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sale" <aleksandar.stancic@.gmail.com> wrote in message
> news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
>|||I Upgrade Personal edition to Standard, and after this everything work
fine.
Thanks for your help people
Monday, March 12, 2012
Performance problem
After a disk failure ( disk was in raid 1 configuration ) SQL Server
start to work very slow.
SqlServer start to raise following event
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 7/3/2006
Time: 8:54:12 AM
User: N/A
Computer: SRVCA2
Description:
17052 :
This SQL Server has been optimized for 8 concurrent queries. This limit
has been exceeded by 19 queries and performance may be adversely
affected.
SQL Server worked fine before this. I replaced hard disk and rebuild of
configuration was sucesfull. In SQL events i saw that SQL Server has
performed recovery of databases.
I have SQL Server Personal edition installed.
Can someone help me with this one please.The verion of the db engine shipped with the Personal Edition has an inbuilt
restriction , that once more than a certain number of activities pass the
restriction.
There are a number of different activities which contribute to the count.
Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
the limit. Maybe you could upgrade
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sale" <aleksandar.stancic@.gmail.com> wrote in message
news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> After a disk failure ( disk was in raid 1 configuration ) SQL Server
> start to work very slow.
> SqlServer start to raise following event
> Event Type: Information
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 7/3/2006
> Time: 8:54:12 AM
> User: N/A
> Computer: SRVCA2
> Description:
> 17052 :
> This SQL Server has been optimized for 8 concurrent queries. This limit
> has been exceeded by 19 queries and performance may be adversely
> affected.
>
> SQL Server worked fine before this. I replaced hard disk and rebuild of
> configuration was sucesfull. In SQL events i saw that SQL Server has
> performed recovery of databases.
> I have SQL Server Personal edition installed.
> Can someone help me with this one please.
>|||"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:TI-dnbeidMt4RzXZnZ2dnUVZ8s-dnZ2d@.bt.com...
> The verion of the db engine shipped with the Personal Edition has an
inbuilt
> restriction , that once more than a certain number of activities pass the
> restriction.
> There are a number of different activities which contribute to the count.
> Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
> the limit. Maybe you could upgrade
Most likely the slower diskI/O during the failure was the cause here.
You may want to upgrade to something like SQL 2005 Express which removes
this particular restriction. (but has others).
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sale" <aleksandar.stancic@.gmail.com> wrote in message
> news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> > After a disk failure ( disk was in raid 1 configuration ) SQL Server
> > start to work very slow.
> >
> > SqlServer start to raise following event
> >
> > Event Type: Information
> > Event Source: MSSQLSERVER
> > Event Category: (2)
> > Event ID: 17055
> > Date: 7/3/2006
> > Time: 8:54:12 AM
> > User: N/A
> > Computer: SRVCA2
> > Description:
> > 17052 :
> > This SQL Server has been optimized for 8 concurrent queries. This limit
> > has been exceeded by 19 queries and performance may be adversely
> > affected.
> >
> >
> > SQL Server worked fine before this. I replaced hard disk and rebuild of
> > configuration was sucesfull. In SQL events i saw that SQL Server has
> > performed recovery of databases.
> >
> > I have SQL Server Personal edition installed.
> >
> > Can someone help me with this one please.
> >
>|||I Upgrade Personal edition to Standard, and after this everything work
fine.
Thanks for your help people
start to work very slow.
SqlServer start to raise following event
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 7/3/2006
Time: 8:54:12 AM
User: N/A
Computer: SRVCA2
Description:
17052 :
This SQL Server has been optimized for 8 concurrent queries. This limit
has been exceeded by 19 queries and performance may be adversely
affected.
SQL Server worked fine before this. I replaced hard disk and rebuild of
configuration was sucesfull. In SQL events i saw that SQL Server has
performed recovery of databases.
I have SQL Server Personal edition installed.
Can someone help me with this one please.The verion of the db engine shipped with the Personal Edition has an inbuilt
restriction , that once more than a certain number of activities pass the
restriction.
There are a number of different activities which contribute to the count.
Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
the limit. Maybe you could upgrade
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sale" <aleksandar.stancic@.gmail.com> wrote in message
news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> After a disk failure ( disk was in raid 1 configuration ) SQL Server
> start to work very slow.
> SqlServer start to raise following event
> Event Type: Information
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 7/3/2006
> Time: 8:54:12 AM
> User: N/A
> Computer: SRVCA2
> Description:
> 17052 :
> This SQL Server has been optimized for 8 concurrent queries. This limit
> has been exceeded by 19 queries and performance may be adversely
> affected.
>
> SQL Server worked fine before this. I replaced hard disk and rebuild of
> configuration was sucesfull. In SQL events i saw that SQL Server has
> performed recovery of databases.
> I have SQL Server Personal edition installed.
> Can someone help me with this one please.
>|||"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:TI-dnbeidMt4RzXZnZ2dnUVZ8s-dnZ2d@.bt.com...
> The verion of the db engine shipped with the Personal Edition has an
inbuilt
> restriction , that once more than a certain number of activities pass the
> restriction.
> There are a number of different activities which contribute to the count.
> Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
> the limit. Maybe you could upgrade
Most likely the slower diskI/O during the failure was the cause here.
You may want to upgrade to something like SQL 2005 Express which removes
this particular restriction. (but has others).
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sale" <aleksandar.stancic@.gmail.com> wrote in message
> news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> > After a disk failure ( disk was in raid 1 configuration ) SQL Server
> > start to work very slow.
> >
> > SqlServer start to raise following event
> >
> > Event Type: Information
> > Event Source: MSSQLSERVER
> > Event Category: (2)
> > Event ID: 17055
> > Date: 7/3/2006
> > Time: 8:54:12 AM
> > User: N/A
> > Computer: SRVCA2
> > Description:
> > 17052 :
> > This SQL Server has been optimized for 8 concurrent queries. This limit
> > has been exceeded by 19 queries and performance may be adversely
> > affected.
> >
> >
> > SQL Server worked fine before this. I replaced hard disk and rebuild of
> > configuration was sucesfull. In SQL events i saw that SQL Server has
> > performed recovery of databases.
> >
> > I have SQL Server Personal edition installed.
> >
> > Can someone help me with this one please.
> >
>|||I Upgrade Personal edition to Standard, and after this everything work
fine.
Thanks for your help people
Performance penalty for LIKE when I really mean Equal?
Hi,
I have a situation where I can retrieve either all the rows in a table or
just some of them based on a "type" field in the row. The type of the rows
to select is passed as a parm to the method that retrieves the rows. If the
calling program wants the whole table it passes String.Empty as the type.
I was thinking of having one parameterized SQL statement like this...
SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
I'll append '%' to whatever "type" is passed the method. So if the calling
routine wants all records, LIKE '%' should return all rows. If the User
passes 'OT' (assuming a two character type code) I should still get all the
'OT' rows from the passed parm of
'OT%'.
Will I suffer a performance penalty by doing this, or is the query optimizer
smart enough to figure out that for a two character field, LIKE 'OT%' is th
e
equivalent of = 'OT'?
Thanks.
BBMFor prefix criteria (i.e. where the wildcard character is not used at the
beginning of the search argument), the query optimizer can take advantage of
indexes if the column used in the LIKE expression is indexed.
ML|||If @.Type is NULL, then the following will return all rows without evaluating
the like comparison:
SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
"BBM" <bbm@.bbmcompany.com> wrote in message
news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
> Hi,
> I have a situation where I can retrieve either all the rows in a table or
> just some of them based on a "type" field in the row. The type of the
> rows
> to select is passed as a parm to the method that retrieves the rows. If
> the
> calling program wants the whole table it passes String.Empty as the type.
> I was thinking of having one parameterized SQL statement like this...
> SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
> I'll append '%' to whatever "type" is passed the method. So if the
> calling
> routine wants all records, LIKE '%' should return all rows. If the User
> passes 'OT' (assuming a two character type code) I should still get all
> the
> 'OT' rows from the passed parm of
> 'OT%'.
> Will I suffer a performance penalty by doing this, or is the query
> optimizer
> smart enough to figure out that for a two character field, LIKE 'OT%' is
> the
> equivalent of = 'OT'?
> Thanks.
> BBM|||Thanks for the tip. I'll try this.
"JT" wrote:
> If @.Type is NULL, then the following will return all rows without evaluati
ng
> the like comparison:
> SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
>
> "BBM" <bbm@.bbmcompany.com> wrote in message
> news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
>
>
I have a situation where I can retrieve either all the rows in a table or
just some of them based on a "type" field in the row. The type of the rows
to select is passed as a parm to the method that retrieves the rows. If the
calling program wants the whole table it passes String.Empty as the type.
I was thinking of having one parameterized SQL statement like this...
SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
I'll append '%' to whatever "type" is passed the method. So if the calling
routine wants all records, LIKE '%' should return all rows. If the User
passes 'OT' (assuming a two character type code) I should still get all the
'OT' rows from the passed parm of
'OT%'.
Will I suffer a performance penalty by doing this, or is the query optimizer
smart enough to figure out that for a two character field, LIKE 'OT%' is th
e
equivalent of = 'OT'?
Thanks.
BBMFor prefix criteria (i.e. where the wildcard character is not used at the
beginning of the search argument), the query optimizer can take advantage of
indexes if the column used in the LIKE expression is indexed.
ML|||If @.Type is NULL, then the following will return all rows without evaluating
the like comparison:
SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
"BBM" <bbm@.bbmcompany.com> wrote in message
news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
> Hi,
> I have a situation where I can retrieve either all the rows in a table or
> just some of them based on a "type" field in the row. The type of the
> rows
> to select is passed as a parm to the method that retrieves the rows. If
> the
> calling program wants the whole table it passes String.Empty as the type.
> I was thinking of having one parameterized SQL statement like this...
> SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
> I'll append '%' to whatever "type" is passed the method. So if the
> calling
> routine wants all records, LIKE '%' should return all rows. If the User
> passes 'OT' (assuming a two character type code) I should still get all
> the
> 'OT' rows from the passed parm of
> 'OT%'.
> Will I suffer a performance penalty by doing this, or is the query
> optimizer
> smart enough to figure out that for a two character field, LIKE 'OT%' is
> the
> equivalent of = 'OT'?
> Thanks.
> BBM|||Thanks for the tip. I'll try this.
"JT" wrote:
> If @.Type is NULL, then the following will return all rows without evaluati
ng
> the like comparison:
> SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
>
> "BBM" <bbm@.bbmcompany.com> wrote in message
> news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)