Functions and WHERE clause and suboptimal query plans

Using functions in WHERE clauses can cause query performance problems. The query optimizer attempts to create a cardinality estimate. If the Optimizer does not know the value to look up – it will base the cardinality estimate on the average per distinct value.

It is more than likely using the average of every distinct value – will lead to a less than optimized plan.

There is a benefit for developers using functions. Quite often data may not be clean and  a function may be used to deal with formatting or NULL .A developer may develop  a function – to be used across multiple stored procedures.

The Optimizer doesn’t know the value in advance – and as you can see from the example below, a new value is passed in.

 Designing queries to exploit the Statistics is critical for sql performance. There are many workarounds to using functions in the WHERE clause.  Deciding on which workaround is defined by the function complexity.

 An example workaround , would be to place the logic as an INNER JOIN . If the function was using a date lookup table – then creating an INNER JOIN to the date lookup table, would create the data set once , place in memory

An simple example of a function used in a WHERE clause and a workaround. Make sure you check the Execution before and after, to see the difference. Read this post to test properly - SQL Server - Query baseline testing - SQL Server DBA

Declare @period datetime
SET @period = avalue

Select t1.col1,t1.col2,t1.mydate 
FROM 
myTable t1
ON anotherTable T2 ON T2.main_id = t1.main_id
WHERE 
MYDB.dbo.fn_(t1.usedDate) = @period

--an example workaround


Declare @period datetime
SET @period = avalue

Select t1.col1,t1.col2,t1.mydate 
FROM 
myTable t1 
INNER JOIN anotherTable T2 ON T2.main_id = t1.main_id
INNER JOIN dateLookUpTable dl ON dl.adate = t1.mydate

 Read More

Filtered statistics, T-SQL best practises for T-SQL Tuesday

SQL Server Query Optimizer and Statistics

SQL Server - Index missing Statistics

SQL Server Performance Checklist - SQL Server DBA

Index Rebuild without logging

“Can I commit an Index Rebuild without logging?” is a regular question from developers and DBAs. 

Most DBAs run regular index maintenance scripts .One common problem is managing the Transaction Logs file growth during the procedure. Typical problems caused by sql log growth include running out of disk space. Therefore, the question related to Index Rebuild without logging .

SQL Server Transaction Logs record information in case of rollback.  For example, the  ALTER INDEX statement below  will drop  all indexes and rebuild in one transaction. If the indexes are very large and there are a large amount of extents changes, this will impact disk requirements. The Active Log  needs to grow to accommodate a  rollback in case of error.

The impact on transaction logs is :

 The start and finish of the transaction

All extent allocation and deallocation

Creating\dropping of index

 

ALTER INDEX ALL ON Database1.Table1
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

 

 

It is not possible to commit an Index Rebuild without some impact on Transaction Logs. It is possible to change the Recovery Model to Bulk Logged or Simple which allows minimal logging, but that does not stop logging.

There a good reasons for the requirement. Time is better spent managing the Index Rebuild process – focusing on techniques to Manage Transaction Logs during Index Rebuild

Read More

Modify sql transaction log file size - increase or decrease

SQL Server – Monitor SQL Transaction Log AutoGrowth for performance issues

SQL Server - Inside the sql transaction logs with fn_dblog and current transaction_id

SQL Server – Read sql transaction Logs with ::fn_dblog

Manage Transaction Logs during Index Rebuild

A DBA manages regular index maintenance scripts . There are many variations – usually based around – REORGANIZE and then at a higher fragmentation level do a INDEX REBUILD. One common problem is managing the Transaction Logs file growth during the index rebuild procedure.

The Transaction Log records relevant information in case of rollback. If a large amount of extents change , these are recorded and can create very large Transaction Logs

 As a general guide the minimum amount of transaction log space required is the size of the largest table.  An Index Rebuild will rebuild the whole table in on etransaction

Some ideas to manage Transaction Log growth during Index Rebuild:

1)       Take more Regular log backups . Consider increasing the frequency during the Index Rebuild phase

2)       Create proper sizings for the transaction log file

3)       Split up the Index Rebuilds over different maintenance windows. If  the database has a number of large tables – than restructure the maintenance windows

4)       Increase the threshold to trigger the Index Rebuild . In other words, commit REORGANIZE up to a higher level of frgementation

Read More

SQL Server Disable Indexes and Rebuild Indexes dynamically

SQL SERVER REBUILD INDEX

SQL Server - Disable Index and Rebuild Index for Updates on very large tables

Predict ALTER INDEX REORGANIZE finish time - SQL Server DBA

How to check for SQL ghost records?

Question: How can I check for  the SQL ghost records count?

Answer: Use the sys.dm_db_index_physical_stats view and check the ghost_record_count column.

The Ghost record count  is the row count in the index leaf level marked for deletion, but not yet deleted by the database engine

Ghost_record_count is the ghost record count awaiting removal by the ghost cleanup task in the allocation unit.

Version_Ghost_record_count  is the ghost records count sustained by a snapshot isolation transaction in an allocation unit.

 

SELECT 
db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count

FROM sys.dm_db_index_physical_stats(DB_ID(N'db_name'), OBJECT_ID(N'table_name'), NULL, NULL , 'DETAILED');

 

Read More

SQL Index Fragmentation and sys.dm_db_index_physical_stats

SQL Kill connections to a database

SQL Server - Monitoring a Rollback and sys.dm_exec_requests

 

Difference between UNIQUE CONSTRAINT versus UNIQUE INDEX

Question: Is there a difference between a UNIQUE CONSTRAINT versus UNIQUE INDEX?

Answer: Creating a UNIQUE CONSTRAINT automatically creates a UNIQUE INDEX. This makes a UNIQUE CONSTRAINT and UNIQUE INDEX similar.

The query optimizer interprets an explicitly created UNIQUE INDEX or a CONSTRAINT created index as the same.

Data is validated in the same way and both check for unique data

Deciding when to implement a UNIQUE CONSTRAINT versus a UNIQUE INDEX is an issue of design. A UNIQUE CONSTRAINT is part of the database design logical view. A UNIQUE INDEX is part of the database design physical view

 

--how to create a unique constraint
ALTER TABLE myTable ADD CONSTRAINT
            myConstraint UNIQUE NONCLUSTERED
    (
               A_col
    )

--how to create a unique index
CREATE UNIQUE INDEX uix_my_unique_index ON myTable(a_col)

 

 

Read More

SQL Table variables do not support indexes MYTH - SQL Server DBA

SQL SELECT INTO - SQL Server DBA

Modify a SQL CHECK CONSTRAINT - SQL Server DBA