SQLServerBang

  According to MSDN article, 'DISABLE_PARAMETER_SNIFFING' instructs Query Optimizer to use average data distribution while compiling a query with one or more parameters. This instruction makes the query plan independent on the parameter value that was first used when the query was compiled. This hint name is equivalent to  trace flag  4136 or  Database Scoped Configuration  setting PARAMETER_SNIFFING = OFF.     Looks like it's a pretty good hint, However, it doesn't means you can resolve all parameter sniffing issue by using this query hint.   Actually,  the sentence 'Query Optimizer to use  average data distribution  while compiling a query with one or more parameters' is not 100% correct. It really depends on what symbol you used in the where clause.   'DISABLE_PARAMETER_SNIFFING' is a replacement of variable, and it has same effect as 'OPTIMIZE FOR UNKNOWN' , These three have exactly same effect.  If you are not familiar with selectivity of va

SQL Server does not sniff for variable, it just simply uses the fixed value.   I'm going to use AdventureWorks 2019 in this post. --------------------Please run this script--------------- use AdventureWorks2019 go IF exists(select 1 from sys.tables where name='SalesOrderDetail' and schema_id=schema_id('dbo'))       drop table SalesOrderDetail go select * into SalesOrderDetail from [Sales].[SalesOrderDetail] go create statistics iProductID ON SalesOrderDetail(productid) with fullscan go dbcc traceon(3604,2363)---trace flag 2363 displays more detail about the selectivity go --------------------Please run this script---------------     Equality(=): all density DECLARE @pid INT = 0 SELECT * FROM SalesOrderDetail WHERE ProductID = @pid     456= 'All density'*card =0.003759399*121317(note, if there new rows inserted or deleted, you have to replace the 121317 with current card)   DBCC execution completed. If DBCC printed error messages, contact your system administ

The threshold of temp table in stored procedure is little bit tricky, it doesn’t exactly follow the regular threshold. First of all, you can’t use the modification_counter to determine when auto update statistics happens. Because the data in tempdb is deleted every time after execution, replying on the data that that already deleted is pointless. Secondly, the algorithm is different: Here is the original threshold of temp table that are not in stored procedure.   Here is the new algorithm. (note, compatibility of user database decides old threshold or sublinear threshold, the compatibility of tempdb does not matter) Table cardinality ( n ) Old threshold Sublinear threshold n  < 6 >=n+6 >=n+6 6 <=  n  <= 500 >=n+500 >=n+500 500<n  <=19682 >= n+(500 +0.2*n) or <= n-(500+0.2*n) >= n+(500 +0.2*n) or <= n-(500+0.2*n) n  >19682 500 + (0.20 *  n ) >= n+sqrt(1000*n) or <=n-sqrt(1000*n)     Here is an example in compatibility level 150 in SQL 2019

SQL Server IAM page Hi Guys, Welcome to SQL Server allocation series, hope you enjoy the  post1 and post2 . Today, I’m going to show you the detail of IAM page. An  Index Allocation Map (IAM)  page maps the extents in a 4-GB part of a database file used by an allocation unit. An allocation unit is one of three types: IN_ROW_DATA Holds a partition of a heap or index. LOB_DATA Holds large object (LOB) data types, such as XML, VARBINARY(max), and VARCHAR(max). ROW_OVERFLOW_DATA Holds variable length data stored in VARCHAR, NVARCHAR, VARBINARY, or SQL_VARIANT columns that exceed the 8,060 byte row size limit. Each partition of a heap or index contains at least an IN_ROW_DATA allocation unit. It may also contain a LOB_DATA or ROW_OVERFLOW_DATA allocation unit, depending on the heap or index schema. An IAM page covers a 4-GB range in a file and is the same coverage as a GAM or SGAM page. If the allocation unit contains extents from more than one file, or more than one 4-GB range of a file, t