Clustered DB Server: performance

Showing posts with label performance. Show all posts

Wednesday, March 7, 2012

Code behaviour/performance on 2 machines

Hi,
I have UAT and production servers with same database schema. I am running a
stored procedure on both machines, Its taking much more time on UAT (31 hrs)
where as it is taking less time i.e. 8 hrs (which is expected because of
nature of query) Can someone please explain why is it taking more time on UA
T?
UAT SQL SERVER 2000 has SP4
Production SQL SERVER 2000 has SP3
Please help ASAP.
I can also be reached at sahil.arora@.cit.com
Thanks
SahilJust becuase the servers share the same database model, there is the issue
of differences in hardware configuration and even differences in the
physical implementation of the database such as: volume of data, placement
of files, index fragmentation, statistics, sever/database configurations,
etc. To start with, take a look at the Performance tab of Task Manager on
both servers and compare to what extent they are maxing out on memory and
CPU.
Compare the execution plan between the 2 servers:
http://msdn.microsoft.com/library/d... />
1_1pfd.asp
Also, the following article provides some good performance oriented check
lists and explains how to audit performance.
http://www.sql-server-performance.c...mance_audit.asp
"Sahil Arora" <Sahil Arora@.discussions.microsoft.com> wrote in message
news:40E34A48-D573-46EF-98BE-BA4A84A9A113@.microsoft.com...
> Hi,
> I have UAT and production servers with same database schema. I am running
> a
> stored procedure on both machines, Its taking much more time on UAT (31
> hrs)
> where as it is taking less time i.e. 8 hrs (which is expected because of
> nature of query) Can someone please explain why is it taking more time on
> UAT?
> UAT SQL SERVER 2000 has SP4
> Production SQL SERVER 2000 has SP3
> Please help ASAP.
> I can also be reached at sahil.arora@.cit.com
> Thanks
> Sahil|||I just checked everything on both servers, its same and the server on which
is taking more time is much more powerful than server with less time. any
comments?
"JT" wrote:

> Just becuase the servers share the same database model, there is the issue
> of differences in hardware configuration and even differences in the
> physical implementation of the database such as: volume of data, placement
> of files, index fragmentation, statistics, sever/database configurations,
> etc. To start with, take a look at the Performance tab of Task Manager on
> both servers and compare to what extent they are maxing out on memory and
> CPU.
> Compare the execution plan between the 2 servers:
> http://msdn.microsoft.com/library/d...>
n_1_1pfd.asp
> Also, the following article provides some good performance oriented check
> lists and explains how to audit performance.
> http://www.sql-server-performance.c...mance_audit.asp
>
> "Sahil Arora" <Sahil Arora@.discussions.microsoft.com> wrote in message
> news:40E34A48-D573-46EF-98BE-BA4A84A9A113@.microsoft.com...
>
>|||Do both servers have identical volumes of data, is the execution plan of the
query identical, is the OS level file fragmentation and database level index
fragmentation optimized or similar on both servers, etc.. It could be one or
all of a hundred things. Perhaps running a performance audit log on both
servers and comparing the results will reveal something.
INF: Job to Monitor SQL Server 2000 Performance and Activity
http://support.microsoft.com/defaul...kb;en-us;283696
"Sahil Arora" <SahilArora@.discussions.microsoft.com> wrote in message
news:2FE8B79F-9EC2-445A-9648-65F1C7CAABAD@.microsoft.com...
>I just checked everything on both servers, its same and the server on which
> is taking more time is much more powerful than server with less time. any
> comments?
> "JT" wrote:
>

Coalesce increasing performance but Why?

This past w

end I tried out a tool called Toad for Sql and it had a query
optimizer in it. I tossed in my slow performing query, walked away and 2
hours later had 50+ alternative queries.
One of the results had a 99.83% improvement. Logical Reads went from
62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
now took 35 seconds.
There were 5 left joins in the query. It simple replaced two of them (see
below), everything else stayed the same. im very happy with the results but
Id like to understand why COALESCE would make the query run almost 100%
faster.
LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
ac.boardID)
and
LEFT OUTER JOIN question_std_answer qsa
ON (qsa.cobrandID=ac.cobrandID
AND qsa.masterID=ac.masterID
AND qsa.accountID=ac.accountID
AND qsa.positionID=ac.positionID
AND qsa.jsrUserID=ac.jsrUserID
AND qsa.questionID=10)
with
LEFT OUTER JOIN question_std_answer qsa
ON qsa.cobrandID = ac.cobrandID
AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
AND qsa.accountID = ac.accountID
AND qsa.positionID = ac.positionID
AND qsa.jsrUserID = ac.jsrUserID
AND qsa.questionID = 10I'm not sure but I am guessing that the use of coalesce negated the indexes
that were previously being used, allowing different indexes to be used.
Have you tried recreating statistics and comparing the two queries again?
It sounds like SQL server is making a mistake in its chosen plan, but using
this function is removing an option and making it default to a muich better
plan.
"Brian" <brian@.nospam.com> wrote in message
news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> This past w

end I tried out a tool called Toad for Sql and it had a
query
> optimizer in it. I tossed in my slow performing query, walked away and 2
> hours later had 50+ alternative queries.
> One of the results had a 99.83% improvement. Logical Reads went from
> 62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
> now took 35 seconds.
> There were 5 left joins in the query. It simple replaced two of them (see
> below), everything else stayed the same. im very happy with the results
but
> Id like to understand why COALESCE would make the query run almost 100%
> faster.
> LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
> LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
> ac.boardID)
> and
> LEFT OUTER JOIN question_std_answer qsa
> ON (qsa.cobrandID=ac.cobrandID
> AND qsa.masterID=ac.masterID
> AND qsa.accountID=ac.accountID
> AND qsa.positionID=ac.positionID
> AND qsa.jsrUserID=ac.jsrUserID
> AND qsa.questionID=10)
> with
> LEFT OUTER JOIN question_std_answer qsa
> ON qsa.cobrandID = ac.cobrandID
> AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
> AND qsa.accountID = ac.accountID
> AND qsa.positionID = ac.positionID
> AND qsa.jsrUserID = ac.jsrUserID
> AND qsa.questionID = 10
>|||I ran many times testing it against different date ranges always starting wi
th:
CHECKPOINT -- write dirty pages from data cache to disk
DBCC DROPCLEANBUFFERS -- clear the data cache
DBCC FREEPROCCACHE -- clear the procedure cache
Everytime is performed essentially the same give or take a second or two.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message news:etU6gJ7OGHA.916@.T
K2MSFTNGP10.phx.gbl...
> I'm not sure but I am guessing that the use of coalesce negated the indexe
s
> that were previously being used, allowing different indexes to be used.
> Have you tried recreating statistics and comparing the two queries again?
> It sounds like SQL server is making a mistake in its chosen plan, but usin
g
> this function is removing an option and making it default to a muich bette
r
> plan.
>
> "Brian" <brian@.nospam.com> wrote in message
> news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> query
> but
>
>|||What does the Execution Plan tell you?
http://msdn.microsoft.com/library/d... />
1_5pde.asp
"Brian" <brian@.nospam.com> wrote in message
news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> This past w

