Feeds:
Posts
Comments

Recently, at one of our customer sites, we noticed this interesting anomaly.

In this database, there are several tables and each table has several statistics. Statistics could be created in several ways.

  • Index based statistics
  • Column Statistics
    • User created
    • Auto created Statistics (that are created by Sql Server)

Problem

When they perform nightly maintenance, some how the ‘auto created’ statistics were not getting updated. The ‘is_auto_update_stats_on’ & ‘is_auto_update_stats_async_on’ options are set to 1. But still some statistics are not getting updated.

Usually, there are many ways to update statstics:

  • Use maintenance plan to perform ‘Update Statics’ (most popular)
  • Use “UPDATE STATISTICS” command to update selective tables or selective statistics
  • sp_updatestats : Updates all statistics in the entire database
  • etc

In a  database environment, given the size and traffic and possible maintenance window duration, the option we choose varies. If you have sufficient maintenance window, we could use a SQL Job to update statistics on all tables in a database using a maintenance plan approach or ‘sp_updatestats’. But in some cases, we may not have enough time to run updates on all statistics. So, we end up selecting a list of statistics and running “UPDATE STATICS <stats_name>” on each of them.

Solution

Guess what happend, in this case. Yes, the other (auto created) statistics will not be updated. And this is what happened at our customer site. They could not afford to perform complete update stats each night and ended up choosing a few stats each night. This inadvertently resulted in ‘auto created’ statistics from not getting updated.

Index stats will be updated when you rebuild indexes.

The lesson is not to depend on the database settings alone (‘is_auto_update_stats_on’ & ‘is_auto_update_stats_async_on’). Have your own maintenance plans to update statistics all on all tables.

Options:

  1. Use UPDATE STATISTICS
  2. Use sp_updatestats
  3. Use maintenance plans

Option 1

Rather than choosing each statistic name in “UPDATE STATISTICS” command, just give the table name. It updates all statistics on that particular table. Below is the image from our lab server as I was running UPDATE STATISTICS <table name>.

In the below image, is a snapshot from the table while the statistics were being updated. Hence you see some are updated and some are still in the process of getting updated. Interesting point is that the ‘auto created’ statistics also get updated using this command with table name.


--

-- Update stats for the entire table

--

UPDATE STATISTICS dbo.CPE_RA_Archive WITH FULLSCAN

Result

Update Statistics

Update Statistics

Option 2

--
--  Update statistics for entire database
--
EXEC sp_updatestats 'resample'

Result

UpdateStatics with Procedure

UpdateStatics with Procedure

Option 3

Use maintenance plan to perform “Update Stats” on the entire database. This in turn calls UPDATE STATISTICS on each table resulting in updating both user & auto created stats.

Hope this helps,
_Sqltimes

Recently, during review of some T-SQL code, I ran into the usage of “not equal to (<>)” operator in one of the queries with LEFT OUTER JOIN. This is interesting, because the general perception of ‘not equal to’ is different from the way Sql Server processes it.

Let’s take an example:
Two tables, Table1 & Table2, with similar structure.

--
-- Create tables
--
CREATE TABLE dbo.Table1(
   ID         INT         NOT NULL IDENTITY(1,1)
 , PatientID  INT         NULL
 , SomeCode   VARCHAR(10) NULL
)
GO

CREATE TABLE dbo.Table2(
   ID           INT         NOT NULL IDENTITY(1,1)
 , PatientID    INT         NULL
 , SomeCode     VARCHAR(10) NULL
)
GO

--
-- Load dummy data
--
INSERT INTO dbo.Table1(PatientID, SomeCode)
VALUES (1, 'Code1')
     , (1, 'Code2')
     , (1, 'Code3')
     , (2, 'Code1')
     , (2, 'Code2')
     , (3, 'Code1')
GO&lt;/pre&gt;
&lt;pre&gt;
INSERT INTO dbo.Table2(PatientID, SomeCode)
VALUES (1, 'Code1')
     , (1, 'Code2')
     , (1, 'Code3')
     , (2, 'Code1')
     , (2, 'Code3')
     , (2, 'Code4')
     , (3, 'Code1')
