How to Start Speaking

Published On: 2018-10-17By:
At the last few events I attended, I’ve gotten into conversations on how to begin as a speaker. So I thought I’d share some of my advice that I provided to them. First and foremost, get your first talk scheduled. Reach out to your local user group and ask to be “penciled” in for a meeting a few months out. Giving yourself a goal and deadline is essential to putting yourself out there to speak. Next, write down ten topics you may want to speak on. Narrow down that list to five by thinking about what you would be most comfortable speaking about. Next look at your five topics and really think about what kind of experiences you can talk about and what kind of examples you can talk about. Jot those ideas down under each heading. If you come up a little lacking in ideas scratch that topic off your list as there probably is not enough content to do a talk. The key is to narrow it down and keep going down levels of detail.  You’ll notice after you get down a level or two that you can begin to see a slide deck constructing itself. Each topic and sub topic become and individual slide with bullet point for each side. Example (randomly streaming ideas while I type this) Query Store
  • Forcing a plan
    • How to figure out which plan
    • When not to do this (how can it hurt)
  • Query Regression
    • What is that
  • Configuration
    • Proper Settings
  • Top Consuming Queries
    • Standard reports you can use
    • How to identify which ones
  • What not to do
Performance Tuning
  • Best Items to change to get biggest results
    • RSCI
    • Statistics
  • Memory
    • Proper Settings
    • Issues I’ve seen
    • Multiple Instances
    • Extra Services Running
  • Indexes
  • 3rd Party Tools
  • Key Metrics
Now that you have a topic, it’s time to write an abstract and come up with a title. An abstract is what you will submit to the event and tells the attendee what your session is about. For events outside your user groups this is also what they will use to determine if your session is one they wish to have presented. I advise you to read some abstracts on SQL Saturday sites to get an idea of how people write them before you get started writing your own. This can be the hardest part of building a talk, and that’s why I come up with a list of topics first.  I then utilize the list of topics to help create my abstract.  Keep in mind that you have a limited amount of words in an abstract to try to convey the full message of the talk. An abstract is much like a movie preview.  The preview has to capture the attention of the audience in a short amount of time and inform them of the plot of the movie. Once you get the abstract written be sure to get a second set of eyes to review it. What sounds good to you may not convey a clear message about your topic. Having another person read it and give you feedback is an important part of the process. After building a list of topics, creating an abstract, getting it reviewed,  it’s time to build your slide deck. Using your bulleted list, create a slide for each item and fill in any missing items that comes to mind. You’ll find it comes along pretty easily since you have already created talking points. Be sure when you create your deck to add lots of notes for each slide it will help you keep on track and know what to cover or even what stories to tell. Some speakers, myself included, can blank out or get off topic easily, these notes will help guide yourself. For an hour presentation, try to create 20 slides as a starting goal, not including your title and about me slides. Approximately 2-3 mins spent talking on each slide is a good rule of thumb and that will give you a 50-minute presentation with a buffer for introductions, questions and tangents. Now the VERY most important part before giving your first session is PRACTICE, PRACTICE, PRACTICE. Set a timer in PowerPoint and go through your slides over and over. Once you feel comfortable, try to give the session to a family member or friend. Ask them to take notes like the things below. They need know nothing about your topic, they are just there to help you hone your presentation skills. What is your go to word?

Do you say umm to many times?

Did it seem fluid?

Could they follow along?

Are the slides too distracting?

Did you fidget?

Use these notes to make improvements. After you give your session for first time to the User Group ask for feedback and session evals. Speakers are constantly improving their sessions and slide decks each time they give that session. You may not knock it out of the park the first time, but as long as you keep building on to your skill you are well on your way to being a great speaker. All speakers had to give their very first session once. We all had to bite the bullet and face the fear of no one wanting to hear us talk, what if I teach something wrong, or I what if throw up because I am to nervous. We’ve all been there. You’re not alone in your journey of becoming a speaker.  I know many speakers within the SQL community that would be willing to review slide decks and listen to give notes.

SQL Server Data Collection and Management Data Warehouse

Published On: 2018-10-03By:

We all have the need to collect system and performance information regarding our SQL Servers. Some of us use third-party tools, SQL Trace, or a homegrown solution.  Did you know Microsoft has a built-in tool to help you accomplish this? It’s called Data Collection and the data it collects (collection sets) is stored in a relational Management Data Warehouse. The data collected is used to generate reports giving us in with very readable and useful insights to our servers. Not only can you get performance information, but you can also use SQL Profiler to export trace definition and create custom collection sets. This is not a new SQL Server feature, it has been around for some time. I find however that it is not used as much as it should be and that could be just because many don’t know it there or how to use it.

Let’s quick set one up and show you how easy this is to configure.

First steps to setting this up is to create a Management Data Warehouse to store the information in a collection. You can accomplish this by using a very simple wizard.

Under Management you will find Data Collections. Right click and choose Tasks then Configure Management Data Warehouse.

Here you need to choose your server you want to store your Data Collection Data. Then choose a database for your data. In this case I choose to create a new and named it MgtDW. Also, one Management Data Warehouse can act as central collection store to house all collections sets for multiple servers.

Next you need to grant access to users. This is done by Roles.

