SQL Server News & Information tsql, performance tuning, industry trends, & bad jokes
tsql, performance tuning, industry trends, & bad jokes
This site is maintained by Jason Massie. He has 10 years experience as a DBA and has specialized in performance tuning for the last five. He was recognized by Microsoft as a SQL Server MVP. Jason has spoken at the Professional Association of SQL Server Conference, the North Texas SQL Server Users Group, SQL Connections and TechED. He has worked at Terremark (formerly Data Return) for nearly a decade.
You can contact him at jason@statisticsio.com or 469.569.5965
Abstracts addition Affinity Aggregation allocation Always Analysis Announced another API Appending article Authentication backup be Behavior between Bootstrapper Breaking Build Cache Caching Check checksums Codeplex collection Connecting contest Controller Creating CTEs CTP CUBE cursors Data Database DATALENGTH Debugging Design Diagnosing Diagnostic Differences Documentation DTS Emergency enhancement Entity ETW Exchange execution Express Extensions Fall February Filestream Filtered group GROUPING have Hosting Idle impact Improvement Increase Index Indexes Inserts Instances Interoperability Introduction IO large Late LOB local Localized Magazine Maintaining Maintenance Management maps March Microsoft minutes missing Mix Never November Offline OLE Online operations operators optimizations Optimized Overlapping Package Page Paging Panacea parallel part Partial Partition partitioned Partitioning PASS Performance PFS plan Plans Practices problem Problems Procedure Program programmatically Programming Protection Queries query read recent Recursive Related released Reports Restore return ROLLUP ROWCOUNT Runtime Security Select Sequence sequential Server Services set SETS Shooting shorts sizes Solutions Sortable SPARSE Spool SQL SQLIOSim SSIS Stalled Star Statement Statements stats Stored strategy Stuck Studio Submission Subreports Suggested Summarizing system Table Tables Tampa Task Than there through Timeouts Total Traces Transaction transfer Tricks Trouble TSQL turning understand Understanding undocumented Unique unused upgrade Upgrading Useful Value variables VDI Vista Will Windows Wireless
In SQL 2000 I wanted to find unused indexes but found it to be difficult. I had to capture a huge trace that fully represents the workload and “trust” that the ITW knows what it’s talking about. You could also use the scan started trace event filtered by dbid, objectid, indexid. This procedure was very tedious.
In SS2005, a quick query of the dynamic management views lets you know which indexes are not being used. You can do this in a few minutes what had previously taken days or weeks. This alone is a very powerful feature.
I am proposing that you take it a step further. We are going to use a little logic and the missing index DMV’s to combine indexes and remove indexes that are still used but redundant. The steps in this process would look like this:
1. Remove unused indexes with the unused index script
2. Get your list of tables to analyze.
3. Remove redundant but used indexes.
4. Revue missing index DMV’s for mistaken index drops.
5. Combine indexes that where it is logical to do so.
6. Revue missing index DMV’s for mistaken index drops.
This methodology is most effective and viable when these conditions are met:
· The server has been online and thus collecting stats for a long time.
· The server is not pushing a hardware bottleneck. If so, this should be done during maint window.
· The server is enterprise edition and the tables allow online operations (i.e. no LOB data or partitions).
What are the benefits of removing unused indexes?
· Reduced writes during updates
· Reduced space usage
· Reduced backup\restore space and time
· Reduced index maintenance time
What are the benefits of removing unused indexes, used but redundant indexes and combining indexes?
· Less memory footprint for the same amount of data
· A different angle to index tuning
· Indexes are more likely to be covering
Now, let’s get down to business…
Remove Unused Indexes
This is pretty straightforward and relatively safe. You want to make sure that your server has been up long enough to get good index usage. This includes infrequent operations like month end reporting, etc.
--Unused indexes
declare @dbid int
select @dbid = db_id()
select object_name(s.object_id) as ObjName
, i.name as IndName
, i.index_id
, user_seeks + user_scans + user_lookups as reads
, user_updates as writes
, sum(p.rows) as rows
from sys.dm_db_index_usage_stats s join sys.indexes i on s.object_id = i.object_id and i.index_id = s.index_id
join sys.partitions p on s.object_id = p.object_id and p.index_id= s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1 and s.index_id> 0 and s.database_id = @dbid
group by object_name(s.object_id), i.name, i.index_id, user_seeks+ user_scans + user_lookups, user_updates
order by reads, writes desc
You can most likely drop any indexes with zero or close to zero reads. The more rows, the more space you will reclaim. The more writes, the better write performance you get by removing them. If the index is not being read but writes are minimal and rows are low, there is little benefit of dropping it. Keep in mind that not having an index that you need is a lot worse than having an index you don’t need in most situations. I suggest starting off with a conservative approach.
Now that we have gotten rid of the low hanging fruit, we can get deeper. I get a list of tables by size and work my way down.
select object_name(object_id), max(rows)
from sys.partitions
group by object_name(object_id)
order by 2 desc
After running that query we see that tEvent is the largest table and should provide nice gains. This is where we start. Let’s use this schema for our example.
create table tEvent
(EventID int primary key clustered,
EventType int,
EventName varchar(100),
EventDetailID int,
CustomerID int,
CompanyID int,
DateOpen datetime,
DateClose da