end I tried out a tool called Toad for Sql and it had a
> query optimizer in it. I tossed in my slow performing query, walked away
> and 2 hours later had 50+ alternative queries.
> One of the results had a 99.83% improvement. Logical Reads went from
> 62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
> now took 35 seconds.
> There were 5 left joins in the query. It simple replaced two of them (see
> below), everything else stayed the same. im very happy with the results
> but Id like to understand why COALESCE would make the query run almost
> 100% faster.
> LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
> LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
> ac.boardID)
> and
> LEFT OUTER JOIN question_std_answer qsa
> ON (qsa.cobrandID=ac.cobrandID
> AND qsa.masterID=ac.masterID
> AND qsa.accountID=ac.accountID
> AND qsa.positionID=ac.positionID
> AND qsa.jsrUserID=ac.jsrUserID
> AND qsa.questionID=10)
> with
> LEFT OUTER JOIN question_std_answer qsa
> ON qsa.cobrandID = ac.cobrandID
> AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
> AND qsa.accountID = ac.accountID
> AND qsa.positionID = ac.positionID
> AND qsa.jsrUserID = ac.jsrUserID
> AND qsa.questionID = 10
>|||Thanks for replying JT ... Im by no means a DBA .. what should I focus on
when looking at the Execution Plan?
Thanks!
"JT" <someone@.microsoft.com> wrote in message
news:%23eMsok8OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> What does the Execution Plan tell you?
> http://msdn.microsoft.com/library/d...>
n_1_5pde.asp
> "Brian" <brian@.nospam.com> wrote in message
> news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||SQL Server does not execute SQL. It interprets SQL, compiles an execution
plan, and then uses that plan to perform a sequence of physical operations
such as index scans, joins, spooling, etc. You can deterministically compare
the design merits of one query versus another similar query by studying
their execution plan. For example, does one query perform a full table scan
while an alternative query perform a more efficient index scan?
Below are a few articles describing in more detail how to gain useful
information from execution plans:
SQL Tuning Tutorial - Understanding a Database Execution Plan (1)
http://www.codeproject.com/cs/datab...-tutorial-1.asp
How to Select Indexes for Your SQL Server Tables
http://www.sql-server-performance.com/mr_indexing.asp
SQL Server Query Execution Plan Analysis
http://www.sql-server-performance.c...an_analysis.asp
"Brian" <brian@.nospam.com> wrote in message
news:eZz$s18OGHA.3064@.TK2MSFTNGP10.phx.gbl...
> Thanks for replying JT ... Im by no means a DBA .. what should I focus on
> when looking at the Execution Plan?
> Thanks!
> "JT" <someone@.microsoft.com> wrote in message
> news:%23eMsok8OGHA.2124@.TK2MSFTNGP14.phx.gbl...
>|||I would guess that this is based on the use of
coalesce to force the use of an index.This is
simply a *trick* that was put forward by
'Umachandar Jayachandran' then an MVP.The idea is
that an index can be forced for any data type
by using the datatype boundries.Although a boundry
is not being used here the construct is probably
enough to force the use of an index.
I don't think this trick is talked about in a kb
for obvious reasons:)
I refer the interested reader to these threads:
http://tinyurl.com/rpa2x
http://tinyurl.com/n2oql
http://tinyurl.com/ns9aq
http://tinyurl.com/n5v4y
$.02 from
www.rac4sql.net|||No, this is a different trick. This trick will have the result that the
optimizer will not consider certain (underperforming) access paths. The
trick that Umachandar introduced is not used for joins, but for
filtering expressions (i.e. the WHERE clause).
Gert-Jan
05ponyGT wrote:
> I would guess that this is based on the use of
> coalesce to force the use of an index.This is
> simply a *trick* that was put forward by
> 'Umachandar Jayachandran' then an MVP.The idea is
> that an index can be forced for any data type
> by using the datatype boundries.Although a boundry
> is not being used here the construct is probably
> enough to force the use of an index.
> I don't think this trick is talked about in a kb
> for obvious reasons:)
> I refer the interested reader to these threads:
> http://tinyurl.com/rpa2x
> http://tinyurl.com/n2oql
> http://tinyurl.com/ns9aq
> http://tinyurl.com/n5v4y
> $.02 from
> www.rac4sql.net|||This comes as a shock as I'm so rarely wrong:(
Wonder why we use the term *trick* and not *kludge* :)
Anyway this is just another illustration of turning *what* not *how*
on its head.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:440499B7.72531652@.toomuchspamalready.nl...
> No, this is a different trick. This trick will have the result that the
> optimizer will not consider certain (underperforming) access paths. The
> trick that Umachandar introduced is not used for joins, but for
> filtering expressions (i.e. the WHERE clause).
> Gert-Jan
>
> 05ponyGT wrote:

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

COALESCE as a Performance Enhancer?!?

I am using a clever product called SQL Expert Pro to optimize
problematic SQL statements. It works by generating syntactically
identical variations of the original SQL, finding all unique execution
plans, and batch testing them.

Anyway, it generally turns out that a very odd change dramatically
improves performance (for a PeopleSoft database on SQL Server 7). Run
time goes from 52 seconds to 8 seconds.

The change is to replace a join condition in a where clause with an
odd equivalent COALESCE construct, e.g. replace WHERE C.PAY_ID =
D.PAY_ID with WHERE C.PAY_ID = COALESCE(D.PAY_ID, D.PAY_ID).

Has anyone seen this sort of behavior before? Why would it be
advantageous to COALESCE on the same field twice?

The original and COALESCE'd versions are shown below. Does the fact
that this is a nine-table join over large tables have anything to do
with it?

Original Query (52 sec)

SELECT F.ACCT_ID,
I.ENTITY_NAME,
A.TNDR_SOURCE_CD,
C.PAY_EVENT_ID,
C.NON_CIS_NAME,
C.NON_CIS_REF_NBR,
C.NON_CIS_COMMENT,
C.PAY_AMT,
D.PAY_SEG_AMT,
E.ACCOUNTING_DT,
':1',
':2',
':3',
E.FREEZE_OPRID,
E.FREEZE_DTTM,
E.FT_TYPE_FLG
FROM PS_CI_TNDR_CTL A,
PS_CI_PAY_TNDR B,
PS_CI_PAY C,
PS_CI_PAY_SEG D,
PS_CI_SA F,
PS_CI_SA_TYPE G,
PS_CI_ACCT_PER H,
PS_CI_PER_NAME I,
PS_CW_FT E
WHERE A.TNDR_CTL_ID = B.TNDR_CTL_ID
AND A.TNDR_SOURCE_CD LIKE 'STK%'
AND B.PAY_EVENT_ID = C.PAY_EVENT_ID
AND C.PAY_ID = D.PAY_ID
AND D.PAY_SEG_ID = E.SIBLING_ID
AND E.ACCOUNTING_DT BETWEEN '2003-10-01' AND '2003-10-31'
AND E.FT_TYPE_FLG IN ('PS', 'PX')
AND NOT EXISTS (SELECT 'X'
FROM PS_CW_INTERFACE_ID J
WHERE J.PAYOR_ACCT_ID = F.ACCT_ID)
AND E.SA_ID = F.SA_ID
AND F.SA_TYPE_CD = G.SA_TYPE_CD
AND G.DEBT_CL_CD = 'NCIS'
AND F.ACCT_ID = H.ACCT_ID
AND H.PER_ID = I.PER_ID
ORDER BY 2

Optimized Query (8 sec)

SELECT F.ACCT_ID,
I.ENTITY_NAME,
A.TNDR_SOURCE_CD,
C.PAY_EVENT_ID,
C.NON_CIS_NAME,
C.NON_CIS_REF_NBR,
C.NON_CIS_COMMENT,
C.PAY_AMT,
D.PAY_SEG_AMT,
E.ACCOUNTING_DT,
':1',
':2',
':3',
E.FREEZE_OPRID,
E.FREEZE_DTTM,
E.FT_TYPE_FLG
FROM PS_CI_TNDR_CTL A,
PS_CI_PAY_TNDR B,
PS_CI_PAY C,
PS_CI_PAY_SEG D,
PS_CI_SA F,
PS_CI_SA_TYPE G,
PS_CI_ACCT_PER H,
PS_CI_PER_NAME I,
PS_CW_FT E
WHERE A.TNDR_CTL_ID = COALESCE(B.TNDR_CTL_ID,B.TNDR_CTL_ID)
AND A.TNDR_SOURCE_CD LIKE 'STK%'
AND B.PAY_EVENT_ID = COALESCE(C.PAY_EVENT_ID, C.PAY_EVENT_ID)
AND C.PAY_ID = COALESCE(D.PAY_ID, D.PAY_ID)
AND D.PAY_SEG_ID = COALESCE(E.SIBLING_ID, E.SIBLING_ID)
AND COALESCE(E.ACCOUNTING_DT, E.ACCOUNTING_DT) BETWEEN '2003-10-01'
AND '2003-10-31'
AND COALESCE(E.FT_TYPE_FLG, E.FT_TYPE_FLG) IN ('PS', 'PX')
AND NOT EXISTS (SELECT 'X'
FROM PS_CW_INTERFACE_ID J
WHERE COALESCE(J.PAYOR_ACCT_ID, J.PAYOR_ACCT_ID) =
F.ACCT_ID)
AND E.SA_ID = COALESCE(F.SA_ID,F.SA_ID)
AND F.SA_TYPE_CD = COALESCE(G.SA_TYPE_CD,G.SA_TYPE_CD)
AND G.DEBT_CL_CD = 'NCIS'
AND F.ACCT_ID = COALESCE(H.ACCT_ID ,H.ACCT_ID)
AND H.PER_ID = COALESCE(I.PER_ID ,I.PER_ID)
ORDER BY 2Check out the query plan for both queries, and you are most likely to
see the difference.

I suppose the COALESCE(JoinColumn,JoinColumn) will be interpreted as a
non-optimizable expression. Because of this, the access path analysis
and join strategy will change. My guess is that it will change in favor
of loop joins. Also, the compilation time is most likely to drop
dramatically.

Gert-Jan

