SQL Performance er blogs omkring at få fart på din SQL server

Expert Performance Indexing for SQL server 2012

Expert Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012

One more book review 🙂 (It’s really nice having a long holiday to read books and relax).

This is also a “Must have book”; simply because Indexing is so important for good performance. Indexing is always a trade off between better read access to data versus storage and degrade in write performance, and I think this book gives you the tools to support your decisions when you are working with the part of performance that are related to indexing.

No doubt that my favorite chapter is the chapter about Myth’s when it comes to indexes. All persons working with SQL servers should read that chapter.

With this book you first get an insight into what indexes are, what types of indexes there is and what statistics are (statistics is critical to know in relation to performance optimization).

The book also describes some of the tools that are important to know when you want to work with indexes (including the most important DMO’s).

Finally I also like that the book gives you some good advices (they call it best practice and strategies) on how to write queries and how to use indexes.

Overall a very competent book (I think all books from APress is competent).

On the negative side, then it is easy to see that this book is an upgrade from the same book on SQL Server 2008, and one of the places where that is seen is when they discuss Column store indexes. It is few pages and it is absolutely NOT Expert knowledge what they give you.

I am not sure it is a negative thing, but the book is called “Expert performance….”. I think this book can be read and should be read by anyone that has been working with the Microsoft SQL server 2012 for more than 6 month to 1 year. It could be a good starting point for going deeper into the Microsoft SQL server.

I think this book is worth buying and I give it 3 out of 5 stars.

 

Executionplan with crazy percentages

I got the following executionplan from a customer (IT IS AN ESTIMATED EXECUTIONPLAN)

2013-12-09 00_40_56-10.5.1.51 - Remote Desktop Connection

and as you can see, then the sum of the percentages is more than 100%. That should be impossible, but when I started to look into it, the reason was quite obviously:

The shown executionplan shows the complete SQL statement, but only a part of it is executed, and the sum of the executed part is 100%. So lets look into the SQL statement (you must have the Adventureworks2008R2 installed)

if (select count(*)
from Person.Person)=1234
begin
	select *
	from person.person
	cross join
	sales.SalesOrderDetail
end

There are 19972 rows in the Person.Person table, so we will never execute the cross join between the begin and end. Therefore the 100% in the executionplan will only be calculated on the Select count(*) from Person.Person.

Now we know how much CPU+IO the executed parts takes (hover over the Cond With Query Cost operator). On my server with my databsae it will take 0.0787.

If I hover over the Nested Loops with a cost of 12866355% that wil NEVER be executed, then the operator cost is approx. 10127,94. Now the formula is 100*10127,94 /0.0787 which approximately equals 12866355%.

So that is the reason for the strange percentages: 100% equals the executed part and then we divide the operator cost for each “Non Executed” operators with the total cost for the executed parts.

What happens if the IF was true so that the cross join will be executed?

Well, then we get the same result.

If I execute the query then I get the following:

 

2013-12-09 00_52_50-10.5.1.51 - Remote Desktop Connection

Strange. But basically I think I understand why the estimated executionplan acts as it does.

Imagine a SQL Statement like this:

if (select count(*) from Person.Person)=19972
begin
	select *
	from person.person
	cross join
		sales.SalesOrderDetail
end
else
begin
	select *
	from person.person
	cross join
		sales.SalesOrderDetail
end

Now the Query got more complicated, but if the sum should get 100% no matter what, then it would also be wrong because we will never execute all code, so I like to conclude:

  1. The code that the Optimizer is 100% sure to execute will calculate 100%.
  2. All other path’s of the code will related to the “100% sure exetued 100%” so that you have an idea about how long time it takes.

Please challenge me on this.

Waits and queues with a practical approach

Earlier I have been writting about Waits and queues and how to use that approach to find out about performance issues. Waits and queues does not give you the answers, but it guides you to the area(s) where there might be a problem.  You can read my post about waits and queues here.

You must have read and understood my blog about waits and queues before you continue with this blog.

With this post I want to show you how to setup an environment where you can see changes in waits and queues and you can do some analysis. If time permits, then I will post a blog later about how to dive into the waits and see what caused the wait.

What I want to achieve is a database where I capture the accumulated waits and queues every hour (half hour or every day, depending on what you prefer).

So, what I need is:

  1. Setup a database.
  2. Create a table to hold the data.
  3. Create a stored procedure to collect the data.
  4. Setup a SQL Server Agent job to automate the collection.
  5. Create a view to see the capture times.
  6. Create a stored procedure to report the data

Before we start, then note that the script to show the waits is done by Paul Randal, www.sqlskills.com.

