Category: SQL

DBA Utilities, SCRIPT GALLERY, SQL

SQL Server Inventory Queries – SQL Server Instances
Here is a T-SQL script used for inventory purpose on your SQL Server Instances.

Tested in: SQL Server 2016/SQL Server 2017

Script:

drop table if exists #t;
 
create table #t(
  ServerName varchar(128) default @@servername,
  ServerEdition sql_variant default serverproperty('edition'),
  ServerVersion sql_variant default @@version,
  ProductVersion sql_variant default serverproperty('productversion'),
  ProductLevel sql_variant default serverproperty('ProductLevel'),
  ServerCollation sql_variant default serverproperty('collation'),
  MachineName sql_variant default serverproperty('ComputerNamePhysicalNetBIOS'),
  DefaultDataPath sql_variant default serverproperty('InstanceDefaultDataPath'),
  DefaultLogPath sql_variant default serverproperty('InstanceDefaultLogPath'),
  IsClustered sql_variant default serverproperty('IsClustered'),
  IsFullTextInstalled sql_variant default serverproperty('IsFullTextInstalled'),
  IsIntegratedSecurityOnly sql_variant default CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
							WHEN 1 THEN 'Windows Authentication'   
							WHEN 0 THEN 'Windows and SQL Server Authentication'   
							END,
  cpu_count int,
  hyperthread_ratio int,
  Physical_Memory_GB Bigint,
  max_workers_count Bigint,
  sqlserver_start_time datetime,
  cores_per_socket bigint,
  numa_node_count bigint,
  --Configuration Details
  [Ad Hoc Distributed Queries] sql_variant,[xp_cmdshell] sql_variant,
  [optimize for ad hoc workloads] sql_variant,[backup compression default] sql_variant,
  [blofcked process threshold (s)] sql_variant,[default trace enabled] sql_variant,
  [clr enabled] sql_variant,[max server memory (MB)]sql_variant,
  [min server memory (MB)] sql_variant,[max degree of parallelism] sql_variant,
  [cost threshold for parallelism] sql_variant,[max text repl size (B)] sql_variant,
  [show advanced options] sql_variant
);

insert into #t(cpu_count,hyperthread_ratio,Physical_Memory_GB,max_workers_count,sqlserver_start_time,cores_per_socket,numa_node_count,
[Ad Hoc Distributed Queries],[xp_cmdshell],[optimize for ad hoc workloads],[backup compression default]
    ,[blofcked process threshold (s)],[default trace enabled],[clr enabled],[max server memory (MB)],[min server memory (MB)]
    ,[max degree of parallelism],[cost threshold for parallelism],[max text repl size (B)],[show advanced options])
 
select cpu_count,hyperthread_ratio,((Physical_Memory_kb/1024.0)/1024.0),max_workers_count,
sqlserver_start_time,cores_per_socket,numa_node_count,
B.*
from sys.dm_os_sys_info A
outer apply ( Select Max([Ad Hoc Distributed Queries]) [Ad Hoc Distributed Queries],
        Max([xp_cmdshell]) [xp_cmdshell] ,
        Max([optimize for ad hoc workloads]) [optimize for ad hoc workloads],
        Max([backup compression default]) [backup compression default],
        Max([blofcked process threshold (s)]) [blocked process threshold (s)],
        Max([default trace enabled]) [default trace enabled],
        Max([clr enabled]) [clr enabled],
        Max([max server memory (MB)]) [max server memory (MB)],
        Max([min server memory (MB)]) [min server memory (MB)],
        Max([max degree of parallelism]) [max degree of parallelism],
        Max([cost threshold for parallelism])[cost threshold for parallelism],
        Max([max text repl size (B)]) [max text repl size (B)],
        Max([show advanced options]) [show advanced options]
 From sys.configurations 
 PIVOT (MAX(Value_in_use) for Name in([Ad Hoc Distributed Queries],[xp_cmdshell],[optimize for ad hoc workloads],[backup compression default]
    ,[blofcked process threshold (s)],[default trace enabled],[clr enabled],[max server memory (MB)],[min server memory (MB)]
    ,[max degree of parallelism],[cost threshold for parallelism],[max text repl size (B)],[show advanced options])) as PVT) B
 
