SQL Server – Page 62 – Darling Data

Be Careful With Memory Recommendations When Installing SQL Server Standard Edition

Posted on July 29, 2022July 12, 2022 by Erik Darling

Friday I’m Not Here

This is a short post to warn you about the memory recommendation tab in the SQL Server installer.

Let’s say you’re doing the smart thing and giving your Standard Edition install 192 GB of RAM:

Why is this smart? Because Standard Edition’s 128GB memory limit is only on the buffer pool. SQL Server can use memory over and above that for other stuff, like:

Query memory grants
Compressed backups
Other caches/Lock memory, etc.

The problem is that the installer’s recommendation only reflects the buffer pool limit. It doesn’t actually consider that memory can get used over that, and you end up with this recommendation:

That’s 128GB exactly, which is daft to the back teeth.

The number I usually start with here is 184320, which is 180GB. That gives Windows about 12GB to work with.

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.

Stored Procedures vs sp_executesql In SQL Server: Is One Better Than The Other?

Posted on July 28, 2022July 12, 2022 by Erik Darling

Basically

I get this question a lot while working with clients, largely in a couple specific contexts:

Me telling someone they need to use dynamic SQL in a stored procedure
Applications sending over parameterized SQL statements that are executed with sp_executesql

Often, the dynamic SQL recommendation comes from needing to deal with:

IF branching
Parameter sensitivity
Optional parameters
Local variables

Even in the context of a stored procedure, these things can really suck performance down to a sad nub.

But The Code

Now, much of the SQL generated by ORMs terrifies me.

Even when it’s developer misuse, and not the fault of the ORM, it can be difficult to convince those perfect angels that the query their code generated is submaximal.

Now, look, simple queries do fine with an ORM (usually). Provided you’re:

Paying attention to indexes
Not using long IN clauses
Strongly typing parameters
Avoiding AddWithValues

You can skate by with your basic CRUD stuffs. I get worried as soon as someone looks at an ORM query and says “oh, that’s a report…” because there’s no way you’re generating reasonable reporting queries with an ORM.

Procedural Drama

The real upside of stored procedures isn’t stuff like plan reuse or caching or 1:1 better performance. A single parameterized query run in either context will perform the same, all things considered.

Where they shine is with additional flexibility in tuning things. Rather than one huge query that the optimizer has to deal with, you can split things up into more manageable chunks.

You also have quite a bit more freedom with various hints, trace flags, query rewrites, isolation levels, etc.

In other words: eventually your query needs will outgrow your ORMs ability to generate optimal queries.

Until then, use whatever you’re able to get your job done with.

Thanks for reading!

Going Further

SQL Server 2022: Database Scoped Configurations And Feature Accessibility

Posted on July 27, 2022July 11, 2022 by Erik Darling

Overlooked

In my quest to tell you about new stuff in SQL Server 2022, I forgot to take a look at new things in sys.database_scoped_configurations.

It turns out that’s important, because some stuff has to be turned on if you want it to work. Here’s a list of new database scoped configurations for SQL Server 2022:

+------------------+-----------------------------------------------+-------+------------------+
| configuration_id |                     name                      | value | is_value_default |
+------------------+-----------------------------------------------+-------+------------------+
|               29 | ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY       |     0 |                1 |
|               31 | CE_FEEDBACK                                   |     1 |                1 |
|               37 | DOP_FEEDBACK                                  |     1 |                0 |
|               26 | DW_COMPATIBILITY_LEVEL                        |     0 |                1 |
|               27 | EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS         |     1 |                1 |
|               34 | MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT        |     1 |                1 |
|               33 | MEMORY_GRANT_FEEDBACK_PERSISTENCE             |     1 |                1 |
|               35 | OPTIMIZED_PLAN_FORCING                        |     1 |                0 |
|               28 | PARAMETER_SENSITIVE_PLAN_OPTIMIZATION         |     1 |                1 |
|               25 | PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES |  1440 |                1 |
+------------------+-----------------------------------------------+-------+------------------+

Some stuff is on by default, some stuff isn’t. You might notice DOP_FEEDBACK and OPTIMIZED_PLAN_FORCING are both off by default, here (even though I turned them on).

ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;

Invitation Only

You might think that Microsoft is so excited about new features (especially in CTP builds) that they want them to be easy to preview and test by people who care enough to do that stuff.

No so much, it turns out.

Heck, I’ve seen several presentations that said Query Store would be on by default in new databases created on SQL Server 2022 instances, but I haven’t had that happen yet and that’s not even a new feature.

Perhaps, like the daftly-hidden Azure Nonsense© in the installer, this will be changed in a future version, but no one tells me anything.

Anyway, not much here beyond this. Mind your database scoped database configurations when you’re testing out new features. Also make sure to check for extended events that help you track down if/when heuristics for those features kick in.

Thanks for reading!

Going Further

This OVERPOWERED Hint Will Break NOLOCK Hints In SQL Server

Posted on July 26, 2022July 8, 2022 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

Updates To sp_HumanEvents

Posted on July 25, 2022July 4, 2022 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

Updates To sp_PressureDetector

Posted on July 22, 2022July 4, 2022 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.

Total physical memory in the server (not just max server memory)
CPU details (NUMA nodes, schedulers; if they’re off line, etc.)
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

New Wait Stats In SQL Server 2022

Posted on July 21, 2022July 2, 2022 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

Updates to sp_QuickieStore

Posted on July 20, 2022July 16, 2022 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

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

Posted on July 19, 2022July 17, 2022 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

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

Posted on July 18, 2022July 16, 2022 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:

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!