September 06, 2016

There isn’t a limit to the number of NULLs allowed in data sets but having more NULLs than you think you need may be a sign that it’s time to review.

Some applications require a high amount of NULLs. It is considered to be acceptable and is a built-in expectation. For example, I was working on a large ETL process and the staging server allowed NULLs as it fused data from different sources. This worked fine. But there were clean up processes which deal with the NULLs . There were post Import scripts which dealt with NULLs in preparation for generating reports

One of the major issues with allowing NULLs is the programming side can get inefficient. Some standard issues include having to construct WHERE clauses that need IS NULL or IS NOT NULL. These can play havoc with the Optimizer, leading to inefficient Execution Plans.

Another issue is built in functions . How will built in functions deal with NULLs?

Null values don’t index effectively. A search against a column with many NULLs can lead to slow response times. There are workarounds – various techniques , filtered statistics and indexes. The drawback for relying on filters is that they are useful for a search that finds somethings. Filters won't help searches that are looking for NULL values.

September 05, 2016

Question: I’m experiencing some strange behaviour with response from the SQL Server DMV sys.dm_db_index_usage_stats. I understand the sys.dm_db_index_usage_stats are refreshed every time there is a SQL Server restart. This is documented behaviour , which I build in to my understanding of how to interpret these statistics

The strange behaviour is when I execute a ALTER INDEX REBUILD and then check the sys.dm_db_index_usage_stats, the usage stats for the index disappear.

I’m using SQL Server 2012 Enterprise SP2

Answer: It sounds like you’re experiencing a bug which appeared in SQL Server 2012 and was fixed in SQL Server 2012 Service Pack 3 + CU3.

The bug is when a ALTER INDEX myIndex ON myTable REBUILD is executed, it deletes the usage stats for that index. It deletes them without a SQL Server restart.

This problem only relates to an INDEX rebuild.

This will have an impact on how you think about analysing index usage stats straight after an index maintenance plan. In the index maintenance phase the program will rebuild indexes. For this version of SQL Server , the index usage stats will flatten to 0.

In case you thought you were going crazy , you’re not!But I’d recommend you apply the relevant Service Pack and cumulative update.

July 11, 2016

Question: I was reviewing a list of sql indexes from a database and spotted some indexes prefixed with “_dta_index”. What are they ? How did they get here? And how can I clean them up.

Answer: These are hypothetical indexes created by the Database Tuning Advisor (DTA).

To find the indexes use the following the query

Use MyDB
GO
select * from sys.indexes where is_hypothetical = 1

If DTA is allowed to complete gracefully , the DTA cleans up the indexes. If the DTA crashes such as shutting down unexpectedly then the hypothetical indexes remain in place – under these circumstances , the hypothetical indexes will remain in the metadata files.

There’s a number of good reasons to keep these hypothetical indexes out of the database. Hypothetical indexes add a management overhead . Maintaining strict controls around the usage of DTA – particularly on a Production system is important.

You may attempt to delete the hypothetical indexes and find you cannot . There are potentially a number of reasons , such as :

July 04, 2016

When a predicate is SARGABLE we mean the sql statement is able to exploit the indexes supporting the sql statement. If the sql statemet is non – SARGABLE – we mean the statement is not capable of exploiting the indexes

If a statement is non-SARGABLE it can return the correct data set – as intended by the developer – but the response rate can be slower than expected. As the statement cannot exploit the index – it’s forced into a full scan. For the smaller data sets this may not appear to be a problem, but as the data sets get bigger , the impact may be severe. In extreme cases , application timeouts occur .

There are many reasons why non SARGABLE statements creep into the code base.

a) Poor database design may force a developer into all sorts of workarounds.

b) Lack of knowledge

c) Lack of quality control

are some of the reasons.

A statement such as the following will be non-sargable. The index will need to evaluate the Function for every row. if the index exists – it cannot exploit it

SELECT col1, col2 FROM myTable WHERE Function(Co) = ‘hdhdh’

You can apply different tactics to identify code following these patterns. This is an example of sql code reporting stored procedures which have LEFT or UPPER function. You can customise the code to your circumstances.

use mydatabase
GO
SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition LIKE '%UPPER(%'
OR m.definition LIKE '%LEFT(%'
AND o.type='P'

Producing a list of stored procedures may take a long time to work through. Normally, I’ll focus on the stored procedures with the highest count. This could be something like taking the query above and checking to see if the stored procedures is in the TOP count of stored procedures.

use myDatabase
GO
SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition LIKE '%UPPER(%'
OR m.definition LIKE '%LEFT(%'
AND o.type='P'
AND o.name IN (
SELECT
TOP 30 OBJECT_NAME(qt.objectid)
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
qt.[dbid] = DB_ID()
ORDER BY
qs.execution_count DESC
)

Query to find non – sargable sql statements is part of the SQL Antipattern series.