In Memory OLTP – 2

Good day everyone , In last post

In Memory OLTP – 1

we saw the what is In Memory OLTP and what are the requirements for that. In This post I will show you the demo of how we get performance gain.

The scripts (demo ) i will be using are from MSDN. which i will be using on my local machine to show you the the  demo.

  1. Create a database named imoltp and alter its file details to set it up for using In-Memory OLTP.

Create a new database.

CREATE DATABASE imoltp

GO

— Prepare the database for In-Memory OLTP by adding a memory-optimized filegroup to the database.

ALTER DATABASE imoltp ADD FILEGROUP imoltp_file_group

    CONTAINS MEMORY_OPTIMIZED_DATA;

— Add a file (to hold the memory-optimized data) to the new filegroup.

ALTER DATABASE imoltp ADD FILE (name=’imoltp_file’, filename=’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\imoltp_file’)

    TO FILEGROUP imoltp_file_group;

GO

2.Create the database objects for our sample:

Three tables and a natively-compiled stored procedure.

run the following code to create the disk-based table, two (2) memory-optimized tables, and the natively-compiled stored procedure that will be used to demonstrate the different data access methods:

USE imoltp

GO

— If the tables or stored procedure already exist, drop them to start clean.

IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = ‘DiskBasedTable’)

   DROP TABLE [dbo].[DiskBasedTable]

GO

IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = ‘InMemTable’)

   DROP TABLE [dbo].[InMemTable]

GO

IF EXISTS (SELECT NAME FROM sys.objects  WHERE NAME = ‘InMemTable2’)

   DROP TABLE [dbo].[InMemTable2]

GO

IF EXISTS (SELECT NAME FROM sys.objects  WHERE NAME = ‘usp_InsertData’)

   DROP PROCEDURE [dbo].[usp_InsertData]

GO

— Create a traditional disk-based table.

CREATE TABLE [dbo].[DiskBasedTable] (

  c1 INT NOT NULL PRIMARY KEY,

  c2 NCHAR(48) NOT NULL

)

GO

— Create a memory-optimized table.

CREATE TABLE [dbo].[InMemTable] (

  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),

  c2 NCHAR(48) NOT NULL

) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);

GO

— Create a 2nd memory-optimized table.

CREATE TABLE [dbo].[InMemTable2] (

  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),

  c2 NCHAR(48) NOT NULL

) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);

GO

— Create a natively-compiled stored procedure.

CREATE PROCEDURE [dbo].[usp_InsertData]

  @rowcount INT,

  @c NCHAR(48)

  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

  AS

  BEGIN ATOMIC

  WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’us_english’)

  DECLARE @i INT = 1;

  WHILE @i <= @rowcount

  BEGIN

    INSERT INTO [dbo].[inMemTable2](c1,c2) VALUES (@i, @c);

    SET @i += 1;

  END

END

 

olp1

3.Run the different queries and display the response times for each query.

Run the following code multiple times. Ignore the results from the first run which is negatively affected by initial memory allocation.

SET STATISTICS TIME OFF;

SET NOCOUNT ON;

— Delete data from all tables to reset the example.

DELETE FROM [dbo].[DiskBasedTable]

    WHERE [c1]>0

GO

DELETE FROM [dbo].[inMemTable]

    WHERE [c1]>0

GO

DELETE FROM [dbo].[InMemTable2]

    WHERE [c1]>0

GO

— Declare parameters for the test queries.

DECLARE @i INT = 1;

DECLARE @rowcount INT = 100000;

DECLARE @c NCHAR(48) = N’12345678901234567890123456789012345678′;

DECLARE @timems INT;

DECLARE @starttime datetime2 = sysdatetime();

— Disk-based table queried with interpreted Transact-SQL.

BEGIN TRAN

  WHILE @I <= @rowcount

  BEGIN

    INSERT INTO [dbo].[DiskBasedTable](c1,c2) VALUES (@i, @c);

    SET @i += 1;

  END

COMMIT

SET @timems = datediff(ms, @starttime, sysdatetime());

SELECT CAST(@timems AS VARCHAR(10)) + ‘ ms (disk-based table with interpreted Transact-SQL).’;

— Memory-optimized table queried with interpreted Transact-SQL.

SET @i = 1;

SET @starttime = sysdatetime();

BEGIN TRAN

  WHILE @i <= @rowcount

    BEGIN

      INSERT INTO [dbo].[InMemTable](c1,c2) VALUES (@i, @c);

      SET @i += 1;

    END

COMMIT

SET @timems = datediff(ms, @starttime, sysdatetime());

SELECT CAST(@timems AS VARCHAR(10)) + ‘ ms (memory-optimized table with interpreted Transact-SQL).’;

— Memory-optimized table queried with a natively-compiled stored procedure.

SET @starttime = sysdatetime();

EXEC usp_InsertData @rowcount, @c;

SET @timems = datediff(ms, @starttime, sysdatetime());

SELECT CAST(@timems AS VARCHAR(10)) + ‘ ms (memory-optimized table with natively-compiled stored procedure).’;

 

here is what i recived as output

oltp2

How memory-optimized tables perform faster

Dual nature:

A memory-optimized table has a dual nature: one representation in active memory, and the other on the hard disk. Each transaction is committed to both representations of the table. Transactions operate against the much faster active memory representation. Memory-optimized tables benefit from the greater speed of active memory versus the disk. Further, the greater nimbleness of active memory makes practical a more advanced table structure that is optimized for speed. The advanced structure is also pageless, so it avoids the overhead and contention of latches and spinlocks.

No locks:

The memory-optimized table relies on an optimistic approach to the competing goals of data integrity versus concurrency and high throughput. During the transaction, the table does not place locks on any version of the updated rows of data. This can greatly reduce contention in some high volume systems.

Row versions:

Instead of locks, the memory-optimized table adds a new version of an updated row in the table itself, not in tempdb. The original row is kept until after the transaction is committed. During the transaction, other processes can read the original version of the row.

When multiple versions of a row are created for a disk-based table, row versions are stored temporarily in tempdb.

Less logging:

The before and after versions of the updated rows are held in the memory-optimized table. The pair of rows provides much of the information that is traditionally written to the log file. This enables the system to write less information, and less often, to the log. Yet transactional integrity is ensured.

How native procs perform faster

Reduction in instruction 

Converting a regular interpreted stored procedure into a natively compiled stored procedure greatly reduces the number of instructions to execute during run time.

Trade-offs of In-Memory features

As is common in computer science, the performance gains provided by the In-Memory features are a trade-off. The better features bring benefits that are more valuable than the extra costs of the feature.

Trade-offs of memory-optimized tables

Estimate memory: You must estimate the amount of active memory that your memory-optimized table will consume. Your computer system must have adequate memory capacity to host a memory-optimized table.

Partition your large table: One way to meet the demand for lots of active memory is to partition your large table into parts in-memory that store hot recent data rows versus other parts on the disk that store cold legacy rows (such as sales orders that have been fully shipped and completed). This partitioning is a manual process of design and implementation.

Trade-Offs Of native procs

A natively compiled stored procedure cannot access a disk-based table. A native proc can access only memory-optimized tables.

When a native proc runs for its first time after the server or database was most recently brought back online, the native proc must be recompiled one time. This causes a delay before the native proc starts to run.

 

 

 

That’s All folks in this post. suggestion and comments are welcome. will try to add more about index for In memory OLTP in next post till than enjoy and keep reading

 

In Memory OLTP – 1

untitled

In-Memory OLTP  (A.K.A. Hekaton) is a specialized, memory-optimized relational data management engine and native stored procedure compiler, integrated into SQL Server.

Microsoft designed In-Memory OLTP to handle the most demanding OLTP workloads. To accomplish this, In-Memory OLTP introduces two fundamentally new concepts: memory-optimized tables and natively compiled stored procedures.

Data in memory-optimized tables resides in memory, and while transactions are logged for recovery, there is no paging to disk like traditional disk-based tables. Memory-optimized tables provide highly optimized data access structures using hash and non-clustered ordered indexes. The internal structures of these indexes are different from traditional B-trees and provide a new, high performance way of accessing in-memory data. Data access and transaction isolation are handled through a multi-version, concurrency control mechanism that provides an optimistic, non-blocking implementation. While implemented differently from traditional RDBMS, In-Memory OLTP still provides ACID compliance.

In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for transaction processing. 

In Memory OLTP significantly improve OLTP database application performance by improving throughput and reducing latency for transactional processing.

System Requirement for use of In Memory OLTP in SQL Server 2014

Hardware requirement

In-Memory OLTP uses memory and disk in different ways than traditional disk-based tables. The  performance improvement you will see with In-Memory OLTP depends the hardware you use.

CPU 

In-Memory OLTP does not require a high-end server to support a high-throughput OLTP workload. Recommendation is to use  mid-range server with 2 CPU sockets. Due to the increased throughput enabled by In-Memory OLTP, 2 sockets are likely going to be enough for business needs.

Turn hyper-threading ON with in-memory OLTP. With some OLTP workloads  have seen performance gains of up to 40% when using hyper-threading.

Memory 

All memory-optimized tables reside fully in memory. Therefore, you must have enough physical memory for the tables themselves and to sustain the workload running against the database – how much memory you actually need really depends on the workload, but as a starting point you will probably want enough available memory for about 2X the data size. You will also need enough memory for the buffer pool in case the workload also operates on traditional disk-based tables.

To find out the memory use by memory optimized table run the below Query

select object_name(object_id), * from sys.dm_db_xtp_table_memory_stats 

It is important to keep in mind when you use in-memory OLTP that your whole database does not need to fit in memory. You can have a multi-Terabyte database and still benefit from in-memory OLTP, as long as the size of your hot data (i.e., the memory-optimized tables) does not exceed 256GB.

Non-durable memory-optimized tables (NDTs), i.e., memory-optimized tables with DURABILITY=SCHEMA_ONLY are not persisted on disk. Although NDTs are not limited by the number of checkpoint files, only 256GB is supported.

Log drive 

Log records pertaining to memory-optimized tables are written to the database transaction log, along with the other SQL Server log records.

It is always important to put the log file on a drive that has low latency, such that transactions do not need to wait too long, and to prevent contention on log IO. Your system will run as fast as your slowest component. You need to ensure that, when running In-Memory OLTP, your log IO device does not become a bottleneck. Recommendation is to use a storage device with low latency, at least SSD.

Note that memory-optimized tables use less log bandwidth than disk-based tables, as they do not log index operations and do not log UNDO records. This can help to relieve log IO contention.

Data drive 

Persistence of memory-optimized tables using checkpoint files uses streaming IO. Therefore, these files do not need a drive with low latency or fast random IO. Instead, the main factor for these drives is the speed of sequential IO and bandwidth of the host bus adapter (HBA). Thus, you don’t need SSDs for checkpoint files; you can place them on high performance spindles (e.g., SAS), as long as their sequential IO speed meets your requirements.

