Tag: Query Tuning

This OVERPOWERED Hint Will Break NOLOCK Hints In SQL Server

Posted on by Erik Darling

G4M3R

CREATE TABLE
    dbo.view_me
(
    id int NOT NULL
);
GO 

CREATE VIEW 
    dbo.viewed
AS 
SELECT
    vm.*
FROM dbo.view_me AS vm WITH(READCOMMITTED);
GO 

SELECT
    v.*
FROM dbo.viewed AS v WITH(NOLOCK);
GO

Msg 4138, Level 16, State 1, Line 22

Conflicting locking hints are specified for table “dbo.view_me”. This may be caused by a conflicting hint specified for a view.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Updates To sp_HumanEvents

Posted on by Erik Darling

Touchy, Touchy

I’ve made some slight tweaks to sp_HumanEvents.

One of them fixes a bug with view creation when you use a custom schema. The rest are minor tweaks and fixes that you (hopefully won’t notice one bit), but make me feel a little bit better about the state of the code.

You can learn how to use the proc, and download the latest version over here.

I took a look through SQL Server 2022’s new Extended Events, and haven’t seen anything in there that would warrant the amount of work it takes to add something new just yet. Maybe at some point, I suppose.

Some folks had asked about adding lightweight query profiling etc. as an option, but it still doesn’t return the kind of details that make it useful for query tuning.

I mean, I guess if you believe in memes it’s okay, but… Maybe you should stop that.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Updates To sp_PressureDetector

Posted on by Erik Darling

All Good Things

I haven’t run into a bug with this thing in just about… Well, ever. This proc has been pretty solid.

As I use it though, especially in front of clients, I find myself wanting some additional data points all in one place. With this release, I’ve added a few small things.

  1. Total physical memory in the server (not just max server memory)
  2. CPU details (NUMA nodes, schedulers; if they’re off line, etc.)
  3. A debug mode for the dynamic SQL

The CPU details column is an XML clickable that will look something like this:

<cpu_details>
  <offline_cpus>0</offline_cpus>
  <cpu_count>8</cpu_count>
  <hyperthread_ratio>8</hyperthread_ratio>
  <softnuma_configuration_desc>OFF</softnuma_configuration_desc>
  <socket_count>1</socket_count>
  <cores_per_socket>4</cores_per_socket>
  <socket_count>1</socket_count>
</cpu_details>

This is an example from my test VM. Please don’t think less of me.

You can find out more, and download the script over here.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

New Wait Stats In SQL Server 2022

Posted on by Erik Darling

Patiently Waiting

This is just a quick post to list out new wait stats in SQL Server 2022. How many will be useful, time will tell.

Some waits that I think might be interesting:

  • CXSYNC_CONSUMER (Currently in Azure)
  • CXSYNC_PORT (Currently in Azure)
  • PARALLEL_DB_SEEDING_SEMAPHORE
  • PLPGSQL

It looks like maybe automatic seeding for Availability Groups is getting the ability to process multiple databases at once, and we’re getting some parallel query waits that used to be Azure only.

I’m really scratching my head about PLPGSQL though. That’s the “programming language” that Postgres supports, which is sort of like what Oracle supports.

Time will tell!