Select * From #t

I would really love to hear from you what else you would like to see to this list?

See Also:

https://sqlzealots.com/2020/04/26/sql-server-inventory-queries-sql-server-databases/

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL, SSMS

How to enable Side by Side Query window in SQL Server Management Studio


Side by Side Query window is a very useful feature in SSMS. When we are comparing queries or its data, then this feature becomes very handy.

Let us quickly see how do we enable this feature with very simple steps as below:

Select Window Menu in SSMS – > Select “New Horizontal Tab Group” OR “New Vertical Tab Group” as you wish to see the query windows either horizontally or vertically!

You can always revert the change as below:
Select Window Menu in SSMS – > Select “Move to Previous Tab Group”

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL, Table Partitioning

Stairway to SQL Server Table Partitioning – Whats is Table Partitioning in SQL Server
There are two types of partitioning in SQL Server – Vertical & Horizontal. Today, we are quickly going to see these two types in SQL Server and its different use cases. One of the easiest way to explain Vertical partitioning is a partition at Column level otherwise Horizontal Partitioning is at Row level.

Vertical Partitioning

Vertical Partitioning is a process of splitting a really big table into multiple smaller tables for various reasons. Performance improvement is the major benefit of vertical partitioning. If your table is having large number of columns and lots of Large Object columns or very lengthy textual information, then partitioning the table into multiple smaller tables would really help to boost the performance especially if not all of your columns are frequently accessed. A simple pictorial representation as below: Lets quickly see how vertical partitioning helps in performance. (Note that this is a very simple example for readers to understand the impact of query execution). Few guys will have different arguments, please post those questions in comment, we will discuss in upcoming blog post. But for now, I strictly wanted to limit the scope of this post ONLY to the context of vertical partitioning.

Script:

Drop table if exists Employee_Tab1
Create Table Employee_Tab1
(EmployeeID int,FirstName varchar(10),
MiddleName varchar(10)
,LastName varchar(10)
,DOB Date
)DECLARE @i int
SET @i = 1
BEGIN TRAN
WHILE @i<>100000
BEGIN
INSERT INTO Employee_Tab1 
VALUES(@i,'a' ,'b','c', getdate())
SET @i=@i+1
END
COMMIT TRAN
GO

Set statistics io on
Select EmployeeID,DOB From Employee_Tab1
where dob = cast(getdate() as date)
Set statistics io off
 
Drop table if exists Employee
Create Table Employee
(EmployeeID int,FirstName varchar(10),
MiddleName varchar(10),
LastName varchar(10),
DOB Date,Address1 nvarchar(max),
Address2 nvarchar(max),
Address3 nvarchar(max))
DECLARE @i int
SET @i = 1
BEGIN TRAN
WHILE @i<>100000
BEGIN
INSERT INTO Employee 
VALUES(@i,'a' ,'b','c',
getdate(),Replicate('a',8000),
Replicate('a',8000),Replicate('a',8000))
SET @i=@i+1
END
COMMIT TRAN
GO
Set statistics io on
Select EmployeeID,DOB From Employee
where dob = cast(getdate() as date)
Set statistics io off

So if you look at the code, we tried to create two tables Employee and Emploee_Tab1 and we inserted almost 100000 records to these tables. You can observe the difference between these two tables – Employee table contains 3 large text columns for address where as the Employee_Tab1 does not have those columns.When we query these tables (note that we are not referring the address columns), we can observe a huge number of logical reads happens with Employee table where as the table employee_tab1 which does not have address columns has to do only less logical reads(532 compared to 2000). This indicates that SQL has to get more data from disk to cache for second query (using employee table) than first query (using vertically partitioned table Employee_Tab1). You may observe the behavior in the below side by side query window screenshot clearly.

Points to ponder:

Use Vertical partitioning

1. If your table is having more columns

2. If your table has lots of BLOB or wide text columns those are not frequently used

3. If your table is a highly transaction table

4. with extreme care if you are restructuring your table since it may have big impact like many objects may need to change.

Horizontal Partitioning

