Index Tuning Wizard

Hi all,
I'd like to use Index Tuning Wizard. I created a workload trace file with
SQL Profiler, then I start the wizard but it ends up with message that there
are no events and queries that can be tuned against current database.
I used trace template 'SQLProfilerTuning.tdf', so there are only
RPC:Completed and SQL:BatchCompleted events, which should be OK, but may be
it is a problem that we use stored procedures for data manipulation, in fact
there is no SQL query called directly from our application, we always call
SP and pass parameters to it. Is Index Tuning Wizard tool limitid only for
queries? Or may I achieve it to work using events for Stored Procedures?
Any advice appretiated.
eXavierI havent used the Index Tuning Wizard much myself, but I
would say that well normalised tables (using the correct
fields for and with the correct indexes) and well written
procs would be more fruitful than using the Wizard.
>--Original Message--
>Hi all,
>I'd like to use Index Tuning Wizard. I created a workload
trace file with
>SQL Profiler, then I start the wizard but it ends up with
message that there
>are no events and queries that can be tuned against
current database.
>I used trace template 'SQLProfilerTuning.tdf', so there
are only
>RPC:Completed and SQL:BatchCompleted events, which should
be OK, but may be
>it is a problem that we use stored procedures for data
manipulation, in fact
>there is no SQL query called directly from our
application, we always call
>SP and pass parameters to it. Is Index Tuning Wizard tool
limitid only for
>queries? Or may I achieve it to work using events for
Stored Procedures?
>Any advice appretiated.
>eXavier
>
>.
>

Index Tuning Problem

Hello:
I am attempting to tune the indexes on a frequently used
SQL 2000 (Enterprise Edition)database. I used Profiler
to create a workload file that captured 3/4 of a day's
transactions.
I started the Index Tuning Wizard and went through the
steps to have the wizard analyze my workload file. After
analyzing the database for awhile, the wizard returns the
following message:
The workload does not contain any events or queries that
can be tuned against the current database. Check to see
that the trace contains valid events or that the SQL
script contains valid queries and you are tuning against
the right database.
The workload file is close to 5GB in size and definitely
contains stored procedures and SQL statements. And I am
definitely sitting in the proper database.
Any comments or suggestions would be greatly appreciated
BrennanThis is a multi-part message in MIME format.
--=_NextPart_000_08A3_01C39C8E.8B8E6540
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You may need to save it as a script and then put in a use statement.
Otherwise, it will assume master. For example, if your database is MyDB:
use MyDB
go
-- the rest of the script goes here
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:011601c39cb7$c7373c60$a401280a@.phx.gbl...
Hello:
I am attempting to tune the indexes on a frequently used
SQL 2000 (Enterprise Edition)database. I used Profiler
to create a workload file that captured 3/4 of a day's
transactions.
I started the Index Tuning Wizard and went through the
steps to have the wizard analyze my workload file. After
analyzing the database for awhile, the wizard returns the
following message:
The workload does not contain any events or queries that
can be tuned against the current database. Check to see
that the trace contains valid events or that the SQL
script contains valid queries and you are tuning against
the right database.
The workload file is close to 5GB in size and definitely
contains stored procedures and SQL statements. And I am
definitely sitting in the proper database.
Any comments or suggestions would be greatly appreciated
Brennan
--=_NextPart_000_08A3_01C39C8E.8B8E6540
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You may need to save it as a script =and then put in a use statement. Otherwise, it will assume master. For =example, if your database is MyDB:
use MyDB
go
-- the rest of the script goes here
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brennan" wrote in message news:011601c39cb7$c7=373c60$a401280a@.phx.gbl...Hello:I am attempting to tune the indexes on a frequently used SQL 2000 =(Enterprise Edition)database. I used Profiler to create a workload file =that captured 3/4 of a day's transactions.I started the Index =Tuning Wizard and went through the steps to have the wizard analyze my =workload file. After analyzing the database for awhile, the wizard =returns the following message:The workload does not contain any events =or queries that can be tuned against the current database. Check =to see that the trace contains valid events or that the SQL script =contains valid queries and you are tuning against the right =database.The workload file is close to 5GB in size and definitely contains stored =procedures and SQL statements. And I am definitely sitting in =the proper database.Any comments or suggestions would be greatly =appreciated Brennan

---

