Identify Unused Indexes On Database

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


ADVERTISEMENT

SQL 2012 :: Identify Unused Indexes In Table

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

DB Engine :: Removing Unused NC Indexes From Subscriber Database

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

How To Identify Unused DB Objects For Cleanup

Aug 28, 2013

I need to setup a Server Audit in sql 2012 standard edition. The goal is to identify all procs, views, tables, functions that are not being used anymore. What is the best way to setup a server audit to accomplish this? What events need to be captured at a minimum so I can delete all the unused crap in this database. This is a smaller, growing company.

View 2 Replies View Related

Index Usage - Find Unused Indexes

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

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 View Related

Deleting Old Unused Database Issue

Nov 7, 2007

I have an old DATABASE that is not more in use , so I want to drop that database.
But before that I want to check that is there any application running on it or not.

can any one suggest me how to do this?

thanks

View 5 Replies View Related

How Can I Identify The Caller's Database From A Stored Procedure In Another Database?

Apr 2, 2008

I am wondering if there is a way to create a procedure in a logging database that can identify the source database from which it is invoked. DB_NAME() of course returns the name of the database in which the stored procedure exists. I could pass the database name as a parameter to the proc, just wondering if there is another way.

This is SQL 2005, I did look into the sys.dm_exec views but nothing seems to have a dbid reflecting the calling context.

Thanks,
Mike

View 4 Replies View Related

SQL Server Admin 2014 :: How To Identify Data Leakage In A Database

Dec 29, 2014

how to identify the data leakage in a database , as I heard in one of my environment?

what is the meaning for data leakage ?

View 3 Replies View Related

SQL Server 2008 :: Capture Database / Server Name In A Derived Column To Identify Source Of Data?

Feb 1, 2012

I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.

When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.

The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.

For Example I have Products table on Server 1 and 2:

Server 2 has more Products and would like to join the two together to create a staging table.

I want see the following:

Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2

get database name and server name in DATA FLOW only (without using a for each in Control Flow)

View 5 Replies View Related

SQL Server 2008 :: Identify If Remote Login Resolved Database Server Via IP / Servername Or FQDN

Feb 16, 2015

Is it possible to view the Connection String information of a remote login/session? I want to know if the login is looking-up the database server via IP address, servername (NetBIOS name) or fully-qualified domain name (FQDN).

Using these DMVs I can get a lot of relevant information:

sys.dm_exec_sessions
Program Name (eg. Microsoft SQL Server Management Studio),
Client Interface Name (eg. .Net SqlClient Data Provider)
sys.dm_exec_connections
Net Transport (eg. TCP),
Client Net Address and TCP Port

but not how the server's IP address was resolved. Is the connection string ever sent by the client to the server, or just used for DNS lookup?

View 0 Replies View Related

Database Indexes

May 9, 2007

Hello Group



I am trying to create indexing structure for my table in SQL Server 2005. For the way i wanted the indexing I cant use the "create Index " statement. Does anyone tell me how to create indexing strctures from scratch ( like using select statements etc ) or any other ideas. waiting for ur replies guys.



Thank You

Rishi Kanth

View 11 Replies View Related

Removal Of Selected Indexes / Script Index Create For List Of Indexes

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

Drop All Database Indexes

Apr 6, 2004

How can I drop all the indexes in all the tables in a MS 2000 SQL Server?

View 9 Replies View Related

Listing All Indexes Within A Database

May 23, 2007

Working with sql server 2005. Is there a way through Management Studio to show all indexes within a db?

thanks.

View 2 Replies View Related

Re-Create All Indexes In Database

Jun 17, 2006

First, I'd like some help stablishing if it's a good idea or not what I'm thinking, and second is what would be the easyist way to achive the result.

Background. We have a multicompany ERP system (200GB, 200+ tables). All data tables have a companyid, counter1id, counter2id, fields. Each table has a primary clusterd index on these fields in that order. And also has multiple other indexes that usually include companyid filed first. ( Note we have 10 companies, ids are 1..10, companies 2 and 5 are the most active, inserts, selects ). MY GUESS IS THAT THIS IS VERY BAD HAVING COMPANYID FIRST. AM I CORRECT? LEADS TO AL LOT OF FRAGMENTATION? VERY BAD TO FIND DATA ON THE INDEXES?


I want to re-create all my indexes and have them counter2id, companyid, counter2id. ( counter1id is a counter for each company, and counter2id is a global counter like identity ) GOOD OR BAD IDEA?

Since I have hundreds of indexes what is the correct procedure? I was thinking on scripting the database, then dropping all indexes, then recreate clustered indexes, then recreate other indexes. Do I have to take care of anything else foreing relationships, etc.

View 6 Replies View Related

Retrieving Information About All Indexes In Database

Sep 8, 2003

Hi,
I want to get information about all the indexes in a database.
Can any one suggest me a query for this.

Thanks.

View 2 Replies View Related

Rebuilding Large Database Tables And Indexes

Jul 7, 2015

I have come across a database system which isn't designed to work optimally. It is fairly large (~400GB) and performance of loading and querying is degrading (improper data types, fragmented indexes, non unique clustering key and other problems). So, I have quite a task in front of me, but I am up for the challenge. I figure this is not a unique situation, many of us would have come across this before. I have done this before too, but only for smaller databases, some of the operations here I expect to take a couple of hours or more to complete (depending on load/infrastructure speed etc, I know).

My plan is thus:

+ Take a full backup of the database
+ Set the recovery model of the DB to simple
+ Drop non clustered indexes
+ Drop clustered indexes
+ Remove PKs (wrong data types, too large!)
+ Narrow data types (add new column, update column in batches to old value, rename new column to old column)
+ Add PKs, which will create clustered indexes automatically based on PK ID
+ Create non clustered indexes
+ Run a SHRINKDB (normal operations I would never do this, but this is a special case, ensure log file is truncated to a logical size especially after all those table modifications...)
+ Set the recovery model of the DB to Full
+ Ensure everything works OK or better

