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


ADVERTISEMENT

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

SQL 2012 :: Reclaim The Unused Table Space?

Sep 15, 2014

currently stuck with an issue where I need to reclaim the unused table space in SQL server.

ISSUE: Due to database size issue we have moved all the images from database to filesystem and want to reclaim the unused space now....I have tried shrinking the database and rebuild the indexes but didn't see any difference in the table or database size.

Similarly I have identified many Non clustered indexes on big transactional tables(~ 4 million records) that where not used since GO live 1 year back, so I wanted to drop these indexes to cut down the performance and maintenance overhead , so my question is will dropping these indexes reduces the database size?

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

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

SQL Server 2012 :: Why Indexes On Table Slow Down DML Operation On Table

Mar 7, 2014

Why the Indexes on table slow down the DML operation on table, what is the exact reason?

View 5 Replies View Related

SQL 2012 :: Way To Remove All Indexes From A Table With One Single Command

Sep 29, 2015

IS there a way to remove all indexes from a table with one single command ( without having to know what the index name is ) ;

View 2 Replies View Related

SQL Server 2012 :: Identify Sets That Have Same Items (where Set ID And Items In Same Table)

Feb 25, 2015

I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:

SetIdItemCode
1A
1B
24
28
26
310
312
410

[code]....

You can see that there are some sets that have the same members:

- 1 and 10
- 2 and 11
- 7, 8 & 9

What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.

View 8 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 2012 :: Finding Unused Stored Procedures

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

SQL 2012 :: Identify Source For Starting A Job

Jul 18, 2014

I have an odd one. I have a SQL job that doesn't have a schedule and is being run each morning. It is a legacy system and I am trying to document the data flow process and I am having a hard time tracking down where/what is starting the job. I see which user executed the job:

SELECT message FROM sysjobhistory WHERE job_id = 'jobid' AND run_date > 'yesterday'

Which is useful, but I want to know what is starting the job.

View 2 Replies View Related

SQL Server 2012 :: Identify Sets Of Rows

Jul 15, 2015

I have the following sets of records:

ColA ColB
----- -----
21 A
22 A
23 A
24 B
25 B
26 D

What I want is to be able to identify a set sequence (1,2,3) based upon ColB such that I'd get the following result:

ColA ColB ColC
----- ----- -----
21 A 1
22 A 1
23 A 1
24 B 2
25 B 2
26 D 3

I know that I should be able to get it using ROW_NUMBER() OVER (PARTITION BY ColB ORDER BY ColA), but instead of getting the sequence (1,1,1,2,2,3) I get (1,2,3,1,2,1). Using DENSE_RANK gave me the same results.

View 4 Replies View Related

SQL Server 2012 :: Condition To Identify Values That Are +/- Than 2 Cents?

Jul 23, 2014

I want to identify rows that go negative but only for 2 cents or more as well as identify rows that 2 or more.

I have this expression that does not work how I want it to work:

CASE
WHEN (SUM(FavUnfavCostChange) < (2/100) THEN 'Less'
WHEN SUM(FavUnfavCostChange) > (2/100) THEN 'More'
ELSE NULL
END AS 'Flag'

But I get:

0.00000815000000000000More -- this is not more than 2 cents, is just a positive number
-0.00094700000000000000Less -- this is not less than 2 cents, is just negative number
-0.00222000000000000000Less -- this is not less than 2 cents, is just negative number
-0.00012250000000000000Less -- this is not less than 2 cents, is just negative number
0.00000000000000000000NULL -- this is zero so null is fine
0.01188576000000000000More -- this is not more than 2 cents, is just a positive number

View 3 Replies View Related

SQL Server 2012 :: Data Compare To Identify Change

Mar 3, 2015

I am in process to develop TSql code to identify change in data.

I read about Binary_checksum and hashbyte. Some people say hashbyte is better than binay_checksum as chances of collision are less.

But if we may consider following, chances exist in hashbyte too. My question is what is the best way to compare data to identify change (I can't configure CDC) ?

select HASHBYTES('SHA','121'+'34'), HASHBYTES('SHA','12'+'134'),BINARY_CHECKSUM('121','34'),BINARY_CHECKSUM('12','134');

View 2 Replies View Related

Sp_spaceused Reports Over 40 GB Unused For A Table

Jul 20, 2005

I have a large table that I recently purged a year of data from.However, the table size in sp_spaceused hasn't decreased as much as Iwould expect. (there are no text or large object columns on thistable, BTW) Running sp_spaceused on the table shows the following.rows reserved data index size unusedTABLE_NAME23470880 67790808 KB 18116312 KB 3211616 KB 46462880 KBI ran a dbcc indexdefrag on all indexes on the table last night,including the clustered index, and I'm still seeing pretty much thesame amount of space reported as unused.The one thing that I haven't done yet is to run the sp_spaceused onthis table with the @updateusage flag set to true as I need to do thisafterhours to reduce contention. I will attempt this tonight andreport on the results.Here's DBCC SHOWCONTIG OUTPUT for the table:DBCC SHOWCONTIG scanning 'TABLE_NAME' table...Table: 'TABLE_NAME' (917578307); index ID: 1, database ID: 7TABLE level scan performed.- Pages Scanned................................: 2264447- Extents Scanned..............................: 285484- Extent Switches..............................: 287092- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 98.59%[283056:287093]- Logical Scan Fragmentation ..................: 0.04%- Extent Scan Fragmentation ...................: 3.19%- Avg. Bytes Free per Page.....................: 2418.9- Avg. Page Density (full).....................: 70.12%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Looking through the history of the group, the one thing that seems toremedy this problem consistantly is to bcp out all the data, trunc thetable and bcp all the data back in. This is not really a possibilityfor me due to the size of the table and the availability expectationsof my customers. Is there anything that I forgot to check? Do youthink that it's just a matter of incorrect statistics in sp_spaceused?Thanks in advance:Matt

View 4 Replies View Related

Reclaiming Unused Space In A Table

Mar 13, 2008

I'm trying to figure out how to reclaim unused space in a huge table (tbl) after setting one of the text columns to be the empty string. Other tables have foreign key columns associated with tbl.

After doing something like this,

update tbl set col = ''

the table doesn't automatically reclaim that space. If I do a direct insert into a new table like this,

insert into tbl2 (cola, colb, colc) select * from tbl

the new table is smaller as expected (about half the size in my case). Any ideas?

Rob

View 3 Replies View Related

SQL 2012 :: Login Information - Identify Who Changed Port Number

Jun 30, 2015

Is there a way to identify who have changed the SQL port number other than the DBA's?

For example through c2 audit or any other triggers?

View 3 Replies View Related

Release Unused Space Reserved For A Table

Sep 22, 2005

Hey guys,

I have a table which has 6 text columns (tblA).. I no longer require 1 of those text columns and want to reclaim the space that it is currently taking up..

Is the only way to BCP out all the data (except the 1 column i no longer require) drop the column and BCP the data back into the table?

View 12 Replies View Related

Reclaim Unused Space / Rebuild Table

Dec 30, 2014

best method to my issue regarding unused space. SQL SERVER 2014 BI EDITTION..I have a table that showed 62% Data, 7% unallocated, and 29% unused space.I ran the ALTER TABLE <table name> REBUILD, which changed it to 32% data and 67% unused.What I do with this table monthly and what I believe is causing this unused space issue is this:Every 1st of the month I get a csv file that holds around 3.2 million rows/15 columns of data. About 3 gbs every time.It's 13 months of data. I remove the last 12 months from the current table and import the new 13 month data.I do this twice a month and have no choice because the data is constantly updated and why we get it twice a month.So I am deleting around 6 gbs of data a month as well as adding around 6 gbs of data a month.

I believe this is why I end up with so much unused space, but just found the REBUILD command, which worked but now a small hit on performance and have too much space in the current table. I have read several times that shrinking the file is no good, but what other way to get rid of the extra space in this table? Also, the table doesn't have any indexes or primary key because of duplicates.

View 5 Replies View Related

Claim Unused Free Space At Table Level?

Feb 23, 2012

how to claim unused free space at table level. The database size is of 4.6TB, recently I deleted some data which is of almost 1.5TB but my unused space has grown upto 2.5TB leaving me short of space on my drives. How do I claim this usused on to OS Disk space?

View 12 Replies View Related

SQL To Identify The Primary Key In A Table?

Oct 24, 2007

Hi

I have been looking for a way to identify the primary key defined in a
table using a SQL Sentence...how can i do it?

Thanx for your support!!!

Diego Bayona

View 5 Replies View Related

How Can I Search A Schema To Identify Table

Feb 12, 2007

I am trying to find a needle in a haystack.

One of the tables in this database has a column named column_xyz.

There are over 800 tables, each with numerous columns. If I know that the column I am looking for is "column_xyz" is there an easy query I can do to identify all tables that contain that column?

I hope.

Thanks

View 4 Replies View Related

How Do I Identify The Base Table For A PageID?

Jul 19, 2007

Hi,

Have a LOB error which provides the corrupt PageID and this recommends a DBCC CHECKDB. However I'm unable to identify what the base table for the PageID. DBCC PAGE does not provide base table info (I think), DBCC PGLINKAGE has been retired in SS2005 and DBCC READPAGE doesn't work either.

Does any know of a valid SS2005 command or technique which will allow my to identify the base table for a PageID???

View 1 Replies View Related

How To Identify The Base Table For A PageID ?

Jul 19, 2007

Hi,

Have a LOB error which provides the corrupt PageID and this recommends a DBCC CHECKDB. However I'm unable to identify what the base table for the PageID. DBCC PAGE does not provide base table info (I think), DBCC PGLINKAGE has been retired in SS2005 and DBCC READPAGE doesn't work either.

Does any know of a valid SS2005 command or technique which will allow my to identify the base table for a PageID???

View 4 Replies View Related

Identify Range And Related Data From Look Up Table

Dec 27, 2007

I have a query/report that I need to create that needs to look at the size of a company and based on that size apply different rules. I am sure that this is not the only query/report I'll need to do using this and I'm also not so sure that the size ranges won't be changed in the future. Given this, I'd like to store the size ranges in a lookup(global) table. That way, if the ranges ever change I can just alter them in that table and not in all of the queries/reports that use them. What I need to figure out is how to join the live table with the look up table.

Specifically, here is what I have. The look up table would be:




Code Block
CREATE TABLE #gl_sizerange (
glsid int IDENTITY(1,1) NOT NULL,
lowsize int,
highsize int,
sizecat varchar(10),
milestone varchar(25),
days int
) ON [PRIMARY]
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Approach', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Interview', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Close', 7)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Approach', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Interview', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Close', 7)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Approach', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Interview', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Close', 7)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Approach', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Interview', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Demonstrate', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Negotiate', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Close', 35)




Then what I have is two more tables, one that indicates the size of the company and another that has the milestone contained within it and I will also calculate how long that milestone has been open and if it is longer than what is in the lookup table for that milestone and size range I need it returned in the report. Here are some quick sample table to represent that data (I've condensed the size and number of tables for the example):




Code Block
CREATE TABLE #en_entity (
enid int NOT NULL,
orgsize int,
) ON [PRIMARY]

INSERT into #en_entity VALUES(1, 5)
INSERT into #en_entity VALUES(2, 18)
INSERT into #en_entity VALUES(3, 24)
INSERT into #en_entity VALUES(4, 25)
INSERT into #en_entity VALUES(5, 47)
INSERT into #en_entity VALUES(6, 101)
INSERT into #en_entity VALUES(7, 499)
INSERT into #en_entity VALUES(8, 500)
INSERT into #en_entity VALUES(9, 10000)
INSERT into #en_entity VALUES(10, 567890)
CREATE TABLE #op_opportunity (
opid int NOT NULL,
enid int NOT NULL,
milestone varchar(25),
daysopen int
) ON [PRIMARY]
INSERT into #op_opportunity VALUES(1, 1, 'Approach', 5)
INSERT into #op_opportunity VALUES(2, 2, 'Interview', 18)
INSERT into #op_opportunity VALUES(3, 4, 'Negotiate', 24)
INSERT into #op_opportunity VALUES(4, 7, 'Demonstrate', 25)
INSERT into #op_opportunity VALUES(5, 7, 'Approach', 7)
INSERT into #op_opportunity VALUES(6, 9, 'Close', 35)
INSERT into #op_opportunity VALUES(7, 8, 'Close', 36)






So, given the sample data, I would expect the results to return me the following opids from the #op_opportunity table because they don't comply with what is in the look up table based on milestone, size and days open: 2,3,4,7

View 4 Replies View Related

SQL 2012 :: Create New Indexes

May 24, 2014

1)When we create Indexes, key columns are the columns that use in where clause and included columns are the columns that can be used in the select list and on join clause column.

2) I am thinking that we have to create new Index, only if we found at least 50 msec time save.

View 9 Replies View Related

SQL 2012 :: Disadvantage Of Having Too Many Indexes

Aug 6, 2015

Any disadvantage of having too many indexes?

View 3 Replies View Related

SQL Server Admin 2014 :: Identify Who Dropped A Table

Jun 30, 2015

I'm getting a replication error that an object (Table) was not found. Any script that can capture this information?

View 3 Replies View Related

SQL Server 2008 :: Identify DB Objects That Depend On Particular Table?

Jul 7, 2015

Any SQL that can be used to identify all objects that depend on a particular table? For example, sprocs, functions, views, etc.

View 3 Replies View Related

Transact SQL :: Identify Primary And Foreign Keys In Two Table

Apr 23, 2015

I've attempted to identify a primary and foreign key in these two tables, but I am getting a bunch of errors re duplicate keys and column names needing to be unique.Perhaps the primary and foreign key I have identified don't meet the criteria?

CREATE TABLE StockNames
(
-- Added Primary key to [stock_symbol]
[stock_symbol] VARCHAR(5) NOT NULL CONSTRAINT PK_stock_symbol PRIMARY KEY,
[stock_name] VARCHAR(150) NOT NULL,
[stock_exchange] VARCHAR(50) NOT NULL,

[code].....

View 19 Replies View Related

SQL 2012 :: Filtered Indexes In Server

Aug 14, 2014

What are driving criteria for creating filtered indexes on SQL server. I am trying to analyze the index stats through DMV,histogram and have to analyze if the filtered indexes should be created on tables. This exercise has to be done for all the transaction tables on the database. What are the approaches I should be looking on?

There was a deadlock on the DB because of huge writes on one of the big tables. Having filtered index on this table for the effected column would reduce the time taken for write operations. Hence we are looking for creating filtered indexes appropriately

View 8 Replies View Related







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