How to Enable or Disable Intellisense in SSMS

If for some ODD reason you want to disable Intellisense in SQL Server Management Studio, there is a very easy way to do so.

How to Enable or Disable Intellisense in SSMS

Open SSMS – Click on Tools, Options, Expand Text Editor, Transact – SQL, and then click on Intellisense (check screenshot below)

Disable or Enable Intellisense in SSMS

It’s as simple as that! If you are trying to enable / disable to refresh the Intellisense cache because you have the red squiggly underline in your T-SQL, check out my post here to show you an easier way to refresh your Intellisense cache in SSMS.

How to Create LOGON Trigger in SQL Server

Most recently I had to create a SQL Logon trigger that set a limit to sessions a user could open in SQL Server.

For example, I log into SSMS and cannot open more than 10 sessions. This logon trigger will enforce that I don’t exceed 10 concurrent sessions.

The script below is what I came up with. It’s fairly straight-forward. At the bottom of the query you will see 2 SELECT statements:

  1. sys.triggers – You will find database level triggers here.
  2. sys.server_triggers – You will find server level triggers here.

Feel free to modify the script below to suit your specific needs.

CREATE TRIGGER limit_sessions_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @ErrorText varchar(128)

SET @ErrorText = 'Cannot exceed 20 sessions. Close out older sessions to open newer ones.
    				If you need help, contact your Database Admin.';

IF ORIGINAL_LOGIN()= 'JohnDoe' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'JohnDoe') > 20
	BEGIN
		PRINT @ErrorText
		ROLLBACK;
	END
END;
GO

--Instance/Server level Triggers
SELECT *
FROM sys.server_triggers


--Database Level Triggers
SELECT *
FROM sys.triggers

--Delete Server level Trigger
DROP TRIGGER limit_sessions_trigger
ON ALL SERVER;
GO

How to use DBCC HELP

Memorizing DBCC commands (or any command for that matter) can be difficult if you don’t use it often. Thank goodness for the folks at Microsoft for creating the DBCC HELP command. Below is a quick way to use DBCC HELP to figure out any command (with syntax) that might have slipped your mind.

First, run DBCC HELP(‘?’) to display a list of available DBCC commands (see image below).

DBCC HELP RESULTS

Second, replace the ‘?’ with whatever DBCC command you want help on. For example, let’s use “checkdb.” Once you run DBCC HELP(checkdb) you get a printout of the CHECKDB syntax. (see image below)

DBCC HELP CHECKDB RESULTS

Use sys.dm_os_waiting_tasks to Find Blocking Transactions

If you want to do a quick and simple exercise to view blocking transactions on your SQL Server machine (code at bottom of blog).

I create a dummy table called “BlockingTable” and insert 10 rows of data. I then open a transaction and doing a simple update of the table. I leave the transaction open and open three new sessions.

USE master;
GO

IF DATABASEPROPERTYEX('BlockingDB', 'Version') > 0
BEGIN
    ALTER DATABASE BlockingDB SET SINGLE_USER
		WITH ROLLBACK IMMEDIATE;
	DROP DATABASE BlockingDB;
END

CREATE DATABASE BlockingDB;
GO

USE BlockingDB;
GO

CREATE TABLE BlockingTable(
col1 INT IDENTITY,
col2 INT
);
GO
--INSERT 10 rows
INSERT INTO BlockingTable
VALUES (2);
GO 10

--Open TRAN and update rows
BEGIN TRAN
UPDATE BlockingTable
SET col2 = 3
WHERE col1 = 3;
GO

--OPEN 3 NEW SESSIONS
--1. do a SELECT * from BlockingTable
--2. Do an UPDATE to BlockingTbale
--3. Query sys.dm_os_waiting_tasks

ROLLBACK

In session 1, I do a simple SELECT of the BlockingTable.

SELECT * FROM BlockingTable

In session 2, I do a simple update to the same BlockingTable.

UPDATE BlockingTable
SET col2 = 3
WHERE col1 = 3;
GO

Both of these sessions hang because they are waiting for the first transaction to finish and as a result you get the “Executing query…” at the bottom of SSMS (see image below).

Executing query

In session 3, I run a SELECT against the sys.dm_os_waiting_tasks DMV (Dynamic Management View) for all wait types that begin with “LCK_” and you will notice that the two sessions that are waiting for the very first UPDATE session are in the result set.