+-----------------------------------------------------+
|                      wait_type                      |
+-----------------------------------------------------+
| ARC_IMDS_RESOURCE_INFO                              |
| BABYLON_POLICY_UPDATE                               |
| BABYLON_PULL_TASK                                   |
| BACKUP_BACKUP_MGR_MIHYBRIDINFO_RWLOCK               |
| BACKUP_LOG_IO_STALL                                 |
| BLOB_LIST_LIMIT_IO_REQUESTS                         |
| BLOB_LIST_RWLOCK                                    |
| BUFFERPOOL_SCAN                                     |
| CDC_SCHEDULERCACHE_ACCESS                           |
| CDC_THROTTLE_LOG_RATE_LOG_SIZE                      |
| CMEMDETOUR                                          |
| COLLECTOR_VIEW_LIST                                 |
| COLUMNSTORE_CSI_CACHE                               |
| COSMOSDB_INIT_MUTEX                                 |
| CXSYNC_CONSUMER                                     |
| CXSYNC_PORT                                         |
| DATA_EXPORT_COMPLETION_SYNC                         |
| DIFF_BACKUP_SELF_THROTTLING                         |
| DIRECTORY_CONTENT_LIST_CLERK                        |
| DIRTY_PAGE_THROTTLING                               |
| DISPATCHER_JOIN                                     |
| DTC_INFO_DMV                                        |
| DTC_PRECOMMIT                                       |
| DTCNEW_DWSHELLDB_PROPERTIES                         |
| EDC_DOPP_BACKGROUND                                 |
| EDC_DOPP_LOCK                                       |
| EDC_INIT_RWLOCK                                     |
| EMC_EXEC                                            |
| EMC_FABRIC_RESOLVE_SERVICE_URI                      |
| EMC_INIT_MUTEX                                      |
| EXECUTED_REQ_TABLE_STATE_RWLOCK                     |
| EXTERNAL_SCRIPT_CREATE_CERTIFICATE                  |
| EXTERNAL_SERVICE_BLOB_MESSAGE                       |
| EXTERNAL_SERVICE_CONNECTION_CLOSE                   |
| EXTGOV_ATTR_SYNC_BACKGROUND                         |
| FABRIC_ENDPOINT_SYNC_EVENT                          |
| FCB_DISKSPACE_COUNTERS_RWLOCK                       |
| FIDO_AUTOSTATISTICS_TASK                            |
| FIDO_CLIENT_STARTUP                                 |
| FOREIGN_FILE_VALIDATION                             |
| FULL_BACKUP_SELF_THROTTLING                         |
| GLOBAL_QUERY_EXTRACTOR_EXECUTE                      |
| HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO               |
| HADR_THROTTLE_LOG_RATE_SLO_DOWNGRADE                |
| HADR_THROTTLE_REFRESH_MAX_SIZE                      |
| HTTP_EXTERNAL_CONNECTION                            |
| HTTP_EXTERNAL_CONNECTION_ALLOW_LIST                 |
| HTTP_EXTERNAL_CONNECTION_IPV4_BLOCK_LIST            |
| INDEX_BUILD_BUCKETIZATION_BARRIER                   |
| INDEX_BUILD_BUCKETIZATION_INFO_MAP_SYNC             |
| INDEX_BUILD_BUCKETIZATION_INFO_SYNC                 |
| LCK_REQ_TSK_PROXY                                   |
| LEDGER_BLOCK_GENERATION                             |
| LEDGER_TRUNCATION                                   |
| LOCK_SAFEMODE                                       |
| LOCK_UPDATE                                         |
| LOGPOOL_CONSUMER_DELETABLE                          |
| LSN_LOC_MAP_LOCK                                    |
| MANAGED_DISKS_CONFIGURATION                         |
| MULTITHREADED_VERSION_CLEANUP_WAIT_WORK             |
| NATIVE_SHUFFLE_OPEN_HANDLE                          |
| ORDLOCK_POPULATE_SYNC                               |
| PARALLEL_BPOOL_DEALLOCATION_WORKER                  |
| PARALLEL_DB_SEEDING_SEMAPHORE                       |
| PLPGSQL                                             |
| POLARIS_TSQL_TASK                                   |
| POPULATE_LOCK_ORDINALS                              |
| PREEMPTIVE_AAD_HTTP_EVENT_WAIT                      |
| PREEMPTIVE_COSMOSDB                                 |
| PREEMPTIVE_EMC                                      |
| PREEMPTIVE_FILE_MAPPING                             |
| PREEMPTIVE_HTTP_EXTERNAL_CONNECTION_EVENT_WAIT      |
| PREEMPTIVE_OS_GETQUEUEDCOMPLETIONSTATUS             |
| PREEMPTIVE_OS_PDH_WMI_QUERY                         |
| PREEMPTIVE_PREDICT_API                              |
| PREEMPTIVE_REPORTING                                |
| PREEMPTIVE_RG_HTTP                                  |
| PREEMPTIVE_SNI_SOCKET_BIND                          |
| PREEMPTIVE_SNI_SOCKET_LISTEN                        |
| PREEMPTIVE_SYNAPSESTREAMING_HTTP_EVENT_WAIT         |
| PREEMPTIVE_XCS_SNAPPY                               |
| PREEMPTIVE_XCS_THRIFT                               |
| PRU_PAGE_LSN_CACHE_LOCK                             |
| PVS_TRACK_PAGES_MUTEX                               |
| PWAIT_AUTO_START_AUDIT_SESSIONS                     |
| PWAIT_DBCC_FREEZEIO_MUTEX                           |
| PWAIT_DBCC_THAWIO_MUTEX                             |
| PWAIT_EXTERNAL_SERVICE_HUB                          |
| PWAIT_EXTERNAL_SERVICE_SEND_MESSAGE                 |
| PWAIT_FCS_MD_READ_AHEAD                             |
| PWAIT_FIDO_INDEXSTORE_CONNECTIONS_MANAGER_HASHTABLE |
| PWAIT_LATCH_ONLY                                    |
| PWAIT_PREEMPTIVE_OS_AUTHENTICATEDWEBCALL            |
| PWAIT_PREEMPTIVE_OS_AUTHENTICATIONTOKEN             |
| PWAIT_PREEMPTIVE_OS_CRYPTOPENSTORAGEPROVIDER        |
| PWAIT_PREEMPTIVE_OS_VSMATTESTATIONSERVICE           |
| PWAIT_PS_RBPEX_HOT_PAGES_RWLOCK                     |
| PWAIT_RBIO_IC_ACQUIRE_PAYLOAD                       |
| PWAIT_SBS_API_STATS_PUBLISH                         |
| PWAIT_SBS_IOAPI_STATS_PUBLISH                       |
| PWAIT_SYNAPSE_LINK_CAPTURE                          |
| PWAIT_SYNAPSE_LINK_COMMIT                           |
| PWAIT_SYNAPSE_LINK_DATA_EXPORT_SESSION              |
| PWAIT_SYNAPSE_LINK_DB_CLEANUP                       |
| PWAIT_SYNAPSE_LINK_END_HISTORY_SESSION              |
| PWAIT_SYNAPSE_LINK_GET_CURRENT_DB_LSN               |
| PWAIT_SYNAPSE_LINK_GET_DB_LIST,                     |
| PWAIT_SYNAPSE_LINK_GET_TABLE_HASHTABLE,             |
| PWAIT_SYNAPSE_LINK_LZN_API_CALL                     |
| PWAIT_SYNAPSE_LINK_MEM_CAP_THROTTLE                 |
| PWAIT_SYNAPSE_LINK_POPULATE_METADATA                |
| PWAIT_SYNAPSE_LINK_PUBLISH                          |
| PWAIT_SYNAPSE_LINK_UPDATE_TABLE_STATUS              |
| PWAIT_TOAD_CELL_ZONE                                |
| PWAIT_TOAD_DELTA_FORCE_ZONE                         |
| PWAIT_TOAD_OCCI_ZONE                                |
| PWAIT_TOAD_STAR_CELL_ZONE                           |
| PWAIT_TOAD_TUNING_ZONE                              |
| PWAIT_VLDB_PLANNED_FAILOVER_FORWARDER_THROTTLING    |
| PWAIT_VLDB_PLANNED_FAILOVER_START_THROTTLING        |
| PWAIT_VLDB_PLANNED_FAILOVER_STOP_THROTTLING         |
| PWAIT_XIO_REQUEST_IN_PROGRESS_LOCK                  |
| PWAIT_XLOG_POOL_EVICT_SLEEP                         |
| RBIO_AWAIT_RESPONSE                                 |
| RBIO_COMM_RETRY                                     |
| RBIO_COMM_UNINITIALIZE                              |
| RBIO_COMPLETE_LOG_READ                              |
| RBIO_CONNECTION_MGR                                 |
| RBIO_DB_RESTART                                     |
| RBIO_DB_TRANS_PRIMARY                               |
| RBIO_DBTRANSPRIMARY_SYNC                            |
| RBIO_FCB_DEFERRED_IO                                |
| RBIO_FCB_DEFERRED_IO_FN                             |
| RBIO_INITIALIZE_MUTEX                               |
| RBIO_PS_ACTOR_COLLECTION_RWLOCK                     |
| RBIO_RG_DESTAGE                                     |
| RBIO_RG_GEOREPLICA                                  |
| RBIO_RG_LOCALDESTAGE                                |
| RBIO_RG_MIGRATION_TARGET                            |
| RBIO_RG_REPLICA                                     |
| RBIO_RG_STORAGE                                     |
| RBIO_RG_STORAGE_CHECKPOINT                          |
| RBIO_UNINITIALIZE                                   |
| RBPEX_CHANGE_FILE_SIZE_MUTEX                        |
| RBPEX_CREATESNAPSHOT_RETRY                          |
| RBPEX_WRITEBEHIND_DB_STATE                          |
| RBPEXSHRINKTASK_SHUTDOWN                            |
| REPORTING_EXEC                                      |
| RG_MANAGER_VHD_GROWTH                               |
| RG_SERVER_CONFIGS                                   |
| ROW_GROUP_POPULATION                                |
| SBS_CONTEXT                                         |
| SBS_LRU_EVICTION                                    |
| SEEDING_COMPLETED_MUTEX                             |
| SEEDING_SELF_THROTTLING                             |
| SESSION_MGR                                         |
| SLEEP_RBPEXSHRINKTASK                               |
| SLEEP_SAFEMODE                                      |
| SOS_RG_MEM_TARGET_LOCK                              |
| SP_RESOLVE_DEFERRED                                 |
| SQLPAL_PREEMPTIVE_WAIT                              |
| SQP_STATS_REPORTING                                 |
| START_BACKGROUND_TASK_MUTEX                         |
| STREAMING_SERVICE_RESTART_FINISHED                  |
| STREAMING_SERVICE_SEND_KILL_PROCESS                 |
| STRIPE_META_UPDATE                                  |
| SYNAPSELINK_CAPTURE_JOBTASK_ACCESS                  |
| SYNAPSELINK_COMMIT_JOBTASK_ACCESS                   |
| SYNAPSELINK_FAILBATCH_ACCESS                        |
| SYNAPSELINK_PUBLISH_JOBTASK_ACCESS                  |
| SYNAPSELINK_SNAPSHOT_JOBTASK_ACCESS                 |
| TELEMETRY_SNAP                                      |
| THROTTLE_LOG_RATE_LOG_STORAGE                       |
| TOAD_AUTOSTATISTICS_ZONE                            |
| TOAD_DELETEBITMAP_ZONE                              |
| TOAD_DISCOVERY                                      |
| TOAD_FUTURE                                         |
| TOAD_QUEUE                                          |
| TOAD_RESOURCE_SEMAPHORE                             |
| TOAD_SHUTDOWN                                       |
| VERSION_LEASE_HASH_LOCK                             |
| VLDB_DUMP_LOG_LOCK                                  |
| VLDB_SNAPSHOT_MUTEX                                 |
| WAIT_AE_KEYADD                                      |
| WAIT_AETM_CRITICAL_SECTION                          |
| WAIT_AETM_ENCLAVE_WORKER_SLEEP                      |
| WAIT_AETM_HOST_WORKER_SLEEP                         |
| WAIT_DELTA_CACHE                                    |
| WAIT_DW_TX_EXTERNALIZATION_IO_COMPLETION            |
| WAIT_FCS_MD_RESOLVE                                 |
| WAIT_FIDO_GLMS_ASYNC_WORKER                         |
| WAIT_FIDO_GLMS_SYNC                                 |
| WAIT_FIDO_GLMS_UT                                   |
| WAIT_FOR_MS_POLL                                    |
| WAIT_FULLTEXT_CRAWL_MANAGER                         |
| WAIT_GC_IO_COMPLETION                               |
| WAIT_GLM_CONTROLLER                                 |
| WAIT_GLM_DB                                         |
| WAIT_GLM_DEK_LIST                                   |
| WAIT_GLM_SYNC_CLIENT                                |
| WAIT_GLMS_ACCESS                                    |
| WAIT_GLMS_LOG                                       |
| WAIT_GLMS_LOG_BLOCK_IO                              |
| WAIT_INDEXSTORE_COMPUTE_PARTITION_BUCKETS           |
| WAIT_INDEXSTORE_LIMIT_REQUESTS                      |
| WAIT_LCKMGR                                         |
| WAIT_LM_CREATE                                      |
| WAIT_LOG_REPLICA_MGR_HASH_LOCK                      |
| WAIT_LOG_REPLICA_ROLE_STABILITY_LOCK                |
| WAIT_LOG_REPLICA_WRITE_LEASE_PROPERTY_HASH_LOCK     |
| WAIT_ODBC                                           |
| WAIT_PHYSICAL_CATALOG                               |
| WAIT_RBAC_PROVIDER                                  |
| WAIT_RBAC_SESSION_AUDIT                             |
| WAIT_RBAC_SESSIONS_AUDIT                            |
| WAIT_RBPEX_WRITEBEHIND_CKPT_CONSISTENCY_LOCK        |
| WAIT_RETENTION_POLICY                               |
| WAIT_RM_RBIOCONNECTION_INIT                         |
| WAIT_SEQUENCE                                       |
| WAIT_SYNC_LAG_PARTNERS_LIST                         |
| WAIT_TRANSPORT                                      |
| WAIT_TSQL_TASK                                      |
| WAIT_VLF_IO_TRACKER_DRAIN_IO                        |
| WAIT_XCS_LOCATOR_FETCH                              |
| WAIT_XLOG_REPLICA_BG_TASK_LOCK                      |
| WMI_REGISTRATION                                    |
| XDB_PKG_LAUNCHER_CONNECTION                         |
| XE_LIVE_TARGET_MUTEX                                |
| XE_PREDICATE_HEAP_ALLOC                             |
| XE_PREDICATE_HEAP_FREE                              |
| XE_RING_TARGET_MUTEX                                |
| XE_SQL_TEXT_HEAP_ALLOC                              |
| XE_SQL_TEXT_HEAP_FREE                               |
| XE_SQL_TEXT_PREDICATE                               |
| XFILE_CACHE_XACT                                    |
| XFILE_DISPATCH                                      |
| XFILE_OBJECT_POOL                                   |
| XFILE_TASK_PROXY_ABORT                              |
| XLOG_BGTHREAD                                       |
| XLOG_BROKER_UNLINKING_IN_PROGRESS                   |
| XLOG_BROKER_WAIT_PAGE                               |
| XLOG_BROKER_WAITFULL                                |
| XLOG_BROKER_WAITMAXALLOWED                          |
| XLOG_EOL_REQUEST_NOTIFICATION                       |
| XLOG_GAPFILLERTHREAD                                |
| XLOG_LC_FWD_SEEDING                                 |
| XLOG_LC_REVERSE_SEEDING                             |
| XLOG_LC_SEEDING_VDL_ADVANCE                         |
| XLOG_POOL_FILLER_SLEEP                              |
| XLOG_POOL_QUERY_WAIT                                |
| XLOG_POOL_SHUTDOWN                                  |
| XLOG_SPACEMGR_INITIALIZE                            |
| XLOG_TOSFILE_GET_ITER_PROXY                         |
| XLOGREAD_SIGNAL                                     |
+-----------------------------------------------------+

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Updates to sp_QuickieStore

