How to tune ISV apps – Capture the workload

This is the first article of my blog post series How to tune ISV apps
In today’s blog post i will describe the steps that you have to do in order to capture a typical workload during business hours.

Importance of the baseline.

Why is it important to capture a workload?
Because you need something that you can compare your improvements against. If you have no baseline, you cannot guarantee that the improvements you have performed against your database are real improvements or just drawbacks.

The tools you need to capture the workload are:

  • SQL Server Profiler
  • SQL Server Management Studio

Don’t worry – SQL Server Profiler will not be used to capture the workload on the production server, it is just the tool to create the right “setup”.

Create the Tracefile

Start SQL Server Profiler, whether from the production server or another server does not matter, because we will not bring the production server to it’s knees 😉
Choose “File -> New Trace…”
CW_Profiler1

Connect to your SQL Server instance using the credentials that you have. (You need at least the “ALTER TRACE” permission for you SQL Server Login.)
CW_Profiler2

In the next wizard the only important thing is the template that you choose, this has to be “TSQL_Replay”. With that template you are able to really replay one and the same workload against many different versions of our optimized database. Everytime you do that, you can check wether your changes have been good ones or bad ones.
CW_Profiler3

Just click “Run”. The grid with the incoming requests appears.
Click immediately on the red square button “Stop” in order to stop the trace.
CW_Profiler41

Now you have to export this new “Script Trace Definition” to a T-SQL file. Just save it anywhere, you can later copy’n’paste it to your production server, if you did not already start the SQL Server Profiler on that very server.
CW_Profiler5

Alter the Trace Definition File

Now you have to open the file and make some settings and tweak it a little bit.

  • Set an appropriate file size. Once you run this Trace Definition File in order to create the real trace files on file system, the trace will generate a new file each time it hits the threshold defined at the parameter “@maxfilesize”. The default for this value is 5 what means 5 MB. Personally i set it to 50 MB:
    set @maxfilesize = 50
    
  • Specify the location where the trace files should be written.
    !Important!: Please make sure that the amount of disk space you need is in place, because the amount of data you collect depends on the workload that you really have got on the production server and also on the duration that you want to capture.
    I captured a workload on a OLTP Helpdesk Trouble Ticket System between 1:30pm and 15:00pm and collected 4 GB of data.
    So please edit the following line, so that it fits your needs.
    You have to change two things in this line:

    • Change the value of the second parameter from “0” to “2” in order to activate the rollover option.
      Otherwise you will find the following error in SQL Server Errorlog:
      Trace ID ‘2’ was stopped because of an error. Cause: Stopping the trace because the current trace file is full and the rollover option is not specified.. Restart the trace after correcting the problem.

    • At the third parameter specify a proper path and filename.
    exec @rc = sp_trace_create @TraceID output, 2, N'D:\CapturedTraces\Trace', @maxfilesize, NULL, 200
    

    As mentioned in the notes above the line, the “.trc” extension will be appended to the filename automatically.

  • At the end of the file you will find a line with
    exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - d7cd774c-ec84-488e-afab-2211df8a6ead'
    

    If the SQL Server Instance you want to trace, hosts more than one database, you have to apply another filter like this, where you have to replace “YourDatabaseNameHere”:

    exec sp_trace_setfilter @TraceID, 35, 0, 6, N'YourDatabaseNameHere'
    
  • Now back to the very top of the file you have to add TSQL code to backup you database before executing the capturing trace like this:
    BACKUP DATABASE [YourDatabaseNameHere]
    TO DISK = N'E:\MSSQL10_50.MSSQLSERVER\MSSQL\YourDatabaseNameHere_4_Replay.bak'
    WITH
    	COPY_ONLY
    	, NOFORMAT
    	, NOINIT
    	, NAME = N'YourDatabaseNameHere-Full Database Backup'
    	, SKIP
    	, COMPRESSION
    	, STATS = 1
    GO
    

    Once again, just replace “YourDatabaseNameHere” with your real database name.
    !Important!: Please make sure that you have set the “COPY_ONLY” option in order to leave the backup chain unbroken. If your version of SQL Server supports backup compression, please also add “COMPRESSION”.

    Execute the Trace

    Once you have finished tweaking your “Trace Definition File”, open your SQL Server Management Studio, connect to your SQL Server instance, open a “New Query” and paste the code or just open the Trace Definition File.

    Now: execute the script, pressing “F5” or clicking “! Execute”.

    In the result grid, you will see
    CW_Profiler6
    This is the ID of your very trace that you started. Keep this ID in mind or elsewhere for later use.

    If you have a look at the file system, you will see the created file(s) in your specified folder. Once the 50 MB have been reached, SQL Server rolls over to the next file, named YourFileNameHere_1.trc
    CW_Profiler7

    Stop the Trace

    After your defined period of time that you wanted to capture, you have to stop the SQL Trace. Here comes the ID of the trace into play as you have to pass it to the stored procedure that you execute as the first parameter.

    exec sp_trace_setstatus 2, 0
    

    After that, just delete the Trace so that there are no more processes living in SQL Server.

    exec sp_trace_setstatus 2, 2
    

    Save the data

    Now that you successfully captured the workload, just move the backup and all of your trace files away from your production server to a location that is safe.

    Summary

    In today’s blog post you have learned how to make use of SQL Server Profiler and SQL Server Management Studio to capture a workload on your production server in order to be able to replay it whenever you want against a specified backup that you made beforehand.

    I hope you enjoyed reading this blog post and will return for the next one when i will cover the topic “Diagnosis and Doing with “sp_BlitzIndex™””

    Thanks,
    Christoph

    Advertisements
