Knowledge Base

Processing nested json dynamically in PySpark (databricks)

2024-11-22T22:39:00.000-08:00

Below youtube video provides excellent way to process complex json dynamically in PySpark (databricks)

Corresponding code is in github

https://github.com/raveendratal/ravi_azureadbadf/blob/main/azure_realtime_scenarios/dynamic_json_process.ipynb

How to remove Duplicates in DataFrame using PySpark

2022-11-19T00:49:00.003-08:00

Below ways can be used to remove duplicates from a dataframe in PySpark:

distinct

df.distinct()
df.distinct(["Column1","Column2"])

dropDuplicates

df.dropDuplicates()
df.dropDuplicates(["Column1","Column2"])

How to Sort a Dataframe in PySpark

2022-11-18T23:47:00.001-08:00

Below are different ways to sort a dataframe:

sort

df.sort(df.ColumnName)
df.sort(df.ColumnName.desc())
df.sort(df.ColumnName.desc(), df.ColumnName2)
df.sort(col("ColumnName"))

orderBy

df.orderBy(df.ColumnName)
df.orderBy(df.ColumnName.desc())
df.orderBy(df.ColumnName1.desc(), df.ColumnName2)
df.orderBy(col("ColumnName"))

How to filter a DataFrame using PySpark

2022-11-18T23:37:00.006-08:00

Multiple ways to filter dataframe data:

filter

df.filter(df.ColumnName ==VALUE)
df.filter(col("ColumnName") == VALUE)
df.filter((col("ColumnName1") == VALUE) | (col("ColumnName2") == VALUE))
df.filter((col("ColumnName1") == VALUE) & (col("ColumnName2") == VALUE))
df.filter(col("ColumnName") != VALUE)

How to ADD New Columns in DataFrame using PySpark

2022-11-18T22:55:00.007-08:00

Below are different ways to add new columns to dataframe in PySpark:

withColumn and lit

df.withColumn("NewColumnName", lit("default value for new column"))

withColumn and col (Derived column)

df.withColumn("NewColumnName", col("Column1") * col("Column2"))

select

df.select(lit("default column value").alias("NewColumnName"), col("Column1"), col("Column2"))

How to Rename columns in DataFrame using PySpark

2022-11-18T22:27:00.005-08:00

There are multiple ways to rename columns in dataframe using PySpark.

withColumnRenamed

df = df.withColumnRenamed("Old_ColumnName1", "New_ColumnName1").withColumnRenamed("Old_ColumnName2", "New_ColumnName2")

selectExpr

df = df.selectExpr("Old_ColumnName1 AS NewColumnName1","Old_ColumnName2 AS NewColumnName2")

select(col().alias(), col())

df2 = df.select(col("Old_ColumnName1").alias("NewColumnName1"), col("Old_ColumnName2"))

Useful tools

2019-03-08T02:11:00.001-08:00

ZoomIt: ZoomIt is a screen zoom and annotation tool for technical presentations that include application demonstrations.
Devart SQL Complete: Code completion, refactoring and formatting tool.
sp_whoisactive: sp_whoisactive is a comprehensive activity monitoring stored procedure that works for all versions of SQL Server from 2005 through 2017.
sp_SQLskills_helpindex: Tool to get index information. sp_helpindex displays limited information. This tool displays lot of information like included columns, filters, etc.

Difference between Table Variables, Temporary Tables (Hash Tables) and Temporal Tables

2018-01-22T00:31:00.000-08:00

Table Variables

Table variable is a special data type that can be used to store a result set for processing at a later time.
Table variable is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function.
Functions and variables can be declared to be of type table.
Table variables can be used in functions, stored procedures, and batches.
To declare variables of type table, use DECLARE @local_variable
A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in.

Example:

DECLARE @myTable TABLE

(

Id INT PRIMARY KEY,

FirstName VARCHAR(100) UNIQUE,

LastName VARCHAR(100) NOT NULL

)

