Problems with Parallelism

Sometimes the SQL server can  benefit of having multiple processors to split a job between them, and other time it causes a very big problem.

A lot of guys have written some very useful blogs about that, but when I visit customers then I start to investigate what there waittypes are.

Please read this post, to read a little bit about how to identify waittypes.

If you see CXPackets, then you must start to be clever and investigate a lot, because there can be many solutions to the waittype CXPackets.

What I would do is, that I would try to set:

sp_configure ‘show advanced options’, 1
reconfigure
go

sp_configure ‘cost threshold for parallelism’, 25
reconfigure
go

sp_configure ‘max degree of parallelism’, 4
reconfigure
go

What this means is:

  1. First we turn on advanced options, so that we can work with the two other options.
  2. Then I set cost threshold for parallelism to 25. It could be 30, 20 e.g. I think that it is 5 per default. It means basically: “In terms of cost to run the query in parallel. How high should it be, before we consider parallelism. the higher, the fewer queries is run by using parallelism.
  3. Finally we set the max degree of parallelism to 4. It could be 2, 3, 8. 0 means unlimited and 1 means no parallelism. I would set it to half of the processors that I have available.

I know that you should do a lot more investigation, but often you have a lot of other work to do, and you might not be so good at performanceoptimization, so the pragmatic approach is to try and test.

Let me point out, that a high CXPackets is not always a problem, but if you e.g. look at your processors and see a high utilization together with cxpackets, then you might try the change in this blog.

 

 

C2 Auditing–Enable

C2 auditing is easily enabled, by writing the following SQL Statement and then restart the instance:

sp_configure ‘show advanced options’, 1
reconfigure

sp_configure ‘c2 audit mode’, 0
reconfigure

When that is done, then you can easily find information that are captured by the C2 auditing. Go to your datafolder of the SQL Server, and find the audittracefiles (ending on .trc).You can then open them in SQL Server Profiler or you can use the following code.

SELECT *
FROM ::fn_trace_gettable(
‘D:\SQL2012\MSSQL11.MSSQLSERVER\MSSQL\DATA\audittrace20121110183153.trc’, default
)
GO

But I would recommend opening it up in SQL Server Profiler.

Setting up a database snapshot

A databasesnapshot is a point in time view of a given database. Imagine that you have the Adventureworks database and you want to have a view of the database pr. one given date and time. In that situation you want a database snapshot of the Adventure works database.

The syntax is:

Create database adventureworks_yyyymmdd on
(Name=adventureworks,  Filename=’d:\sqltests\Snapshots\adventureworksSnapshot.sss’)
as snapshot of adventureworks

What you have created now is a databasesnapshot that you can query like all other “databases”, the only difference is that the size of this database is 0.

When you create a snapshot, then it is empty until a page is changed. Before a page is changed, then the original page is moved to the snapshot and then the page is changed.

So when you query a snapshot, then the query first check if a page is changed since the snapshot. If so, then it is taken from the snapshot, otherwise it is taken from the original databasefile.

Setting up Transparent Data Encryption

Below I have posted all my code for setting up Transparent Data Encryption from scratch. Feel free to use it, but please note that you do it at your own risk.

Open in Management studio and run one step at a time.

If any questions, then please contact me.


— This script shows you how to setup Transparent data encryption
— Remember to backup all you keys and certificates

— NOTE: YOU RUN THIS SCRIPT ON YOUR OWN RISK

— Created by Søren Agerbo Frydensbjerg, 2011
— Feel free to use the examples and share them with your network.
— Just remember to give me some credit

use master
— TDE
— 1. Create a master database key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘1234’;

— 2. Create a certificate
CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certifikat’;

— 3. BACKUP Certificate
BACKUP CERTIFICATE tdeCert TO FILE = N’c:\sj\cert.sjback’

   WITH PRIVATE KEY (

         FILE = N’c:\sj\Key.sjback’,

         ENCRYPTION BY PASSWORD = ‘1234’);

use AdventureWorks
— 4. Create Database Encryption Key
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert

— 5. Enable encryption
ALTER DATABASE adventureworks SET ENCRYPTION ON

— 6. Follow progress
SELECT db_name(database_id), encryption_state
FROM sys.dm_database_encryption_keys

What are table hints and how are they used

