Performance Tuning

I am a SQL DBA newbie. I like to tune a database that I
know very little about. Other than creating indexes on
the tables that are frequently used, and rebuilding them
occasionally, can you list the different tuning tasks I
need to do please. Thank you.
Dianemonitor your slowest sprocs and tune them individually. There is a template
in SQL Profiler that will catch sproc duration statistics for you.
check peformance counters (IO, RAM, CPU, Network) for bottlenecks
dont know what to fix if you dont know what is broken.
Greg Jackson
PDX, Oregon|||Thank you.
>--Original Message--
>monitor your slowest sprocs and tune them individually.
There is a template
>in SQL Profiler that will catch sproc duration statistics
for you.
>
>check peformance counters (IO, RAM, CPU, Network) for
bottlenecks
>dont know what to fix if you dont know what is broken.
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||Here are some links that may help:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Diane Siona" <anonymous@.discussions.microsoft.com> wrote in message
news:abeb01c3ec26$6f7ab150$a301280a@.phx.gbl...
> I am a SQL DBA newbie. I like to tune a database that I
> know very little about. Other than creating indexes on
> the tables that are frequently used, and rebuilding them
> occasionally, can you list the different tuning tasks I
> need to do please. Thank you.
> Diane

Performance Tuning

I am a SQL DBA newbie. I like to tune a database that I
know very little about. Other than creating indexes on
the tables that are frequently used, and rebuilding them
occasionally, can you list the different tuning tasks I
need to do please. Thank you.
Dianemonitor your slowest sprocs and tune them individually. There is a template
in SQL Profiler that will catch sproc duration statistics for you.
check peformance counters (IO, RAM, CPU, Network) for bottlenecks
dont know what to fix if you dont know what is broken.
Greg Jackson
PDX, Oregon|||Thank you.

>--Original Message--
>monitor your slowest sprocs and tune them individually.
There is a template
>in SQL Profiler that will catch sproc duration statistics
for you.
>
>check peformance counters (IO, RAM, CPU, Network) for
bottlenecks
>dont know what to fix if you dont know what is broken.
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||Here are some links that may help:
http://www.microsoft.com/technet/tr...ide/default.asp
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Diane Siona" <anonymous@.discussions.microsoft.com> wrote in message
news:abeb01c3ec26$6f7ab150$a301280a@.phx.gbl...
> I am a SQL DBA newbie. I like to tune a database that I
> know very little about. Other than creating indexes on
> the tables that are frequently used, and rebuilding them
> occasionally, can you list the different tuning tasks I
> need to do please. Thank you.
> Dianesql

Performance question: Indexes on separate file group or dimension table on separate file?

Hi,
I have a set of disks available on my server (but 1 controller only). I want
to use it to improove queries performance...
I want to know what is better to improove the performance:
* moving all (non clustered) indexes on a separate file group on this set of
disk
* moving some tables on this file group (like dimension tables)
I'll monitor the queries to indentify if clustered indexes are more used
then standard indexes.
But I want to know what scenario, generally, helps the performance.
Thanks
Jerome.
Hi Jerome,
This is a difficult question to answer without knowing what kinds of tables
and indexes you're working with. For instance, if you find that you have a
lot of covering indexes for certain common queries, you might find that
moving those off to a different disk will improve performance -- that way
the other disk can satisfy those common queries and the disk with the
clustered index can satisfy other queries. Another consideration you
mentioned is dimension tables -- if you have a lot of large dimension
tables that get scanned during JOINs, you may find that moving them off to a
different disk than the fact tables will improve performance, as the disks
will be able to read the data in tandem. This is definitely something
you're going to have to experiment with on your end, I think.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a set of disks available on my server (but 1 controller only). I
want
> to use it to improove queries performance...
> I want to know what is better to improove the performance:
> * moving all (non clustered) indexes on a separate file group on this set
of
> disk
> * moving some tables on this file group (like dimension tables)
> I'll monitor the queries to indentify if clustered indexes are more used
> then standard indexes.
> But I want to know what scenario, generally, helps the performance.
> Thanks
> Jerome.
>
|||ok...
in my case I have some small dimensions and only 1 "big" (100 000 rows)
my fact tables could have between 1 000 rows to 20 000 000 rows!
Generally the clustered index of each fact table contain all foreign keys
columns.
from your comments, there is no "default" recommandation.
So I'll done some tests I think...
thanks for your comments.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bjLBTZKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi Jerome,
> This is a difficult question to answer without knowing what kinds of
> tables
> and indexes you're working with. For instance, if you find that you have
> a
> lot of covering indexes for certain common queries, you might find that
> moving those off to a different disk will improve performance -- that way
> the other disk can satisfy those common queries and the disk with the
> clustered index can satisfy other queries. Another consideration you
> mentioned is dimension tables -- if you have a lot of large dimension
> tables that get scanned during JOINs, you may find that moving them off to
> a
> different disk than the fact tables will improve performance, as the disks
> will be able to read the data in tandem. This is definitely something
> you're going to have to experiment with on your end, I think.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> want
> of
>
|||"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eVltbdbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
> ok...
> in my case I have some small dimensions and only 1 "big" (100 000 rows)
> my fact tables could have between 1 000 rows to 20 000 000 rows!
> Generally the clustered index of each fact table contain all foreign keys
> columns.
In my experience those small dimensions don't matter too much -- those pages
will end up in cache pretty quickly and usually won't go out of cache. It's
the big huge tables that cause the issues... Good luck tuning it!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic

Performance question: Indexes on separate file group or dimension table on separate fi