INSERT INTO @myTable(Id, FirstName, LastName) VALUES (1,'A','A')

INSERT INTO @myTable(Id, FirstName, LastName) VALUES (2,'B',NULL) --Fail

INSERT INTO @myTable(Id, FirstName, LastName) VALUES (3,'A','A') --Fail

SELECT * from @myTable

SELECT * INTO @This_Will_Not_Work FROM @myTable

Only constraints allowed on the table variables are:

Primary Key
UNIQUE
NULL

Further, SELECT * INTO will not work

Temporary Tables (Hash Tables)

There are two types of temporary tables; one is local and the other is global.

Local Temporary Tables

Local temporary tables are the tables stored in tempdb. Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session. They are specified with the prefix #, for example #table_name and these temp tables can be created with the same name in multiple windows.

Example:

--Local temporary table

CREATE TABLE #myLocalTable

(

Id INT PRIMARY KEY,

FirstName VARCHAR(100) UNIQUE,

LastName VARCHAR(100) NOT NULL DEFAULT('')

)

INSERT INTO #myLocalTable(Id, FirstName, LastName) VALUES (1,'A','A')

INSERT INTO #myLocalTable(Id, FirstName) VALUES (2,'B') --Default LastName

Global Temporary Tables

Global temporary tables are also stored in tempdb. Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. They are specified with the prefix #, for example ##table_name.

Example:

--Global temporary table

CREATE TABLE ##myLocalTable

(

Id INT PRIMARY KEY,

FirstName VARCHAR(100) UNIQUE,

LastName VARCHAR(100) NOT NULL DEFAULT('')

)

INSERT INTO ##myLocalTable(Id, FirstName, LastName) VALUES (1,'A','A')

INSERT INTO ##myLocalTable(Id, FirstName) VALUES (2,'B') --Default LastName

Temporal Tables

SQL Server 2016 introduced support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

Example:

CREATE TABLE dbo.TemporalTable

(

ID INT NOT NULL PRIMARY KEY CLUSTERED,

FirstName VARCHAR(100),

LastName VARCHAR(100),

ValidFrom datetime2 GENERATED ALWAYS AS ROW START,

ValidTo datetime2 GENERATED ALWAYS AS ROW END,

PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

)

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableHistory));

--Insert a record

INSERT INTO dbo.TemporalTable(ID, FirstName, LastName) VALUES(1,'Akash','Agrawal')

--Current table will have one record. Notice the ValidFrom and ValidTo columns

SELECT * FROM dbo.TemporalTable

SELECT * FROM dbo.TemporalTableHistory --zero records

--Now, update record in the table

UPDATE TemporalTable

SET FirstName = 'Aakash'

WHERE ID = 1

--Data will be updated in current table. Notice the ValidFrom and ValidTo columns

SELECT * FROM dbo.TemporalTable

--History table will have one record with ValidFrom and ValidTo columns populated.

SELECT * FROM dbo.TemporalTableHistory

--Update again

UPDATE TemporalTable

SET FirstName = 'Aakaash'

WHERE ID = 1

--Notice multiple rows in History table

SELECT * FROM dbo.TemporalTable

SELECT * FROM dbo.TemporalTableHistory

--How to query History data

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME BETWEEN '2018-01-22 08:12:26.2254807' AND '2018-01-22 08:14:44.7907589'

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME AS OF '2018-01-22 08:14:44.7907589'

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME FROM '2018-01-22 08:12:26.2254807' TO '2018-01-22 08:14:44.7907589'

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME CONTAINED IN('2018-01-22 08:12:26.2254807' , '2018-01-22 08:15:44.7907589')

SELECT *

FROM dbo.TemporalTable

FOR SYSTEM_TIME ALL

ORDER BY ValidFrom

Limiting error log file size in SQL Server

2018-01-16T00:43:00.002-08:00

It has often been observed that error logs in SQL server increase to large values. Further there is not way to set limit on size of log file from SSMS.

