Friday, 26 May 2017

How to know which non-clustered indexes are not being used any longer

It is said that indexes are extremely useful to boost the performance of databases, especially indexes on huge tables. I am in agreement with that and I also support the idea of having the right indexes to achieve the best performance if they are created and used properly. Consequently, we have to vigilant of it, indexes may have been created to solve a particular problem in a moment, however, it does not mean that they are going to be useful forever but only for some days or months while data does not change too much. So, I should say as well that indexes are not the be-all and end-all for every business case, and we must always monitor the index usage and should consider the choose of dropping them if we verify they are not being using any longer. Keep in mind, not only could indexes be big and occupy a lot of space, but also impact on write operations negatively. Therefore, we should avoid wasting space, and having done that we can also optimise preventive indexes maintenance time.

This post will show a code to know exactly which non-clustered indexes are being used since the last restart of SQL Server engine. This code also filters out the indexes which are not related to primary keys. As you have seen in the code, we figure out the indexes by checking the value of columns user_seeks, user_scans, and user_lookups, if they are equal to zero then is says to us they were not used until this moment. Please be cautious, before using this code to get the indexes to be deleted, you should make sure that the database engine is not been restarted recently, otherwise, you will delete the wrong indexes and will impact on the performance tremendously. I strongly suggest using this code only if the latest restart of SQL Server has been done three months ago, more and less.

SELECT  i.type_desc,last_user_seek, last_user_scan, last_user_lookup,
        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]
, 'DROP INDEX ['+ schema_name(o.[schema_id]) +'].['+ OBJECT_NAME(s.[object_id]) +'].[' + i.name  + '];' as Statement_Drop_Index
,i.is_unique, i.is_unique_constraint, i.is_primary_key
FROM    sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
INNER JOIN sys.objects o on o.object_id = i.object_id
    ON s.[object_id] = i.[object_id]  AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
      AND s.database_id = DB_ID()     
      AND i.index_id > 1 and i.is_primary_key=0 and i.is_unique=0 and i.is_unique_constraint=0
      AND isnull(user_seeks, 0) + isnull(user_scans, 0) + isnull(user_lookups,0) =0 
ORDER BY  OBJECT_NAME(s.[object_id]), [Difference] DESC, [Total Writes] DESC,[Total Reads] ASC ;

When you execute it, you will also get a column containing the T-SQL code to delete the indexes which have not been used since the latest restart of SQL Server, and then you can execute it to delete them. To sum up, do not get me wrong, I am not against using indexes, on the contrary, I am in favour of using them properly, and if there were indexes that are not being using since a long time ago then they should be deleted. That's all for now. Let me know any remarks you may have. Thanks for reading.

Thursday, 11 May 2017

ObjectExplorer error: Dedicated administrator connections are not supported

Dedicated Administrator Connection (DAC) is a type of privileged connection that we can establish to SQL Server database engine when it does not respond because of too much workload. In this context, DAC is truly useful which allow to diagnosis and solve this sort of problems without restarting the database server any longer. Its is said that it is not possible to establish a DAC connection to the Object Explorer panel via SSMS so that we can get the following error while trying do it in that way:

   Cannot connect to admin:<SQLInstanceFullName>.
   Dedicated administrator connections are not supported. (ObjectExplorer)




It is true up to a point, but it does not mean that SSMS does not support DAC connections. Therefore, the question is:  can we ONLY establish a DAC connection to SQL Server by using SQLCMD? the answer is 'NO'. We also can do it via SSMS while creating a new SQL Query, which means that instead of clicking on 'Object Explorer' and 'Connect', we must click on 'New Query' and finally write 'admin:<Full Name of SQL Instance>' on 'Server Name' text box. That is all, it couldn't have been simpler.



Having done that, we will have a DAC connection ready via SSMS. Let me know any remarks you may have. Thanks for reading. Stay tuned.

Monday, 1 May 2017

Renewed as 2017 Microsoft MVP Data Platform - all roads lead me to help more

Today I have been renewed again as 2017 Microsoft MVP. This is my third award as Microsoft MVP Data Platform since 2015. Thank you all very much fro all your support. SQL Server has been my lifelong passion which inspires me to share much more with the SQL community, where I have also learned a lot. All roads lead me to help more and this makes me happy too.
By the way, I am not working for Microsoft, but SQL Server has given me a lot. I do not agree with some people who say that SQL Server is nowhere near as good as Oracle. Clearly, they might say it while struggling with performance issues and under that stressful situation it might be understandable. evertheless, in one way or another, I am going to speak out about some technician's opinions related to SQL Server.

To begin with, Microsoft SQL Server is cleverly designed and has an easy-to-use interface that relieves you from having to know arcane syntax details to complete any job. So, if other database management products do not have the same it is not a SQL Server's problem. is it not true that the top priority of any software is to make your life easier and more prolific? SQL Server just makes it. To give you an example, it has a dashboard that displays performance statistics  and notifies you of some situation that is worthy of attention. What's more, SQL Server provides scalable performance mostly when there is only one instance running per server, only then will it make efficient use of resources and eliminate the overhead of having various SQL instances struggling with shared memory and limited storage throughput.

Obviously, it is worth noting that it is heartedly recommendable carrying out maintenance tasks once a week at least to warrant strongly consistent data and to eke out every last ounce of performance. Finally, to be brutally honest, I am not one of the database engineers who speaks glowingly of SQL Server because I fully understand that after all it is not more than one of the many hugely powerful tools available to manage databases, and every solution depends entirely on you. So, in the unlikely event that you are not satisfied with the product, remember what I previously mentioned. SQL Server just performances as good as it is installed. It is of paramount importance to meet SQL Server minimum hardware requirements, otherwise, unforeseen problems may arise in the foreseeable future. Thus, it is the base on which SQL Server must always be installed. The major problem is mostly who designed and installed the database server, I mean the problem is the lack of knowledge, not the technology on its own. Do not place the blame on SQL Server on the grounds of one's inefficiency. So, the more you know SQL Server inside and out, the better the performance will be. As simple as that.