Posted on by Erik Darling

Just Visiting

I made some updates to sp_QuickieStore recently, which you can download here.

The main updates to the code are to:

  • Add support for SQL Server 2022 views
  • Add the ability to search by query hash, plan hash, and SQL handle

The SQL Server 2022 stuff isn’t important just yet, but… Hey, maybe someday.

The new search functionality is really important though, at least for how I use sp_QuickieStore much of the time. Often, you’ll find hashes and handles in other parts of the database:

  • Plan cache
  • Deadlock XML
  • Blocked process report
  • Query plans

There’s still no good way for you to search Query Store by anything. Not plan or query id, not query text, not object names. Nothing.

Don’t worry, I’m here for you.

Some other minor updates were to:

  • Improve the help section
  • Improve code comments throughout
  • Remove the filter to only show successful executions (sometimes you need to find queries that timed out or something)
  • If you filter on any hash or handle, I’ll display that in the final output so they’re easy to identify
  • Replace TRY_CONVERT with TRY_CAST, which throws errors in fewer circumstances

That’s about all the stuff you need to know about. Aside from that, all my changes were slight logical errors or plumbing to implement the new features.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

SQL Server 2017 CU 30: The Real Story With SelOnSeqPrj Fixes

Posted on by Erik Darling

I am a heading