Below is a very nice article to set the limits on the size of error log file along with the number of error log files.

https://www.sqlskills.com/blogs/paul/limiting-error-log-file-size-in-sql-server-2012/

Use of functions on indexed columns in predicates

2017-05-02T04:53:00.000-07:00

Use of functions on indexed columns which are used in predicates results in index not getting used for seek operation.

For example:

Use tempdb

set nocount on

IF OBJECT_ID(N'Customers', N'U') IS NOT NULL

DROP TABLE dbo.Customers;

CREATE TABLE dbo.Customers (

customer_nbr INT NOT NULL PRIMARY KEY,

customer_name VARCHAR(35) NOT NULL);

CREATE NONCLUSTERED INDEX ix_customers

ON dbo.Customers(customer_name);

INSERT dbo.Customers VALUES(1, 'Joe');

INSERT dbo.Customers VALUES(2, 'Leo');

INSERT dbo.Customers VALUES(3, 'Dave');

INSERT dbo.Customers VALUES(4, 'Lenny');

INSERT dbo.Customers VALUES(5, 'Larry');

INSERT dbo.Customers VALUES(6, 'Lefty');

INSERT dbo.Customers VALUES(7, 'Lemur');

If I want to write a query on this table to get all the customers whose first name starts with 'L'.

SELECT customer_name

FROM dbo.Customers

WHERE LEFT(customer_name, 1) = 'L';

Looking at the execution plan for above query. We can see index scan in here and not seek:

Now, what if we rewrite this query without the use of LEFT function as below:

SELECT customer_name

FROM dbo.Customers

WHERE customer_name LIKE 'L%';

Looking at the execution plan for above query shows us index seek and the output of query is same as before.

Functions on indexed column used in predicates makes them non-sargable and impacts performance.

Reference:

https://sqlbits.com/Sessions/Event14/Common_TSQL_Mistakes

SQL Server important links

2017-03-10T01:28:00.001-08:00

Joins

https://www.techonthenet.com/sql_server/joins.php

Indexes

https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

Index seek, scan, lookup and others in execution plan

https://www.simple-talk.com/sql/performance/identifying-and-solving-index-scan-problems/

Temporary tables vs table variables

https://www.mssqltips.com/sqlservertip/2825/sql-server-temp-table-vs-table-variable-performance-testing/

SARGable expressions

Store and retrieve data into XML from table in SQL Server

2017-01-24T01:30:00.002-08:00

The aim of this post is to store data in a table in the form of XML along with schema, and to retrieve the same from XML in a tabular structure.

Let us take an example of a table say Employee:

CREATE TABLE Employee

(

ID INT,

FName VARCHAR(200),

LNAME VARCHAR(200),

Salary FLOAT

);

INSERT INTO Employee(ID, FName, LName, Salary)

VALUES(1,'A','B',10.1),

(2,'C','D',12.123),

(3,'E','F',123.13)

DECLARE @xml XML

SELECT @xml = (

SELECT

ID,

FName,

LName,

Salary

FROM Employee

FOR XML AUTO, ELEMENTS, XMLSCHEMA('AnySchema'), ROOT('Root');

)

SELECT @xml

This is how Table can be stored in XML format along with schema. If you see the output XML, it will have two portions, one with schema and another with actual data.

Below, is how we can get back the data from XML into tabular structure.

DECLARE @XSD XML, @sql VARCHAR(MAX), @TableName VARCHAR(100)

SET @XSD = (SELECT

x.query('.')

FROM @XML.nodes('/Root/*[1]') a (x))

SELECT

@XML = (SELECT

CAST(STUFF(

CAST(@XML.query('*[1]/*') AS VARCHAR(MAX))

, 1

, CHARINDEX(

'</xsd:schema>'

, CAST(@XML.query('*[1]/*') AS VARCHAR(MAX))

) + 12

, ''

) AS XML)

FOR XML RAW ('Root'))

SELECT

