Voice of the DBA

Tag: Humor

Customizing Statistics Histogram in SQL Server 2019
The use of statistics in SQL Server is tightly embedded in the query optimizer and query processor. The creation and maintenance of statistics is usually handled by the SQL Server engine, though many DBAs and developers know that periodically we might need to update those statistics to ensure good performance of queries. SQL Server 2019 gives us new options.

The historical organization of statistics for a table is a 200 step histogram of values sampled from the data. This could be a sample of the entire dataset or a subset. For tables less than 8MB, the entire table is sampled. Above this, the proportion changes to a lower rate and reduce the resources required.

This means that sometimes we have less accuracy in the histogram than we would like.

A New DMF

In SQL Server 2019, we have a new DMF, sys.dm_exec_table_stats, that is designed to create a new statistics entry for your table. The parameters for this DMF are:
- object_id – this is required. If you have just the table name, is object_id() to enter that, but you need the id of the table.
- schema_id – also required. The schema_id of the schema for this table.
- column_id – required. Column on which you are creating statistics
- histogram_steps – not required, but defaults to 200, which defeats the purpose of this DMF. You can specify any value up to 1024
This means that you can create new statistics that include a more granular detail. You can see this in action with the DBCC SHOW_STATISTICS command and the WITH HISTOGRAM option. I ran this DMF with a value of 1024 on a large version of AdventureWorks and got these results. I am only showing the bottom of the results here.

I didn’t get the full 1024 values, but I did get close here. As you can see, the histogram is significantly larger than the 200 step limit.

Using Larger Statistics Histograms in your Database

There is a great tutorial on how this works from the SQL Server Tiger Team. I’d encourage you to read this and experiment with your own data set and see what values are most useful. It seems for larger tables, the Tiger Team recommends 500 steps, so 1024 might be overkill for most of us.

Also, this is an April Fool’s joke, which you might have realized if you clicked on some of the links above. Hope you enjoyed this.
March 31, 2019
Fun at Work

I am lucky in that I get to travel and speak at a number of events every year. As a result, I meet lots of people in a variety of situations. Many of these places have a social component and often result in pictures. I love that because pictures are memories for me, and I look back at them to reminisce about good times in life. I greatly appreciate having Facebook remind me of images from the past, some of which I’ll share back out as they’re great memories for others in my family.

This has changed dramatically in the last decade, as everyone seems to have a camera on them at all times in their phone. That means many more memories I can capture. Some of these are silly, and I wrote a blog with some of the funny ones I’ve gotten over the years. In the past, I have relatively few pictures from work. Early in my career, film cameras were rare. We only carried them around for special events, which wasn’t often at work. I do have actual physical pictures of a few work parties, but rarely of workspaces and situations. There are certainly some I wish I’d been able to capture and save.

For many of you, I’m sure that’s different now. Some of you have grown up with cameras and spent most of your career with them. When I asked about desks, quite a few of you posted pictures, with some really interesting items. Those are likely going to be fun memories for you at some point in the future.

Today I’m wondering if you have some funny, silly, even head shake photos of yourself or work. Nothing that will get anyone in trouble or be offensive, but maybe you’ve run across a sign on a wall, some wild cabling, or even a snapshot of you and others celebrating some accomplishment. If you can share them. Check out the fun pics on my blog over the years that make me smile, and if any of you have old pictures of me, please post or send them along.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

August 24, 2018
Silly Photos at Work

Someone I stumbled across this picture of @brento on my drive.

This might be the best one:

And this was a good time:

However, I have a collection of silly moments. Enjoy.

July 20, 2018
This Made Me Laugh: SQL v NoSQL

It’s a humorous history lesson on SQL (and product promotion), but it opens like this:

SQL awakens to fight the dark forces of NoSQL

From there it looks at the beginnings of SQL and the growth of NoSQL, going through a few sections, like

Part 1: A New Hope

and

Part 2: NoSQL Strikes Back

before moving to

Part 3: Return of the SQL

They finish with the idea that SQL is the most important, and common, tool for data analysis. In this era of Python and R, many may disagree, but I think they’re overall right.

Perhaps this last part proves that?

Master Data Scientist Yoda

It’s an interesting read, and I may have to watch some Star Wars this weekend.

September 29, 2017