Clustered DB Server: appears

Showing posts with label appears. Show all posts

Wednesday, March 7, 2012

COALESCE in a WHERE clause

Hi
We have recently been experiencing a performance problem that appears to
involve COALESCE in a WHERE clause. It started to happen recently when one
of the affected tables grew a bit. Given the following tables (each is
about 200,000 rows and 10 or 12 columns) and view:
CREATE TABLE Table1 (
keycol1 VARCHAR(10) NOT NULL PRIMARY KEY
,col1 VARCHAR(5) NOT NULL
...
);
CREATE TABLE Table2 (
keycol1 VARCHAR(10) NOT NULL
REFERENCES Table1 (keycol1)
ON UPDATE CASCADE
ON DELETE CASCADE
,keycol2 VARCHAR(6) NOT NULL
,col2 VARCHAR(20) NOT NULL
...
,PRIMARY KEY (keycol1, keycol2)
);
CREATE VIEW View1
AS
SELECT T2.keycol1, T2.keycol2, T1.col1, T2.col2
FROM Table2 AS T2
JOIN Table1 AS T1 ON T1.keycol1 = T2.keycol2
The following procedure (I removed the CREATE PROCEDURE for simplicity) is
almost instantaneous, with subsecond response time.
DECLARE @.param AS VARCHAR(10);
SET @.param = 'abcdefgh';
SELECT keycol1, keycol2, col1, col2 FROM View1
WHERE keycol1 = @.param;
However, when using a COALESCE in the WHERE (because a NULL is possible)
SELECT keycol1, keycol2, col1, col2 FROM View1
WHERE keycol1 = COALESCE(@.param, keycol1);
All of the sudden the query starts to crawl (average 30 seconds or more).
It seems to be centered on the check for NULL in the parameter, because if I
change it to:
SELECT keycol1, keycol2, col1, col2 FROM View1
WHERE @.param IS NULL;
It is still slow. Any ideas?
JoeThis is because when you use ANY function operating on a column, in a Where
Clause, the Query Processor can no longer use an index for the query. So
then it has t oread the entire table, to run the Coalesce(@.Param, keycol1)
function on every row. The index does not have the value of
COALESCE(@.param, keycol1) in it, it just has teh value of keyCol1 in it...
Change the query to
SELECT keycol1, keycol2, col1, col2 FROM View1
WHERE @.Param Is Null Or keycol1 = @.param
And it should be able to use the index again...
"J. M. De Moor" wrote:

> Hi
> We have recently been experiencing a performance problem that appears to
> involve COALESCE in a WHERE clause. It started to happen recently when on
e
> of the affected tables grew a bit. Given the following tables (each is
> about 200,000 rows and 10 or 12 columns) and view:
> CREATE TABLE Table1 (
> keycol1 VARCHAR(10) NOT NULL PRIMARY KEY
> ,col1 VARCHAR(5) NOT NULL
> ...
> );
> CREATE TABLE Table2 (
> keycol1 VARCHAR(10) NOT NULL
> REFERENCES Table1 (keycol1)
> ON UPDATE CASCADE
> ON DELETE CASCADE
> ,keycol2 VARCHAR(6) NOT NULL
> ,col2 VARCHAR(20) NOT NULL
> ...
> ,PRIMARY KEY (keycol1, keycol2)
> );
> CREATE VIEW View1
> AS
> SELECT T2.keycol1, T2.keycol2, T1.col1, T2.col2
> FROM Table2 AS T2
> JOIN Table1 AS T1 ON T1.keycol1 = T2.keycol2
> The following procedure (I removed the CREATE PROCEDURE for simplicity) is
> almost instantaneous, with subsecond response time.
> DECLARE @.param AS VARCHAR(10);
> SET @.param = 'abcdefgh';
> SELECT keycol1, keycol2, col1, col2 FROM View1
> WHERE keycol1 = @.param;
> However, when using a COALESCE in the WHERE (because a NULL is possible)
> SELECT keycol1, keycol2, col1, col2 FROM View1
> WHERE keycol1 = COALESCE(@.param, keycol1);
> All of the sudden the query starts to crawl (average 30 seconds or more).
> It seems to be centered on the check for NULL in the parameter, because if
I
> change it to:
> SELECT keycol1, keycol2, col1, col2 FROM View1
> WHERE @.param IS NULL;
> It is still slow. Any ideas?
> Joe
>
>|||Did you get a chance to go through some alternatives suggested at:
http://www.sommarskog.se/dyn-search.html
Anith|||Anith
Terrific article...especially the bag of tricks in the end. Thanks.
Joe

