Erik Darling talks about DBCC CHECKDB frequency:
The shorter the period of time you keep backups, the more often you need to run DBCC CHECKDB. If you keep data for two weeks, weekly is a good starting point. If you take weekly fulls, you should consider running your DBCC checks before those happen. A corrupt backup doesn’t help you worth a lick. Garbage backup, garbage restore. If your data only goes back two weeks, and your corruption goes back a month, best of luck with your job search.
Erik provides some good guidelines on where to begin, but as always, your answer will depend upon your particular circumstances.
Kendra Little shows us how to how to fake reads and writes:
Trainers and speakers need the code they write to be predictable, re-runnable, and as fast as possible. Faking writes can be useful for speakers and teachers who want to be able to generate some statistics in SQL Server’s index dynamic management views or get some query execution plans into cache. The “faking” bit makes the code re-runnable, and usually a bit faster. For writes, it also reduces the risk of filling up your transaction log.
I didn’t invent either of the techniques used below. Both patterns are very common and generic, and so simple that no origin is known.
This isn’t applicable to everyone, but if you’re giving a presentation and want to simulate data access, these are good techniques.
The CSS SQL Engineers have a new series called “It Just Runs Faster.” In their first post, they discuss DBCC improvements:
Internally DBCC CHECK* uses a page scanning coordinator design (MultiObjectScanner.) SQL Server 2016 changes the internal design to (CheckScanner), applying no lock semantics and a design similar to those used with In-Memory Optimized (Hekaton) objects, allowing DBCC operations to scale far better than previous releases.
Making DBCC checks significantly faster for large databases makes administration that much easier. I’m looking forward to seeing this. James Anderson, however, is concerned that things might be worse at the extreme low end.
Michael J Swart has updated the Blocked Process Report Viewer:
The viewer can consume blocked process report events captured by any extended events session as long as that session has a target of
ring_bufferorevent_file. For example, if you set up your extended events session the way Jeremiah Peschka did in Finding Blocked Processes and Deadlocks using SQL Server Extended Events. Then you would use the viewer like this:
Dan English compares Marvel versus DC movies using Power BI:
This is an interactive report comparing the results of Marvel versus DC movie information with regards to the number of movies, adjusted worldwide gross box office earnings, and includes IMDb ratings. You can get a feel for the shift from the 1960’s through the 1990’s as DC dominated the market and then Marvel stepped in and has dominated the box office since.
This is a fun data set and dashboard.
James Anderson shows off paginated report improvements in SSRS 2016:
Anyone who has used SSRS in the past has probably been slightly frustrated with the lack of control for parameter positioning. It was possible to have some control by manipulating the ordering of the parameters, but for 2016 we have a new interface to define the positioning. It’s basically a grid onto which parameters, along with their labels, can be placed.
The big one for me: HTML 5 support. I remember spending so many hours trying to figure out why reports wouldn’t display in Firefox correctly or why they sometimes wouldn’t work at all (because the report builder executable wasn’t installed correctly or that one time there was a bug in the executable)…and that was before mobile took off as a serious platform.
NC_TABLE1 is 36 total extents. 288 eight k pages. 2.25 mb. It can be read in 5 reads – one read for each contiguous run.CI_TABLE1 is comprised of 48 extents. 3 mb. It can be read in 11 reads – again, one for each contiguous run.The SQL Server instance has the -E startup option enabled. Without that startup option, proportional fill algorithm would distribute incoming data to the 8 data files with a potential smallest allocation of a single 64k extent before rotating round-robin to the additional files in the filegroup. With the -E startup option, the smallest possible new allocation by proportional fill is sixty four 64k extents – 4 mb.That means if I can rebuild the indexes into new space at the tail of the files, the contiguity should be improved considerably.
I had never considered that the scenario described here before, so this was definitely interesting.
Grant Fritchey shows that Query Store commands kinda-sorta overpower recompilation hints:
Now, no matter what value I pass it, the RECOMPILE hint is effectively ignored. I have the same plan every time (in my case, the London plan). Ah, but is the RECOMPILE hint ignored? In fact, no, it isn’t. If I capture the extended event sql_statement_recompile, I can see every single time I execute this procedure that it’s going through a full recompile… which is then tossed and the plan I chose is forced upon the optimizer. Every time.
This tells me that if you were using OPTION(RECOMPILE) to get around nasty parameter sniffing problems before, and if you go to Query Store, and if you force a particular plan to get around said nasty parameter sniffing problems, then you probably want to update the query to get rid of that OPTION(RECOMPILE) in order to prevent unnecessary plan compilation work.
Rolf Tesmer shows how proportional fill for files in a filegroup works:
When multiple files are involved, and if these are ideally located on different physical spindles on the underlying disk subsystem, then a rather nicely performing data striping can be achieved for the database. If proportional fill kicks in and starts to focus on files with more free space then you may get hot spots for those files. However nowadays with auto-tiering SAN’s, SSD and (abstracted) cloud storage (for IaaS deployments) this is beginning to matter less and less.
This is a good introduction to proportional fill, including what happens when you add files later. If you are counting on proportional fill, it’s a good idea to make sure all files are the same size and grow them all at once.
Kenneth Fisher shows us how to generate multiple connections using Powershell:
As with most of these types of things, I had a need. I want to show how using sys.dm_exec_[requests/sessions/connections] is better than sp_who. Particularly when you have a large number of connections. Well in order to do that I need a large number of connections right? Now I’m sure someone out there has a script to generate somewhat random connections but writing one myself would be good practice and I’d like to get better at Powershell anyway. In the end I need some help and as aways it was plentiful and easy to find. So thanks to Derik Hammer (b/t), Drew Furgiuele(b/t), and of course it wouldn’t be a PoSH script if I didn’t get help from Mike Fal (b/t). (To be honest Mike actually wrote most of the final script)
This is great for demonstrations, and with a few tweaks you can turn this into a very poor man’s load tester.
Kevin Feasel
2016-02-26
Administration