GO

Here, in Table1, Patient No.1 has 3 codes i.e. Code1, 2, 3 & Patient 2 has 2 codes i.e Code1, 2; Patient3, etc

Requirement Definition

In plain English, if we say, we want to see all records from Table1, that do not have a matching record in Table1 (a.k.a. LEFT OUTER JOIN scenario).

You want to see something like this:

Dataset and manual comparision

Dataset and manual comparision

 

The data set in green is the matching and red is the non-matching. So you only want to see the ones in red box (but only from Table1 – left hand side).

Options

This general English language query could be converted into these two SQL queries


--

-- Option 1: Using &lt;&gt; operator

--

SELECT A.PatientID, A.SomeCode, B.PatientID, B.SomeCode
FROM dbo.Table1 AS A
LEFT OUTER JOIN dbo.Table2 AS B
    ON A.PatientID = B.PatientID
    AND A.SomeCode &lt;&gt; B.SomeCode
GO

This results in more data than what we expect. For  every record in Table1, it cartesian-join’s every record in Table2 (after PatientID condition) and return every combination of mis-match.

See the image below:

Not Equal Operator Output

Not Equal Operator Output

 


--

-- Option 2: Using &quot;WHERE SomeCode IS NULL&quot;

--

SELECT A.PatientID, A.SomeCode, B.PatientID, B.SomeCode
FROM dbo.Table1 AS A
LEFT OUTER JOIN dbo.Table2 AS B
    ON A.PatientID = B.PatientID
    AND A.SomeCode = B.SomeCode
WHERE B.SomeCode IS NULL

GO

This returns the correct dataset that we are expecting i.e. only the records from Table1 that do not have matching record in Table2.

Using WHERE CLAUSE

Using WHERE CLAUSE

 

Summary