DMV output

In my scenario (your session IDs may vary), session 52 (the very first UPDATE) is blocking session 53 (my SELECT statement) and session 53 is blocking session 54 (the second UPDATE).

If you see the “wait_type” column, you will notice 2 lock wait types: LCK_M_S and LCK_M_U. The LCK_M_S occurs when a task is waiting to acquire a Shared lock and the LCK_M_U occurs when a task is waiting to acquire an Update lock. Visit the MSDN page for a complete list of wait types.

Pretty neat huh? This was a pretty straight-forward example but still wanted to share it because it’s exciting! I will definitely post more in the near future!

NEW Features in SQL Server 2016 – User Group Notes

I attended my second Northern Virginia (NOVA) SQL User Group session last night. The session was called, “SQL 2016 – Cool New Features” by Microsoft SQL PFE Harsh Deep (blog)

SQL Server 2016 New Features
SQL Server 2016 New Features

Like the first session I attended, this session was held at the Microsoft office in Reston, VA and lasted roughly three hours from 6:30 pm to 9 pm. Food and beverages were provided and two Amazon Fire TVs were raffled off at the end of the session. No, I didn’t win. :)

NEW Features in SQL Server 2016

There are so many new features in SQL Server 2016 that I cannot list them all. I will mention a few that sparked my interest. You can get a complete list of new features, enhancements by visiting the official Microsoft SQL Server 2016 page here.

AlwaysOn – Included in Enterprise as well as Standard Edition. Replaces Mirroring. Failover at database level, not Instance level. Load balancing for readable secondaries.

Always Encrypted – Protects data in motion and rest. The encryption key resides within the application. The only modification you have to make it a one liner in the application connection string.

Polybase – This is used to grab data on a “Big Data” platform like Hadoop. The cool thing is…you can use TSQL!

Stretch Database – Data that is “cold” can be stored on the cloud while data that is “hot” will remain active on-premise. My friend Tim Radney (twitter | blog) wrote a great blog on Stretch Database here. Feel free to check it out.

SQL Server 2016 New Features Harsh Deep
Microsoft SQL PFE Harsh Deep

You can download a pdf of the presentation here. You can also find MORE links to articles and videos here.

Next Up…

The next Northern Virginia (NOVA) SQL User Group session, and the first for 2016, will be held on January 25th. The topic is “Demystifying Master Data Services” taught by Benjamin Franklin. Feel free to sign up here.

But…

Before that…I will be attending my very first SQL Saturday in DC on December 4th and I am pretty excited!

Brent Ozar (blog | twitter), Steve Jones (SQLServerCentral.com), Senior Microsoft SQL PFE Ayman El-Ghazali (blog | twitter) will be among the many awesome speakers that day!

You can sign up here and check out the session schedule here. #SQLSaturdayDC

INDEX REORGANIZE vs REBUILD in SQL Server

This blog post is meant to help distinguish the differences between REORGANIZE and REBUILD when it comes to index fragmentation. If you are serious about learning more of the internals of how indexing works in SQL Server, watch Kimberly Tripp’s (blog | twitter) video called, “Index Internals” here (open link and scroll down to “Index Internals”).

INDEX REORGANIZE vs REBUILD in SQL Server

REORGANIZE – This is the lightweight version. It reorders the leaf pages of the index in a logical order. It is done ONLINE and doesn’t ROLLBACK if interrupted. If for some reason the REORGANIZE operation is interrupted, it can stop at the last operation and continue where it left off. The fill-factor is reset to the previously set fill factor. REORGANIZE is single threaded only and only requires 8 KB of free space to run.

ALTER INDEX [PK_Index_Name] on [MyTable] REORGANIZE;
GO

REBUILD – Creates new indexes and you can define the fill factor. Can be done ONLINE (with SQL Server Enterprise Edition) or OFFLINE. REBUILD is a fully transactions operation. If the operation is interrupted it will have to ROLLBACK the transaction and that can take a while depending how large your indexes are, etc.

ALTER INDEX [PK_Index_Name] ON [MyTable] REBUILD;
GO

For all the specific ALTER commands that you can use with REORGANIZE / REBUILD, visit the MSDN article here.

How to Find SQL Server Edition Information

