Just curious if anyone has a script to find and delete all indexes
created by index tuning wizard, leaving the original indexes untouched.
All of the original indexes in this particular database are preceded
with IX_, whereas those created by ITW are the table name followed by a
number.
I'm thinking of something along the lines of "sp_MSforeachtable
@command1="print '?'" + a DBCC which just targets the ITW indexes (if
such a thing exists). Any ideas how to go about this?
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 ?
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
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.
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.
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'.
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
We have transaction replication set up on one of our servers whose replicated DB is used for reporting purpose:
Now, the replicated database "D" of size 350 GB has mainly grown huge as compared to Published DB (200 GB) because of the index sizes at subscribed DB. Therefore:
I have found that Database D has one table of data size 15 GB, with rows 8349533 and index size 28 GB the biggest in the database. And adding have total of 109 Non clustered indexes:
On using SP_blitzindex i have found approx 50 NC indexes unused with below usage:
Reads: 0 Writes:273,243 and total size of indexes being 18 GB
Last User Seek Last User Scan
Last User Lookup all '0' 0 singleton lookups; 0 scans/seeks; 0 deletes; 0 updates; except for few 3 or 4 where updates are more than 4000.
Is this sufficient enough to delete all the above 49 unused NC indexes? And Can i create Missing index on Subscriber database?
I want to tune the indexes on my database and I am trying to use the SQL Server Profiler to collect data for the Index tuning wizard to analyze. My question is what do I need to trace with the profiler so that the Index tuning wizard can work? I am looking at the trace properties in Profiler at the Events, Data Columns, and Filters tabs but I have no idea of what I need to capture.
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
I have become frustrated and I am not finding the answers I expect.
Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough.
So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good.
I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good
Next, I select "Copy data from one or more tables or views". I move on to the next screen and select all of the Objects from a Schema. These are Tables that only relate to our application or in other words, nothing Oracle System wise.
When I get to the end it progresses to about 20% and then throws this error about 300 or so times:
Could not connect source component. Warning 0x80202066: Source - AM_ALERTS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
So, I'm thinking "Alright, we can search on this error and I'm sure there's an easy fix." I do some checking and indeed find out that there is a property setting called "AlwaysUseDefaultCodePage" in the OLEDB Data Source Properties. Great! I go back and look at the connection in the Import and .... there's nothing with that property!
Back to the drawing board. I Create a new SSIS package and figure out quickly that the AlwaysUseDefaultCodePage is in there. I can transfter information from the Oracle Source Table to the SQL Server 2005 Destination Table, but it appears to be a one to one thing. Programming this, if I get it to work at all, will take me about 150 hours or so.
This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!!
This generates 2 questions: 1. If the Import Data Wizard cannot handle this operation on the fly, then why can't the AlwaysUseDefaultCodePage property be shown as part of the connection 2. How do I create and SSIS Package that will copy all of the data from Oracle to SQL Server? The source tables have been created and have the same Schema and Object Names as the Source. I don't want to create a Data Flow Task 600 times.
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this: When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004 Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing? If so does this "pseudocode" example also cause this to occur: sqlstring="select * from item where itemid=12345" rs.open sqlstring, etc, etc, etc rs.Fields("ItemName")="My New Item Name" rs.Fields("ItemPrice")=1.00 rs.Update Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
I have a view which joins 3 tables. One has 15 million rows the next another 5 million and the third 500k. When I join them the execution plan tells me that 15 million rows were retrieved from the first (taking about 5 mins) 1.5 million from the 2nd taking 3 mins and 4.5 million from the third taking almost no time.
The first two ause a clustered index, one being a seek the other a scan and the third a regular index seek. All followed by a hash match/inner join which takes 2 mins.
Any ideas on optimizing the SQL?
Here is the syntax: SELECT b.packno, b.COMM_DATE, a.ben_grp_cr, a.ben_dsc_cr, c.gst_inc, SUM(a.credit) FROM TABC c INNER JOIN TABB B ON b.PACKNO = c.PACKNO AND b.COMM_DATE = BENDTL.COMM_DATE AND b.BEN_NUM = c.BEN_NUM INNER JOIN TABA a ON b.tran_id = a.tran_id WHERE b.tran_date > '20040401' AND c.gst_inc = 0 GROUP BY b.packno, b.COMM_DATE, a.ben_grp_cr, a.ben_dsc_cr, c.bendtl.gst_inc
I tried on finding out the problem in a slow running sp with profiler. I found that there are some waiting resource 'tracewrite' and 'async_network_io'. Any idea on it? Thanks in advance
I need to find a way to make this query run faster. Please let me know if you can help. Here are some of the issues.
1) I have a database with one table, and No indexes can be put in place.
2) This is a very large database. (4,000,000) records, and growing daily.
3) the following query returns 720,000 records.
4) The query takes about 18 minutes to complete.
I understand that by doing a table scan, the way this has to run is difficult, but is there anything I can do?
The Problem: When I run this query, I also run performance monitor, I have found that my page file is at 100%. I have 1 Gig of Ram on the server with 2 P III Xeion Processors. I don't know what other information I can give, but please let me know.
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!!!
I am interested in finding out if anyone out there has experience with extremely high-performance SQL Server applications. The I/O needs of my database server are growing very quickly, and I am on the verge of launching a major upgrade project.
We have done all the standard tuning tasks: proper indexing, stored procedure tuning, etc... and are running on good small-server scale hardware ( dual PIII 700s, 1G RAM, but no RAID). The only path I can see to achieving higher performance are:
- lots of RAID, perhaps on a SAN. - server upgrade, maybe 4 proc? I've been looking at RAIDZONEs and Netfinity's - data partitioning ( I REALLY want to avoid this if I can! )
What do you do when you need Major Enterprise scale database performance from SQL Server? I've found lots of resources for Oracle and DB2, but I can't find many case studies for serious SQL Server installations.
Hi, does the upgradition SQL Server 6.5 to 7.0 will simply solve some problems which we are facing currently like ODBC errors Insert failed and update failed and also supporting more users ? We have Access front end to SQL Server backend, so do we need to touch code in front end for optimizations ?
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?
Good Morning Everybody, I had a single table consists of one million records. To retrive data from that table it takes lot of time. how i can reduce execution time? what is the procedures to tune the database. i implemented cluster index on primary key of that table. still i can't able to reduce execution time. can anybody help me in this issue?
Hi, i am working on sql server 6.5 version.actually this is developed just one year back. but now the system is almost dead(low performance).i think the reasons r database design,networking,hardware etc.is it correct. and how to rectify these errors. i am suggesting that upgradation is the best option.so pl give the suggestions asap. Thanx Janreddy
What are some of the things i can do to improve query performance if the querey performance is realatively slow today compared to yesterday's performance: Here are some of things i looked at: -updating statistics -checking the execution plan -DBCC showcontig
In the older versions 6.5-7.0 you could adjust max_async_io setting if you had fast controllers and disks that could handle more IO from SQLServer. In 2000 this setting is removed. Are there any settings that I could adjust/tune regarding IO and disk handling ?