Jeff Roughgarden wrote:
> I am using a clever product called SQL Expert Pro to optimize
> problematic SQL statements. It works by generating syntactically
> identical variations of the original SQL, finding all unique execution
> plans, and batch testing them.
> Anyway, it generally turns out that a very odd change dramatically
> improves performance (for a PeopleSoft database on SQL Server 7). Run
> time goes from 52 seconds to 8 seconds.
> The change is to replace a join condition in a where clause with an
> odd equivalent COALESCE construct, e.g. replace WHERE C.PAY_ID =
> D.PAY_ID with WHERE C.PAY_ID = COALESCE(D.PAY_ID, D.PAY_ID).
> Has anyone seen this sort of behavior before? Why would it be
> advantageous to COALESCE on the same field twice?
> The original and COALESCE'd versions are shown below. Does the fact
> that this is a nine-table join over large tables have anything to do
> with it?
> Original Query (52 sec)
> SELECT F.ACCT_ID,
> I.ENTITY_NAME,
> A.TNDR_SOURCE_CD,
> C.PAY_EVENT_ID,
> C.NON_CIS_NAME,
> C.NON_CIS_REF_NBR,
> C.NON_CIS_COMMENT,
> C.PAY_AMT,
> D.PAY_SEG_AMT,
> E.ACCOUNTING_DT,
> ':1',
> ':2',
> ':3',
> E.FREEZE_OPRID,
> E.FREEZE_DTTM,
> E.FT_TYPE_FLG
> FROM PS_CI_TNDR_CTL A,
> PS_CI_PAY_TNDR B,
> PS_CI_PAY C,
> PS_CI_PAY_SEG D,
> PS_CI_SA F,
> PS_CI_SA_TYPE G,
> PS_CI_ACCT_PER H,
> PS_CI_PER_NAME I,
> PS_CW_FT E
> WHERE A.TNDR_CTL_ID = B.TNDR_CTL_ID
> AND A.TNDR_SOURCE_CD LIKE 'STK%'
> AND B.PAY_EVENT_ID = C.PAY_EVENT_ID
> AND C.PAY_ID = D.PAY_ID
> AND D.PAY_SEG_ID = E.SIBLING_ID
> AND E.ACCOUNTING_DT BETWEEN '2003-10-01' AND '2003-10-31'
> AND E.FT_TYPE_FLG IN ('PS', 'PX')
> AND NOT EXISTS (SELECT 'X'
> FROM PS_CW_INTERFACE_ID J
> WHERE J.PAYOR_ACCT_ID = F.ACCT_ID)
> AND E.SA_ID = F.SA_ID
> AND F.SA_TYPE_CD = G.SA_TYPE_CD
> AND G.DEBT_CL_CD = 'NCIS'
> AND F.ACCT_ID = H.ACCT_ID
> AND H.PER_ID = I.PER_ID
> ORDER BY 2
> Optimized Query (8 sec)
> SELECT F.ACCT_ID,
> I.ENTITY_NAME,
> A.TNDR_SOURCE_CD,
> C.PAY_EVENT_ID,
> C.NON_CIS_NAME,
> C.NON_CIS_REF_NBR,
> C.NON_CIS_COMMENT,
> C.PAY_AMT,
> D.PAY_SEG_AMT,
> E.ACCOUNTING_DT,
> ':1',
> ':2',
> ':3',
> E.FREEZE_OPRID,
> E.FREEZE_DTTM,
> E.FT_TYPE_FLG
> FROM PS_CI_TNDR_CTL A,
> PS_CI_PAY_TNDR B,
> PS_CI_PAY C,
> PS_CI_PAY_SEG D,
> PS_CI_SA F,
> PS_CI_SA_TYPE G,
> PS_CI_ACCT_PER H,
> PS_CI_PER_NAME I,
> PS_CW_FT E
> WHERE A.TNDR_CTL_ID = COALESCE(B.TNDR_CTL_ID,B.TNDR_CTL_ID)
> AND A.TNDR_SOURCE_CD LIKE 'STK%'
> AND B.PAY_EVENT_ID = COALESCE(C.PAY_EVENT_ID, C.PAY_EVENT_ID)
> AND C.PAY_ID = COALESCE(D.PAY_ID, D.PAY_ID)
> AND D.PAY_SEG_ID = COALESCE(E.SIBLING_ID, E.SIBLING_ID)
> AND COALESCE(E.ACCOUNTING_DT, E.ACCOUNTING_DT) BETWEEN '2003-10-01'
> AND '2003-10-31'
> AND COALESCE(E.FT_TYPE_FLG, E.FT_TYPE_FLG) IN ('PS', 'PX')
> AND NOT EXISTS (SELECT 'X'
> FROM PS_CW_INTERFACE_ID J
> WHERE COALESCE(J.PAYOR_ACCT_ID, J.PAYOR_ACCT_ID) =
> F.ACCT_ID)
> AND E.SA_ID = COALESCE(F.SA_ID,F.SA_ID)
> AND F.SA_TYPE_CD = COALESCE(G.SA_TYPE_CD,G.SA_TYPE_CD)
> AND G.DEBT_CL_CD = 'NCIS'
> AND F.ACCT_ID = COALESCE(H.ACCT_ID ,H.ACCT_ID)
> AND H.PER_ID = COALESCE(I.PER_ID ,I.PER_ID)
> ORDER BY 2|||Hi Jeff,

That's pretty bizarre. I can only guess that the old style JOIN
creates a cartesian product with a lot of nulls everywhere, before the
WHERE filters the rows. And that coalesce somehow handles the nulls
better?

Friday, February 24, 2012

Clustering, Security, Performance, Load Balance

Perhaps this group can help me fill in ms-sqlserver related following
questions.

1. Do this database have data Clustering capabilities?
1a. If yes, what mechanism is used such as shared disk, share nothing,
etc.
2. Do these dB have Security features?
2a. If yes, what security features are supported? For instance do they
support encryption or SSL connection?
3. How does the database perform and what is the criteria for the
performance matrix?
4. Do they have inbuilt load balance capabilities?

I want to thank everyone for taking your time to read this
correspondence. I will also greatly appreciate your efforts in sharing
your thoughts.

Regards,
ManishManish (marora@.gmail.com) writes:

Quote:

Originally Posted by

I think this question has been asked number of times. However, I am
looking for some specific information. Perhaps some of you can help
close the gap. Or perhaps you can point me towards right direction.
>
Perhaps this group can help me fill in ms-sqlserver related following
questions.
>
1. Do this database have data Clustering capabilities?
1a. If yes, what mechanism is used such as shared disk, share nothing,
etc.

What sort of clustering do you have in mind? Clustering in MS SQL
Server is all about high availablility. That is two or more machines
that share disk. If one machine dies, another in the machine in the
cluster can take over very quickly.

I know that in other products, clustering is about scalinng out, but
MS SQL Server does not offering anything like that.

Quote:

Originally Posted by

2. Do these dB have Security features?

Yes, there are security features in MS SQL Server. :-)

Quote:

Originally Posted by

2a. If yes, what security features are supported? For instance do they
support encryption or SSL connection?

You can encrypt data in SQL 2005, and you can also use SSL for
encrypting the connection.

Quote:

Originally Posted by

3. How does the database perform and what is the criteria for the
performance matrix?
4. Do they have inbuilt load balance capabilities?

I'm not sure that I understand these questions. But if they were asked
with a scale-out solution like Oracle's RAC in mind, they are not
applicable to SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Nov 27, 5:38 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Manish (mar...@.gmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

Perhaps this group can help me fill in ms-sqlserver related following
questions.

Quote:

Originally Posted by

1. Do this database have data Clustering capabilities?
1a. If yes, what mechanism is used such as shared disk, share nothing,
etc.

>
What sort of clustering do you have in mind? Clustering in MS SQL
Server is all about high availablility. That is two or more machines
that share disk. If one machine dies, another in the machine in the
cluster can take over very quickly.
>
I know that in other products, clustering is about scalinng out, but
MS SQL Server does not offering anything like that.
>

Quote:

Originally Posted by

2. Do these dB have Security features?

>
Yes, there are security features in MS SQL Server. :-)
>

Quote:

Originally Posted by

2a. If yes, what security features are supported? For instance do they
support encryption or SSL connection?

>
You can encrypt data in SQL 2005, and you can also use SSL for
encrypting the connection.
>

Quote:

Originally Posted by

3. How does the database perform and what is the criteria for the
performance matrix?
4. Do they have inbuilt load balance capabilities?

>
I'm not sure that I understand these questions. But if they were asked
with a scale-out solution like Oracle's RAC in mind, they are not
applicable to SQL Server.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

How does the database perform and what is the criteria for the
performance matrix? To clarify, how will one collect performance data
related to MS-SQLserver? What is the criteria for collecting such
data?|||Manish (marora@.gmail.com) writes:

Quote:

Originally Posted by

How does the database perform and what is the criteria for the
performance matrix? To clarify, how will one collect performance data
related to MS-SQLserver?

You can use performance counters, Profiler, query dynamic management views.

Quote:

Originally Posted by

What is the criteria for collecting such data?

I'm afraid that I can't answer why you would like to collect some data.
Or I am not understanding your question.

Sunday, February 19, 2012

Clustering for performance

Hi,
As far as I know, clustering is used to increase availability of SQL Server.
Is it possible to benefit from clustering for performance (load balancing
or...)?
Thanks in advance,
Leila
No, SQL Server Clustering does not load balance a database across servers.
An instance of SQL will only run on any one machine at any time.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Leila" <Leilas@.hotpop.com> wrote in message
news:OuYWVHYzEHA.1192@.tk2msftngp13.phx.gbl...
> Hi,
> As far as I know, clustering is used to increase availability of SQL
> Server.
> Is it possible to benefit from clustering for performance (load balancing
> or...)?
> Thanks in advance,
> Leila
>