@XML = CAST(REPLACE(CAST(@XML AS VARCHAR(MAX)), ' xmlns="AnySchema"', '') AS XML)

SELECT

@TableName = a.x.value('@name', 'varchar(100)')

FROM @XSD.nodes('/*/*') a (x)

SELECT

@XSD = (SELECT

a.x.query('.')

FROM @XSD.nodes('/*/*[2]/*/*') a (x))

SELECT

@XSD = CAST(REPLACE(CAST(@XSD AS VARCHAR(MAX)), 'p1:', '') AS XML)

IF @XSD IS NULL

BEGIN

SELECT

'XML Schema was not specified'

RETURN

END

IF EXISTS (SELECT

FROM Tempdb..sysobjects

WHERE id = OBJECT_ID('Tempdb..#TableDefinition'))

DROP TABLE #TableDefinition

CREATE TABLE #TableDefinition

(

ID INT IDENTITY(1,1),

ColumnName VARCHAR(255),

ColumnDataType1 VARCHAR(255),

ColumnDataType2 VARCHAR(255),

ColumnDataType2Length VARCHAR(255),

hasLength INT

)

INSERT INTO #TableDefinition

SELECT

a.x.value('@name', 'varchar(100)') ColumnName

,REPLACE(CAST(a.x.value('@type', 'sysname') AS VARCHAR(100)), 'sqltypes:', '') ColumnDataType1

,REPLACE(CAST(a.x.value('*[1]/*[1]/@base', 'sysname') AS VARCHAR(100)), 'sqltypes:', '') ColumnDataType2

,REPLACE(CAST(a.x.value('*[1]/*[1]/*[1]/@value', 'sysname') AS VARCHAR(100)), 'sqltypes:', '') ColumnDataType2Length

,0 AS hasLength

FROM @XSD.nodes('/*/*') a (x)

UPDATE #TableDefinition

SET hasLength = 1

,ColumnDataType2Length =

CASE

WHEN ColumnDataType2 IN ('numeric', 'decimal') THEN RTRIM(ColumnDataType2Length) + ',10'

ELSE ColumnDataType2Length

END

WHERE ColumnDataType2Length IS NOT NULL

DECLARE @TableDefinition VARCHAR(MAX)

SET @TableDefinition = (SELECT

CAST(CASE

WHEN hasLength = 1 THEN ColumnName + ' ' + ColumnDataType2 + '(' + ColumnDataType2Length + '),'

ELSE ColumnName + ' ' + ColumnDataType1 + ','

END AS VARCHAR(MAX))

FROM #TableDefinition a

FOR XML PATH (''))

SELECT

@TableDefinition = SUBSTRING(@TableDefinition, 0, LEN(@TableDefinition))

IF EXISTS (SELECT

FROM Tempdb..sysobjects

WHERE id = OBJECT_ID('Tempdb..#XML'))

DROP TABLE #XML

SELECT

@XML AS XMLDATA INTO #XML

SELECT

@sql =

' DECLARE @handle INT, @PrepareXMLStatus INT, @XML2 XML