A question that I often get is what a table hint is and how it is used?

Well, basically when you write some code in TSQL, then the SQL Server needs to translate it to something it can execute.

That is a multistep process, and the last steep: is to optimize the execution. Basically the SQL server says: “I can now run the statement, but how can I run it in the best possible way?”. This is a costbased process, where the Query optimizer (that is the name of the last step) calculates a cost for different execution plans, and then it chooses the one with the lowest cost.

So if you e.g. have a statement like:

select *
from Person.address
where City=’Bothell’

Then the SQL Server finds what it think is an optimal plan of execution. It is not necessarily the best plan, but it is a good plan.

For the above query, the executionplan looks like:

image

The executionplan is read from right to left, and for this moment it does not matter what it means. the important think is that with tablehints you can override the way the query is executed.

Try the following:

select *
from Person.address with (index(1))
where City=’Bothell’

The executionplan looks like this:

image

So by adding the table hint WITH (index(1)), then you can change the way the sql server executes your statement.

What else does table hints do?

Well, it does a lot of things, and you can read about it here, but one thing that I would like to show is using tablehints in a practical world. If you have a system with a lot of users and you want to extract some data from a table that are heavily used, then you could use the table hint with (NOLOCK).

It means that the query does not take any locks and does not care about any locks taken on an object. In other terms: This query will execute no matter if other users are updating the table or not AND it will not wait on them to complete.

The risk is that you get inconsistent data, but the benefit is that you get data!! This is very useful for reporting, where e.g. you need data that are out of the range of data that are updated.

Examining waits – some nice TSQL

I am not a fan of borrowing from others, but with this blog I would like to show you a cool SQL-statement that can be used to understand the data from the dynamic management view sys.dm_os_wait_stats.

This views gives you information about waits on the SQL Server. It gives you information about waits since the last restart of the instance or since you last flushed the data with

dbcc sqlperf(‘sys.dm_os_wait_stats’, clear)

If you write the following SQL statement, then you get the result from the view, and you see a lot of rows and columns, that basically are very difficult to understand.

select *
from sys.dm_os_wait_stats

Paul Randal from SQL Skills, has published a script on his blog, where he has included his brilliant understanding of how the SQL Server works. Three things he has done are:

  1. Removed waits that are shown in the view that causes no problems (in a normal situation).
  2. Written the code so that it only identifies the 95% of the waits (the most important waits).
  3. Created an output that is understandable.

The TSQL looks like:

WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms – signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
‘CLR_SEMAPHORE’, ‘LAZYWRITER_SLEEP’, ‘RESOURCE_QUEUE’, ‘SLEEP_TASK’,
‘SLEEP_SYSTEMTASK’, ‘SQLTRACE_BUFFER_FLUSH’, ‘WAITFOR’, ‘LOGMGR_QUEUE’,
‘CHECKPOINT_QUEUE’, ‘REQUEST_FOR_DEADLOCK_SEARCH’, ‘XE_TIMER_EVENT’, ‘BROKER_TO_FLUSH’,
‘BROKER_TASK_STOP’, ‘CLR_MANUAL_EVENT’, ‘CLR_AUTO_EVENT’, ‘DISPATCHER_QUEUE_SEMAPHORE’,
‘FT_IFTS_SCHEDULER_IDLE_WAIT’, ‘XE_DISPATCHER_WAIT’, ‘XE_DISPATCHER_JOIN’, ‘BROKER_EVENTHANDLER’,
‘TRACEWRITE’, ‘FT_IFTSHC_MUTEX’, ‘SQLTRACE_INCREMENTAL_FLUSH_SLEEP’,
‘BROKER_RECEIVE_WAITFOR’, ‘ONDEMAND_TASK_QUEUE’, ‘DBMIRROR_EVENTS_QUEUE’,
‘DBMIRRORING_CMD’, ‘BROKER_TRANSMITTER’, ‘SQLTRACE_WAIT_ENTRIES’,
‘SLEEP_BPOOL_FLUSH’, ‘SQLTRACE_LOCK’)
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) – W1.Percentage < 95; — percentage threshold
GO

Wow… Would you have figured that code out your self? I wouldn’t, but running it is brilliant. the output looks like:

image