View 9 Replies View Related

Generate Script Of All Indexes In A Particular Database In Format

Jul 15, 2015

I am in need to generate script of all the indexes in a particular database in the format "if not exist ....create index" format.I tried to google this I get for missing indexes, fragmentation level all that extra stuff, I need only in the format of "The script should be in "if not exist ....create index" format for a single database.

View 6 Replies View Related

Creating Indexes On Tables From The Database Explorer

Jan 16, 2006

I would like to create an index on a table from the Database Explorer.

I am using Microsoft Visual C# 2005 Express Edition.

I downloaded Microsoft SQL Server Management Studio Express with the intention of using it to create an index, but it does not seem to find the database that I created from the Database Explorer.

Thanks!

 

View 1 Replies View Related

DEBATE: Moving Heavily Used Database Or Breaking-off Indexes

Feb 20, 2002

Scenario:


We run a multiple database environment, with two of the databases receiving most of the user activity. (both write and read). These databases are roughly 25gb each and receive roughly the same amount of activity. Currently both of the .mdf files sit on the same drive shelf. Their log files are located on a separate drive shelf.

Debate: We have an extra fiber channel shelf available for us to use. We are not having too many problems related to performance, but we are always seeking for different ways to increase application/server performance. The debate centers on what to do with the extra shelf. There are two different suggestions on how best to use the shelf. They are:

1)Separate the .mdf files for two most utilized databases. This would separate the databases and the I/O associated with each across two different shelves

2)Break off the indexes for all 5 databases on to the extra shelf. This would leave all the .mdf files on the same shelf, but it would move the I/O associated with the indexes to a different shelf.

Can anyone provide the pros and cons of either suggestion?
I would like to see arguements for either side.


Dave

View 2 Replies View Related

SQL Server 2008 :: Indexes On Subscriber Database With Replication?

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

Copy A Database Table With All Its Data, Indexes And Constraints To A New Table In The Same Database

Feb 4, 2008

Hi,
How can I copy a database table with all its data, indexes and constraints to a new table in the same database in sql server 2005

View 7 Replies View Related

SQL 2012 :: Performance Limit On Number Of Indexes Per Table / Database

Oct 1, 2014

Is there a performance limit on the number of indexes per table / database ? With Filtered indexes there appear to be many more opportunities for more finely defined, and therefore smaller indexes resulting in many more indexes on a single table.

View 4 Replies View Related

SQL Server 2008 :: Display List Of Indexes - Add Database Name In Results

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

SQL Server 2008 :: Update Statistics On Tables Of Database After Rebuild Indexes?

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

Transact SQL :: Fragmentation Levels Of All The Clustered Column-store Indexes In A Database?

Sep 18, 2015

I have a  database in which I have  some tables in which I have implemented Clustered columnstore Index. How to find the fragmentation levels of all these indexes via  a single T-SQl script

View 3 Replies View Related

Unused Space

Mar 1, 2006

How can I determine how much unused space to remove from database files? I am setting up a maintenance plan and I am trying figure that out.
Also, is there a rule on how far to shrink TL?

Please help. Thanks.

View 3 Replies View Related

Help Regarding Unused Columns

Aug 23, 2007

Dear Experts,
I'm working for an ERP company, for our product, there are around 2000 tables. in each table, we are using 10 extra columns for our future use. all these are using nvarchar(50) datatype.

almost 95% of the columns in these are not in use almost. those are having null value and in some columns, the values are like 0,1...

is this affects the performance?


please spare your valuble time

thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 2 Replies View Related

Very Confused - Unused Space

Mar 29, 2001

Hello,
I have a table which resides on a diff.filegroup.
The space allocated is 7700MB.
Now ehen I use sp_spaceused 'table_name' to see how much space is left it's giving me data as
rows reserved data index Unused
at 1000hrs 629879 6777904 5068344 8 1709552
After an hour 637537 6780336 5070800 8 1709528
After 2hours 643883 6782560 5072904 0 1709656
After 2.5hrs 646887 6783584 5073920 0 KB 1709664 kb
after 3hrs 647239 6783712 5074056 0 KB 1709656 K
If you note rows are increasing,so is the reserved place and data but unused is also increasing.I think it should come down.
What behavior is this?How can I find out how much space is left in this table residing on diff.file group?
TIA

View 3 Replies View Related

How To Audit For Unused Login Ids???

Dec 9, 2002

I would like to verify that we do not have any unused login ids. I know I can turn on auditing for successful login attempta and either manually check the logs or write some type of findstring code to capture successful login attempts and then for each success, remove the id from a master list, but I was wondering if there may be a simpler approach. Any thoughts?

Thanks, Dave

View 4 Replies View Related

Updates And Unused Fields

Aug 15, 2005

I have a 3rd party developed app which has over 42000 data fields in 1200 tables. It is a heavy transaction type app. Of these fields over 10,000 are null for every record. SOme of their homegrown references these fields (and this can be removed), and in many instances many of the fields are not referenced at all. My opinion is that these extra fields are adding a heavy overhead to updates, inserts etc to the server. Any disagreements?

View 2 Replies View Related

HELP! How To Reduce The Unused Spaces ..

Aug 9, 2004

HI all,

I have a database that allocated:

Data: 7300 MB
Log: 2000 MB

But only used

Data: 5500MB
Log: 50MB

How can I free the unused space in the transaction log because
the database is getting too big.

Thank you for your suggestion.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved