Discover and read the best of Twitter Threads about #sqlserver
Most recents (16)
Basic SQL can be learnt in 4-6 hours and it's enough to get you practising in the job.
Here are 5 resources to get to learn the basics for free. 👇
Here are 5 resources to get to learn the basics for free. 👇
- sqlbolt.com
- w3schools.com/sql
- mode.com/sql-tutorial
- sqlteaching.com
- SQLZoo.net
- selectstarsql.com
- pgexercises.com
- w3schools.com/sql
- mode.com/sql-tutorial
- sqlteaching.com
- SQLZoo.net
- selectstarsql.com
- pgexercises.com
All of them get recommended highlyđź’Ş, especially No 1.
cc: Senne Vermassen
#SQL #SQLresources #learning
cc: Senne Vermassen
#SQL #SQLresources #learning
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.
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.
probably won’t be able to work with production data in nonprofit database, either.
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.
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.
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.
just like that, i'm probably done thinking about scalabilty strategies for #sqlserver, and back to investigating latch timeouts on very busy systems. 
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.
but, Availability Group lease timeout can only be set as high as 100 seconds currently.
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.
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.
such a good blog post from @nocentino ...
~~
Understanding #SQL Server IO Size
2021 December 10
nocentino.com/posts/2021-12-…
~~
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.
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.
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.
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.
not a vm-wide memory problem. no paging space used.
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.
nanannaa nnaanaa nan na na na...
be the #sqlserver query workspace memory grant...
be the memory grant...
be the memory...
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.
rowgroup compression for a bulk insert can't spill to tempdb.
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.
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.
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...
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.
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.
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 :-)
i completely understand the value of such a resource. still, at first i was a bit frightened :-)
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.
- 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.
on to post 2.
~~
Pushing the Limits of Windows: Virtual Memory
@markrussinovich
2018 November 17
techcommunity.microsoft.com/t5/windows-blo…
~~
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.
approximately 15 minutes into the test.
if this #sqlserver vm becomes unresponsive, it'll be ~15 minutes from now.
if this #sqlserver vm becomes unresponsive, it'll be ~15 minutes from now.
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...
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.
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.
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).
and the waits it experiences aren't all that easy to eliminate (memory_allocation_ext waits).
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…
~~
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.
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.
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.
