Discover and read the best of Twitter Threads about #sqlserver

Most recents (15)

trying to reproduce a system issue that presents itself in a complex ETL when run at full scale on a given system is tough.
but it’s a task i can do sometimes, and want to be able to do more often.
it’s an important task bc attaching a debugger to production is risky. and extended events is of limited use when some system symptoms are known but query/worker/memory condition contributors aren’t.
the rules of the game: i almost never get to work with the production system itself. can supply some low-impact information gather/logging tools.
probably won’t be able to work with production data in nonprofit database, either.
Read 38 tweets
the idea for today is to cause #sqlserver [total server memory] to exceed [target server memory] by several gb with a rowstore-only workload and *then* read columnstore segments into buffer pool (and column store object pool).
while there are no columnstore related pages in bpool and columnstore object pool does not exist, the impossibly-high [target pages] value means almost no SQLOS free memory on the system.
initializing column store object pool brings [target pages] to a value well under [target server memory (kb)] - though, note [target pages] is measured in 8kb pages.
Read 5 tweets
when do the stories people believe about a work of art, regardless of artist intent or history of the piece, become a part of the work of art?
i don’t know.
working with a database engine, i’m an absolute stickler for “design intent” as revealed by documentation and historical development.
i have a particular view of RDBMS cpu scheduling and memory management: unless documented otherwise (in detail with boundaries), if an user query workload with no external calls makes the system unresponsive due to cpu or memory saturation, it’s a bug.
Read 7 tweets
just like that, i'm probably done thinking about scalabilty strategies for #sqlserver, and back to investigating latch timeouts on very busy systems. screen capture of a Teams instant message exchange. name of
here's the thing. the number of mdmp files for a given large-memory system can be pretty small, especially if an Availability Group is involved.
while a dump is in progress, #sqlserver memory must remain in a constant consistant state. the more memory, the longer the dump may take (there are other un-enumerated factors).

but, Availability Group lease timeout can only be set as high as 100 seconds currently.
Read 5 tweets
484 #sqlserver batch mode on rowstore queries. DOP 8. run consecutively by a single session. each query is a fullscan of a different single table, grouping by a CASE statement on a non-indexed INT/BIGINT/NUMERIC data type column. producing one or two result rows of 1 column each. On a Y axis scale with maximum value 500,000 and labeled in
based on observation of the first experiment with these queries, in which the queries were ordered alphabetically by the name of the only table in the FROM, i reordered the queries based on decreasing pages:rows ratio. that gives the evident decreasing pages/sec trend :-)
a closeup of the first 10 minutes shows the considerable variation even though the downward trend is evident. 16:18 to 16:28 on the x axis, labeled in 1 minute increments
Read 26 tweets
such a good blog post from @nocentino ...
~~
Understanding #SQL Server IO Size
2021 December 10
nocentino.com/posts/2021-12-…
i'll nitpick two details, though :-)
the 512k upper bound for disk IO size isn't properly a #sqlserver limit, although it *is* the limit on most systems. it's the upper bound of the disk adapter.
some disk adapters - most fibre channel HBAs and some others, too - allow configuration of the maximum disk transfer size, and can be set higher than 512kb.
Read 23 tweets
i don't use #Windows Resource Monitor very often.
it doesn't seem to like me.
first right-hand disk graphs froze, stopped updating. Even though sqlservr numbers kept updating.
then i tried to change graph size/position and whatever in the lower screen cap happened. Image
not a vm-wide memory problem. no paging space used. Image
not a vm-wide cpu problem. 67% is the peak - got batch mode workers on 32 of 48 vcpus and they're keeping things cooking. Image
Read 66 tweets
nanannaa nnaanaa nan na na na...
be the #sqlserver query workspace memory grant...
be the memory grant...
be the memory...
memory grants for bulk inserts into CCIs are tricky, especially if the query has significant and highly variable memory-consuming operators (hashes, sorts, etc) that are not involved in the compression.
"regular" memory-consuming operators - sorts, hashes and the like - can spill to temp if they need more memory than allowed by the grant.
rowgroup compression for a bulk insert can't spill to tempdb.
Read 9 tweets
ohmigosh.
a colleague just forwarded an email of mine about Windows registry setting QueueFullWaitIoPercentage. i had hit a wall trying to investigate/research it but passed it on to some colleagues in a #sqlserver storage investigation.
i don't think we know anything new yet.
email was from October 25. i was excited to find QueueFullWaitIoPercentage, thinking it may be a missing piece in some investiations.