--=_NextPart_000_08A3_01C39C8E.8B8E6540--|||How do I save it as a script?
Brennan
>--Original Message--
>You may need to save it as a script and then put in a
use statement.
>Otherwise, it will assume master. For example, if your
database is MyDB:
>use MyDB
>go
>-- the rest of the script goes here
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:011601c39cb7$c7373c60$a401280a@.phx.gbl...
>Hello:
>I am attempting to tune the indexes on a frequently used
>SQL 2000 (Enterprise Edition)database. I used Profiler
>to create a workload file that captured 3/4 of a day's
>transactions.
>I started the Index Tuning Wizard and went through the
>steps to have the wizard analyze my workload file. After
>analyzing the database for awhile, the wizard returns the
>following message:
>The workload does not contain any events or queries that
>can be tuned against the current database. Check to see
>that the trace contains valid events or that the SQL
>script contains valid queries and you are tuning against
>the right database.
>The workload file is close to 5GB in size and definitely
>contains stored procedures and SQL statements. And I am
>definitely sitting in the proper database.
>Any comments or suggestions would be greatly appreciated
>Brennan
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0944_01C39C96.6BD78620
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
I was referring to your workload script. You can load your trace into the
Profiler and then click on File->Save As->SQL Script.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:038301c39cbf$4da0b450$a101280a@.phx.gbl...
How do I save it as a script?
Brennan
>--Original Message--
>You may need to save it as a script and then put in a
use statement.
>Otherwise, it will assume master. For example, if your
database is MyDB:
>use MyDB
>go
>-- the rest of the script goes here
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:011601c39cb7$c7373c60$a401280a@.phx.gbl...
>Hello:
>I am attempting to tune the indexes on a frequently used
>SQL 2000 (Enterprise Edition)database. I used Profiler
>to create a workload file that captured 3/4 of a day's
>transactions.
>I started the Index Tuning Wizard and went through the
>steps to have the wizard analyze my workload file. After
>analyzing the database for awhile, the wizard returns the
>following message:
>The workload does not contain any events or queries that
>can be tuned against the current database. Check to see
>that the trace contains valid events or that the SQL
>script contains valid queries and you are tuning against
>the right database.
>The workload file is close to 5GB in size and definitely
>contains stored procedures and SQL statements. And I am
>definitely sitting in the proper database.
>Any comments or suggestions would be greatly appreciated
>Brennan
>
--=_NextPart_000_0944_01C39C96.6BD78620
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I was referring to your workload =script. You can load your trace into the Profiler and then click on File->Save =As->SQL Script.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brennan" wrote in message news:038301c39cbf$4d=a0b450$a101280a@.phx.gbl...How do I save it as a script?Brennan>--Original Message-->You may need to save it as a script and then put in =a use statement.>Otherwise, it will assume master. For =example, if your database is MyDB:>>use =MyDB>go>-- the rest of the script goes here>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Bren=nan" wrote in message>news:011601c39cb7$c7373c60$a401280a@.phx.gbl...>=Hello:>>I am attempting to tune the indexes on a frequently used>SQL 2000 (Enterprise Edition)database. I used Profiler>to create a =workload file that captured 3/4 of a day's>transactions.>>I =started the Index Tuning Wizard and went through the>steps to have the =wizard analyze my workload file. After>analyzing the database for =awhile, the wizard returns the>following message:>>The =workload does not contain any events or queries that>can be tuned against =the current database. Check to see>that the trace contains =valid events or that the SQL>script contains valid queries and you are tuning against>the right database.>>The workload file is =close to 5GB in size and definitely>contains stored procedures and SQL statements. And I am>definitely sitting in the proper database.>>Any comments or suggestions would be greatly appreciated>>Brennan>

---

--=_NextPart_000_0944_01C39C96.6BD78620--

Index tuning

Hi all,
I created load file (with few test queries) via Profiler and then I tried to
run tuning wizard. Wizards gave message "Workload does not contain any
events or queries that can be tuned against current database ..."
It's strange because load file contains events with queries and this queries
were run over current database (I also set filter condition for database
name to be sure ...)..
Any ideas what am I doing wrong ?
Regards,
Jan
Jan Hruz wrote:
> Hi all,
> I created load file (with few test queries) via Profiler and then I
> tried to run tuning wizard. Wizards gave message "Workload does not
> contain any events or queries that can be tuned against current
> database ..." It's strange because load file contains events with
> queries and this
> queries were run over current database (I also set filter condition
> for database name to be sure ...)..
> Any ideas what am I doing wrong ?
> Regards,
> Jan
I think the ITW requires SQL:BatchCompleted and RPC:Completed events
with the necessary columns. Please post the events and columns that were
captured as well as any filters you used during the trace.
David Gugick
Imceda Software
www.imceda.com
|||I used ProfilerTuning template. it is setup to trace events :
- RPC:Completed
- SQL:BatchCompleted
and data columns :
- EventClass
- TextData
- Duration
- BinaryData
- SPID
I added new filted condition (to track only my requests):
- DBUserName
- DatabaseName
Regards,
Jan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O604jpHRFHA.3288@.TK2MSFTNGP14.phx.gbl...
> Jan Hruz wrote:
> I think the ITW requires SQL:BatchCompleted and RPC:Completed events with
> the necessary columns. Please post the events and columns that were
> captured as well as any filters you used during the trace.
> --
> David Gugick
> Imceda Software
> www.imceda.com