As mentioned earlier, horizontal partitioning is a partition process that carries out at row level. That means, if you a table with (m/b)illions of data, you can partition the table horizontally. This would enable better manageability/maintainability etc. Horizontal partitioning may also have performance benefits, however, we should know how to implement this technique efficiently.

Let us quickly see a pictorial representation of the Horizontal partitioning as below.

Horizontal Partitioning requires understanding of few important terminologies.

1. Partition Key Column

 Partition Key column determines on what basis the table has to be partitioned. In our example, the partition key column is AuditDate. It is important to be noted that the choice of partition key column is very important for performance aspects. We will cover this in details in next blog posts.

2. Partition Function

 Just partition key column does not partition the table. We need a partition logic to be implemented on key column to partition the table. This logic can be implemented through a function called partition function. We can implement the function using RANGE Right and RANGE Left. Simply illustration of Range as below.
    1. Range left means that the actual boundary value belongs to its left partition, it is the last value in the left partition.

    2. Range right means that the actual boundary value belongs to its right partition, it is the first value in the right partition.

Usually, for an year column partition, the last day of the year (31st Dec year) is the range left and first day of the year (1st Jan year) is the range right. If range has not been specified properly, then there will be an overlapping of two years data in the same partition that can cause lots of confusion and difficulties, in fact it may even defeat the purpose of partition altogether.

3. Partition Scheme

The partition scheme maps the logical partitions to physical filegroups. It is possible to map each partition to separate filegroup or all partitions to one filegroup. There are many benefits with a proper mapping of partition and filegroup that we can cover in next blog posts.

Points to ponder:

Use Horizontal partitioning

1. If you know your table and data in detail (I am not joking – 🙂 )

2. If your table has lots of data and you have a definite partition requirement

3. If your table needs an archival of data or efficient movement of data

We will cover more on this topic in coming days, until then stay tuned!

Refer Also:

Stairway to SQL Server Table Partitioning – How do we partition an existing table

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SCRIPT GALLERY, SQL

Understanding PIVOT operator in SQL Server with Examples
Today, we will quickly see PIVOT operator in SQL Server and why do we need this and its various forms of usages. To understand better, let us think of a transformation of data as below and see how do we approach further.

First off, let us create a CustomerPurchaseTable as below. Please note if you are not heard of DROP IF EXISTS yet, please refer here. 

Drop Table if exists  CustomerPurchaseTable

create Table CustomerPurchaseTable( CustomerID int, FruitName varchar(100), Quantity int)
Insert into CustomerPurchaseTable Values
(100,'Apple',10),(100,'Grapes', 5),(101,'Orange',10),(102,'Pineapple',5),(102,'Grapes',10)

Select * From CustomerPurchaseTable

I remember when I started my career I was using SQL Server 7.0 and SQL 2000. It was not so easy task to achieve transformations as of today, particularly, the pivoting had achieved using Select ..CASE statements as below. 

Select CustomerID, Sum(Case When FruitName = 'Apple' Then Quantity Else NULL End )'Apple',
				   Sum(Case when FruitName = 'Grapes' Then Quantity Else NULL End ) 'Grapes',
				   Sum(Case when FruitName = 'Orange' Then Quantity Else NULL End ) 'Orange'
From CustomerPurchaseTable
Group by CustomerID

Post SQL Server 2005 version release, there was a new operator introduced “PIVOT” which does the work with ease. I would say, its a fancy form of old way.But, at the same time, I would agree that its a saver at times, we would see that later part of this blog post.

Here is the syntax of PIVOT:

Syntax:
SELECT first_column AS ,
[pivot_value1], [pivot_value2], ... [pivot_value_n]
FROM 
() AS 
PIVOT 
(
 aggregate_function()
 FOR 
 IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
) AS ;

Select * From CustomerPurchaseTable
PIVOT (Sum(Quantity) For FruitName in ([Apple],[Grapes],[Orange]))as PivotTable

Now, let us look at a case how it would work for a non-fixed type of fruits in the above example? Then, this should be a dynamic transformation that might required. With old approach(prior to SQL Server 2005), this is a complicated one and hard to achieve with Dynamic Cross-Tabs/Pivot Tables. With the introduction of PIVOT that becomes handy. That does not mean that we do not need dynamic query, but it extremely handy to build dynamic list of columns and assign the value to the dynamic Query to produce the transformation. The generation of column list with quotename is much easier than generating the entire sql construct of CASE…WHEN as in the old way. 