1. Setup a database

First we must setup the database, and I suggest that you do the following (remember to change physical path):

USE [master]
GO

CREATE DATABASE [Logging] ON  PRIMARY
( NAME = N'Logging', FILENAME = N'd:\SQL\Data\Logging.mdf'
         , SIZE = 524288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 131072KB )
 LOG ON
( NAME = N'Logging_log', FILENAME = N'e:\SQL\LOG\Logging_log.ldf'
      , SIZE = 262144KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

2. Setup a table to hold the data

Now we must have a table to store the data that we collect every hour, and I would recommend the following table. It can be optimized, so this is a basic table:

USE [Logging]
GO

CREATE TABLE [dbo].[wait_statistics](
    [CaptureTime] [datetime] NOT NULL,
    [WaitType] [nvarchar](60) NOT NULL,
    [Wait_S] [decimal](14, 2) NULL,
    [Resource_S] [decimal](14, 2) NULL,
    [Signal_S] [decimal](14, 2) NULL,
    [WaitCount] [bigint] NOT NULL,
    [Percentage] [decimal](4, 2) NULL,
    [AvgWait_S] [decimal](14, 4) NULL,
    [AvgRes_S] [decimal](14, 4) NULL,
    [AvgSig_S] [decimal](14, 4) NULL
) ON [PRIMARY]

3. Create a stored procedure to collect the data

So, now we are ready to fill in some data, and for that we use a stored procedure. The reason for that, is that the wait types we are filtering (that we don’t want to see) changes from version to version, and therefore it would be nice to have a stored procedure where we can easily change what to filter and not.

USE [Logging]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[collect]
as
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')
    )
insert into wait_statistics
SELECT
    getdate() as CaptureTime
    ,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,
    case when w1.WaitCount<>0 then
        CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4))
    else
        0
    end AS AvgWait_S,
    case when w1.WaitCount<>0 then
        CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4))
    else
        0
    end AS AvgRes_S,
    case when w1.WaitCount<>0 then
        CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4))
    else
        0
    end 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

GO

It is very important to know what version of SQL Server you are using, because then you can decide which wait types you are going to exclude in the line WHERE WAIT_TYPE NOT IN (xxx).

4. Setup a SQL Server Agent Job to automate collection

Now when we have a stored procedure to collect the data it is very easy to setup a sql server agent job to automate collection.

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'Collection',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @delete_level=0,
        @description=N'This job collects wait statistics.',
        @category_name=N'Database Maintenance',
        @owner_login_name=N'YOURUSERID', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Collection', @server_name = N'YOURSERVERNAVN'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Collection', @step_name=N'Wait_statistics',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_fail_action=2,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'exec collect',
        @database_name=N'Logging',
        @flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Collection',
        @enabled=1,
        @start_step_id=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @delete_level=0,
        @description=N'This job collects wait statistics.',
        @category_name=N'Database Maintenance',
        @owner_login_name=N'YOURUSER',
        @notify_email_operator_name=N'',
        @notify_netsend_operator_name=N'',
        @notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'Collection', @name=N'EveryHour',
        @enabled=1,
        @freq_type=8,
        @freq_interval=1,
        @freq_subday_type=8,
        @freq_subday_interval=1,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20130312,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

5. Create a view to see the capture times

So.. Let’s see if we can create a view that can show us all capture times, because we need them as inputparameters for the stored procedure that will return the figures.

use Logging
go

create view CaptureTimes
as
select distinct capturetime
from wait_statistics

6. Create a stored procedure to report the data

So, now we only need to create a stored procedure to capture the data. That is very easy, and when that is done, you can start monitoring the waits and queues.

USE [logging]
GO