Index rebuild

I run dbcc dbreindex command and send the output to the text file. Is there any other way to check that indexes were in fact rebuild?You could do a before and after DBCC SHOWCONTIG. But, if DBREINDEX retunrs no errors, it ran successfully.

Index file grown bigger than data file day by day

Hi all,

I have a serious problem:

I created 1 database with 2 file group : 1 primary and 1 index.
- Primary file group includes 1 data file (*.mdf): store all tables
- Index file group includes 1 index file (*.ndf): store all indexes

Most of indexes are non-cluster indexes

After a short time using, data file is 2GB but index file is 12 GB.
I do not know what problem happened in my database.
I have some questions:

1/ How so I reduce size of index file ?
2/ How to know what is stored in index file?
3/ How to trace all impact to index file?
4/ How to limit size growing of index file?

Thanks in advanced

Thi NguyenIf your tables aren't updated often I believe the size of index is reduce with a higher fillfactor (like 99). Also check if your indexes are fragmented (see DBCC SHOWCONTIG). You can compact indexes with DBCC INDEXDEFRAG|||thanks for your help

Thi Nguyen

Index file grown bigger than data file day by day

Hi all,
I have a serious problem:
I created 1 database with 2 file group : 1 primary and 1 index.
- Primary file group includes 1 data file (*.mdf): store all tables
- Index file group includes 1 index file (*.ndf): store all index
after a short time using, data file is 2GB but index file is 12 GB.
I do not know what problem with my database.
I have some questions:
1/ How so I reduce size of index file ?
2/ How to know what is stored in index file?
3/ How to trace all impact to index file?
4/ How to limit size growing of index file?
Thanks in advanced
TN
"TN" <TN@.discussions.microsoft.com> wrote in message
news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> Hi all,
> I have a serious problem:
> I created 1 database with 2 file group : 1 primary and 1 index.
> - Primary file group includes 1 data file (*.mdf): store all tables
> - Index file group includes 1 index file (*.ndf): store all index
> after a short time using, data file is 2GB but index file is 12 GB.
> I do not know what problem with my database.
> I have some questions:
> 1/ How so I reduce size of index file ?
> 2/ How to know what is stored in index file?
Here's how to show the allocation of objects to file groups.
SELECT
OBJECT_NAME(id) TABLE_NAME,
CASE WHEN INDID IN (0,1) THEN NULL ELSE NAME END INDEX_NAME,
FILEGROUP_NAME(groupid) AS FILEGROUP_NAME,
dpages PAGES
FROM sysindexes
ORDER BY TABLE_NAME, INDEX_NAME

> 3/ How to trace all impact to index file?
> 4/ How to limit size growing of index file?
The distinction between data and index data is shaky at best. It's common
practice in Sql Server to use clustered indexes on many tables. Any table
with a clustered index has nothing but "index data". Queries will use
multiple indexes as often as they will use an index and "table data".
David
|||When laying out tables accross filegroups, it's really important to
understand the difference between clustered and non-clustered indexes. When
a clustered index exists on a table, it actually IS the table so if you
intended to seperate row storage from index storage, you probably meant to
store the clustered indexes on the primary filegroup and the non-clustered
indexes on the index filegroup.
Regards,
Greg Linwood
SQL Server MVP
"TN" <TN@.discussions.microsoft.com> wrote in message
news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> Hi all,
> I have a serious problem:
> I created 1 database with 2 file group : 1 primary and 1 index.
> - Primary file group includes 1 data file (*.mdf): store all tables
> - Index file group includes 1 index file (*.ndf): store all index
> after a short time using, data file is 2GB but index file is 12 GB.
> I do not know what problem with my database.
> I have some questions:
> 1/ How so I reduce size of index file ?
> 2/ How to know what is stored in index file?
> 3/ How to trace all impact to index file?
> 4/ How to limit size growing of index file?
> Thanks in advanced
> TN
|||Hi David,
Thanks for your advice.
I do not know which is better between : index and data are stored in the
same file (data file ) or index and data are stored in separately 2 file
(index file and data file).
Please tell me what should I change to reduce index file growth.
Thanks
TN
"David Browne" wrote:

> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> Here's how to show the allocation of objects to file groups.
> SELECT
> OBJECT_NAME(id) TABLE_NAME,
> CASE WHEN INDID IN (0,1) THEN NULL ELSE NAME END INDEX_NAME,
> FILEGROUP_NAME(groupid) AS FILEGROUP_NAME,
> dpages PAGES
> FROM sysindexes
> ORDER BY TABLE_NAME, INDEX_NAME
>
> The distinction between data and index data is shaky at best. It's common
> practice in Sql Server to use clustered indexes on many tables. Any table
> with a clustered index has nothing but "index data". Queries will use
> multiple indexes as often as they will use an index and "table data".
> David
>
>
|||"TN" <TN@.discussions.microsoft.com> wrote in message
news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> Hi all,
> I have a serious problem:
> I created 1 database with 2 file group : 1 primary and 1 index.
> - Primary file group includes 1 data file (*.mdf): store all tables
> - Index file group includes 1 index file (*.ndf): store all index
> after a short time using, data file is 2GB but index file is 12 GB.
> I do not know what problem with my database.
> I have some questions:
> 1/ How so I reduce size of index file ?
> 2/ How to know what is stored in index file?
> 3/ How to trace all impact to index file?
> 4/ How to limit size growing of index file?
Watch your *clustered* indexes.
You've likely created your clustered indexes on your index file
group. If you did, your data is on the index file group and not
on your data file group.
You'll want to put all of your indexes *except* your clustered
indexes on the index file group. Your *clustered* indexes should
be created on the data (primary) filegroup.
|||Hi Sgt. Sausage,
Most of indexes in my database are non-cluster indexes and created on Index
filegroup.
Please help me more.
Thanks
TN
"Sgt. Sausage" wrote:

> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> Watch your *clustered* indexes.
> You've likely created your clustered indexes on your index file
> group. If you did, your data is on the index file group and not
> on your data file group.
> You'll want to put all of your indexes *except* your clustered
> indexes on the index file group. Your *clustered* indexes should
> be created on the data (primary) filegroup.
>
>
|||first thing, what is your physical disk setup? if you are on raid 5 or
raid 0+1 then having seperate file groups does nothing for performance.
if you are on raid 1 and have 2 seperate mirrored drive pairs, then
seperate file groups would help. filegroup seperation is mainly used
for backup and recovery purposes or if you are in a non-raid setup. if
you are on a raid setup, i would move everything back to one filegroup
and be done with it.
as stated above, you have to make sure that all of your clustered
indexes are on your primary filegroup, otherwise the data will be in
the wrong filegroup. indexes have what is called a leaf level...in a
clusterd index, that level is the actual data.....so, when you tell
sql server to create an index on a file group, if it happens to be a
clustered index, it will move the table over as well. i am not sure of
you index file group name so you will have to edit this, but this is
the code from above slightly modified:
SELECT
OBJECT_NAME(id) as TABLE_NAME,
NAME as INDEX_NAME,
FILEGROUP_NAME(groupid) as FILEGROUP_NAME,
dpages PAGES
FROM sysindexes
WHERE FILEGROUP_NAME(groupid) = '<name_of_index_filegroup>'
AND indid in (0,1)
ORDER BY TABLE_NAME, INDEX_NAME
this will let you know if you have any clustered indexes on the wrong
filegroup.
i cannot tell you why your databases is growing so rapidly without
knowing more detail:
remember, indexes are for select statements. if you have a heavy dml
(updates and inserts) then your indexes are going to hurt your system.
hth,
hans nelsen

Index file grown bigger than data file day by day