The biggest factor in determining the speed requirement is your RTO [Recovery Time Objective] on server restart. During database recovery, all data in the memory-optimized tables needs to be read from disk, into memory. Database recovery happens at the sequential read speed of your IO subsystem; disk is the bottleneck.

To meet strict RTO requirements Recommendation is to spread the checkpoint files over multiple disks, by adding multiple containers to the MEMORY_OPTIMIZED_DATA filegroup.

SQL Server supports parallel load of checkpoint files from multiple drives – recovery happens at the aggregate speed of the drives.

 

Requirement Summary

  • 64-bit Enterprise, Developer, or Evaluation edition of SQL Server 2014.
  • SQL Server needs enough memory to hold the data in memory-optimized tables and indexes. To account for row versions, you should provide an amount of memory that is two times the expected size of memory-optimized tables and indexes
  • If you have disk-based tables in the database, you need to provide enough memory for the buffer pool and query processing on those tables.
  • Free disk space for that is two times the size of your durable memory-optimized tables.
  • A processor needs to support the instruction cmpxchg16b to use In-Memory OLTP. All modern 64-bit processors support cmpxchg16b.
  • The total in-memory size of all durable tables in a database should not exceed 250 GB.
  • This release of In-Memory OLTP is targeted to perform optimally on systems with 2 or 4 sockets and fewer than 60 cores.
  • Checkpoint files must not be manually deleted. SQL Server automatically performs garbage collection on unneeded checkpoint files.
  • If you create one or more databases with memory-optimized tables, you should enable Instant File Initialization (grant the SQL Server service startup account the SE_MANAGE_VOLUME_NAME user right) for the SQL Server instance. Without Instant File Initialization, memory-optimized storage files (data and delta files) will be initialized upon creation, which can have negative impact on the performance of your workload.

 

 

 

In The Next Post , I will show you the demo of Performance improvement by using In Memory OLTP  and will explain you why we get the gain in performance.

Till then  enjoy ….. and if you like the post or have something to say than do comment so..

 

How to identify the source of page splits in a database

Page splits can slow down your queries and increase the amount of I/O needed to retrieve your data.

So, how do you monitor and identify the source of page splits, so you can avoid them in your databases?

The short answer is that you can monitor page splits with extended events. There’s an event called page_split. The problem is that there are many kinds of page split. You only need to catch the problematic ones, but the page_split event doesn’t identify this.

When one page becomes full and a new page is needed, this is reported as a page split. But this is a regular operation – it doesn’t necessarily have bad consequences for your queries.

The problem occurs with updates and non-sequential inserts, when a row needs to be inserted in the middle of an object’s pages and there’s no space.

SQL Server creates a new page, transfers half of the page data to the new page and writes the row data. This creates page fragmentation, which is very bad for performance. This is also reported as page split.

You can find these problematic page splits using the event sql_server.transaction_log. This event monitors all the activities in the transaction log, so we need to use it with caution.

Specifically, you need to filter the operation field, looking for the value 11, which records LOP_DELETE_SPLIT. This is the deletion of rows that happens when SQL Server moves rows from one page to another in a ‘bad’ page split.

This event isn’t visible in the Extended Event interface, so we need to create a session using T-SQL

CREATE EVENT SESSION [BadSplits] ON SERVER 

ADD EVENT sqlserver.transaction_log (   

WHERE operation = 11 — LOP_DELETE_SPLIT

)

ADD TARGET package0.event_file

 — You need to customize the path   

( SET filename = N’C:\xel\badsplits.xel’ )

GO 

— Start the session

ALTER EVENT SESSION [Blocked]

ON SERVERSTATE = START;

GO

After some time, we can query this session to find the source of a page split. The first challenge is to find the database where the page splits are happening.

To read the information in the files, you need to use the DMF sys.fn_xe_file_target_read_file. The information will be returned in XML, so we need to use XML methods to retrieve the information:

WITH    qry   