Hi,
I have a set of disks available on my server (but 1 controller only). I want
to use it to improove queries performance...
I want to know what is better to improove the performance:
* moving all (non clustered) indexes on a separate file group on this set of
disk
* moving some tables on this file group (like dimension tables)
I'll monitor the queries to indentify if clustered indexes are more used
then standard indexes.
But I want to know what scenario, generally, helps the performance.
Thanks
Jerome.Hi Jerome,
This is a difficult question to answer without knowing what kinds of tables
and indexes you're working with. For instance, if you find that you have a
lot of covering indexes for certain common queries, you might find that
moving those off to a different disk will improve performance -- that way
the other disk can satisfy those common queries and the disk with the
clustered index can satisfy other queries. Another consideration you
mentioned is dimension tables -- if you have a lot of large dimension
tables that get scanned during JOINs, you may find that moving them off to a
different disk than the fact tables will improve performance, as the disks
will be able to read the data in tandem. This is definitely something
you're going to have to experiment with on your end, I think.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a set of disks available on my server (but 1 controller only). I
want
> to use it to improove queries performance...
> I want to know what is better to improove the performance:
> * moving all (non clustered) indexes on a separate file group on this set
of
> disk
> * moving some tables on this file group (like dimension tables)
> I'll monitor the queries to indentify if clustered indexes are more used
> then standard indexes.
> But I want to know what scenario, generally, helps the performance.
> Thanks
> Jerome.
>|||ok...
in my case I have some small dimensions and only 1 "big" (100 000 rows)
my fact tables could have between 1 000 rows to 20 000 000 rows!
Generally the clustered index of each fact table contain all foreign keys
columns.
from your comments, there is no "default" recommandation.
So I'll done some tests I think...
thanks for your comments.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bjLBTZKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi Jerome,
> This is a difficult question to answer without knowing what kinds of
> tables
> and indexes you're working with. For instance, if you find that you have
> a
> lot of covering indexes for certain common queries, you might find that
> moving those off to a different disk will improve performance -- that way
> the other disk can satisfy those common queries and the disk with the
> clustered index can satisfy other queries. Another consideration you
> mentioned is dimension tables -- if you have a lot of large dimension
> tables that get scanned during JOINs, you may find that moving them off to
> a
> different disk than the fact tables will improve performance, as the disks
> will be able to read the data in tandem. This is definitely something
> you're going to have to experiment with on your end, I think.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> want
> of
>|||"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eVltbdbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
> ok...
> in my case I have some small dimensions and only 1 "big" (100 000 rows)
> my fact tables could have between 1 000 rows to 20 000 000 rows!
> Generally the clustered index of each fact table contain all foreign keys
> columns.
In my experience those small dimensions don't matter too much -- those pages
will end up in cache pretty quickly and usually won't go out of cache. It's
the big huge tables that cause the issues... Good luck tuning it!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--sql

Performance problems (strange)

Hello,
I encounter serious performance degradation and what's more important -
strange SQL server behaviour after removing and adding again indexes on few
(6-8) tables.
Let me explain - I have original database, which performs well. Database
size is about 400-600 MB on disk. Then I remove few indexes from few tables
and add it again. Now database performs drastically slower. More than that,
the query schema has changed after removal/adding indexes, but database
schema is the same! So it looks strange.
Please help me and explain why query schema has changed, while database
schema is unchanged? Why database performs slower?
I use SQL Srv 2000 SP3a, Win 2000Srv, 1 GB RAM. Tested in many hardware
scenarios, also with Windows Server 2003.
Adam.
What do you mean the query schema has changed? What is occurring slower?
(Selects? Updates? etc..)
"aheczko@.nospam.nospam" <aheczko@.nospam.nospam@.discussions.microsoft.com >
wrote in message news:D6E52964-B847-4DB7-BACD-CEA5A8BECB97@.microsoft.com...
> Hello,
> I encounter serious performance degradation and what's more important -
> strange SQL server behaviour after removing and adding again indexes on
> few
> (6-8) tables.
> Let me explain - I have original database, which performs well. Database
> size is about 400-600 MB on disk. Then I remove few indexes from few
> tables
> and add it again. Now database performs drastically slower. More than
> that,
> the query schema has changed after removal/adding indexes, but database
> schema is the same! So it looks strange.
> Please help me and explain why query schema has changed, while database
> schema is unchanged? Why database performs slower?
> I use SQL Srv 2000 SP3a, Win 2000Srv, 1 GB RAM. Tested in many hardware
> scenarios, also with Windows Server 2003.
> Adam.
>
|||Selects are much slower. Also sql execution plan shown by query analyser is
different than on a original database.
So I have 2 databases, they have identical schema but SQL execution plan are
different and performance degraded on 2nd db.
Adam
"Jeff Fiegel" wrote:

> What do you mean the query schema has changed? What is occurring slower?
> (Selects? Updates? etc..)
>
> "aheczko@.nospam.nospam" <aheczko@.nospam.nospam@.discussions.microsoft.com >
> wrote in message news:D6E52964-B847-4DB7-BACD-CEA5A8BECB97@.microsoft.com...
>
>
|||Adam,
Are you *sure* the indexes are the same as before? After rebuilding your
indexes run sp_updatestats.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
aheczko@.nospam.nospam wrote:[vbcol=seagreen]
> Selects are much slower. Also sql execution plan shown by query analyser is
> different than on a original database.
> So I have 2 databases, they have identical schema but SQL execution plan are
> different and performance degraded on 2nd db.
> Adam
> "Jeff Fiegel" wrote:
>

Performance problem?

