SQL Server 2008 :: Removal Of Unused Indexes
May 21, 2015
I am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones
When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
[code]....
View 2 Replies
ADVERTISEMENT
Jul 1, 2014
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?
View 5 Replies
View Related
Mar 30, 2011
Find unused databases in a instance or when last used or accessed?
I'm on SQL SERVER 2008 R2 64bit -enterprize
I need to find when the databse is last accessed.
View 3 Replies
View Related
May 12, 2015
There are too many indexes built on DB. As per the naming convention it seems the indexes are built as per the suggestions provided from execution plan. I presume most of the indexes are used only once in a month for the reports but are hampering the performance of daily running queries. These are also occupying a lot of space.
To confirm on this I have used the below query to know & identify the unused indexes. I have recorded the counters before and after the huge operations and I observed NO CHANGE in any of the values.
What the below values exactly indicate and when do they change? Is it good to delete the indexes having low USER_SEEKS, USER_SCANS, USER_LOOKUPS?
Query:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
[Code] .....
View 2 Replies
View Related
Sep 13, 2004
Can anyone tell me a good way to monitor which indexes are not being used? Over time, I'm sure there are extraneous indexes in our database, which I would like to get rid of.
Any ideas would be appreciated.
Thanks,
Rob
View 3 Replies
View Related
Jan 15, 2015
How to find if there is a query that can be written on DMV's which will be able to retrieve the indexes that are not being used in a table.
View 2 Replies
View Related
Nov 13, 2015
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?
View 2 Replies
View Related
Jun 25, 2015
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.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
View 5 Replies
View Related
May 1, 2015
This application runs on a SQL Server 2008 R2 database.This application receives messages from an integration module. It has a core table: Table-A. Each message is inserted as 1 row into Table-A. Then when it is processed, that row in Table-A is updated.
There are two environments which are both connected to the same integration. So in both environments, Table-A has exactly the same amount of records inserted and updated. In both environments Table-A has around 80 million rows, with an extra 150,000 rows being inserted and then updated every day.Table-A has 8 indexes. For some reason unknown to me, the 8 indexes fragment really quickly in one environment but not in the other.
e.g. In Environment-1 the index fragmentation ranges from 0 - 19% and this environment has not been re-indexed for over 2 months.BUT a reindex was performed in Environment-2 and only 2 days later the index fragmentation ranges from 72 - 99.93%!
Our DBA has confirmed the re-index in Environment-2 completed successfully and has shown stats before and after the reindex to show that the 8 indexes for Table-A in Environment-2 went down to 0% fragmentation.
My question is, how can the indexes in Environment-2 fragment so much more quickly than the indexes in Environment-1? Both environments are on exactly the same hardware and have exactly the same inbound messages. The database on Environment-1 is actually a clone from Environment-2. The only known differences between the 2 databases is Environment-1 is STANDARD edition - SQL Server 2008 R2 (SP2) whereas Environment-2 is ENTERPRISE edition - SQL Server 2008 R2 (SP1). Could this difference be due to the Service Pack levels or even because one is STANDARD and the other ENTERPRISE?
This is what I have checked so far:
1) In both Environments all 8 indexes have "Set Fill Factor" unchecked and "Automatically recompute statistics", "Use row locks...", "Use page locks..." checked.
2) The "Index Usage Statistics" report in both Environments shows a similar amount of #UserUpdates and #UserScans
View 9 Replies
View Related
Jun 16, 2015
We have a maintenance plan that reorganize all indexes in a database. We disabled one of the index on one table this job failed. How to set up to the maintenance plan to without failing to ignore the reorganizing of disabled indexes?
View 1 Replies
View Related
Jul 15, 2015
When creating the snapshot I didn't choose the option copy non-clustered indexes. I created some indexes manually on subscriber database. Replication failed later with some issue.
so reinitialized with new snapshot but all the indexes are gone from the subscriber database.
How to keep the indexes without dropping from subscriber database whenever we reinitialized with snapshot
View 5 Replies
View Related
Apr 6, 2015
I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.
ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR
[Code] .....
View 2 Replies
View Related
Nov 5, 2015
If I rebuild some indexes that are above 30% of average fragmentation, should I after that update statistics?
Also, How can I see if I Need to update statistics^on the tables of my database?
View 3 Replies
View Related
May 25, 2015
Somewhere i read..that in SQL Server...usage of Built-in Functions on Columns, makes query optimizer to ignore indexes on that column...!
So lets say we have table EMP with Emp_id and Emp_Name....
Also we have Non-Clustered index on Emp_Name.
So following query would NOT use Non-Clustered index on Emp_Name column.
SELECTLEFT(emp_name, 3) as emp3
FROMdbo.EMP
Is this true? i am using SQL Server 2008.
View 4 Replies
View Related
Aug 14, 2015
I'm looking to dynamically remove records from tables dynamically using the information schema within SQL Server. Looking to remove records from all the tables within a schema. I have gotten as far as generating the script dynamically then using a while exist clause to execute the delete statements.
DECLARE@TargetSchema varchar(100),
--@LibNameData varchar(100),
@fnameIndex varchar(100),
--@startOFR_SCR_FILENAME_DATE varchar(25),
[code]...
Would like to execute the statements generated by the results from the information schema.
View 6 Replies
View Related
Mar 3, 2014
How do you find stale stored procedures ?
In a scenario where a developer created a slight modification of a stored procedure because he was afraid of breaking something else and took the easy way out, and a few more later down the line, multiple versions of a stored proc. doing slightly different things are just laying around.
"Last used" would be useful piece of information to determine the most recent date a stored procedure was called, either by the application itself or by another stored procedure itself called by the application.
Any stored proc not used for more than say 6 months would then be identified as a candidate for clean-up.
So - short or creating - after the fact - a trigger to update the usage date upon each call - which means a lot of work and no result for the next six months, how can one go about this ?
And could this be done in SS2K8 ?
View 5 Replies
View Related
Jan 22, 2008
Sometimes at the end of a project you'll end up with unused Tables,Stored Procs, Functions, and Views.Since there is not something like a 'SELECT' trigger ... what is thebest way for telling what is not being used by your solution? To turnon some kind of tracing?Any ideas?Thanks,Kelly GreerJoin Bytes!change nospam to yahoo
View 4 Replies
View Related
Aug 10, 2007
Hello all,
I've been struggling with an interesting problem. I currently have a solution but it is very slow.
I will be cycling data through a table. Each cycle has 1 million records with 60 fields. One procedure I need to perform on this data is a character cleanse. I have a list of 12 characters that need to be removed.
Right now I have a stored procedure that pulls the characters from a table one at a time. It feeds it to a nested loop that replaces the character with nothing ('') on records that contain the character (something like "update tbl1 set FIELD = replace(FIELD, '&', '') where Field like '%&%'"). This works... but seems rather inefficient. It can take 10 minutes to do a 250,000 record table.
I have tried borrowing regular expressions from VBscript using com objects, it worked and seemed more efficient at first but then I threw a large file at it and it took a half hour to complete.
Im running SQL 2005 on a dual Xeon 3.4 box with 2 gb of ram.
Any advice would be greatly appreciated!!
~~~Thanks~~
View 5 Replies
View Related
Jul 20, 2005
I am looking for some T_SQL code that would help us eliminateduplicate data entry into our mailing db. Finding and removing theexisting dups is not rocket science, but I am looking for a completesolution.We also want to be able to come up with a T-SQL script to beintelligent enough to know if:"Bill Jones" is the same as "Billy Jones" or"Bill Jones" is the same as "William Jones" or"Bill Jones at 123 My street" is the same as "Bill Jones at 123 MySt."Purchasing scripts from a company is not out of the question. Its amatter of script cost versus my hourly rate to create them... Needlessto say, something cheap.We have only heard of one app that is dynamic enough to handle this,and the cost was way, way out of our range, 6 figures is almost 2years salary.Any ideas would be greatly appreciated.
View 4 Replies
View Related
Jul 11, 2007
We can use rs.exe to publish a report automatically, but how do we do the reverse - remove a report automatically?
Is there a way by using a MS utility?
Is there a way by writing code, using library objects?
Part of the problem we face is having servers with RS all set up with a particular connection string and published reports. We then want to change everything and we want to do it automatically.
View 7 Replies
View Related
Jun 27, 2007
I have a situation where my client wants to ensure that data which is removed from a SQL Server database is truly removed. That is, if I delete a record, or delete data from a row, can I be sure that the data is truly gone and that it's not just the internal B-Tree pointers that were removed? Can I trust that someone with access to the .mdb file could not perform some binary pattern matching on the file and find the data that I assumed was deleted (regardless of how difficult that task would be)? I'm using SQL Server 2000. Thanks in advance.
View 5 Replies
View Related
Jan 8, 2004
Hi,
Ive got a server which replicates an awful lot of constantly changing data - the db itself could be around 2gb in size, however with the replication data in it it has ballooned to 6gb. On top of this, there is all the data in the D:MSSQLREPLDATAunc directories which seem to represent another log of some description.
Can I delete this data? I cant see what it would be used for and it totals some 25Gb! Obviously I cannot have this lying around on my server.
Does anyone know what it is used for?
thanks
Pete Storey
View 5 Replies
View Related
Apr 29, 2015
I currently have a lot of XML blobs being stored in several of the production databases that I look after. They're there as a application "logging" feature, so that the developers can investigate issues if they ever occur.
I want to get these out of my OLTP databases asap and am currently looking at options. I know Brent Ozar recently posted an article about Content Addressable Storage systems [URL] ....
How do you handle point in time recovery? Any other ways of storing this data?
View 9 Replies
View Related
Feb 15, 2006
Hi,
is there a way to ensure a merge subscription is synchronised before it is removed from the publisher using SMO? - (it is a push subscription)
I thought I had it as there is a MergeSynchronizationAgent object on the SubscriptionDatabase object, but calling Synchronize() on this throws an exception saying it can't contact the publisher.
Any help would be really appreciated, thanks.
Graham
View 4 Replies
View Related
Feb 20, 2007
I created a setup package and installed it on my SQL2005 server. I cant seem to find anywhere on the server where they are installed, but I can access them from the Job schedule tool, which work for me.
However, now I have to upgrade the deployed SSIS and I cant seem to install on top of an existing one (gave me no errors but after testing it i realized it is still running the old version.)
My questions are:
1) How can I find and delete these SSIS packages from the server?
2) How can I just upgrade for a new version of a SSIS package?
I need to be able to do both things as at some point I may want to just remove some packages.
View 11 Replies
View Related
Apr 20, 2007
Hello,
We needed to detach a database that contained a target ServiceBroker service and wanted to do this without impacting the rest of our system. The idea was the ServiceBroker would enqueue message to this service to be pickedup when the database was re-attached. Our sequence to do this was:
1. Disable the queue on the target service forcing ServiceBroker to queue in the initating side transmission queue.
2. Detach the database.
3. Re-attach the database.
4. Re-enable the queue
Messages sent to the service have a specified LIFETIME. What appeared to happen was that messages were being stored in the transmission queue as expected but on hiting the LIFETIME period they were being removed from the transmission queue and were therfore "lost".
Has anyone else experienced this or can anyone suggest what we are doing wrong.
Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)
Any suggestions gratefully recieved.
View 5 Replies
View Related
Mar 29, 2008
Hi everyone,
I am using this temporary data table which gets cluttered after certain time (table is used for registering data waiting for email confirmation).
Is there a possibility to empty a data table automatically every day (at a certain moment)?
Kind regards,Maxime
View 2 Replies
View Related
Oct 4, 2005
I need help using the TSQL Command.... I want to display car reviews by authors.... a car may be reviewed by multiple authors.... but the latest entry should be displayed... the older enteries will not be displayed....the following is the query </P><FONT color=#0000ff size=2>
<P><FONT color=#000000>SELECT CarReviews.Date AS Date, CarReviews.Company AS Company, CarReviews.Name AS Reviewer, CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image AS Image, CarReviews.Id AS IdFROM CarReviews INNER JOIN CarMake ON CarReviews.MakeId = CarMake.Id INNER JOIN CarModel ON CarReviews.ModelId = CarModel.IdGROUP BY CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image, CarReviews.Id, CarReviews.Name, CarReviews.Company, CarReviews.DateORDER BY CarReviews.Date DESC</FONT></FONT></P><FONT color=#0000ff size=2>
<P></P></FONT>
<P>Results of above statement....
Date Company Reviewer MakeName ModelName YearName Image Id
----------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------- ----------------------------------------------------------- -----------
10/3/2005 5:12:25 PM LATimes.com Dan Neil MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 8
10/3/2005 5:11:41 PM Star-Telegram.com G. Chambers Williams III MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 7
10/3/2005 4:51:31 PM MotoFlare.com Joe Wiesenfelder MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 6
10/3/2005 4:49:22 PM MotoFlare.com Jim Flammang MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 5
10/1/2005 4:19:25 PM MotoFlare.com Erphan Rajput Audi A4 (2005.5) 2005 RAJ-0089/RAJ-0089-80.jpg 3
10/1/2005 4:19:25 PM MotoFlare.com Jim Flammang Audi A4 (2005.5) 2005 RAJ-0089/RAJ-0089-80.jpg 4 Problem is repeation... In the above statement .... the statement should return only 2 records... but it is displaying 6 records....record one should be of MINI Cooper S 2005 & second should be of Audi A4 (2005.5) 2005Urgent reply will be highy appreciated...Thanks,Erphan Rajput..
View 1 Replies
View Related
Jun 13, 2008
We have a N+1 SQL 2005 x65 SP2 + 3159 cluster which was running fine until the service account which runs Windows cluster and the SQL services was removed as a login from SQL. This was a new setup so the only SQL accounts are (SA) as SA, Windows Domain GROUP's which one group is configured as SA and the cluster account is added to this group. I cannot start SQL service on the cluster now. I've tried logging into one node as the service account which is member of a AD group which has SA rights to SQL as well as my personal account. If anyone knows a way ot fix this without reinstalling I'd appreciate your help.
View 8 Replies
View Related
Mar 21, 2006
Hello...
I am going crazy trying to figure out how to do this. I have a flat file which I am massaging the data and loading into a table here is an example of a line out of the flat file:
"ABC NUTRITIONAL PRODUCTS","550","","","N","FAIR OAKS","","","COLORADO SPRINGS","C0","","","","","","","","A","","",""
My problem is that I have one field which is this address in a concatinated form. The fields that do not apply to this entry are suite#, floor# and other columns which are designated by the "" characters. The final concatinated addres field looks like this:
"550""""N""FAIR OAKS"""
I would like to remove the "" characters in the concatinated string. I just don't know the best way to do this? I was told DTS had a way of removing the "" from the flat file source. Since I have not used DTS extensively I am not sure if this is true. I was wondering how in SSIS I could go about removing the "" marks without removing the "" say if someone is quoted eg. John said "This is only a test". Removing the quotation marks in this instance would be changing the data. I am not sure how to do this and any help or advice is greatly appreciated!
Thank you...
View 1 Replies
View Related
Sep 11, 2015
I've done already the schemas for all the tables a you suggested.But when I created a new table the dbo prefix (dbo.ADM.NewTable) appears.I'd like this dbo not to automatically appear whenever i created a new table — as it requires me to alter whenever I created a new table.Is this something that is required to determine the creator of the table?Otherwise, I'd like that to remove.
View 7 Replies
View Related
Nov 15, 2015
I have this table:
id | Name | Age
==================
1 | AAA | 22
1 | AAA | 22
2 | BBB | 33
2 | BBB | 33
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
I need to delete from this table all the duplicate records and leave only one record. The table will looks like this:
id | Name | Age
==================
1 | AAA | 22
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
I work with sqlCE for Mobile...
View 8 Replies
View Related
Mar 31, 2008
There are a few features in the new SQL Server - Reporting Services that I really need in production. I have tested everything and it works great. I am running the CTP version since Microsoft is saying they aren't releasing the release version until 3rd quarter 2008.
Since Microsoft won't sell SQL 2008 until 3rd quarter, can I run the CTP in production until the release and then purchase SQL 2008?
Jim
View 1 Replies
View Related
