Finding Implicit Column Conversions in the Plan Cache

I’m just sharing with you guys a script to find all implicit data type conversions associated with mismatched data types in a specific database.
It’s pretty handy when you are either developing or coding reviewing.

test

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME()); 

WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'),
   ic.DATA_TYPE AS ConvertFrom,
   ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
   t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo,
   t.value('(@Length)[1]', 'int') AS ConvertToLength,
   query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
JOIN INFORMATION_SCHEMA.COLUMNS AS ic
   ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
   AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
   AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

More details here: http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx

Clearing a Single Plan from Cache

Most of you guys know that you can clear all cached plans using DBCC FREEPROCCACHE.
In SQL Server 2008 you are able to clean a single pan from cache. The query below helps you to find the plan.

SELECT st. text, qs.query_hash,'DBCC FREEPROCCACHE(' ,plan_handle ,')'
FROM sys .dm_exec_query_stats qs
CROSS APPLY sys .dm_exec_sql_text( qs. sql_handle) st
WHERE st. text LIKE 'SELECT * FROM Person.Address%' -- your query

There is full example in the Books Online, so you might want to read the following link:
http://msdn.microsoft.com/en-us/library/ms174283.aspx

Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log will contain the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to ‘DBCC FREEPROCCACHE’ or ‘DBCC FREESYSTEMCACHE’ operations." This message is logged every five minutes as long as the cache is flushed within that time interval.

You can also clear all the plans for one particular database from cache. You can use the command:

DBCC FLUSHPROCINDB(<db_id>);

In the next post I will talk about how to force a specific execution plan.
I know that it’s a old subject, but I still see many people asking about it in the SQL Server community.

Sargable queries – Using indexes effectively

It’s really important to have effective indexes in your database, but also equally important to ensure that yours queries are written properly to use these indexes effectively.
So, one thing that we always need to keep in mind is to avoid using Nonsargable search conditions.

The word sargable is a contraction of “Search ARGument ABLE”, where the optimizer has the ability to seek a row in the index.

Sargable Conditions
The following predicates CAN be matched to an index seek operation:

=,>,>=,<,<=, and BETWEEN, and some LIKE conditions such as LIKE 'Search%'

Nonsargable Conditions
An index cannot be used to seek in the following predicates:

<>,!=,!>,!<, NOT EXISTS, NOT IN, and N0T LIKE IN, OR, and some
LIKE conditions such as LIKE '%Search'

So, basically you should use this:

WHERE ID BETWEEN 51825 AND 51828
WHERE Name LIKE 'Ice%'
WHERE ProductID = 771
WHERE UnitPrice < 3.975
rather than this:
WHERE ID IN (51825, 51826, 51827, 51828)
WHERE Name LIKE '%Ice%'
WHERE LEFT(Name,3) ='Ice'
WHERE ABS(ProductID) = 771
WHERE ProductID <> 771
WHERE ProductID NOT IN (771,772)
WHERE UnitPrice + 1 < 3.975

Also, we shouldn’t use functions in the left side (the field side). Functions on WHERE clause columns also hurt query performance for the same reasons as mentioned before.

So, we should use this

WHERE Startdate >= '20160501' and Startdate < '20160601'

rather than

WHERE MONTH(Startdate)=5 AND year(Startdate)=2016

You might want to see more details about it in the links below.
http://www.simple-talk.com/sql/performance/index-selection-and-the-query-optimizer
http://sqlmag.com/t-sql/inside-search-arguments

Forcing a parallel query execution plan

Sometimes you might need to convince the query optimizer to generate a parallel execution plan to make your query faster. There is an undocumented trace flag (8649) that will help you with that.

Take a look the example below.

SELECT top (1) FirstName
FROM [Person].[Person] AS P
INNER JOIN [Person].[PersonPhone] AS H
    ON P.BusinessEntityID = H.BusinessEntityID

Now, the same query with QUERYTRACEON 8649.

SELECT top (1) FirstName
FROM [Person].[Person] AS P
INNER JOIN [Person].[PersonPhone] AS H
 ON P.BusinessEntityID = H.BusinessEntityID
OPTION (RECOMPILE, MAXDOP 4, QUERYTRACEON 8649)

I cannot recommend you use it directly in production unless advised by Microsoft, but you might like to use it on a test system as an extreme last resort, perhaps to generate a plan guide or USE PLAN hint for use in production (after careful review).”

If you need more details about that, check this link.
http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx

Why ado.net queries run so slow and my SSMS queries are so fast?

Many times I face questions from development teams “Why ado.net queries run so slow and my SSMS queries are so fast?”.

The most common reason of this behavior is about the option ARITHABORT.
Ado.net defaults are different than SSMS settings:

Your application connects with ARITHABORT OFF, but when you run the query in SSMS, ARITHABORT is ON and thus you will not reuse the cache entry that the application uses, but SQL Server will compile the procedure anew, sniffing your current parameter values, and you may get a different plan than from the application. So there you have a likely answer to the initial question of this article. There are a few more possibilities that we will look into in the next chapter, but the by far most common reason for slow in the application, fast in SSMS in SQL 2005 and later is parameter sniffing and the different defaults for ARITHABORT.

arithabort

This and many more issues can be fixed if you read “Slow in the Application, Fast in SSMS? Understanding Performance Mysteries” by Erland Sommarskog.

You can check the ARITHABORT option, running the query below:

DECLARE @ARITHABORT VARCHAR(3) = 'OFF';
IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON';
SELECT @ARITHABORT AS ARITHABORT;

Good reading!

Alwayson Availability Group Replica is Not Synchronizing (Error: 976, Severity: 14, State: 1.)

sqlserver2016

You might lose communication between the primary and secondary replicas.

As known, there is a policy on SQL Server to check the data synchronization state of the database replica. The policy is in an unhealthy state when the data synchronization state is NOT SYNCHRONIZING or the state is not SYNCHRONIZED for the synchronous-commit database replica.

After investigating the sql server errorlog, I got the following msg:

The target database, ‘YourDatabase’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. (Microsoft SQL Server, Error: 976)

According to Technet, this issue can be caused by the following:

  • The availability replica might be disconnected.
  • The data movement might be suspended.
  • The database might not be accessible.
  • There might be a temporary delay issue due to network latency or the load on the primary or secondary replica.

Further information: https://technet.microsoft.com/en-us/library/hh245199.aspx

Resolution:
You can fix it, using the following T-SQL command to force the resume synchronization:

ALTER DATABASE YourDatabase SET HADR RESUME;

You can also follow the link: How to resume an AG database.

Check out these related tips:

How to monitor Timeout and Changes Roles from Availability Groups with Alerts
How to Monitor AlwaysON – Primary / Secondary Replicas

 

Default trace security auditing [SQL Server]

The Default trace enabled can provides us valuable information about the running server, but it isn’t well-documented.

What is the default trace? The default trace is enabled by default in SQL Server and is a minimum weight trace which consists by default of five trace files ( .trc) located in the SQL Server installation directory. The files are rolled over as time passes.

The script below will give you the most recently manipulated objects in your databases.