SET @XML2 = (SELECT XMLDATA FROM #XML);

EXEC @PrepareXMLStatus = sp_xml_preparedocument @handle OUTPUT, @XML2;

SELECT *

FROM OPENXML(@handle, ''/Root/' + @TableName + ''', 2)

WITH (' + @TableDefinition + ')

EXEC sp_xml_removedocument @handle;'

EXEC (@sql)

Remove specific plan for procedure or query from the cache in SQL Server

2017-01-24T01:27:00.002-08:00

--Find the plan handle by providing the procedure name

SELECT plan_handle, st.text ,*

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE

objtype = 'Proc'

and text LIKE N'%zz_%';

-- Remove the specific plan from the cache.

DBCC FREEPROCCACHE (0x05000600D1D5E903F057B85B0400000001000000000000000000000000000000000000000000000000000000);

Implicit data conversion

2015-03-11T09:05:00.000-07:00

This is one thing that I didn't knew happened in SQL Server.

When integers are implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters ASCII character 42, the asterisk (*).

If we look at below example, when integer 123 is inserted it is implicitly converted to VARCHAR successfully. But, when value with length greater than 3 is inserted, asterisk is inserted instead.

Find a job with a particular text in SQL Server

2013-06-26T00:48:00.005-07:00

Find a job in SQL Server with a particular text.

USE [msdb]

SELECT j.job_id,

s.srvname,

j.name,

js.step_id,

js.command,

j.enabled

FROM dbo.sysjobs j

JOIN dbo.sysjobsteps js

ON js.job_id = j.job_id

JOIN master.dbo.sysservers s

ON s.srvid = j.originating_server_id

WHERE js.command LIKE N'%text to search in job%'

Get comma separated values from table grouped by particular column

2013-06-26T00:42:00.002-07:00

Suppose we have a table

CREATE TABLE #temp

(

ID INT,

name NVARCHAR(100)

)

INSERT INTO #temp(ID,name)

VALUES(1,'Akash'),

(1,'B'),

(1,'C'),

(2,'AA'),

(2,'BB')

We want to get the names as comma separated grouped by ID, so for ID 1 we will have 'Akash, B, C'. Below query will create a comma separated list grouped by ID.

select distinct u1.ID,

Replace((select distinct u2.name + ',' as 'data()'

from #temp u2

where u2.ID = u1.ID

FOR XML PATH('')) + '$', ',$', '') as Names

from #temp u1

order by u1.ID

Clear Plans for particular type of object in SQL Server

2013-06-26T00:35:00.004-07:00

-- clear plans for a particular type of object

-- clear sql plans: adhoc sql, prepared statements

dbcc freesystemcache('SQL Plans');

-- clear object plans: stored procedures, functions, triggers

dbcc freesystemcache('Object Plans');

-- clear bound trees: views, constraints, defaults

dbcc freesystemcache('Bound Trees');

-- clear extended stored procedures

dbcc freesystemcache('Extended Stored Procedures');

Find all the stored procedures with particular text..

2013-06-26T00:34:00.000-07:00

Below query will help find all the stored procedures with a particular text. System table syscomments contains the text of stored procedure.

DECLARE @StringToSearch VARCHAR(500)

SET @StringToSearch = '%text in stored procedure%'

SELECT Distinct SO.Name

FROM sysobjects SO (NOLOCK)

INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID

AND SO.Type = 'P'

AND SC.Text LIKE @stringtosearch

ORDER BY SO.Name

Find Indexes Not In Use in SQL Server

2012-12-21T07:36:00.000-08:00

We found out that we have lot of indexes on our tables which were not of any use and hence needed to be removed. Found this link that does the same. Below query helps to find out indexes which have not been used since last restart.

SELECT

o.name

, indexname=i.name

, i.index_id

, reads=user_seeks + user_scans + user_lookups

, writes = user_updates

, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)

, CASE

WHEN s.user_updates < 1 THEN 100

ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates

END AS reads_per_write

, 'DROP INDEX ' + QUOTENAME(i.name)

+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id

INNER JOIN sys.objects o on s.object_id = o.object_id

INNER JOIN sys.schemas c on o.schema_id = c.schema_id

WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1

AND s.database_id = DB_ID()

AND i.type_desc = 'nonclustered'

AND i.is_primary_key = 0

AND i.is_unique_constraint = 0

AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000

ORDER BY reads

How to script SQL Server Logins

2012-12-21T07:27:00.001-08:00

Every now and then you add new instance you have to create logins on new server which are exact replica of some existing server. In that case it is tedious to create logins manually and a script that would do the job would be great.

I found a great script which does the same. Below is the script copied from the link. It is of great help.

