Free eBook: SQL Server Execution Plans, Second Edition (SQLServerCentral 11/14/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Free eBook: SQL Server Execution Plans, Second Edition]( - [Stairway to SQL PowerShell Level 11: SQL Server Maintenance Using SQL PowerShell]( - [SQL Data Catalog beta launched]( - [Filtered Indexes in SQL Server]( (From the SQLServerCentral Blogs) - [SQL Homework – November 2018 – Constraints]( (From the SQLServerCentral Blogs) Featured Script - [Automatically Failover (Enable or Disable) Jobs Based on HADR Role]( The Voice of the DBA The Linux CoC This is a busy time of year for me, with lots of conferences and other events taking place. It's busy most years, but this past October was especially busy in my life. I've been in New York, London, and Hong Kong during the month, which is quite a spread of time zones. It's been a mix of work and pleasure, and I agreed to all these trips, so I can't complain. In any case, it's both an exciting set of trips and a daunting set, and I don't know if I'd want to do that again. In my travels, I noticed some press about a [new Linux Kernel Code of Conduct](. This is a change from the original [Code of Conflict]( that Linus Torvalds published. I'm not sure he adhered to his own words from the reports I've seen over the years about his comments to developers. In any case, he signed off, though [not everyone likes the new code of conduct](. I don't know enough about the issues, but I do realize that not everyone behaves well towards others, especially in this business. In any case, I go to lots of conferences. I meet lots of people, and see lots of different situations play out between attendees, organizers, venues, and speakers. For the most part people are fairly well behaved and treat each other respectfully. That's not always the case, which is why many conferences and organizations have adopted some Code of Conduct that should apply to those that attend events, are members, etc. I do think this is a good idea, as it gives us a common framework where we can evaluate behavior as well as debate future changes. Last week PASS has their annual Summit, with [their own Anti-Harassment policy](. While I haven't observed any actions that would violate the policy, I have had friends report they have experienced these types of behavior. I've had friends ask for an escort over concerns of potential behavior. It's sad that this happens in the world, but it's a reality. I'm glad that organizations are trying to move in a direction that protects those that feel harassed or threatened. It's likely that there will be overreactions, reports of misunderstandings, and similar issues. Certainly some people want the freedom to behave as they see fit, where they don't believe they are doing anything wrong. I understand that, but ultimately I also believe that it would be better to have a few people investigated or thrown out of a conference for no reason than have others suffer because they aren't believed. [I'm here for any of you]( that are struggling with abuse, and I hope others are as well. I hope that we learn to live by the famous quote from [Bill and Ted's Excellent Adventure](: be excellent to each other. If some of you can't do that, then at least learn to live by [Wheaton's Law](. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 3.6MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [Webinar]( Enable business agility through Database DevOps Founder and Chief Technologist at Nebbia Technology, Esteban Garcia, joins Redgate to discuss how DevOps helps technology teams to go faster and automate everything. As teams lower the cycle time between idea and production, how can organizations leverage these new capabilities to improve product delivery and quality? [Register now]( [GDPR](www.red-gate.com/gdpr?utm_source=ssc&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20181114-slot2) How to make your SQL Server development GDPR ready Redgate’s SQL Data Privacy Suite helps you to build a data catalog of your SQL Server estate, and put in processes to protect and monitor personal information. [Find out more about how our tools help with GDPR compliance](www.red-gate.com/gdpr?utm_source=ssc&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20181114-slot2) Featured Contents  [] [Free eBook: SQL Server Execution Plans, Second Edition]( Press Release from [Redgate]() Every day, out in the various online forums devoted to SQL Server, and on Twitter, the same types of questions come up repeatedly: Why is this query running slowly? Why is SQL Server ignoring my index? Why does this query run quickly sometimes and slowly at others? My response is the same in each case: have you looked at the execution plan?[More »]( ---------------------------------------------------------------  [] [Stairway to SQL PowerShell Level 11: SQL Server Maintenance Using SQL PowerShell]( Ben Miller from [SQLServerCentral.com]() This level will demonstrate simple techniques to perform a few important SQL Server maintenance tasks using PowerShell. We'll barely scratch the surface of what's possible but I hope it will whet your appetite.[More »](Series/134713/) ---------------------------------------------------------------  [] [SQL Data Catalog beta launched]( Redgate has launched a beta for a new product that makes discovering and classifying sensitive data in SQL Server easy. An open taxonomy lets you label columns in line with your classification scheme. Integration with Redgate SQL Provision informs masking data in dev and test. [More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Filtered Indexes in SQL Server]( Klaus Aschenbrenner from [SQLServerCentral Blogs]( (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [SQL Homework – November 2018 – Constraints]( Kenneth Fisher from [SQLServerCentral Blogs]( You’ve created tables before but how about constraints? Constraints allow a finer level of control over what data is allowed...[More »]( Question of the Day Today's Question (by Steve Jones): I have a data frame in R v3.5 defined like this: > df= read_csv(' col_names = TRUE) I want to sample the first few rows to get an idea of columns. I run this: > head(df) How many rows are returned? Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: R Language. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload. Pick up your copy of this great book today at [Amazon]( today. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I created and endpoint for my Availability Group that is owned by me, Steve. I don't want that dependency as I may get a new job, so I want to change the endpoint owner to sa. The endpoint is called ag_endpoint. What code do I run to change the owner? Answer: ALTER AUTHORIZATION ON ENDPOINT::ag_endpoint TO sa Explanation: The ALTER AUTHORIZATION DDL will allow this. The ENDPOINT::is the syntax for referencing the particular item. Ref: Always On - Endpoint Ownership - [( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [Automatically Failover (Enable or Disable) Jobs Based on HADR Role]( Eitan Blumin from [SQLServerCentral.com]() Imagine the following: You have a couple or more SQL Servers with some sort of High Availability solution between them (AlwaysOn Availability Groups, Database Mirroring, etc.). You also have a bunch of scheduled jobs which you need to be run on the Primary server. But wait.... How would you make these jobs run only on the Primary server? After all, if they try to run on the Secondary, they would fail (whether because the database is inaccessible or because it's read-only). Additionally, you would need to consider the possibility of a failover where the Primary and Secondary servers would switch roles. This means that you would have to, first, create these jobs on both servers, and implement some sort of mechanism that would detect, for each job, whether the instance it's being executed at is currently the Primary. There are a few ways to go about doing this. [For the sake of this article, let's ignore the fact that most people don't even think about it, and leave all of their important jobs on the Primary server only, while ignoring the risks of what would happen when their Primary server crashes and fails over to the Secondary] The "Meh..." Solution: Adding an IF check in each job One way is to add some sort of IF in the beginning of each job, which would check whether the current server is the Primary, otherwise stop the job. I personally don't like this approach at all, because it means we'd have these jobs executing on all servers all the time, filling up the MSDB job history with a lot of useless junk (who cares about thousands of executions that didn't do anything? We want to see what the jobs actually DID!). I would prefer not to have these jobs run at all, if the current server isn't the Primary. Also, I would prefer to have some sort of solution which would be easy to maintain. Could I really guarantee that whoever's writing and/or updating the jobs would remember to put that IF check every time? So, the best I could come up with is the following solution: The "Awesome!" Solution: The Master Control Job Instead of modifying the jobs themselves, we would have a sort of "Master Control Job" which would exist on both servers, and that job would know to automatically disable or enable the jobs, based on the current HADR role of the server. Ideally, this Master Control Job would use some sort of predefined list of job names, which would be the specific jobs that it would control (more often than not, you would also have jobs which you would not want to disable or enable automatically). For this purpose, I created a couple of scripts (click on the following links to see the code in my GitHub Gist): ⇒ [Change Job Status Based on Availability Group Role]( ⇒ [Change Job Status Based on Database Mirroring Role]( They both use the same principles, essentially. Only difference is the type of HADR solution being in use. These two scripts contain a stored procedure, which you would need to create in some sort of impartial database (which is not part of the HADR solution). It could also be one of the system databases (such as MSDB or Master)... TEMPDB is not a good choice for this, because it would be dropped every time the SQL service is restarted. Configuring The Jobs List Within these procedures, there's a list of job names that would be automatically disabled or enabled. For example: DECLARE @CurrJob NVARCHAR(500) DECLARE JobsToUpdate CURSOR READ_ONLY FORWARD_ONLY FOR select name from msdb..sysjobs where name in ( 'Job name 1', 'Job name 2', 'Job name 3', 'Job name 4', 'Job name 5' ) and [enabled] <> @NeedToEnableJobs Before deploying and using these stored procedures, you would need to update this list of job names, to whatever relevant jobs that you're using in your servers (don't forget to create them in BOTH servers). PRO TIP If you know how to use [Table-Valued Parameters](, you could make a few changes to the stored procedures so that they would receive the jobs list as a parameter, instead of being hard-coded within the procedure itself. Choosing the Main HADR Database The stored procedures both receive a parameter called @DBName. This parameter should receive the name of the database which should be used for determining which server is currently the Primary. The reason for this is that it's possible to have several different HADR solutions on the same SQL Server instance. For example, you could have a SQL Server which has both Availability Groups, as well as Database Mirroring. Or, you could have a SQL Server which has several different Availability Groups, each with their own database(s). This is why we need to choose which database should serve as the "main" database. Because it would be the database after which all the jobs would "follow". This also means that, if we have several different HADR solutions, we could create several different "Master Control Jobs", each controlling a different set of jobs, based on a different "main" database. Creating the Master Control Job Next step would be to create a scheduled job (again, on BOTH servers) which would run the relevant stored procedure every so often. The schedule of this job would mostly depend on the most frequent schedule of the jobs you're controlling. For example, let's say that one of your jobs is running every 10 minutes. Then the Master Control Job would need to be run at least as often as every 10 minutes. But if you also have a job which runs, let's say, every 1 minute, then it means you're at risk of missing scheduled runs of this job, between when the HADR fails over, and until the Master Control Job enables all the jobs on the new Primary server. So this means that, in this case, you'd need to schedule the Master Control Job to be run at least as often as every 1 minute. Here is an example create script of such a job: 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'HADR Control Jobs', @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'control', @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 msdb..ChangeJobStatusBasedOnMirroring @DBName = ''MyMainDatabase''', @database_name=N'master', @flags=4 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'Master Control Schedule', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=4, @freq_subday_interval=10, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20180101, @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 This job would run the ChangeJobStatusBasedOnMirroring procedure within the MSDB database, with "MyMainDatabase" as the name of the "main" database, and it would be scheduled to run every 10 minutes. Another PRO TIP Theoretically, it should be possible to implement this logic even better: Instead of a scheduled job, the "automatic fail over" of jobs could be "event-based". In other words: The jobs would "fail over" only when the HADR solution itself fails over. ⇒ [In the case of Database Mirroring, you could create an SQL Alert]( which would start the same "Master Control Job" when a State Change event is fired. Similarly, ⇒ [in the case of Availability Group, you could also create an SQL Alert]( which would start the "Master Control Job" when a Role Change event is fired. Starting the "Master Control Job" based on events instead of schedules should be something preferable, if you want to avoid cluttering MSDB with useless job history junk. Conclusion Once you create the procedure and scheduled (or event-based) job on both servers, you would have a list of jobs that know how to automatically "fail over" whenever the HADR fails over. As always, you can find these scripts in my GitHub Gist here: ⇒ [click here]( And in my TechNet Gallery here: ⇒ [click here]( [More »]( Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Administration]( [How to encrypt all stored procedure, functions and views in sql server]( - Hi, How to encrypt all stored procedure, functions and views in sql server. Please advise best method, is available any tool for... [Running query with Actual Execution Plan On slows down the query?]( - when running a loing SQL Statement with Actual Plan On it takes 2 hours. if run without Actual Exec Plan it... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Struggling with formatting decimal numbers and padding them with zeros to be saved in the char fields]( - I am preparing data for being output to text file via ssis in a certain format required by the receiving... [trying to avoid the Arithmetic Overflow error]( - How can I convert the number to DECIMAL (4,1) and then back to varchar (this is a varchar (40) column... [Column names on INNER JOINS]( - In my code below I have Invoices.InvID [How to study the layout of a database?]( - Hello friends, Suppose you download a sample database, whether it be AdventureWorks, Northwind, or whatever other database. What steps do you... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [Data conversion of linked sets]( - I have a table that lists linked warehouse locations. The previous design was to link each location id to every... [How to create a calculated column that encodes in base64]( - I am trying to create a calculated column which is a single other column but encoded in base64. I have... [Creating a Stored Procedure with Loop and If statement into a new table]( - Hi Guys,  Can anybody help? [SSIS to load complex Excel File]( - Hi, I want to load the below table into SQL Server Database using SSIS package. Can you one please tell me... [Encryption and data length limitations]( - I'm having an issue in encrypting large documents. I know that previous editions ENCRYPTBYKEY had a maximum size of 8,000... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Pass SSIS parameter into Agent Job]( - Hi The problem: I am calling a stored proc from VBA which executes a stored proc. The stored proc triggers an... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [character data type selection in table design]( - Let's say I have a field that will have a max value of 'yes'. What difference does it make if I... [how to find the lowest usage of a database]( - Hi Guys How do we find the lowest usage of a database ? I need to find the best time to ... --------------------------------------------------------------- [Reporting Services]( : [Reporting Services]( [How do you change the Text Legend to BOLD just for the total in the SRSS report?]( - In my Series Group, I have the Label with the following expression: =(Fields!School.Value) & (COUNT(Fields!School.Value, "Chart3_SeriesGroup"))  want to make the COUNT part appear... [How to group similar data together in SSRS]( - Hi, I created a report and it shows below:  Office Name        Employee # ABC - OFFICE1          2 ABC - OFFICE2          3 ABC - OFFICE3      &n --------------------------------------------------------------- [Reporting Services]( : [SSRS 2012]( [Matrix help with blank field]( - Hello there --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [Replacing string in text file with another string]( - I need to replace all occurrences of 'ABCD' in .txt file with 'EFGH'. Is it possible to do from an SSIS... --------------------------------------------------------------- [SQL Server 2005]( : [SQL Server 2005 Integration Services]( [How can I update original Ole DB Source after Multicast?]( - Requirement: I have a flat table that I need to split into many tables, and then timestamp (basically confirming the... --------------------------------------------------------------- [SQL Server 2005]( : [T-SQL (SS2K5)]( [SELECT TOP 1 on Primary Key?]( - I have a table (let's call it TableA) with a primary key defined (let's say the column is called ColumnPrimary).... This email has been sent to {EMAIL}. To be removed from this list, please click [here.]( If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. --------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com