Author: latheeshnk

1 Comment Posted on by latheeshnk Temp Tables & Variables

Table Variables in SQL Server

Table variables are another types of temporary objects to store transient data. Please refer Temporary Table objects for more details about Temporary Tables in SQL Server.

Differences between Table Variables and Temporary tables

When do you use Temporary Tables over Table Variables, vice versa

So, now we should be good enough to take a decision on when do we need to use table variable and Temporary tables. I would like to reiterate statistics as one of the most important factor. Table variable does not have statistics where as temporary table has statistics maintained. As SQL Server optimizer is based on cost- based optimization, statistics are very important factor for Cost based approach to identify the best plan for your query. If your transient data is more than 100, I would suggest to use Temporary tables(with right indexes) over table variables to make use of the statistics to help SQL Server to identify the best plan for us. 


DECLARE @TabVariableTesting TABLE (
 id INT PRIMARY KEY,
 ScrambledData Varchar(7000)
)

INSERT INTO @TabVariableTesting 
SELECT number, Replicate('SQLZEalot',100)
 FROM master..spt_values WHERE NAME IS NULL

SELECT * FROM @TabVariableTesting WHERE id > 75


CREATE TABLE #TempTableTesting (
 id INT PRIMARY KEY,
 somecolumn Varchar(7000)
)

INSERT INTO #TempTableTesting
SELECT number, Replicate('SQLZEalot',100)
 FROM master..spt_values WHERE NAME IS NULL

SELECT * FROM #TempTableTesting WHERE id > 75

DROP TABLE #TempTableTesting

In other words, if you have very less data, then I would prefer to use table variables over temporary tables. If you need to store data in a user defined function, table variables are the way for you currently. The choice is not hard rule one, but choose the best for your needs/requirements.

Hope you enjoyed this post, please share your feedback and thoughts.

Advertisements
Leave a comment Posted on by latheeshnk Linked Server

Linked Server in SQL Server – Best Practices

Linked Server is one of the easiest way of communicating between multiple servers/instances in SQL Server. By linking the servers, you would be able to receive/send data between the partners. This becomes handy as it may not really need *lots of* changes in your code to work, however, caveat is it may have some performance issues depending on your code and other factors. I am trying to put few best practices for you while you work with Linked Server in this post.

1. “Collation Compatible” Setting while creating the Linked Server

Collation Compatible is a setting to instruct SQL Server to do the evaluation of comparisons on character columns locally. By default, the value is false.

If the value is true, then SQL Server will consider that all partners of a Linked Servers are compatible regards to character set and sort order.

If the value is false, then SQL Server tries to process the data locally by pulling the data from remote server. This may lead to a performance issue if your remote table has more data. A word of caution: The choice of this setting should be considered at most care as this may even lead for data inconsistency if we set the value is true for performance gain and collation are different for Linked servers.

2. PUSH versus PULL method in Linked Server

PUSH or PULL are denoting how the SQL query is being operated through Linked Server. PUSH denotes the data to send (push) to the partner. PULL denotes the data to receive as required from the partner. In Linked Server world, PULL is much faster than PUSH method.

Eg: If you need to get some data from Remote, Local server can PULL data from remote server table instead of pushing the data from remote server. This has an impact on performance and I personally experienced in one of my earlier projects. Having said, the amount of data pass through the network has a major impact on the performance. As much as can limit the data volume over the network, we will get better performance. It depends on the query being passed, size of tables, cardinality, type of queries etc. This is a huge topic to be discussed later and forever.

3. Query dialects

SQL Server always do the best work for you, meaning, it will do most of the work at remote server and get you the required data to locally. However, sometimes, it may not happen as desired due to many factors. There are few operations that can hinder the work at remote if you use in your queries.

Any formatting/conversion of data
Queries that use uniqueidentifier datatype
Queries that use TOP operator
Complex queries
UNION queries with local objects

4. Required Permission

This is one of the important criteria while you set a Linked Server. SQL Server query execution is heavily depending on the statistics of objects, which will decide the best execution plan for your query. Linked Server is not a magic technology in SQL Server. It gets an additional layer OLEDB interface while communicating between servers. That enables Linked Server to connect heterogeneous systems. When your Linked server is between SQL Servers, the native client SQL Server retrieves statistics from the remote and process the query for the performance. Please find the SQL Profiler collected from the remote server for one of my Linked Query:

The highlighted lines are the ones getting executed in remote server to get the statistics. SQL Server needs special permission for the users connected to remote server to use the statistics of remote server. This could be either of the below:

1. sysadmin
2. db_owner
3. ddl_admin

If the user is not having any of the above roles, then the query cannot use the distributed statistics from the remote server and it will have an impact on performance. The issue with providing these roles is that compromising the security.

Prior to SQL Server 2012 (SP1), this is one of “must to verify”, however, there is a relief in SQL Server 2012 (SP1) , MS has put a fix to use statistics information even for read only user (not necessarily be associated with any of the above user roles). You have a choice of disabling this behavior by using trace flag 9485, that means, enabling trace flag 9485 reverts the new permission check to the original behavior. But, be aware, this may not solve all your Linked Server performance issues.

Please comment your experiences with Linked Server if you find anything interesting to be shared.

Leave a comment Posted on by latheeshnk DDL, Errors and Exceptions

How to create a lock on a table that prevents other requests in SQL Server

Problem Statement:

Today, I got a very peculiar request from one of my colleague that he wants to put a lock on a table so that no other requestscan be served. The request was looking weird to me initially as most of us want to avoid locking/blocking scenarios in a system, but the request was looking for creating one. But Why? After asking many questions, I got his intention that he is looking for replicating a locking scenario in his local environment that ends up with a Timeout.

Solution:

The solution provided is simple but put a Schema modification lock(Sch-M) on his table. Schema modification lock will be acquired when a DDL statement is executed on the table. As Schema modification is not compatible with any other locks, it will prevent access to the locked object data. An example of the Sch-M lock is lock during index rebuild (Please note when used with ONLINE option, index rebuild will acquire Sch-M lock shortly at the end of the process).


Session 1:
--Create Table
create Table TestLock(Col1 int)
Insert into TestLock values ('100')

--Raise a Schema modification lock
begin Tran T1
ALTER TABLE TESTLOCK ALTER COLUMN COL1 VARCHAR(5000)

-----------------------------------------------------------------------
Session 2:
Select * From TestLOCK(NOLOCK)--NOLOCK has no impact on SCH-M locked object.

Thoughts:

Finally, I understood he is trying to put some code to handle the locking situation, but not the solution for the timeout. As he mentioned the issue happens ONLY when maintenance window is running, I believe this is something related to any maintenance operations like (rebuild index,update stats etc.). He can , later, better of find the actual issue and fix it like move the functionality out of the maintenance window!!!

2 Comments Posted on by latheeshnk Temp Tables & Variables

Temporary objects in TEMPDB database

With this blog post, a continuation of earlier post on “TEMPDB – the most important system database in SQL Server“, we are going to see more about the temporary objects created in TEMPDB database. There are basically 3 different types of temporary objects that can create manually as below.

1. Local Temporary Tables

Local Temporary tables are created for operations that may require to access a specific set of data instead of querying every time the actual table. This will reduce the number of reads on the actual table and reduce the blocks/locks on the table.Everytime, you create a temp table (irrespective of the databases), the temp table is created in TEMPDB database.

Scope of the Local Temporary tables

The scope of the temp table is session/connection based, hence if you create two connections (two different management windows) and create the temp table with the same name, it will create two different objects in TEMPDB. 


open two Windows and execute the below
create Table #T1(Col1 int)
--Get the temp table created using the below
SELECT
    *
FROM
    sys.tables AS T
WHERE
    T.name LIKE '#t%' ;

However, as local temporary tables scope is session/connection based, these objects can still be used at nested levels.

But, wait, Will you be able to use temporary table in a function? – BIG NO. That is one of the difference between procedures and functions.

2. Global Temporary Tables

Global Temporary tables are again another temporary tables in SQL Server created in TEMPDB. Those are created by prefixing “##” the tablenames.

Scope of Global Temporary tables