What I can see from here, then the waittype that my topwaittype is IO_COMPLETION, and if I want to then, I could start to investigate what that means and how to solve it. But here is the first warning!!!!!

Do NOT jump to any hasty conclusions. Always be clever and think one step ahead, and don’t conclude: I am waiting for my disk system so I must buy a faster disk system. YOU WILL NEVER SUCCEED using money. You will succeed if you use your brain.

So if you have waittypes that indicates that the disksystem is under preassure, then find out why it is under preassure.

How to improve the TSQL SCRIPT? Basically I would add CREATE VIEW getWaits AS in front of the TSQL. Paul Randal has in his teaching on PluralSight suggested that you put the information into a table and then track it over time. Set it up by writing:

select GETDATE() as capturedate, *
into waitHistory
from getWaits

and then create a SQL Server Job where you write:

insert into waitHistory
select GETDATE() as capturedate, *
from getWaits

If you want to read Paul Randals orignal post, then click here.

Extended events– the objects

Before reading this blog, it might be a good idea to read the blog Your First extended event if you are not familiar with Extended events.

A friend of mine asked me (for some month ago if there was an easy way to figure out what events you can use, and that give me the idea of writing this blogpost (and today I have the time for it).

What events can we track? Well, when we setup an event session we write something like:

Create event session testSQL on server
add event sqlserver.sql_statement_completed

The text written in red is the event and the package the event belongs to. If we look at the package (sqlserver), then you can see the current packages by writing:

select name, description, guid
from sys.dm_xe_packages

It gives you the name, description and the guid for the package.

The main source of information is the view sys.dm_xe_objects, and you can utilize that by writing

select *
from sys.dm_xe_objects t1

As you can see, there is an object_type column, and it has multiple values (7 in 2008R2), but the once we are interested in now are:

  1. event
  2. target
  3. action

Combining that with the view sys.dm_xe_packages, then we get an SQL statement like the following to get the events we can use:

select pck.name as package, obj.name as eventName, obj.description
from sys.dm_xe_objects AS obj
inner join sys.dm_xe_packages as pck on obj.package_guid=pck.guid
where object_type = ‘event’
order by pck.name, obj.name

So now you know what to write when you want to create an event session for a given event. If you are in doubt about what events you can use, then just google the event name and Extended event.

If we look at the action part of an event session, then it could look like:

Create event session testSQL on server
add event sqlserver.sql_statement_completed
(    action (sqlserver.sql_text)
where sqlserver.database_ID=30)

As you can see, then we have an action, where we will capture the sql_text for a database with the id = 30.

You can find the actions by replacing object_type=’event’ with object_type=’action’ in the above query:

select pck.name as package, obj.name as actionName, obj.description
from sys.dm_xe_objects AS obj
inner join sys.dm_xe_packages as pck on obj.package_guid=pck.guid
where object_type = ‘action’
order by pck.name, obj.name

That’s very easy Smile.

The second last step we need to investigate when setting up an eventsession is the target:

Create event session testSQL on server
add event sqlserver.sql_statement_completed
(    action (sqlserver.sql_text)
where sqlserver.database_ID=30)
add target package0.asynchronous_file_target
(set filename = N’C:\xetest\SQLTEST.xml’
, metadatafile= N’C:\xetest\SQLTESTmeta.xml’
)
with (max_dispatch_latency = 1 seconds)

As you can see, I have a target called package0.asynchronous_file_target, and I can find these targets by writing:

select pck.name as package, obj.name as actionName, obj.description
from sys.dm_xe_objects AS obj
inner join sys.dm_xe_packages as pck on obj.package_guid=pck.guid
where object_type = ‘target’ and pck.name=’package0′
order by pck.name, obj.name

So the final part is now to find out how we can setup the WITH clause for an event session, and that is very easy. Read the document from Microsoft.

Your first extended event

Extended events are used to track down what happens in the SQL Server, and you can very easily setup an  extended event to e.g. montioring what SQL Statements a user is executing, what waits there are on the servers and e.g.

I recommend that you try this demo first, and then read the webpages from MSDN that introduces Extended events (http://msdn.microsoft.com/en-us/library/bb630354%28v=sql.105%29.aspx).

Learning Extened events are quite easy when you have an SQL server 2008 (and in 2012 it is getting easier again). It is a three step process:

  1. Setting up your first extened event session.
  2. Understanding the output
  3. Learning what events and actions you can use.

But let me start with a warning. Extened events are lightweight and very efficent, but if you set it up wrongly and try to catch to many events and outputs, then you can basically drain the performance on the SQL Server.

So lets try to get working (you need to have the adventureworks database installed, and you can download it from here. You..  also need an SQL Server 2008 or never).
OK. Lets start to setup an extended event that catches all sql statements in the adventureworks database.

  1. Open a command promt (go to start and type CMD).
  2. Write MD c:\XEtest hit enter

Extened events requires a folder to store the XML files that are the output (at least we choose to output to a file in this example). The service account that has started the SQL Server must have access to write to that folder.

We want to create an extended event called testSQL and therefore we must make sure that we drop it if it exists on the server (so that we can set it up). Open Microsoft Sql Server Management studio and start a new query. Write:

use master
go
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’testSQL’)
DROP EVENT SESSION testSQL ON SERVER

This makes sure that we can setup a new SQL Extended event called testSQL, since we have just dropped the old one (if it existed).

Before we can setup the event session, then we must find the ID of the database that we want to track the SQL statements from. Execute the following statement and write down the number that it returns (for me it is 30)

select DB_ID(‘adventureworks’)

Now, lets try to setup the new extended event, and first of all we must decide what event to track, what action to setup and where to output. Let’s write the code

Create event session testSQL on server
add event sqlserver.sql_statement_completed
(    action (sqlserver.sql_text)
where sqlserver.database_ID=30)
add target package0.asynchronous_file_target
(set filename = N’C:\xetest\SQLTEST.xml’
, metadatafile= N’C:\xetest\SQLTESTmeta.xml’
)
with (max_dispatch_latency = 1 seconds)

 

The words I have written in red, is the important words, and let me try to explain.

First of all we Create an event session, and the syntax is CREATE EVENT SESSION xxxxx ON SERVER, where xxxxx is the name.

To create the event session, we must add an EVENT to track, and in this situation it is and event called sqlserver.sql_statement_completed. In a later post I will go through some of the cool events that can be used.

When the event is added, then we must add a target, and in this situation we just write to a file (I will also go through this in a later post).

Finally we can set some options with the with clause, and in our situation we have set a max_dispatch_latency on one second, which basically means that we store what we have cached in memory for max of 1 second.

If you have set it up correctly, then you now can start the event:

alter event session testSQL on server
State = Start

Now the event is starting, and you can try to write some sql statements:

use AdventureWorks
go

select *
from Person.Address

when you have done that, then you can stop the eventsession

alter event session testSQL on server
State = Start

 

and we are ready to see what we have captured.

select DATA as xmldata
, Data.value(‘(/event/action[@name=”sql_text”]/value)[1]’, ‘varchar(max)’)
from (
select CONVERT(xml, event_data) as data
from sys.fn_xe_file_target_read_file(
‘c:\xetest\sqltest*.xml’
, N’c:\xetest\sqltestmeta*.xml’
, null
, null
)
) entries

If you run this script, you will see two columns, one with some xml that you can look at, and one with the statement that has been executed.

And that is basically it. That is how you setup Extended events. But of course there are more to it.

  1. What events can we capture
  2. What actions can we do
  3. Which targets can we send the output to
  4. What options can we use.
  5. How can we format the output.

Especially 5 is very interesting and difficult, but basically now you know how to setup an extended event.

 

 

 

 

Merge replication – a beginners guide

If you want to share data between multiple databases, then replication could be an alternative, and there are four types of replication you should know to be able to choose the best option:

  1. Snapshot replication
  2. Transactional replication
  3. Peer-to-peer replication (a kind of transactional replication)
  4. Merge replication

In this video I will show you how to setup Merge replication.

 

[youtube=http://youtu.be/VxeUMlLW4rc]

Peer to peer replication – a beginners guide

If you want to share data between multiple databases, then replication could be an alternative, and there are four types of replication you should know to be able to choose the best option:

  1. Snapshot replication
  2. Transactional replication
  3. Peer-to-peer replication (a kind of transactional replication)
  4. Merge replication

In this video I will show you how to setup Peer to Peer replication.

[youtube=http://www.youtube.com/watch?v=Ld_bwHe8kcQ]