Index question

Does the performance increase if the same index created on
the table twice ?. Say I have a 2 nonclustered index on
the same column. Does it increase the performance or the
performance will be the same if there is only 1 index on
that column ?
Thanks.
The engine can only use one index, so no. In fact, I would bet this would
be worse off for your system... first, the engine has to consider another
option when creating a query plan, plus the extra space the additional index
will require.
Why do you think this would be better?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
> Does the performance increase if the same index created on
> the table twice ?. Say I have a 2 nonclustered index on
> the same column. Does it increase the performance or the
> performance will be the same if there is only 1 index on
> that column ?
> Thanks.
|||I never thought it would be better. Our dumb developers
created a couple of indexes like that in the production
database and I removed the duplicates thinking it wouldn't
cause performance issues............
Thanks.........

>--Original Message--
>The engine can only use one index, so no. In fact, I
would bet this would
>be worse off for your system... first, the engine has to
consider another
>option when creating a query plan, plus the extra space
the additional index
>will require.
>Why do you think this would be better?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
on
>
>.
>
|||Yes bad practise - no reason for it and like Aaron said, there's the disk space too. Also, when you modify the data you have to change the data in the table and the two indexes. You should get your developers to script their indexes (and make sure they na
me them!)
Alicia
Http://www.sqlporn.co.uk

Index question

Does the performance increase if the same index created on
the table twice ?. Say I have a 2 nonclustered index on
the same column. Does it increase the performance or the
performance will be the same if there is only 1 index on
that column '
Thanks.The engine can only use one index, so no. In fact, I would bet this would
be worse off for your system... first, the engine has to consider another
option when creating a query plan, plus the extra space the additional index
will require.
Why do you think this would be better?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
> Does the performance increase if the same index created on
> the table twice ?. Say I have a 2 nonclustered index on
> the same column. Does it increase the performance or the
> performance will be the same if there is only 1 index on
> that column '
> Thanks.|||I never thought it would be better. Our dumb developers
created a couple of indexes like that in the production
database and I removed the duplicates thinking it wouldn't
cause performance issues............
Thanks.........

>--Original Message--
>The engine can only use one index, so no. In fact, I
would bet this would
>be worse off for your system... first, the engine has to
consider another
>option when creating a query plan, plus the extra space
the additional index
>will require.
>Why do you think this would be better?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
on[vbcol=seagreen]
>
>.
>|||Yes bad practise - no reason for it and like Aaron said, there's the disk sp
ace too. Also, when you modify the data you have to change the data in the t
able and the two indexes. You should get your developers to script their ind
exes (and make sure they na
me them!)
Alicia
Http://www.sqlporn.co.uk

Index question

Does the performance increase if the same index created on
the table twice ?. Say I have a 2 nonclustered index on
the same column. Does it increase the performance or the
performance will be the same if there is only 1 index on
that column '
Thanks.The engine can only use one index, so no. In fact, I would bet this would
be worse off for your system... first, the engine has to consider another
option when creating a query plan, plus the extra space the additional index
will require.
Why do you think this would be better?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
> Does the performance increase if the same index created on
> the table twice ?. Say I have a 2 nonclustered index on
> the same column. Does it increase the performance or the
> performance will be the same if there is only 1 index on
> that column '
> Thanks.|||I never thought it would be better. Our dumb developers
created a couple of indexes like that in the production
database and I removed the duplicates thinking it wouldn't
cause performance issues............
Thanks.........
>--Original Message--
>The engine can only use one index, so no. In fact, I
would bet this would
>be worse off for your system... first, the engine has to
consider another
>option when creating a query plan, plus the extra space
the additional index
>will require.
>Why do you think this would be better?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
>> Does the performance increase if the same index created
on
>> the table twice ?. Say I have a 2 nonclustered index on
>> the same column. Does it increase the performance or the
>> performance will be the same if there is only 1 index on
>> that column '
>> Thanks.
>
>.
>sql

Index problems