Posted in SQL Server, Uncategorized | Tagged , , , , | 1 Comment

Last “Touch” on each Database per Instance

Enhanced the T-SQL Script by adding last_user_scan, last_user_seek and last_user_lookup and compared the values against each other to get the most recent access to the very table.

cms4j

Today i was asked if some of the databases on a specific instance were in use or not.
As this is an interesting question that could be easily answered by the SQL Server DMVs i wrote a little script to answer this question.

It creates a temporary table with five colummns named DatabaseName, TableName, IndexType, LastTouch and ActualDate (this is just for comparison).
It then executes sp_MSforeachdb and inserts a record for each database within the instance except for the system databases.
It then tells us which table was recently used and when.

View original post

Posted in Uncategorized | Leave a comment

Blog Post Series „How to tune ISV apps“

Inspired by Brent Ozars Blog post http://www.brentozar.com/archive/2014/12/whos-allowed-add-indexes-isv-apps/ I will start this series.

It will consist of:

  1. Capture the workload
    Capturing a typical workload during business hours.
  2. Diagnosis and Doing with “sp_BlitzIndex™”
    Diagnose and Delete/Alter/Add Indexes with the help of Brent Ozars “sp_BlitzIndex™”.
  3. Diagnosis and Doing with “Find Key Lookups in Cached Plans”
    Diagnose and Alter/Add Indexes with the help of Kendal van Dykes “Find Key Lookups in Cached Plans”.
  4. Setup Distributed Replay
    Setup a SQL Server Instance with Distributed Replay.
  5. Replay the workload
    Replay the captured workload against a backup first taken before capturing the first workload.
  6. Analysis of Performance Improvements
    Analyze two captured workloads with the help of Qure Analyzer.
  7. Analysis with Chrome Developer Tools.
    Load the ISV app in Chrome and make use of the Developer Tools to measure the response times of the Database Server.
  8. Subjective measurements
    Let human beings test the improved app.

Hope to keep you as readers,
Christoph Müller-Spengler

Posted in SQL Server | Tagged , , | 1 Comment

Is SQL Server under high Memory Pressure?

A few weeks ago, i started a blog post series that covers the finding of performance bottlenecks in SQL Server. In the first blog post in the series https://cms4j.wordpress.com/2014/09/30/is-sql-server-under-high-cpu-pressure/
i focused on CPU Pressure.

In this blog post i will explain how to decide from just a few queries to SQL Server DMVs if SQL Server is under high Memory pressure.

We all know there are far more reasons why SQL Server could be slow, but let’s stick with Memory for now.

Page Life Expectancy

A first and very good indicator is the so called Page Life Expectancy (PLE) that can be queried by the DMV sys.dm_os_performance_counters. The Page Life Expectancy tells us how long a data page that was read from disk subsystem will stay in SQL Server’s Buffer Pool.
In former times when SQL Server just had about 4 GB of RAM a value of 300 (seconds) was recommended by Microsoft. But times are changing, so this is no more longer the amount of RAM that you will find in nowadays SQL Servers.

Jonathan Kehayias has a very good blog post about this:
http://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/ -> What’s Wrong about Page Life Expectancy >= 300?

So depending on your Servers RAM you have to define a different value than 300.
Jonathan calculates
Recommended PageLifeExpectancy = Physical RAM / 4 * 300

Top Wait Type

SQL Server always provides you with statistics what he is waiting on for the most of the time. This can be queried from the DMV sys.dm_os_wait_stats.
With high memory pressure i often experienced the Wait Type PAGEIOLATCH_XX as the top wait type.
This is because not all data pages that SQL Server wants to hold in the Buffer Pool fit into it – so SQL Server has to drop some pages from the Buffer Pool in order to read some new fresh pages from the disk subsystem.
Please note that this Wait Type must not always mean that you have Memory Pressure. It could also be a slow disk subsystem or a poor indexing strategy, because there are too many table scans instead of index seeks.
Please take a look at Paul Randals blog post http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/.
I added two more wait types to the “benign” waits: “BACKUPBUFFER” and “BACKUPBIO” as the backup process does not make use of the Buffer Pool.

Buffer Pool Rate

Joining the discussion of the right value for Page Life Expectancy Thomas LaRock brought up another calculation for his so called Buffer Pool Rate. In his blog post
http://thomaslarock.com/2012/05/are-you-using-the-right-sql-server-performance-metrics/ -> 5. BUFFER POOL I/O RATE
he develops this value as
MAX MEMORY / PLE = xx.x MB/sec
He likes to have it below 20 MB/sec.

Building the T-SQL Script

So with these three criteria i built a script.

/*
	CMS, 05.12.2014:
	Is SQL Server under Memory Pressure?
    
    This check consists of 3 Steps:
    1) Check Actual Page Life Expectancy
    2) Check Top Wait Stat                  - with the help of Paul Randal's Wait Stats CHECK
                                              http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
    3) Check Buffer Pool Rate               - With the help of SQL Rockstars Buffer Pool Rate Calculation
                                              http://thomaslarock.com/2012/05/are-you-using-the-right-sql-server-performance-metrics/ -> Buffer Pool I/O Rate
    
*/