USE [master]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_hexadecimal]    ****/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_hexadecimal] 
(
    -- Add the parameters for the function here
     @binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN

    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF' 
    WHILE (@i <= @length) 
    BEGIN
      DECLARE @tempint int
      DECLARE @firstint int
      DECLARE @secondint int
      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
      SELECT @i = @i + 1
    END
    return @charvalue

END
GO


SET NOCOUNT ON
GO
--use MASTER
GO
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Script created on ' + CAST(GETDATE() AS VARCHAR(100))
PRINT '-----------------------------------------------------------------------------'
PRINT ''
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the windows logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = ''' + [name] + ''')
    CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + 
        default_database_name + '], DEFAULT_LANGUAGE=[us_english]
GO

'
FROM master.sys.server_principals
where type_desc In ('WINDOWS_GROUP', 'WINDOWS_LOGIN')
AND [name] not like 'BUILTIN%'
and [NAME] not like 'NT AUTHORITY%'
and [name] not like '%\SQLServer%'
GO

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the SQL Logins'
PRINT '-----------------------------------------------------------------------------'
select 'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
    CREATE LOGIN [' + [name] + '] 
        WITH PASSWORD=' + [master].[dbo].[fn_hexadecimal](password_hash) + ' HASHED,
        SID = ' + [master].[dbo].[fn_hexadecimal]([sid]) + ',  
        DEFAULT_DATABASE=[' + default_database_name + '], DEFAULT_LANGUAGE=[us_english], 
        CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
    ALTER LOGIN [' + [name] + ']
        WITH CHECK_EXPIRATION=' + 
            CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=' + 
            CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + '
GO