I'm having performance problems in the following scenario:
I import about 3 million rows into a table with DTS(no indexes because of
the large amount of data). I then run a script with 3 delete and about 25
update stmnts. This takes about 3 hours( Is that long?), I then Truncate the
table and recreate this process(with new data ofcourse). It takes longer and
longer on each iteration. What can i do to improve the performance? Would
dropping and recreating the table or the actual DB help? I've noticed that
the time I save running this script with an index during the import is offset
, since it takes much longer to import that much data with an index. Any
ideas would be greatly appreciated! Thanks!
Also, recovery is set to SIMPLE!
Hi
Why don't you:
Drop the Indexes
Load the Data
Create the Indexes
Run the Update/Deletes
Running deletes and updates against a suitably indexed table is a lot faster.
"mikeb" wrote:

> I'm having performance problems in the following scenario:
> I import about 3 million rows into a table with DTS(no indexes because of
> the large amount of data). I then run a script with 3 delete and about 25
> update stmnts. This takes about 3 hours( Is that long?), I then Truncate the
> table and recreate this process(with new data ofcourse). It takes longer and
> longer on each iteration. What can i do to improve the performance? Would
> dropping and recreating the table or the actual DB help? I've noticed that
> the time I save running this script with an index during the import is offset
> , since it takes much longer to import that much data with an index. Any
> ideas would be greatly appreciated! Thanks!
> Also, recovery is set to SIMPLE!
>

Performance problem?

I'm having performance problems in the following scenario:
I import about 3 million rows into a table with DTS(no indexes because of
the large amount of data). I then run a script with 3 delete and about 25
update stmnts. This takes about 3 hours( Is that long?), I then Truncate the
table and recreate this process(with new data ofcourse). It takes longer and
longer on each iteration. What can i do to improve the performance? Would
dropping and recreating the table or the actual DB help? I've noticed that
the time I save running this script with an index during the import is offset
, since it takes much longer to import that much data with an index. Any
ideas would be greatly appreciated! Thanks!
Also, recovery is set to SIMPLE!Hi
Why don't you:
Drop the Indexes
Load the Data
Create the Indexes
Run the Update/Deletes
Running deletes and updates against a suitably indexed table is a lot faster.
"mikeb" wrote:
> I'm having performance problems in the following scenario:
> I import about 3 million rows into a table with DTS(no indexes because of
> the large amount of data). I then run a script with 3 delete and about 25
> update stmnts. This takes about 3 hours( Is that long?), I then Truncate the
> table and recreate this process(with new data ofcourse). It takes longer and
> longer on each iteration. What can i do to improve the performance? Would
> dropping and recreating the table or the actual DB help? I've noticed that
> the time I save running this script with an index during the import is offset
> , since it takes much longer to import that much data with an index. Any
> ideas would be greatly appreciated! Thanks!
> Also, recovery is set to SIMPLE!
>

Performance Problem

The query below is taking 3-4 seconds to run under a light load, which
seems to be a bit lengthy for the indexes that are in place and the
amount of data that exists in the tables. I have outlined everything
below, including all table definitions, indexes, and row counts. Any
help at all will be appreciated. It seems no matter how I think an
index will function it never seems to work properly.
==
BEGIN QUERY
==
SELECT tblC.catDesc AS Category_Name,
COUNT(DISTINCT tblS.set_ID) AS Set_Count,
tblC.cat_ID AS Category_ID,
COUNT(tblI.Img_ID) AS Image_Count,
MIN(tblI.Img_ID) AS Image_ID,
(
SELECT COUNT(tblI2.Img_ID)
FROM tblImage tblI2
LEFT JOIN tblSets tblS2 ON tblS2.set_ID = tblI2.set_ID
WHERE tblI2.d_t > @.d_t
AND tblI2.cat_ID = tblC.cat_ID
AND tblI2.display_status = 1
) AS New_Image_Count,
(
SELECT COUNT(DISTINCT tblI3.set_ID)
FROM tblImage tblI3
LEFT JOIN tblSets tblS3 ON tblS3.set_ID = tblI3.set_ID
WHERE tblI3.d_t > @.d_t
AND tblI3.cat_ID = tblC.cat_ID
) AS New_Set_Count
FROM tblCategories tblC
LEFT JOIN tblImage tblI ON tblC.cat_ID = tblI.cat_ID
LEFT JOIN tblSets tblS ON tblI.set_ID = tblS.set_ID
WHERE tblC.skin_ID = @.skin_ID
GROUP BY tblC.cat_id, tblC.catDesc
==
END QUERY
==
==
tblImage (approx. 71000 rows)
==
Definition:
Img_ID (int, Not Null) - PK
set_ID (int, Null)
cat_ID (int, Null)
d_t (datetime, Null)
display_status (int, Null)
Indexes:
1. Img_ID (clustered)
2. cat_id DESC, display_status DESC, d_t DESC
3. d_t DESC, display_status DESC, set_ID, cat_ID
4. set_ID DESC
==
END tblImage
==
==
tblCategories (approx. 35 rows)
==
Definition:
cat_ID (int, Not Null) - PK
catDesc (varchar(25), Null)
skin_ID (int, Null)
Indexes:
1. cat_ID (clustered)
2. skin_ID, cat_ID
==
END tblCategories
==
==
tblSets (approx. 1500 rows)
==
Definition:
set_ID (int, Not Null) - PK
setName (varchar(25), Null)
setKeywords (varchar(500), Null)
Indexes:
1. set_ID (clustered)
==
END tblSets
==If I understand the query correctly, (If set_ID is unique in tblSets) Then
the following might work and should be faster since it doesn't have the
subquerys...
Select C.catDesc Category_Name,
Count(Distinct S.set_ID) Set_Count,
C.cat_ID Category_ID,
Count(tblI.Img_ID) Image_Count,
Min(I.Img_ID) Image_ID,
Sum(Case When I.d_t = @.d_t
And display_status = 1
Then 1 End) New_Image_Count,
Sum(Case When I.d_t = @.d_t
Then 1 End) New_Set_Count
From tblCategories C
Left Join tblImage I
On I.cat_ID = C.cat_ID
Left Join tblSets S
On S.set_ID = I.set_ID
Where C.skin_ID = @.skin_ID
Group By C.cat_id, C.catDesc
"iTISTIC@.gmail.com" wrote:

> The query below is taking 3-4 seconds to run under a light load, which
> seems to be a bit lengthy for the indexes that are in place and the
> amount of data that exists in the tables. I have outlined everything
> below, including all table definitions, indexes, and row counts. Any
> help at all will be appreciated. It seems no matter how I think an
> index will function it never seems to work properly.
> ==
> BEGIN QUERY
> ==
> SELECT tblC.catDesc AS Category_Name,
> COUNT(DISTINCT tblS.set_ID) AS Set_Count,
> tblC.cat_ID AS Category_ID,
> COUNT(tblI.Img_ID) AS Image_Count,
> MIN(tblI.Img_ID) AS Image_ID,
> (
> SELECT COUNT(tblI2.Img_ID)
> FROM tblImage tblI2
> LEFT JOIN tblSets tblS2 ON tblS2.set_ID = tblI2.set_ID
> WHERE tblI2.d_t > @.d_t
> AND tblI2.cat_ID = tblC.cat_ID
> AND tblI2.display_status = 1
> ) AS New_Image_Count,
> (
> SELECT COUNT(DISTINCT tblI3.set_ID)
> FROM tblImage tblI3
> LEFT JOIN tblSets tblS3 ON tblS3.set_ID = tblI3.set_ID
> WHERE tblI3.d_t > @.d_t
> AND tblI3.cat_ID = tblC.cat_ID
> ) AS New_Set_Count
> FROM tblCategories tblC
> LEFT JOIN tblImage tblI ON tblC.cat_ID = tblI.cat_ID
> LEFT JOIN tblSets tblS ON tblI.set_ID = tblS.set_ID
> WHERE tblC.skin_ID = @.skin_ID
> GROUP BY tblC.cat_id, tblC.catDesc
> ==
> END QUERY
> ==
>
> ==
> tblImage (approx. 71000 rows)
> ==
> Definition:
> Img_ID (int, Not Null) - PK
> set_ID (int, Null)
> cat_ID (int, Null)
> d_t (datetime, Null)
> display_status (int, Null)
> Indexes:
> 1. Img_ID (clustered)
> 2. cat_id DESC, display_status DESC, d_t DESC
> 3. d_t DESC, display_status DESC, set_ID, cat_ID
> 4. set_ID DESC
> ==
> END tblImage
> ==
> ==
> tblCategories (approx. 35 rows)
> ==
> Definition:
> cat_ID (int, Not Null) - PK
> catDesc (varchar(25), Null)
> skin_ID (int, Null)
> Indexes:
> 1. cat_ID (clustered)
> 2. skin_ID, cat_ID
> ==
> END tblCategories
> ==
>
> ==
> tblSets (approx. 1500 rows)
> ==
> Definition:
> set_ID (int, Not Null) - PK
> setName (varchar(25), Null)
> setKeywords (varchar(500), Null)
> Indexes:
> 1. set_ID (clustered)
> ==
> END tblSets
> ==
>

Performance Problem

I have the following DDL/indexes and the following Stored procedure. I am
getting 28 seconds for per user which is unexceptable. What will be the best
way to index this table ?
UserName column has 28000 rows for 1 user (User1) and TR column has mostly
usique numbers........
Thanks for any help.
CREATE TABLE [dbo].[CostCompAvailTemp] (
[UserName] [char] (40) NOT NULL ,
[TR] [decimal](10, 0) NOT NULL ,
[RHour] [smallint] NULL ,
[Def] [decimal](5, 3) NULL ,
[Price] [decimal](4, 2) NULL ,
[RDL] [smallint] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
[dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
GO
CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTem
p]([TR]) ON
[PRIMARY]
GO
----
--
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
declare @.TR as int
/* clean up old */
delete from CostCompAvailTemp
where UserName = @.UserName
set @.TR = 1
while @.TR < 6
begin
insert into CostCompAvailTemp
select @.UserName,
case @.TR
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline,
CreditAvail,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end,
Null
from AvailabilityDetail
where AvailNumber = @.AvailNum
set @.TR = @.TR + 1
end
/* clean then invalid values */
delete from CostCompAvailTemp
where TR < 1
or TR is Null
GOI think the main problem of your process is with this statement
delete from CostCompAvailTemp
where TR < 1
or TR is Null
here your are not using the index you create since the column TR is not a
part of the index you could either add the column TR to the index or create
a
second index for this column. you could also consider create a primary key
for your temp table.
Greetings,
Lic. Alfonso Rafael Chavez de León
Consultor TI y DBA
Xignux Corporativo SA de CV
"DXC" wrote:

> I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the be
st
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvail
Temp]([TR]) ON
> [PRIMARY]
> GO
> ----
--
>
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> declare @.TR as int
> /* clean up old */
> delete from CostCompAvailTemp
> where UserName = @.UserName
> set @.TR = 1
> while @.TR < 6
> begin
> insert into CostCompAvailTemp
> select @.UserName,
> case @.TR
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline,
> CreditAvail,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end,
> Null
> from AvailabilityDetail
> where AvailNumber = @.AvailNum
> set @.TR = @.TR + 1
> end
> /* clean then invalid values */
> delete from CostCompAvailTemp
> where TR < 1
> or TR is Null
>
> GO
>|||"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
>I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the
> best
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailT
emp]([TR])
> ON
> [PRIMARY]
> GO
>
First, use one query, not 5.
Second, don't insert crap, and then clean it up later. Just refrain from
inserting the invalid rows to begin with.
Here's a rewrite using a temp table, a cross join and a derived table:
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
set nocount on
delete from CostCompAvailTemp
where UserName = @.UserName
declare @.TR table(tr int primary key)
insert into @.TR(tr) values (1)
insert into @.TR(tr) values (2)
insert into @.TR(tr) values (3)
insert into @.TR(tr) values (4)
insert into @.TR(tr) values (5)
insert into CostCompAvailTemp
select * from
(
select
@.Username UserName,
case tr.tr
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline RHour,
CreditAvail Def,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end Price,
Null RDL
from AvailabilityDetail
cross join @.TR tr
where AvailNumber = @.AvailNum
) dt
where TR < 1
or TR is Null|||Thanks but developers are telling me that TR column will have duplicates...
..
"David Browne" wrote:

> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
> First, use one query, not 5.
> Second, don't insert crap, and then clean it up later. Just refrain from
> inserting the invalid rows to begin with.
> Here's a rewrite using a temp table, a cross join and a derived table:
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> set nocount on
> delete from CostCompAvailTemp
> where UserName = @.UserName
>
> declare @.TR table(tr int primary key)
> insert into @.TR(tr) values (1)
> insert into @.TR(tr) values (2)
> insert into @.TR(tr) values (3)
> insert into @.TR(tr) values (4)
> insert into @.TR(tr) values (5)
> insert into CostCompAvailTemp
> select * from
> (
> select
> @.Username UserName,
> case tr.tr
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline RHour,
> CreditAvail Def,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end Price,
> Null RDL
> from AvailabilityDetail
> cross join @.TR tr
> where AvailNumber = @.AvailNum
> ) dt
> where TR < 1
> or TR is Null
>
>

performance problem

Hello there
I have table with more then 30,000,000 records.
When i do simple select on 3 fields who are indexes, i see on the execution
plan 90% on bookmark lookup.
What can cause this?"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:Oea7Jf9UGHA.4792@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have table with more then 30,000,000 records.
> When i do simple select on 3 fields who are indexes, i see on the
> execution plan 90% on bookmark lookup.
> What can cause this?
Date: Sat, 31 Dec 2005 10:56:24 +0200
Roy, fix your system clock please.
You're still celebrating New Years Eve.

Performance Problem

Hello there
I have sql server database. In there i have two tables with indexes. When i
run simple select between these two tables i get hash join in the execution
plan.
The reason of the hash join is a result of selecting the wrong indexes on
the execution plan.
Whay it use the wrong index? and how can i force it to select the write
index?
any help would be usefulCan you give us the select query that you are using?|||WOudl be easier with an execution plan, ddl or the Select to encounter the
problem.
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Roy Goldhammer" <roygoldh@.hotmail.com> schrieb im Newsbeitrag
news:e$9DEO%23QFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have sql server database. In there i have two tables with indexes. When
> i
> run simple select between these two tables i get hash join in the
> execution
> plan.
> The reason of the hash join is a result of selecting the wrong indexes on
> the execution plan.
> Whay it use the wrong index? and how can i force it to select the write
> index?
> any help would be useful
>
>

Performance Problem

I have the following DDL/indexes and the following Stored procedure. I am
getting 28 seconds for per user which is unexceptable. What will be the best
way to index this table ?
UserName column has 28000 rows for 1 user (User1) and TR column has mostly
usique numbers........
Thanks for any help.
CREATE TABLE [dbo].[CostCompAvailTemp] (
[UserName] [char] (40) NOT NULL ,
[TR] [decimal](10, 0) NOT NULL ,
[RHour] [smallint] NULL ,
[Def] [decimal](5, 3) NULL ,
[Price] [decimal](4, 2) NULL ,
[RDL] [smallint] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
[dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
GO
CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR]) ON
[PRIMARY]
GO
-----
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
declare @.TR as int
/* clean up old */
delete from CostCompAvailTemp
where UserName = @.UserName
set @.TR = 1
while @.TR < 6
begin
insert into CostCompAvailTemp
select @.UserName,
case @.TR
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline,
CreditAvail,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end,
Null
from AvailabilityDetail
where AvailNumber = @.AvailNum
set @.TR = @.TR + 1
end
/* clean then invalid values */
delete from CostCompAvailTemp
where TR < 1
or TR is Null
GO
I think the main problem of your process is with this statement
delete from CostCompAvailTemp
where TR < 1
or TR is Null
here your are not using the index you create since the column TR is not a
part of the index you could either add the column TR to the index or create a
second index for this column. you could also consider create a primary key
for your temp table.
Greetings,
Lic. Alfonso Rafael Chavez de León
Consultor TI y DBA
Xignux Corporativo SA de CV
"DXC" wrote:

> I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the best
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR]) ON
> [PRIMARY]
> GO
> -----
>
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> declare @.TR as int
> /* clean up old */
> delete from CostCompAvailTemp
> where UserName = @.UserName
> set @.TR = 1
> while @.TR < 6
> begin
> insert into CostCompAvailTemp
> select @.UserName,
> case @.TR
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline,
> CreditAvail,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end,
> Null
> from AvailabilityDetail
> where AvailNumber = @.AvailNum
> set @.TR = @.TR + 1
> end
> /* clean then invalid values */
> delete from CostCompAvailTemp
> where TR < 1
> or TR is Null
>
> GO
>
|||"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
>I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the
> best
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR])
> ON
> [PRIMARY]
> GO
>
First, use one query, not 5.
Second, don't insert crap, and then clean it up later. Just refrain from
inserting the invalid rows to begin with.
Here's a rewrite using a temp table, a cross join and a derived table:
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
set nocount on
delete from CostCompAvailTemp
where UserName = @.UserName
declare @.TR table(tr int primary key)
insert into @.TR(tr) values (1)
insert into @.TR(tr) values (2)
insert into @.TR(tr) values (3)
insert into @.TR(tr) values (4)
insert into @.TR(tr) values (5)
insert into CostCompAvailTemp
select * from
(
select
@.Username UserName,
case tr.tr
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline RHour,
CreditAvail Def,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end Price,
Null RDL
from AvailabilityDetail
cross join @.TR tr
where AvailNumber = @.AvailNum
) dt
where TR < 1
or TR is Null
|||Thanks but developers are telling me that TR column will have duplicates......
"David Browne" wrote:

> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
> First, use one query, not 5.
> Second, don't insert crap, and then clean it up later. Just refrain from
> inserting the invalid rows to begin with.
> Here's a rewrite using a temp table, a cross join and a derived table:
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> set nocount on
> delete from CostCompAvailTemp
> where UserName = @.UserName
>
> declare @.TR table(tr int primary key)
> insert into @.TR(tr) values (1)
> insert into @.TR(tr) values (2)
> insert into @.TR(tr) values (3)
> insert into @.TR(tr) values (4)
> insert into @.TR(tr) values (5)
> insert into CostCompAvailTemp
> select * from
> (
> select
> @.Username UserName,
> case tr.tr
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline RHour,
> CreditAvail Def,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end Price,
> Null RDL
> from AvailabilityDetail
> cross join @.TR tr
> where AvailNumber = @.AvailNum
> ) dt
> where TR < 1
> or TR is Null
>
>

Performance Problem

