List availables tables: index

Drive space is being eaten away and we are still a couple of weeks from
moving to a newer larger server. There is concern over what is filling up
the data drives (not log) so quickly and there is a list of things to do that
are pretty drastic...
1-Drop indexes - not sure how to do this correctly - can indexes be dropped
if an index with the same number of items in it plus some extras are already
existing - and will this retrieve some disk space?
2-Archive more data to the data warehouse - most all that can be archived is
archived but there may be more we can do
3-Add ram - already done to the max
4-change all switches to gigabit switches (in process)
5-can the 10 templog files that are on the E:\ drive be removed and all temp
file logs go to the F:\ drive where space is adequate (both are raid 5) - not
sure how to remove the ten on the E drive - the big one is already on F:\
6-Can't think of one but would like to hear feedback
SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using the
\PAE boot.ini switch. Running Four processors...with affinity mask for all
400 maximum worker threads with Boost SQL Server - single processor used for
parallel processing, memory configured dynamically with a 1 Meg Minimum and
using configured values
--
Regards,
JamieHi
I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
> Drive space is being eaten away and we are still a couple of weeks from
> moving to a newer larger server. There is concern over what is filling up
> the data drives (not log) so quickly and there is a list of things to do
> that
> are pretty drastic...
> 1-Drop indexes - not sure how to do this correctly - can indexes be
> dropped
> if an index with the same number of items in it plus some extras are
> already
> existing - and will this retrieve some disk space?
> 2-Archive more data to the data warehouse - most all that can be archived
> is
> archived but there may be more we can do
> 3-Add ram - already done to the max
> 4-change all switches to gigabit switches (in process)
> 5-can the 10 templog files that are on the E:\ drive be removed and all
> temp
> file logs go to the F:\ drive where space is adequate (both are raid 5) -
> not
> sure how to remove the ten on the E drive - the big one is already on F:\
> 6-Can't think of one but would like to hear feedback
> SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using the
> \PAE boot.ini switch. Running Four processors...with affinity mask for
> all
> 400 maximum worker threads with Boost SQL Server - single processor used
> for
> parallel processing, memory configured dynamically with a 1 Meg Minimum
> and
> using configured values
> --
> Regards,
> Jamie|||No, not tempdb. It was actually our EDI server which accepts transmission
from partners. We're just running on the edge of space and time. I'm
looking for any improvement I can get. I optimized some views and added
indexing for them but adding more indexes will also add more disk space. I
don't know how to remove (other than random delete) indexes if they are not
needed. What would be the criteria to drop an index? IS there a way to
tell it is no longer needed? To the statistics tell me anything?
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
>
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
> > Drive space is being eaten away and we are still a couple of weeks from
> > moving to a newer larger server. There is concern over what is filling up
> > the data drives (not log) so quickly and there is a list of things to do
> > that
> > are pretty drastic...
> > 1-Drop indexes - not sure how to do this correctly - can indexes be
> > dropped
> > if an index with the same number of items in it plus some extras are
> > already
> > existing - and will this retrieve some disk space?
> > 2-Archive more data to the data warehouse - most all that can be archived
> > is
> > archived but there may be more we can do
> > 3-Add ram - already done to the max
> > 4-change all switches to gigabit switches (in process)
> > 5-can the 10 templog files that are on the E:\ drive be removed and all
> > temp
> > file logs go to the F:\ drive where space is adequate (both are raid 5) -
> > not
> > sure how to remove the ten on the E drive - the big one is already on F:\
> > 6-Can't think of one but would like to hear feedback
> > SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using the
> > \PAE boot.ini switch. Running Four processors...with affinity mask for
> > all
> > 400 maximum worker threads with Boost SQL Server - single processor used
> > for
> > parallel processing, memory configured dynamically with a 1 Meg Minimum
> > and
> > using configured values
> > --
> > Regards,
> > Jamie
>
>|||Hi
I'd turn on SQL Server Profiler and try to identify long running queries
(group by Duration/Reads/CPU) and then you have to optimize them (add/drop
indexes)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A249BF2D-905A-4C52-BE9F-1B8DE6A1BB06@.microsoft.com...
> No, not tempdb. It was actually our EDI server which accepts transmission
> from partners. We're just running on the edge of space and time. I'm
> looking for any improvement I can get. I optimized some views and added
> indexing for them but adding more indexes will also add more disk space.
> I
> don't know how to remove (other than random delete) indexes if they are
> not
> needed. What would be the criteria to drop an index? IS there a way to
> tell it is no longer needed? To the statistics tell me anything?
> --
> Regards,
> Jamie
>
> "Uri Dimant" wrote:
>> Hi
>> I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
>>
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
>> > Drive space is being eaten away and we are still a couple of weeks from
>> > moving to a newer larger server. There is concern over what is filling
>> > up
>> > the data drives (not log) so quickly and there is a list of things to
>> > do
>> > that
>> > are pretty drastic...
>> > 1-Drop indexes - not sure how to do this correctly - can indexes be
>> > dropped
>> > if an index with the same number of items in it plus some extras are
>> > already
>> > existing - and will this retrieve some disk space?
>> > 2-Archive more data to the data warehouse - most all that can be
>> > archived
>> > is
>> > archived but there may be more we can do
>> > 3-Add ram - already done to the max
>> > 4-change all switches to gigabit switches (in process)
>> > 5-can the 10 templog files that are on the E:\ drive be removed and all
>> > temp
>> > file logs go to the F:\ drive where space is adequate (both are raid
>> > 5) -
>> > not
>> > sure how to remove the ten on the E drive - the big one is already on
>> > F:\
>> > 6-Can't think of one but would like to hear feedback
>> > SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using
>> > the
>> > \PAE boot.ini switch. Running Four processors...with affinity mask for
>> > all
>> > 400 maximum worker threads with Boost SQL Server - single processor
>> > used
>> > for
>> > parallel processing, memory configured dynamically with a 1 Meg Minimum
>> > and
>> > using configured values
>> > --
>> > Regards,
>> > Jamie
>>|||I've been doing just that - that is, except the part where you drop indexes.
If the profiler happens to optimize something that runs at 8 AM and it
doesn't run again til 5 AM and recommends that an index be dropped because it
doesn't see usage on it and then about noon, that index is needed - but it
has been dropped and the system goes to its knees, I'm better off living with
the current issues. I think the answer is to take an entire day of profile
and optimize against that. Seems a bit extreme. Is there another way?
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> I'd turn on SQL Server Profiler and try to identify long running queries
> (group by Duration/Reads/CPU) and then you have to optimize them (add/drop
> indexes)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A249BF2D-905A-4C52-BE9F-1B8DE6A1BB06@.microsoft.com...
> > No, not tempdb. It was actually our EDI server which accepts transmission
> > from partners. We're just running on the edge of space and time. I'm
> > looking for any improvement I can get. I optimized some views and added
> > indexing for them but adding more indexes will also add more disk space.
> > I
> > don't know how to remove (other than random delete) indexes if they are
> > not
> > needed. What would be the criteria to drop an index? IS there a way to
> > tell it is no longer needed? To the statistics tell me anything?
> > --
> > Regards,
> > Jamie
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
> >>
> >>
> >>
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
> >> > Drive space is being eaten away and we are still a couple of weeks from
> >> > moving to a newer larger server. There is concern over what is filling
> >> > up
> >> > the data drives (not log) so quickly and there is a list of things to
> >> > do
> >> > that
> >> > are pretty drastic...
> >> > 1-Drop indexes - not sure how to do this correctly - can indexes be
> >> > dropped
> >> > if an index with the same number of items in it plus some extras are
> >> > already
> >> > existing - and will this retrieve some disk space?
> >> > 2-Archive more data to the data warehouse - most all that can be
> >> > archived
> >> > is
> >> > archived but there may be more we can do
> >> > 3-Add ram - already done to the max
> >> > 4-change all switches to gigabit switches (in process)
> >> > 5-can the 10 templog files that are on the E:\ drive be removed and all
> >> > temp
> >> > file logs go to the F:\ drive where space is adequate (both are raid
> >> > 5) -
> >> > not
> >> > sure how to remove the ten on the E drive - the big one is already on
> >> > F:\
> >> > 6-Can't think of one but would like to hear feedback
> >> > SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using
> >> > the
> >> > \PAE boot.ini switch. Running Four processors...with affinity mask for
> >> > all
> >> > 400 maximum worker threads with Boost SQL Server - single processor
> >> > used
> >> > for
> >> > parallel processing, memory configured dynamically with a 1 Meg Minimum
> >> > and
> >> > using configured values
> >> > --
> >> > Regards,
> >> > Jamie
> >>
> >>
> >>
>
>|||Hi
http://www.sql-server-performance.com/articles/per/finding_duplicate_indexes_p1.aspx
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:7A3271B1-31DB-45A4-8882-89E3AD40D5BD@.microsoft.com...
> I've been doing just that - that is, except the part where you drop
> indexes.
> If the profiler happens to optimize something that runs at 8 AM and it
> doesn't run again til 5 AM and recommends that an index be dropped because
> it
> doesn't see usage on it and then about noon, that index is needed - but it
> has been dropped and the system goes to its knees, I'm better off living
> with
> the current issues. I think the answer is to take an entire day of
> profile
> and optimize against that. Seems a bit extreme. Is there another way?
> --
> Regards,
> Jamie
>
> "Uri Dimant" wrote:
>> Hi
>> I'd turn on SQL Server Profiler and try to identify long running queries
>> (group by Duration/Reads/CPU) and then you have to optimize them
>> (add/drop
>> indexes)
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:A249BF2D-905A-4C52-BE9F-1B8DE6A1BB06@.microsoft.com...
>> > No, not tempdb. It was actually our EDI server which accepts
>> > transmission
>> > from partners. We're just running on the edge of space and time. I'm
>> > looking for any improvement I can get. I optimized some views and
>> > added
>> > indexing for them but adding more indexes will also add more disk
>> > space.
>> > I
>> > don't know how to remove (other than random delete) indexes if they are
>> > not
>> > needed. What would be the criteria to drop an index? IS there a way
>> > to
>> > tell it is no longer needed? To the statistics tell me anything?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
>> >>
>> >>
>> >>
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
>> >> > Drive space is being eaten away and we are still a couple of weeks
>> >> > from
>> >> > moving to a newer larger server. There is concern over what is
>> >> > filling
>> >> > up
>> >> > the data drives (not log) so quickly and there is a list of things
>> >> > to
>> >> > do
>> >> > that
>> >> > are pretty drastic...
>> >> > 1-Drop indexes - not sure how to do this correctly - can indexes be
>> >> > dropped
>> >> > if an index with the same number of items in it plus some extras are
>> >> > already
>> >> > existing - and will this retrieve some disk space?
>> >> > 2-Archive more data to the data warehouse - most all that can be
>> >> > archived
>> >> > is
>> >> > archived but there may be more we can do
>> >> > 3-Add ram - already done to the max
>> >> > 4-change all switches to gigabit switches (in process)
>> >> > 5-can the 10 templog files that are on the E:\ drive be removed and
>> >> > all
>> >> > temp
>> >> > file logs go to the F:\ drive where space is adequate (both are raid
>> >> > 5) -
>> >> > not
>> >> > sure how to remove the ten on the E drive - the big one is already
>> >> > on
>> >> > F:\
>> >> > 6-Can't think of one but would like to hear feedback
>> >> > SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using
>> >> > the
>> >> > \PAE boot.ini switch. Running Four processors...with affinity mask
>> >> > for
>> >> > all
>> >> > 400 maximum worker threads with Boost SQL Server - single processor
>> >> > used
>> >> > for
>> >> > parallel processing, memory configured dynamically with a 1 Meg
>> >> > Minimum
>> >> > and
>> >> > using configured values
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >>
>> >>
>> >>
>>|||See in-line for comments:
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
> Drive space is being eaten away and we are still a couple of weeks from
> moving to a newer larger server. There is concern over what is filling up
> the data drives (not log) so quickly and there is a list of things to do
> that
> are pretty drastic...
I hope the new server is better configured than the current one or you will
have similar issues. From the following comments you posted this system does
not appear to be configured properly. If you carry these same concepts over
to the new machine you may not have gained much. Before you change any
setting from the default you beet have a valid reason and know what that
change will do in advance.
> 1-Drop indexes - not sure how to do this correctly - can indexes be
> dropped
> if an index with the same number of items in it plus some extras are
> already
> existing - and will this retrieve some disk space?
That is too general a question to answer correctly with a yes or no. If the
index is truely duplicate then yes it can be removed. If you have indexes
with lots of columns then unless they are really being used effectively as
covering indexes you may be able to drop them in favor of smaller but
similar indexes. If for instance the smaller index is very selective already
the other columns may not add more overhead than necessary if again they are
not effective as a covering index.
> 2-Archive more data to the data warehouse - most all that can be archived
> is
> archived but there may be more we can do
Only you can decide that
> 3-Add ram - already done to the max
What will that do to alleviate your disk space issues?
> 4-change all switches to gigabit switches (in process)
Again that does nothign for disk space issues but is never a bad idea.
> 5-can the 10 templog files that are on the E:\ drive be removed and all
> temp
> file logs go to the F:\ drive where space is adequate (both are raid 5) -
> not
> sure how to remove the ten on the E drive - the big one is already on F:\
Why do you have 10 log files for any database? You gain nothing by having
more than 1 log file per db on a single array.
> 6-Can't think of one but would like to hear feedback
> SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using the
> \PAE boot.ini switch. Running Four processors...with affinity mask for
> all
> 400 maximum worker threads with Boost SQL Server - single processor used
> for
> parallel processing, memory configured dynamically with a 1 Meg Minimum
> and
> using configured values
SQL 2000 Std will only use 2GB max so the 6GB you have is mostly wasted. Why
do you have the affinity mask set? Why did you change the MAX worker thread
count? Turn off the Boost priority.
> --
> Regards,
> Jamie

