THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |


You searched for the word(s):
Showing page 1 of 52 (512 total posts) < 1 second(s)
  • Rethink Server Sizing II

    The huge disparity between round-trip access to DRAM memory and the CPU clock cycle has been a serious problem for more than ten years. It is simple to demonstrate that even a small amount of pointer chasing code, in which the memory value being accessed determines the next memory to access, results in performance being almost entirely ...
    Posted to Joe Chang (Weblog) by jchang on February 8, 2018
  • Quick performance check with latest patches

    there is some degree of press hysteria, lawsuits etc. concerning the kernel vulnerabilities of Intel processors with out-of-order execution (which originates to Pentium Pro), so I did a quick check. Earlier, I posted on testing with the TPC-H database and queries at scale factor 100 (in conditions that do not meet official TPC-H ...
    Posted to Joe Chang (Weblog) by jchang on January 11, 2018
  • Rethink Server Sizing

    Standardizing on 2 and 4 sockets systems for servers has been an established practice going back to 1996. There were very good reasons for this, but it was so long ago that they have been forgotten. Yet the practice continues unquestioned, almost as a reflex action ingrained in our subconscious. Back then, 2-way was the baseline, and a 4-way ...
    Posted to Joe Chang (Weblog) by jchang on December 26, 2017
  • Memory Allocated To Plan Caching

    I could complain about Entity Framework, but there is probably nothing that has not already been aired. So instead, I will complain about the lack of tuning options in SQL Server's Memory Allocated To Plan Caching. As of SQL Server versions 2005 SP2 and 2008, the formula is 75% from the first 0-4GB, 10% from 4-64GB and 5% of the remaining ...
    Posted to Joe Chang (Weblog) by jchang on August 12, 2017
  • Azure database_id inconsistencies

    I am working on improvements to my SQL ExecStats tool, including Azure support. Previously, I had put try catch blocks around everything that bombs in Azure. Now I am putting IF EXISTS tests around SQL referencing system views and procedures not in Azure an other. One of issues I encountered is that there is an inconsistency between DB_ID() and ...
    Posted to Joe Chang (Weblog) by jchang on May 23, 2017
  • re: Fixing the Query Optimizer Cost Model

    changing the default CTOP is nice, but the most important matter is for the cost model to correctly model IO cost in parallel execution, i.e., scale IO cost with parallelism. Second most important is probably parallelism and NUMA, scaling is great with all threads on single socket, questionable over multiple sockets - redistribute streams having ...
    Posted to Joe Chang (Weblog) by jchang on March 16, 2017
  • re: Fixing the Query Optimizer Cost Model

    It would be a cold day in hell before MS allows the cost model to be tunable, but MS said the same thing about PAE (used by AWE) back in 1997/8. There is a trace flag for the CPU/IO ratio, see Paul White's posts? But I do not think this is helpful because neither the current cost model CPU or IO components constitute a reasonable representation of ...
    Posted to Joe Chang (Weblog) by jchang on February 26, 2017
  • re: Fixing the Query Optimizer Cost Model

    hello Glenn, that is correct, I endorse single socket with the Xeon E5 v4 processor that is capable of 2-way. You could buy either a UP or DP motherboard from Supermicro with 1-socket populated. Or you could buy a Dell or HP server with 1 E5v4. Of course the E3 is not chump change, 16 PCI-E lanes is workable if I could get 4 x4 + the DMI equiv ...
    Posted to Joe Chang (Weblog) by jchang on February 21, 2017
  • Fixing the Query Optimizer Cost Model

    The model for the cost of operations used by the SQL Server query optimizer to produce an execution plan is rather simple. It has been largely unchanged since SQL Server version 7, RTM in 1998. Back then, SQL Server had not yet achieved tier 1 status with commensurate development resources. Some adjustments were made in version ...
    Posted to Joe Chang (Weblog) by jchang on February 20, 2017
  • re: Parallel Execution on SQL Server 2016

    The key to Hyper-Threading is in there being significant round-trip memory access no-op cycles. For transactions, this is very true. In other posts, I have suggested that this might be around 5% of instructions, meaning that perhaps only 12% of cycles are actually used by a thread. Hence scaling with HT is almost linear in the absence of ...
    Posted to Joe Chang (Weblog) by jchang on February 5, 2017
1 2 3 4 5 Next > ... Last »
Privacy Statement