SQL

SQL

Posted on: 27/03/14

When taken as a whole, the area of SQL Server Performance Tuning is quite literally massive. Over the next few articles I am going to concentrate on covering the basics of performance tuning.

This particular Article concentrates on Indexing, and asks the question ‘Do I have any Duplicate Indexes in my SQL Server Database’?

Unfortunately, SQL Server does not restrict you from ‘accidentally’ creating duplicate indexes, there are a few exceptions to this rule (for instance, you can only have a single Clustered index per table), but not many. I have recently been asked to take a look at a customer database and identified over 30 duplicate indexes across the database.

So what you might ask? Well, a query is only going to use one of the duplicate indexes, which means that the other one is simply taking up space. Worse than this though is that when data is written to a table, each index for that table is also written to as part of this process, with the potential to reduce overall performance.

Duplicate indexes generally start to creep in when people use tools like the database tuning advisor (DTA) without really knowing what they are doing. The tool is incredibly powerful, but takes a decent amount of understanding in order to get the best out of it. The DTA will only analyse the TSQL that is put through it, this can be a large dataset, or right down to a single TSQL query. It does not consider any activity that has not been captured and analysed.

The following script can be used to identify duplicated indexes on a database.

In my next Article, we’ll take a look at a further aspect of SQL Server indexing and ask the question ‘Do I have any potentially bad indexes in my SQL Server Database’?

Celerity have a number of consultants who are experts with a wide range of Microsoft products including SQL Server. If you have any concerns about the performance of your system, or are looking for a health check, contact us and we would be happy to discuss your requirements in more detail and tailor a solution to your environment. 

Mike Metcalf, Technical Consultant, Celerity Limited

Disclaimer:

The scripts used in this article may have been created by other people and/or gathered from specialist sites the internet, no implication of ownership should be inferred.

To view this article on the Celerity Website click here

Analyse You Storage environment

Make the most of your back-up and recovery environment with Butterfly Metrics.  Drawing data from up to 4000 indicators, Butterfly provides comprehensive, easy to read single pane reports which enable a wide range of benefits including:  Rapidly visualise size, risk and complexity of your current environment with accurate performance, capacity and risk information. Understand scalability, performance and cost by easily comparing  IBM Tivoli Storage Manager data. Safely create and configure automated data migrations quickly and independently of live backup production environment. Celerity can provide a 40-50% saving with the Butterfly Assessment and implementation of Tivoli Storage Manager. To find out how to take advantage of this incredible solution click here and we will show you how.

- See more at: http://www.celerity-uk.com/our-solutions/data-storage-management/butterfly#sthash.EUvYhljn.dpuf

Celerity Becomes Dell Preferred Partner

Celerity was awarded Preferred Dell Business Partner status earlier this year after both technical and sales teamsundertook rigorous training to achieve the required competencies and accreditations needed to support the Dell Preferred Partnership level.

Preferred Partners are recognised as experts in key Dell product and service areas. To attain this status, they must meet several criteria and complete mandatory training.

We are pleased to be able to offer our customers the full Dell technology portfolio including Servers, Storage, Data Protection, Security, Networking, Systems Management, Information Management, Desktop Vitualisation, and Cloud Services and Solutions.

Celerity’s Dell Business Manager Alan Mackenzie-Wintle commented “By partnering with Dell Celerity can now offer our customers a wide range of choice when deploying best of breed infrastructure solutions. Dell has a fantastic portfolio of hardware and software solutions which are relevant to both our Public Sector and Commercial customers. When combined with Celerity’s proven technical skills, project management expertise, and knowledge of the IBM environment, we are uniquely placed to offer our customers a best fit solution for their individual requirements”.

For more information please call Alan Mackenzie-Wintle on 07711 137368, email or alternatively visitwww.celerity-uk.com/our-partners/dell

To view this article on the celerity website please click here