'
--[name], [sid] , password_hash 
from master.sys.sql_logins 
where type_desc = 'SQL_LOGIN' 
and [name] not in ('sa', 'guest')

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Disable any logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'ALTER LOGIN [' + [name] + '] DISABLE
GO
' 
from master.sys.server_principals 
where is_disabled = 1

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Assign groups'
PRINT '-----------------------------------------------------------------------------'
select 
'EXEC master..sp_addsrvrolemember @loginame = N''' + l.name + ''', @rolename = N''' + r.name + '''
GO

'
from master.sys.server_role_members rm
join master.sys.server_principals r on r.principal_id = rm.role_principal_id
join master.sys.server_principals l on l.principal_id = rm.member_principal_id
where l.[name] not in ('sa')
AND l.[name] not like 'BUILTIN%'
and l.[NAME] not like 'NT AUTHORITY%'
and l.[name] not like '%\SQLServer%'

Who is Active? DMV Monitoring tool

2012-07-16T02:36:00.001-07:00

Here is the link for the very important tool that you need to know what is running on your SQL Server... Tool is developed by Adam Machanic...

http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx

How to Partition a Table in SQL Server..

2012-05-24T19:53:00.000-07:00

What is Table Partitioning?
Table partitioning is built-in method of horizontally partitioning data within a table and/or index still maintaining the table as a single logical object. Partitioning keeps the same no of columns in each partition, but reducing the number of rows.

Why do we need Table Partitioning?

- Easy management of large tables and indexes.
- Decrease load time.
- Improve query performance.
- Provide small window for maintenance, means easy to maintain chunks of data instead of whole.

How Partitioning is done?

- Create file -groups to hold the partitions.
- Add files to each file -group in the partitioning.
- Create Partition Function.
- Create Partition Scheme.
- Create table , binding a specific column to a Partition Scheme.

Below is an example of partitioning a table:

Create a sample database to be used for partitioning. I have added two file groups other than the primary file group to it.

CREATE DATABASE [PartitionDB] ON
PRIMARY
      (     NAME = N'PartitionDB',
            FILENAME = N'C:\PartitionDB\PartitionDB.mdf' ,
            SIZE = 3072KB , FILEGROWTH = 1024KB
      ),
FILEGROUP [FG2]
      (     NAME = N'PartitionDB_2',
            FILENAME = N'C:\PartitionDB\PartitionDB_2.ndf' ,
            SIZE = 3072KB , FILEGROWTH = 1024KB
      )
FILEGROUP [FG3]
      (     NAME = N'PartitionDB_3',
            FILENAME = N'C:\PartitionDB\PartitionDB_3.ndf' ,
            SIZE = 3072KB , FILEGROWTH = 1024KB
      )
LOG ON
      (     NAME = N'PartitionDB_log',
            FILENAME = N'C:\PartitionDB\PartitionDB_log.ldf' ,
            SIZE = 9216KB , FILEGROWTH = 10%
      )

Now we create a Partition Function. Partition Function can be used for any Partition Scheme. Please read the below link for specific details about the partition function:

http://msdn.microsoft.com/en-us/library/ms187802(v=sql.105).aspx

CREATE PARTITION FUNCTION EqualFunc (INT)
AS RANGE LEFT
FOR VALUES (0,1)

Next step is to create a Partition Scheme. Here is the link for Partition Scheme:

http://msdn.microsoft.com/en-us/library/ms179854(v=sql.105).aspx

CREATE PARTITION SCHEME EqualScheme
AS PARTITION EqualFunc
TO (FG2, FG3, [PRIMARY])

Finally we create a table using the Partition Scheme that we created. Based on the value of ID column PartitionElement will be computed and that will be used in deciding on which file group the row will be stored.

CREATE TABLE Customer
(
      ID     INT,
      Name        VARCHAR(20),
      PartitionElement AS ID % 3 PERSISTED
)
ON EqualScheme (PartitionElement)

INSERT INTO Customer(ID,Name) VALUES(3,'abc3')
INSERT INTO Customer(ID,Name) VALUES(1,'abc1')
INSERT INTO Customer(ID,Name) VALUES(2,'abc2')

Below query will determine the rows in each partition that is created for the table.

SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('Customer')

If we want to get the resultset for the particular partition here is the query

SELECT *
FROM  Customer
WHERE $PARTITION.EqualFunc(PartitionElement) = 2

It is advisable to keep the filegroups on different disk drives for parallelism. Partitioning is a great way to improve performance for the tables with large amount of data.

Memory Behavior in SQL Server

2012-05-05T22:18:00.003-07:00

Memory behavior of SQL Server 2008 is very different from SQL Server 2005. On observing the memory utilization of the server on which SQL Server was installed we found that SQL Server takes up all the memory that is available. But we were not completely right. By doing some research I came up with below summary:

o “When you start SQL Server, SQL Server memory usage may continue to steadily increase and not decrease, even when activity on the server is low. Additionally, the Task Manager and Performance Monitor may show that the physical memory available on the computer is steadily decreasing until the available memory is between 4 to 10 MB.This behavior alone does not indicate a memory leak. This behavior is normal and is an intended behavior of the SQL Server buffer pool.”

o “By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) depending on the physical memory load reported by the operating system. As long as enough memory is available to prevent paging (between 4 - 10 MB), the SQL Server buffer pool will continue to grow. As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.”

o You can establish upper and lower limits to the amount of memory (buffer pool) used by the SQL Server database engine with the min server memory and max server memory configuration options.

o “Note that the max server memory option only limits the size of the SQL Server buffer pool. The max server memory option does not limit a remaining unreserved memory area that SQL Server leaves for allocations of other components such as extended stored procedures, COM objects, non-shared DLLs, EXEs, and MAPI components. Because of the preceding allocations, it is normal for the SQL Server private bytes to exceed themax server memory configuration. ”

o If you are running 32-bit SQL Server, and need access to more than 2 GB of user mode VAS (Virtual Address Space), then you have to use Lock Pages in Memory; there is no debate there. You must configure the OS to use Physical Address Extensions (PAE), enable Address Windowing Extensions (AWE), and then assign the Lock Pages in Memory permission to the SQL Server account so that it can allocate AWE memory, via calls to AllocateUserPhysicalPages() (Windows API). So, in a typical 32-bit server using LPIM(Lock Pages In Memory) you'd have, in addition to the 2 GB of pageable user mode VAS, a separate AWE-mapped area, up to 64 GB (the PAE pointer was 36-bit), of non-pageable memory. This AWE-mapped area is for exclusive use by the data cache portion of the buffer pool. The rest of the buffer pool (mainly the plan cache), and other non-buffer pool allocations are still mapped within the 2 GB of user mode VAS.

o However, the advent of a 64-bit SQL Server process completely changed the dynamics of memory allocation by the SQLOS. In place of the default 2 GB user mode VAS, for a 32-bit process, a 64-bit process has access to up to 8 TB of user mode VAS out-of-the-box, without any need for further configuration changes! 64-bit users now have a potentially-vast amount of memory for the buffer pool, but all of which is allocated via VirtualAlloc, and backed by user mode VAS, and so is pageable i.e. in the absence of LPIM, the memory allocated for the data and plan cache is pageable.

o Since the operating system still requires memory to operate, you should set the max server memory configuration option to ensure that the operating system has sufficient memory once SQL Server has committed its memory for use. This is especially important on 64 bit servers where the larger VAS allows for the SQL process to take all of the physical memory which can result in a paging situation for the SQL processes, degrading performance. For this reason, 64 bit servers should use the Lock Pages in Memory right so that the AWE Mechanism is used to allocate memory, preventing paging of the BPool.

References:

1. http://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/

2. http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

3. http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/ed17f4d1-0980-4e3a-9a12-26a85cbbac04

4. http://support.microsoft.com/kb/321363

5. http://blogs.msdn.com/b/slavao/archive/2005/04/29/413425.aspx

TempDB Configuration in SQL Server

2012-05-05T22:11:00.000-07:00

Recently in our production environment we were facing performance issues. After tracking down we found that reason could be because of incorrect tempDB configuration. So, I started reading few articles on msdn and written by experts about tempDB configuration and came up with below summary.

· Should I use 1 file or multiple files for TempDB?

o One TempDB data file per physical corefor the SQL Server instance.This prevents helps contention that can occur during peak tempdb activity.

o The data files for tempdb should all be the same size. This allows for optimal proportional-fill performance.

o All data files have equal growth increment

o No data files have percent growth increment

· Where (i.e. what disks) should I create TempDB?

o Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.

o Put the tempdb database on disks that differ from those that are used by user databases.Tempdb should be separated and isolated from user databases. This creates contention between the user databases and tempdb.

o Tempdb data files and log files should be on separate disks.

o The TempDB files should definitely not be on the system (C) drive. If TempDB grows out of control, perhaps due to a large sort query, the system can run out of hard drive space and fail to start.

· What should be the size of TempDB?

o Unlike user databases where you can probably estimate the growth rate of tables over time based on the workload, estimating the size of TempDB is non-trivial.

o The size of TempDB can be affected for example by a change in query plans, version store when there is a long running transaction and so on.

o The best way to estimate the size of TempDB is by running your workload in a test environment. Once you have a good estimate of the size of TempDB, use Alter Database command to set its size with a safety factor that you feel is appropriate.

o Don’t rely on auto-grow to manage file sizes. You must set the appropriate size of TempDB using Alter Database command.

o Auto-grow causes a pause during processing when you can least afford it

o Auto-grow leads to physical fragmentation

o Tempdb Data files should be large enough to handle the workload for all 24 hours of the day.

References:

1. http://blogs.msdn.com/b/dfurman/archive/2010/04/05/tempdb-configuration-check-script.aspx

2. http://sqlserverpedia.com/blog/sql-server-bloggers/5-common-mistakes-with-tempdb/

3. http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx

4. http://technet.microsoft.com/en-us/library/cc966545.aspx

5. http://sqlserverpedia.com/wiki/Configuring_Database_Files_for_Optimal_Perfomance#TempDB_Database_Configuration

Few articles about different types of locks in SQL Server

2012-04-08T19:52:00.000-07:00

http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005