Hi all,
I have a serious problem:
I created 1 database with 2 file group : 1 primary and 1 index.
- Primary file group includes 1 data file (*.mdf): store all tables
- Index file group includes 1 index file (*.ndf): store all index
after a short time using, data file is 2GB but index file is 12 GB.
I do not know what problem with my database.
I have some questions:
1/ How so I reduce size of index file ?
2/ How to know what is stored in index file?
3/ How to trace all impact to index file?
4/ How to limit size growing of index file?
Thanks in advanced
TN"TN" <TN@.discussions.microsoft.com> wrote in message
news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> Hi all,
> I have a serious problem:
> I created 1 database with 2 file group : 1 primary and 1 index.
> - Primary file group includes 1 data file (*.mdf): store all tables
> - Index file group includes 1 index file (*.ndf): store all index
> after a short time using, data file is 2GB but index file is 12 GB.
> I do not know what problem with my database.
> I have some questions:
> 1/ How so I reduce size of index file ?
> 2/ How to know what is stored in index file?
Here's how to show the allocation of objects to file groups.
SELECT
OBJECT_NAME(id) TABLE_NAME,
CASE WHEN INDID IN (0,1) THEN NULL ELSE NAME END INDEX_NAME,
FILEGROUP_NAME(groupid) AS FILEGROUP_NAME,
dpages PAGES
FROM sysindexes
ORDER BY TABLE_NAME, INDEX_NAME
> 3/ How to trace all impact to index file?
> 4/ How to limit size growing of index file?
The distinction between data and index data is shaky at best. It's common
practice in Sql Server to use clustered indexes on many tables. Any table
with a clustered index has nothing but "index data". Queries will use
multiple indexes as often as they will use an index and "table data".
David|||When laying out tables accross filegroups, it's really important to
understand the difference between clustered and non-clustered indexes. When
a clustered index exists on a table, it actually IS the table so if you
intended to seperate row storage from index storage, you probably meant to
store the clustered indexes on the primary filegroup and the non-clustered
indexes on the index filegroup.
Regards,
Greg Linwood
SQL Server MVP
"TN" <TN@.discussions.microsoft.com> wrote in message
news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> Hi all,
> I have a serious problem:
> I created 1 database with 2 file group : 1 primary and 1 index.
> - Primary file group includes 1 data file (*.mdf): store all tables
> - Index file group includes 1 index file (*.ndf): store all index
> after a short time using, data file is 2GB but index file is 12 GB.
> I do not know what problem with my database.
> I have some questions:
> 1/ How so I reduce size of index file ?
> 2/ How to know what is stored in index file?
> 3/ How to trace all impact to index file?
> 4/ How to limit size growing of index file?
> Thanks in advanced
> TN|||Hi David,
Thanks for your advice.
I do not know which is better between : index and data are stored in the
same file (data file ) or index and data are stored in separately 2 file
(index file and data file).
Please tell me what should I change to reduce index file growth.
Thanks
TN
"David Browne" wrote:
> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> > Hi all,
> >
> > I have a serious problem:
> >
> > I created 1 database with 2 file group : 1 primary and 1 index.
> > - Primary file group includes 1 data file (*.mdf): store all tables
> > - Index file group includes 1 index file (*.ndf): store all index
> >
> > after a short time using, data file is 2GB but index file is 12 GB.
> > I do not know what problem with my database.
> > I have some questions:
> >
> > 1/ How so I reduce size of index file ?
> > 2/ How to know what is stored in index file?
> Here's how to show the allocation of objects to file groups.
> SELECT
> OBJECT_NAME(id) TABLE_NAME,
> CASE WHEN INDID IN (0,1) THEN NULL ELSE NAME END INDEX_NAME,
> FILEGROUP_NAME(groupid) AS FILEGROUP_NAME,
> dpages PAGES
> FROM sysindexes
> ORDER BY TABLE_NAME, INDEX_NAME
>
> > 3/ How to trace all impact to index file?
> > 4/ How to limit size growing of index file?
> The distinction between data and index data is shaky at best. It's common
> practice in Sql Server to use clustered indexes on many tables. Any table
> with a clustered index has nothing but "index data". Queries will use
> multiple indexes as often as they will use an index and "table data".
> David
>
>|||"TN" <TN@.discussions.microsoft.com> wrote in message
news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> Hi all,
> I have a serious problem:
> I created 1 database with 2 file group : 1 primary and 1 index.
> - Primary file group includes 1 data file (*.mdf): store all tables
> - Index file group includes 1 index file (*.ndf): store all index
> after a short time using, data file is 2GB but index file is 12 GB.
> I do not know what problem with my database.
> I have some questions:
> 1/ How so I reduce size of index file ?
> 2/ How to know what is stored in index file?
> 3/ How to trace all impact to index file?
> 4/ How to limit size growing of index file?
Watch your *clustered* indexes.
You've likely created your clustered indexes on your index file
group. If you did, your data is on the index file group and not
on your data file group.
You'll want to put all of your indexes *except* your clustered
indexes on the index file group. Your *clustered* indexes should
be created on the data (primary) filegroup.|||Hi Sgt. Sausage,
Most of indexes in my database are non-cluster indexes and created on Index
filegroup.
Please help me more.
Thanks
TN
"Sgt. Sausage" wrote:
> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:FD5ADA92-D326-4139-A459-F030EB62DE27@.microsoft.com...
> > Hi all,
> >
> > I have a serious problem:
> >
> > I created 1 database with 2 file group : 1 primary and 1 index.
> > - Primary file group includes 1 data file (*.mdf): store all tables
> > - Index file group includes 1 index file (*.ndf): store all index
> >
> > after a short time using, data file is 2GB but index file is 12 GB.
> > I do not know what problem with my database.
> > I have some questions:
> >
> > 1/ How so I reduce size of index file ?
> > 2/ How to know what is stored in index file?
> > 3/ How to trace all impact to index file?
> > 4/ How to limit size growing of index file?
> Watch your *clustered* indexes.
> You've likely created your clustered indexes on your index file
> group. If you did, your data is on the index file group and not
> on your data file group.
> You'll want to put all of your indexes *except* your clustered
> indexes on the index file group. Your *clustered* indexes should
> be created on the data (primary) filegroup.
>
>|||first thing, what is your physical disk setup? if you are on raid 5 or
raid 0+1 then having seperate file groups does nothing for performance.
if you are on raid 1 and have 2 seperate mirrored drive pairs, then
seperate file groups would help. filegroup seperation is mainly used
for backup and recovery purposes or if you are in a non-raid setup. if
you are on a raid setup, i would move everything back to one filegroup
and be done with it.
as stated above, you have to make sure that all of your clustered
indexes are on your primary filegroup, otherwise the data will be in
the wrong filegroup. indexes have what is called a leaf level...in a
clusterd index, that level is the actual data.....so, when you tell
sql server to create an index on a file group, if it happens to be a
clustered index, it will move the table over as well. i am not sure of
you index file group name so you will have to edit this, but this is
the code from above slightly modified:
SELECT
OBJECT_NAME(id) as TABLE_NAME,
NAME as INDEX_NAME,
FILEGROUP_NAME(groupid) as FILEGROUP_NAME,
dpages PAGES
FROM sysindexes
WHERE FILEGROUP_NAME(groupid) = '<name_of_index_filegroup>'
AND indid in (0,1)
ORDER BY TABLE_NAME, INDEX_NAME
this will let you know if you have any clustered indexes on the wrong
filegroup.
i cannot tell you why your databases is growing so rapidly without
knowing more detail:
remember, indexes are for select statements. if you have a heavy dml
(updates and inserts) then your indexes are going to hurt your system.
hth,
hans nelsen