Once you have set up your DW now it’s time to setup your data collecting.

Data collection uses SQL Agent and SSIS to collect data and populate the data. I am not going to dive into the details of exactly how it works in this blog. Data collection either runs constantly or on a user-defined schedule.  After you complete your setup you will see new jobs.

If you go back under Data Collection you can now see that it is collecting data on your server.

Looking at logs you can now see that data is being collected.

Now that you are you collecting data you can see what’s being collected easy in Reports. To get to these reports you Right click on Data Collection. Not the System Data Collection Set.

Here is a link to find all the information you will need on what these reports will show you.

You can see how easy this was to setup and start using. Keep in mind I would expect a performance hit on the server are you are collecting data from (target server). Though minimal, keeping impact in mind is always important. SQL Server Data Collection is a great way to get important system information on your servers and is part of your STANDARD edition of SQL Server.

 

Cycle SQL Server Error Logs

Published On: 2018-09-26By:

I saw this again recently and see it too often in environments so wanted to take a second to remind everyone to cycle their error logs on a regular basis. SQL Server keeps error logs and when you reboot or restart SQL Server services the logs are cycled and a new one is created. Depending on how many logs you have configured for SQL Server to have this may include removal of the oldest log as well. Since many of pride ourselves on keeping our SQL Servers up and running, reboots may be few and far between thus our logs get large in size.

When they grow out of control it can require long wait times for the logs open to even view them. An easy way to keep this from happening is to cycle them routinely. You can easily automate these by creating a SQL Agent job to cycle the log to a new one on a regular basis whether it is monthly, weekly or even daily.

First double check and determine how many error logs you want to maintain.

For this example, Ill be keeping one for each month and setting up a monthly job to cycle the logs.  This means I will want to configure the maximum number of error logs to 12. Now something to keep in mind is that when the server is rebooted you will still only have 12 logs, you may end up loosing a log you may want to keep. I am using 12 as an example but would suggest you have a buffer and go with a number like 16 or 20 to be safe.

Using T-SQL

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 12 
GO

Here is the script to create the job. It simply creates and schedules an Agent job to run as system procedure called EXEC master.sys.sp_cycle_errorlog .

USE [msdb]
GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MaintenanceCycleErrorlogs',

 @enabled=1,

 @notify_level_eventlog=0,

 @notify_level_email=0,

 @notify_level_netsend=0,

 @notify_level_page=0,

 @delete_level=0,

 @description=N'No description available.',

 @category_name=N'[Uncategorized (Local)]',

 @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Recycle the SQL Server Error Log',

 @step_id=1,

 @cmdexec_success_code=0,

 @on_success_action=1,

 @on_success_step_id=0,

 @on_fail_action=2,

 @on_fail_step_id=0,

 @retry_attempts=0,

 @retry_interval=0,

 @os_run_priority=0, @subsystem=N'TSQL',

 @command=N'EXEC master.sys.sp_cycle_errorlog;',

 @database_name=N'master',

 @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monthly',

 @enabled=1,

 @freq_type=16,

 @freq_interval=1,

 @freq_subday_type=1,

 @freq_subday_interval=0,

 @freq_relative_interval=0,

 @freq_recurrence_factor=1,

 @active_start_date=20111110,

 @active_end_date=99991231,

 @active_start_time=0,

 @active_end_time=235959


IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

If you are not doing this in your environment, please take the time to set it up. It’s a simple maintenance task you should not neglect.

Careful with your SQL Server Max Memory settings

Published On: 2018-09-19By:

Quite often I see database administrators set SQL Server max server memory thinking everything related to SQL Server uses this shared memory pool. This is a mistake. There are many things that rely on memory that are not part of SQL Server. Best practices state that you should leave memory allotted for the operating system. However, did you know that if you are running services like SSIS, SSAS or SSRS on the same server as the database engine that it does not use the same memory you have allocated for SQL Server? If the Max Memory setting is not configured correctly, these other serves could incur memory pressure.  While the memory consumed by SSAS and SSRS can be configured, SSIS can be a little bit more challenging. Beyond this, there are even scenarios where SQL Server max memory consumed can exceed the setting, like with CLR in versions earlier than 2012 and some other bugs in SQL Server.

As a consultant, I have seen memory pressure and memory exhausted too many times to count because the DBA was unaware of this. I applaud those that take the time to properly configure this setting according to what the database engine requires. Let’s take it a step further and take the time to look at what additional services you are using and allot memory accordingly.

Beyond just thinking of what additional services are running also be aware of additional instances on that server. Again, I have seen time and time again over allocation of memory when other instances are not considered. I have seen where each instance has the same maximum memory value which over extends the available physical memory instead of spreading that amount across the instances according to each of their workloads. SQL Server makes zero attempts to balance memory usage across instances that reside on the same server.

I am not going to go into how to set your max memory as there are many great resources out there to help you do that. I am writing this just to put a little bug in your ear of things to consider when choosing a value above and beyond what your database engine may require. Be sure to leave enough memory for those additional things running on your server besides the operating system.

Note: Jonathan Kehayias (B|T) wrote a great post on configuring max memory. I recommend reading, How much memory does my SQL Server actually need?

1 2 3 14

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.