until 5 minutes ago, i had completely forgotten registry setting QueueFullWaitIoPercentage exists.
other than my tweets, this appears to be the only occurrence of that registry setting on twitter...
Read 5 tweets
the #sqlserver production and perf/scale test systems i work with have recommended configs based on models and observations by my colleagues. those recommendations are solid :-)

but not every non-prod vm needs to have production-level perf/scale.
today i was asked about a non-prod #sqlserver instance used as a first stop for report-writers and developers. For schema exploration, ensuring queries are correct logically.
Two vcpus, 8 gb vRAM.
That's *way* smaller than any production or perf/scale test #sqlserver system i've worked with. production schema, but small test data set. #sqlserver 2019.

i completely understand the value of such a resource. still, at first i was a bit frightened :-)
Read 5 tweets
so my ideas for tomorrow
- DisablePagingExecutive
- LowMemoryThreshold = 256 mb

after testing those options individually in hopes of making remote shutdown more responsive, continue testing lower values of Max Server Memory, looking for test completion w/o error.
i've been batting this problem around for *four* years now. time to make some serious headway.
comes a time when "just keep setting [Max Server Memory] lower if the vm becomes unresponsive" is no longer a palatable strategy.
Read 104 tweets
on to post 2.
~~
Pushing the Limits of Windows: Virtual Memory
@markrussinovich
2018 November 17
techcommunity.microsoft.com/t5/windows-blo…
in this post, the Windows response to low virtual memory is described. but in my tests, virtual memory is not low. Paging space utilizations plateaus ~12% in these tests. #sqlserver with LPIM allocates bpool via AWE, so not eligible for paging.
specifically of interest to me is the Windows Available Memory low water mark, at which point physical memory contents start paging out to pagefile. that's 200 mb, and i want to know if configurable.
Read 41 tweets
approximately 15 minutes into the test.
if this #sqlserver vm becomes unresponsive, it'll be ~15 minutes from now. T-SQL query of performance ...
well, that was unexpected. The prior screenshot was from 10:45 am. it only took 8 minutes - the vm became unresponsive at 10:53 am
remote restart command issued at 10:55...
Read 23 tweets
och. the stuff i'm looking at today has so many variables and so much variability its about to do me in.

i'm trying to help optimize work in a workload group with a small footprint of concurrent queries and workers while other workload groups are active.
the gray inside the red-dashed-line boxes - that's what i'm trying to help out. without doing unnecessary damage to the other workload groups.
might be a long haul.
its hard to pick out the CPU utilization for just that workload group from 3 am to 6 am.

and the waits it experiences aren't all that easy to eliminate (memory_allocation_ext waits).
Read 47 tweets
there's some good stuff in here about #sqlserver soft-NUMA which applies to autosoftNUMA
~~
Understanding Non-uniform Memory Access
2012 October 4
docs.microsoft.com/en-us/previous…
"Memory nodes are created based on hardware NUMA and therefore not impacted by soft-NUMA."
Thumbs up! soft-numa nodes must be fully contained in memory nodes. so memory nodes enforce something on soft-numa, but not the other way 'round.
"Soft-NUMA does not provide memory to CPU affinity."
Thumbs up, i think. i guess i would probably have said that memory-to-cpu affinity is based on memory node rather than soft-NUMA node.
Read 15 tweets

Related hashtags

Did Thread Reader help you today?

Support us! We are indie developers!


This site is made by just two indie developers on a laptop doing marketing, support and development! Read more about the story.

Become a Premium Member ($3.00/month or $30.00/year) and get exclusive features!

Become Premium

Too expensive? Make a small donation by buying us coffee ($5) or help with server cost ($10)

Donate via Paypal Become our Patreon

Thank you for your support!