hi guru's

would appreciate if someone could show how to list all the clustered
indexes in the database.

if it can done as a output of single query it would be fine. the output
should be the table name, column name and clustered index name.

thanx
balaI don't think it can be done in a single query, but you can do it like
so:

--declare variables and temp table for accumulation
DECLARE @.tName varchar(200)
CREATE TABLE #t (table_name varchar(200),
index_name varchar(200),
index_description varchar(210),
index_keys nvarchar(2078))

--open cursor for user tables
DECLARE C CURSOR LOCAL FOR
SELECT name
FROM sysobjects
WHERE xtype = 'U'

OPEN C

FETCH NEXT FROM c INTO @.tname

WHILE @.@.FETCH_STATUS = 0
BEGIN

--run sp_helpindex against table in cursor
INSERT INTO #t (index_name, index_description, index_keys)
exec sp_helpindex @.tname

--since sp_helpindex doesn't return a table name,
--have to update the current NULL table_name
UPDATE #t
SET table_name = @.tname
WHERE table_name is NULL

--Loop by getting next row from cursor
FETCH NEXT FROM c INTO @.tname
END

CLOSE C

DEALLOCATE C

--retrieve specified data; limit it to clustered indexes
SELECT table_name, index_name, index_keys
FROM #t
WHERE index_description like 'clustered%'

