Measuring impact of “optimize for ad hoc workloads” via CACHESTORE_SQLCP

The cache store “CACHESTORE_SQLCP” represents cached ad-hoc query plans, server-side cursors and prepared statements.   One way to gather memory allocation values for this specific cache store is by using the following query: SELECT single_pages_kb, multi_pages_kb FROM sys.dm_os_memory_clerks WHERE type = ‘CACHESTORE_SQLCP’ You can also see allocations to CACHESTORE_SQLCP via DBCC MEMORYSTATUS (example output below for…

0

ARP Caching and SQL Server Failover Cluster install failures

Now I’m definitely not a networking guy, so even the more basic networking concepts fill me with wonder.  One area I learned about recently with regards to the following scenario was related to Address Resolution Protocol (ARP) caching.    Consider the following scenario… You’re performing a SQL Server Failover Cluster installation and it is failing…

4

Forced Parameterization with Filtered Indexes

Consider the following scenario: You have a database that has PARAMETERIZATION FORCED enabled. You have a table using a filtered index. Here is a demo setup so you can follow along: — Create demo database USE [master] GO CREATE DATABASE [FI_PF_Error_Demo]; GO   — Set new database to forced parameterization ALTER DATABASE [FI_PF_Error_Demo] SET PARAMETERIZATION…

2