DECLARE 
    @columns NVARCHAR(MAX) = '', @sql     NVARCHAR(MAX) = '';

SELECT @columns += QUOTENAME(FruitName) + ','
FROM (Select Distinct FruitName From CustomerPurchaseTable) A
ORDER BY FruitName ASc

SET @columns = LEFT(@columns, LEN(@columns) - 1);
PRINT @columns;

Set @SQL = 'Select * From CustomerPurchaseTable
PIVOT (Sum(Quantity) For FruitName in (' + @columns + '))as PivotTable'

Exec sp_executeSQL @SQL

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Errors and Exceptions, SQL

Error Message in SQL Server: Conflicting locking hints are specified for table “tablename”. This may be caused by a conflicting hint specified for a view.
Recently, One of my colleague has reported an issue as below from production environment.
Msg 4138, Level 16, State 1, Line 19 Conflicting locking hints are specified for table “tablename”. This may be caused by a conflicting hint specified for a view.
Error message is very clear and we can easily understand there are some incompatible hints applied in the same query caused a conflict. Let me quickly reproduce the issue for all of us to understand better with a simple example. 
-- Sample tables and data population
Drop Table if exists T1,T2
GO
create Table T1(Col1 int )
create Table T2(Col1 int )
GO
Insert into T1 Values(1)
Insert into T2 Values(1)
GO
Select * From T1
Select * From T2
The above code will create two tables – T1 and T2. I do not want to get complicated with more columns and its a simple and easy replication with a single column Col1. Now, Let us create a view using both of the tables. Please note, while creating the view we have given WITH (NOLOCK) on one of table called T2 as below. 
--Sample view 
Drop view if exists vw_test
GO
Create OR ALTER View vw_test 
as 
Select * From T1 
     where exists(Select 1 From T2 with (nolock) where t1.Col1 = t2.col1)
If you notice the VIEW, as mentioned above, NOLOCK has given to avoid any locking scenario deliberately. We are not going to cover whether NOLOCK is good or bad in this blog post, but this is a common bad practice that almost all people do, even experience people would do it at times. A view is a generalized form that anyone can use it as they like later. So, in this case, let us see some of the usages of the view to explain the behavior. 
-- SELECT with intended lock
Select * From vw_test A with (UPDLOCK,HOLDLOCK)
This is an example of intended lock on view. While executing the SELECT statement, we will end up with error message “Conflicting locking hints are specified for table “T2″. This may be caused by a conflicting hint specified for a view.”

Another example: 
--Rowlock will conflicts with NOLOCK
Update A Set A.Col1  = A.Col1 + 2 
From vw_test A with (ROWLOCK)
This is another example of ROW lock on view for an UPDATE query. While executing the UPDATE statement, we will end up with error message “Conflicting locking hints are specified for table “T2″. This may be caused by a conflicting hint specified for a view.”

So, precisely, there are many combinations that NOLOCK could end up with this similar exception.

Few are: ROWLOCK/TABLELOCK/UPDLOCK/SERIALIZABLE etc.

How to resolve this issue?

Two ways you can avoid issue as below:

1. Change the view definition to remove NOLOCK 

Create OR ALTER View vw_test 
as 
Select * From T1 
     where exists(Select 1 From T2 where t1.Col1 = t2.col1)
--Rowlock will conflicts with NOLOCK
Update A Set A.Col1  = A.Col1 + 2 
From vw_test (TABLOCKX) A

2. Change the query that uses view to remove the conflict lock hints 

--updlock will conflicts with NOLOCK
Update A Set A.Col1  = A.Col1 + 2 
From vw_test  A

Summary:

This is a simple error message and does not really require any extra thoughts to be put in to understand the error message or conflict scenario. This is not just for NOLOCK, any incompatible hints would cause this issue , however, I would like to take an important point that many of us are using NOLOCK as a free tool without knowing its implications. With this example( its just an another example), I would like to reiterate DO NOT use NOLOCK as a free tool, use it with atmost careful evaluation and understanding!

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!