SET NOCOUNT ON

DECLARE @MaxServerMemory FLOAT
DECLARE @ActualPageLifeExpectancy FLOAT
DECLARE @RecommendedPageLifeExpectancy FLOAT
DECLARE @RecommendedMemory FLOAT
DECLARE @TopWaitType sysname
DECLARE @BufferPoolRate FLOAT

-- ####################################################################################################
SELECT  @MaxServerMemory = (1.0 * cntr_value / 1024 / 1024)
		, @RecommendedPageLifeExpectancy = Convert(INT ,(1.0 * cntr_value) / 1024 / 1024 / 4.0 * 300)
FROM    sys.dm_os_performance_counters
WHERE   counter_name = 'Target Server Memory (KB)'

SELECT  @ActualPageLifeExpectancy = 1.0 * cntr_value
FROM    sys.dm_os_performance_counters
WHERE   object_name LIKE '%Buffer Manager%'
AND		LOWER(counter_name) = 'page life expectancy'

-- ####################################################################################################
/*
Check TOP Wait Type.
If the TOP 1 Wait Type is PAGEIOLATCH_SH it indicates that SQL Server is waiting on datapages to be read 
into the Buffer Pool.
Memory Pressure might not be the root cause, it can be a poor indexing strategy
because way too many pages have to be read into the buffer pool and they are deleted from the buffer pool too early.
*/
;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 (
        N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
        N'CHKPT',                           N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                        N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
        N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT')
    -- @cms4j: do not take BACKUP into Account, as it does not use the Buffer Cache
	AND  [wait_type] NOT IN (
        N'BACKUPBUFFER', N'BACKUPIO')
    AND [waiting_tasks_count] > 0
 )
SELECT @TopWaitType = (SELECT top 1
    MAX ([W1].[wait_type])
    FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95); -- percentage threshold


-- ####################################################################################################
-- SQL Rockstar comes up with BUFFER POOL RATE. But that depends on SUPER DUPER STORAGE. If that is low, the BufferPoolRate will also be slow.
SELECT  @BufferPoolRate = ( 1.0 * cntr_value / 128.0 )
        / ( SELECT  1.0 * cntr_value
            FROM    sys.dm_os_performance_counters
            WHERE   object_name LIKE '%Buffer Manager%'
                    AND LOWER(counter_name) = 'page life expectancy' )
		FROM    sys.dm_os_performance_counters
WHERE   object_name LIKE '%Buffer Manager%'
        AND counter_name = 'Database pages'

-- ####################################################################################################
-- Calculate Recommended Max Memory
SELECT @RecommendedMemory = Convert(INT, @RecommendedPageLifeExpectancy / @ActualPageLifeExpectancy * @MaxServerMemory)

-- ####################################################################################################
-- Put all the things together...
SELECT	CASE
			WHEN @RecommendedMemory > @MaxServerMemory AND @TopWaitType LIKE 'PAGEIOLATCH_%' AND @BufferPoolRate > 20.0 THEN '3 x YES'
            WHEN
				(@RecommendedMemory > @MaxServerMemory AND @TopWaitType LIKE 'PAGEIOLATCH_%')
				OR
				(@RecommendedMemory > @MaxServerMemory AND @BufferPoolRate > 20.0) THEN '2 x YES'
            WHEN @RecommendedMemory > @MaxServerMemory AND @TopWaitType NOT LIKE 'PAGEIOLATCH_%' THEN '1 x YES, TOP Wait: ' + @TopWaitType
            WHEN @RecommendedMemory < @MaxServerMemory  THEN 'no'
        END AS [Is SQL Server under Memory Pressure?]
        , @MaxServerMemory AS [Max Server Memory (GB)]
        , @ActualPageLifeExpectancy AS [Actual PLE]
        , @RecommendedPageLifeExpectancy AS [Recommended PLE]
        , @RecommendedMemory AS [Recommended Memory (GB)]
        , @TopWaitType AS [Top Wait Type]
        , @BufferPoolRate AS [BufferPool Rate, you want < 20]

So if all of the three criteria equal to “true” the script will show ‘3 x YES’.
If it’s Page Life Expectancy and PAGEIOLATCH_XX or Page Life Expectancy and Buffer Pool Rate, you will get ‘2 x YES’.
Otherwise you will get other results as shown in the script.

Please keep in mind that this is just the starting point where you have to investigate further and proove your first results with more detailed reasons why SQL Server is under high Memory Pressure.

Thank you for reading,
Christoph Müller-Spengler

Other blog posts in that series:

Posted in SQL Server | Tagged , , | Leave a comment

Migrate SharePoint Database from SQL Server 2012 backwards to SQL Server 2008 R2

Last week we were faced with the situation that we had to deploy a Database for SharePoint from another Service Provider.
No problem at all, just download the Backup-File, Restore it to the SQL Server Instance and mount it into SharePoint.

Simply: NO.

In our case we were provided with a SQL Server 2012 Database. It’s not possible to restore a Database from a SQL Server Instance running “SQL Server 2012” into a SQL Server Instance running “SQL Server 2008 R2”.

But we had to find a solution to this problem.

First thought was to choose
Database -> right mouse click -> Tasks -> Export data tier.

So we tested it with an existing database in a SQL Server Instance running SQL Server 2012. But we had no luck, the database contained views that referenced objects from system databases.

