Day #334 - Lessons Learnt in evaluating SQL 2016 Performance Features

Sharing my lessons on proposing SQL In-Memory table implementation for the product I worked with. I worked with Sunil Agarwal from the SQL product team to evaluate the features, benefits, migration approach, etc.



Happy Learning!!!

SQL 2019 - Interesting Features

SQL 2019 - Interesting Features (Link)

I have a lot of bias for SQL Server. Some SQL 2019 features are awesome. The things I liked are
Query heterogeneous databases with Polybase (Polybase feature was there in 2016 too but the databases supported is not as many as I see now)

• Polybase provides in SQL Server 2019 through a concept called an EXTERNAL TABLE.
• External tables are just like SQL Server tables except SQL Server only stores the metadata of the table definition
• Polybase uses ODBC drivers to connect to sources such as Oracle, Teradata, MongoDB, and SQL Server.

SQL Datalake capability

• Support for SQL, NoSQL
• Support for ML Engine
• Support for HDFS

These are promising features. Obviously, there will be some product limitations in the early stages.
Highlights

• Support for unstructured data
• Heterogenous database support
• Schema on read is achieved with external tables
• SQL wrapper to query both different databases / unstructured data
• Integration with HDFS
• ML APIs / Visualization features

Very good move to accommodate / position SQL as an Integration Database engine for heterogenous/unstructured/structured data

Happy Learning!!!

SQL Performance Tuning & Coding Guidelines

This vacation was useful to find some of my prior work / presentation. Sharing some of my Earlier SQL Performance Tuning Slides I did my Balmukund from SQL Product Support Team.

Happy Learning!!!

SQL Server Database Restore Notes

While installing SQL 2012 on Windows Server 2008 R2

• Enable windows 3.5 from Windows Add / Remove Features
• While trying to restore SQL 2012 backup on SQL 2008, Error 'media family on device is incorrectly formed' for version mismatch is misleading

SQL Version to check query

SELECT

SERVERPROPERTY('ProductVersion') AS ProductVersion,

SERVERPROPERTY('ProductLevel') AS ProductLevel,

SERVERPROPERTY('Edition') AS Edition,

SERVERPROPERTY('EngineEdition') AS EngineEdition;

GO

Useful Notes

• How to extract the contents from an .ISO file without burning the .ISO to disc.
• Copying a SQL Server Database to Another Environment - (RESTORE FILELISTONLY command was very useful)
• How I completely remove a SQL Server database – Guaranteed to work – At least on my current setup - (Stop Services -> Delete Db Files -> Start Services -> Delete DBs) - 2 mins deletion

Quick Script encompassing the steps from above notes

Step 1 - Stop SQL Server
NET stop MSSQLSERVER

Step 2 - Remove DB Folders (MDF, LDF Files)
rmdir /s /q "C:\Databases\VOL\Data\"
rmdir /s /q "C:\Databases\VOL\Log\"

Step 3 - Recreate Same file path for Restore
mkdir  "C:\Databases\VOL\Data\"
mkdir  "C:\Databases\VOL\Log\"

Step 4 - Start SQL Server
NET start MSSQLSERVER

Step 5 - Run DB Restore script
Db Restore Command

Restore Script

• Identify mdf, log files from bak files
• Set DB to single user to restrict access
• Restore DB
• Set DB to multi user mode

RESTORE FILELISTONLY

FROM DISK = N'E:\TestDB_backup_20150210_000143.bak'

use master

ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE TestDB

FROM DISK = N'E:\TestDB_backup_20150210_000143.bak'

WITH REPLACE,

FILE = 1

    , MOVE N'TestDB_DAT'

        TO N'C:\databases\vol\DATA\TestDB.MDF'

    , MOVE N'TestDB_LOG'

        TO N'C:\databases\vol\LOG\TestDB_log.LDF'

    , NOUNLOAD, STATS = 10

   

ALTER DATABASE TestDB

SET MULTI_USER

WITH ROLLBACK IMMEDIATE

GO

Tweak it as you need :)

Happy Learning!!!

How to Lock a Stored Procedure for Single Use Only

A gud example mentioned using usage of Application locks. Application lock is a bit different than other kinds of SQL Server locks though. While other locks lock schema or data, application locks lock a part of your code. There are 2 stored procedure that are used for this: sp_getapplock and sp_releaseapplock.

Example here

SQL Server Training videos

Free SQL Server Training Vidoes

http://weblogs.asp.net/scottgu/archive/2007/03/01/free-sql-server-training-videos-and-other-good-data-tutorial-pointers.aspx

http://www.jumpstarttv.com/

http://www.asp.net/LEARN/webcasts/

How do I Vidoes
http://msdn.microsoft.com/en-us/bb629407.aspx

SQL Blog Collection List - WOW 40 Blogs

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/02/14/wow-lots-of-blogs-from-the-sql-product-team.aspx

Partition Elimination in SQL Server 2005

Partition function and Partition Scheme Step by Step Example

http://blogs.msdn.com/sqlcat/archive/2006/02/17/Partition-Elimination-in-SQL-Server-2005.aspx


Tried the same for Table test1. Deleting data from a Partition
--STEP 1

select $partition.PF1(a) [Partition Number], a, b from test1

--STEP2
--To Delete this data from a Partition in the Table, Create another table and switch the partition
create table test1_truncate (a int, b int)

DECLARE @PartitionNumber INT
SET @PartitionNumber = 1
ALTER TABLE dbo.test1 SWITCH PARTITION @PartitionNumber TO test1_truncate;

TRUNCATE TABLE test1_truncate

--STEP 3
select $partition.PF1(a) [Partition Number], a, b from test1

Partitioning Tips
Handling Large SQL Server Tables with Data Partitioning
Dynamically controlling the number of rows affected by a SQL Server query


Thanks,
Sivaram

SQL Server Side Trace explained

As Against using profiler, Server side trace is light weight, runs in the server not at the client end. Details and walk thru provided here http://www.mssqltips.com/tip.asp?tip=1035

Thanks,
Sivaram