Most recently I had to figure out the edition (Enterprise, Standard, etc.) of SQL Server running on a specific production database server. The client wanted to know to what extent was the database server STIG’d.

I initially ran SELECT @@VERSION but that doesn’t bring back the edition information. So I ran:

SELECT SERVERPROPERTY(‘Edition’)

…and that returned the edition information. I also included the @@VERSION information as that brings back what Service Pack level is applied (see snapshot below).

Find Edition of SQL Server

After seeing this database server was running on SQL Server Standard Edition, I let the client know that Common Criteria Compliance and TDE (Transparent Database Encryption) cannot be enabled as those are Enterprise features.

SQL Server DDL vs DML

For the longest time I’ve had a hard time remembering the difference between SQL Server DDL vs DML statements. I had a hard time remembering what statement fell under what category. Was INSERT, UPDATE, DELETE a DML command or DDL? What about CREATE / ALTER?  It was all confusing to me.

I finally figured out a way to remember the difference. Before I tell you my “secret”, here’s a quick explanation of each.

DDL

DDL, or Data Definition Language, consists of the following commands:

CREATE, ALTER and DROP

Below is an image taken straight from MSDN that includes all the DDL statements.

DDL vs DML SQL Server DDL

DML

DML, or Data Manipulation Language, consists of the following commands:

INSERT, UPDATE, DELETE, SELECT…

Below is an image taken straight from MSDN.

DDL vs DML SQL Server

How did I memorize the difference? Well, for DML the ‘M’ is for ‘manipulation’, so I automatically associate “INSERTING” or “UPDATING” data as “manipulating” data. Once I understood that, DDL became easy to remember and associated it with CREATE, ALTER, DROP. By memorizing one of them you automatically know the second. :)

What’s Msg 208, Level 16, State 1, Line 1

It’s usually something simple that’s overlooked that ends up causing the biggest troubleshooting headache. Let me explain. I was creating a test database called “testDB” with a test table called “testTable” (yes I know, I put a lot of thought in the naming of these objects) and when I tried to insert data into this new testTable I got the following error:

Msg 208, Level 16, State 1, Line 1 Invalid object name ‘testTable’.

Msg 208, Level 16, State 1, Line 1What the hec? How can it be an invalid object? I just created it!

The answer is extremely simple and kind of embarrassing to admit.

When I created the testDB I was in the Master database. Then I forgot to “use testDB;” and proceeded to create the testTable. After creating the testTable, I did a “USE testDB;” and ran the INSERT INTO command. That’s when it failed.

Now if you’re confused as I was, this is why it failed:

After creating testDB, and before creating testTable, I was still in the Master database. SO what actually happened was I created the testTable inside of the Master database (look at snapshot below). Doh!

Msg 208, Level 16, State 1, Line 1 master

I dropped the testTable inside the Master database, switched over to testDB, created the testTable, ran the insert command and everything worked fine.

Don’t overlook the small things. Pay attention to what database you’re in. :)

My First SQL Server User Group Session

I attended my very first SQL Server User Group meeting last night and it was awesome! I want to start by giving my friend Tim Radney a BIG thank you for telling me about the importance of attending these sessions!

THANKS brother!

I joined the Northern Virginia (NOVA) SQL User Group a few weeks ago and attended my very first session last night. The session was called, “Hands-On Lab- Migrating a SQL Server Database to a Microsoft Azure SQL Database” and it was an online lab.

It was held at the Microsoft office in Reston, VA and lasted roughly three hours from 6:30 pm to 9 pm.

If you want to do the lab yourself, feel free to download the lab book here and start the TechNet Virtual Lab here

My Thoughts

I absolutely loved it! There was pizza, soda, a big bucket full of Halloween candy, and a raffle at the end.

The group organizer Tim McAliley (who works for Microsoft) and Brian Moran (Founder of Linchpin People) were friendly, open to discussions/questions, and just plain down to earth.

It was a great experience and I will definitely attend more meetings! In fact, the next time NOVASQL Group is meeting is on Nov 2nd 2015 for a session called, “SQL Server 2016 Cool New Features” with Harsh Deep Singh Narula (a Microsoft PFE). If you’re in the area, come and join us! Sign up here.

My one regret was not taking any pictures! I’ll definitely take some next time! :)