Global Temporary tables are visible to all sessions once its created. On the flip side, there will be more contention if global temporary tables are heavily used. In addition, the scope of the global temporary table ends immediately when the session that created is terminated (or) any other session that uses the object is dropped manually. So, if you are planning to use global temporary tables, you should be very careful on its usage.

3. Temporary procedures

I believe, most of us are familiar with Temporary tables. But what about temporary stored procedures? Yes, just like temporary table, you can create temporary procedures as well in two ways – Local and Global. As we discussed earlier, the scope of the Local and Global procedures exactly same as temporary tables. These temporary tables are created in TEMPDB.

Let us look at the usages of temporary procedures. I could not find any good reason for using temporary procedures, may be, we have not used with this. But, it may be helpful while testing any procedure where we do not want to pollute the database with unnecessary objects. While deploying database, we may need to create temporary procedures for any purpose which we may not be using later etc.

If you have any use case, I would request to share your ideas.

2 Comments Posted on by latheeshnk Temp Tables & Variables

TEMPDB – the most important system database in SQL Server

Introduction

If you had anytime worked in SQL Server, you would be knowing about a system database – TEMPDB. TEMPDB is one of the most important system databases in SQL Server. As the name denotes TEMPDB database is a temporary database to SQL Server to do certain operations. As the topic is vast and its impossible to cover all aspects of TEMPDB in a single blog, planning to cover in multiple blogs to explain about TEMPDB.

Characteristics of TEMPDB

1. TEMPDB is automatically created/recreated every time you start/restart your SQL Service
That means, data anything reside in the tempdb is not persistent forever. If the service has been restarted, all the data will be lost.
2. Without TEMPDB, you will not be able to start your SQL Server
3. TEMPDB cannot be dropped or created manually
4. TEMPDB cannot be backup up
5. TEMPDB can have ONLY on SIMPLE recovery model
6. TEMPDB can have only one filegroup(PRIMARY Filegroup)
7. TEMPDB is a shared database
8. TEMPDB never stored redo information, That means, you can ONLY rollback with help of undo information
9. TEMPDB databse cannot be take to OFFLINE
10. DBCC CHECKALLOC and CHECKCATALOG cannot be issued

Usages of TEMPDB

TEMPDB has been used in SQL Server for various purposes.

1. Global/Temporary tables/Temporary procedures are created in TEMPDB (more details)
2. Table Variable (more details)
3. Cursors and internal objects created by DB engine to store intermediate results
SQL Server database engine will create internal work tables as required as per the instruction from the relational engine. This is to improve the performance of operations like sorting, spooling, hashing etc.(more details)
4. Online index operations needed TEMPDB
5. Snapshot and Read Committed Snapshot Isolation requires TEMPDB
6. Triggers
7. Version store

I am sure this not an exhaustive list, If I miss anything, please feel free to add as comments.

How to understand the usages of TEMPDB


select
reserved_MB=(unallocated_extent_page_count+
			version_store_reserved_page_count+
			user_object_reserved_page_count+
			internal_object_reserved_page_count+
			mixed_extent_page_count)*8/1024. ,
unallocated_extent_MB =unallocated_extent_page_count*8/1024., 
internal_object_reserved_page_count,
internal_object_reserved_MB =internal_object_reserved_page_count*8/1024.,
user_object_reserved_page_count,
user_object_reserved_MB =user_object_reserved_page_count*8/1024.
from sys.dm_db_file_space_usage

Hope, you had a good read and stay tuned for more about TEMPDB. I will explain each one in detail in coming posts.

Leave a comment Posted on by latheeshnk DBA Utilities

A quick look at SQL Server Logs

Logs are always important for anyone who works with any application. SQL Server has its own Logs that can be accessed to get the required information. Often, people call it as SQL Server Error logs. However, I personally would like to refer as SQL Server logs instead of Error Logs. The reason is that it does not only report the errors or exceptions, but also other important information.

What are the different types of logs in SQL Server?

Each one represents the logs for different purposes.
1. SQL Server – Logs SQL Server specific information
2. SQL Server Agent – Logs SQL Server Agent information
3. Database Mail – Logs Database Mail related information
4. Windows NT – Logs System related information

How do we see these log information?

There are multiple ways to get the log information as below.

using TSQL command using XP_READERRORLOG