Index File growing big

Hi all

I have a DB with 1 data file, 1 log file and 1 index file.
data file is 3 GB but index file is 12 GB.
Index file is growing big day by day.
This cause performance of DB down.
What should I do to prevent index file become bigger and size of index file smaller?

Thanks in advanced
Thi Nguyenhelp me.

Thi Nguyen|||Do you have ANY index maintenance tasks implemented?|||Hi rdjabarov
What do you mean "ANY index maintenance tasks" ?
I have many indexes in DB.

Thi Nguyen|||do u mean transaction log file as index file...?|||I meant that index file growing big, not transaction log.
Thanks

Index Errors!

Hi,
Our database has been producing alot of errors in the SQL
Server Log file. Most of these errors are codes 624, 644
and 605.
What could be causing these errors to appear so
frequently? This only started recently.
We are going to perform the Stress Test as recommended on
this page http://support.microsoft.com/default.aspx?
scid=kb;en-us;826433. But we're waiting for a good time to
do this as it will take up a considerable amount of time.
Thanks!Os Sutrisno wrote:
> Hi,
> Our database has been producing alot of errors in the SQL
> Server Log file. Most of these errors are codes 624, 644
> and 605.
> What could be causing these errors to appear so
> frequently? This only started recently.
Your database looks like it has been corrupted lately. Did you try to
execute DBCC CHECKDB to determine the extent of the damage? Make sure you
have a good set of latest backups on site, they might come in handy soon.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||We've done a DBCC CheckDB and the loss was only 2 rows of
data. However, these errors still appear the following day
on another table or database.
What could be causing these corruption? Is there a way to
keep the database healthy? any maintenance that can be
done?
Thanks,
Os

