Welcome to the third and final in the series of SQL Server Performance articles concentrating on indexes. In previous articles, we have looked at how to identify duplicated indexes, and also how to identify potentially bad indexes. This one follows on from there and asks the question - ‘Do I have any Missing Indexes in my SQL Server Database’?

Trying to find a missing index (or indexes) is a bit of a two-edged sword if you do it in a piecemeal fashion, the management view (DMV) that identifies missing indexes works on individual SQL Statements rather than as a whole. This means that taking the advice offered and creating the missing index would almost definitely improve the individual query it was generated from, but may also have the unwanted side effect of reducing overall performance for some of the other TSQL queries at the same time.

The script below can be used to identify the top 50 missing indexes from your database:
 

Again, the results gained from the missing indexes script should be used as a guide rather than a full recommendation. If you do want to consider a larger range of SQL queries then you should definitely take the time to get to know how the Database Tuning Advisor works properly.

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, we would be happy to discuss your requirements in more detail and tailor a solution to fit. Please do not hesitate to contact us.

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

Mike Metcalf, Technical Consultant, Celerity Limited

To view this article on Celerity Limited website, please click here.

SQL - 2nd Article in a Series of 3

Posted on: 17/04/14

Following on from my previous post about duplicate indexes existing in SQL Server, this time I am going to shift focus slightly and look at trying to answer the question ‘Do I have any Potentially Bad Indexes in my SQL Server Database?'

What is a ‘bad’ index? That’s a very good question. I tend to think that a 'bad' index is one where the benefit of it being there is outweighed by the cost of creating and maintaining it.   Additionally, you may have indexes that have never been used, again these should potentially be considered to be ‘bad’, but see the caveat below.

The following script will help you to identify where potentially ‘bad’ indexes may exist:-

 

NB. Please do not treat any index identified by the above script to be an automatic candidate for removal, whilst it may be the case that it receives a lot more write activity than read activity, or may not have been used at all since the last restart, it may still be important for a certain part of the system using the database.

I would suggest that you treat the results of this script as a guide only, and have someone that properly understands the system evaluate whether the index is worth keeping, or whether it could be removed/incorporated into another to reduce the write activity.

In my next rticle, I will take a look at a further aspect of SQL - Server Indexing and ask the question ‘Do I have any missing 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, Celerity would be happy to discuss your requirements in more detail and tailor a solution to fit.  Please do not hesitate to contact us.

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

Mike Metcalf, Technical Consultant, Celerity Limited

To view this article on Celerity's website click here

Should Software Licensing Influence your Platform Strategy?

As the old saying goes, hardware eventually breaks and software eventually works. In many organisations they are often managed as two entirely separate entities, but should software licensing influence your platform strategy?

All software packages have different licensing models. One of the most common approaches is to base it on the number of CPUs or Cores whilst some licensing is based on the number of sockets in a system.

It was all so much simpler years ago when systems had processors that got faster every two years and everyone just bought a new server with much faster processors and carried on as before. This doubling in speeds pretty much ended around the 5 GHz range* and it all became about more throughput and less about GHz. Some people still get confused between CPUs, cores and sockets so I’ll try to simplify it.

 

Sockets: A socket is where the CPU chip is connected into on a systems motherboard, and so it pretty much refers to the chip itself. Motherboards can have multiple sockets that can in turn accept multi-core chips.

CPUs and Cores: These terms are effectively the same, a core is effectively a CPU sitting on the chip (socket). As mentioned above for years, this was a one to one relationship. One chip had one CPU. All the latest chips have multiple cores inside them. So one socket (chip) may have 4 or 8 cores.

If we take that information and look at the example of a server that has two sockets each with 4 cores (8 CPUs/cores in total) versus another perhaps newer server that has two sockets each with 8 cores (16 CPUs/cores); in a licensing per socket model each of the systems would require two licenses whereas with a CPU/core base license model the difference would be 8 – 16 licenses required.

When you start to look at the different models for different software vendors (using the example above) this can have a significant impact across a server estate. Particularly when considering factoring in different virtualisation technologies, do you license against the cores allocated to each virtual machine or to the server as a whole?

We have only scratched the surface here, but the point is to demonstrate that it should definitely factor into your strategic thinking around platforms and architecture. Not just deciding which products to use in isolation, but in relation to the hardware platform, operating system and all the way up the stack. Particularly when you realise that hardware prices have come down over time whereas software and licensing costs in the main, arguably have not.

So to answer the original question: software licensing can and should drive your Server Platform thinking and strategic decisions.

*Based on IBM Power 6 Processors

Still not convinced? Then contact Celerity Limited today to find out more.

Neil Hulme, Technical Consultant. Celerity Limited

-To view this article on Celerity Limited's website, please click here

Celerity InTouch Newsletter Issue 9

Posted on: 03/04/14

To view this newsletter on our website click here

Highlights   Issue 9   IBM Tivoli and Butterfly Assessment | Celerity Becomes Dell Preferred Partner |Celerity Adds 'OEM' to Microsoft Silver Competencies | Disaster Recovery | IBM  Sale of x86 Server Business to Lenovo | Flash at the price of disk! | Training Corner | Employee's Profile

Celerity Becomes Dell Preferred Partner Celerity was awarded Preferred Dell Business Partnerstatus earlier this year after both technical and sales teams undertook rigorous training to achieve the required competencies and accreditations needed to support the Dell Preferred Partnership level. For more information please call Alan Mackenzie-Wintle on 07711 137368, email alan.mackenzie-wintle@celerity-uk.com or click here to visit our Dell web page. Read Full Article