So we tried with another database. Still no luck as this one used extended properties, the ones that are used to describe what specific use this very column in that specific table has.

But there had to be a way to solve the problem.

That’s where the SQL Database Migration Wizard comes into play.
SQL Database Migration Wizard is a tool that you can download from Codeplex: https://sqlazuremw.codeplex.com/.
Fun fact: The name still references an older name of SQL Database. For the full history of names go to http://en.wikipedia.org/wiki/SQL_Azure.

Be careful to download the corresponding version to your SQL Server version:

  • v3x: SQL Server 2008 R2 SP1
  • v4x: SQL Server 2012
  • v5x: SQL Server 2014

Just download the right version for you, copy it to your Management Machine and start it from “SQLAzureMW.exe”.
The Wizard itself is really self-explanatory and there are a dozen blog posts out there how to use this wizard,
e.g. Grant Fritchey’s from May 2011: SQL Azure Migration Wizard to migrate OnPremise Databases to Microsoft Azure
or by Sascha Dittmann as part of a Backup strategy from Azure to OnPremise: Datensicherung unter SQL Azure
As you can see, all directions are possible, even
Azure -> Azure
and
OnPremise -> OnPremise.
That’s what makes the SQL Database Migration Wizard so powerful.

As mentioned in the Blog post title we had to deal with a SharePoint Database that we wanted to “restore” on another OnPremise SQL Server Instance.

This is where i want to point out the secrets that you have to be aware of in this very special case.

Disk where you start the SQL Database Migration Wizard from

Make sure that you place the SQL Database Migration Wizard on a drive that has at least double the amount of free space available of the database you want to migrate. This is because SQL Database Migration Wizard creates some temporary objects along with the scripted data. You do not want your SQL Server Instance to stop because you placed the SQL Database Migration Wizard on your C: drive and run out of space!

NotSupportedByAzureFile.Config

By default this file contains all that stuff that SQL Database is not capable of or simply does not support,
e.g. Fill Factor with Indexes, BULK INSERT and so on.

As you know that your OnPremise SQL Server Instance supports everything, you just have to comment all the exceptions (in theory). Under pressure i was not even able to build a valid XML file so i erased all exceptions and came up with a file like this:

<?xml version="1.0"?>
<TSQLNotSupportedByAzure xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <DefaultMessage>{0} is not supported in current version of SQL Azure</DefaultMessage>
    <Skip>
        <SupportedStatement Text="SET ANSI_NULLS ON" />
        <SupportedStatement Text="SET QUOTED_IDENTIFIER ON" />
    </Skip>
    <Table>
        <TableStatement>
            </TableStatement>
    </Table>
    <Index>
        <IndexOptions>
        </IndexOptions>
    </Index>
    <Schema>
        <SchemaChecks>
            </SchemaChecks>
    </Schema>
    <View>
        <ViewStatement>
        </ViewStatement>
    </View>
    <GeneralTSQL>
        </GeneralTSQL>
    <ActiveDirectorySP>
        </ActiveDirectorySP>
    <BackupandRestoreTable>
    </BackupandRestoreTable>
    <ChangeDataCapture>
    </ChangeDataCapture>
    <DatabaseEngineSP>
    </DatabaseEngineSP>
    <DatabaseMailSP>
    </DatabaseMailSP>
    <DatabaseMaintenancePlan>
    </DatabaseMaintenancePlan>
    <DataControl>
    </DataControl>
    <DistributedQueriesSP>
    </DistributedQueriesSP>
    <FullTextSearchSP>
    </FullTextSearchSP>
    <GeneralExtendedSPs>
    </GeneralExtendedSPs>
    <IntegrationServicesTable>
    </IntegrationServicesTable>
    <LogShipping>
    </LogShipping>
    <MetadataFunction>
    </MetadataFunction>
    <OLEAutomationSP>
    </OLEAutomationSP>
    <OLEDBTable>
    </OLEDBTable>
    <ProfilerSP>
    </ProfilerSP>
    <ReplicationSP>
    </ReplicationSP>
    <ReplicationTable>
    </ReplicationTable>
    <RowsetFunction>
    </RowsetFunction>
    <SecurityFunction>
    </SecurityFunction>
    <SecuritySP>
    </SecuritySP>
    <SQLMailSP>
    </SQLMailSP>
    <SQLServerAgentSP>
    </SQLServerAgentSP>
    <SQLServerAgentTable>
    </SQLServerAgentTable>
    <SystemCatalogView>
    </SystemCatalogView>
    <SystemFunction>
    </SystemFunction>
    <SystemStatisticalFunction>
    </SystemStatisticalFunction>
    <Unclassified>
    </Unclassified>
</TSQLNotSupportedByAzure>

SQLAzureMW.exe.config

This configuration file does contain not only the L10N strings for the GUI, but also some Application Settings, starting with line 207 😉

DBCollation

The most important key that you have to specify is

<add key="DBCollation" value="Latin1_General_CI_AS_KS_WS"/>

because SharePoint needs this collation for all of it’s databases.
If you do not specify the DBCollation, the SQL Database Migration Wizard will simply not set this property for the Database and therefore the Default Collation of the SQL Server Instance will be set for the Database that you migrate. Trust me, i have done this once and will never forget about it, as a 30 GB Database will take some time to migrate :-/

BCPFileDir

Another important key is

<add key="BCPFileDir" value="E:\BCPData"/>

because this is where your scripted data will reside and surely you remember that you do not want to fill up your system drive 😉

ScriptTableAndOrData

I recognized in version v4.15.5 that you can choose to just script the SCHEMA or both SCHEMA and DATA or DATA only.
So set this key according to the action you want to perform.
In our case it’s ScriptOptionsTableSchemaData of course 😉

<add key="ScriptTableAndOrData" value="ScriptOptionsTableSchemaData"/>

Let’s do the migration

Once you payed attention to these important points, you can start the SQL Database Migration Wizard by executing the exe file.

  • On the first screen hit the radio button “Analyze / Migrate: Database” and click “Next >”.
  • Enter the Source-SQL Server Instance information along with your Authentication credentials, specify the Source Database by it’s name and click “Connect”.
  • Select the Database and click “Next >”
  • Hit the radio button “Script all database objects” and click “Next >”
  • The “Script Wizard Summary” just shows what it’s named for. Click “Next >”
  • You are prompted with the Question: “Ready to generate SQL script?” Click “Yes” and help yourself to some coffee 😉
  • After a few seconds or some hours, the blue progress bar on top will hit the right border of the wizard and below the progress bar the simple hint “Done!” will appear. You now have the possibility to save the logged information to your hard disk.
  • Click “Next >”
  • Enter the Destination-SQL Server Instance information along with your Authentication credentials, leave the radio button next to “Master DB (List all databases) and click “Connect”.
  • You might now select an existing Database that you want to migrate your Source Database into. Otherwise click “Create Database”
  • Enter the Database Name and be lucky, that you have specified the “SharePoint Collation” in the file “SQLAzureMW.exe.config” because you do not really want to scroll down the whole list of available Collations and pick the right one by accident ;-). Click “Create Database”.
  • Click “Next >”.
  • You are prompted with the question: “Execute the script against destination server”? Of course you want to: click “Yes”.
  • Once again the blue progress bar flushes the screen and you read the four letters “Done!”.
  • Again you have the chance to save the logged information to your hard disk.
  • As you have reached the final screen of the Wizard, klick “Exit”.

Now you have migrated a database running on a SQL Server 2012 Instance into a SQL Server 2008 Instance.

Be careful!

Technically i bet you can “downgrade” a Database from SQL Server 2014 to SQL Server 2005. But keep in mind that from one version to the next some things might have changed within SQL Server.
SQL Server is for good reason not backward compatible.
Some names of DMVs might have changed, the call of RAISERROR has changed.
I even think that it’s not possible to migrate assemblies that you have written in C# and deployed into SQL Server using the CLR. This is not proven, just a thought.
So please be careful and test all of the functionality that the Application that uses the Database provides is still working.

I hope you enjoyed this blog post,
Thanks for reading, Christoph

Posted in SQL Server | Leave a comment

Windows Server 2012, .NET Framework 3.5, 0x800F0906, 0x800F081F

This week i tried to install SQL Server 2012 on a freshly provided Windows Server 2012.

During the setup the error message opened that

“… computer cannot download the required files from Windows Update”

So i tried to add the .NET 3.5 Feature from Server Manager, but that even did not work out. Once again i was prompted with the above mentioned error message.

To make a long story short, if you google the buzz words from the title of this Blog post, you will see a whole bunch of other Blog posts or questions and answers on stackoverflow.com etc pp. All this stuff did not work out. At least a colleague of mine found the solution on this Blog:

http://consulting.risualblogs.com/blog/2012/07/04/enabling-net-3-5-on-server-2012-rp-source-files-not-found/

There is also another requirement to install the .NET 3.5 ServerFeatures component before .NET 3.5 can be installed. To do this you need to run the following DISM commands;


DISM.EXE /Online /Enable-Feature /FeatureName:NetFX3ServerFeatures /Source:Z:\Sources\SXS

DISM.EXE /Online /Enable-Feature /FeatureName:NetFX3 /Source:Z:\Sources\SXS

… where Z is the drive letter of the Server 2012 Media.

Sometimes it’s just that easy.
When you know it.

Now you know it too 😉

Thank’s for reading

Posted in Windows Server | Tagged , | 1 Comment

Is SQL Server under high CPU pressure?

Situation

Sometimes we get a phone call, a trouble ticket or just a visit from a colleague telling us

“The application xy is slow, what the heck is going on with the underlying SQL Server?”

This is the only information that we are provided with – so we have to investigate what’s really going on with SQL Server.
We even have to prove that it is SQL Server that makes the app so slow, or we can prove that it is not SQL Server.

There are plenty of ways to find out what’s going on, some people start with the amazing script by BrentOzar sp_AskBrent®, some rely on the Wait Statistics of SQL Server, you even might visit the server via Remote Desktop and have a look at the Task Manager. But how can you tell that the high green line in the CPU box is caused by SQL Server?

For me it would be handy to have a script that i can execute directly in SQL Server Management Studio that would tell me what’s going on. It would tell me from the insights of SQL Server if there is something going wrong.

In todays blog post i focus on just CPU pressure. We all know there are far more reasons why SQL Server could be slow, but let’s stick with CPU for now.

Start Investigation

Starting with a script taken from the eBook
“Troubleshooting SQL Server A Guide for the Accidental DBA” by Jonathan Kehayias and Ted Krueger
we find a script that shows us the CPU pressure via the signal wait time.