/****** Object:  StoredProcedure [dbo].[InternalDezision_reports_waits_output]    Script Date: 03/12/2013 09:50:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[ReportWaits]

@endtime        datetime,
@starttime        datetime
as

begin try
    drop table #tmptable
end try
begin catch
end catch

select WaitType
        , convert(numeric(38,10), Wait_S - isnull((select Wait_s
from wait_statistics i1 where t1.WaitType=i1.WaitType
and i1.CaptureTime=@starttime),0)) as Wait_s
        , Resource_S - isnull((select Resource_s from wait_statistics i1
where t1.WaitType=i1.WaitType and i1.CaptureTime=@starttime),0) as Resource_s
        , Signal_S - isnull((select Signal_s from wait_statistics i1
where t1.WaitType=i1.WaitType and i1.CaptureTime=@starttime),0) as signal_s
        , WaitCount - isnull((select WaitCount from wait_statistics i1
where t1.WaitType=i1.WaitType and i1.CaptureTime=@starttime),0) as waitcount
        , convert(numeric(38,10), 0.00) as percentage
        , convert(numeric(38,10), 0) as avg_Wait_S
        , convert(numeric(38,10), 0) as avg_Resource_S
        , convert(numeric(38,10), 0) as avg_signal_s
into #tmptable
from wait_statistics t1
where CaptureTime=@endtime
order by CaptureTime desc

-- Lets delete
delete #tmptable
where waitcount=0

-- Let's find the wait
declare @wait_s numeric(38,10)
select @wait_s=SUM(wait_s) from #tmptable

update #tmptable
set percentage=wait_s/@wait_s *100
    , avg_Wait_S=wait_s/waitcount
    , avg_resource_S=resource_s/waitcount
    , avg_signal_S=signal_s/waitcount

select *
        , @starttime as starttime
        , @endtime as endtime
        , datediff(s, @starttime, @endtime) as PeriodInSeconds
        , 100*wait_s/datediff(s, @starttime, @endtime) as ShareOfPeriod
from #tmptable
order by percentage desc

7. Monitoring – An example

Let’s see how it works:

First you try to find two dates you can compare, and for that you simply write:

select *
from capturetimes

The result in my case will be:

image

So I have two capturetimes to compare and therefore I can write:

USE [Logging]
GO

EXEC    @return_value = [dbo].[ReportWaits]
        @endtime = N'2013-03-12 09:49:19.373',
        @starttime = N'2013-03-12 09:41:59.793'

In my case (it is a very small timeperiod), the result is something like this:

image

As you can see, then on my server it is BackupIO that has caused 97.4877% of the waits.

What is missing in a lot of other blogs you are reading, is the column ShareOfPeriod. Here you can see that BackupIO has caused 98.865909, but the main thing is: With this column, you can see how long time of the capturetime we have been waiting. There could be a lot of theoretical discussions about this figure, and if it is good or bad, if we can use this column when we have multiple processor and so on.

BUT!!! It is better than just knowing: It is from the last restart or DBCC. When we collect each hour, we get a lot of flexibility to narrowing down where the problem is.

You can do a lot of optimization and make it easier to run the stored procedure without having to find the capturetimes, but… there should also be something for you to do.

Unique identifer or INT as clustered index

Today I was asked about what is best to use for a clustered index and a primary key in a table created on e.g. SQL Server 2008 R2. Is it a Unique Identifier or an Int.

As usual my answer was… It Depends, but if we only look at index maintenance and other performanceissues, then I would always prefer an Int instead of an Unique Identifier.

So what else can we to than try to test different scenarios.

First we create a database.

 

use master
go

-- First let's see if the database
-- already exists and if we have to delete it.
if DATABASEPROPERTYEX('keyTest','Version') is not null
begin
    drop database KeyTest
end

-- Lets create the database
Create database keyTest
go

use keyTest
go

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

First step is to test if the database KeyTest already exists. If it does then we drop it and create it again.  Then we switch context to the keyTest database.

Now, lets create two tables that have the same data, but a clustered index that are build on different datatypes. We implement the clustered index by using the primary key keyword (there are a big difference between a clustered index and a primary key, but if we create a table with the primary key keyword, then a clustered index is also created.

-- Lets create the two tables
Create table IdentifierTBL
        (    ID        uniqueidentifier        primary key
             default newID()
            , Postalcode        char(4)
            , Name                char(50)
        )

Create table IntTBL
        (    ID        int    identity        primary key
            , Postalcode        char(4)
            , Name                char(50)
        )

The two tables has the same columns, but in the first table the ID is a uniqueidentifier, that are 16 bytes long and in the first table it is an int, that are 4 bytes long.

Now let try to insert 10.000 rows in each of the tables.

 

-- Let's insert some data
insert into IdentifierTBL values (newid(), '1234', 
Replicate('A', 50))
go 10000

insert into IntTBL values ('1234', Replicate('A', 50))
go 10000

Thats it. We have two tables with 1000 rows. The only test we want to carry out now is to see the fragmentation and how many pages they take up.

select    t1.object_id
        , t2.name
        , t1.avg_fragmentation_in_percent
        , index_level, page_count
        , avg_record_size_in_bytes
from sys.dm_db_index_physical_stats(DB_ID(), 
       null, null, null, 'Detailed') t1
    inner join sys.objects t2 on t1.object_id=t2.object_id
where t2.name in ('IdentifierTBL', 'IntTBL')

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The result looks like this:

image

We can see that both tables consists of two levels, a leaf level and a root level. We can see that if we use an Unique identifier, then fragmentation is approx. 99%, and when we use an integer, then it’s approx. 2%.

We can also see that the table with an Int as the clustered key, takes up 92 pages and the average record size is 65 bytes. The same figures for the table with the unique identifier is 154 pages and 77 bytes in average recordsize.

But it does not stop here. What if we create a nonclustered index on the postalcode. What will happen to our physical structures?

USE [keyTest]
GO
CREATE NONCLUSTERED INDEX
        [Nonclustered] ON [dbo].[IntTBL]
(
    [Postalcode] ASC
)

CREATE NONCLUSTERED INDEX
        [Nonclustered] ON [dbo].[IdentifierTBL]
(
    [Postalcode] ASC
)

 

Lets see the characteristica

 

select    t1.object_id
        , t2.name
        , t3.name
        , t1.avg_fragmentation_in_percent
        , index_level, page_count
        , avg_record_size_in_bytes
from sys.dm_db_index_physical_stats(DB_ID(), null, 
null, null, 'Detailed') t1
    inner join sys.objects t2 on t1.object_id=t2.object_id
    inner join sys.indexes t3 on t1.object_id=t3.object_id 
and t1.index_id=t3.index_id
where t2.name in ('IdentifierTBL', 'IntTBL')
and t3.name='NonClustered'

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

The result is

image

What I am interested in showing you is the page count and the record size. As you can see, then the index is much bigger with a unique identifier (it uses 36 pages compared to the 20 pages). That is because the recordsize is  24 bytes compared to the 12 bytes that are used in the IntTBL. The difference is 12 bytes, and it is the difference between the 16 bytes of a unique identifier and the 4 bytes of an int.

What we can conclude is that the clustered index key is added to the nonclustered index and if it is 16 bytes, then 16 bytes is added; if it is 4 bytes, then it is 4 bytes that are added.

Why should we ever then use Unique identifier, because I have now showed you how much better an integer performs as a clustered key.

Well……. If I e.g. should have a database with disconnected users, e.g. a CRM system, then it could be a good idea to use a unique identifier. But it is not within the scope of this blog to write about that.

Søren

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.

 

 

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.

 

 

 

 

Index fragmentation

I have seen a lot of nice ways of handling index fragmentations, but in the blog I would try to give you my approach on how to setup index defragmentation on a production server.

First of all, then there is a lot of Things to take into consideration, and that is Things like:

  1. How often do we need to do defragmentation.
  2. What version do we have of SQL server.
  3. How often is our data defragmented.
  4. How large are our indexes.
  5. And so on.

and when we have used a lot of time to think about that, then we will (at least in 90%) of all situations end up with some standardrecommandations:

  1. When an index is more than 10% fragmented, then we must do defragmentation.
  2. When it is between 10% and 30%  fragmented, then we must do a reorganize.
  3. when it is over 30% fragmented, then we must do a rebuild.

This blog is not about answering the questions about when to defragment, how often and so on, this script is just about getting some code that can optimize defragmentation.

We will use three special techonologies called:

  1. Dynamic Management object.
  2. Cursors
  3. Dynamic SQL.

First we must identify fragmentation on each index, and we will use a Dynamic Management object called dm_db_index_physical_stats, and it takes the following parameters:

  1. DatabaseID
  2. ObjectID
  3. IndexID
  4. PartitionID
  5. Mode

the important thing is the mode, because it takes 5 different values, and the two most important is SAMPLED, DETAILED. Use SAMPLED if you have a big database with some heavy indexes. Use DETAILED, when you have a smaller database. DETAILED gives a more accurate view of the indexes, and SAMPLED returns the result faster.

So, if we use that, then we now can create a small TSQL script that returns fragmentation for all indexes in a database.

select DB_NAME(database_ID) asdatabasename
       , T1.object_id as [Table ID]
       , OBJECT_NAME(t1.object_ID) as [Table name]
       , t2.index_id as [Index ID]
       , t2.name as [Index name] 
       , max(avg_fragmentation_in_percent) as Fragmentation 
       , case when max(avg_fragmentation_in_percent)<10 then  
           'No action required' 
         else 
           case when max(avg_fragmentation_in_percent)<40 then 
             'Index should be REORGANIZED' 
           else  
             'Index should be REBUILD' 
           end 
         end as ActionType
from sys.dm_db_index_physical_stats(db_id('adventureworks'),null,null,null,'SAMPLED')t1 
       inner join sys.indexes t2 on t1.index_id=t2.index_id and t1.object_id=t2.object_id
group by DB_NAME(database_ID) 
         , T1.object_id
         , OBJECT_NAME(t1.object_ID)   
         , t2.index_id  , t2.name

order by case when max(avg_fragmentation_in_percent)<10 then 
           'No action required' 
         else 
           case when max(avg_fragmentation_in_percent)<40 then 
             'Index should be REORGANIZED' 
           else  
             'Index should be REBUILD' 
           end
         end ,[Table name] , [Index ID]

Try to run that script some couple of times and then you can see how it works.

Now we can find the information we needs pr. index, and that is:

  1. Object_ID
  2. Index_ID
  3. Fragmentation in percent

And we can now create a cursor that do the appropriate action. Please not that we also use dynamic SQL to run do this task. The code looks like

Use AdventureWorks
go
Declare @objectid  varchar(20)
Declare @indexid  varchar(20)
Declare @fragmentation float
Declare sjCursor Cursor  for
              select t2.object_id   
              , t3.index_id   
              ,  max(avg_fragmentation_in_percent) as max_fragmentation_index
              from sys.dm_db_index_physical_stats(DB_ID(), null, null, null, 'Sampled') t1   
                     inner join sys.objects t2 on t1.object_id=t2.object_id   
                     inner join sys.indexes t3 on t1.index_id = t3.index_id and t3.object_id=t1.object_id
              group by t2.object_id   
              , t3.index_id
              having max(avg_fragmentation_in_percent)>10
open sjCursor
fetch next from sjcursor into @objectid, @indexid, @fragmentation
while @@FETCH_STATUS=0
begin  
  Declare @sqlstatement varchar(500)  
  Declare @objectnavn  Varchar(500)  
  Declare @indexnavn  varchar(500)    
  set @objectnavn=(select '['+t2.name+'].['+t1.name+']'
                   from sys.objects t1
                      inner join sys.schemas t2 on t1.schema_id=t2.schema_id
                   where t1.object_id=@objectid)  
  set @indexnavn = (select '['+name+']'
                    from sys.indexes
                    where object_id=@objectid and index_id=@indexid)  
  if @fragmentation<=30   
    set @sqlstatement='alter index '+@indexnavn+ ' on '+@objectnavn+' REORGANIZE'  
  else   
    set @sqlstatement='alter index '+@indexnavn+ ' on '+@objectnavn+' REBUILD'
  exec(@sqlstatement)
  fetch next from sjcursor into @objectid, @indexid, @fragmentation
end close sjCursor
deallocate sjCursor

 

THATS IT. run this script on the SQL Server, and you have a smarter way of doing defragmentation.

You could improve the script in many ways, and some of them are

  1. Create another cursor so that you can do multiple databases.
  2. Add a top 100 to the select statement, so that it only defrags the most fragmented indexes.
  3. Fix code to tune performance of the script.

But anyway…… It Works and it is a good way of working.

All scripts on this blog is executed at your own risk!!!!

Søren

 

 

10 steps to better performance

Hi,

I have decided to write 10 short blogs about performance improvement from a practical point of view. These 10 blogs are not the only answer to the performance issues, but they are practical answers to a lot of problems that a lot of users / companies experience.

The 10 blogs will be

  1. Forget about indexing, lets work with executionplans instead. A lot of users tend to be to focused on indexing. Indexing can be a great thing, but if you don’t understand the nature of your queries and what causes the problems, then you can’t do prober indexing. Don’t misunderstand me… Indexing is very IMPORTANT, but it is not everything.
  2. Let’s start profiling. Executionplans shows how the query is executed and profiling shows what is executed. Both things are important, and if you know how to master profiling and executionplans, then you are well ahead with performancetuning.
  3. Database properties. This one is a simple one, but basically its about how to setup your database to tweak the best performance out off it, or at least to avoid a degrade in performance.
  4. Server configuration. We don’t look at everything that you need to know about serverconfiguration, but we talk about the small things that can improve performance without a lot of costly changes.
  5. Indexes. Lets discuss indexes, what types of indexes that you can use and when to use them.
  6. Statistics. What are statistics and how can it help improve performance.
  7. Concurrency analysis. How do we handle multiple users.
  8. Code analysis and rewriting. A lot of developers write a lot of crap code, and in this blog, I will try to show what is important when you want to have a fight with developers.
  9. The worst tool, Database Engine tuning advisor. But we need to know how to use it, and what we can use it for.
  10. Datatypes and common mistakes.

I hope that you will join me in these blogs, and you are also welcome to comment on them.