Celerity Adds 'OEM' to Microsoft Silver Competencies Celerity has once again further strengthened its Microsoft offerings by adding OEM to its ever growing list of Silver Competencies, having already achieved Hosting, Server Platform and Midmarket Solutions Providercompetencies. For more information on Celerity’s Microsoft offerings please contact your Celerity Representative today. Read Full Article

Disaster Recovery by Steve Laidler, Celerity Technical Consultant The thing about disasters is that most of the time nobody knows they are about to happen – except, on occasions, the weather forecasters. But even though someone can predict an impending event, they do not necessarily know what the consequences will be. This means the people on the receiving end need to plan for any and every possible thing that can go wrong. Read Full Article

IBM Announce Sale of x86 Server Business to Lenovo Early this year IBM announced the proposed sale of its x86 server business to Lenovo (subject to regulatory approval). The threat of sale has been hanging over the x86 business since it was first reported nearly twelve months ago; but there is now clarity that this will happen with Lenovo confirmed as the purchaser. Peter Reakes, Sales Director at Celerity commented "This is positive news for both Celerity and more importantly for our customers." Read Full Article

Run applications as fast as your business demands by putting 100% of your hot data on flash, and don’t forget –you can do it at the price for disk!! Contact us today on 01772 542450 email dell@celerity-uk.com or click here to visit our Dell web page.

Training Corner Celerity Spring Sale Starts Now... Security - 40% off Check Point & Juniper training delivered April & May Storage - 5 day NetApp courses as 4 day fast-track versions to fit into the bank holiday week. Virtualisation - VMware ICM as a 4 day offering on 14-17 April and 22-25 April - London, Harrogate, Nottingham, Virtual - £2495 Sale Price £1497 NetApp Data On Tap 7-Mode Administration - 14-17 April – London, Harrogate, Nottingham, Virtual - £3840 Sale Price £3072 NetApp Clustered Data On Tap 8.2 Administration – 22-25 April – London, Harrogate, Nottingham, Virtual - £3840 Sale Price £3072 Please click here to visit our Training Portal for a copy of the full course schedule. To register or check availability please emailtraining@celerity-uk.com or telephone 01670 528240

Latest Accreditations Congratulations! to Celerity Sales and Technical Team Members… For achieving all the competencies and accreditations required to support Celerity becoming a Dell Preferred Partner. Gary Eckman for achieving ITIL Foundation certification Darren Sanders for achieving IBM Certified Specialist - High-End Tape Technical Solutions V7 and IBM Certified Specialist - System Storage DS8000 Technical Solutions V3 Paul Hunt & Darren Ashley for achieving IBM Enterprise Storage Sales Specialist Edward Yates for achieving IBM Certified BPM Program Manager - WebSphere Lombardi Edition V7.2, IBM Certified Specialist - Enterprise Storage Technical Support V3 and IBM Certified Specialist - XIV Storage System Technical Solutions V4 Neil Hulme for achieving SuSE Certified Linux Administrator (CLA)

Employee Profile The latest member of the Celerity team to go under the spotlight: • Alan Mackenzie-Wintle • Dell Business Manager • Based at Ascot • Worked at Celerity 2 years   Read the Q&As

Meet our Expanding Team  (click on each photograph to read their full profile)

Dominic Cheeseman Business Development Manager Based at Ascot   David King Technical Consultant Based at Preston   Zoe Farnworth Internal Sales Based at Preston

T +44 (0)1772 542450+44 (0)1772 542450  |  W www.celerity-uk.com  |  E info@celerity-uk.com

- See more at: http://www.celerity-uk.com/news/262/celerity-intouch-newsletter-issue-9#sthash.bWJJnt4H.dpuf

Disaster Recovery

The thing about disasters is that most of the time nobody knows they are about to happen – except, on occasions, the weather forecasters. But even though someone can predict an impending event, they do not necessarily know what the consequences will be. This means the people on the receiving end need to plan for any and every possible thing that can go wrong.

Take the floods in the South of England as a most recent example of good old wrath-of-god type stuff. It is fair to say that quite a few businesses will be closed or severely impeded by the loss of their computer systems from water damage and loss of power.

• How many of them have a backup ?
• How many of them have a disaster recovery plan ?
• Will they ever recover?

The implications of not having a plan when disaster strikes are only ever really understood when disaster happens, or even a near miss. Flooding means that not only is a redundant environment a good idea, it may be the only thing that can function, especially when a large scale flood knocking out power for extended periods of time occurs. Effects of water on electrical appliances are well known. Water damaged systems can be completely destroyed along with the data. However, how do you get spare parts or replacements to a flooded area by conventional means if the routes to and from your datacenter are flooded?

Your business is now in peril. You have no ability to respond to your customers, and it could be days or weeks before you can be operational. Your personnel may not be able to physically get to your datacenter. If the backups are held on-site;  things could be grim.

Celerity have a proven track record of resilient solutions design, encompassing disaster recovery and high availability that incorporates multi-site design, high availability clusters, backup and recovery together with storage management. We have worked with many organisations to ensure that their systems and processes are redundant, survivable and adhere to best practice. We pride ourselves on working with our customers to meet their requirements should the worst happen, and keep their systems operational.

Should you require any further information on disaster recovery or wish to talk to a Celerity Representative about your requirements, please do not hesitate to contact us.

Steve Laidler, Technical Consultant, Celerity Limited

To view this article on Celerity Limited's website click here