Voice of the DBA

Category: Blog

  • SQL in the City Streamed is Today

    You can still register, but join me later today for SQL in the City Streamed, along with Grant, Kathi, and Kendra. We’re all in the Redgate Software office today for the broadcast.

    Steve

    Here are a few highlights:

    • Learn about the cultural shift necessary to introduce collaboration between and across teams
    • Discover the top ten SQL Toolbelt tips for standardizing and automating database changes
    • See the advantages to be gained by provisioning masked database copies for use in development
    • Hear about the critical role the database now plays in software development
    • Understand the business case for database change management

    Register and join us later.

  • 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.

    2019-03-27 11_43_47-SQL Prompt - Insert results1.sql - Plato_SQL2017.AdventureWorks2012CS (PLATO_Ste

    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.

  • What’s the State of SQL Server Monitoring?

    You can help everyone understand how people monitor and manage their instances. Redgate is prepping the 2019 State of SQL Server Monitoring report and we’re asking people to share some data on what they monitor today.

    Take our survey today (closes Apr 5)

    We publish this information, so it’s not something that just Redgate looks at. You can get last year’s report here, which lots of you have downloaded. The data in here can help you understand if you’re doing a good job with your SQL Server estate and perhaps give you a few things to think about that might help you do a better job.

    If you have a few minutes, and you’d like to win an Amazon US$250GC, take the survey. You’ve got a week, but take 5 minutes today and think about your monitoring process and applications and answer a few questions.

  • SQL in the City Summits coming this spring

    The SQL in the City Summits are back for 2019, and we’ve got more scheduled than every before. You can see the complete list on our event page, and I’m lucky to be invited to all of them. Here’s the schedule.

    London

    We kick off the year in London on April 30. Having been busy with volleyball all this year, my first weekend free is going to be heading back to London and helping kick off our 2019 tour.

    Tickets are on sale now, and if you use “SteveJones” as a code, you should get 50% off. Register today and I’ll see you at Canary Wharf.

    The US Tour

    It’s a short tour, but we’ll be back in the fall again, so if you’re not near one of these cities, you’ll still have a chance to attend. Use “SteveJones” as the code when you register for a ticket.

    We start on May 15 with SQL in the City Summit Los Angeles. I enjoy the city of Angels for a few day and I’m looking forward to coming back for an hour at Venice Beach. I’ll also be speaking, along with Kendra, our top engineer, Arneh, and the amazing Ike Ellis. It will be a great time, so brave the traffic and come see us at the Microsoft office in LA.

    Next we head to Austin, TX on May 22 for the next Summit. Austin is a great town, and full of SQL Server professionals. We came through here a few years ago and I’m looking forward to coming back. A quick trip for me as my daughter graduates this week, so I’m jetting back to Colorado after the event.

    SQL in the City Down Under

    Redgate recently opened an office in Australia and as part of our expansion, we’re doing a SQL in the City Tour in June. I’m very excited as I’ve never been to this part of the world, though this will be a long trip with some vacation taken to enjoy the trip.

    We start in Brisbane, on May 31 as the first stop. We coincide with SQL Saturday #838 – Brisbane on June 1, so be sure you register for both. Warwick Rudd, of SQL Masters Consulting, will be joining me to talk about Redgate and database DevOps. We also have Hamish Watson, Michael Noonan, and Dr. Greg Low joining us. A special thanks to Warwick for moving the SQL Saturday date to align here and help me out.

    Next I’m taking a few days off and making my way to Christchurch, NZ for our second event on June 7, with SQL Saturday #831 on June 8. Hamish Watson is an exciting speaker and he’ll be hosting us. Looking forward to a fun NZ winter, perhaps with some skiing before or after.

    We round out the tour in Melbourne on June 14, with SQL Saturday #865 on June 15. I’ve heard wonderful things about the city and I’m hoping I get to enjoy a little time there. If you say hi, let me know what you like to do around town.

    It’s going to be a hectic few months, but I’m looking forward to delivering some sessions and meeting some wonderful people to talk databases.