XP_ReadErrorlog is an extended procedure available in SQL server. Please note that this is an undocumented procedure.

Parameter details:

There is also another method using sp_readerrorlog. However, if you take the definition of the procedure, it uses XP_readerrorlog internally.

Using Object Explorer

Management provides two options for us to view the logs as below.
1. Management -> SQL Server Logs

2. SQL Server Agent -> Error Logs

How to find the location of SQL Server log file?

The logs are not storing in SQL Server anywhere, but in the disk. When you access the logs in any of the above methods, it reads from a file from the predefined location. We can identify the location of the log files by any of the below methods.

using XP_Readerrorlog

using SQL Server configuration Manager

– SQL Server Configuration Manager -> Right click on SQL Service, properties -> Select “Startup Parameters”
– Parameter that starts with “-e” represent the SQL Server log path

using SERVERPROPERTY


How do we clear the Logs from SQL Server?

To clear the Logs, there are two options as below. To be clear, there is no concept of clearing the log *immediately*, but closing the most recent one and clearing the oldest one as per the configuration.

sp_cycle_errorlog
– Closes the current log for SQL Server logs and flush the oldest one.
– Customize the number of maximum archives that you want to keep. The range is between 6(Min) to 99(Max).
– You can configure the number of error logs by Right click on “SQL Server Logs” under Management -> Configure -> specify the number of files. By default, the value is 6.
sp_cycle_agent_errorlog
– Closes the current log for SQL Server logs and flush the oldest one.
– Make sure the current database is msdb.
– Make sure the SQL Server Agent is up and running.
– Can have maximum of 9 archives for agent logs.

Hope this post helps you to understand the Logs in SQL Server, Looking for your feedback if I missed anything.

Leave a comment Posted on by latheeshnk Always Encrypted

Task failed due to following error: Cannot save package to file. Error SQL71561: Error validating element in Always Encrypted Implementation

Problem Summary While I am trying to configure Always Encrypted for one of my databases, I received an error as below.
Sep 19 2018 02:31:58 [Informational] TaskUpdates: Message:Task: ‘Performing encryption operations’ — Status: ‘Failed’ — Details: ‘Task failed due to following error: Cannot save package to file. The model has build blocking errors: Error SQL71561: Error validating element [dbo].[ViewTableName]: View: [dbo].[ViewTableName] has an unresolved reference to object [DBName].[dbo].[TableName].
Environment Details Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 6.3 (Build 14393: ) (Hypervisor) Observations In fact, looking at the wizard error log report, I have seen hundreds of error messages similar to above for different elements. While  investigating, it is found that the error has raised due to the usage of three part reference in one of views. And it is confirmed by Microsoft that its a bug in the Always Encrypted tool that it performs a strict integrity verification for the entire database schema instead of the targeted verification, that means, only for the columns to be encrypted. Solution/Work around To solve the problem, you need to make sure the referenced objects are present in your environment. In my case, I copied all dependent databases in testing environment and followed the below steps.
  1. Use Generate Scripts(right click on the database, select Generate Scripts in the Tasks, in the wizard, we can select View) to back up the view.
  2. Drop all the selected views which use the three-part naming conventions.
  3.  Apply always encrypt feature to columns.
  4.  Execute the generated script to re-create the view  if needed
Additional Information When I tried to replicate the same issue in another environment with the below configuration, the above issue could not be replicated. Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 14393: ) (Hypervisor) Test Scripts 

--Create two databases
create Database Test1
create Database Test2

use test1
--Create the below table in Test1
create Table testtable (Col1 varchar(5000))
Insert into dbo.testtable Select ('Test data')

use test2
--Create the below table in Test1
create Table testtable (Col1 varchar(5000))
Insert into dbo.testtable Select ('Test data')

--Create the below view in database Test2
create View vw_test1 as Select Col1, Cast(GetDate() as datetime) as [DATECREATED],   
  Cast(GetDate() as datetime) as [DATELASTMODIFIED],   
  Cast(newid() as uniqueidentifier) as [_IMPORTED]   From Test1.dbo.Testtable

--Apply "Always Encrypted", you will see the mentioned behavior.
Try yourself with the above code and share your comments…