SQL DMV: Most Expensive Queries, Missing Indexes

Database Management Views and their usage in SQL performance tuning scenarios are well known. There are plenty posts with many different versions of the Most Expensive Queries query. As I don’t want to look for the right one again and again I archive my favorite one here:

-- Most expensive queries
SELECT TOP 20
	SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
		((CASE qs.statement_end_offset
			WHEN -1 THEN DATALENGTH(qt.TEXT)
			ELSE qs.statement_end_offset
			END - qs.statement_start_offset)/2)+1)
		AS query_text,
    db.name AS [db_name],
    qs.total_elapsed_time/1000 AS total_elapsed_time_ms,
    qs.total_elapsed_time/qs.execution_count/1000 AS average_elapsed_time_ms,
    qs.last_elapsed_time/1000 AS last_elapsed_time_ms,
    qs.execution_count,
    qs.total_worker_time/1000 AS total_worker_time_ms,
    qs.total_worker_time/qs.execution_count/1000 AS average_worker_time_ms,
    qs.last_worker_time/1000 AS last_worker_time_ms,
    qs.last_execution_time,
    qs.total_logical_reads,
	qs.last_logical_reads,
    qs.total_logical_writes,
	qs.last_logical_writes
    --qp.query_plan
    FROM sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
        LEFT JOIN sys.databases db ON (qt.dbid = db.database_id)
    -- WHERE db.name='DatabaseName'
    -- ORDER BY qs.total_logical_reads DESC
    -- ORDER BY qs.total_logical_writes DESC
    -- ORDER BY qs.last_worker_time DESC -- CPU time (active)
    -- ORDER BY qs.last_elapsed_time DESC -- clock time (including locks, etc.)
    -- ORDER BY qs.total_worker_time DESC
	ORDER BY qs.total_elapsed_time DESC

…and for the same purpose the one for missing indexes (Please don’t add these indexes without further analysis of the overall impact!):

SELECT
	mid.statement
	  ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
	  'CREATE INDEX [missing_index_' + CONVERT (VARCHAR, mig.index_group_handle) + '_' + CONVERT (VARCHAR, mid.index_handle)
	  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
	  + ' ON ' + mid.statement
	  + ' (' + ISNULL (mid.equality_columns,'')
		+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
		+ ISNULL (mid.inequality_columns, '')
	  + ')'
	  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
	  migs.*, mid.database_id, mid.[object_id]
	FROM sys.dm_db_missing_index_groups mig
		INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
		INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
	WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
	ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

AppService: Setting a time-zone with a WEBSITE_TIME_ZONE App Setting (and many more)

By default, your AppServices run in UTC. There is an almost undocumented (or at least very little known) feature of KUDU which allows you forcing a specific time zone to your application. By adding specific Application Settings to your App Service (in Azure Portal) you can set following features:

  • WEBSITE_TIME_ZONE, e.g. Central Europe Standard Time (List of supported values)
  • SCM_TOUCH_WEBCONFIG_AFTER_DEPLOYMENT = 1/0 – does what it says (default is 1)
  • SCM_TRACE_LEVEL = 1..4 – you can get more detailed tracing by setting higher level (default is 1)
  • Build/GIT related options
  • Caching related options
  • Setting Node/NPN version
  • WEBSITE_LOAD_CERTIFICATES – Loading certificates
  • Low-level diagnostic switches
  • WEBSITE_DISABLE_SCM_SEPARATION – allows you to run your site and SCM (Kudu) in the same sandbox (and potentially save some resources) – unfortunately marked as legacy-obsolete and not supported any more
  • and many more

You can find complete list of features in Project KUDU Wiki.
 

Mocking a ConfigurationSection (or full configuration file)

In unit-tests you sometimes find yourself in a situation that you need to mock a configuration section. The ConfigurationSection class (or your inherited class) encapsulates nicely and there is no easy way how to break it’s read-only public interface.