I have the following DDL/indexes and the following Stored procedure. I am
getting 28 seconds for per user which is unexceptable. What will be the best
way to index this table ?
UserName column has 28000 rows for 1 user (User1) and TR column has mostly
usique numbers........
Thanks for any help.
CREATE TABLE [dbo].[CostCompAvailTemp] (
[UserName] [char] (40) NOT NULL ,
[TR] [decimal](10, 0) NOT NULL ,
[RHour] [smallint] NULL ,
[Def] [decimal](5, 3) NULL ,
[Price] [decimal](4, 2) NULL ,
[RDL] [smallint] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
[dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
GO
CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR]) ON
[PRIMARY]
GO
-----
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
declare @.TR as int
/* clean up old */
delete from CostCompAvailTemp
where UserName = @.UserName
set @.TR = 1
while @.TR < 6
begin
insert into CostCompAvailTemp
select @.UserName,
case @.TR
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline,
CreditAvail,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end,
Null
from AvailabilityDetail
where AvailNumber = @.AvailNum
set @.TR = @.TR + 1
end
/* clean then invalid values */
delete from CostCompAvailTemp
where TR < 1
or TR is Null
GOI think the main problem of your process is with this statement
delete from CostCompAvailTemp
where TR < 1
or TR is Null
here your are not using the index you create since the column TR is not a
part of the index you could either add the column TR to the index or create a
second index for this column. you could also consider create a primary key
for your temp table.
--
Greetings,
Lic. Alfonso Rafael Chavez de León
Consultor TI y DBA
Xignux Corporativo SA de CV
"DXC" wrote:
> I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the best
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR]) ON
> [PRIMARY]
> GO
> -----
>
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> declare @.TR as int
> /* clean up old */
> delete from CostCompAvailTemp
> where UserName = @.UserName
> set @.TR = 1
> while @.TR < 6
> begin
> insert into CostCompAvailTemp
> select @.UserName,
> case @.TR
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline,
> CreditAvail,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end,
> Null
> from AvailabilityDetail
> where AvailNumber = @.AvailNum
> set @.TR = @.TR + 1
> end
> /* clean then invalid values */
> delete from CostCompAvailTemp
> where TR < 1
> or TR is Null
>
> GO
>|||"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
>I have the following DDL/indexes and the following Stored procedure. I am
> getting 28 seconds for per user which is unexceptable. What will be the
> best
> way to index this table ?
> UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> usique numbers........
> Thanks for any help.
> CREATE TABLE [dbo].[CostCompAvailTemp] (
> [UserName] [char] (40) NOT NULL ,
> [TR] [decimal](10, 0) NOT NULL ,
> [RHour] [smallint] NULL ,
> [Def] [decimal](5, 3) NULL ,
> [Price] [decimal](4, 2) NULL ,
> [RDL] [smallint] NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> GO
> CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR])
> ON
> [PRIMARY]
> GO
>
First, use one query, not 5.
Second, don't insert crap, and then clean it up later. Just refrain from
inserting the invalid rows to begin with.
Here's a rewrite using a temp table, a cross join and a derived table:
--CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
Declare @.UserName char(40)
Declare @.AvailNum as int
Declare @.PVal as dec(4,2)
Declare @.PCol as int
--as
set nocount on
delete from CostCompAvailTemp
where UserName = @.UserName
declare @.TR table(tr int primary key)
insert into @.TR(tr) values (1)
insert into @.TR(tr) values (2)
insert into @.TR(tr) values (3)
insert into @.TR(tr) values (4)
insert into @.TR(tr) values (5)
insert into CostCompAvailTemp
select * from
(
select
@.Username UserName,
case tr.tr
when 1 then TR1
when 2 then TR2
when 3 then TR3
when 4 then TR4
when 5 then TR5
end as TR,
Deadline RHour,
CreditAvail Def,
case @.PCol
when 1 then Price1
when 2 then Price2
when 3 then Price3
else @.PVal
end Price,
Null RDL
from AvailabilityDetail
cross join @.TR tr
where AvailNumber = @.AvailNum
) dt
where TR < 1
or TR is Null|||Thanks but developers are telling me that TR column will have duplicates......
"David Browne" wrote:
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:4AE4D52C-DD9D-4C25-B592-A02F9A29C554@.microsoft.com...
> >I have the following DDL/indexes and the following Stored procedure. I am
> > getting 28 seconds for per user which is unexceptable. What will be the
> > best
> > way to index this table ?
> >
> > UserName column has 28000 rows for 1 user (User1) and TR column has mostly
> > usique numbers........
> >
> > Thanks for any help.
> >
> > CREATE TABLE [dbo].[CostCompAvailTemp] (
> > [UserName] [char] (40) NOT NULL ,
> > [TR] [decimal](10, 0) NOT NULL ,
> > [RHour] [smallint] NULL ,
> > [Def] [decimal](5, 3) NULL ,
> > [Price] [decimal](4, 2) NULL ,
> > [RDL] [smallint] NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE CLUSTERED INDEX [CostCompAvailTemp_ix1] ON
> > [dbo].[CostCompAvailTemp]([UserName]) ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [CostCompAvailTemp_ix2] ON [dbo].[CostCompAvailTemp]([TR])
> > ON
> > [PRIMARY]
> > GO
> >
> First, use one query, not 5.
> Second, don't insert crap, and then clean it up later. Just refrain from
> inserting the invalid rows to begin with.
> Here's a rewrite using a temp table, a cross join and a derived table:
> --CREATE PROCEDURE [dbo].[CostCompCopyAvailIntoTemp]
> Declare @.UserName char(40)
> Declare @.AvailNum as int
> Declare @.PVal as dec(4,2)
> Declare @.PCol as int
> --as
> set nocount on
> delete from CostCompAvailTemp
> where UserName = @.UserName
>
> declare @.TR table(tr int primary key)
> insert into @.TR(tr) values (1)
> insert into @.TR(tr) values (2)
> insert into @.TR(tr) values (3)
> insert into @.TR(tr) values (4)
> insert into @.TR(tr) values (5)
> insert into CostCompAvailTemp
> select * from
> (
> select
> @.Username UserName,
> case tr.tr
> when 1 then TR1
> when 2 then TR2
> when 3 then TR3
> when 4 then TR4
> when 5 then TR5
> end as TR,
> Deadline RHour,
> CreditAvail Def,
> case @.PCol
> when 1 then Price1
> when 2 then Price2
> when 3 then Price3
> else @.PVal
> end Price,
> Null RDL
> from AvailabilityDetail
> cross join @.TR tr
> where AvailNumber = @.AvailNum
> ) dt
> where TR < 1
> or TR is Null
>
>

Performance of SQL2k5 with snapshot isolation level turned on

I did several tests of performance of SQL2k5, and I see quite strange result
s.
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache
-
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:

> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>
>|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...[vbcol=seagreen]
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:
>

Performance of SQL2k5 with snapshot isolation level turned on

I did several tests of performance of SQL2k5, and I see quite strange results.
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache -
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?
As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>
|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:

> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>
>
|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...[vbcol=seagreen]
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:

Performance of SQL2k5 with snapshot isolation level turned on

I did several tests of performance of SQL2k5, and I see quite strange results.
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache -
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:
> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
> >I did several tests of performance of SQL2k5, and I see quite strange
> >results.
> > I restored database from SQL2k, rebuilded indexes, updated statistics. I
> > did
> > all what described in migration process. Backuped the resulting database.
> > In
> > each of next tests I restored the database from backup. Next I run tests.
> > Since our application, OLTP, is very performance sensitive, we need to
> > have
> > good distribution of transaction execution time. In case if I run tests on
> > SQL2k5, when snapshot isolation level is not turned on, I see good
> > distribution - less than 0.5% of transactions executing more than 200ms,
> > average execution time is ~5 ms. In case if I change compatibility level
> > to
> > 90 and turn on allow_snapshot_isolation, behavior is dramatically
> > changing.
> > Near 5% of transactions start to execute more than 200 ms, average
> > execution
> > time is also significally increasing. There are no transactions which are
> > really use snapshot isolation level, stored procedures are exacly same as
> > before. There is no any memory pressure, no IO bottlenecks. During the
> > tests
> > counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> > 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> > GB
> > size, but only ~200 MB of the data was read in the tests. 1.2GB write
> > cache -
> > it means what all writing during the test was done only to write cache on
> > CX500, tests were not so long to fill the entire write cache. Windows 2003
> > x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> > CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> > tests with snapshot level. 2CPU.
> > From the test it looks like I can not remove replication to QueryDB and
> > run
> > queries on same computer where OLTP load...
> > Why impact of snapshot isolation level is so big?
> >
>
>|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:
>> As soon as you change the isolation level to either Snapshot Isolation or
>> Read Committed Snapshot, SQL Server will start saving ALL updated data in
>> the version store in tempdb, whether or not any queries every read the
>> versioned data. So every changed row will have additional overhead
>> associated with it.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "andsm" <andsm@.discussions.microsoft.com> wrote in message
>> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>> >I did several tests of performance of SQL2k5, and I see quite strange
>> >results.
>> > I restored database from SQL2k, rebuilded indexes, updated statistics.
>> > I
>> > did
>> > all what described in migration process. Backuped the resulting
>> > database.
>> > In
>> > each of next tests I restored the database from backup. Next I run
>> > tests.
>> > Since our application, OLTP, is very performance sensitive, we need to
>> > have
>> > good distribution of transaction execution time. In case if I run tests
>> > on
>> > SQL2k5, when snapshot isolation level is not turned on, I see good
>> > distribution - less than 0.5% of transactions executing more than
>> > 200ms,
>> > average execution time is ~5 ms. In case if I change compatibility
>> > level
>> > to
>> > 90 and turn on allow_snapshot_isolation, behavior is dramatically
>> > changing.
>> > Near 5% of transactions start to execute more than 200 ms, average
>> > execution
>> > time is also significally increasing. There are no transactions which
>> > are
>> > really use snapshot isolation level, stored procedures are exacly same
>> > as
>> > before. There is no any memory pressure, no IO bottlenecks. During the
>> > tests
>> > counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data
>> > on
>> > 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have
>> > 2
>> > GB
>> > size, but only ~200 MB of the data was read in the tests. 1.2GB write
>> > cache -
>> > it means what all writing during the test was done only to write cache
>> > on
>> > CX500, tests were not so long to fill the entire write cache. Windows
>> > 2003
>> > x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
>> > CPU load was ~50% in tests without snapshot isolation level, and ~70%
>> > with
>> > tests with snapshot level. 2CPU.
>> > From the test it looks like I can not remove replication to QueryDB and
>> > run
>> > queries on same computer where OLTP load...
>> > Why impact of snapshot isolation level is so big?
>> >
>>
>>

Performance Monitor for Indexes

I have a SQL Server 2000 database with numerous indexes. The database
developers have reworked the indexes to increase database performance.
This database has Windows 2003 as the operating system on this server.
What are the performance monitor counters that I should add to my perfmon job
to collect statistic before and after the indexes have been modified?
Please help me with this task.
Thanks,
Indexing will have most impact on disk io, so I would monitor PhysicalDisk:
%Read time and PhysicalDisk %Write time for each disk. You should also look
at CPU changes by monitoring the cpu counters, and also Average Disk Queue
length.
My preferred approach in this case though is to use SQL Profiler and run a
trace before adding indexes and trace to a table. Then add your indexes and
run the same workload through.
Then query your trace tables to find how your longest runnnig queries before
indexing have improved. You will be able to examine duration of query, reads,
writes and cpu. This will give you more information and suggest to you
queries that are still not performing well after you apply your indexing
script.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Joe K." wrote:

> I have a SQL Server 2000 database with numerous indexes. The database
> developers have reworked the indexes to increase database performance.
> This database has Windows 2003 as the operating system on this server.
> What are the performance monitor counters that I should add to my perfmon job
> to collect statistic before and after the indexes have been modified?
> Please help me with this task.
> Thanks,

Performance Monitor for Indexes

I have a SQL Server 2000 database with numerous indexes. The database
developers have reworked the indexes to increase database performance.
This database has Windows 2003 as the operating system on this server.
What are the performance monitor counters that I should add to my perfmon jo
b
to collect statistic before and after the indexes have been modified?
Please help me with this task.
Thanks,Indexing will have most impact on disk io, so I would monitor PhysicalDisk:
%Read time and PhysicalDisk %Write time for each disk. You should also look
at CPU changes by monitoring the cpu counters, and also Average Disk Queue
length.
My preferred approach in this case though is to use SQL Profiler and run a
trace before adding indexes and trace to a table. Then add your indexes and
run the same workload through.
Then query your trace tables to find how your longest runnnig queries before
indexing have improved. You will be able to examine duration of query, reads
,
writes and cpu. This will give you more information and suggest to you
queries that are still not performing well after you apply your indexing
script.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Joe K." wrote:

> I have a SQL Server 2000 database with numerous indexes. The database
> developers have reworked the indexes to increase database performance.
> This database has Windows 2003 as the operating system on this server.
> What are the performance monitor counters that I should add to my perfmon
job
> to collect statistic before and after the indexes have been modified?
> Please help me with this task.
> Thanks,

Performance Monitor for Indexes

I have a SQL Server 2000 database with numerous indexes. The database
developers have reworked the indexes to increase database performance.
This database has Windows 2003 as the operating system on this server.
What are the performance monitor counters that I should add to my perfmon job
to collect statistic before and after the indexes have been modified?
Please help me with this task.
Thanks,Indexing will have most impact on disk io, so I would monitor PhysicalDisk:
%Read time and PhysicalDisk %Write time for each disk. You should also look
at CPU changes by monitoring the cpu counters, and also Average Disk Queue
length.
My preferred approach in this case though is to use SQL Profiler and run a
trace before adding indexes and trace to a table. Then add your indexes and
run the same workload through.
Then query your trace tables to find how your longest runnnig queries before
indexing have improved. You will be able to examine duration of query, reads,
writes and cpu. This will give you more information and suggest to you
queries that are still not performing well after you apply your indexing
script.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Joe K." wrote:
> I have a SQL Server 2000 database with numerous indexes. The database
> developers have reworked the indexes to increase database performance.
> This database has Windows 2003 as the operating system on this server.
> What are the performance monitor counters that I should add to my perfmon job
> to collect statistic before and after the indexes have been modified?
> Please help me with this task.
> Thanks,