Clustering for Performance

Hello All. I need to set up quite a large SQL System 2005 system that needs
to deal with 40,000 concurrent users, and one of the tables will contain blob
data. 40,000 users throwing around Mb's of data at the same time worries me a
little bit!
My question is to do with Database Clustering and Mirroring. From what I can
see, there is still no load-balancing with SQL Server 2005, so does this mean
even in a clustered environment I am still basically only using a single
database server? I have seen many posts that tell me that clustering is ONLY
for failover and not for performance. I understand that with Active/Passive
this is the case, but how about Active/Active? If I can set up Active/Active
(2 nodes? 4 nodes? 8 nodes? how many are possible?) with a SAN and NOT have
failover implemented (can I turn failover off?) then would I have a load
balanced environment? I could then have all nodes running up to 100% (because
I don't have to worry about the failover) and therefore give me a dramatic
increase in performance compared with using a single server?
If I can do this then I can set up 2 identical clusters and an extra server
for the witness, and use database mirroring for failover? Of course I
understand that mirroring will decrease performance on the clusters. But
would this give me a super-fast database system that might cope with what I
need?
Also, I am thinking about taking the blob data of of the database and create
a new database that just deals with the blobs. How would this affect my
clustered/mirrored environment?
Thanks
Richard
Active/Active is a holdover from a specific technical implementation of
clustering for SQL 7.0. It was not scale-out either. The correct current
term is multi-instance. As you have read, SQL clustering is a failover and
availability technology only. SQL clustering does not load balance. SQL
does not have a native, automatic load balancing technology. There are some
third party virtualization technologies, but like any scale-out technology,
adding nodes does not equate to linear performance gains, and there are many
situations where such solutions do not work.
As with clustering, Database Mirroring is a failover technology and actually
has negative impacts on performance. There is one area where it can be used
to scale out, but that only works for read-only queries.
Segregating the blob data is not a bad idea, provided you do it down to the
physical disk level.
To summarize, there are no shortcuts to scalability, just like you can't
shortcut availability. Having said that, it sounds like you need a
reasonably large SQL server. That is not an impossible proposition. SQL
Server scales up very well. There are several vendors that can sell you an
adequate system. Some will even help you size it. You might think about
hiring an experienced SQL consultant who specializes in large-scale SQL
Systems to guide you through the process. (No, I am not trying to drum up
business, my time is fully booked).
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:A115E5ED-8801-4142-B921-FD25665FE3C0@.microsoft.com...
> Hello All. I need to set up quite a large SQL System 2005 system that
> needs
> to deal with 40,000 concurrent users, and one of the tables will contain
> blob
> data. 40,000 users throwing around Mb's of data at the same time worries
> me a
> little bit!
> My question is to do with Database Clustering and Mirroring. From what I
> can
> see, there is still no load-balancing with SQL Server 2005, so does this
> mean
> even in a clustered environment I am still basically only using a single
> database server? I have seen many posts that tell me that clustering is
> ONLY
> for failover and not for performance. I understand that with
> Active/Passive
> this is the case, but how about Active/Active? If I can set up
> Active/Active
> (2 nodes? 4 nodes? 8 nodes? how many are possible?) with a SAN and NOT
> have
> failover implemented (can I turn failover off?) then would I have a load
> balanced environment? I could then have all nodes running up to 100%
> (because
> I don't have to worry about the failover) and therefore give me a dramatic
> increase in performance compared with using a single server?
> If I can do this then I can set up 2 identical clusters and an extra
> server
> for the witness, and use database mirroring for failover? Of course I
> understand that mirroring will decrease performance on the clusters. But
> would this give me a super-fast database system that might cope with what
> I
> need?
> Also, I am thinking about taking the blob data of of the database and
> create
> a new database that just deals with the blobs. How would this affect my
> clustered/mirrored environment?
> Thanks
> Richard
|||Richard,
Try not to take this the wrong way but if you are asking questions like
these and need to implement a system that large you are probably a bit over
your head. I suggest you seriously consider hiring a consultant who has
been thru things like this before. Quite honestly there aren't many people
who have dealt with systems that large. And if not done correctly it will
almost certainly turn belly up and die when you even get close to that many
users. That said here are a few comments. One is I doubt you will really
have 40K concurrent users especially since SQL Server only allows 32,767<g>.
Even with heavy use web based apps you rarely have as many concurrent
connections as you would think. And if you are talking anywhere near this
amount you are talking some serious hardware to support it. As I mentioned
in another post Clustering is not a load balancing option. Only one node at
a time has control over a specific disk resource in the cluster. So even
with Active / Active (or more correctly Multi-Instance) you can't share a
database since it resides on only one disk resource. You can't design a
system like this in a newsgroup and if you try you will fail. It requires a
very careful and well laid out plan to implement a large scale database
application.
Andrew J. Kelly SQL MVP
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:A115E5ED-8801-4142-B921-FD25665FE3C0@.microsoft.com...
> Hello All. I need to set up quite a large SQL System 2005 system that
> needs
> to deal with 40,000 concurrent users, and one of the tables will contain
> blob
> data. 40,000 users throwing around Mb's of data at the same time worries
> me a
> little bit!
> My question is to do with Database Clustering and Mirroring. From what I
> can
> see, there is still no load-balancing with SQL Server 2005, so does this
> mean
> even in a clustered environment I am still basically only using a single
> database server? I have seen many posts that tell me that clustering is
> ONLY
> for failover and not for performance. I understand that with
> Active/Passive
> this is the case, but how about Active/Active? If I can set up
> Active/Active
> (2 nodes? 4 nodes? 8 nodes? how many are possible?) with a SAN and NOT
> have
> failover implemented (can I turn failover off?) then would I have a load
> balanced environment? I could then have all nodes running up to 100%
> (because
> I don't have to worry about the failover) and therefore give me a dramatic
> increase in performance compared with using a single server?
> If I can do this then I can set up 2 identical clusters and an extra
> server
> for the witness, and use database mirroring for failover? Of course I
> understand that mirroring will decrease performance on the clusters. But
> would this give me a super-fast database system that might cope with what
> I
> need?
> Also, I am thinking about taking the blob data of of the database and
> create
> a new database that just deals with the blobs. How would this affect my
> clustered/mirrored environment?
> Thanks
> Richard
|||Wait, who said you were an experienced SQL consultant who specializes in
large-scale SQL
Systems? Oh yeah, I have and firmly believe it - DOH!
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:e9MrWhrFGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Active/Active is a holdover from a specific technical implementation of
> clustering for SQL 7.0. It was not scale-out either. The correct current
> term is multi-instance. As you have read, SQL clustering is a failover
> and availability technology only. SQL clustering does not load balance.
> SQL does not have a native, automatic load balancing technology. There
> are some third party virtualization technologies, but like any scale-out
> technology, adding nodes does not equate to linear performance gains, and
> there are many situations where such solutions do not work.
> As with clustering, Database Mirroring is a failover technology and
> actually has negative impacts on performance. There is one area where it
> can be used to scale out, but that only works for read-only queries.
> Segregating the blob data is not a bad idea, provided you do it down to
> the physical disk level.
> To summarize, there are no shortcuts to scalability, just like you can't
> shortcut availability. Having said that, it sounds like you need a
> reasonably large SQL server. That is not an impossible proposition. SQL
> Server scales up very well. There are several vendors that can sell you
> an adequate system. Some will even help you size it. You might think
> about hiring an experienced SQL consultant who specializes in large-scale
> SQL Systems to guide you through the process. (No, I am not trying to
> drum up business, my time is fully booked).
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> news:A115E5ED-8801-4142-B921-FD25665FE3C0@.microsoft.com...
>
|||Thank you Geoff and Andrew for you swift responses.
We are actually looking for a consultant to come in and help with this.
However I am just doing some ground work beforehand.
I was hoping that SQL Server 2005 would give us some performance
improvements (such as true Load Balancing like Oracle Real Application
Clusters), but the improvements over 2000 seem to be mostly for failover
rather than scaling.
Thanks again for your input
Richard
"Andrew J. Kelly" wrote:

> Richard,
> Try not to take this the wrong way but if you are asking questions like
> these and need to implement a system that large you are probably a bit over
> your head. I suggest you seriously consider hiring a consultant who has
> been thru things like this before. Quite honestly there aren't many people
> who have dealt with systems that large. And if not done correctly it will
> almost certainly turn belly up and die when you even get close to that many
> users. That said here are a few comments. One is I doubt you will really
> have 40K concurrent users especially since SQL Server only allows 32,767<g>.
> Even with heavy use web based apps you rarely have as many concurrent
> connections as you would think. And if you are talking anywhere near this
> amount you are talking some serious hardware to support it. As I mentioned
> in another post Clustering is not a load balancing option. Only one node at
> a time has control over a specific disk resource in the cluster. So even
> with Active / Active (or more correctly Multi-Instance) you can't share a
> database since it resides on only one disk resource. You can't design a
> system like this in a newsgroup and if you try you will fail. It requires a
> very careful and well laid out plan to implement a large scale database
> application.
> --
> Andrew J. Kelly SQL MVP
>
> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> news:A115E5ED-8801-4142-B921-FD25665FE3C0@.microsoft.com...
>
>
|||SQL 2005 has lots of performance enhancements among other things. Just
because it does not work like RAC does not mean it can not scale or handle
large workloads. I just finished working on a system that was doing over 25K
batch requests per second with upwards of 1000 concurrent (active and real
connections) and it was hardly breaking a sweat. Please don't make any
decisions based on appearance or misconceptions.
Andrew J. Kelly SQL MVP
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:427BB31F-57CA-43A9-965C-E5F18196D696@.microsoft.com...[vbcol=seagreen]
> Thank you Geoff and Andrew for you swift responses.
> We are actually looking for a consultant to come in and help with this.
> However I am just doing some ground work beforehand.
> I was hoping that SQL Server 2005 would give us some performance
> improvements (such as true Load Balancing like Oracle Real Application
> Clusters), but the improvements over 2000 seem to be mostly for failover
> rather than scaling.
> Thanks again for your input
> Richard
> "Andrew J. Kelly" wrote:
|||I second what Andrew said.
Scale-out computing only works for certain workload profiles. SQL will
scale up to almost any real-world database problem. I would start a project
like this by determining the service level requirements such as expected
workload, availability requirements, budget, in-house skills, etc. Then I
would build a solution based on the actual business requirements. After
all, your company could care less how you build the system as long as it
meets its service requirements.
I understand the trepidation you face when designing such a large-scale
system. There is nothing worse than a brand new database system that is
just a little bit too small or slow. Given the likely cost of your system,
getting a good consultant on board early before some irrevocable decisions
are made will save money, time, and frustration.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23GTouptFGHA.752@.TK2MSFTNGP12.phx.gbl...
> SQL 2005 has lots of performance enhancements among other things. Just
> because it does not work like RAC does not mean it can not scale or handle
> large workloads. I just finished working on a system that was doing over
> 25K batch requests per second with upwards of 1000 concurrent (active and
> real connections) and it was hardly breaking a sweat. Please don't make
> any decisions based on appearance or misconceptions.
> --
> Andrew J. Kelly SQL MVP
>
> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> news:427BB31F-57CA-43A9-965C-E5F18196D696@.microsoft.com...
>
|||Now you know why I hate hearing active/active and active/passive with
respect to SQL Server.
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%23PTVCQsFGHA.216@.TK2MSFTNGP15.phx.gbl...
> Wait, who said you were an experienced SQL consultant who specializes in
> large-scale SQL
> Systems? Oh yeah, I have and firmly believe it - DOH!
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:e9MrWhrFGHA.3936@.TK2MSFTNGP12.phx.gbl...
>
|||Lots of questions and LOTS of things to consider. No, a cluster doesn't
give you performance. You have a single database, no copies anywhere.
Now, if the data your users are reading is truely static or at least static
within a given timeframe AND you have a SAN, you can get increased capacity
by using the Scalable Shared Database feature keeping in mind that all reads
are going to the same set of disk devices.
If the data isn't static, but you need increased read capacity, you can
leverage the replication engine which can provide not only a readable copy
of your database, but it can also be used for failover.
40,000 concurrent users is doable, even on small systems. It really depends
upon what the data volume looks like, how the network is setup to handle the
traffic, and how the code is written that will run against it.
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:A115E5ED-8801-4142-B921-FD25665FE3C0@.microsoft.com...
> Hello All. I need to set up quite a large SQL System 2005 system that
> needs
> to deal with 40,000 concurrent users, and one of the tables will contain
> blob
> data. 40,000 users throwing around Mb's of data at the same time worries
> me a
> little bit!
> My question is to do with Database Clustering and Mirroring. From what I
> can
> see, there is still no load-balancing with SQL Server 2005, so does this
> mean
> even in a clustered environment I am still basically only using a single
> database server? I have seen many posts that tell me that clustering is
> ONLY
> for failover and not for performance. I understand that with
> Active/Passive
> this is the case, but how about Active/Active? If I can set up
> Active/Active
> (2 nodes? 4 nodes? 8 nodes? how many are possible?) with a SAN and NOT
> have
> failover implemented (can I turn failover off?) then would I have a load
> balanced environment? I could then have all nodes running up to 100%
> (because
> I don't have to worry about the failover) and therefore give me a dramatic
> increase in performance compared with using a single server?
> If I can do this then I can set up 2 identical clusters and an extra
> server
> for the witness, and use database mirroring for failover? Of course I
> understand that mirroring will decrease performance on the clusters. But
> would this give me a super-fast database system that might cope with what
> I
> need?
> Also, I am thinking about taking the blob data of of the database and
> create
> a new database that just deals with the blobs. How would this affect my
> clustered/mirrored environment?
> Thanks
> Richard
|||The operative word is CAN. If you are running Database Mirroring in HA
mode, it can have a negative impact on performance. The impact on
performance is related to how far apart the principal and mirror are as well
as the networking between the two. It is also impacted by the volume of
transactions in the system which is directly related to how much of the
bandwidth between principal and mirror is being used. The impact is further
impacted by the size of the transaction. Tiny, point transactions will be
impacted more by mirroring that will large transactions which either process
a large number of rows or take a long time to execute.
Database Mirroring in HP mode isn't going to have any impact to your
application that an end user will ever be able to notice.
You can create a Database Snapshot against a mirror, but that is a
point-in-time read-only copy of the database which means that in order for
users to see updated data on the other side, the Database Snapshot has to be
dropped and recreated.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:e9MrWhrFGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Active/Active is a holdover from a specific technical implementation of
> clustering for SQL 7.0. It was not scale-out either. The correct current
> term is multi-instance. As you have read, SQL clustering is a failover
> and availability technology only. SQL clustering does not load balance.
> SQL does not have a native, automatic load balancing technology. There
> are some third party virtualization technologies, but like any scale-out
> technology, adding nodes does not equate to linear performance gains, and
> there are many situations where such solutions do not work.
> As with clustering, Database Mirroring is a failover technology and
> actually has negative impacts on performance. There is one area where it
> can be used to scale out, but that only works for read-only queries.
> Segregating the blob data is not a bad idea, provided you do it down to
> the physical disk level.
> To summarize, there are no shortcuts to scalability, just like you can't
> shortcut availability. Having said that, it sounds like you need a
> reasonably large SQL server. That is not an impossible proposition. SQL
> Server scales up very well. There are several vendors that can sell you
> an adequate system. Some will even help you size it. You might think
> about hiring an experienced SQL consultant who specializes in large-scale
> SQL Systems to guide you through the process. (No, I am not trying to
> drum up business, my time is fully booked).
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> news:A115E5ED-8801-4142-B921-FD25665FE3C0@.microsoft.com...
>

Thursday, February 16, 2012

clustered vs non-clustered performance

Hi,
In a previous post I've asked a question which in the end boiled down to
the differences between clustered & non-clustered indexes.
I am not using clustered indexes in general but people here suggested that I
should. Here is an example of a query that should have made a difference
after I've changed my indexes to clustered from nonclustered as people
suggested.
With all nonclustered...
Table 'REVIEW_PROCESS_STATUS'. Scan count 2, logical reads 527, physical
reads 0, read-ahead reads 0.
Table 'REP_REVIEW_ALLOCATION_REF'. Scan count 1, logical reads 17024,
physical reads 0, read-ahead reads 0.
Table 'REVIEW'. Scan count 2, logical reads 650, physical reads 0,
read-ahead reads 0.
Table 'REVIEW_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'PROCESS_STATUS'. Scan count 3, logical reads 84, physical reads 0,
read-ahead reads 0.
Table 'ABN_MEMBER'. Scan count 2, logical reads 132, physical reads 0,
read-ahead reads 0.
Table 'ENTITY_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'RISK_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
With tables I can create as Clustered cause data arrive in the right order.
That means table review, abn_member and entity_type were changed.
Table 'REVIEW_PROCESS_STATUS'. Scan count 2, logical reads 527, physical
reads 0, read-ahead reads 0.
Table 'REP_REVIEW_ALLOCATION_REF'. Scan count 1, logical reads 17024,
physical reads 0, read-ahead reads 0.
Table 'REVIEW'. Scan count 2, logical reads 658, physical reads 0,
read-ahead reads 0.
Table 'PROCESS_STATUS'. Scan count 3, logical reads 165, physical reads 0,
read-ahead reads 0.
Table 'REVIEW_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'ABN_MEMBER'. Scan count 2, logical reads 106, physical reads 0,
read-ahead reads 0.
Table 'ENTITY_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'RISK_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
That means the nonclustered is actually better by 16 logical reads,
basically the same performance. I take logical reads as the best way to judg
e
performance. Someone mentioned that doing insertions with clustered indexes
is better cause it knows where to insert do you know by how much?
Also do you know what happens if the size of a table is over 8K > size of
page and the index is clustered, does this create any issues with performanc
e?
Thank you,
Panos.
P.S the old post was here...
http://www.microsoft.com/technet/co...0ee0f8
1Panos Stavroulis. wrote:
> Hi,
> In a previous post I've asked a question which in the end boiled
> down to
> the differences between clustered & non-clustered indexes.
> I am not using clustered indexes in general but people here suggested
> that I should. Here is an example of a query that should have made a
> difference after I've changed my indexes to clustered from
> nonclustered as people suggested.
> <SNIP>
Using logical reads is only one metric to measure performance. CPU would
be another very useful metric. Your statement about changing indexes
from non-clustered to clustered has me worried. Firstly, you can only
have one clustered index per table. So either your tables only have a
single index on each or you somehow chose which index to change.
I'm in the camp that believes there's almost always a compelling reason
for each table to have a clustered index. But there are many
considerations you have to take into account before choosing which
index, if any, is best for this. Have a look here for some good
information:
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi Panos
Non-clustered indexes have the advantage of being able to be specifically
tailored to queries (having exacctly the columns required by the query, no
more, no less). This is commonly refered to as "covering" the query.
From a performance tuning perspective, Clustered indexes have the
dis-advantage of always having EVERY column in the table in their leaf
index. So, unless your query actually requires all columns (eg, select
*...), any i/o performed against the leaf pages in a clustered index
involves reading data that is not required for the query.
Hence, its common to see i/o against non-clustered indexes be less than
against clustered indexes.
I generally use clustered indexes to manage physical database maintenance
issues rather than performance tuning.
Regards,
Greg Linwood
SQL Server MVP
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:09D211F2-EC83-4410-A129-4FDA5EDE6050@.microsoft.com...
> Hi,
> In a previous post I've asked a question which in the end boiled down to
> the differences between clustered & non-clustered indexes.
> I am not using clustered indexes in general but people here suggested that
> I
> should. Here is an example of a query that should have made a difference
> after I've changed my indexes to clustered from nonclustered as people
> suggested.
> With all nonclustered...
> Table 'REVIEW_PROCESS_STATUS'. Scan count 2, logical reads 527, physical
> reads 0, read-ahead reads 0.
> Table 'REP_REVIEW_ALLOCATION_REF'. Scan count 1, logical reads 17024,
> physical reads 0, read-ahead reads 0.
> Table 'REVIEW'. Scan count 2, logical reads 650, physical reads 0,
> read-ahead reads 0.
> Table 'REVIEW_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> Table 'PROCESS_STATUS'. Scan count 3, logical reads 84, physical reads 0,
> read-ahead reads 0.
> Table 'ABN_MEMBER'. Scan count 2, logical reads 132, physical reads 0,
> read-ahead reads 0.
> Table 'ENTITY_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> Table 'RISK_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
>
> With tables I can create as Clustered cause data arrive in the right
> order.
> That means table review, abn_member and entity_type were changed.
> Table 'REVIEW_PROCESS_STATUS'. Scan count 2, logical reads 527, physical
> reads 0, read-ahead reads 0.
> Table 'REP_REVIEW_ALLOCATION_REF'. Scan count 1, logical reads 17024,
> physical reads 0, read-ahead reads 0.
> Table 'REVIEW'. Scan count 2, logical reads 658, physical reads 0,
> read-ahead reads 0.
> Table 'PROCESS_STATUS'. Scan count 3, logical reads 165, physical reads 0,
> read-ahead reads 0.
> Table 'REVIEW_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> Table 'ABN_MEMBER'. Scan count 2, logical reads 106, physical reads 0,
> read-ahead reads 0.
> Table 'ENTITY_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> Table 'RISK_TYPE'. Scan count 1, logical reads 1, physical reads 0,
> read-ahead reads 0.
> That means the nonclustered is actually better by 16 logical reads,
> basically the same performance. I take logical reads as the best way to
> judge
> performance. Someone mentioned that doing insertions with clustered
> indexes
> is better cause it knows where to insert do you know by how much?
> Also do you know what happens if the size of a table is over 8K > size of
> page and the index is clustered, does this create any issues with
> performance?
> Thank you,
> Panos.
> P.S the old post was here...
> http://www.microsoft.com/technet/co...0ee0
f81
>

Sunday, February 12, 2012

clustered index rebuilds and performance hit

Hi
We're about to implement some clustered index changes on tables with 10s of
millions of rows.
I would like to know what the implications will be on:
SELECT
INSERT
UPDATE
DELETE
during the duration of the index changes/rebuilds. We have to plan for this
and want our users to know exactly what to expect i.e. what they will and
will not be able to do in the database.
thanks..u
--
-- cranfield, DBAYou mention both "change" and rebuild. Which is it? Removing one clustered index and adding it back
on some other column? Or defragmenting the clustered index?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
> Hi
> We're about to implement some clustered index changes on tables with 10s of
> millions of rows.
> I would like to know what the implications will be on:
> SELECT
> INSERT
> UPDATE
> DELETE
> during the duration of the index changes/rebuilds. We have to plan for this
> and want our users to know exactly what to expect i.e. what they will and
> will not be able to do in the database.
> thanks..u
> --
> -- cranfield, DBA|||if you drop and recreate OR if you use DBCC DBREINDEX, then the table will
be exclusively locked for the duration.
IF you just defrag it via DBCC IndexDefrag, then there will only be minor
impact.
Greg Jackson
PDX, Oregon|||Hi Tibor
We are removing the clustered index and creating a new one on a different key.
We will be creating the old clustered index as non-clustered. These changes
are required due to a logic change in our application.
thanks for the reply.
alan cranfield
"Tibor Karaszi" wrote:
> You mention both "change" and rebuild. Which is it? Removing one clustered index and adding it back
> on some other column? Or defragmenting the clustered index?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
> > Hi
> >
> > We're about to implement some clustered index changes on tables with 10s of
> > millions of rows.
> >
> > I would like to know what the implications will be on:
> >
> > SELECT
> > INSERT
> > UPDATE
> > DELETE
> >
> > during the duration of the index changes/rebuilds. We have to plan for this
> > and want our users to know exactly what to expect i.e. what they will and
> > will not be able to do in the database.
> >
> > thanks..u
> > --
> > -- cranfield, DBA
>
>|||Any time you drop, create or re-create a clustered index the table will be
unavailable for the duration of the event. Your best bet is to follow these
steps:
1. stop all access to this table
2. drop all the nonclustered indexes
3. drop the clustered index
4. Create the new clustered index
5. Recreate the nonclustered indexes
If you just drop the clustered index first it will recreate all the
nonclustered indexes. Then when you create a new clustered index it will
rebuild all the nonclustered indexes again.
--
Andrew J. Kelly SQL MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:18AB918A-44F4-4B91-8F40-1CFA8B5DEC40@.microsoft.com...
> Hi Tibor
> We are removing the clustered index and creating a new one on a different
> key.
> We will be creating the old clustered index as non-clustered. These
> changes
> are required due to a logic change in our application.
> thanks for the reply.
> alan cranfield
> "Tibor Karaszi" wrote:
>> You mention both "change" and rebuild. Which is it? Removing one
>> clustered index and adding it back
>> on some other column? Or defragmenting the clustered index?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
>> > Hi
>> >
>> > We're about to implement some clustered index changes on tables with
>> > 10s of
>> > millions of rows.
>> >
>> > I would like to know what the implications will be on:
>> >
>> > SELECT
>> > INSERT
>> > UPDATE
>> > DELETE
>> >
>> > during the duration of the index changes/rebuilds. We have to plan for
>> > this
>> > and want our users to know exactly what to expect i.e. what they will
>> > and
>> > will not be able to do in the database.
>> >
>> > thanks..u
>> > --
>> > -- cranfield, DBA
>>

Clustered Index performance

I have a table with the structure as below. I am running the query 'update
DTH_StatementMaster_PREP set PrintIndicator = 1 where BatchID =
'BTCH00000000030'. There are only 25,000 records in the table and all of the
m
qualify for the update. Since there is a clustered index on the predicate
(BatchID), I would naturally expect this query to run quick. Unfortunately i
t
is taking over 3 seconds to run which is way too long.
If I look at the execution plan, it says 83% of the cost is on a Sort
operation. The arguments of the Sort operation are PrintIndicator desc,
BatchID asc. Can anyone explain what this Sort operation is? I didn't expect
to see it as I'm not retreiving records, just updating.
Thanks,
Dean
CREATE TABLE [dbo].[DTH_StatementMaster_PREP] (
[StatementID] [char] (15) COLLATE Latin1_General_BIN NOT NULL ,
[StatementAmount] [money] NOT NULL ,
[StatementDate] [smalldatetime] NOT NULL ,
[CurrentBalance] [money] NOT NULL ,
[OverdueBalance] [money] NOT NULL ,
[CustomerID] [varchar] (15) COLLATE Latin1_General_BIN NOT NULL ,
[BatchID] [char] (15) COLLATE Latin1_General_BIN NOT NULL ,
[EntryUserID] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[EntryDateTime] [datetime] NOT NULL ,
[PrintIndicator] [tinyint] NOT NULL ,
[RowID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_DTH_StatementMaster_PREP_BatchID] ON
[dbo].[DTH_StatementMaster_PREP]([BatchID]) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_DTH_StatementMaster_PREP] ON
[dbo]. [DTH_StatementMaster_PREP]([CustomerID])
ON [PRIMARY]
GO
CREATE INDEX [IX_DTH_StatementMaster_PREP_PrintIndica
tor] ON
[dbo]. [DTH_StatementMaster_PREP]([PrintIndicat
or] DESC ) ON [PRIMARY]
GOThere might be a composite key on the table and which is causing the
sort operation.
If you always use 'BTCH00000000030' to update, just create a view for
this and try to update the view
Please let me know if u have any questions
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Dean,
I think we need more info about the execution plan. Which index is the
optimizer using during this operation?

> CREATE INDEX [IX_DTH_StatementMaster_PREP_PrintIndica
tor] ON
> [dbo]. [DTH_StatementMaster_PREP]([PrintIndicat
or] DESC ) ON [PRIMARY]
> GO
Can you tell us a little bit more about possible values for column
[PrintIndicator]?
What is the selectivity for those values?
Based on the selectivity, is it valuable to have an index by [PrintIndicator]?
Tips on Optimizing Non-Clustered
SQL Server Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Dean" wrote:

> I have a table with the structure as below. I am running the query 'update
> DTH_StatementMaster_PREP set PrintIndicator = 1 where BatchID =
> 'BTCH00000000030'. There are only 25,000 records in the table and all of t
hem
> qualify for the update. Since there is a clustered index on the predicate
> (BatchID), I would naturally expect this query to run quick. Unfortunately
it
> is taking over 3 seconds to run which is way too long.
> If I look at the execution plan, it says 83% of the cost is on a Sort
> operation. The arguments of the Sort operation are PrintIndicator desc,
> BatchID asc. Can anyone explain what this Sort operation is? I didn't expe
ct
> to see it as I'm not retreiving records, just updating.
> Thanks,
> Dean
>
>
> CREATE TABLE [dbo].[DTH_StatementMaster_PREP] (
> [StatementID] [char] (15) COLLATE Latin1_General_BIN NOT NULL ,
> [StatementAmount] [money] NOT NULL ,
> [StatementDate] [smalldatetime] NOT NULL ,
> [CurrentBalance] [money] NOT NULL ,
> [OverdueBalance] [money] NOT NULL ,
> [CustomerID] [varchar] (15) COLLATE Latin1_General_BIN NOT NULL ,
> [BatchID] [char] (15) COLLATE Latin1_General_BIN NOT NULL ,
> [EntryUserID] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
> [EntryDateTime] [datetime] NOT NULL ,
> [PrintIndicator] [tinyint] NOT NULL ,
> [RowID] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_DTH_StatementMaster_PREP_BatchID] ON
> [dbo].[DTH_StatementMaster_PREP]([BatchID]) ON [PRIMARY]
> GO
> CREATE UNIQUE INDEX [IX_DTH_StatementMaster_PREP] ON
> [dbo]. [DTH_StatementMaster_PREP]([CustomerID])
ON [PRIMARY]
> GO
> CREATE INDEX [IX_DTH_StatementMaster_PREP_PrintIndica
tor] ON
> [dbo]. [DTH_StatementMaster_PREP]([PrintIndicat
or] DESC ) ON [PRIMARY]
> GO
>|||"Dean" <Dean@.discussions.microsoft.com> wrote in message
news:13F04AE4-2B00-4CB2-AB55-DA9A416CD759@.microsoft.com...
>I have a table with the structure as below. I am running the query 'update
> DTH_StatementMaster_PREP set PrintIndicator = 1 where BatchID =
> 'BTCH00000000030'. There are only 25,000 records in the table and all of
> them
> qualify for the update. Since there is a clustered index on the predicate
> (BatchID), I would naturally expect this query to run quick.
Why would you expect that? The clustered index makes it cheap to identify
the set of records that qualify for the update. But you said that all of
the rows qualify!

>Unfortunately it
> is taking over 3 seconds to run which is way too long.
> If I look at the execution plan, it says 83% of the cost is on a Sort
> operation. The arguments of the Sort operation are PrintIndicator desc,
> BatchID asc. Can anyone explain what this Sort operation is? I didn't
> expect
> to see it as I'm not retreiving records, just updating.
Since the update changes the PrintIndicator on every row, it must
completely rewrite this index:

> CREATE INDEX [IX_DTH_StatementMaster_PREP_PrintIndica
tor] ON
> [dbo]. [DTH_StatementMaster_PREP]([PrintIndicat
or] DESC ) ON [PRIMARY]
> GO
Since BatchID is the clustered index, this nonclustered index contains
PrintIndicator and BatchID. Thus the two-column sort.
However, while not cheap, 3 seconds does seem a bit long to update 25,000
rows, even with a sort. Is this a disk sort? (Look at physical IO). How
much memory does SQL Server have?
David|||I'm not sure which index is the optimizer. Is this something I can see in th
e
execution plan?
Print indicator is really a bit field but had to make it tinyint to put on
index on it. Possible values are only 0, 1 but testing has shown the index o
n
PrintIndicator helps considerably.
Thanks,
Dean|||Dean,

> I'm not sure which index is the optimizer. Is this something I can see in
the
> execution plan?
You can use "set statistics profile on" to display the profile information
for the statement.
AMB
"Dean" wrote:

> I'm not sure which index is the optimizer. Is this something I can see in
the
> execution plan?
> Print indicator is really a bit field but had to make it tinyint to put on
> index on it. Possible values are only 0, 1 but testing has shown the index
on
> PrintIndicator helps considerably.
> Thanks,
> Dean
>

Friday, February 10, 2012

Clustered Index

We are currently addressing many performance issues in an existing
application. One of the items we are investigating is adding clustered
indexes to tables that do not have them. Currently, about 90 tables out of
200 don't have clustered indexes. The long-term solution is to analyze each
table and determine what the best clustered index is. As a shorter-term
solution I've done some analysis to determine what some of the best
candidates would be by writing a query to analyze primary keys, identity
columns, and unique indexes. The problem is that many of these tables have
none of those (primary keys, identity columns, or unique indexes).
With the understanding that this database has much to be redesigned (meaning
I'm not currently looking for comments on bad design), this is my question:
What are the benefits / di

vantages of adding an IDENTITY column and a
CLUSTERED INDEX on that identity column to every table that does not have a
clustered index, primary key, identity column, and unique index. I'm mainly
looking for insight into any di

vantages or problems this will cause. The
reason for the identity column is that it would be a "safe" column to add
from an application perspective and would be a decent candidate for the
clustered index.
I'm very hesitant about doing this blindly and am s

ing to find out if its
feasible in the short-term to gain performance by eliminating heaps in this
manner without introducing other problems.
Thanks,
Mike Jansen> What are the benefits / di

vantages of adding an IDENTITY column and a
> CLUSTERED INDEX on that identity column to every table that does not have
> a clustered index, primary key, identity column, and unique index.
Why do your tables not have keys?
If the column is not there for any good reason, I see no advantage in adding
it. But I really am curious why you have enough tables without the above
elements where this is even a concern or some desperate grasp at using a
jackhammer to hang a picture...|||> What are the benefits / di

vantages of adding an IDENTITY column and a
> CLUSTERED INDEX on that identity column to every table that does not have
a
> clustered index, primary key, identity column, and unique index. I'm main
ly
> looking for insight into any di

vantages or problems this will cause. T
he
> reason for the identity column is that it would be a "safe" column to add
> from an application perspective and would be a decent candidate for the
> clustered index.
The only benefit will be that using an identity column will avoid page split
during insert operations, but from the point of view of query performance,
only you can know. You should analyze every posible query you execute agains
t
the table and from there, spot possible columns. Preferable those used in
range queries, "group by" and "sort by" operations.
Tips on Optimizing SQL Server Clustered Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Mike Jansen" wrote:

> We are currently addressing many performance issues in an existing
> application. One of the items we are investigating is adding clustered
> indexes to tables that do not have them. Currently, about 90 tables out o
f
> 200 don't have clustered indexes. The long-term solution is to analyze ea
ch
> table and determine what the best clustered index is. As a shorter-term
> solution I've done some analysis to determine what some of the best
> candidates would be by writing a query to analyze primary keys, identity
> columns, and unique indexes. The problem is that many of these tables hav
e
> none of those (primary keys, identity columns, or unique indexes).
> With the understanding that this database has much to be redesigned (meani
ng
> I'm not currently looking for comments on bad design), this is my question
:
> What are the benefits / di

ing to find out if i
ts
> feasible in the short-term to gain performance by eliminating heaps in thi
s
> manner without introducing other problems.
> Thanks,
> Mike Jansen
>
>|||> Why do your tables not have keys?
You would have to ask people who left before I started.

> If the column is not there for any good reason, I see no advantage in
> adding it. But I really am curious why you have enough tables without the
> above elements where this is even a concern or some desperate grasp at
> using a jackhammer to hang a picture...
Adding the identity column has no other purpose other than to create a
decent candidate for a clustered index (because its an always-increasing
value and won't be changed) where time is lacking to determine a better one.
The idea I'm trying to get valid feedback on is: Generally speaking, does
adding an identity column and a clustered index on that identity column give
me a performance gain over a heap without introducing any significant
problems? And if it does introduce problems, what might they be? The
assumption being just having a clustered index on a table will perform
better on SELECTs than SELECTs on a heap. Adding the identity column and
clustering on it is to avoid performance problems during INSERTs or UPDATEs
from a poorly chosen clustering index without having to do the full analysis
of all 50 tables. I realize that better analysis will yield a better
clustering index. So I'm not looking for comments on that. I'm looking for
input on whether this will truly be better performing than the heaps (which
I believe is "yes") and are there any drawbacks or di

vantages (apart
from the extra disk space for the identity column).
This is just a small piece of short-to-mid-term performance improvements.
The mid-to-long-term improvements include more drastic analysis and
redesign. I can't satisfy your curiosity about the origin of these
problems, because I don't know them.
Thanks for any constructive help,
Mike|||> The idea I'm trying to get valid feedback on is: Generally speaking, does
> adding an identity column and a clustered index on that identity column
> give me a performance gain
A gain WHERE? If it's just a heap, then I don't know whether your INSERT
performance will improve, since it shouldn't have much effect on the
physical location of new rows. And I don't know what your queries look
like, so it's tough to comment on that as well. If you are doing a lot of
range queries on dates, for example, it would make much more sense for the
clustered index to be on the column(s) with date-related data.
I would say that the question is very difficult to answer "in general." It
seems like a very haphazard approach to me, and you will probably do better
to spend a day or two analyzing the impact of adding sensible keys
(regardless of whether they are natural or surrogates like identity), where
clustered indexes should be, and why -- e.g. what is each table being used
for, how high is the traffic, and what kind of queries are having
performance issues. If you give it a workload, the index tuning wizard
should give a better suggestion than just a blanket "throw identities on all
tables."
A|||Thanks Alejandro. The identity column was generically chosen to avoid
performance hits during INSERTs and UPDATEs. We aren't looking for the
optimal query performance boost in this but simply any amount of boost.
Getting the optimal boost will take more time to analyze than we have for
the short-term. The optimal boost will come later when we take the time to
do a more detailed analysis.
My main concerns are 1) that we are actually getting a performance boost on
SELECTs just by adding a clustered index (compared to having heap) and 2)
that we aren't introducing anything negative because of the clustered index
being clustered on an identity column created solely for the purpose of the
clustered index.
Thanks,
Mike|||You didn't mention the method you are currently using to determine an
indexing strategy. You can try to infer this by examing the SQL selects, but
using the Show Execution Plan option of Query Analyzer can be more
revealing. Be sure you understand what a clustered index is and how it
affects the physical sorting (or re-sorting) of pages in the table.
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:%23SSNMtfkFHA.2484@.TK2MSFTNGP15.phx.gbl...
> We are currently addressing many performance issues in an existing
> application. One of the items we are investigating is adding clustered
> indexes to tables that do not have them. Currently, about 90 tables out
> of 200 don't have clustered indexes. The long-term solution is to analyze
> each table and determine what the best clustered index is. As a
> shorter-term solution I've done some analysis to determine what some of
> the best candidates would be by writing a query to analyze primary keys,
> identity columns, and unique indexes. The problem is that many of these
> tables have none of those (primary keys, identity columns, or unique
> indexes).
> With the understanding that this database has much to be redesigned
> (meaning I'm not currently looking for comments on bad design), this is my
> question:
> What are the benefits / di

vantages or problems this will
> cause. The reason for the identity column is that it would be a "safe"
> column to add from an application perspective and would be a decent
> candidate for the clustered index.
> I'm very hesitant about doing this blindly and am s

ing to find out if
> its feasible in the short-term to gain performance by eliminating heaps in
> this manner without introducing other problems.
> Thanks,
> Mike Jansen
>|||Hey Mike,
Before you do anything in the short term, you need to sit down and
write out a long-term plan. I realize that you (like most of us) are
pressed for time, and are trying to solve the problem as quickly as you
can to move on to bigger issues. However, if you're honest with
yourself, you'd probably admit that many short-term solutions NEVER get
revisited.
I'm not saying that a short-term solution is not a good idea (sometimes
you gotta do what you gotta do); I am saying that you should make sure
that it is a short-term solution. Six months from now, if your shim is
still holding up the table, you may never get around to fixing the
problem. Have a plan to address the problem, even if you can't get to
it today; set a date for fixing the problem. (BTW, I'm lecturing
myself just as much as I'm lecturing you).
In my experience, any table of a reasonable size (and that definition
varies based on performance) will benefit from a clustered index. If
you're doing a lot of INSERTS and UPDATES, the clustered index belongs
on a monotically increasing column (including IDENTITY values, but
timestamps or DateOfInsert columns might make more sense). If your
data is relatively static (lookup values, etc), then a clustered index
will make more sense on columns where you are retrieving a large range
of data. Regardless of where you place it, most tables benefit from
it.
http://msdn.microsoft.com/library/d...>
_05_5h6b.asp
HTH,
Stu|||Mike,
If there are many deletes, then over time a Heap can take up too much
space, which can hurt Select performance. This is something a Clustered
Index can prevent.
Other than that, I can't see how the performance would increase, other
than purely accidental. Without keys, and without analysing the queries,
you are only guessing. And yes, you can hurt performance if you do not
choose the clustered index correctly.
By the way: although a unique index is preferred, the clustered index
does not have to be unique. So I would definitely NOT add an Identity
column. If you have no clue, and there are already indexes on the table,
then you could choose the narrowest nonclustered index and promote it to
be the clustered index. If there are no existing indexes on the table,
you could create the clustered index on the smallest column with the
most distinct values.
But just like everyone is telling you: the real solution is to create a
well thought out, properly normalized data model, with proper keys and
relations. When that is in place, you might not even have to add any
indexes, other than indexed on the foreign key constraints.
HTH,
Gert-Jan
Mike Jansen wrote:
> We are currently addressing many performance issues in an existing
> application. One of the items we are investigating is adding clustered
> indexes to tables that do not have them. Currently, about 90 tables out o
f
> 200 don't have clustered indexes. The long-term solution is to analyze ea
ch
> table and determine what the best clustered index is. As a shorter-term
> solution I've done some analysis to determine what some of the best
> candidates would be by writing a query to analyze primary keys, identity
> columns, and unique indexes. The problem is that many of these tables hav
e
> none of those (primary keys, identity columns, or unique indexes).
> With the understanding that this database has much to be redesigned (meani
ng
> I'm not currently looking for comments on bad design), this is my question
:
> What are the benefits / di

ing to find out if i
ts
> feasible in the short-term to gain performance by eliminating heaps in thi
s
> manner without introducing other problems.
> Thanks,
> Mike Jansen|||Apologies if a different version gets posted; for some reason my
earlier post went off into the ether.
I understand your desire to quickly fix the problem so you can move on
to bigger and better things, but a first step to fixing this is to
write out a plan with definitive dates for fixing all of the
performance issues. Don't let this short-term fix become a permanent
part of your solution (again, I'm lecturing myself just as much as I'm
lecturing you).
I have never encountered a situation where a table of reasonable size
could not benefit from a clustered index; putting the index in the
wrong spot could be bad, but in most cases, a clustered index will
improve performance. If you're doing a lot of INSERTS and UPDATES,
then placing a clustered index on a monotonically increasing value (say
a DateEntered field or DateLoaded or a Timestamp) is a good idea to
avoid page splits. If you place a clustered index on a randomly loaded
field (say a UNIQUEIDENTIFIER or a varchar), you run the risk of
fragmentation. If your data doesn't change that much, the risk is
probably acceptable.
Note that non-clustered indexes include pointers to a clustered index,
so changing the clustered index can have downstream effects on your
nonclustered indexing solution. If you have a lot of non-clustered
indexes (which is likely, given the unstructured "design" you
inherited; been there, done that), be prepared to wait a while as they
get rebuilt when you add a clustered index.
HTH,
Stu

Wednesday, March 7, 2012

Code behaviour/performance on 2 machines

Coalesce increasing performance but Why?

COALESCE in a WHERE clause

COALESCE as a Performance Enhancer?!?

Friday, February 24, 2012

Clustering, Security, Performance, Load Balance

Sunday, February 19, 2012

Clustering for performance

Clustering for Performance

Thursday, February 16, 2012

clustered vs non-clustered performance

Sunday, February 12, 2012

clustered index rebuilds and performance hit

clustered index rebuilds and performance hit

clustered index rebuilds and performance hit

Clustered Index performance

Friday, February 10, 2012

Clustered Index

Clustered DB Server

Blog Archive

About Me