You might consider abstracting the whole section and/or exposing it via some kind of adapter, but sometimes you don’t need to be 100% pure and still want get some basic test coverage.

There is an obscure but functional way how to mock the ConfigurationSection (or a whole configuration file if needed):

1. Create a fake configuration file in your test project

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<configSections>
<section name="mySection" type="MyConfigurationSection, MyAssembly" />
	</configSections>
	<mySection>
		<identities>
			<identity clientId="fake_id" name="FAKE_NAME" partitionName="FAKE_PARTITION" permissions="Full" />
		</identities>
	</mySection>
</configuration>

The file can be placed in any directory in test project. Right beside your test-class might be the right place.

2. Set the configuration file to be copied to build output

In Properties of the configuration file, set:

  • Build Action = Content
  • Copy to Output Directory = Copy always

3. Create a unit-test which uses the file to build a fake configuration

[TestClass]
public class MyServiceTests
{
	public TestContext TestContext { get; set; }

	[TestMethod]
	[DeploymentItem("PathInTestProject\\MyServiceTests_ScenarioName.config")]
	public void MyService_DoSomething_ScenarioName_ExpectedBehavior()
	{
		// arrange
		ExeConfigurationFileMap configFileMap = new ExeConfigurationFileMap()
		{
			ExeConfigFilename = Path.Combine(TestContext.DeploymentDirectory, "MyServiceTests_ScenarioName.config")
		};
		Configuration config = ConfigurationManager.OpenMappedExeConfiguration(configFileMap, ConfigurationUserLevel.None);
		MyConfiguarionSection mySection = config.GetSection("mySection") as MyConfiguarionSection ;

		var sut = new MyService(mySection);

		// act
		...
	}
}

The basics:

  • The ConfigurationManager.OpenMappedExeConfiguration() method allows you to use any configuration file you want.
  • The [DeploymentItem(...)] attribute copies the config file from build output to the “test deployment directory” (e.g. $(SolutionDir)\TestResults\Deploy_username 2017-09-29 22_02_39\Out\)
  • The TestContext.DeploymentDirectory contains the path whereas the TestContext property is automatically injected with appropriate instance of the test context.

 

 

Azure: Checking if a blob exists using its URI

Having only a storage account (CloudBlobClient) and a blob URI makes it a little bit tricky to check if the blob actually exists. To be able to use the ​​ICloudBlob.Exists()​ method, you have to have a blob-reference and there is no easy way to get it from URI.

If you just need to know if the blob exists, you can make a simple HTTP HEAD request to the URI with any HTTP client.

If you need the blob-reference for further use, there is a CloudBlobClient.GetBlobReferenceFromServer(Uri blobUri) method. As you call it, it hits the server with a HEAD request and throws an exception if the blob does not exist (so there is no chance to use the Exists method later).

You can catch this exception with a nice pattern-matching:

ICloudBlob blob;
try
{
    blob = blobClient.GetBlobReferenceFromServer(new Uri(url));
}
catch (Microsoft.WindowsAzure.Storage.StorageException ex)
            when ((ex.InnerException is System.Net.WebException wex)
                    && (wex.Response is System.Net.HttpWebResponse httpWebResponse)
                    && (httpWebResponse.StatusCode == System.Net.HttpStatusCode.NotFound))
{
    // blob does not exist, do whatever you need here
    return null;
}
// further code able to use the blob-reference

Visual Studio: Keyboard shortcuts to navigate to next/previous member in code

In Resharper there is a well known keyboard shortcut to navigate to next (Alt+Down) or previous (Alt+Up) member in code.

In plain Visual Studio (at least in VS2017+) you are able to add these shortcuts easily. There are Edit.NextMethod and Edit.PreviousMethod commands which used to be available only for Visual Basic editor. Nowadays, as C# and Visual Basic are widely aligned, they are fully functional in C# code as well:

2017-09-26_10-12-19

I will probably go for Ctrl+Up, Ctrl+Down which are bound to Scroll-Up/Down by default.