Index Tuning Wizard Error
May 9, 2006
Hi
When I try to run the ITW to increase performance I get this error in the event viewer. Earlier I have used the ITW with success but now I dont get any more index recommendations, just this error. Do you know what it means :
"The description for Event ID ( 4 ) in Source ( ITW ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event:
Error in Parsing Event:
execute sp_Sys'.
View 3 Replies
ADVERTISEMENT
Mar 31, 2006
Hi,
I wanted to use the Index Tuning Wizard to monitor the performance of a stored procedure but the option is greyed out. Is there a way to turn this functionality on, or does this indicate that this option is not installed ?
Thanks,
Jeff
View 1 Replies
View Related
Apr 17, 2007
hi all,
jusr read about index tuning wizard in BOL.. not sure about it enuff.. can anybody explain in what scenario we should use this wizard/
~~~Focus on problem, not solution~~~
View 5 Replies
View Related
Jul 20, 2005
HiI tried running ITW against one of our databases, and it came up with anumber of suggestions, but also filled the Application Log with messageslike:-The description for Event ID ( 4 ) in Source ( ITW ) could not be found. Itcontains the following insertion string(s):Error in Parsing Event:declare @P1 nvarchar(1)declare @P2 nvarchar(1)declare @P3 nvarchar(1)declare @P4 nvarchar(1)EXEC "dbo"."sp_get_RAO_indexW2K2" @P1 , @P2 , @P3 , @P4 .Does anyone know if this is serious (or indeed what it means at all)?There were no apparent errors in the ITW run.Chloe Crowder
View 1 Replies
View Related
Aug 16, 1999
I've tried many times to get the index tuning wizard to work...It never seems to find any 'parseable queries' in my workload file. I believe that I'm running the profiler correctly.
When I submit a simple sql file as a workload file, I get a 'server error creating table or index analysis...' back.
Any suggestions or hints?
Thanks,
Steve
View 1 Replies
View Related
Jan 14, 1999
I am running a Desktop SQL 7.0 database on Windows 95. I produced a trace file using the Profiler and am trying to run the Index Tuning Wizard on the trace file. I always get an error message "Error analyzing workload (60).". I cannot find anything in Books Online or the Microsoft web site.
Thanks for any help.
View 1 Replies
View Related
Jul 20, 2005
Hi,I am having problems getting anything useful out of the index tuningwizard.I have created a table and inserted data into it. When i run the indextuning wizard i expect 2 indexes to be recommended so the book says(Index011 with a key on the uniqueid column and a non clustered indexnamed table02 with a key on the col03 and LongCol02)Instead i get nothing being recommended.What am i doing wrong????Please helpMaryamif exists (select name from dbo.sysobjects where name ='table01' andtype ='u')drop table table01create table table01(uniqueid int identity, longcol02 char(300)DEFAULT 'THIS IS THE DEfault column',col03 char(1))godeclare @counter intset @counter =1while @counter<=1000begininsert table01 (col03) values('a')insert table01 (col03) values('b')insert table01 (col03) values('c')insert table01 (col03) values('d')insert table01 (col03) values('e')insert table01 (col03) values('f')set @counter=@counter+1end
View 3 Replies
View Related
Jan 8, 2006
I saw this tool for SQL-Server 2000 :
http://www.sql-server-performance.com/index_tuning_wizard_tips.asp
Is there anything similar for SQL-Server 2005 Express ?
Thank you very much for any help!
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
View 2 Replies
View Related
Nov 10, 2005
I am unable to migrate any DTS packages, from a SQL Server 2000 package, from a structured storage file, or from a DTS package imported into SQL2005 (Developer Edition, 32 bit). Running the Package Migration Wizard, every time I get to the List Package screen, i get the error below. I haven't found any other mention of this so far. Anyone else seen this error or have suggestions?
View 20 Replies
View Related
Jul 20, 2005
Just curious if anyone has a script to find and delete all indexescreated by index tuning wizard, leaving the original indexes untouched.All of the original indexes in this particular database are precededwith IX_, whereas those created by ITW are the table name followed by anumber.I'm thinking of something along the lines of "sp_MSforeachtable@command1="print '?'" + a DBCC which just targets the ITW indexes (ifsuch a thing exists). Any ideas how to go about this?
View 3 Replies
View Related
Jun 7, 2001
I want to use the Index tuning wizard on some of my tables. Is it OK if I use when people are on the server or to do it during off-pick period. Thanks!!!
View 1 Replies
View Related
Nov 29, 1999
Other than the SQL Server 7.0 Index Tuner wizard (which isn't suggesting anything). Is there a 3rd party Index Tuner piece of software out there? Or is there something special that needs ot be done to get the SQL Index Tuner to work?
View 3 Replies
View Related
Jul 26, 2007
for what purpose we are splitting the non-clustered index into 3 instead of 1
create index si_acct_info_dtl_INDX1 on si_acct_info_dtl(account_code, ctrl_acct_type)
create index si_doc_hdrfk_ci_acct_info_dtl on si_acct_info_dtl(tran_ou, tran_type, tran_no)
whether index rebuit everycolumn when search is given????.if we build the index in one
statement like this:create index si_acct on si_acct_info_dtl(batch_id)
create index si_acct_info_dtl_guid on si_acct_info_dtl(batch_id,account_code, ctrl_acct_type,tran_ou, tran_type, tran_no)
View 4 Replies
View Related
Apr 22, 2008
Hi,
Our reporting tool dynamically creates tables and fields based upon Excel spreadsheets that are imported. To improve query performance, we are attempting to create indices on these tables automatically. The problem we are facing is that the tool allows the user to group the data by a whole range of fields when generating reports, therefore making it hard to decide what indices to create.
So, as an example, we have a table that has 10 VARCHAR(250) fields (the default size for text based fields in the tool), each of which could potentially be in the group by clause, and 15 numeric fields on which calculations will be carried out.
Is there a single index that we can create dynamically that would improve performance for all potential queries that may be generated? Such as an index that contains all the 10 VARCHAR(250) fields, and the numeric values as Included Columns?
If so, what effect does the order of the fields in the index have, ie we have some fields that have a lot of distinct values and some that have only a few. Which ones should appear higher in the list?
Any help would be greatly appreciated.
James
View 5 Replies
View Related
Jul 28, 2000
Eveytime I feed a query into the Index Tuner
I get the usefull message Error analyzing query (60)
What is it and how do I get round it
View 2 Replies
View Related
May 2, 2003
Is it possible to achieve Index tuning using sql script within Query Analyser when being just a db_owner and not sysadmin.
In case the answer is yes, would you please provide any link to help or examples.
Thanks
View 8 Replies
View Related
Oct 22, 2007
I'm using the Database Engine Tuning Advisor to do some performance evaluation on my database. I have one particular table that will potentially have a couple million rows. but this may not occur for a few months to a year from now.
when i run the advisor with 1.5 million rows, it recommends that i add an index across all columns in this table (covering index) for an estimated 91% improvement. when i run it with 1500 rows in it, it recommends that i add an index on 2 key columns for an 8% improvement.
how should i reconcile this? can i have both and what does that do to performance?
View 17 Replies
View Related
Nov 1, 2007
Hi All!
I would like to try and increase the performance of the reportserver database. Im thinking of adding an index to dbo.catalog.itemID - before I break the database and subsequently kill the 200+ reports i spent ages writing, does anyone know of any problems doing this?
The reports seem to be a bit slow first run but speed up during the course of the day - the server generates about 1500 instances of reports each day (7 instances of reportmanager all pointing to the reportserver db). I primarily use precompiled storedprocedures to feed the datasets so this is why im thinking index tuning may help.
Any thoughts/comments are welcomed!
View 2 Replies
View Related
May 15, 2000
HI
I have this complex logical model with 60 tables which i have converted to phycical model and into sql 7.0 database now the database is completely new for a new internet project without even single row of data now i have to optimise database and determine if the indexes created by the the designing team are good in sql 7.0 will the give me optimised database or not . I just dont know what to do
1) is it possible for me to use indextuning wizard without the data
whenever i try to to use indextuning wizard it askes me to use a workload file or make workloas file useing a trace as i do not have the data i just have to capture a trace by saying select * from <table>
as the work load file does not return any records the index tuning wizard does not suggest any changes In the indexing
tell me HOW DO I DETERMINE WHAT INDEXES TO CREATE FOR A OPTIMISED DATABESE ?
2) IT IS NOT POSSIBLE FOR ME TO PUT DATA IN ANY ONE TABLE AS ALL THE TABLES HAVE fk AND pk RELATION SHIP so what do i do
3) WHAT DOES dba normally do when he recives an empty data base to optimise
4) where do i make views to optimise the data
5) how do I populate data in so many tables
will puting dummy data help ?
Can some one help me with some SP which will populate the table with dummy data by some kind of do while loop with counter incrementing with 1000 rows
please help me
and if i am not clear
pl tell ask me the questions on my default email or
johnusa44@hotmail.com
john or G2
View 3 Replies
View Related
Apr 11, 2007
hello,
I'm looking for a way to populate my index on insertion but not on updates.
I tried each possible value for CHANGE_TRACKING MANUAL|AUTO|OFF and it automatically takes every changes that have been made before in account. is there a way to "flag" the rows that I don't want the server to re-index (i.e. updated rows).
Thanks for reading, any help is welcome.
View 1 Replies
View Related
Nov 14, 2007
Hello all.
I have the following table
Create Table Item(
I_AssetCode NVarChar(40) Primary Key NOT NULL,
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20)
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
It is populated with 50 000 records.
Searching on indexed columns is fast, but I've run into the following problem:
I need to get all distinct companies in the table.
I've tried with these two queries, but they both are very slow!
1. "select I_Company from item group by I_Company " - This one takes 19 seconds
2. "select distinct(I_Company) from item" -This one takes 29 secons
When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first...
The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time !
Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing!
So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause?
According to SQL CE Books online:
Group By
"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group."
It seems the aggregate is run every time, not only when there is an aggregate function.
Is this a bug?
Thanks in advance,
TipoMan
View 4 Replies
View Related
Jun 3, 2006
Here is an extract from the Acc2Sql2.doc >>
By default, the Upsizing Wizard
transfers all indexes as nonclustered indexes. You can modify the Upsizing
Wizard to transfer the primary key index to a clustered index. To make this
change, start Microsoft Access and open the upsizing wizard library database.
For Microsoft Access 95, the filename is Wzcs.mda. For Microsoft Access 97, the
filename is Wzcs97.mda. When the database is open, click the Modules tab and open the
UT_ModUserConstants module. Search down to the UT_CLUSTERED constant. Change
the default value from False to True.
<<< end quote
Question: I am using ACCESS 2002 upsize wizard, I searched my computer for *.mda and could not find any wzcs*.mda. How do I set it so that it creates clustered-index instead of non-clustered-index?
View 1 Replies
View Related
Nov 22, 2000
Hi
Is there any good books for Query Tuning and Stored procedure Tuning
Thanks
View 1 Replies
View Related
Jul 3, 2006
I have a very irritating error with Database Engine Tuning Advisor. While processing traces from Sql Profiler I keep getting trace log full of syntax error of most of queries - some of them very simple and ALL of them perfectly correct ( I check them separatly in QueryAnaliser ) - program gives recomendtation when I enter queries one by one - in that case the syntax is correct for Database Engine Tuning Advisor, but I just can't imagine why quries given to analysis in the set aren't correct? I dont' want to enter trace query by query (while I have thousends of them) !
It all seem like a Database Engine Tuning Advisor bug, any ideas about this problem?
Joanna
View 1 Replies
View Related
Dec 5, 2007
Hi,
I am experiencing a Named Pipes Provider error 40 message as detailed below. I get this when I'm running the dta utility on a production server which is trying to remote onto a test server to use the metadata and optimizer there.
The error reported is...
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
(note: there is no (Microsoft SQL Server, Error: xxx) reported at the end of the error string)
The error results from the following cmdline syntax run from the production server.
dta -A 0 -E -e C:TuningLog.xml -F -ix C:DTA.xml -N OFF -of C:outputdta.sql -rl ALL -s DTA_20071205
Prod box: 64bit SQL2005 SP2 Std Ed Named instance, TCPIP and Named Pipes enabled, SQL Browser service running.
Test box: 32bit SQL2005 SP2 Std Ed Default instance, Listening for local and remote connections on TCPIP and Named Pipes (configured through the SAC), SQL Browser service running. From the SQL Log on the test box I can see TCPIP is listening & Named pipes is listening on \.pipesqlquery
There is no firewall configured on either box.
I can connect to the test server from the prod box using sqlcmd -E -S<servername>
I can ping the test server from the prod box
Would you have any idea what I'm doing incorrectly? The genericity of the error message doesn't give me much of a clue and the resolutions I've read all revolve around SQLBrowser, surface area config, and SQL Express which I've covered off.
many thanks
Matthew Eames
View 1 Replies
View Related
Apr 4, 2008
Hi there
Apologies if I'm in the wrong post. I have a strange problem. I installed Sql Express Toolkit with the sole purpose to offer the import/export functionality in the Microsoft SQL Server90DTSBinn folder. After installation I did a quick test to make sure it works and it did. Suddenly now I get an error when I run the wizard file saying that it is not a valid win 32 application. I have a clean Windows XP S2 installation and definitly no virussus/worms etc. I have a simmilar problem when I try to run ASP.Net web starter kit files (.vss or something like that...)
PLEASE help me!
View 1 Replies
View Related
Aug 1, 2000
. I get following message when the scheduled DTS job starts.
( it runs fine interactively)....
*** I've read other messages about SQLServiceAgent 'permissions' but how do you setup these permissions..
----------------------------------------------------------------------------
DTSRun: Loading... Error: -2147024809 (80070057); Provider Error: 0 (0) Error string: The parameter is incorrect. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 713. Process Exit Code 1. The step failed.
-----------------------------------------------------------------------------
.
Any help would be appreciated - Thanks
View 3 Replies
View Related
Jan 16, 2001
I am trying to perform DTS from a 7.0 server to
a 6.5 server.
But for one of the 6.5 servers, I am getting the following
DTS Wizard error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: The catalog storedprocedures installed on server
VACTBETA4 are version 6.50.193;version 07.00.0330 or later is required
to support IDBSchemaRowset.Please contact your systemadmnistrator.
It is a production server and needs to be fixed.
Help appreciated.
Thanks.
View 1 Replies
View Related
Jul 15, 1999
I am trying to export data from SQL Server to a Visual FoxPro database using the DTS export wizard and I get the same error message every time. After I select a destination (Microsoft Visual FoxPro Driver) and select a DSN is get a DTSA wizard error. It says:
Error Source: Microsoft OLE DB Provider for ODBC Drivers.
Error Destination: (Microsoft)(ODBC Visual FoxPro Driver) SourceDB
Any help will be greatly appreciated.
Mike
View 1 Replies
View Related
Jan 28, 2007
Got the following error on the last part of the wizard, after setting all the requirements:
Setting the snapshot share permissions (Error)
Messages
The operation completed successfully. (Exception from HRESULT: 0x80070000) (mscorlib)
View 4 Replies
View Related
Dec 18, 2006
Hi,
I'm just trying to use the Maintenance Wizard for the first time, but the SQL Server Management Studio shows me an error message about 'Agent XPs' is not running on my server; and I should activate it through 'sp_configure'?
I've tried to understand 'sp_configure' by searching it on MS TechNet, etc. But of course, because I'm not a SQL Server expert user; I don't even know how to use 'sp_configure' at all....
Anyone know how to work this thing around? Appreciate all the help...
PS: I almost forgot to mention, that I'm using MS Windows Small Business Server R2 2003 with SQL Server 2005 Workgroup Edition.
View 6 Replies
View Related
Jul 29, 2007
I created a template with a Logo, Header data and Footer data. I then copied the report to the template directory.
and renamed it report.rdl (replacing the default report that was there).
C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssembliesProjectItemsReportProject
When I create a report using the report wizard I get the following error:
Deserialization failed: The report definition element 'Report' contains more than one child element of type 'Language' at line 362, position 4. Line 362, position 4.
Edit code
The report works just fine when I created it and did a preview.
I was hoping to create a report template that I could substitute for the default report (report.rdl) so that when using the wizard the report would be created with logos, headers and footers already there.
If I put the original report back the report wizard works just fine.
Am I doing something wrong or is this not allowed.
View 1 Replies
View Related
Apr 16, 2007
I'm using SQL Server 2005 Enterprise x64 and when attempting to migrate a DTS from our SQL Server 2000 Standard x86 server I received the following error message:
DTS Migration Wizard Error
Could not load file or assembly 'Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of it's dependencies. The system cannot find the file specified.
Click Abort to stop the migration of the current package.
Click Retry to retry the operation.
Click Skip to skip the migration of the current task and continue to the next task.
From the error message, it would appear something did not install or register correctly. Any idea on what is missing and how I can fix it?
View 6 Replies
View Related