Thanks for watching! Demo scripts below.

Demo Scripts

USE StackOverflow2013;
EXEC dbo.DropIndexes;
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
GO 


CREATE INDEX 
   chunk 
ON dbo.Posts 
    (OwnerUserId, Score DESC) 
INCLUDE  
    (CreationDate, LastActivityDate)
WITH
    (MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
GO 

CREATE OR ALTER VIEW 
    dbo.PushyPaul
WITH SCHEMABINDING
AS
    SELECT 
        p.OwnerUserId,
        p.Score,
        p.CreationDate,
        p.LastActivityDate,
        PostRank = 
            DENSE_RANK() OVER
            ( 
               PARTITION BY 
                  p.OwnerUserId 
               ORDER BY     
                  p.Score DESC 
            )
    FROM dbo.Posts AS p;
GO 

SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656;
GO 

CREATE OR ALTER PROCEDURE 
    dbo.StinkyPete 
(
    @UserId int
)
AS 
SET NOCOUNT, XACT_ABORT ON;
BEGIN
    SELECT 
        p.* 
    FROM dbo.PushyPaul AS p
    WHERE p.OwnerUserId = @UserId;
END;
GO 

EXEC dbo.StinkyPete 
    @UserId = 22656;



/*Start Here*/

ALTER DATABASE 
    StackOverflow2013 
SET PARAMETERIZATION SIMPLE;

DBCC TRACEOFF
(
    4199, 
    -1
);

ALTER DATABASE SCOPED CONFIGURATION 
    SET QUERY_OPTIMIZER_HOTFIXES = OFF;


SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656
AND   1 = (SELECT 1); /*Avoid trivial plan/simple parameterization*/


/*Let's cause a problem!*/
ALTER DATABASE 
    StackOverflow2013 
SET PARAMETERIZATION FORCED;

SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656
AND   1 = (SELECT 1); /*Avoid trivial plan/simple parameterization*/


/*Can we fix the problem?*/
DBCC TRACEON
(
    4199, 
    -1
);


SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656
AND   1 = (SELECT 1); /*Avoid trivial plan/simple parameterization*/


/*That's kinda weird...*/
DBCC FREEPROCCACHE;


SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656
AND   1 = (SELECT 1); /*Avoid trivial plan/simple parameterization*/


/*Turn Down Service*/
DBCC TRACEOFF
(
    4199, 
    -1
);

SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656
AND   1 = (SELECT 1); /*Avoid trivial plan/simple parameterization*/


/*Okay then.*/


/*I'm different.*/
ALTER DATABASE SCOPED CONFIGURATION 
    SET QUERY_OPTIMIZER_HOTFIXES = ON;


SELECT 
    p.* 
FROM dbo.PushyPaul AS p
WHERE p.OwnerUserId = 22656
AND   1 = (SELECT 1); /*Avoid trivial plan/simple parameterization*/



/*Cleanup*/
ALTER DATABASE 
    StackOverflow2013 
SET PARAMETERIZATION SIMPLE;

ALTER DATABASE SCOPED CONFIGURATION 
    SET QUERY_OPTIMIZER_HOTFIXES = OFF;

DBCC TRACEOFF
(
    4199, 
    -1
);

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

SQL Server 2017 CU 30 Doesn’t Actually Fix The Problem With Views And Parameters

Posted on by Erik Darling

I am a heading


In the release notes for SQL Server 2017 CU30, there’s a note that it fixes a problem where parameters can’t be pushed passed Sequence Project operators:

“In Microsoft SQL Server 2017, running parameterized queries skips the SelOnSeqPrj rule. Therefore, pushdown does not occur.” But it doesn’t actually do that.

Paul White Original Post: The Problem with Window Functions and Views

The Problem In The Plan

Here are the good and bad plans, comparing using a literal value vs. a parameterized value:

SQL Server Query Plan
dunksville
  • In the plan with a literal value, the predicate is applied at the index seek, and the filtering is really quick.
  • In the plan with a parameterized value, the index is scanned, and applied at a filter way later in the query plan.

This is where the SelOnSeqPrj rule comes in: The parameter can’t be pushed past the Sequence Project operator like the literal value can.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Things SQL Server vNext Should Address: How Did I Do?

Posted on by Erik Darling

Mom I Did It

A while back, I wrote a bunch of posts about things I’d like to see vNext take care of. In this post, since it’s Friday and I don’t wanna do anything, will round those up and cover whether or they made it in or not.

Well, maybe I’ll need to update the list for future releases of SQL Server 2022.

Hmpf.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

SQL Server 2022 DOP Feedback: Related Extended Events

Posted on by Erik Darling

Short Round

Sort of on the heels of yesterday’s post, here are some Extended Events related to the DOP Feedback feature new to SQL Server 2022.

Here’s the complete text of a session to collect all of the related events that I’ve noticed so far:

CREATE EVENT SESSION 
    dop_feedback 
ON SERVER 
ADD EVENT 
    sqlserver.dop_feedback_eligible_query
    (
        ACTION(sqlserver.sql_text)
    ),
ADD EVENT 
    sqlserver.dop_feedback_provided
    (
        ACTION(sqlserver.sql_text)
    ),
ADD EVENT 
    sqlserver.dop_feedback_validation
    (
        ACTION(sqlserver.sql_text)
    ),
ADD EVENT 
    sqlserver.dop_feedback_reverted
    (
        ACTION(sqlserver.sql_text)
    )
ADD TARGET package0.event_file(SET filename=N'dop_feedback')
WITH 
(
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE = ON
);

There is one additional event in the debug channel called maxdop_feedback_received, but the contents of it don’t appear immediately actionable.

Defining Moments

Here are the definitions for each of the events above:

  • dop_feedback_eligible_query: Reports when a query plan becomes eligible for dop feedback
  • dop_feedback_provided: Reports DOP feedback provided data for a query
  • dop_feedback_validation: Reports when the validation occurs for the query runtime stats against baseline or previous feedback stats
  • dop_feedback_reverted: This reports when a DOP feedback is reverted

Fairly straightforward, here. Also seems like a decent set of events that you’d wanna have in place.

Thanks, Microsoft.

MAPDOP

The map values for each of these events is also available:

+-----------------------+---------+--------------------------------------+
|         name          | map_key |              map_value               |
+-----------------------+---------+--------------------------------------+
| dop_calculation_stage |       0 | SetMaxDOP                            |
| dop_calculation_stage |       1 | SetTraceflag                         |
| dop_calculation_stage |       2 | CalculateBasedOnAvailableThreads     |
| dop_calculation_stage |       3 | PostCalculate                        |
| dop_feedback_state    |       0 | NotAnalyzed                          |
| dop_feedback_state    |       1 | NotEligible                          |
| dop_feedback_state    |       2 | InAnalysis                           |
| dop_feedback_state    |       3 | NoRecommendation                     |
| dop_feedback_state    |       4 | AnalysisStoppedDueToThrottling       |
| dop_feedback_state    |       5 | AnalysisStoppedDueToMaxResetsReached |
| dop_feedback_state    |       6 | AnalysisStoppedMinimumDOP            |
| dop_feedback_state    |       7 | PendingValidationTest                |
| dop_feedback_state    |       8 | VerificationRegressed                |
| dop_feedback_state    |       9 | RegressionDueToAbort                 |
| dop_feedback_state    |      10 | Stable                               |
| dop_statement_type    |       1 | Select                               |
| dop_statement_type    |       2 | Insert                               |
| dop_statement_type    |       3 | Update                               |
| dop_statement_type    |       4 | Delete                               |
| dop_statement_type    |       5 | Merge                                |
+-----------------------+---------+--------------------------------------+

Why two are zero-based and one is not is beyond what I can explain to you, here.

Perhaps that will be addressed in a future release.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

PASS Data Community Summit 2022: Come See My Session, The SQL Server Performance Tasting Menu

Posted on by Erik Darling

Workin’

On top of my precon, The Professional Performance Tuning Blueprint, I’ve also got a regular session selected!

The SQL Server Performance Tasting Menu:

You’re a DBA or Developer, and you’ve been using SQL Server for a few years.

You know there are different ways to make queries faster, but you’re not sure when to use them.

I’m Erik Darling, and I’ll be your sommelier for the evening.

Over several courses of delicious demos, I’ll show you the types of performance problems different tuning techniques pair well with, and which ones to avoid.

When we’re done, you’ll understand exactly what patterns to look for when you’re troubleshooting slow queries, and how to approach them.

You’ll have the secret recipe for gourmet queries.

Dates And Times

The PASS Data Community Summit is taking place in Seattle November 15-18, 2022 and online.

You can register here, to attend online or in-person. I’ll be there in all my fleshy goodness, and I hope to see you there too!

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.