Sunday, February 19, 2012

Clustering and Hyperthreading

Hi,
We had a problem with a clustered sql box a month or so ago and contacted MS product support. Anyway, we got a patch which appears to have fixed our problem. However, one thing was curious. I didn't speak to Moft but the person who did said MS said NOT to
turn hyperthreading on SQL clusters on! Now that shocked me. Does anyone know why? We turned it off to see whether it was reponsible for some performance issues and results were inconclusive. So we were going to turn it back on. We were strongly advised
not to do so. So does anyone know why or could point me in the right direction? I've never heard this mentioned before.
Thanks
DaveK
http://www.sqlporn.co.uk
The only time you turn it off, as far as I know of, is when you have the max
number of processors installed already.
Example: Windows XP support only 2 processors, if you have two already, turn
off Hyperthreading.
Example: Windows Enterprise supports 8 processors, if you have 8 already,
turn off Hyperthreading.
So, how many processors are in your nodes?
Cheers,
Rod
"DaveK" <anonymous@.discussions.microsoft.com> wrote in message
news:1AF8D235-E366-4CF0-8F9F-23FBBE1D25F4@.microsoft.com...
> Hi,
> We had a problem with a clustered sql box a month or so ago and contacted
MS product support. Anyway, we got a patch which appears to have fixed our
problem. However, one thing was curious. I didn't speak to Moft but the
person who did said MS said NOT to turn hyperthreading on SQL clusters on!
Now that shocked me. Does anyone know why? We turned it off to see whether
it was reponsible for some performance issues and results were inconclusive.
So we were going to turn it back on. We were strongly advised not to do so.
So does anyone know why or could point me in the right direction? I've never
heard this mentioned before.
> Thanks
> DaveK
> http://www.sqlporn.co.uk
|||Not exactly.
I have a cluster with 8-way Hyperthreaded hosts. SQL handles the 16 virtual
processors just fine. I believe that Service Pack 2 contained the code to
fix the virtual/physical processor count issue. Build 816 and 910 (post SP3
hotfixes) contain some code to improve performance on Hyperthreaded systems.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%23PHgs2%23REHA.3344@.TK2MSFTNGP12.phx.gbl...
> The only time you turn it off, as far as I know of, is when you have the
max
> number of processors installed already.
> Example: Windows XP support only 2 processors, if you have two already,
turn[vbcol=seagreen]
> off Hyperthreading.
> Example: Windows Enterprise supports 8 processors, if you have 8 already,
> turn off Hyperthreading.
> So, how many processors are in your nodes?
> Cheers,
> Rod
> "DaveK" <anonymous@.discussions.microsoft.com> wrote in message
> news:1AF8D235-E366-4CF0-8F9F-23FBBE1D25F4@.microsoft.com...
contacted
> MS product support. Anyway, we got a patch which appears to have fixed our
> problem. However, one thing was curious. I didn't speak to Moft but the
> person who did said MS said NOT to turn hyperthreading on SQL clusters on!
> Now that shocked me. Does anyone know why? We turned it off to see whether
> it was reponsible for some performance issues and results were
inconclusive.
> So we were going to turn it back on. We were strongly advised not to do
so.
> So does anyone know why or could point me in the right direction? I've
never
> heard this mentioned before.
>
|||HP recommends to turn it off on XP if you have two processors. I don't have
time to lookup the article right now. With it on or off on XP, I did not see
a difference.
Cheers,
Rod
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> Not exactly.
> I have a cluster with 8-way Hyperthreaded hosts. SQL handles the 16
virtual
> processors just fine. I believe that Service Pack 2 contained the code to
> fix the virtual/physical processor count issue. Build 816 and 910 (post
SP3
> hotfixes) contain some code to improve performance on Hyperthreaded
systems.[vbcol=seagreen]
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%23PHgs2%23REHA.3344@.TK2MSFTNGP12.phx.gbl...
> max
> turn
already,[vbcol=seagreen]
> contacted
our[vbcol=seagreen]
on![vbcol=seagreen]
whether
> inconclusive.
> so.
> never
>
|||I agree with the XP recommendation. For a dedicated SQL server, I see a
significant difference between off and on. (Unisys ES7000 8x2.8GHz) Average
CPU 26% on, 40% off.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> HP recommends to turn it off on XP if you have two processors. I don't
have
> time to lookup the article right now. With it on or off on XP, I did not
see[vbcol=seagreen]
> a difference.
> Cheers,
> Rod
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> virtual
to[vbcol=seagreen]
> SP3
> systems.
the[vbcol=seagreen]
already,[vbcol=seagreen]
> already,
> our
the[vbcol=seagreen]
> on!
> whether
do
>
|||Sweet, I know I turn it on with only 2 procs, have not run into an 8 way
with it on or off. Great info, thanks!
Cheers,
Rod
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eQtgTU$REHA.3300@.TK2MSFTNGP09.phx.gbl...
> I agree with the XP recommendation. For a dedicated SQL server, I see a
> significant difference between off and on. (Unisys ES7000 8x2.8GHz)
Average[vbcol=seagreen]
> CPU 26% on, 40% off.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> have
> see
code[vbcol=seagreen]
> to
(post[vbcol=seagreen]
> the
> already,
fixed[vbcol=seagreen]
> the
clusters[vbcol=seagreen]
> do
I've
>
|||Hopefully your not trying to run a cluster on WinXP anyway<g>.
Andrew J. Kelly
SQL Server MVP
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> HP recommends to turn it off on XP if you have two processors. I don't
have
> time to lookup the article right now. With it on or off on XP, I did not
see[vbcol=seagreen]
> a difference.
> Cheers,
> Rod
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> virtual
to[vbcol=seagreen]
> SP3
> systems.
the[vbcol=seagreen]
already,[vbcol=seagreen]
> already,
> our
the[vbcol=seagreen]
> on!
> whether
do
>
|||Dave,
We put 910 into place less than a week ago when we found out why we had to have it: previously - 818 - SQL 2000 allows all logins access to the system before tempdb comes online. Well, we are a web-based company. We are running an active/active SQL cluste
r on a Win2K3 server ent ed cluster with HT enabled. So, I'm concerned to hear your comment about this option. Now, yesterday we see odd behavior on the system. Specifically the following error:
Transaction (Process ID 350) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction..
Sure, we've had deadlocks before but, not this type "communication buffer".
Another thing we've noticed is that if a process wants to go parallel, we've seen it go to 49 degrees of parallelism. You can imagine all of our CPUs are then spiking (2 servers x 8 CPUs each x HT), which in turn doesn't make the rest of the processes hap
py.
We only have SQL Server running on these servers. All web servers (over 40) can hit this cluster at any given time.
Would love to have a discussion with you. Microsoft told us that we are only the second company that reported needing the 910 patch. So, I'm expecting you are the other company. Are you web-based? Or, have lots of web server activity against your database
servers?
We've seen some other odd behavior as well. But, this e-mail is getting lengthy.
I see you are in the UK. We are in the USA.
Your reply is appreciated,
Doug
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||910 was built specifically for us after we reported a bug via Unisys.
Technically the build was for Unisys under their Premier agreement, but we
were the end customer.
I have also seen extra-high parallelism. I have limited the degree of
parallelism intentionally since I prefer higher response to the many
transactional queries rather than the few analysis-type queries.
Yes, we are very web-based. About 85-90% of our pages are .Net based. We
are the number one job board in the US (we recently passed Monster).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Doug Thomas" <dougt@.ecollege.com> wrote in message
news:uRIk$jTVEHA.3332@.tk2msftngp13.phx.gbl...
> Dave,
> We put 910 into place less than a week ago when we found out why we had to
have it: previously - 818 - SQL 2000 allows all logins access to the system
before tempdb comes online. Well, we are a web-based company. We are running
an active/active SQL cluster on a Win2K3 server ent ed cluster with HT
enabled. So, I'm concerned to hear your comment about this option. Now,
yesterday we see odd behavior on the system. Specifically the following
error:
> Transaction (Process ID 350) was deadlocked on lock | communication
buffer resources with another process and has been chosen as the deadlock
victim. Rerun the transaction..
> Sure, we've had deadlocks before but, not this type "communication
buffer".
> Another thing we've noticed is that if a process wants to go parallel,
we've seen it go to 49 degrees of parallelism. You can imagine all of our
CPUs are then spiking (2 servers x 8 CPUs each x HT), which in turn doesn't
make the rest of the processes happy.
> We only have SQL Server running on these servers. All web servers (over
40) can hit this cluster at any given time.
> Would love to have a discussion with you. Microsoft told us that we are
only the second company that reported needing the 910 patch. So, I'm
expecting you are the other company. Are you web-based? Or, have lots of web
server activity against your database servers?
> We've seen some other odd behavior as well. But, this e-mail is getting
lengthy.
> I see you are in the UK. We are in the USA.
> Your reply is appreciated,
> Doug
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

Clustering and Hyperthreading

Tuesday, February 14, 2012

Clustered index with a truncate table operation question.

I have a situation at here that appears once in a while:
The table in question has a single clustered index on ReferenceID. It's
populated by the following process: Truncatae table MyTable, run DTS to
populate the table.
This runs every xx minutes.
The table has roughly 50K records but the records are large.
The problem occurs during a query similar to the following:
select ...
from MyTable
LEFT JOIN (select .. from MyTable JOIN MyTable Seconds ON
MyTable.AssociatedID = seconds.MasterID) as Calculated on
Calculated.ReferenceID = MyTable.ReferenceID
At one point this query, which normally takes 30 seconds or so, went to over
16 minutes to complete consistently.
Eventually we ran
dbcc dbreindex(MyTable)
and that seems to have fixed it.
My question is why: We clear the table before repopulating, how did the
index get so jacked up?
Thanks,
EricYour problem is likely caused by internal or external Index Fragmentation
(based on your reporting that dbreindex() fixed the problem... This is cause
d
by having a clusterd index on a field (or COmbination od fields) for which
inserts are randomly dispersed. So, if your CLustered Index is on
ReferenceID, try to ensure that DTS sorts it's data by ReferenceID before it
runs... Then the pages in the clustered Index will be as full as possible,
and both internal and external fragmentation will be at a minimum. If you
can;t do this, then you need t orun dbcc reindex after every DTS run, or
choose a different Clustered Index that you can sort the DTS import data on.
.
"Eric" wrote:

> I have a situation at here that appears once in a while:
> The table in question has a single clustered index on ReferenceID. It's
> populated by the following process: Truncatae table MyTable, run DTS to
> populate the table.
> This runs every xx minutes.
> The table has roughly 50K records but the records are large.
> The problem occurs during a query similar to the following:
> select ...
> from MyTable
> LEFT JOIN (select .. from MyTable JOIN MyTable Seconds ON
> MyTable.AssociatedID = seconds.MasterID) as Calculated on
> Calculated.ReferenceID = MyTable.ReferenceID
>
> At one point this query, which normally takes 30 seconds or so, went to ov
er
> 16 minutes to complete consistently.
> Eventually we ran
> dbcc dbreindex(MyTable)
> and that seems to have fixed it.
> My question is why: We clear the table before repopulating, how did the
> index get so jacked up?
> Thanks,
> Eric
>
>

Wednesday, March 7, 2012

COALESCE in a WHERE clause

Sunday, February 19, 2012

Clustering and Hyperthreading

Clustering and Hyperthreading

Clustering and Hyperthreading

Tuesday, February 14, 2012

Clustered index with a truncate table operation question.

Clustered DB Server

Blog Archive

About Me