Blocked Process Report Viewer

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_buffer or event_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:

Check out the report.

Movie Analysis Using Power BI

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.

Paginated Reports

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.

Rebuilding Indexes For Contiguity

SQL Sasquatch throws out an interesting question:  why would you rebuild an index which is 0.44% fragmented?

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.

Query Store And Recompile

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.

Proportional Fill

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.

Simulate SQL Server Connections

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.

Make SSMS Beep

Denny Cherry shows us that you can make Management Studio beep when a batch completes:

The next time you run a query (you might need to close all your query windows or restart SSMS, you usually do with this sort of change in SSMS) it’ll beep when the query is done.

Personally I’ve actually used this as an alarm clock when doing long running overnight deployments so that I’d get woken up when the script was done so I could start the next step. It’s also handy when you want to leave the room / your desk while queries are running.

I must have seen that screen dozens of times and never once noticed this checkbox.

Columnstore And SSIS 2016

Niko Neugebauer mentions that with SQL Server 2016 and Integration Services 2016, clustered columnstore index insertion can get much faster:

To solve the performance problem I went straight to the DefaultBufferMaxRows setting and set it to be equal of the maximum number of rows in a Row Group – 1048576. Together with the AutoAdjustBufferSize setting it helps the actual current size of the DataFlow Buffer that will be used for transferring the data from the source to the destination table.

What should I say – it worked like magic:
I guess that with 2:09 Minutes the clear winner of this test is the configuration with AutoAdjustBufferSize set to True and the DefaultBufferMaxRows to 1048576. It took less then a half of the time with just AutoAdjustBufferSize activated and the insertion process was executed with the help of the Bulk Load API – meaning that we did not have to wait for the Tuple Mover or to execute it manually.

Doubling insertion performance is nothing to scoff, especially for something like columnstore tables, where we expect millions (or more) of rows to be inserted.

Finding Orphaned Users

Jon Morisi has a script to find orphaned users:

With the SQL Server 2005 end date approaching, you may find yourself migrating databases.  One of the gotchas with any database migration is orphaned users.  Below is a script I put together, and have been using for years, to help me resolve issues with orphaned users:

I’m not a fan of the sp_msforeachdb in there because there are issues which can cause “each” database to skip databases.  If you have installation authority, Aaron Bertrand’s sp_foreachdb is a better alternative, and if you need to do Insert/Exec calls, there’s a newer version which removes the INSERT/EXEC in that procedure.


September 2017
« Aug