SQL2K EE SP3 , WIN 2K EE SP2
We have a 27 Gb database that continually has index / performance problems.
We do a DBCC DBREINDEX every night, and sp_updatestats every hour during the
day. The database has a lot of read, write and delete activity, usually inv
olving 1000's of records at a time and more than 100,000 records a few time
s a day.
Because of slow query response we started doing the DBREINDEX every night an
d it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page (64:2562414) by RID because the slotid (742594
184) is not valid.
This was solved after the nightly DBREINDEX, but since we cannot DBREINDEX d
uring the day while users are in the system we started running sp_updatestat
s every hour and this fixes it as well.
Now we have had a few times were a query returns no records even though the
data exists. The last time we had the problem the following query was being
run:
SELECT *
FROM dbo.MV_Detail
WHERE Tailor_ID = 359884
AND Circ_ID = 81670
If we take out the AND Circ_ID = 81670 statement and do an order by on the t
able we see that we have data that match the criteria, or if we add a 3rd cr
iteria with a table join then we see the data.
SELECT *
FROM dbo.mv_Detail a,
(select distinct geography from tempdb.dbo.mv_Geo40499999) b
where a.Tailor_ID = 359884 AND a.Circ_ID = 81670 and b.geography = a.geograp
hy
I ran sp_updatestats on the database and we still did not get any records fr
om the first query. After we ran DBREINDEX the first query started to return
records again.
I have included the table create script for the main table were we have prob
lems with. This table is about 1/3 of the database size and contains almost
60 million records and uses about 5Gb in data space and 5.5 Gb in index spac
e.
Any ideas on how to improve this situation would be greatly appreciated. We
changed the fill factor on the indexes from 80 to 90 to conserve space, but
have added disk space since then. Could reducing the fill factor help with t
hese types of problems?
CREATE TABLE [dbo].[MV_Detail] (
[Circ_ID] [int] NOT NULL ,
[Tailor_ID] [int] NOT NULL ,
[geography] [varchar] (10) COLLATE SQL_Latin1_General_CP437_CI_AS NOT NULL ,
[circtype] [int] NOT NULL ,
[UseAny] [bit] NOT NULL ,
[Monday_Use] [bit] NOT NULL ,
[Tuesday_Use] [bit] NOT NULL ,
[Wednesday_Use] [bit] NOT NULL ,
[Thursday_Use] [bit] NOT NULL ,
[Friday_Use] [bit] NOT NULL ,
[Saturday_Use] [bit] NOT NULL ,
[Sunday_Use] [bit] NOT NULL ,
[monday] [int] NOT NULL ,
[tuesday] [int] NOT NULL ,
[wednesday] [int] NOT NULL ,
[thursday] [int] NOT NULL ,
[friday] [int] NOT NULL ,
[saturday] [int] NOT NULL ,
[sunday] [int] NOT NULL ,
[Date_Changed] [datetime] NULL ,
[Changed_By] [char] (8) COLLATE SQL_Latin1_General_CP437_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MV_Detail] ADD
CONSTRAINT [DF_MV_Detail_UseAny] DEFAULT (0) FOR [UseAny],
CONSTRAINT [DF_MV_Detail_Monday_Use] DEFAULT (0) FOR [Monday_Use],
CONSTRAINT [DF_MV_Detail_Tuesday_Use] DEFAULT (0) FOR [Tuesday_Use],
CONSTRAINT [DF_MV_Detail_Wednesday_Use] DEFAULT (0) FOR [Wednesday_Use],
CONSTRAINT [DF_MV_Detail_Thursday_Use] DEFAULT (0) FOR [Thursday_Use],
CONSTRAINT [DF_MV_Detail_Friday_Use] DEFAULT (0) FOR [Friday_Use],
CONSTRAINT [DF_MV_Detail_Saturday_Use] DEFAULT (0) FOR [Saturday_Use],
CONSTRAINT [DF_MV_Detail_Sunday_Use] DEFAULT (0) FOR [Sunday_Use]
GO
CREATE INDEX [IX_MV_Detail] ON [dbo].[MV_Detail]([Tailor_ID], [geography]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
CREATE INDEX [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [circtype]) WITH FILLF
ACTOR = 90 ON [PRIMARY]
GOMike,
It sounds like data corruption. See if this article helps:
http://support.microsoft.com/defaul...kb;en-us;826433
SK
"Mike" <Mike@.Comcast.net> wrote in message news:u$1HcAD5DHA.564@.TK2MSFTNGP10
.phx.gbl...
SQL2K EE SP3 , WIN 2K EE SP2
We have a 27 Gb database that continually has index / performance problems.
We do a DBCC DBREINDEX every night, and sp_updatestats every hour during the
day. The database has a lot of read, write and delete activity, usually inv
olving 1000's of records at a time and more than 100,000 records a few time
s a day.
Because of slow query response we started doing the DBREINDEX every night an
d it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page (64:2562414) by RID because the slotid (742594
184) is not valid.
This was solved after the nightly DBREINDEX, but since we cannot DBREINDEX d
uring the day while users are in the system we started running sp_updatestat
s every hour and this fixes it as well.
Now we have had a few times were a query returns no records even though the
data exists. The last time we had the problem the following query was being
run:
SELECT *
FROM dbo.MV_Detail
WHERE Tailor_ID = 359884
AND Circ_ID = 81670
If we take out the AND Circ_ID = 81670 statement and do an order by on the t
able we see that we have data that match the criteria, or if we add a 3rd cr
iteria with a table join then we see the data.
SELECT *
FROM dbo.mv_Detail a,
(select distinct geography from tempdb.dbo.mv_Geo40499999) b
where a.Tailor_ID = 359884 AND a.Circ_ID = 81670 and b.geography = a.geograp
hy
I ran sp_updatestats on the database and we still did not get any records fr
om the first query. After we ran DBREINDEX the first query started to return
records again.
I have included the table create script for the main table were we have prob
lems with. This table is about 1/3 of the database size and contains almost
60 million records and uses about 5Gb in data space and 5.5 Gb in index spac
e.
Any ideas on how to improve this situation would be greatly appreciated. We
changed the fill factor on the indexes from 80 to 90 to conserve space, but
have added disk space since then. Could reducing the fill factor help with t
hese types of problems?
CREATE TABLE [dbo].[MV_Detail] (
[Circ_ID] [int] NOT NULL ,
[Tailor_ID] [int] NOT NULL ,
[geography] [varchar] (10) COLLATE SQL_Latin1_General_CP437_CI_AS NOT NULL ,
[circtype] [int] NOT NULL ,
[UseAny] [bit] NOT NULL ,
[Monday_Use] [bit] NOT NULL ,
[Tuesday_Use] [bit] NOT NULL ,
[Wednesday_Use] [bit] NOT NULL ,
[Thursday_Use] [bit] NOT NULL ,
[Friday_Use] [bit] NOT NULL ,
[Saturday_Use] [bit] NOT NULL ,
[Sunday_Use] [bit] NOT NULL ,
[monday] [int] NOT NULL ,
[tuesday] [int] NOT NULL ,
[wednesday] [int] NOT NULL ,
[thursday] [int] NOT NULL ,
[friday] [int] NOT NULL ,
[saturday] [int] NOT NULL ,
[sunday] [int] NOT NULL ,
[Date_Changed] [datetime] NULL ,
[Changed_By] [char] (8) COLLATE SQL_Latin1_General_CP437_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MV_Detail] ADD
CONSTRAINT [DF_MV_Detail_UseAny] DEFAULT (0) FOR [UseAny],
CONSTRAINT [DF_MV_Detail_Monday_Use] DEFAULT (0) FOR [Monday_Use],
CONSTRAINT [DF_MV_Detail_Tuesday_Use] DEFAULT (0) FOR [Tuesday_Use],
CONSTRAINT [DF_MV_Detail_Wednesday_Use] DEFAULT (0) FOR [Wednesday_Use],
CONSTRAINT [DF_MV_Detail_Thursday_Use] DEFAULT (0) FOR [Thursday_Use],
CONSTRAINT [DF_MV_Detail_Friday_Use] DEFAULT (0) FOR [Friday_Use],
CONSTRAINT [DF_MV_Detail_Saturday_Use] DEFAULT (0) FOR [Saturday_Use],
CONSTRAINT [DF_MV_Detail_Sunday_Use] DEFAULT (0) FOR [Sunday_Use]
GO
CREATE INDEX [IX_MV_Detail] ON [dbo].[MV_Detail]([Tailor_ID], [geography]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
CREATE INDEX [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [circtype]) WITH FILLF
ACTOR = 90 ON [PRIMARY]
GO|||If it is a hardware issue, would it be capturing it in error logs anywhere?
Mike
"Steve Kass" <skass@.drew.edu> wrote in message news:OEbuj8E5DHA.1804@.TK2MSFT
NGP12.phx.gbl...
Mike,
It sounds like data corruption. See if this article helps:
http://support.microsoft.com/defaul...kb;en-us;826433
SK
"Mike" <Mike@.Comcast.net> wrote in message news:u$1HcAD5DHA.564@.TK2MSFTNGP10
.phx.gbl...
SQL2K EE SP3 , WIN 2K EE SP2
We have a 27 Gb database that continually has index / performance problems.
We do a DBCC DBREINDEX every night, and sp_updatestats every hour during the
day. The database has a lot of read, write and delete activity, usually inv
olving 1000's of records at a time and more than 100,000 records a few time
s a day.
Because of slow query response we started doing the DBREINDEX every night an
d it improved performance.
Than we started getting the following errors:
Cannot retrieve row from page (64:2562414) by RID because the slotid (742594
184) is not valid.
This was solved after the nightly DBREINDEX, but since we cannot DBREINDEX d
uring the day while users are in the system we started running sp_updatestat
s every hour and this fixes it as well.
Now we have had a few times were a query returns no records even though the
data exists. The last time we had the problem the following query was being
run:
SELECT *
FROM dbo.MV_Detail
WHERE Tailor_ID = 359884
AND Circ_ID = 81670
If we take out the AND Circ_ID = 81670 statement and do an order by on the t
able we see that we have data that match the criteria, or if we add a 3rd cr
iteria with a table join then we see the data.
SELECT *
FROM dbo.mv_Detail a,
(select distinct geography from tempdb.dbo.mv_Geo40499999) b
where a.Tailor_ID = 359884 AND a.Circ_ID = 81670 and b.geography = a.geograp
hy
I ran sp_updatestats on the database and we still did not get any records fr
om the first query. After we ran DBREINDEX the first query started to return
records again.
I have included the table create script for the main table were we have prob
lems with. This table is about 1/3 of the database size and contains almost
60 million records and uses about 5Gb in data space and 5.5 Gb in index spac
e.
Any ideas on how to improve this situation would be greatly appreciated. We
changed the fill factor on the indexes from 80 to 90 to conserve space, but
have added disk space since then. Could reducing the fill factor help with t
hese types of problems?
CREATE TABLE [dbo].[MV_Detail] (
[Circ_ID] [int] NOT NULL ,
[Tailor_ID] [int] NOT NULL ,
[geography] [varchar] (10) COLLATE SQL_Latin1_General_CP437_CI_AS NOT NULL ,
[circtype] [int] NOT NULL ,
[UseAny] [bit] NOT NULL ,
[Monday_Use] [bit] NOT NULL ,
[Tuesday_Use] [bit] NOT NULL ,
[Wednesday_Use] [bit] NOT NULL ,
[Thursday_Use] [bit] NOT NULL ,
[Friday_Use] [bit] NOT NULL ,
[Saturday_Use] [bit] NOT NULL ,
[Sunday_Use] [bit] NOT NULL ,
[monday] [int] NOT NULL ,
[tuesday] [int] NOT NULL ,
[wednesday] [int] NOT NULL ,
[thursday] [int] NOT NULL ,
[friday] [int] NOT NULL ,
[saturday] [int] NOT NULL ,
[sunday] [int] NOT NULL ,
[Date_Changed] [datetime] NULL ,
[Changed_By] [char] (8) COLLATE SQL_Latin1_General_CP437_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MV_Detail] ADD
CONSTRAINT [DF_MV_Detail_UseAny] DEFAULT (0) FOR [UseAny],
CONSTRAINT [DF_MV_Detail_Monday_Use] DEFAULT (0) FOR [Monday_Use],
CONSTRAINT [DF_MV_Detail_Tuesday_Use] DEFAULT (0) FOR [Tuesday_Use],
CONSTRAINT [DF_MV_Detail_Wednesday_Use] DEFAULT (0) FOR [Wednesday_Use],
CONSTRAINT [DF_MV_Detail_Thursday_Use] DEFAULT (0) FOR [Thursday_Use],
CONSTRAINT [DF_MV_Detail_Friday_Use] DEFAULT (0) FOR [Friday_Use],
CONSTRAINT [DF_MV_Detail_Saturday_Use] DEFAULT (0) FOR [Saturday_Use],
CONSTRAINT [DF_MV_Detail_Sunday_Use] DEFAULT (0) FOR [Sunday_Use]
GO
CREATE INDEX [IX_MV_Detail] ON [dbo].[MV_Detail]([Tailor_ID], [geography]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE UNIQUE INDEX [CircTailorGeoType] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [geography], [circtype]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
CREATE INDEX [IX_Circ_Trigger] ON [dbo].[MV_Detail]([Circ_ID], [Tailor_ID], [circtype]) WITH FILLF
ACTOR = 90 ON [PRIMARY]
GO

Index Performance Monitor Counter

I have a SQL Server 2000 database that I have clean up duplicates indexes
and numerous other unneeded indexes.
What are good performance monitor counters to show the database running
better
without these indexes?Joe K. wrote:
> I have a SQL Server 2000 database that I have clean up duplicates
> indexes and numerous other unneeded indexes.
> What are good performance monitor counters to show the database
> running better
> without these indexes?
Record a profiler trace of typical traffic before and after the change.
That way you can also look at ITW's suggestions for indexes to drop /
created.
Kind regards
robertsql

Index Performance Monitor Counter

I have a SQL Server 2000 database that I have clean up duplicates indexes
and numerous other unneeded indexes.
What are good performance monitor counters to show the database running
better
without these indexes?
Joe K. wrote:
> I have a SQL Server 2000 database that I have clean up duplicates
> indexes and numerous other unneeded indexes.
> What are good performance monitor counters to show the database
> running better
> without these indexes?
Record a profiler trace of typical traffic before and after the change.
That way you can also look at ITW's suggestions for indexes to drop /
created.
Kind regards
robert

index on separate physical disk

does anyone have any data on performance increase due to separating
data and indexes - each on a different physical drive? thanks,
arthur
Something like that depends on a lot of factors and is hard to quantify as
such. I don't think you will find a white paper or anything official along
those lines due to the fact there are so many variables. Do you have a
specific question in mind?
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1109187034.716627.83110@.g14g2000cwa.googlegro ups.com...
> does anyone have any data on performance increase due to separating
> data and indexes - each on a different physical drive? thanks,
> arthur
>
|||well, we've got several huge tables, and queries against them that are
suffering from poor performance. both data and indexes are on a single
raid 0+1 drive. we've got several bays left in our disk array, and i'm
wondering if moving the indexes to their own raid 0+1 or raid 5 drive
will increase performance ( - in oracle this is a no brainer). you can
assume that the queries and indexes themselves are optimized.
|||also, these huge tables have both clustered and non-clustered indexes
on them, and most of the slow running queries involve joins on
non-clustered indexes.
|||you could consider moving individual tables and indexes to their own
drives\filegroups to improve performance. But you would want the filegroup
to be no the same server (not on a different machine somewhere).
how many disks (Physical) are in the 0+1 array ?
How much Write cache does your RAID Controller have ?
You do not want to go to a RAID 5 volume. The performance penalty in
comparison to 0+1 would kill you
Greg Jackson
PDX, Oregon
|||Have you done any research to see why they are slow? What do the query
plans look like? Are you scanning the indexes of doing seeks? Have you tried
tuning the queries and or indexes? Yes it is possible that if you split
some of the indexes onto a different drive array you can get better
performance if you are maxing out the current array or channel. If you are
then you should see why that is. Have you looked at the disk counters to
see what is going on as well?
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1109194461.051461.129970@.l41g2000cwc.googlegr oups.com...
> also, these huge tables have both clustered and non-clustered indexes
> on them, and most of the slow running queries involve joins on
> non-clustered indexes.
>
|||> Have you looked at the disk counters to
> see what is going on as well?
Right... unless you are using old IDE disks baled together with hay wire, it
is doubtful that disk is your #1 bottleneck against a nc index... you might
be able to squeeze a little more performance by moving some things to
different disks, but that will be a lot of effort for little gain in my
experience, and certainly not a silver bullet solution.
|||1. 4 physical disks on the 0+1 array.
2. 256MB read/write cache that's set to 50/50. it was at 90/10, but
we're playing around w/ the settings.
3. is that a performance penalty in reads or writes? according to
http://www.acnc.com/04_01_05.html, raid 5 has the highest read
transaction rate.
4. the queries are slow because of bad database, application, and query
design. we don't have the resources, however, to spend on fixing these.
we need a quick solution that will keep performance at an acceptable
level while we focus our resources on our application rewrite.
5. the query plans show a lot of index scans which are unavoidable. our
users can search on members w/ conditions like, last_name = 'd%'. our
primary key is not on last_name, hence the index scan.
6. yes, we've tuned the queries and indexes as much as possible
(without modifiying table structure and application code).
7. yes, we routinely check serveral performance counters, including
disk counters, and i/o isn't much of an issue.
|||see notes below:

> 1. 4 physical disks on the 0+1 array.
> 2. 256MB read/write cache that's set to 50/50. it was at 90/10, but
> we're playing around w/ the settings.
Change this to 100%. As Andrew pointed out to me last week, SQL Server
Caches Reads anyway. When I changed my controller setting sto 100% Writes,
it boosted IO significantly.

> 3. is that a performance penalty in reads or writes? according to
> http://www.acnc.com/04_01_05.html, raid 5 has the highest read
> transaction rate.
Penalty on Writes

> 4. the queries are slow because of bad database, application, and query
> design. we don't have the resources, however, to spend on fixing these.
> we need a quick solution that will keep performance at an acceptable
> level while we focus our resources on our application rewrite.
so throw hardware at this bad boy eh ?

> 5. the query plans show a lot of index scans which are unavoidable. our
> users can search on members w/ conditions like, last_name = 'd%'. our
> primary key is not on last_name, hence the index scan.
You need AN Index on Lastname. Even with the Wildcard, the index will be
used. Does not have to be a clustered index and does not have to be the PKey

> 6. yes, we've tuned the queries and indexes as much as possible
> (without modifiying table structure and application code).
Are you sure, you cant improve the index usage at all ? when you say you are
seeing scans, that to me means there IS room for improvement.

> 7. yes, we routinely check serveral performance counters, including
> disk counters, and i/o isn't much of an issue.
IO is not much of an issue ? You dont see any Disk Queueing ? With the
Scans, I would guess you are seeing some queueing
Greg Jackson
PDX, Oregon
|||I agree with everything Greg stated here. If I/O isn't an issue then why do
you think adding more drives will help? If it is an issue then the scans
are most likely the cause. Even if you have to add more indexes than you
want it may get you through the tough times until you can spend time fixing
everything. Any chance you can add a covering index? There are some things
that throwing hardware at simply can not fix unless you can add enough ram
and cpus to over come the scans.
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23yw3smfGFHA.2416@.TK2MSFTNGP14.phx.gbl...
> see notes below:
>
> Change this to 100%. As Andrew pointed out to me last week, SQL Server
> Caches Reads anyway. When I changed my controller setting sto 100% Writes,
> it boosted IO significantly.
>
> Penalty on Writes
>
> so throw hardware at this bad boy eh ?
>
> You need AN Index on Lastname. Even with the Wildcard, the index will be
> used. Does not have to be a clustered index and does not have to be the
> PKey
>
> Are you sure, you cant improve the index usage at all ? when you say you
> are seeing scans, that to me means there IS room for improvement.
>
> IO is not much of an issue ? You dont see any Disk Queueing ? With the
> Scans, I would guess you are seeing some queueing
>
> Greg Jackson
> PDX, Oregon
>

Index Performance Monitor Counter

I have a SQL Server 2000 database that I have clean up duplicates indexes
and numerous other unneeded indexes.
What are good performance monitor counters to show the database running
better
without these indexes?Joe K. wrote:
> I have a SQL Server 2000 database that I have clean up duplicates
> indexes and numerous other unneeded indexes.
> What are good performance monitor counters to show the database
> running better
> without these indexes?
Record a profiler trace of typical traffic before and after the change.
That way you can also look at ITW's suggestions for indexes to drop /
created.
Kind regards
robert

Index Performance

Hi,

I am trying to improve performance in some statements i run against a 15M rows table.

I have batchs that inserts about 6M rows and some that insert 500K to 1M rows...

Statistically i have a match of 1 to 3.

I am currently disabling all indexes in the destination table and running the insert batch's.

After it finishes the statements i execute the rebuild of all indexes...

This takes up to 4 hours to run... ( 3 of them used just to rebuild the indexes )

My question is: Am i using the best aproach? Or is it better to leave the indexes enabled and do the inserts batch with them?

I Hope i was clear enough

Best Regards,

Yes, that is most often the best approach. It is much faster to add data to a heap, and then to reorganize the data just once. Generally speaking, doing large quantities of inserts would already likely fragment the indexes and need to be rebuilt anyhow.

|||

No, you do NOT want to leave the indexes 'enabled' during the imports.

You may wish to try the following to determine if performance can be improved.

If your situation allows:

switch the database RECOVERY model to 'Simple',

Drop the indexes,

Make the imports in smaller 'batches' (Perhaps 100K rows), Looping until complete

ReBuild the Indexes

switch back to FULL recovery when finished,

and then make a FULL BACKUP.

Be sure that you Build any Clustered Indexex first!

|||

Thats exactly what i am doing...

DB's are in simple mode forever.. because we dont need transactions.

Indexes are not droped but instead are disabled...

I execute the batch (dont use loops because performance is ok - i only use loops for delete's )

I execute Rebuild with ALL on Table_name parameter.

So i guess theres no way around.... I have to wait 3 hours for the indexes to get rebuild all the time... even if i only insert 1 row...

Also the post above speakes on Reorganizing indexes but i guess he meant Rebuilding no? Changing that amount of rows would definitly need a rebuild right?

Regards,

|||

You can also try, if possible, importing to a dummy table, leaving the original table in tact, then later insert into the real table.

In this case, you have 1 hour to import + 1 hour to INSERT = 2 hours

You've just saved 2 hours on waiting for the indexes to be re-enabled.

Just my twist on it,

Adamus

|||

Sorry?

But my batch is already inserting from a temporary table into a final one... So you would do it with the indexes up?

Regards

|||

So you are bulk INSERTING not bulk IMPORTING.

Correct?

Adamus

|||

Yes sorry... it was my english that was right in the first post and wrong in the second when i mentioned import instead of insert

To take out any doubts i am bulk inserting from one temporary table into a final one...

You are true if i was importing anyway

Best Regards,

|||

Are are overwriting or appending to the existing records in the final table?

What I'm getting at is, instead of touching the records for the sake of reports and processing, why not rename the temp table to the name of the final table, if it exists in SQL, and create a new temp table?

If this is possible, add the indexes to the new table.

If you are overwriting:

|

V

Drop Final Table

|

V

Rename Temp Table to Final Table

|

V

Create New Temp Table

|

V

Apply indexes to new final table

Adamus

|||

>>Also the post above speakes on Reorganizing indexes but i guess he meant Rebuilding no? Changing that amount of rows would definitly need a rebuild right?<<

No,if you are just adding rows to a table, you can let SQL Server reorganize the pages of the index instead of doing a full rebuild. It can be done while others are using the table. In 2005, ALTER INDEX...REORGANIZE, in 2000, it is a DBCC.

>>So i guess theres no way around.... I have to wait 3 hours for the indexes to get rebuild all the time... even if i only insert 1 row... <<

Now here is a tricky question. You would be silly to drop all of the indexes and rebuild the table for a single row insert (unless your only job is to do this, and while you wait you can read books on database design. Then it would be a good thing

Seriously, all the answers we gave you were based on a BULK load of the table. Meaning you were the only user at the time, pumping in a very high percentage of rows. Your original question stated:

>>

I am trying to improve performance in some statements i run against a 15M rows table.

I have batchs that inserts about 6M rows and some that insert 500K to 1M rows..<

You are significantly changing the shape of the table if you are adding or even changing keys of .1 of the table. So the fastest thing to do in this case is like to employ one of the techniques mentioned, and use a BULK operation and rebuild indexes.

We haven't even talked about what you are clustering on, or how many indexes, all important things to discuss, if you are only adding (or changing) far fewer rows. Testing is the key. I would suggest you write at least two or three scenarios and test.

If rowcount < N, then just insert directly.

If rowcount >= N and < M, disable these indexes, insert your rows, then add them back,

if rowcount >= M, disable/drop all indexes (including Clustered) and SSIS the rows in.

Especially if you are running short of time for your (presumably) off hours processing to take place

|||

Hi Arnie,

I can see the benefits of switching to "bulk-logged" recovery model, but siwtching to "simple" will break the sequence of transaction log backups.

Switching from the Full or Bulk-Logged Recovery Model

http://msdn2.microsoft.com/en-us/library/ms190203.aspx

AMB

|||

Alejandro,

I didn't indicate, but assumed (and yes, I know that one shouldn't assume) that a full backup would be made before changing the recovery model

I should have been more directly clear on that point.

Your point about the chain of backups is very important. Thanks for adding that to clarify the conversation.

|||

I guess you are using SS 2005. Do a test without disabling the indexes, because SS can decide to use a strategy sometimes

called index-at-a-time, were instead updating each index by each row inserted, SS gather a group of rows, sort them per each

index and merge them. The advantage is that SS will not access same index page more than once.

You can read about this estrategy in the book:

Inside Microsoft SQL Server 2005: The Storage Engine

by Kalen Delaney

Chapter 7 - Index Internals and Management

Table-Level vs. Index-Level Data Modification

Tame Bulky Inserts

By: Kalen Delaney

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=25521&DisplayTab=Article

AMB

|||

Well my table suffers changes in adding and removing records... so this is alot of change happening here... a complete transformation of the final result.

So i think rebuild is definitly a must :/ I didn't thought it would take that amount of time!

Regards

index on view

If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C44498.B5850D10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The following link has some useful information on improving performance =using indexed views:
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/dnsql2=
k/html/indexedviews1.asp
However, to be specific to your scenario, you might consider running a =workload through Index Tuning Wizard to see whether it suggests that you =create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" <kqd02@.yahoo.com> wrote in message =news:143df01c444cd$f6355010$a301280a@.phx.gbl...
If an application uses a lot of views and the performance is slow, should we create indexes on views? What kind of indexes to create?
Thanks.
--=_NextPart_000_0008_01C44498.B5850D10
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

The following link has some useful information on =improving performance using indexed views:
http://msdn.microsoft.com/library/defau=lt.asp?url=3D/library/en-us/dnsql2k/html/indexedviews1.asp
However, to be specific to your scenario, you might =consider running a workload through Index Tuning Wizard to see whether it =suggests that you create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" wrote in message news:143df01c444cd$=f6355010$a301280a@.phx.gbl...If an application uses a lot of views and the performance is slow, =should we create indexes on views? What kind of indexes to create?Thanks.

---

--=_NextPart_000_0008_01C44498.B5850D10--|||You may consider indexed view, some people call it materialized view
too. Beware that there're alot limitations on indexed view, for detail,
read BOL.
You may also want to investigate how those views are constructed, are
they nested views? views joining another view? Based on my experience,
joining differnet views are bad idea, it may be easy to program, but
performance really sucks.
If that's not the case, run those views inside query analyzer to see if
there are any table scans, then create index accordingly.
Eric
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Eric Li
SQL DBA
MCDBA|||If performance is low, then you should start by adding the appropriate
indexes to the base tables that are used in the view. SQL-Server will
automatically take these into consideration.
If that doesn't help (enough), and you are running Enterprise Edition of
SQL-Server 2000, you could consider indexed views.
Hope this helps,
Gert-Jan
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
--
(Please reply only to the newsgroup)|||Hi
Just to add to the other posts...
You may also want to consider if the view is being used appropriately!!!
e.g. It is not a great idea of using a view that joins half a dozen tables
when you only want data from a single base table.
John
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.|||Be very careful about creating indexed views. The cost of maintenance can be
very high... So exhaust all other possibilities prior to choosing indexed
views as a solution.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.

index on separate physical disk

does anyone have any data on performance increase due to separating
data and indexes - each on a different physical drive? thanks,
arthurSomething like that depends on a lot of factors and is hard to quantify as
such. I don't think you will find a white paper or anything official along
those lines due to the fact there are so many variables. Do you have a
specific question in mind?
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1109187034.716627.83110@.g14g2000cwa.googlegroups.com...
> does anyone have any data on performance increase due to separating
> data and indexes - each on a different physical drive? thanks,
> arthur
>|||well, we've got several huge tables, and queries against them that are
suffering from poor performance. both data and indexes are on a single
raid 0+1 drive. we've got several bays left in our disk array, and i'm
wondering if moving the indexes to their own raid 0+1 or raid 5 drive
will increase performance ( - in oracle this is a no brainer). you can
assume that the queries and indexes themselves are optimized.|||also, these huge tables have both clustered and non-clustered indexes
on them, and most of the slow running queries involve joins on
non-clustered indexes.|||you could consider moving individual tables and indexes to their own
drives\filegroups to improve performance. But you would want the filegroup
to be no the same server (not on a different machine somewhere).
how many disks (Physical) are in the 0+1 array ?
How much Write cache does your RAID Controller have ?
You do not want to go to a RAID 5 volume. The performance penalty in
comparison to 0+1 would kill you
Greg Jackson
PDX, Oregon|||Have you done any research to see why they are slow? What do the query
plans look like? Are you scanning the indexes of doing seeks? Have you tried
tuning the queries and or indexes? Yes it is possible that if you split
some of the indexes onto a different drive array you can get better
performance if you are maxing out the current array or channel. If you are
then you should see why that is. Have you looked at the disk counters to
see what is going on as well?
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1109194461.051461.129970@.l41g2000cwc.googlegroups.com...
> also, these huge tables have both clustered and non-clustered indexes
> on them, and most of the slow running queries involve joins on
> non-clustered indexes.
>|||> Have you looked at the disk counters to
> see what is going on as well?
Right... unless you are using old IDE disks baled together with hay wire, it
is doubtful that disk is your #1 bottleneck against a nc index... you might
be able to squeeze a little more performance by moving some things to
different disks, but that will be a lot of effort for little gain in my
experience, and certainly not a silver bullet solution.|||1. 4 physical disks on the 0+1 array.
2. 256MB read/write cache that's set to 50/50. it was at 90/10, but
we're playing around w/ the settings.
3. is that a performance penalty in reads or writes? according to
http://www.acnc.com/04_01_05.html, raid 5 has the highest read
transaction rate.
4. the queries are slow because of bad database, application, and query
design. we don't have the resources, however, to spend on fixing these.
we need a quick solution that will keep performance at an acceptable
level while we focus our resources on our application rewrite.
5. the query plans show a lot of index scans which are unavoidable. our
users can search on members w/ conditions like, last_name = 'd%'. our
primary key is not on last_name, hence the index scan.
6. yes, we've tuned the queries and indexes as much as possible
(without modifiying table structure and application code).
7. yes, we routinely check serveral performance counters, including
disk counters, and i/o isn't much of an issue.|||see notes below:

> 1. 4 physical disks on the 0+1 array.
> 2. 256MB read/write cache that's set to 50/50. it was at 90/10, but
> we're playing around w/ the settings.
Change this to 100%. As Andrew pointed out to me last week, SQL Server
Caches Reads anyway. When I changed my controller setting sto 100% Writes,
it boosted IO significantly.

> 3. is that a performance penalty in reads or writes? according to
> http://www.acnc.com/04_01_05.html, raid 5 has the highest read
> transaction rate.
Penalty on Writes

> 4. the queries are slow because of bad database, application, and query
> design. we don't have the resources, however, to spend on fixing these.
> we need a quick solution that will keep performance at an acceptable
> level while we focus our resources on our application rewrite.
so throw hardware at this bad boy eh ?

> 5. the query plans show a lot of index scans which are unavoidable. our
> users can search on members w/ conditions like, last_name = 'd%'. our
> primary key is not on last_name, hence the index scan.
You need AN Index on Lastname. Even with the Wildcard, the index will be
used. Does not have to be a clustered index and does not have to be the PKey

> 6. yes, we've tuned the queries and indexes as much as possible
> (without modifiying table structure and application code).
Are you sure, you cant improve the index usage at all ? when you say you are
seeing scans, that to me means there IS room for improvement.

> 7. yes, we routinely check serveral performance counters, including
> disk counters, and i/o isn't much of an issue.
IO is not much of an issue ? You dont see any Disk Queueing ? With the
Scans, I would guess you are seeing some queueing
Greg Jackson
PDX, Oregon|||I agree with everything Greg stated here. If I/O isn't an issue then why do
you think adding more drives will help? If it is an issue then the scans
are most likely the cause. Even if you have to add more indexes than you
want it may get you through the tough times until you can spend time fixing
everything. Any chance you can add a covering index? There are some things
that throwing hardware at simply can not fix unless you can add enough ram
and cpus to over come the scans.
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23yw3smfGFHA.2416@.TK2MSFTNGP14.phx.gbl...
> see notes below:
>
> Change this to 100%. As Andrew pointed out to me last week, SQL Server
> Caches Reads anyway. When I changed my controller setting sto 100% Writes,
> it boosted IO significantly.
>
>
> Penalty on Writes
>
> so throw hardware at this bad boy eh ?
>
> You need AN Index on Lastname. Even with the Wildcard, the index will be
> used. Does not have to be a clustered index and does not have to be the
> PKey
>
> Are you sure, you cant improve the index usage at all ? when you say you
> are seeing scans, that to me means there IS room for improvement.
>
> IO is not much of an issue ? You dont see any Disk Queueing ? With the
> Scans, I would guess you are seeing some queueing
>
> Greg Jackson
> PDX, Oregon
>

index on separate physical disk

does anyone have any data on performance increase due to separating
data and indexes - each on a different physical drive? thanks,
arthurSomething like that depends on a lot of factors and is hard to quantify as
such. I don't think you will find a white paper or anything official along
those lines due to the fact there are so many variables. Do you have a
specific question in mind?
--
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1109187034.716627.83110@.g14g2000cwa.googlegroups.com...
> does anyone have any data on performance increase due to separating
> data and indexes - each on a different physical drive? thanks,
> arthur
>|||well, we've got several huge tables, and queries against them that are
suffering from poor performance. both data and indexes are on a single
raid 0+1 drive. we've got several bays left in our disk array, and i'm
wondering if moving the indexes to their own raid 0+1 or raid 5 drive
will increase performance ( - in oracle this is a no brainer). you can
assume that the queries and indexes themselves are optimized.|||also, these huge tables have both clustered and non-clustered indexes
on them, and most of the slow running queries involve joins on
non-clustered indexes.|||you could consider moving individual tables and indexes to their own
drives\filegroups to improve performance. But you would want the filegroup
to be no the same server (not on a different machine somewhere).
how many disks (Physical) are in the 0+1 array ?
How much Write cache does your RAID Controller have ?
You do not want to go to a RAID 5 volume. The performance penalty in
comparison to 0+1 would kill you
Greg Jackson
PDX, Oregon|||Have you done any research to see why they are slow? What do the query
plans look like? Are you scanning the indexes of doing seeks? Have you tried
tuning the queries and or indexes? Yes it is possible that if you split
some of the indexes onto a different drive array you can get better
performance if you are maxing out the current array or channel. If you are
then you should see why that is. Have you looked at the disk counters to
see what is going on as well?
--
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1109194461.051461.129970@.l41g2000cwc.googlegroups.com...
> also, these huge tables have both clustered and non-clustered indexes
> on them, and most of the slow running queries involve joins on
> non-clustered indexes.
>|||> Have you looked at the disk counters to
> see what is going on as well?
Right... unless you are using old IDE disks baled together with hay wire, it
is doubtful that disk is your #1 bottleneck against a nc index... you might
be able to squeeze a little more performance by moving some things to
different disks, but that will be a lot of effort for little gain in my
experience, and certainly not a silver bullet solution.|||1. 4 physical disks on the 0+1 array.
2. 256MB read/write cache that's set to 50/50. it was at 90/10, but
we're playing around w/ the settings.
3. is that a performance penalty in reads or writes? according to
http://www.acnc.com/04_01_05.html, raid 5 has the highest read
transaction rate.
4. the queries are slow because of bad database, application, and query
design. we don't have the resources, however, to spend on fixing these.
we need a quick solution that will keep performance at an acceptable
level while we focus our resources on our application rewrite.
5. the query plans show a lot of index scans which are unavoidable. our
users can search on members w/ conditions like, last_name = 'd%'. our
primary key is not on last_name, hence the index scan.
6. yes, we've tuned the queries and indexes as much as possible
(without modifiying table structure and application code).
7. yes, we routinely check serveral performance counters, including
disk counters, and i/o isn't much of an issue.|||see notes below:
> 1. 4 physical disks on the 0+1 array.
> 2. 256MB read/write cache that's set to 50/50. it was at 90/10, but
> we're playing around w/ the settings.
Change this to 100%. As Andrew pointed out to me last week, SQL Server
Caches Reads anyway. When I changed my controller setting sto 100% Writes,
it boosted IO significantly.
> 3. is that a performance penalty in reads or writes? according to
> http://www.acnc.com/04_01_05.html, raid 5 has the highest read
> transaction rate.
Penalty on Writes
> 4. the queries are slow because of bad database, application, and query
> design. we don't have the resources, however, to spend on fixing these.
> we need a quick solution that will keep performance at an acceptable
> level while we focus our resources on our application rewrite.
so throw hardware at this bad boy eh ?
> 5. the query plans show a lot of index scans which are unavoidable. our
> users can search on members w/ conditions like, last_name = 'd%'. our
> primary key is not on last_name, hence the index scan.
You need AN Index on Lastname. Even with the Wildcard, the index will be
used. Does not have to be a clustered index and does not have to be the PKey
> 6. yes, we've tuned the queries and indexes as much as possible
> (without modifiying table structure and application code).
Are you sure, you cant improve the index usage at all ? when you say you are
seeing scans, that to me means there IS room for improvement.
> 7. yes, we routinely check serveral performance counters, including
> disk counters, and i/o isn't much of an issue.
IO is not much of an issue ? You dont see any Disk Queueing ? With the
Scans, I would guess you are seeing some queueing
Greg Jackson
PDX, Oregon|||I agree with everything Greg stated here. If I/O isn't an issue then why do
you think adding more drives will help? If it is an issue then the scans
are most likely the cause. Even if you have to add more indexes than you
want it may get you through the tough times until you can spend time fixing
everything. Any chance you can add a covering index? There are some things
that throwing hardware at simply can not fix unless you can add enough ram
and cpus to over come the scans.
--
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23yw3smfGFHA.2416@.TK2MSFTNGP14.phx.gbl...
> see notes below:
>
>> 1. 4 physical disks on the 0+1 array.
>> 2. 256MB read/write cache that's set to 50/50. it was at 90/10, but
>> we're playing around w/ the settings.
> Change this to 100%. As Andrew pointed out to me last week, SQL Server
> Caches Reads anyway. When I changed my controller setting sto 100% Writes,
> it boosted IO significantly.
>
>> 3. is that a performance penalty in reads or writes? according to
>> http://www.acnc.com/04_01_05.html, raid 5 has the highest read
>> transaction rate.
> Penalty on Writes
>> 4. the queries are slow because of bad database, application, and query
>> design. we don't have the resources, however, to spend on fixing these.
>> we need a quick solution that will keep performance at an acceptable
>> level while we focus our resources on our application rewrite.
> so throw hardware at this bad boy eh ?
>> 5. the query plans show a lot of index scans which are unavoidable. our
>> users can search on members w/ conditions like, last_name = 'd%'. our
>> primary key is not on last_name, hence the index scan.
> You need AN Index on Lastname. Even with the Wildcard, the index will be
> used. Does not have to be a clustered index and does not have to be the
> PKey
>
>> 6. yes, we've tuned the queries and indexes as much as possible
>> (without modifiying table structure and application code).
> Are you sure, you cant improve the index usage at all ? when you say you
> are seeing scans, that to me means there IS room for improvement.
>
>> 7. yes, we routinely check serveral performance counters, including
>> disk counters, and i/o isn't much of an issue.
> IO is not much of an issue ? You dont see any Disk Queueing ? With the
> Scans, I would guess you are seeing some queueing
>
> Greg Jackson
> PDX, Oregon
>|||These gurus covered it all. I can't agree more about the indexes. If you
know which fields are being searched, especially frequently, add indexes if
they're not already there. You might get a little boost out of your
existing indexes by running UPDATE STATISTICS and DBCC INDEXDEFRAG on your
tables and indexes also.|||Just make sure you do it in this order DBCC INDEXDEFRAG and then UPDATE
STATISTICS<g>.
--
Andrew J. Kelly SQL MVP
"Michael C#" <xyz@.abcdef.com> wrote in message
news:JTbTd.19988$8m4.18245@.fe08.lga...
> These gurus covered it all. I can't agree more about the indexes. If you
> know which fields are being searched, especially frequently, add indexes
> if they're not already there. You might get a little boost out of your
> existing indexes by running UPDATE STATISTICS and DBCC INDEXDEFRAG on your
> tables and indexes also.
>|||Good point :) Thx
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Vmg7mnGFHA.2744@.tk2msftngp13.phx.gbl...
> Just make sure you do it in this order DBCC INDEXDEFRAG and then UPDATE
> STATISTICS<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:JTbTd.19988$8m4.18245@.fe08.lga...
>> These gurus covered it all. I can't agree more about the indexes. If
>> you know which fields are being searched, especially frequently, add
>> indexes if they're not already there. You might get a little boost out
>> of your existing indexes by running UPDATE STATISTICS and DBCC
>> INDEXDEFRAG on your tables and indexes also.
>|||> unless you can add enough ram and cpus to over come the scans.
exactly! again, part of the problem w/ scans is that users can search
(for example on a member) via any of a number of fields, each of which
have an associated non-clustered index. if they search for a particular
user, e.g. where last_name = 'smith', then performance is fine,
however, when they do, where last_name like 's%', performance degrades.
some of this is a training issue, but still.
also, we've got a job that runs indexdefrag nightly.
anyway, i ran some tests last night by creating a copy of one of our
large tables and putting the associated indexes on a separate drive
(raid 5). i ran a number of (select) queries against both the large
table and copy of large table (issuing dbcc freeproccache and
dropcleanbuffers before each run), and saw a performance difference of
0 to 27% depending on the complexity of the query.
thanks for all your help so far on this!|||> user, e.g. where last_name = 'smith', then performance is fine,
> however, when they do, where last_name like 's%', performance degrades.
Actually, this isn't always true. When you use LIKE 's%' there is a very
good chance that the index will be utilized, however when you use LIKE
'%s...' that is when you will likely see a change in the plan.
A|||fyi: here's an article that suggest splitting data and indexes,
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=42|||Don't forget to UPDATE STATISTICS also. The select ... where last_name like
's%' query should be able to take advantage of your indexes in its query
plan. You might try running the Index Tuning Wizard from Enterprise Manager
to help optimize your indexes. Based on the scans, it sounds like your
indexes could use some optimization.
"arthur" <alangham@.gmail.com> wrote in message
news:1109265280.958546.16260@.f14g2000cwb.googlegroups.com...
>> unless you can add enough ram and cpus to over come the scans.
> exactly! again, part of the problem w/ scans is that users can search
> (for example on a member) via any of a number of fields, each of which
> have an associated non-clustered index. if they search for a particular
> user, e.g. where last_name = 'smith', then performance is fine,
> however, when they do, where last_name like 's%', performance degrades.
> some of this is a training issue, but still.
> also, we've got a job that runs indexdefrag nightly.
> anyway, i ran some tests last night by creating a copy of one of our
> large tables and putting the associated indexes on a separate drive
> (raid 5). i ran a number of (select) queries against both the large
> table and copy of large table (issuing dbcc freeproccache and
> dropcleanbuffers before each run), and saw a performance difference of
> 0 to 27% depending on the complexity of the query.
> thanks for all your help so far on this!
>sql

index key limitation

I am wondering if there is a limit on key size as far as relates to
performance ?The limit would probably be the same as rowsize / sorting (~8k), but
generally you want to keep your keys as small as possible. You might want
to read
http://www.aspfaq.com/2504
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Aron E. Tekulsky" <atekulsky@.iie.org> wrote in message
news:uXvQXWcQEHA.3732@.TK2MSFTNGP11.phx.gbl...
> I am wondering if there is a limit on key size as far as relates to
> performance ?
>|||User specified index key size limitation is 900 bytes for both clustered and
non-clustsred index.
--
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aron E. Tekulsky" <atekulsky@.iie.org> wrote in message
news:uXvQXWcQEHA.3732@.TK2MSFTNGP11.phx.gbl...
> I am wondering if there is a limit on key size as far as relates to
> performance ?
>

Index granularity

Hi,

I was looking at the new index locking granularity option available in 2005. I did not understand in what case can this be a performance enhancement. Has anybody looked into this ?

Are you talking about sp_indexoption? It is an advanced tuning option and useful in cases where you understand your query pattern / data completely. You do not need to use it normally.|||

Can you post any examples of possible usage? I was seriously considering setting table granularity locks on my data warehouse type tables, especially during the day, when all access is wholly write during ETL, or wholly read during cube build.

Using a read only database is not really convienient, but this should give some of the same properties, right?

|||

Hi,

I am not talking about the sp_indexoption. I am asking about the new index locking granularity in sql server 2005. You have an option to either lock or unlock the index row or the index page. I did a few tests. I started one transaction(session 1) with isolation level as serializable. and executed a select statement.(for this session i set the index locks to off basically meaning that the index locking for row and page is not allowed) In another session(session 2) i tried to execute an update. It did not happen till i committed my query in session 1. This means that though you have requested not to lock the indexes, still the rules of the isolation level prevail. I did not check if table level locks were used. It might happen that when don't allow the row adn page level locks(setting the option to off) might be the locks are escalated to the table level. I am not able to make out as to for what circumstances this feature can be useful ?

Thanks

Sapna

|||Please post some sample code demonstrating the issues. The index options you talked about can be set via CREATE / ALTER INDEX also in SQL Server 2005. This is the only new change otherwise it was available in SQL Server 2000 too via sp_indexoption.|||

There is no issue as such. I just want to know when this option when set ON, can be of any advantage. And what is the situation when this can be a performance issue.

Thanks

Sapna

Index Fragmenation

I'm having trouble with slow performance due to index fragmentation. I'll insert 20000 records into a table and then have index fragmentation of like 67%! The database is so slow query this data back (over 3 minutes!). After rebuilding the index on the primary key (the only index) the same query takes less than 2 seconds. I'm using GUID as my primary key and I have recently switched from using NEWID() to using the new NEWSEQUENTAILID() to generate them. Can anyone suggest why I'm still having such a hard time with fragmentation?Unless you really NEED a GUID because you need an id that HAS to be unique across an entire network, I'd use an integer or bigint. There are a lot of articles out there regarding the performance hit associated with GUID's primarily due to page splits if memory serves. Is your primary key also a clustered index?
|||Yes it is a clustered index. I thought the problem with GUIDs in general is that they are pseudo-random. That is why I went to the newsequentialid() function that generates sequential guids across a machine.|||Index fragementation caused by using GUIDs in indexed columns shouldn't be causing the issues you're seeing.

While it is certainly true that you'll see very high index fragmentation for these columns (our production DB often has 95%+ fragmentation), this isn't necessarily going to kill your performance. We did extensive performance comparisons before decided to go with nearly 100% GUIDs are primary keys. There was certainly a perf difference, but it was negligible overall.

In fact, INSERT performance may actually increase thanks to the fact that disk hot spots are far less common.

I would examine the query plans before/after your defragmentation/rebuild your index. I think something else must be going on here.|||Its very likely that you have out of date statistics on the table that is giving you a bad query plan after the inserts. Rebuilding the index automatically updates statistics. Try just running UPDATE STATISTICS TableName after the INSERT and see if that gives you a better query plan.

Index building performance

I am building some tables with 10s of millions of
records. Each table has 1 or 2 indexes. Is it faster to
create the index when I create the table, then let it
build as I load the data. OR Should I load all my data
then let SQL build the index?
Thanks.Depends really. Say you've got a clustered index on a
table that you are going to bulk insert from a .txt file
into. You would have much better performance if you built
the clustered index after the data was all in place.
However, when you say building tables, do you mean just
insert? Or are you doing any insert/ update type of deals.
We very often do insert/ updates. (If already exists
update where column1 = this else insert.) For this
example, an index on column1 would be very benificial.
insert into
>--Original Message--
>I am building some tables with 10s of millions of
>records. Each table has 1 or 2 indexes. Is it faster to
>create the index when I create the table, then let it
>build as I load the data. OR Should I load all my data
>then let SQL build the index?
>Thanks.
>.
>|||You'll get better performance by loading data into a table with no
indexes and then building the indexes (including primary key and unique
constraints) afterward. The exception is you can load data into a table
with only a clustered index and get good performance if the data is
sorted in clustered index sequence.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"DB" <daveblair@.adelphia.net> wrote in message
news:1384801c38473$1a3b5f70$a601280a@.phx.gbl...
> I am building some tables with 10s of millions of
> records. Each table has 1 or 2 indexes. Is it faster to
> create the index when I create the table, then let it
> build as I load the data. OR Should I load all my data
> then let SQL build the index?
> Thanks.

index & statistics

sql2000
is there some statistics that will show me if some index is missing?
i mean, according to table usage, queries etc, that overall performance
[respond time] would be improved if some column being indexed
i've read that server may refuse to use some existing index during query if
estimated that no performance would be gained?
is server able to by itself create and use temporary index if estimate that
flat table scan is time & disk i/o wasting?
any comments?
thnxSQL Server 2005 keep track of index usage over time and also can suggest you to create new useful
indexes (through some dynamic management views). 2000 do not have that functionality.
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
Yes, SQL Server estimate cost for different ways of executing a query and will use the plan it
considers the cheapest.
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
To create an index, it would have to scan all data. IF the alternative is a table scan in the first
place, why scan all data, create the index, if the alternative was to scan all data in the first
place? Having said that, there are strategies that SQL Server can use which are similar to creating
an index on the fly.
Say you have a join between two tables, and if you were to execute this so that "for each row in
tableA, look for matches in tableB". Now, tableB would be scanned over *several times*. SQL Server
6.5 and earlier could create a "temporary" index on tableB. As of 7.0, we have more modern methods,
like a "hash join", which at a *very* high level could be considered like creating a temporary
index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23W2tx1M1HHA.3788@.TK2MSFTNGP02.phx.gbl...
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance [respond time] would be
> improved if some column being indexed
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
> any comments?
> thnx
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je
napisao u poruci interesnoj
grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>> is server able to by itself create and use temporary index if estimate
>> that flat table scan is time & disk i/o wasting?
> To create an index, it would have to scan all data. IF the alternative is
> a table scan in the first place, why scan all data, create the index, if
> the alternative was to scan all data in the first place?
no! no!
i meant in relation with statistics [having history of usage]
if some query is repeatedly [many times per day] used as flat scan, f.e
like:
select *
from tbl1
where fld1 between val1 and val2
is sql server able to recognize that having index on fld1 may *dramaticaly*
improve performances?|||Hi,
I think you need to consider using the Index Tuning Wizard to help you
out here -
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx.
Jonathan
sali wrote:
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance
> [respond time] would be improved if some column being indexed
> i've read that server may refuse to use some existing index during query if
> estimated that no performance would be gained?
> is server able to by itself create and use temporary index if estimate that
> flat table scan is time & disk i/o wasting?
> any comments?
> thnx
>|||SQL Server 2005 will recognize that fact and you can query the dynamic management views to get this
historical information, and create indexes based on that information. SQL Server 2005 will not
create indexes by itself.
SQL Server 2000 does not have any such functionality, so Index Tuning Wizard, as suggested by
Jonathan can be a tool to consider.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:uSvVWrO1HHA.6072@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je napisao u poruci interesnoj
> grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>> is server able to by itself create and use temporary index if estimate that flat table scan is
>> time & disk i/o wasting?
>> To create an index, it would have to scan all data. IF the alternative is a table scan in the
>> first place, why scan all data, create the index, if the alternative was to scan all data in the
>> first place?
> no! no!
> i meant in relation with statistics [having history of usage]
> if some query is repeatedly [many times per day] used as flat scan, f.e like:
> select *
> from tbl1
> where fld1 between val1 and val2
> is sql server able to recognize that having index on fld1 may *dramaticaly* improve performances?
>
>