In the first version, when we say ‘<>’ (A.SomeCode <> B.Somecode, though in plain English we seem to be asking the correct question, in SQL the meaning changes. This returns a lot more data than what we’d like to see.

It takes each record from Table1 and returns every record from Table2, that does not match it (after PatiendID condition is applied), like a cartesian join

In general terms, stay away from ‘<>’ operator, unless it is the exact logic you need. Also, it is inefficient for general use cases.

Hope this helps,
_Sqltimes

Quick one today:

Starting Sql Server 2012, looks like there has been a change to restarting options with Sql Server when connected from remote SSMS (a.k.a. when connecting to a Sql Server instance on a lab/prod server using SSMS on your laptop).

SqlAgent Restart is Disabled

SqlAgent Restart is Disabled

Looks like this is part of the new security features. In a typical scenario, you rarely (or almost never) would perform restarts on Sql Server instance or Sql Server Agent on a production instance from your local SSMS. During a production maintenance window, the usual practice is to, log into the server (i.e. RDP into the box) and then use Sql Server Configuration Manager to perform such operation. This sounds like a better/safer practice than performing similar operations from my local SSMS. Though it may seem inconvenient, it is better this way to prevent an inadvertent downtime.

As always, there seem to be some techniques to bypass this limitation. Though there has not been a successful outcome when these techniques were attempted, one of the popular suggestions is to open SSMS using ‘Administrator’ privileges.

Other techniques are listed in the blog here. Please not that I do not recommend attempting any of them.

 
Hope this helps,
_Sqltimes

More often these days, there have been many conversations around the topic of handling unicode data and UTF-* type data in Sql Server. Some details are below:

Basics

  • ASCII means American Standard Code for Information Interchange – based on English alphabet
    • As you can see above, ASCII is only for English
  • Unicode is a standard for representing characters of most of world’s languages (written)
    • Just like ASCII, but covers more languages.
  • UTF-8 means UCS Transformation Format – 8 bit (1 byte).
    • UTF-8 is the most used standard for web and has closest compatibility with ASCII
    • UCS means Universal Character Set defined by ISO
    • UTF-16 uses 2 bytes
    • UTF-32 uses 4

How this relates to Sql Server:

  • Sql Server uses COLLATION setting to determine the techniques to compare and sort character data.
  • In Sql Server we have COLLATION at instance level, database level and column level.
    • Instance collation could be configured during installation
    • Installation collation is inherited for database and table columns, when no other collation is explicitly specified.
  • .Net string is unicode encoded in UTF-16
  • Java string is unicode encoded in UTF-16
  • In previous post, we discussed ways to store unicode data.
    • When you store unicode data in non-unicode column, Sql Server finds the closest match. Example : ‘À’ to ‘A’
    • But when a close match is not available, it replaces it with ‘?’
  • Use windows collation where possible (not SQL collation)
    • Windows collations are updated when there are new characters (or alphabet), where as SQL collations are not going to up updated going forward.
    • SQL collations are for backwards compatibility and when one Sql Server needs to communicate directly with other Sql Servers in the environment.
      • Either through SSIS
      • Replication
      • etc
  • In a multi-language environment, use the collation with best overall support for the languages used.
  • Performance is not (and should not be) a factor in deciding collation.

Several posts have helped gather the information above. I’ll list some of them:

  1. BoL
  2. Article on UTF
  3. Dan Guzman
  4. MSDN Blog

Hope this helps,
_Sqltimes

Quick one today:

Sometimes, we need to rename some column names. Surprisingly, there is no intuitive way to rename a column name in Sql Server i.e. ALTER TABLE statement type method. We need to use a system stored procedure called sp_rename.

Example Below:

The sample code below, renames the column ‘PKID’ in ‘dbo.ProductAttribute’ table to ‘ProductAttributesID’.

--
-- Rename Column
--
EXEC sp_rename 'dbo.ProductAttributes.PKID', 'ProductAttributesID', 'COLUMN'
GO

 

Hope this helps,
_Sqltimes

Quick one today:

Sometimes, for some scripts, viewing execution statistics for IO and TIME help measure the improvement you make with code changes.

They could be enabled with the following T-SQL:

--
-- Enable IO and TIME execution statistics for a query
--
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

-- Run some query
SELECT * FROM sys.objects
GO

-- disable them
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

Result:

SET STATISTICS ON

SET STATISTICS ON

Hope this helps,
_Sqltimes

Quick one today:

More frequently that I would like, we keep getting these questions from customers. “What Sql Server Edition do we have in our production / Development environment?

There are many easy ways to check, I’ll list a couple here.

Using T-SQL

--
-- Sql Server Edition
--
SELECT @@VERSION                       AS [Complete_Version]
     , SERVERPROPERTY('Edition')       AS [Sql Server Edition]
     , SERVERPROPERTY('EngineEdition') AS [Sql Server Engine Edition]
GO
  • @@VERSION variable has complete details.
  • SERVERPROPERTY(‘Edition‘) also give you the edition information. Possible values are below:
    • ‘Enterprise Edition’
    • ‘Enterprise Edition: Core-based Licensing’
    • ‘Enterprise Evaluation Edition’
    • ‘Business Intelligence Edition’
    • ‘Developer Edition’
    • ‘Express Edition’
    • ‘Express Edition with Advanced Services’
    • ‘Standard Edition’
    • ‘Web Edition’
    • SQL Database
  • Similarly, SERVERPROPERTY(‘EngineEdition‘), gives the same information in INT format. Possible values are below:
    • 1 = Personal or Desktop Engine (Not available in SQL Server 2005 and later versions.)
    • 2 = Standard (This is returned for Standard, Web, and Business Intelligence.)
    • 3 = Enterprise (This is returned for Evaluation, Developer, and both Enterprise editions.)
    • 4 = Express (This is returned for Express, Express with Tools and Express with Advanced Services)
    • 5 = SQL Database
Sql Server Edition Result

Sql Server Edition Result

Using SSMS

Go to SSMS, right click on the Sql Server Instance and go to properties. See below:

Sql Server Edition

Sql Server Edition

Hope this helps,
_Sqltimes

Follow

Get every new post delivered to your Inbox.