The signal wait time is the time that is measured within SQL Server’s DMVs when a task that is runnable and has all ressources available (e.g. the data pages are read from disk into the Buffer Pool) is waiting for the CPU to become available for him. So the signal wait time is pure CPU wait time.

So here’s the script from the eBook:

SELECT  SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,
        ( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
          / SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 ) AS PercentageSignalWaitsOfTotalTime
FROM    sys.dm_os_wait_stats
-- (Page 79, Listing 3.1: Verifying CPU Pressure via signal wait time.)

This might show you a result like this:

TotalSignalWaitTime

You might be impressed: Wow, SQL Server is waiting about 20% of the time for the CPU to become available for the next runnable task…

The interesting thing here is, that even on a SQL Server box that is sitting around bored, the results may be nearly the same. So i came across a blog post by Paul S. Randal:
Wait statistics, or please tell me where it hurts
In this blog post he clarifies:

“Bunch of waits are being filtered out of consideration, waits happen all the time and these are the benign ones we can usually ignore.” (Paul S. Randal)

So i added the WHERE clause to the upper SELECT statement and got the following script:

SELECT  SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,
        ( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
          / SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 ) AS PercentageSignalWaitsOfTotalTime_WithoutBenignWaits
FROM    sys.dm_os_wait_stats
WHERE	[wait_type] NOT IN (
			N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
			N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
			N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
			N'CHKPT',                           N'CLR_AUTO_EVENT',
			N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
			N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
			N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
			N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
			N'EXECSYNC',                        N'FSAGENT',
			N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
			N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
			N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
			N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
			N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
			N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
			N'PWAIT_ALL_COMPONENTS_INITIALIZED',
			N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
			N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
			N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
			N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
			N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
			N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
			N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
			N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
			N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
			N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
			N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
			N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
			N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
			N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
			N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
			N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT')
AND		[waiting_tasks_count] > 0

Executing that against the very same SQL Server Instance, i got the following result:

TotalSignalWaitTime_WithoutBenignWaits

Oh wait – this reduced the real wait for CPU to a bit more than 3% Signal Wait Time.
So from this point of view, this SQL Server Instance could not be under high CPU pressure.

So i executed the script without Paul’s benign waits against a server that i know is under CPU pressure:

BothSignalWaits

Now our Percent of Signal Wait Time Without Benign Waits is even higher than the Total Percent Value. That means that the “bad” waits should be taken even more into account as these Waitings take relatively longer than All Waits.
So this must be a very good indicator for high CPU pressure.
It’s just that easy: Just substract the value without the benign waits from the total value – if you get a negative value, there you are: This really must be a SQL Server under high CPU pressure.

This leads us to the following script:

DECLARE @PercentageSignalWaitsOfTotalTime FLOAT
DECLARE @PercentageSignalWaitsOfTotalTime_WithoutBenignWaits FLOAT

SELECT		@PercentageSignalWaitsOfTotalTime = (( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
            / SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 ))
			, @PercentageSignalWaitsOfTotalTime_WithoutBenignWaits =
			  (SELECT
							( SUM(CAST(PaulR.signal_wait_time_ms AS NUMERIC(20, 2)))
							/ SUM(CAST(PaulR.wait_time_ms AS NUMERIC(20, 2))) * 100 )
				FROM		sys.dm_os_wait_stats PaulR
				WHERE		PaulR.wait_type NOT LIKE '%SLEEP%' -- remove eg. SLEEP_TASK and
				-- LAZYWRITER_SLEEP waits
				AND			PaulR.wait_type NOT LIKE 'XE%'	 -- remove Extended Events
				AND			PaulR.wait_type NOT IN (			 -- remove system waits
						N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
						N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
						N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
						N'CHKPT',                           N'CLR_AUTO_EVENT',
						N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
						N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
						N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
						N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
						N'EXECSYNC',                        N'FSAGENT',
						N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
						N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
						N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
						N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
						N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
						N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
						N'PWAIT_ALL_COMPONENTS_INITIALIZED',
						N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
						N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
						N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
						N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
						N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
						N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
						N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
						N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
						N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
						N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
						N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
						N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
						N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
						N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
						N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
						N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT'))
FROM		sys.dm_os_wait_stats

SELECT		@PercentageSignalWaitsOfTotalTime - @PercentageSignalWaitsOfTotalTime_WithoutBenignWaits AS [IfNegativeThenCPUPressure]
			, @PercentageSignalWaitsOfTotalTime AS [PercentageSignalWaitsOfTotalTime]
			, @PercentageSignalWaitsOfTotalTime_WithoutBenignWaits AS [PercentageSignalWaitsOfTotalTime_WithoutPaulRandalsBenignWaits]

Executed against the SQL Server it puts this result to the grid:

IfNegativeThenCPUPressure

Hey, minus 20% is a big deal, isn’t it?

But wait!
Let’s double check it with SQL Servers we know are just sitting around bored.

BothSignalWaitsOnBoredSQLServer

Hmmm, following my own thoughts how to determine if a SQL Server is under high CPU pressure or not, this would lead to “False Positives”. So we have to set a threshold.

The following article from the Technet Magazine SQL Server: SQL Server Delays Demystified as an excerpt from “SQL Server DMV Starter Pack,” published by Red Gate Books (2010), written by Glenn Berry, Louis Davidson and Tim Ford, states that

The key metric, with regard to potential CPU pressure, is the signal wait as a percentage of the total waits. A high-percentage signal is a sign of excessive CPU pressure. The literature tends to quote “high” as more than about 25 percent, but it depends on your system.

On our systems, we treat values greater than 10 percent to 15 percent as a worrying sign. Overall, the use of wait statistics represents a very effective means of diagnosing response times in your system. In simple terms, you either work or you wait. Response time equals service time plus the wait time.

Applying the threshold of 15 percent for the Percentage of Signal Waits of Total Time i end up with this script:

DECLARE @PercentageSignalWaitsOfTotalTime FLOAT
DECLARE @PercentageSignalWaitsOfTotalTime_WithPaulRandalsExclusions FLOAT
Declare @SubstractedValue FLOAT

SELECT		@PercentageSignalWaitsOfTotalTime = (( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
            / SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 ))
			, @PercentageSignalWaitsOfTotalTime_WithPaulRandalsExclusions =
			  (SELECT
							( SUM(CAST(PaulR.signal_wait_time_ms AS NUMERIC(20, 2)))
							/ SUM(CAST(PaulR.wait_time_ms AS NUMERIC(20, 2))) * 100 )
				FROM		sys.dm_os_wait_stats PaulR
				WHERE		PaulR.wait_type NOT LIKE '%SLEEP%' -- remove eg. SLEEP_TASK and
				-- LAZYWRITER_SLEEP waits
				AND			PaulR.wait_type NOT LIKE 'XE%'	 -- remove Extended Events
				AND			PaulR.wait_type NOT IN (			 -- remove system waits
						N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
						N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
						N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
						N'CHKPT',                           N'CLR_AUTO_EVENT',
						N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
						N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
						N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
						N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
						N'EXECSYNC',                        N'FSAGENT',
						N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
						N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
						N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
						N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
						N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
						N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
						N'PWAIT_ALL_COMPONENTS_INITIALIZED',
						N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
						N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
						N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
						N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
						N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
						N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
						N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
						N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
						N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
						N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
						N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
						N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
						N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
						N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
						N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
						N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT'))
FROM		sys.dm_os_wait_stats;

SELECT		@SubstractedValue = @PercentageSignalWaitsOfTotalTime - @PercentageSignalWaitsOfTotalTime_WithPaulRandalsExclusions;

SELECT		CASE
				WHEN @PercentageSignalWaitsOfTotalTime < 15.0  THEN 'no'                 WHEN @PercentageSignalWaitsOfTotalTime > 15.0
				     AND @SubstractedValue > 0.0 THEN 'no'
				WHEN @PercentageSignalWaitsOfTotalTime > 15.0
				     AND @SubstractedValue < 0.0 THEN 'YES'
			END AS [IsSQLServerUnderCPUPressure?]
			, @PercentageSignalWaitsOfTotalTime AS [PercentageSignalWaitsOfTotalTime]
			, @PercentageSignalWaitsOfTotalTime_WithPaulRandalsExclusions AS [PercentageSignalWaitsOfTotalTime_WithoutPaulRandalsBenignWaits];

This is what i get to see if i run this script against a SQL Server under high CPU pressure:

FinalResult

Finally we got it! A nearly rock solid indicator that a SQL Server is under high CPU pressure.

What about the current situation?

Now we come to another important point:

Querying against the Dynamic Management Views of SQL Server means querying against data, that is collected from the last SQL Server Instance Restart. Unless you clear the cache e.g. executing this little script:

DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR);
GO

which you might do to ensure that you get an isolated set of data from a special given starting point.

So with a SQL Server Instance, that has been up and running for a long time, you get an average value for this period.

So besides the general information if SQL Server is or at least was under CPU pressure since the last restart, we want to know if SQL Server is right now under CPU pressure.

So again Paul Randal provides a script to create a snapshot of the wait statistics, that can be found here:

Wait statistics or please tell me where it hurts

So i took the script and adapted it to come back with a definitive answer whether the SQL Server Instance is under CPU pressure or not.

The most important parts to decide are the following ones:

CPU pressure is indicated by increasing signal-wait times and long Runnable Queues

So this is the final “snapshot” script:


-- Is SQL Server under CPU Pressure right now?

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = N'##CMSWaitStatsCPU1')
DROP TABLE [##CMSWaitStatsCPU1];

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = N'##CMSWaitStatsCPU2')
DROP TABLE [##CMSWaitStatsCPU2];
GO

;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]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)

SELECT [wait_type], [WaitS], [ResourceS], [SignalS], [WaitCount], [Percentage]
INTO [##CMSWaitStatsCPU1]
FROM [Waits]

WAITFOR DELAY '00:00:30'
GO
;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]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)
SELECT [wait_type], [WaitS], [ResourceS], [SignalS], [WaitCount], [Percentage]
INTO [##CMSWaitStatsCPU2]
FROM [Waits]

DECLARE @SignalWait_2_TotalWait_Ratio DECIMAL (16,2)
DECLARE @TotalPercentageOfSOS_SCHEDULER_YIELD DECIMAL (16,2)
DECLARE @TotalWaits DECIMAL (16,2)
DECLARE @ResourceWait DECIMAL (16,2)
DECLARE @SignalWaits DECIMAL (16,2)
DECLARE @WaitCount INT
DECLARE @TopWaitType sysname
;WITH [DiffCPUWaits] AS
(
-- Waits that weren't in the first snapshot
SELECT [CPU2].[wait_type] ,
[CPU2].[WaitS] ,
[CPU2].[ResourceS] ,
[CPU2].[SignalS] ,
[CPU2].[WaitCount],
[CPU2].[Percentage]
FROM [##CMSWaitStatsCPU2] AS [CPU2]
LEFT OUTER JOIN [##CMSWaitStatsCPU1] AS [CPU1] ON [CPU1].[wait_type] = [CPU2].[wait_type]
WHERE [CPU1].[wait_type] IS NULL

UNION
-- Diff of Waits in both snapshots
SELECT [CPU2].[wait_type] ,
[CPU2].[WaitS] - [CPU1].[WaitS],
[CPU2].[ResourceS] - [CPU1].[ResourceS],
[CPU2].[SignalS] - [CPU1].[SignalS],
[CPU2].[WaitCount] - [CPU1].[WaitCount],
[CPU2].[Percentage]
FROM [##CMSWaitStatsCPU2] AS [CPU2]
LEFT OUTER JOIN [##CMSWaitStatsCPU1] AS [CPU1] ON [CPU1].[wait_type] = [CPU2].[wait_type]
WHERE [CPU1].[wait_type] IS NOT NULL
)

-- as we run snapshots with a time difference of 30 seconds,
-- the Signal Wait Time has to be at least 15% of that --> 4.5 seconds.

-- COLLECT the results
SELECT
@SignalWait_2_TotalWait_Ratio = (CAST ( (SUM([DiffCPUWaits].[SignalS]) / SUM([DiffCPUWaits].[WaitS])) * 100.0 AS DECIMAL(16,2)))
, @TotalWaits = (CAST (SUM([DiffCPUWaits].[WaitS]) AS DECIMAL(16,2)))
, @ResourceWait = (CAST (SUM([DiffCPUWaits].[ResourceS]) AS DECIMAL(16,2)))
, @SignalWaits = (CAST (SUM([DiffCPUWaits].[SignalS]) AS DECIMAL(16,2)))
, @WaitCount = (CAST (SUM([DiffCPUWaits].[WaitCount]) AS INTEGER))
, @TopWaitType = (SELECT TOP 1 wait_type FROM [DiffCPUWaits] ORDER BY [WaitS])
, @TotalPercentageOfSOS_SCHEDULER_YIELD = (SELECT [Percentage] FROM [DiffCPUWaits] WHERE [DiffCPUWaits].[wait_type] = 'SOS_SCHEDULER_YIELD')
FROM [DiffCPUWaits]
-- INTERPRET the results
SELECT CASE

WHEN @SignalWait_2_TotalWait_Ratio < 15.0
AND @SignalWaits < 4.5 THEN 'no' WHEN @SignalWait_2_TotalWait_Ratio > 15.0
AND @SignalWaits < 4.5
THEN '1 x YES'

WHEN @SignalWait_2_TotalWait_Ratio < 15.0 AND @SignalWaits > 4.5
THEN '1 x YES'

WHEN @SignalWait_2_TotalWait_Ratio > 15.0
AND @SignalWaits > 4.5
AND @TotalPercentageOfSOS_SCHEDULER_YIELD < 10.0 THEN '2 x YES' WHEN @SignalWait_2_TotalWait_Ratio > 15.0
AND @SignalWaits > 4.5
AND @TotalPercentageOfSOS_SCHEDULER_YIELD > 10.0
THEN '3 x YES - INTERNAL CPU PRESSURE'
WHEN @SignalWait_2_TotalWait_Ratio > 15.0
AND @SignalWaits > 4.5
AND @TotalPercentageOfSOS_SCHEDULER_YIELD > 10.0
AND @TopWaitType = 'SOS_SCHEDULER_YIELD'
THEN '4 x YES - HIGH INTERNAL CPU PRESSURE'

END AS [Is SQL Server under CPU Pressure?]
, @SignalWait_2_TotalWait_Ratio AS [Signal Waits / Total Waits (%) < 15]
, @TotalWaits AS [Waits - from R.ing to R.ing (sec)]
, @ResourceWait AS [Ressource Waits - for Rescources (sec)]
, @SignalWaits AS [Signal Waits - from R.able to R.ing (sec) < 4.5]
, @TotalPercentageOfSOS_SCHEDULER_YIELD AS [SOS_SCHEDULER_YIELDS Waits Of Total (%) < 10]

-- Cleanup
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = N'##CMSWaitStatsCPU1')
DROP TABLE [##CMSWaitStatsCPU1];

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = N'##CMSWaitStatsCPU2')
DROP TABLE [##CMSWaitStatsCPU2];

GO

So what do you do with these findings?

From a Full Service Provider’s point of view:
You might only be able to throw hardware to the VM / physical Server.

From a developers point of view:
You are the one that can fix the situation. You can have a look at the implementation of the queries, the proper indexing, is sorting really something for the second most expensive Sorting Server?

In any case:
You will have to investigate further as the information “Is my SQL Server Instance under CPU pressure” is just the tip of the iceberg 🙂

I hope you enjoyed reading this blog post, best of luck investigating further!
Thanks
Christoph

Other blog posts in that series

Posted in SQL Server | Tagged , | Leave a comment