>--Original Message--
>Os Sutrisno wrote:
SQL
>Your database looks like it has been corrupted lately.
Did you try to
>execute DBCC CHECKDB to determine the extent of the
damage? Make sure you
>have a good set of latest backups on site, they might
come in handy soon.
>sincerely,
>--
>Sebastian K. Zaklada
>Skilled Software
>http://www.skilledsoftware.com
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>.
>|||Looks like you've got bad hardware somewhere. You should check your
errorlog, the NT event logs and run all relevant hardware diagnostics on
your IO subsystem. If you need help with this, PSS
(http://support.microsoft.com) should be able to help you.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Os Sutrisno" <oswin8@.hotmail.com> wrote in message
news:718a01c4024c$402c1f60$a301280a@.phx.gbl...
> We've done a DBCC CheckDB and the loss was only 2 rows of
> data. However, these errors still appear the following day
> on another table or database.
> What could be causing these corruption? Is there a way to
> keep the database healthy? any maintenance that can be
> done?
> Thanks,
> Os
>
> SQL
> Did you try to
> damage? Make sure you
> come in handy soon.
> confers no rights.|||Os Sutrisno wrote:

> We've done a DBCC CheckDB and the loss was only 2 rows of
> data. However, these errors still appear the following day
> on another table or database.
> What could be causing these corruption? Is there a way to
> keep the database healthy? any maintenance that can be
> done?
Many things, too many to count. But you should focus on your hardware,
especially I/O subsystem (drives, controllers). Does your drive controller
use write-ahead caching? If yes, try to disable it. Be sure that your disk
controller prevents the uncontrolled reset of the caching controller, has
on-board battery backup, and uses mirrored or ECC memory. Do not implement
write caching unless the hardware vendor guarantees that the write cache
includes all features required to prevent data loss.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks everyone!
I'll try to find out whats going wrong...
Unfortunately, the support from Microsoft in Singapore is
almost non-existent. The last time I emailed and contacted
them, they said Microsoft Singapore only provides support
for OS and Desktop Applications. I was told to contact
HP/Compaq for server applications support. But then,
Hp/Compaq wants our company to sign a yearly $50k
contract, before they'll lend a hand...
Anyways, Thanks again!

>--Original Message--
>Os Sutrisno wrote:
>
of
day
to
>Many things, too many to count. But you should focus on
your hardware,
>especially I/O subsystem (drives, controllers). Does your
drive controller
>use write-ahead caching? If yes, try to disable it. Be
sure that your disk
>controller prevents the uncontrolled reset of the caching
controller, has
>on-board battery backup, and uses mirrored or ECC memory.
Do not implement
>write caching unless the hardware vendor guarantees that
the write cache
>includes all features required to prevent data loss.
>sincerely,
>--
>Sebastian K. Zaklada
>Skilled Software
>http://www.skilledsoftware.com
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>.
>|||I'll pass this feedback to the appropriate PSS management.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Os Sutrisno" <oswin8@.hotmail.com> wrote in message
news:81da01c40314$dffd5f90$a101280a@.phx.gbl...
> Thanks everyone!
> I'll try to find out whats going wrong...
> Unfortunately, the support from Microsoft in Singapore is
> almost non-existent. The last time I emailed and contacted
> them, they said Microsoft Singapore only provides support
> for OS and Desktop Applications. I was told to contact
> HP/Compaq for server applications support. But then,
> Hp/Compaq wants our company to sign a yearly $50k
> contract, before they'll lend a hand...
> Anyways, Thanks again!
>
> of
> day
> to
> your hardware,
> drive controller
> sure that your disk
> controller, has
> Do not implement
> the write cache
> confers no rights.

Index Errors!

Hi,
Our database has been producing alot of errors in the SQL
Server Log file. Most of these errors are codes 624, 644
and 605.
What could be causing these errors to appear so
frequently? This only started recently.
We are going to perform the Stress Test as recommended on
this page http://support.microsoft.com/default.aspx?
scid=kb;en-us;826433. But we're waiting for a good time to
do this as it will take up a considerable amount of time.
Thanks!Os Sutrisno wrote:
> Hi,
> Our database has been producing alot of errors in the SQL
> Server Log file. Most of these errors are codes 624, 644
> and 605.
> What could be causing these errors to appear so
> frequently? This only started recently.
Your database looks like it has been corrupted lately. Did you try to
execute DBCC CHECKDB to determine the extent of the damage? Make sure you
have a good set of latest backups on site, they might come in handy soon.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||We've done a DBCC CheckDB and the loss was only 2 rows of
data. However, these errors still appear the following day
on another table or database.
What could be causing these corruption? Is there a way to
keep the database healthy? any maintenance that can be
done?
Thanks,
Os
>--Original Message--
>Os Sutrisno wrote:
>> Hi,
>> Our database has been producing alot of errors in the
SQL
>> Server Log file. Most of these errors are codes 624, 644
>> and 605.
>> What could be causing these errors to appear so
>> frequently? This only started recently.
>Your database looks like it has been corrupted lately.
Did you try to
>execute DBCC CHECKDB to determine the extent of the
damage? Make sure you
>have a good set of latest backups on site, they might
come in handy soon.
>sincerely,
>--
>Sebastian K. Zaklada
>Skilled Software
>http://www.skilledsoftware.com
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>.
>|||Looks like you've got bad hardware somewhere. You should check your
errorlog, the NT event logs and run all relevant hardware diagnostics on
your IO subsystem. If you need help with this, PSS
(http://support.microsoft.com) should be able to help you.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Os Sutrisno" <oswin8@.hotmail.com> wrote in message
news:718a01c4024c$402c1f60$a301280a@.phx.gbl...
> We've done a DBCC CheckDB and the loss was only 2 rows of
> data. However, these errors still appear the following day
> on another table or database.
> What could be causing these corruption? Is there a way to
> keep the database healthy? any maintenance that can be
> done?
> Thanks,
> Os
>
> >--Original Message--
> >Os Sutrisno wrote:
> >> Hi,
> >>
> >> Our database has been producing alot of errors in the
> SQL
> >> Server Log file. Most of these errors are codes 624, 644
> >> and 605.
> >>
> >> What could be causing these errors to appear so
> >> frequently? This only started recently.
> >
> >Your database looks like it has been corrupted lately.
> Did you try to
> >execute DBCC CHECKDB to determine the extent of the
> damage? Make sure you
> >have a good set of latest backups on site, they might
> come in handy soon.
> >
> >sincerely,
> >--
> >Sebastian K. Zaklada
> >Skilled Software
> >http://www.skilledsoftware.com
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> >
> >.
> >|||Os Sutrisno wrote:
> We've done a DBCC CheckDB and the loss was only 2 rows of
> data. However, these errors still appear the following day
> on another table or database.
> What could be causing these corruption? Is there a way to
> keep the database healthy? any maintenance that can be
> done?
Many things, too many to count. But you should focus on your hardware,
especially I/O subsystem (drives, controllers). Does your drive controller
use write-ahead caching? If yes, try to disable it. Be sure that your disk
controller prevents the uncontrolled reset of the caching controller, has
on-board battery backup, and uses mirrored or ECC memory. Do not implement
write caching unless the hardware vendor guarantees that the write cache
includes all features required to prevent data loss.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks everyone!
I'll try to find out whats going wrong...
Unfortunately, the support from Microsoft in Singapore is
almost non-existent. The last time I emailed and contacted
them, they said Microsoft Singapore only provides support
for OS and Desktop Applications. I was told to contact
HP/Compaq for server applications support. But then,
Hp/Compaq wants our company to sign a yearly $50k
contract, before they'll lend a hand...
Anyways, Thanks again!
>--Original Message--
>Os Sutrisno wrote:
>> We've done a DBCC CheckDB and the loss was only 2 rows
of
>> data. However, these errors still appear the following
day
>> on another table or database.
>> What could be causing these corruption? Is there a way
to
>> keep the database healthy? any maintenance that can be
>> done?
>Many things, too many to count. But you should focus on
your hardware,
>especially I/O subsystem (drives, controllers). Does your
drive controller
>use write-ahead caching? If yes, try to disable it. Be
sure that your disk
>controller prevents the uncontrolled reset of the caching
controller, has
>on-board battery backup, and uses mirrored or ECC memory.
Do not implement
>write caching unless the hardware vendor guarantees that
the write cache
>includes all features required to prevent data loss.
>sincerely,
>--
>Sebastian K. Zaklada
>Skilled Software
>http://www.skilledsoftware.com
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>.
>|||I'll pass this feedback to the appropriate PSS management.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Os Sutrisno" <oswin8@.hotmail.com> wrote in message
news:81da01c40314$dffd5f90$a101280a@.phx.gbl...
> Thanks everyone!
> I'll try to find out whats going wrong...
> Unfortunately, the support from Microsoft in Singapore is
> almost non-existent. The last time I emailed and contacted
> them, they said Microsoft Singapore only provides support
> for OS and Desktop Applications. I was told to contact
> HP/Compaq for server applications support. But then,
> Hp/Compaq wants our company to sign a yearly $50k
> contract, before they'll lend a hand...
> Anyways, Thanks again!
> >--Original Message--
> >Os Sutrisno wrote:
> >
> >> We've done a DBCC CheckDB and the loss was only 2 rows
> of
> >> data. However, these errors still appear the following
> day
> >> on another table or database.
> >>
> >> What could be causing these corruption? Is there a way
> to
> >> keep the database healthy? any maintenance that can be
> >> done?
> >
> >Many things, too many to count. But you should focus on
> your hardware,
> >especially I/O subsystem (drives, controllers). Does your
> drive controller
> >use write-ahead caching? If yes, try to disable it. Be
> sure that your disk
> >controller prevents the uncontrolled reset of the caching
> controller, has
> >on-board battery backup, and uses mirrored or ECC memory.
> Do not implement
> >write caching unless the hardware vendor guarantees that
> the write cache
> >includes all features required to prevent data loss.
> >
> >sincerely,
> >--
> >Sebastian K. Zaklada
> >Skilled Software
> >http://www.skilledsoftware.com
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> >
> >.
> >

index and constraints

Dear,
I export the SQL server 2000 database setting
(table,sp,view,user..etc) to script file then import the
script(setting) to another database.
After import the setting to database, the index and
constraints was missing on the table.
How can i import the index and constriants to new
database ? (i have choice the index and constraints option
when i export the database setting)
Many Thanks
JohnHi John
If you've chosen the option to include them at the end of the script objects
wizard then you've done the right thing & it should have worked.
Another option is to use the Import / Export wizard, which also has an
option to copy objects along with indexes / constraints etc. You can choose
to include / exclude data using the import / export wizard.
Regards,
Greg Linwood
SQL Server MVP
"John" <acos3ltd1@.hotmail.com> wrote in message
news:020601c3c511$49c837c0$a401280a@.phx.gbl...
> Dear,
> I export the SQL server 2000 database setting
> (table,sp,view,user..etc) to script file then import the
> script(setting) to another database.
> After import the setting to database, the index and
> constraints was missing on the table.
> How can i import the index and constriants to new
> database ? (i have choice the index and constraints option
> when i export the database setting)
> Many Thanks
> John|||you could also try another tool. Try "DB Ghost" at
www.dbghost.com
>--Original Message--
>Dear,
>I export the SQL server 2000 database setting
>(table,sp,view,user..etc) to script file then import the
>script(setting) to another database.
>After import the setting to database, the index and
>constraints was missing on the table.
>How can i import the index and constriants to new
>database ? (i have choice the index and constraints
option
>when i export the database setting)
>Many Thanks
>John
>.
>