Home » SQL Server Administration » Page 3

SQL Server Administration

sql server DBA 999

SQL Server Memory & Storage Technology

SQL Server- Understanding Server Storage Technology Explained

First we need to understand the different types of memory below

a.Static random access memory (SRAM) comprising L1 and L2 caches for each physical core of a processor,larger but slower shared L3 cache for the entire processor.

b. Dynamic random access memory (DRAM) which also our RAM we install/upgrade in servers.

Both SRAM and DRAM are volatile and hold data only when electrical power is on.

c. SATA/SAS SSD hard drive(NAND)

d. Magnetic spinning media hard disk drives.

Each of Storage/memory above cost lower per MB or GB with higher total capacity as you go from a to d above.

Now New NVM Express PCIe SSD’s (NAND). These are really good with latency … Read the rest

sql server DBA 999

SQL Server 2016 New Features In-Memory OLTP enhancements

SQL Server 2016 New Features In-Memory OLTP enhancements

1. SQL Server In-Memory OLTP – Parallel and heap scan for memory-optimized tables

Memory-optimized tables and indexes in In-memory optimized tables are supported to parallel scan. This improves the performance of queries. Heap scan is also supported which can be performed in parallel which is scanning of all the rows in a table using the in-memory heap data structure used for storing the data.

2. SQL Server In-Memory OLTP -Transact-SQL/TSQL Improvements for memory-optimized tables

a.UNIQUE constraints and indexes are supported.
b.FOREIGN KEY references between memory-optimized tables are supported.
c.CHECK constraints are supported.
d. NULL values are allowed
e.Trigger’s are supported.

3. SQL Server 2016 In-Memory OLTP Storage format is now changed

In-Memory … Read the rest

sql server DBA 999

Setup R Services for SQL Server 2016 Step by Step Explained

Setup R Services for SQL Server 2016 Step by Step Tutorial

Microsoft purchased Revolution Analytics (around 2015) and with that they were able to give R Services which is the most popular open source programming language for advanced analytics of data inside of SQL Server bringing R processing closer to the data..

By adding R processing into SQL Server 2016 which is offered as a new feature that supports enterprise level data services.

What is R (Revolution) Services?

Revolution Analytics (formerly Revolution Computing) is a statistical software company focused on developing open source software for enterprise, academic and analytics customers. Revolution Analytics was founded in 2007. In 2009 the company received nine million in venture capital from Intel along with … Read the rest

sql server DBA 999

SQL Server Database Mail Configuration Step by Step

    SQL Server Database DB Mail Configuration Step by Step:

This Article will help Configuring SQL Server Database mail in SQL Server for Alerts and Notifications.

Database Mail is very Useful and powerful ability in SQL Server. Send email in SQL Server is one of the best ability for monitoring SQL Server

One can configure SQL Server Database Mail so that it sends you important notifications and alerts for example

1. Alerts for any high severity errors, corruptions etc..
2. Notifications Agent jobs Success or failure
3. You can also use Database Mail to email query results and reports

Configuring Database Mail and Enabling it on the SQL Server Agent for Alerts can be done by following below steps

Step 1.… Read the rest

sql server DBA 999

SQL Server 2014 Compared to older versions

 

SQL Server 2014 Compared to older versions

 

 

Features Feature Description SQL Server 2014 SQL Server 2012 SQL Server 2008ÿR2 SQL Server 2008
Performance In-memory OLTP* Yes No No No
In-memory ColumnStore* Yes Yes No No
Buffer pool extension to SSD Yes No No No
Resource Governor Yes Yes Yes Yes
Availability AlwaysOn* Yes Yes No No
Enhanced virtualization support and live migration Yes Yes Yes No
Security Transparent data encryption* Yes Yes Yes Yes
Backup encryption support Yes No No No
Fine-grained auditing Yes Yes Yes Yes
Separation of duties Yes Yes No No
Cloud-readiness Backup to Microsoft Azure Yes Yes No No
Disaster recovery to Microsoft Azure Yes No No No
Optimized VM images in Microsoft
… Read the rest
sql server DBA 999

SQL Server 2014 released..New features

SQL Server 2014 released… Let’s discover what’s new in SQL Server 2014 now. There are versions in SQL Server out of which Express version is still free . Download SQL Server from Microsoft Site or your company volume licensing depending on your version you require.

New features will be listed here which are really fantastic….

Note: Database Compatibility Level of 90 compatibility level is not valid in SQL Server 2014.

I will also post some examples of how to use these features in coming days…
Database Engine Feature Enhancements  For more info click here for microsoft site
Memory-Optimized Tables: In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine.

Buffer Pool Extension: The Buffer Pool Extension provides … Read the rest

sql server DBA 999

CTE vs Temp Table vs Temp variables

Temp tables: they are just like any normal tables except that they are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that it calls. Just like normal tables and have primary keys, constraints, indexes and column statistics which are kept for the table which makes query execution really fast in most cases.

Temp tables have space assigned to them in the TempDB database and are generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

Table Variables: These tables behave very much like other variables in their … Read the rest

sql server DBA 999

Temporary Variables vs Temporary Tables SQL Server 2008/2005

Myth: A table variable is a memory-only structure.
This is not True. A Temp table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables.

Note: If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

Normally whether to go with Temp Variables or Temp Tables depends on:
•The number of rows that are inserted to the table.
•The number of recompilations the query is saved from.
•The type of queries and their dependency on indexes and statistics for performance.
Some other important advantages of temporary tables:
•Non-clustered … Read the rest