SQL Server and antivirus software

From time to time we have performance degradation problems with applications and in general we can find quickly what is the issue. But we had few problems that took longer to find the main reason for the degradation and we come to discover that is about the antivirus software.

 

Last time we have found that one weekly process that used to run for 10 minutes took more than 90 minutes to complete and it was because the antivírus software was running a full scan in the same time.

 

This article is to remind you to verify the exclusions that are configured in your antivirus software for MS SQL server files, folders and processes and also to verify the scan schedules for your antivirus software. 

 

The more important is to exclude SQL Server datafiles (*.mdf, *.ndf), transaction log files (*.ldf) and SQL Server process (sqlservr.exe), Analysis Server process (msmdsrv.exe) and Reporting Services process (reportingservicesservice.exe). But you can find more complete information in this  Microsoft article about using antivirus with MS SQL Server, so I do not need to rewrite here the full article.

 

Keep your servers free of virus but also keep them with a good performance.

EDIT: You should also (ask for) check the files that are scanned in application server and file server. Can also degradate the performance and the first thing the clients will complain is the slow access to the database.

Cannot generate SSPI context

I lost the count for how many times I resolved or helped someone to resolve the "Cannot generate SSPI context" error.

Even there is a lot of forums that talk about this error, it seems to me that some people still have difficulty for understand it. So I will give my try and be as simple as I can with useful links for the people that want to find more about this subject.

When this error appears?

This error usually appears when there is no SPN (Server Principal Name) registered to server name in active directory for SQL Server service.

Why it appears?

This error only occurs when SSPI (Security Support Provider Interface) uses kerberos authentication (that uses SPN) and can not complete the necessary operations to successfully delegate the user security token to the destination computer that is running SQL Server.

What is the Solution?

This problem can be avoid if the SQL Server service account have the "Read servicePrincipalName" permission and the "Write servicePrincipalName" permission in the Active Directory. Because the policy in many companies does not allow that, this problem can be solved by asking the systems administrators for adding SPN with SETSPN tool. Here is an example of how to use the command:

a) SETSPN -A MSSQLSvc/<ServerName>:<port> <SQL_Service_Account>

b) SETSPN -A MSSQLSvc/<ServerName>:<InstanceName> <SQL_Service_Account>

There is any Advantage for using kerberos?

The advantage for using kerberos with SQL Server is to provide a highly secure method to authenticate users and also a better performance, since the authentication protocol is different from NTLM.

And there is any Disadvantage?

The disadvantage is if you need to configure the SQL Server instance to listen to a different port, then you need to recreate the SPN with the new port number. It will be worse if the Instance is configure to use dynamic IP addresses. Then you will need to recreate the SPN each time the instance is started.

NOTES: 

• Dedicate Admin Connection (DAC) do not uses SPN, only uses NTLM. 
• If SQL Server instance is configured to listen to multiple IP addresses, the server will only automatically register the SPN with the first port that it identifies.

Multiple rows to single column

Last year I had a challenge to find a solution for transform rows in a single column using only one query.
The initial query was this one:

SELECT object_name(o.object_id, c.name
FROM sys.objects o
INNER JOIN sys.columns c on (c.object_id = o.object_id)
WHERE o.type = 'U'


My solution was the following script that returns all columns names from all user tables in the database context. It returns one row by user table.

SELECT object_name(o.object_id),

      STUFF(

            (SELECT ','+ c.name

            FROM sys.columns c

            WHERE c.object_id = o.object_id 

                 for XML path(''),type).value('.','NVARCHAR(MAX)')

            ,1,1,'')

FROM sys.objects o

WHERE o.type = 'U'

FOR XML PATH('') instruction do the trick for returning all columns names into a single column (XML string). The parameter ('') is for eliminate the "<row>" and "</row"> tag.
*The TYPE parameter it is for handling eventual special characters as '<', '>', '&', '/', etc.

STUFF function, deletes a specified length of characters and inserts another set of characters at a specified starting point. In this case only deletes the comma in the first position.

edit*: I want to thanks Jeff Moden for his input about this article. He made some pertinent comments about the TYPE parameter for XML PATH that made me add it and called me attention for improve the solution with only one sub-select, what I also did.

<> operator

If you read my last article, I had optimized a query by transforming a sub-query in an inner join and reduced drastically the execution time of that query. This is the optimized query:
SELECT Column1, Column2, Column3, Column4, Column5

FROM dbo.wi  

INNER JOIN dbo.qi ON wi.r_object_id = qi.item_id 

INNER JOIN dbo.p ON wi.r_workflow_id = p.r_workflow_id 

INNER JOIN dbo.wk ON wi.r_workflow_id = wk.r_object_id 

CROSS JOIN dbo.dr 

    INNER JOIN dbo.dmi ON dmi.r_component_id = dr.r_object_id
WHERE (p.r_object_id = dmi.r_object_id) 

      AND (wi.r_runtime_state <> 2) 

      AND (wk.r_runtime_state = 1) 

      AND (qi.router_id <> '0000000000000000')

      AND (dr.i_is_deleted = 0) 

      AND (qi.task_state <> 'paused')

ORDER BY Column1

But it can be more optimized. This query handles more than 3 billions records and uses 3 '<>' operators. Substituting those operators for equal operators and then negate it did the job:
SELECT Column1, Column2, Column3, Column4, Column5

FROM dbo.wi  

INNER JOIN dbo.qi ON wi.r_object_id = qi.item_id 

INNER JOIN dbo.p ON wi.r_workflow_id = p.r_workflow_id 

INNER JOIN dbo.wk ON wi.r_workflow_id = wk.r_object_id 

CROSS JOIN dbo.dr 

    INNER JOIN dbo.dmi ON dmi.r_component_id = dr.r_object_id
WHERE (p.r_object_id = dmi.r_object_id) 

      AND NOT (wi.r_runtime_state = 2) 

      AND (wk.r_runtime_state = 1) 

      AND NOT (qi.router_id = '0000000000000000')

      AND (dr.i_is_deleted = 0) 

      AND NOT (qi.task_state = 'paused')

ORDER BY Column1

This new query ran in 5 seconds. Almost in half time the before one (8 seconds). So in two steps I could optimize a query that took 4 minutes and 25 seconds to execute in 5 seconds. 53 times faster and no indexes created!!

I made some more tests with this solution and looks like that you can see only results when you work with massive data. With few thousands records I did not see any difference.
Curious is that there was no changes in the query plan. Means that it is how the SQL Server engine treats the data when use <> or = operators.

SUBQUERY - be careful when using it

Few years ago I had a call from a client that was complaining about one slow query.
As always I started a SQL Profiler to catch long running queries. It wasn't hard  to find in the trace the following query that took 4 minutes and 25 seconds to run:

SELECT Column1, Column2, Column3, Column4, Column5

FROM dbo.wi  

INNER JOIN dbo.qi ON wi.r_object_id = qi.item_id 

INNER JOIN dbo.p ON wi.r_workflow_id = p.r_workflow_id 

INNER JOIN dbo.wk ON wi.r_workflow_id = wk.r_object_id 

CROSS JOIN dbo.dr 

WHERE (p.r_object_id IN (SELECT r_object_id 
                   FROM dbo.dmi 
                   WHERE (r_component_id = dr.r_object_id)))        

      AND (wi.r_runtime_state <> 2) 

      AND (wk.r_runtime_state = 1) 

      AND (qi.router_id <> '0000000000000000')

      AND (dr.i_is_deleted = 0) 

      AND (qi.task_state <> 'paused')

ORDER BY Column1


The cross join and the sub-query called my attention immediately. Cross join produces the Cartesian product of the tables involved in the join. In this example the cross join returns more than 3 billions rows and each of these rows will be correlated with the sub-select.
I changed that query and transformed the sub-query in inner join clause. The new query:

SELECT Column1, Column2, Column3, Column4, Column5

FROM dbo.wi  

INNER JOIN dbo.qi ON wi.r_object_id = qi.item_id 

INNER JOIN dbo.p ON wi.r_workflow_id = p.r_workflow_id 

INNER JOIN dbo.wk ON wi.r_workflow_id = wk.r_object_id 

CROSS JOIN dbo.dr 

    INNER JOIN dbo.dmi ON dmi.r_component_id = dr.r_object_id
WHERE (p.r_object_id = dmi.r_object_id) 

      AND (wi.r_runtime_state <> 2) 

      AND (wk.r_runtime_state = 1) 

      AND (qi.router_id <> '0000000000000000')

      AND (dr.i_is_deleted = 0) 

      AND (qi.task_state <> 'paused')

ORDER BY Column1

This new query took 8 seconds to run.

The difference is in the number of executions that SQL Server engine needs to make so be very careful when using sub-queries. Only use it if necessary. Whenever you can use inner join instead.

This query can be more optimized but I will let that for my next article.

SQL Server 2000 migration

The SQL Server 2000 SP4 extended support period is ending in 9th April. If your company still have SQL Server 2000 databases you should migrate them quickly.

Don't forget that you can't migrate SQL Server 2000 databases to SQL Server 2012 in one single step. You need to add an extra step that is migrate from SQL Server 2000 to SQL Server 2005 or 2008 or 2008R2.

If you want to upgrade the full instance, remember that SQL Server 2000 instance need to be with SP4 before you start. And if you are upgrading to SQL Server 2012, in the additional step, SQL Server 2005 should be at least with SP4 and SQL Server 2008 with SP2 and SQL Server 2008R2 with SP1 or later.

In-place migration will be more complex because you may also need to migrate the operating system for supporting earlier versions of SQL Server, so I always recommend side by side migrations.

I also recommend you to visit this MSDN article before you start the migration plan.

Have a nice migration :)

Hi folks. This blog is intended to help any SQL Server professional, developer or DBA.

​

I decided to create this blog after I helped a lot of people with questions in Experts Exchange. 

​

So I hope this blog will be a place where SQL Server professionals (or those who are just curious) can get more knowledge.

​

I will try to post articles often but if you have any particular question that you want to see answered drop me a message. I promise to answer to everyone as fast as I can.

​

Thank you for your visit and enjoy.