SELECT  TE. name ,
        v .subclass_name ,
        DB_NAME(t .DatabaseId) AS DBName ,
        T .NTDomainName ,
        t .NTUserName ,
        t .HostName ,
        t .ApplicationName ,
        t .LoginName ,
        t .Duration ,
        t .StartTime ,
        t .ObjectName ,
        CASE t. ObjectType
          WHEN 8259 THEN 'Check Constraint'
          WHEN 8260 THEN 'Default (constraint or standalone)'
          WHEN 8262 THEN 'Foreign-key Constraint'
          WHEN 8272 THEN 'Stored Procedure'
          WHEN 8274 THEN 'Rule'
          WHEN 8275 THEN 'System Table'
          WHEN 8276 THEN 'Trigger on Server'
          WHEN 8277 THEN '(User-defined) Table'
          WHEN 8278 THEN 'View'
          WHEN 8280 THEN 'Extended Stored Procedure'
          WHEN 16724 THEN 'CLR Trigger'
          WHEN 16964 THEN 'Database'
          WHEN 16975 THEN 'Object'
          WHEN 17222 THEN 'FullText Catalog'
          WHEN 17232 THEN 'CLR Stored Procedure'
          WHEN 17235 THEN 'Schema'
          WHEN 17475 THEN 'Credential'
          WHEN 17491 THEN 'DDL Event'
          WHEN 17741 THEN 'Management Event'
          WHEN 17747 THEN 'Security Event'
          WHEN 17749 THEN 'User Event'
          WHEN 17985 THEN 'CLR Aggregate Function'
          WHEN 17993 THEN 'Inline Table-valued SQL Function'
          WHEN 18000 THEN 'Partition Function'
          WHEN 18002 THEN 'Replication Filter Procedure'
          WHEN 18004 THEN 'Table-valued SQL Function'
          WHEN 18259 THEN 'Server Role'
          WHEN 18263 THEN 'Microsoft Windows Group'
          WHEN 19265 THEN 'Asymmetric Key'
          WHEN 19277 THEN 'Master Key'
          WHEN 19280 THEN 'Primary Key'
          WHEN 19283 THEN 'ObfusKey'
          WHEN 19521 THEN 'Asymmetric Key Login'
          WHEN 19523 THEN 'Certificate Login'
          WHEN 19538 THEN 'Role'
          WHEN 19539 THEN 'SQL Login'
          WHEN 19543 THEN 'Windows Login'
          WHEN 20034 THEN 'Remote Service Binding'
          WHEN 20036 THEN 'Event Notification on Database'
          WHEN 20037 THEN 'Event Notification'
          WHEN 20038 THEN 'Scalar SQL Function'
          WHEN 20047 THEN 'Event Notification on Object'
          WHEN 20051 THEN 'Synonym'
          WHEN 20549 THEN 'End Point'
          WHEN 20801 THEN 'Adhoc Queries which may be cached'
          WHEN 20816 THEN 'Prepared Queries which may be cached'
          WHEN 20819 THEN 'Service Broker Service Queue'
          WHEN 20821 THEN 'Unique Constraint'
          WHEN 21057 THEN 'Application Role'
          WHEN 21059 THEN 'Certificate'
          WHEN 21075 THEN 'Server'
          WHEN 21076 THEN 'Transact-SQL Trigger'
          WHEN 21313 THEN 'Assembly'
          WHEN 21318 THEN 'CLR Scalar Function'
          WHEN 21321 THEN 'Inline scalar SQL Function'
          WHEN 21328 THEN 'Partition Scheme'
          WHEN 21333 THEN 'User'
          WHEN 21571 THEN 'Service Broker Service Contract'
          WHEN 21572 THEN 'Trigger on Database'
          WHEN 21574 THEN 'CLR Table-valued Function'
          WHEN 21577
          THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
          WHEN 21581 THEN 'Service Broker Message Type'
          WHEN 21586 THEN 'Service Broker Route'
          WHEN 21587 THEN 'Statistics'
          WHEN 21825 THEN 'User'
          WHEN 21827 THEN 'User'
          WHEN 21831 THEN 'User'
          WHEN 21843 THEN 'User'
          WHEN 21847 THEN 'User'
          WHEN 22099 THEN 'Service Broker Service'
          WHEN 22601 THEN 'Index'
          WHEN 22604 THEN 'Certificate Login'
          WHEN 22611 THEN 'XMLSchema'
          WHEN 22868 THEN 'Type'
          ELSE 'Hmmm???'
        END AS ObjectType
FROM    [fn_trace_gettable]( CONVERT(VARCHAR (150), ( SELECT TOP 1
                                                            value
                                                    FROM    [fn_trace_getinfo](NULL)
                                                    WHERE   [property] = 2
                                                  )), DEFAULT ) T
        JOIN sys .trace_events TE ON T. EventClass = TE .trace_event_id
        JOIN sys .trace_subclass_values v ON v. trace_event_id = TE .trace_event_id
                                            AND v. subclass_value = t .EventSubClass
WHERE   --TE.name IN ( 'Object:Created', 'Object:Deleted', 'Object:Altered' )AND
                -- filter statistics created by SQL server                                        
        --t.ObjectType NOT IN ( 21587 )-- filter tempdb objects
         DatabaseID <> 2
              -- AND NTUserName <> 'username'
              -- get only events in the past 24 hours
        AND StartTime > DATEADD(HH, -24, GETDATE())
ORDER BY t.StartTime DESC ;

If you need to enable:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO

I hope this was useful.