AS ( SELECT

— Retrieve the database_id from inside the XML document

theNodes.event_data.value   

(‘(data[@name=”database_id”]/value)[1]’,’int’)

AS database_id FROM

(SELECT CONVERT(XML, event_data)

    event_data — convert the text field to XML

FROM    — reads the information in the event files

sys.fn_xe_file_target_read_file  

(‘c:\xel\badsplits*.xel’,NULL, NULL, NULL)) theData

CROSS APPLY theData.event_data.nodes

   (‘//event’) theNodes ( event_data ))

SELECT  DB_NAME(database_id) ,COUNT(*) AS total

FROM    qry

GROUP BY DB_NAME(database_id)

   — group the result by database

ORDER BY total DESC

Once you know the database with the most page splits, you need to identify the objects that are causing the problem. This is a bit tricky, but here’s how it works (and there’s a code example at the end).

The event only returns the allocation_unit_id, so you need to convert the allocation_unit_id to the object name that owns these pages. The query to convert the allocation_unit_id to an object name needs to run on the database that contains the allocation units, so we’ll filter the results for this specific database.

We now need to join the result with the DMV sys.allocation_units using the allocation_unit_id field. This DMV has three kinds of allocation units but we are interested only in two of them: type 1 and type 3, which represent data row pages and row overflow pages.

Next, we need to add sys.partitions to the query, and join the container_id field of sys.allocation_units with the hobt_id field in sys.partitions. Finally, we add sys.objects to the query, joining with the object_id field from sys.partitions, to get the name from sys.objects:

WITH qry AS

        (SELECTtheNodes.event_data.value

   (‘(data[@name=”database_id”]/value)[1]’,’int’)

     AS database_id,

theNodes.event_data.value

   (‘(data[@name=”alloc_unit_id”]/value)[1]’,’varchar(30)’)

     AS alloc_unit_id,

theNodes.event_data.value

   (‘(data[@name=”context”]/text)[1]’,’varchar(30)’)

     AS context

        FROM                (SELECT CONVERT(XML,event_data) event_data 

              FROM

        sys.fn_xe_file_target_read_file

               (‘c:\xel\badsplits*.xel’, NULL, NULL, NULL)) theData

   CROSS APPLY theData.event_data.nodes(‘//event’)

               theNodes(event_data) )

SELECT name,context,COUNT(*) AS total

  — The count of splits by objects

FROM qry,sys.allocation_units au, sys.partitions p, sys.objects ob

WHERE qry.alloc_unit_id=au.allocation_unit_id   AND au.container_id=p.hobt_id

AND p.object_id=ob.object_id  AND (au.type=1 or au.type=3)

AND        db_name(database_id)=’MDW’ — Filter by the database

GROUP BY name,context — group by object name and context

ORDER BY name

Now that we know which table is causing the most page splits, you can analyze its indexes to solve the problem.

Although this solution can identify page split problems, you can’t let the session run for a long time, because you’re capturing transaction log activities, which can be too intensive for the server.

Again thanks Red Gate . and  do like the post if you find it useful ..

New Series 2 at a Time

Hi all

i have posted quite a few blogs till now, all of them were individual post except  a short series on New Features of SQL 2016 (Which can be found here  and here ). So i have decided to post a series and that too not one but 2 . I will be publishing 2 series simultaneously from now.

Topic Of first series will be  SQL from Basic.

In this series i will be posting about SQL, All things (Most of them ) about SQL will be covered here from Basics.

Will have post on

  • Introduction to SQL Server
  • SQL Server  Architecture
  • Memory Architecture
  • Installing SQL Server
  • Upgrading the SQL server
  • Configuring SQL Server
  • Managing Database Services
  • SQL Server Security
  • SQL Server Agent
  • Backup & Restore
  • Log- Shipping
  • Database Mirroring
  • Database Snapshots
  • Replication
  • SQL Server Clustering
  • High Availability

For the 2nd Series topic is Managing Environment  As a DBA  

In this series i will be posting about how to manage the the environment AS DBA. How to handle day to day task, how to automate the task, how to use SQL features to ease things. i will be also posting how to automate the daily checklist.
and some basic reports.

so if your interested keep following the blogs. follow me  by subscribing the blog and do click on like if you liked it 🙂

How to find blocked processes

Blocked processes are often a big problem for DBAs, because they’re difficult to monitor. We receive desperate calls saying “Everything is slow!” but before we can do anything a second call arrives saying, “Don’t worry, everything’s OK again!”

Most of the time, this happens because a badly-behaved process blocks other tasks for just a short period. So how do you find this kind of problem?

SQL Server has a server configuration called Blocked Process Threshold. You can set a value, in milliseconds, and SQL Server will generate a report every time a process is blocked by this amount of time.

To configure Blocked Process Threshold, you can use this code

EXEC sp_configure ‘Blocked Process Threshold’, 5

RECONFIGURE

GO

In this example, one blocked process report will be generated every time a process is blocked for more than five seconds. You’ll need to adapt this value to your servers.

There are a few ways to capture blocked process reports:

  • SQL Profiler has an event called ‘Blocked Process Report’ specifically to capture this information.
  • Extended Events also contains a ‘Blocked Process Report’ event.
  • Alerts – it’s possible to generate an alert around blocked process reports and send an email in response.
  • Third-party tools, such as Redgate SQL Monitor.

Here’s the code to create an extended events session to capture the blocked process report:

— Create the session

CREATE EVENT SESSION [Blocked] ON SERVER 

ADD EVENT sqlserver.blocked_process_report 

ADD TARGET package0.event_file

(SET filename=N’C:\xel\blocked.xel’)      — You need to change the path or create the folder

GO 

— Start the session

ALTER EVENT SESSION [Blocked]ON SERVERSTATE = start;

GO

After creating and starting the session, you can query the reports captured by this session. You need to use the sys.fn_xe_file_target_read_file Dynamic Management Function (DMF)  to read the session information.

The main information is returned as an XML field, so you’ll need to extract the information from the XML using XML functions.

Here’s the query to retrieve the report:

SELECT

theNodes.event_data.value   (‘(//blocked-process/process)[1]/@spid’,   ‘int’) AS blocking_process 

,theNodes.event_data.value   (‘(//blocked-process/process/inputbuf)[1]’,    ‘varchar(max)’) AS blocking_text 

,theNodes.event_data.value   (‘(//blocked-process/process)[1]/@clientapp’,    ‘varchar(100)’) AS blocking_app ,theNodes.event_data.value   (‘(//blocked-process/process)[1]/@loginname’,    ‘varchar(50)’) AS blocking_login ,theNodes.event_data.value   (‘(//blocked-process/process)[1]/@isolationlevel’    ‘varchar(50)’) AS blocking_isolation ,theNodes.event_data.value   (‘(//blocked-process/process)[1]/@hostname’,    ‘varchar(50)’) AS blocking_host ,theNodes.event_data.value   (‘(//blocking-process/process)[1]/@spid’,    ‘int’) AS blocked_process ,theNodes.event_data.value   (‘(//blocking-process/process/inputbuf)[1]’,    ‘varchar(max)’) AS blocked_text ,theNodes.event_data.value   (‘(//blocking-process/process)[1]/@clientapp’,    ‘varchar(100)’) AS blocked_app ,theNodes.event_data.value   (‘(//blocking-process/process)[1]/@loginname’,    ‘varchar(50)’) AS blocked_login ,theNodes.event_data.value   (‘(//blocked-process/process)[1]/@isolationlevel’,    ‘varchar(50)’) AS blocked_isolation ,theNodes.event_data.value   (‘(//blocking-process/process)[1]/@hostname’,    ‘varchar(50)’) AS blocked_host 

  FROM ( SELECT CONVERT(XML, event_data) event_data   FROM   sys.fn_xe_file_target_read_file(‘c:\xel\blocked*.xel’,                                            NULL, NULL, NULL) ) theData CROSS APPLY theData.event_data.nodes       (‘//event’) theNodes ( event_data )

Extended Events doesn’t include a way to get notifications about the reports, but you can get them using an alert.

SQL Server has a performance counter called Process Blocked that counts the number of blocked processes according to your Blocked Process Threshold configuration. You can create an alert over this counter and configure the notification to send an email. Here’s the script:

— Create the alert

EXEC msdb.dbo.sp_add_alert

@name =      N’Blocked Process Alert’,

@enabled = 1,     

@category_name = N'[Uncategorized]’,     

@performance_condition =            N’General Statistics|Processes blocked||>|0′

GO

— Add one e-mail notification to one operator

EXEC msdb.dbo.sp_add_notification

@alert_name =      N’Blocked Process Alert’,       

@operator_name = N’Fulano’               — You need to configure the operator first       ,

@notification_method = 1;

GO

And Big Thanks To Red Gate..(NO NO i dont work for RED GATE or nor they sponsor me but they send this kind of mail very often )

Untrusted Check Constraints

Check constraints are useful for more than just verifying the data that goes into your tables. The query optimizer can use them to avoid looking for values that couldn’t possibly be there.

For example, imagine a listprice field with a constraint that keeps the field value over $100. If a query asks for the records with values under $100, the query optimizer can return the data without reading any records, just because of the constraint.

The problem is that the constraint can be disabled, and records can be inserted or updated in the meantime. SQL Server solves this problem by keeping a flag for each constraint, which states if the constraint is trusted or not.

The query optimizer only uses the constraint when it’s flagged as trusted. If it isn’t, the query optimizer won’t use it, and queries that look for values which don’t exist will have a higher cost.

Here’s an example you can try in your test environment, using NORTHWIND as a test database

CREATE TABLE  productTest    (  id INT IDENTITY(1, 1) ,

      productname VARCHAR(50) ,

      listprice DECIMAL       

— Constraint to check listprice field        CONSTRAINT chkprice CHECK ( listprice > 100 )    )

 GO

— import data from products table, but only valid records

INSERT  INTO productTest       

SELECT  productname ,

                unitprice

        FROM   products

        WHERE   unitprice > 100

Let’s check if the constraint is valid:

SELECT  name ,

        OBJECT_NAME(parent_object_id) “table” ,

        is_not_trusted

FROM    sys.check_constraints

valid con1

If you check the execution plans for the following queries, you’ll see they’re different, even though the results are the same.

SELECT  productname ,

        listprice

FROM    productTest

WHERE   listprice = 120.00OPTION  ( RECOMPILE ) 

chkcon2

 SELECT  productname ,

        listprice

FROM    productTest

WHERE   listprice = 50.00OPTION  ( RECOMPILE )

con3

The first one uses a clustered index scan to check if there are records with listprice $120. The second only needs a constant scan, because the constraint that there isn’t a value of $50 in the table.

The problem appears when you disable the constraint, perhaps because you want to import a lot of records that you’re already sure are valid:

ALTER TABLE productTest NOCHECK CONSTRAINT ALL

If you query the is_not_trusted flag again, you’ll notice that the constraint is already marked as not trusted, even before any records were inserted or updated.

con4

It will remain untrusted, even if you enable the constraint again, because any new records haven’t been checked:

ALTER TABLE productTest CHECK CONSTRAINT ALL

To solve this problem, you need to enable the constraint again with the CHECK option. This means the constraint will immediately check all the records in the table:

ALTER TABLE productTest WITH CHECK CHECK CONSTRAINT ALL

valid con1

Be sure to keep your constraints trusted, so the query optimizer can use them to avoid unnecessary reads.

Another Interesting stuff with help of Red-Gate

SQL Server’s statistics

Again another interesting article from RED GATE …

SQL Server’s statistics provide the query optimizer with the information it needs to retrieve table data as efficiently as possible. If the statistics aren’t correct, query performance will be unnecessarily slow.

So how do you check if your statistics are up to date?

One option is to turn on AUTO_UPDATE_STATISTICS, which updates them when the number of row changes in a table reaches 20% of its total rows.

However, this threshold is too high for large tables. For example, a table with 1 million rows will only update statistics after 200,000 changes. Such a high limit can affect query plans and performance.

To change this behavior, you can enable trace flag 2371, which makes the threshold that triggers the AUTO_UPDATE_STATISTICS dynamic.

Once a table reaches 25,000 rows, the required number of updates starts to decrease. A table with 1 million rows, for example, only needs around 3.2% changes to trigger AUTO_UPDATE_STATISTICS.

If you enable this trace flag, it’s still important to identify whether the AUTO_UPDATE_STATISTICS are running often enough. If they’re not, you’ll need to schedule the update using a SQL Agent job.

You can find when the last statistics update happened, and how many row modifications have taken place since, using the sys.dm_db_stats_properties DMV with a few JOINs (download the scripts as a .zip):

SELECT    [schema]

,   [Table]

,   name

,   [Statistic]

,   [last_updated]

,   [rows]

,   [rows_sampled]

,   [Modifications]

,   ( 100 * sp.modification_counter ) / sp.[rows] [Percent]   — Calculates the percent of modifications

FROM      [sys].[stats] AS [s]  INNER JOIN sys.stats_columns sc ON s.stats_id = sc.stats_id                              AND s.object_id = sc.object_id  INNER JOIN sys.columns c ON c.object_id = sc.object_id                              AND c.column_id = sc.column_id  INNER JOIN sys.objects o ON s.object_id = o.object_id  INNER JOIN sys.schemas sch ON o.schema_id = sch.schema_id  OUTER APPLY sys.dm_db_stats_properties              ([s].[object_id], [s].[stats_id])  AS [sp]WHERE   sch.name <> ‘sys’        AND ( sp.modification_counter > 1000              OR ( sp.modification_counter * 100 ) > sp.rows            )   — removes rows with too few modifications

ORDER BY [Percent] DESC ,        last_updated DESC — the result in order of priority

To make this easier, let’s create a user defined function, StatisticsModifications, which retrieves information about all the statistics in the database:

CREATE FUNCTION StatisticsModifications ( )RETURNS TABLEASRETURN   

( SELECT    [schema]

,                [Table]

,                name

,                [Statistic]

,                [last_updated]

,                [rows]

,                [rows_sampled]

,                [Modifications]

FROM        [sys].[stats] AS [s]  INNER JOIN sys.stats_columns sc ON s.stats_id = sc.stats_id                             AND s.object_id = sc.object_id  INNER JOIN sys.columns c ON c.object_id = sc.object_id                             AND c.column_id = sc.column_id  INNER JOIN sys.objects o ON s.object_id = o.object_id  INNER JOIN sys.schemas sch ON o.schema_id = sch.schema_id  OUTER APPLY sys.dm_db_stats_properties([s].[object_id],                                      [s].[stats_id]) AS [sp]    WHERE     sch.name <> ‘sys’    )

Now you can use this function (with some filters) to retrieve the most outdated statistics.

SELECT  [schema]

,        [Table]

,        name

,        [Statistic]

,        [last_updated]

,        [rows]

,        [rows_sampled]

,        [Modifications]

,        ( 100 * modifications ) / [rows] [Percent]FROM    dbo.statisticsmodifications()/* removes rows with too few modifications */WHERE   ( modifications > 1000          OR ( modifications * 100 ) > [rows]        )

ORDER BY [Percent] DESC,  last_updated DESC

With the results of this query, you can identify if your statistics are being updated often enough, or if you need to schedule some extra statistics.

Live Query Statistics

The objective of this post is to introduce the new feature in SQL Server 2016 called Live Query Statistics. This option is very useful in troubleshooting and performance tuning because it helps to pinpoint the problematic area in a query by providing detailed information.

We need to Include Actual Execution Plan and write code to see how query is processed as well as how much IO and time it took to complete the processing. What the Query Processor is doing is not visible to us; we only see the end result. To see what happens at the back end, what actions SQL Server is performing currently, the time spent on each operator, and the flow of the query, we need to use a new feature called Live Query Stats in SQL SERVER 2016.

In  order to demonstrate this feature, I have installed SQL Server 2016 CTP 2  on Windows 8. SQL Server 2016 is compatible only on Windows 8 or higher versions. I have created a test database with a table and few records

First, a simple select query is executed. We have selected Include Actual Execution Plan and code to show time taken to complete the query . Below is the query.

Select   firstname, secondname, email from dbo.testenvironment

Group by mailaddress, firstname, email, secondname

Order by firstname

1

From  the above image you  can see the cpu time and total time taken  to process the query.  The query took 10782 ms to fetch 1000000 records.

The below image shows the actual execution plan with cost of each operator.

2

The above information seems to be quite sufficient but the new feature LIVE QUERY STATS gives lot more information of query processing.

In order to see the functionality of live query stats, This time we enable the option to INCLUDE LIVE QUERY STATS  by  clicking  on the INCLUDE LIVE QUERY STATISTICS button, which is right next to the INCLUDE ACTUAL EXECUTION plan, Then we  execute the same SELECT statement.

Once we execute the query, new tab appears along side the tabs of results and messages called as Live Query Statistics which shows the live information. This information is updated as statement runs . From the below  image , we can see that  query is 44% completed, data scan is 100%  done and SQL sorting is 40% completed.

3

We also see the time taken by each operator. In below image you can see that   SQL server took 3.376s to do 100% table scan and 7.141s to complete 40% of distinct sort. Current transaction  flow is shown with  dotted line and completed transaction with continuous lines.

4

Once query is executed completely another result set pops up with additional information. In this example we have 3 operators namely select, sort and table scan. There is a one  row per operator providing detail information about that operator. The information includes type of operation , physical operator and corresponding logical operator, estimated values of I/O,CPU and Row, total sub tree cost ,estimated executions and output list.

55

6

This additional information is quite useful while analyzing and testing the performance of query as it helps in pin pointing the painful area. One can decide the part of a query  to work on to improve the performance  or to troubleshoot  by checking this information.

This feature can be used in SQL Server 2014  too, but then one need to run the query using SQL server 2016’s SSMS.

The thing to consider while using this feature is that it will impact the  performance of executing query as it will show live details and that’s an additional work processor has to do.

How to find CPU intensive queries ?

HI all

Today received the mail from RED-GATE team on this topic “How to find CPU intensive queries” . It  is an interesting article and with good focus on DMV  and its use to find out expensive query . hence I  thought to share it .

High CPU usage is a common SQL Server problem. Fortunately, you can find queries with high CPU time using the sys.dm_exec_query_stats DMV, which was added in SQL Server 2008.

This DMV keeps performance statistics for cached query plans, so you can find the most expensive queries and query plans on your system.

Let’s have a look at how to do it.

Much of the information is stored in fields called “Total” or “Last” – for example, total_worker_time and last_worker_time – which give you the total cost for all executions of the query, and the cost of the last execution of the query.

total_worker_time can be used to find the query that uses the most CPU time (recorded in microseconds). This isn’t necessarily the most CPU intensive query, though – a high total_worker_time just means the query is being executed often, which may also be a problem.

To find the most CPU intensive query, use last_worker_time. Check the execution_count too, because the query may not be a problem if it only runs once or twice.

To get query itself, you’ll need a CROSS APPLY with the sys.dm_exec_sql_text DMF. This returns the text for the entire batch, however, while the rows in sys.dm_exec_query_stats keeps information about each query.

You can solve this with statement_start_offset and statement_end_offset, which pinpoint the position of the query inside the batch, plus a bit of string manipulation to retrieve the query text.

Last, you need to retrieve the query plan, so you can find the reason for high CPU consumption. You can do so using a CROSS APPLY with the sys.dm_exec_query_plan DMF.

Let’s see how it works – if you want a query with high CPU usage first, try this batch in a test environment  (download the scripts as a .zip):

— Clear the query plan cache (don’t execute this in production!)

DBCC FREEPROCCACHE

GO

— CPU intensive query over AdventureWorks database

SELECT   TransactionId ,

         ProductId ,

         ReferenceOrderId ,

         ReferenceOrderLineId ,

         TransactionDate ,

         TransactionType ,

         Quantity ,

         ActualCost ,

         ModifiedDate

FROM     production.TransactionHistory

ORDER BY modifiedDate DESC

Now, let’s check the most CPU intensive queries in our system:

SELECT  

— using statement_start_offset and

— statement_end_offset we get the query text

— from inside the entire batch

        SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1,

         ( ( CASE qs.statement_end_offset

               WHEN -1 THEN DATALENGTH(qt.TEXT)

               ELSE qs.statement_end_offset

             END – qs.statement_start_offset ) / 2 ) + 1) AS [Text] ,

     qs.execution_count ,

     qs.total_logical_reads ,

     qs.last_logical_reads ,

     qs.total_logical_writes ,

     qs.last_logical_writes ,

     qs.total_worker_time ,

     qs.last_worker_time ,

converting microseconds to seconds

     qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,

     qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,

     qs.last_execution_time ,

     qp.query_plan

FROM sys.dm_exec_query_stats qs

— Retrieve the query text

     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

        — Retrieve the query plan

     CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_worker_time DESC — CPU time

  

List Out All the SQL servers In domain

HI ALL

In One Of the Group the question was asked that How to list all the Servers in which SQL is installed?

It is genuine query. There are many scenario when you need to Find out how much SQL servers are there in the domain ? many a times if a environment is not properly managed and someone takes charge as DBA first thing asked is how much servers we have ?

On The Internet we will find the many tools to do so. Microsoft uses their tool MAP to  discover the servers it is perfect tool and easy to use but many time third party tool is not allowed in those scenario  Power-shell comes to rescue . Powershell  script perfect solution to find all the SQL servers in domain.I searched on the net for that as i am not a developer and powershell is not my forte . i find the below script .

You need to change the domain here . replace corporate with your domain.

once you run this script in powershell , on the screen you will get the list of all the servers in the  domain and the result of ping status . but actual thing you will find in the location  “C:\Users\username” . you will get the excel file named as Yourdomain_Servers_out  this file will list out all the discovered  sql server with details of Server, Instance, Version, Edition .

param

(

      [string]$domain = “corporate”

)

## ==================================================================================

## Title       : Find All Servers in a Domain With SQL

## Description : Get a listing of all servers in a domain, test the connection

##               then check the registry for MS SQL Server Info.

##                 Output(ServerName, InstanceName, Version and Edition).

##                 Assumes that instances of MS SQL Server can be found under:

##                 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names

## Author      : C.Perry

## Date        : 10/2/2012

## Input       : -domain <fully.qualified.domainname>    

## Output      : List of SQL Server names

## Usage       : PS> . FindAllServersWithSQL.ps1 -domain dev.construction.enet

## Notes       :

## Tag           : SQL Server, test-connection, ping, AD, WMI

## Change log  :

## ==================================================================================

# INITIALIZATION SECTION

cls

# Domain context

#$domain = $null

#$domain=”<domainName>”

# Initialize variables and files

$dom = $null

$ErrorActionPreference = “Continue”

$found = $null

$InstNameskey = “SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names”

$RegInstNameKey = $null

$MSSQLkey = “SOFTWARE\Microsoft\Microsoft SQL Server”

$notfound = $null

#Output file goes into directory you execute from

$outfile = “$domain” + “_Servers_out.csv” 

$reg = $null

$regInstance = $null

$regInstanceData = $null

$regKey = $null

$root = $null

$SetupVersionKey = $null

$SQLServerkey = $null

$sbky = $null

$sub = $null

$type = [Microsoft.Win32.RegistryHive]::LocalMachine

“Server, Instance, Version, Edition” | Out-File $outfile

# Domain Initalization

# create the domain context object

$context = new-object System.DirectoryServices.ActiveDirectory.DirectoryContext(“domain”,$domain)

# get the domain object

$dom = [system.directoryservices.activedirectory.domain]::GetDomain($context)

# Debug line #$dom 

# go to the root of the Domain

$root = $dom.GetDirectoryEntry()

#create the AD Directory Searcher object

$searcher = new-object System.DirectoryServices.DirectorySearcher($root)

#filter for all servers that do not start with “wde”

$filter=”(&(objectClass=Computer)(operatingSystem=Windows Server*) (!cn=wde*))”

$searcher.filter = $filter

# By default, an Active Directory search returns only 1000 items.

# If your domain includes 1001 items, then that last item will not be returned.

# The way to get around that issue is to assign a value to the PageSize property. 

# When you do that, your search script will return (in this case) the first 1,000 items, 

# pause for a split second, then return the next 1,000. 

# This process will continue until all the items meeting the search criteria have been returned.

$searcher.pageSize=1000

$colProplist = “name”

foreach ($j in $colPropList){$searcher.PropertiesToLoad.Add($j)}

# get all matching computers

$colResults = $searcher.FindAll()

# PROCESS Section

# interate through all found servers

foreach ($objResult in $colResults)

{#Begin ForEach

    $objItem = $objResult.Properties

    [string]$Server=$objItem.name

    Try

    {

        IF (test-connection -computername $Server  -count 1 -TimeToLive 4 -erroraction continue -quiet)

        {#IfConnectionFound       

            $found = $Server + ” is pingable”

            #echo $found

            $InstanceNameskey = “SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names”

            $MSSQLkey = “SOFTWARE\Microsoft\Microsoft SQL Server”

            $type = [Microsoft.Win32.RegistryHive]::LocalMachine

            $regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $Server)

            $SQLServerkey = $null

            $SQLServerkey = $regKey.OpenSubKey($MSSQLkey)

            # Check to see if MS SQL Server is installed

            IF ($SQLServerkey)

            {#Begin IF $SQLSERVERKEY    

                #DEBUG Write to Host “Sub Keys”

                #Write-Host

                #Write-Host “Sub Keys for $MSSQLkey”

                #Write-Host “——–“

                #Foreach($sbky in $SQLServerkey.GetSubKeyNames()){$sbky}

                $Instkey = $null

                $Instkey = $regKey.OpenSubKey($InstanceNameskey)

                # Check to see in chargeable Instances of MS SQL Server are installed

                IF ($Instkey)

                { 

                    #DEBUG Write-Host “Values” of SubKeys

                    #Write-Host

                    #Write-Host “Sub Keys for $InstanceNameskey”

                    #Write-Host “——“

                    #Foreach($sub in $Instkey.GetSubKeyNames()){$sub}

                    foreach ($regInstance in $Instkey.GetSubKeyNames())  

                    {  

                        $RegInstNameKey = $null

                        $SetupKey = $null

                        $SetupKey = “$InstanceNameskey\$regInstance”

                        $RegInstNameKey = $regKey.OpenSubKey($SetupKey)

                        #Open Instance Names Key and get all SQL Instances

                        foreach ($SetupInstance in $RegInstNameKey.GetValueNames())  

                        {  

                            $version = $null  

                            $edition = $null

                            $regInstanceData = $null

                            $SetupVersionKey = $null

                            $VersionInfo = $null

                            $versionKey = $null

                            $regInstanceData = $RegInstNameKey.GetValue($SetupInstance) 

                            $SetupVersionKey = “$MSSQLkey\$regInstanceData\Setup”

                            #Open the SQL Instance Setup Key and get the version and edition

                            $versionKey = $regKey.OpenSubKey($SetupVersionKey)

                            $version = $versionKey.GetValue(‘PatchLevel’) 

                            $edition = $versionKey.GetValue(‘Edition’)   

                            # Write the version and edition info to output file

                            $VersionInfo = $Server + ‘,’ + $regInstanceData + ‘,’ + $version + ‘,’ + $edition  

                            $versionInfo | Out-File $outfile -Append 

                        }#end foreach $SetupInstance

                    }#end foreach $regInstance

                }#end If $instKey

                ELSE

                {#Begin No Instance Found

                    $found = $found + ” but no chargable instance found.”

                    echo $found

                }#End No Instance Found

            }#end If $SQLServerKey

        }#end If Connectionfound

        ELSE

        {#ELSE Connection Not Found

            $notfound = $Server + ” not pingable”

            echo $notfound

        }

    }#endTry

    Catch

    {

         $exceptionType = $_.Exception.GetType()

        if ($exceptionType -match ‘System.Management.Automation.MethodInvocation’)

        {#IfExc

             #Attempt to access an non existant computer

             $Wha=$Server + ” – ” +$_.Exception.Message

               write-host  -backgroundcolor red  -foregroundcolor Black $Wha   

        }#endIfExc

        if ($exceptionType -match ‘System.UnauthorizedAccessException’)

          {#IfEx

            $UnauthorizedExceptionType = $Server + ” Access denied – insufficent privileges”

            # write-host “Exception: $exceptionType”

            write-host -backgroundcolor red “UnauthorizedException: $UnauthorizedExceptionType”

           }#endIfEx

        if ($exceptionType -match ‘System.Management.Automation.RuntimeException’)

            {#IfExc

               # Attempt to access an non existant array, output is suppressed

               write-host  -backgroundcolor cyan  -foregroundcolor black “$Server – A runtime exception occured: ”   $_.Exception.Message; 

            }#endIfExc

    }#end Catch

}#end ForEach servers in domain

#number of servers

$colResults.count

enjoy the script and comments are always welcome