T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests

The Good, the Bad, and the Bug-ly.

The Good, the Bad, and the Bug-ly.

This month’s T-SQL Tuesday is brought to us by Brent Ozar ( work | personal | tweets ) and has to do with SQL Server Bugs and Enhancement requests on Microsoft Connect.

Now the reason that this is the topic of this month’s T-SQL Tuesday is nicely spelled out by the host himself:

Now, more than ever, Microsoft has started to respond to Connect requests and get ’em fixed not just in upcoming versions of SQL Server, but even in cumulative updates for existing versions.

If you’ve kept an ear to the ground and an eye out on the social media, (seriously get on Twitter if you haven’t already; with #sqlhelp and #sqlfamily, you’ll never be alone, stuck, and/or bored again), you’d realise that this is true. Microsoft has engaged more than ever with it’s audiences, from hiring a PowerShell developer to actively engaging in various Trello Channels. So much so that a twitterbot was created to keep track of Connect items closed as fixed (Closed As Fixed) by MVP Chrissy LeMaire ( blog | tweets ).

Shameless Plug:

Now, I happen to have a Connect item and I’m going to link to it here. I can do this as this blog shares a commonality with Lesley Gore’s birthday party song. (It’s mine and I can cry if I want to)

The Good…

First of all, lets see what we’re running on here…

SELECT @@VERSION

 

 

version
I really should be doing these against SQL Server 2016… ūüė¶

First contender: Inserting to an indexed view can fail

What would happen if I told you that, with regards to a view, sometimes inserting into the table could fail? Well that’s what this Connect item from Dave_Ballantyne found, along with the reason.

CREATE TABLE myTable
(
    Id integer not null,
    InView char(1) not null,
    SomeData varchar(255) not null
)
GO

CREATE VIEW vwIxView
WITH SCHEMABINDING
AS
SELECT 
    ID, 
    Somedata, 
    LEFT(SomeData, CHARINDEX('x', SomeData) - 1) AS leftfromx
FROM 
    dbo.myTable
WHERE 
    InView ='Y'
GO

CREATE UNIQUE CLUSTERED INDEX pkvwIxView ON vwIxView(Id)
GO

 

 

Now that we have the groundwork laid out, it’s time for our insert attempt:

DECLARE @id integer,
@inview char(1),
@Somedata char(50)

SELECT @id = 1,
@inview = 'N',
@Somedata = 'a'

INSERT INTO myTable(Id, InView, SomeData)
SELECT @id, @inview, @Somedata

 

daveballantyneerror
Do you see a LEFT() or SUBSTRING() ANYWHERE there???

He’s even gone so far as to give an explanation as to why this happens!

This is due to the compute scalar being executed prior to the filter.

Taking a look at the estimated execution plan, we can see that he’s right!

daveballantyneerrorplan
Let’s do it first, then see if we should have!

Imagine trying to troubleshoot what was causing the inserts to fail? Horrible! I can imagine tearing my hair out at that one!

I have this done as “The Good” just for the fact that, not content to just report the bug, Dave_Ballantyne even went so far as to find the possible cause. Now it’s just up to Microsoft to get to the fixing…

…The Bad…

Next up, we have moody31415 with “Can’t create Materialized View that references same table twice

Groundwork:

CREATE TABLE test (A int)
GO
CREATE VIEW vtest WITH SCHEMABINDING
AS
SELECT t1.A, t2.A as A2
FROM dbo.test T1
JOIN dbo.test T2 ON T1.A=T2.A
GO

The problem occurs when we try to add a unique clustered index on this bad boy:

CREATE UNIQUE CLUSTERED INDEX UCL_Test
on dbo.vTest (A, A2)

 

maddog_selfjoinviewserror
Ever get those times where you just can’t stand yourself?

Now, I originally put this down as “The Bad” because I thought that the issue could be down to trying essentially index the same column twice but that’s not the case…

CREATE TABLE dbo.B (col1 tinyint, col2 tinyint)
GO
CREATE VIEW dbo.SecondOne
WITH SCHEMABINDING
AS
SELECT t1.col1, t2.col2
FROM dbo.B AS t1
JOIN dbo.B AS t2 ON t1.col1 = t2.col1
GO
CREATE UNIQUE CLUSTERED INDEX UCL_test
ON dbo.SecondOne (col1, col2)

 

maddog_selfjoinviewserror_02
…I mean After-A-Massive-Meal-Cant-Stand-Yourself

In the end, the reason that I have this in “The Bad” section is that I went to the documentation and read this part…

The SELECT statement in the view definition must not contain the following Transact-SQL elements: […] self-joins […]

Now it’s unknown whether this was there when this Connect item was created, but it’s there now (and I didn’t have enough time to re-plan this blog post)

…and The Bug-ly.

In my quest through the magical lands of Connect I stumbled across this little beauty of a bug by Anatoly V. Popov that I had to mention as “The Bug-ly”.

Altering indexed view silently removes all indexes

Yes, I know it’s the same as my one but dammit if I don’t think it’s a bug! That’s why it’s getting my “The Bug-ly” title.
This leaves me with a bit of a conundrum though…Do I close mine and comment on this one to get re-opened? Or do I try and continue to push on my one?

To be honest I don’t know which option to choose but, for anyone new to SQL Server, filing a Connect Item is an exciting but nerve-wracking time.
Exciting because you’ve worked with SQL Server enough to think you know how something should be done.
However, it’s nerve-wracking because you haven’t worked with SQL Server for long enough to know if you are just whining or have an actual item to be taken seriously.

Finding this Connect item was a small little shot of validation for me. Just a little something to say “Hey, I thought this too and I’m with you”.

Summary

It’s a great thing that Microsoft have started to respond to the Connect Items again and going through them you realise how different people are using SQL Server in ways that you didn’t even think to imagine.

So check out a few, leave your comment and up-vote/down-vote as you see fit.

Just…just don’t be this guy please

dt_ofdbas

 

 

Understanding “Scan Counts 0, Logical Reads N”

So yesterday, the 30th of November, was Arun Sirpal’s (b | t) birthday and as a birthday present, he challenged me to write a blog post.

Now I’ll admit that I didn’t have any plan to write a blog post before I got the challenge¬†as I’ve been steadily getting busier with work/study/other commitments, so apologies if it’s a bit long, rambling, and not thought through fully.

Anyway, happy birthday Arun, here’s your blog post:

Understanding “Scan Counts 0, Logical Reads N”

Have you ever run SET STATISTICS IO ON; ?

I’ll confess that I do it a lot, especially when I am performance tuning. Yet, like most things in SQL Server, I don’t fully understand it…yet!

Now don’t get me wrong, the little that I understand is extremely helpful, but recently I had a case where I didn’t understand the output of STATISTICS IO¬†, and asking my Senior DBA got me the look from him that I come to think of as ‘The You_Are_Either_Joking_Or_You_Are_Being_Stupid_Again Look’.

So to document my stupidity, here’s the question for that look.

How come the Logical Reads from STATISTICS IO are so high when it says Scan count is 0?

tl;dr – They are related but not exactly a 1:1 relationship. Plus a scan count of 0 does not mean that the object wasn’t used at all.

Logical Reads:

Let us just get this definition out of the way as it’s very short, sweet, and to the point. Logical Reads are the…

Number of pages read from the data cache.

Right, great, got’cha. Logical reads, 8kb pages, read from the data cache. If your STATISTICS IO¬†reports logical reads 112 then you’ve read 112 pages from the cache. Simples!

Scan Count:

This is the blighter that got me the look…well more like ‘my misunderstanding of what Scan Count means’ got me the look but it still holds my contempt at this moment in time.

My previous intuitions about this guy was…

“Scan count is the number of hits the table/index received”
(THIS IS NOT CORRECT! …and why it isn’t down as a full quote).

Let’s check out the definition again and see what it has to say for itself.
Scan count is the…

Number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output.

That’s a very specific definition isn’t it?¬†It’s not all of the definition though, there’s more! And it is this “more” that I want to focus on.

Testing The Defintions

First, things first, let us set up our query environment.

USE [tempdb];
GO

SET STATISTICS IO ON;
GO

Next paragraph…

Scan count is 0 if the index used is a unique index or clustered index on a primary key and you are seeking for only one value. For example WHERE Primary_Key_Column = <value>.

Well let’s see about that!


CREATE TABLE dbo.Unique_DefinedUnique
(
 col1 INT NOT NULL PRIMARY KEY
);
INSERT INTO dbo.Unique_DefinedUnique (col1)
SELECT x.n FROM (VALUES (1), (2), (3), (4), (5)) AS x(n);

CREATE UNIQUE NONCLUSTERED INDEX uci_Unique_DefinedUnique_col1
ON dbo.Unique_DefinedUnique ( col1 )
GO

SELECT col1 FROM dbo.Unique_DefinedUnique WHERE col1 = 1;

scancount0
okay…maybe it does…

Never mind, the next paragraph please!

Scant count is 1 when you are searching for one value using a non-unique clustered index which is defined on a non-primary key column. This is done to check for duplicate values for the key value that you are searching for. For example WHERE Clustered_Index_Key_Column = <value>.

I don’t believe you!


CREATE TABLE dbo.Unique_NotDefinedUnique
(
col2 INT NOT NULL
);
GO
INSERT INTO dbo.Unique_NotDefinedUnique (col2) VALUES (1), (2), (3), (4), (5);

CREATE NONCLUSTERED INDEX nci_Unique_NotDefinedUnique_col2
ON dbo.Unique_NotDefinedUnique ( col2 )
GO

SELECT col2 FROM dbo.Unique_NotDefinedUnique WHERE col2 = 1;

scancount1
Fine…you got me there too…

Final bit!

Scan count is N when N is the number of different seek/scan started towards the left or right side at the leaf level after locating a key value using the index key.

Hmmm, so if we have duplicate values, then this will happen?…
Nah, ridiculous!


CREATE TABLE dbo.NotUnique
(
col3 CHAR(1) NOT NULL
);
GO

INSERT INTO dbo.NotUnique (col3)
VALUES
('A'), ('A'), ('B'), ('B'), ('C'), ('C'), ('D'), ('D'), ('E'), ('E'),
('F'), ('F'), ('G'), ('G'), ('H'), ('H'), ('I'), ('I'), ('J'), ('J');
GO 2

CREATE NONCLUSTERED INDEX nci_NotUnique_col3
ON dbo.NotUnique ( col3 )
GO

--Let's try it with 2 and then 3!!!

SELECT col3 FROM dbo.NotUnique
WHERE col3 = 'A' OR col3 = 'B'

SELECT col3 FROM dbo.NotUnique
WHERE col3 = 'A' OR col3 = 'B' OR col3 = 'C'

scancountn
Alright, alright! I was an idiot!

Putting away my toys…

DROP TABLE dbo.Unique_DefinedUnique, dbo.Unique_NotDefinedUnique, dbo.NotUnique;

Round Up:

I was confused about the Scan count being 0 but logical reads not being 0. How can the scan count not actually scan/seek anything?

But it is scanning/seeking!

Read the Scan Count definition again…I’ll capitalize¬†the words that I glossed over

Number of seeks/scans STARTED AFTER REACHING THE LEAF LEVEL in any direction to retrieve all the values to construct the final dataset for the output.

Scan count of 0 occurs when there is a unique index or clustered index¬†on a primary key and you are seeking for only one value. The word of the day is…”Unique”.

So because the unique index looking for a unique value in a column guaranteed to be unique,¬†it’s not so much that the query¬†isn’t looking for a value, it’s more that ¬†once the query reaches the leaf level it already knows that it’s on the value it needs!
Since it doesn’t look any more¬†“after reaching the leaf level”, the scan count is allowed to be 0.

This explains why, if the value is unique but not guaranteed to be so (either the index, value, or column is not guaranteed unique) the query has to do 1 scan/seek to check that the next value isn’t what it wants.
Therefore, Scan Count will be 1…

And I’ll leave it as an exercise to figure out why Scan Count N is Scan Count N.
(Hint: it’s because Scan Count N)

Take Away:

I sometimes find the documentation dry and not terribly interesting but I don’t like the “You_Are_Either_Joking_Or_You_Are_Being_Stupid_Again” look more.
So read the documentation, study what you are doing, know why things do what they do…

It’ll save you some funny looks off of people. ūüôā

The Danger of Disabled Logins

When disabling sysadmin logins just ain’t enough

words: 691

Reading Time: ~3 minutes

Intro:

I’m becoming more and more of a fan of Powershell the more that I interact with it. And I’m a big fan of the work that those over at dbatools are doing (seriously, check it out and also check out their Slack channel).

So when reading an article by Steve Jones (b|t) that mentions using Powershell, especially dbatools, I took immediate attention!

However, while reading the blog, what jumped out at me was the fact that dbatools copies the logins and the passwords. I think that’s epic and saves so much hassle, especially with migrations and environment creation.

But when you script out a login in SSMS, you get the following “warning comment” (and this comes straight from Microsoft) :

/* For security reasons the login is created disabled and with a random password. */

I understand why you don’t want to script out a login’s password for security reasons but disabling it…does that even do anything?

Something that I only recently learned is that, for logins with high privileges, disabling them is not enough; they must be removed.

Overkill, I hear you say?

Example, I retort!

Example:

I will admit that for my example to work, there needs to be help from a member of the securityadmin server role login. So for this example we’re going to have…

  1. A disabled sysadmin login,
  2. A “compromised” securityadmin login,
  3. An “attacking” low-privilege login.

Window 1 (High Permission Account):


-- Create a high privilege login (HPL)
CREATE LOGIN [AllThePower]
    WITH PASSWORD = '1m5trong15wear!';

ALTER SERVER ROLE sysadmin
    ADD MEMBER AllThePower;

-- Disable it.
ALTER LOGIN AllThePower DISABLE;

-- Create a "compromised" login
CREATE LOGIN Enabler
    WITH PASSWORD = 'AlreadyHereButCompromised';

-- Make them part of security so can grant permissions
ALTER SERVER ROLE securityadmin
    ADD MEMBER Enabler;

-- Create a low privilege login (LPL)
CREATE LOGIN Copycat
    WITH PASSWORD = 'NotAsStrongButDoesntMatter';

 

 

So now we have all our actors created, we need to connect to the database with all 3 accounts.

Simple as “Connect” -> “Database Engine” -> Change to SQL Auth. and put in the details above for who you want.

Window 2 (CopyCat):

First things first, check who we are and who can we see?


-- Who are we?
SELECT
    SUSER_NAME() AS LoginName,
    USER_NAME() AS UserName;

-- Who can we see?
SELECT
    *
FROM sys.server_principals;

 

copycatsee
We can’t see “Enabler” or “AllThePower”

Okay, so we can’t see it but we know that it’s there.

Let’s just cut to the chase and start “God-mode”


-- Can we get all the power
ALTER SERVER ROLE sysadmin
ADD MEMBER CopyCat;

copycatinitialsysadminfail
It was worth a shot…

Can we impersonate it?

-- Can we impersonate AllThePower
EXECUTE AS LOGIN = 'AllThePower'
SELECT
    SUSER_NAME() AS LoginName,
    USER_NAME() AS UserName;

 

copycatinitialattemptfail
I’ve put the different possibilities on individual lines…We “do not have permission” btw

Time to go to our compromised account:

Window 3 (Enabler):

Now, who are we and what can we see?

enablersee
Sauron: I SEE YOU!

Notice that “Enabler” as part of securityadmin can see the disabled “AllThePower” login?

Great, we can see it, so let’s promote our CopyCat login!

enablerinitialattemptfail
Look but don’t touch

So even though we’re now a member of the securityadmin role, we still can’t promote our login!

I think you’d be safe in thinking that people would give up here, but we know from server_principals that “AllThePower” is there, even though it’s disabled!

So even though we don’t have the ability to promote our login, we do have something that we can do in our power.

GRANT IMPERSONATE.


-- Give CopyCat Grant permission
GRANT IMPERSONATE ON LOGIN::AllThePower TO CopyCat;

enablerinitialattemptsucceed
Every little helps

Window 2 (CopyCat):

Now can we impersonate our Disabled login?

copycatinitialattemptsuccess
Whoooo are you? Who-oo? Who-oo?

And can we get all the power?

copycatsecondattemptsuccess
I know an uh-oh moment when I see it…

Finally, we’ll revert our impersonation and see if we actually are sysadmin?


-- Go back
REVERT;
SELECT
SUSER_NAME() AS LoginName,
USER_NAME() AS UserName;

-- Are we superuser?
SELECT IS_SRVROLEMEMBER('sysadmin') AS AreWeSysAdmin;

CopyCatGodMode.PNG
I..HAVE..THE POWER!!!

And now I can do whatever I feel like, anything at all!

Summary:

I’m a fan of removing high-permission accounts that are not needed but I’ve never put into words why. Now I know why disabling is just not enough and, for me, removing them is the option.

sp_rename to change schema?

words: 519

Reading time: ~2.5 minutes

The Set Up:

Recently I was asked by a developer whether they could use sp_rename to change the schema of a table.

I said no but I realised that I don’t know for sure as I’ve¬†never tried it this way.

Granted I have never needed to when we have such a descriptive command like ALTER SCHEMA.

So I tested to see if sp_rename could change the schema of a table and thought I would share my results.

Here they are:


SP_RENAME:

Script 1:


SELECT [Schema Name] = SCHEMA_NAME([schema_id]),
 [Table Name] = [name]
 FROM sys.tables
 WHERE [name] = N'Alphanumeric';

original_table

Now taking a look at the documentation for “sp_rename”, turns out all we need is

  1. the current name,
  2. the new name we want to call it, and
  3. an optional object type (which I’ll include because I like typing).

So with that, it seems simple to run the following…

Script 2:


 EXEC sp_rename
 @objname = N'dbo.Alphanumeric',
 @newname = N'deleteable.Alphanumeric',
 @objtype = 'OBJECT';

sp_rename
That error message! I enjoy that error message ūüôā

So now all there is left to do is check if it worked, so we run our first script again and we get???:

original_table_changed
eh…what?

I repeat the above:¬†eh…what???

Where did my table go???

Please tell me I didn’t delete the table? It’s a test system and I took a backup before starting but it’s a whole lot of hassle to recreate the table.

However, on a whim, I changed my first query to use a LIKE:

Script 3:

sp_rename_found
ehh..WHAT??

So I haven’t changed the schema? I’ve renamed it to be dbo.deletable.Alphanumeric?

Is that even query-able?


SELECT * FROM dbo.deleteable.Alphanumeric; -- Fails!

SELECT * FROM [dbo].[deleteable.Alphanumeric]; -- Works!

Okay, let’s just change it back quickly and pretend it never happened:

Script 4:


EXEC sp_rename
@objname = N'deletable.Alphanumeric',
@newname = N'Alphanumeric',
@objtype = 'OBJECT';

error_message_02
You’re killing me!!

Okay, okay maybe it’s like the SELECT statement and I need to wrap it in square brackets?

Script 5:


EXEC sp_rename
@objname = N'[deletable.Alphanumeric]',
@newname = N'[Alphanumeric]',
@objtype = 'OBJECT';

error_message_03
I count that as a metaphorical middle finger to me…

Maybe we’re being too specific?

Script 6:

EXEC sp_rename '[deleteable.Alphanumeric]', 'Alphanumeric';
finally_works
Hopes are up…

A quick run of our first script to confirm?

works_again
…and¬†we’re back to normal!!!

Now, as to why that syntax works but the others don’t…I have no idea.

I will try and figure that out (fodder for another blog post ūüôā ) but I’m going to need a few more coffees before I go touch that again.


ALTER SCHEMA:

It’s a bit sad though… all that hassle for something that didn’t even work in the end?

Now, lets check out the documentation of “ALTER SCHEMA”.

  1. where we are changing it to, and
  2. what we’re changing.

Seems simple, but then so did sp_rename and that burnt me.

Script 7:

alter_schema
Give my “deleteable” the “dbo.Alphanumeric” object!

A quick check to see if it actually¬†worked as I’m not swayed anymore just by a lack of warnings:

actually_works
Yes!

Sum it up:

If I didn’t know the answer at the start, I definitely do now.

Can you change the schema of an object by using “sp_rename”?

Hell no.

Save yourself the hassle and just stick to ALTER SCHEMA. It’s easier, believe me.

 

Create View Permissions

CREATE VIEW Permission

I’m mainly writing this as documentation for myself as, in the end, this is the original purpose of this blog, to document SQL Server and new aspects of it that I learn and try.

Personal Template

I’ve always had a little block with regard to this as, for database permissions, I always followed a template in my head:

USE <database>
<Give/Take away> <what permission> <On What> <To Whom>

It’s The Little Things That Trip You

With CREATE¬†permissions this isn’t the case; there is a piece of the above template that isn’t needed, and it’s quite easy to see why when I¬†sat down and thought¬†about it.

Specifically, it’s this bit:

<On What>

I’m¬†granting CREATE permissions; since I¬†haven’t created anything, I¬†can’t grant the permission on anything.
So for CREATE permission, I have to modify my template a bit:

USE <database>
<Give/Take away> <what permission> <To Whom>

If I use this now as a template to a GRANT CREATE VIEW, it will work:

USE [localTesting];
GRANT CREATE VIEW TO [testUser];

And it works!

[SQL Server] Efficiency of Permission Granting.

Words: 349

Reading Time: ~1.5 minutes.

The lead up

Recently I was asked to create a temporary user with SELECT permissions on a database.

So far, not a problem. Taking advantage of the pre-defined roles in SQL Server, I just add this new user to the pre-defined role [db_datareader], which grants SELECT permissions to all tables and views in a database.

Why would I grant SELECT permissions this way and not manually choose the tables and views that this user could connect to?

Well,

  1. This is a test server so there is no sensitive information held that I’m worried about blocking access to,
  2. I didn’t get the exact requirements of the tables this user is to query so I don’t know which tables/views to grant access to and which to deny access to (I consider this a mistake on my part and something I have to act on next time),
  3. The test user is only required for 2 days, after which it is getting reviewed and deleted as quickly as I can, and
  4. Efficiency.

Efficiency, how?

Why grant SELECT on tables individually when I can grant on all tables in 1 fell swoop?

In the same vein, hypothetically speaking, if I was asked to grant SELECT permissions on 96 out of 100 tables, I would GRANT SELECT on all of them and then DENY SELECT on the 4 required as long as no column-level GRANTs have been given on those tables.

 Summary

A recent notion that came to me was that one of the roles of a DBA is to gather knowledge, but to a level that promotes efficiency.

Sure, we know¬†how to grant permissions, but we should also know the pitfalls, such as “deny beats grant unless the grant is on the column level” or “there are some combinations of permissions that allow more than intended“.

Knowing these caveats allows us to say when options can be automated or where rules need to be added to check for different statuses.

This will allow us to move on to the next aspect that needs a DBA’s eye and gentle guiding touch…or 2 cups of coffee and a full throttling !

[SQL Server] How Well Do You know Your sys Tables? Test yourself

I may have been too reliant on Intellisense…

Intro:

Reading Time: ~1 minutes

Recently I had to check on the nature of my check constraints and foreign keys; whether they were trusted or not.

select name, is_not_trusted from sys.check_constraints;
select name, is_not_trusted from sys.foreign_keys;

In case you are wondering, this has some Query Optimiser (QO) benefits so it’s a definitely a bonus to ensure that they are trusted.

However, something that should have taken 5 seconds and be a no-brainer, took me 30 seconds and required a bit of memory power on my part.

All because I had a problem with my SQL Server Intellisense, and said SQL Server Intellisense stopped working.
This forced me to drudge up these names out of my memory from whatever blog post or BOL entry I read them in.

This, in turn, got me wondering; how well do you really know your sys tables?

If your intellisense broke tomorrow, would you know your Dynamic Management Objects (DMO)?

Now before you dismiss this notion as simple, remember that it is not just sys tables that you have to know. This will test your knowledge on your application tables, your columns, stored procedures and functions.

Do you know all their names? Which table has the column “ID”, which has “<table_name>ID” and which has “<table_name>_ID”?

Test yourself:

The fix for my problem with SQL Server Intellisense not working is going to be reversed to allow you to test your knowledge.

In the dialog box,¬†Tools -> Options -> Text Editor -> General, there are two checkboxes under the “Statement completion” section:

  • Auto list members
  • Parameter information

If you uncheck these two checkboxes, your intellisense is gone!

TransactSQLGeneral

Try it out, even for 5-10 minutes.

Hopefully, with your Intellisense gone, your sense will remain.