DROP TABLE #t

HTH,
Stu|||hey stu

thanx for the quick response. will try it out tomorrow in office

regards
bala|||This should work too.

select object_name(id) as table_name, name as index_name
from sysindexes
where indid = 1|||bala (balkiir@.gmail.com) writes:
> would appreciate if someone could show how to list all the clustered
> indexes in the database.
> if it can done as a output of single query it would be fine. the output
> should be the table name, column name and clustered index name.

Here is a query:

SELECT tblname = CASE WHEN ik.keyno = 1 THEN o.name ELSE '' END,
ixname = CASE WHEN ik.keyno = 1 THEN i.name ELSE '' END,
ik.keyno, colname = c.name,
isdesc = CASE indexkey_property(o.id, i.indid, ik.keyno,
'IsDescending')
WHEN 1 THEN 'DESC'
ELSE ''
END
FROM sysobjects o
JOIN sysindexes i ON o.id = i.id
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
JOIN syscolumns c ON c.id = ik.id
AND c.colid = ik.colid
WHERE i.indid = 1
ORDER BY o.name, ik.keyno

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ahhhh; I didn't even see the sysindexkeys table. I tried doing it with
INDEX_COL(), but it was a miserable failure.

Stu|||thanx guys. the pointer towards the right direction is much appreciated

bala

Monday, March 26, 2012

Lo Disk Space-archive?- index reduction-slow server, need assistan

Monday, March 12, 2012

List Property - can grow/can shrink

Wednesday, March 7, 2012

list of clustered index in the database

List availables tables

Blog Archive

About Me