Blog

You Spin me Round like “Parser NULL (DTEParseMgd)”

I love the 80’s and that crazy music. This error drove me crazy (nothing on the web had a fix) and spun me around like a record baby, right, round, round. Dead or Alive 1984 – Funny old video

ERROR: Received “Parser NULL (DTEParseMgd)” error when adding an SSIS package to a SQL Server Agent job step.

JobParserNull


CAUSE: The Visual Studio was at a higher version then the SQL Server Management Studio.


FIX: Upgrade SSMS or Find another server with a higher version SSMS and connect to the instance where you want to create the SSIS job.


Details:

I have SQL Server 2016 running on Windows Server 2012 R2, but the real issue was the SSMS level.

ServerVersion

First I tried to create a job on an SSMS that was at 13.0.16000.28 and I received the error. SSMS that did not work:

SSMSVersion_Prod16_works

Then I connected to another server with another version of SSMS 13.0.16106.4, connected to the SQL Server I wanted to create the job on (the original server), created the job and it did work with no errors.

SSMS on another server that did work:

SSMSVersion

 

SSIS was compiled in this VS Version:

VSVersion

This is How We Do It: MSX Job Needs Access to Shared Directory

Obscure 90’s song reference “This is How We Do It” by Montell Jordan 1995

I have an MSX job that runs on three target servers and needs access to a shared directory. In order for the job to run successfully, I allow access for each target server SQL Agent service group. Since this MSX job runs on 3 different servers, I need to find the service group for each server’s SQL Agent and then give each service group access to the shared directory.

Although the code is deployed to Datahub (Master Server), the SQL Server Agent job actually executes on the Target Server. The target server will send messages back to the Master server and periodically download job and code changes. The Master server will be the central hub for all code and jobs.

So each Server agent job service account needs to have access to the directory the job needs to write too.

https://docs.microsoft.com/en-us/sql/ssms/agent/automated-administration-across-an-enterprise

“A master server distributes jobs to, and receives events from, target servers. A master server also stores the central copy of job definitions for jobs that are run on target servers. Target servers connect periodically to the master server to update their schedule of jobs. If a new job exists on the master server, the target server downloads the job. After the target server completes the job, it reconnects to the master server and reports the status of the job. Note that your job definition must be same when performing any database related activities.”

SQL Server Instance Configuration Settings

SQL Server Instance configurations can be changed in the properties. Right click on the SQL Server Instance and select properties.

Properties1


General page: I look under the general page for easy access to the machine (or VM) amount of RAM, processors, and if the server is clustered. Also, If you are using Always On Availability Groups, then HADR should be set to True.

To see more about the general page: Microsoft’s explanation of the General Page

InstanceGeneral


Memory page: Use this page to set the Min and Max SQL Server memory usage. I never touch the index creation or min memory per query; I just leave those at default.

Max memory should/must be changed. In order to calculate the correct settings for Min and Max SQL memory, there is a delicate balance between OS and SQL Server. If you have multiple instances of SQL Server on the same server, then both SQL Servers are trying to regulate memory at the same time. Calculating memory is not an exact science.

Just for fun, click on those radio buttons on the bottom. Switch between Config and running. Configured Values are the values you typed into the input fields. Running values is what is actually being used at this time. If Config and Running do not match, then the SQL Server Instance needs a reboot in order for the changes to take affect.

See these blogs for more help:

Instancememory


Processors page: Usually there is nothing to configure here. However, I have used this page when RedGate was hammering processor 0, so I set SQL Server to not use that one processor. I also used changed the CPU affinity when I had two instances of SQL Server running on the same server. I was able to direct SQL Server one to processors 0+1 and SQL Server two to processors 2+3. I did that so the two SQL instances would not step on each other while processing. As with the memory page, config and running show what is selected and what is actually happening, respectively.

DO NOT SELECT boost SQL Server Priority.

Processor affinity changes

Max Worker threads

Lightweight Pooling

Instanceprocessors3


Security Page: The server authentication you choose is based on the authentication you prefer. I usually use SQL Server and Windows Authentication, just in case Active Directory goes offline.

For Login Auditing, I normally use Failed logins and monitor weekly. However, at one company that stored encrypted credit card information the auditor asked us to store both failed and successful logins. Please be aware that logging successful logins on a busy system will make for VERY LARGE files in your log directory. So plan disk space accordingly. Logging is also recorded in the Windows application event log.

Set up the server proxy account to run xp_cmdshell from a SQL Server login that has less privileges than sysadmin. The last three checkboxes I have never used, but included links to documentation.

InstanceSecurity4


Connections Page:  Connections should be set to 0 (unlimited), unless you want to limit the number of connections to the instance. I always make sure that allow remote connections is checked.

Instanceconnections5


Database Settings Page: These are the default settings for every database in your instance. I like to check Compress Backup.

InstanceDBSetting6


Advanced page: My favorite Server Properties page. I have been on this page too many times to count. Most of the time I am here to configure Cost Threshold or Max DOP.

Enable Contained Databases – Allows logins to be contained within the database. All metadata is contained in the database and the contained database does not rely on the instance properties. Microsoft Contained Databases

Filestream – Used to store BLOB data (examples: Word docs, images, pdf, etc…) on disk with a pointer stored in the Filestream attribute column. Jacob Sebastian Intro to Filestream

Miscellaneous – I have never changed any of these options, but have a link to describe each one. Microsoft Advanced page properties

Network – I have never messed with these properties. SQL Soldier To fiddle with or not to fiddle with

Parallelism – Oh here it is, my favorite part. Performance 101. There are many blogs on how to configure Cost Threshold and Max DOP, but he proper configuration for those fields is all trial and error. Testing, tons of testing to figure out what the proper balance is for those fields. Never set Max DOP to the number of processors on your server or one query can take up all the threads and not leave any for other queries. I also do not let SQL Server determine what Max DOP should be, so I do not set that value at 0. I set my Max DOP in multiples of 2 or 4, just my rule of thumb, and leave at least 2 cores for other processes.

InstanceAdvanced7


Permissions Page: This topic I will leave to Microsoft. This topic requires a 3 day class. I will have to blog on this topic in the future.

Instancepermissions8_1

 

What to do when asked to create a database. First Steps

Someone has asked you to create a database for _____. Now What? Where do you go?

Step 1: What is the database for?

  • What will the database be used for? Is this a Data Warehouse, Data Mart, Reporting, or OLTP database?
  • If this database will be attached to a 3rd party tool, what are the tool requirements for the database?
  • If this database will be attached to an in house tool, what are the requirements? Don’t worry if they do not have any requirements. Sometimes we DBAs have to create the requirements for the developers.

Step 2: What version and edition of SQL Server?

  • If a 3rd party tool, what is the version the tool must have?
  • If this is the first time this database was ever created or you are designing the database. Ask management the following questions. Their answers will help decide which edition or version of SQL Server to use.
    • What is the SLA (Service Level Agreement) for up time of the instance? If there is not an SLA, create one and have them sign it. CYA (Cover your ass’ets’) . The SLA will cover topics that you need in order to decide the version of SQL Server you need.
    • What is type of High Availability do they want? Always-On? Replication?
    • What type of Failover and Disaster Recovery? Cluster? Replication? Always-on
    • Are there any special functions or SSIS/Visual Studio the developers would like to use? What versions of SQL Server work with that tool?
    • Is there a reporting tool the developers would like to use? What versions of SQL Server work with that tool?
    • Prepare a spreadsheet
      • create column headers for each edition: Standard, Enterprise, Developer, or Express (include all four editions for each version of SQL Server being evaluated) (I usually pick the most current version of SQL Server, unless directed otherwise by management.)
      • In the first row, list the license requirements and cost for each edition
      • List in each row the requirements management wants
      • Place an “X” where the edition includes the requirement.
      • Figure out your recommendation.
      • Now let management decide which edition to use.

Step 3: How much disk space is needed for the data file, index files, temp files, log files, and system files?

  • If this new database is for a 3rd party tool, find the tool database requirements.
  • If this is a new database for an internal application and the application is not designed yet. Sizing will be decided during development. In this case, sizing is an art. There are tons of blogs on how to design a database. Time to Google.
  • If this is for reporting and the database has not been designed, again turn to google to design the data mart or data warehouse.
  • If this is a current database in another edition/version of SQL Server, what is the current disk space? Is the current disk space sufficient to hold and grow for the next six months?
  • The System Admin will need you to email all the drives you would like and how much space on each one. In the old days before SAN, placement of files mattered. Now just decide how you would like to separate the files for viewing purposes. If there are no directly connected hard drives, then all the files go into one big storage server.
  • I usually have C drive for the OS, D drive for SQL Server installation and any other application installations, L drive for logs, T drive for temp files, S drive for System files, I drive for index files, and P drive for data files. Backups I place in the P drive and then move to another network directory on a separate SAN. Separate out your files however it makes logical sense to you. Try to keep consistent between all the database servers. Maybe there is a company document on what drives to place the files. Be consistent, because consistency makes finding files for maintenance easier.

 

Step 4: How many processors (CPU)?

  • If this new database is for a 3rd party tool, find the tool database requirements.
  • If this is a current database , What is the current core count? Are the cores sufficient to run the current databases on that server?
  • Do they expect a change in usage of this database?
  • If there are no changes in usage and the server runs find with the current number of processors, then stay with the same number of processors.
  • Will there be an upgrade in the Windows OS? This could affect the number of cores needed.
  • Will there be any other applications running on this VM? I hope not. Fight to keep this server for SQL only. If you lose, make sure management knows that Sql Server does its own memory and core management. The database will be slower because the application will steal resources when competing with SQL Server.

 

Step 5: How much memory?

  • If this new database is for a 3rd party tool, find the tool database requirements.
  • If this is a current database , What is the current memory? Are there any memory waits during peak times on the current database?
  • If there are memory waits during peak times, then increase the memory. This will have to be a guesstimate.
  • Will there be an upgrade in the Windows OS? This could affect the amount of memory needed. Always leave 10% of the memory for OS, the rest can be SQL Server.
  • Will there be any other applications running on this VM? I hope not, but if so the memory will need to be increased to hold the application and SQL Server.

 

In my next blog I will discuss the SQL Server Instance and Database internal settings. I hope these questions helped to decide what you need in order to install SQL Server successfully.

How to Answer DBA Interview Questions – Brent Ozar

In technology we must always stay up to date and current with our skills. We know our jobs but get nervous when we get to the job interview. Those questions on some obscure piece of SQL Server that you have never used or you used five years ago. I swear I have been a DBA for 20 years, but when I am nervous I can mess up an interview. This is where Brent Ozar is going to help us.

Brent Ozar New Courses

Filtered Index can’t use like or functions in the where

Today I needed to create a filtered index to help a large update go faster. I wanted to use a like statement, but found out I could not use like in the where clause. Thanks to google; I found another way to make my update statement faster.

Filtered index I wanted:

CREATE NONCLUSTERED INDEX [INDX_Filter_IR_URI] ON [dbo].[IR_RecMedia]

([MediaURI] ASC,[RecordingDate] ASC)

INCLUDE ( [ExpirationDate])

Where MediaURI like ‘HTTP%’;

Received ERROR Message:

Msg 156, Level 15, State 1, Line 16

Incorrect syntax near the keyword ‘like’.


Decided to use a function to create the filtered index, but this also received an error.

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20170329-200714] ON [dbo].[IR_RecMedia]

([MediaURI] ASC,[RecordingDate] ASC)

INCLUDE ( [ExpirationDate])

Where right(MediaURI, 4) = ‘HTTP’;

 

Received the Error:

Msg 10735, Level 15, State 1, Line 8

Incorrect WHERE clause for filtered index ‘NonClusteredIndex-20170329-200714’ on table ‘dbo.IR_RecMedia’.


How did I fix this issue:

I created an extra column on the table to store 1 (for true) or 0 (for false) based on if the MediaURI matched like ‘HTTP%’. Then I created an index on that column and RecordingDate. I had to change my update statement to use where HTTPbit = 1, instead of where MediaURI like ‘HTTP%’.

 

Free Webinars hosted by GroupBy

Group_By_Conference_Logo

 

Developers, Query writers, and DBAs – Register for Free Webinars hosted by GroupBy.  You do not need to attend all the sessions.

https://groupby.org/

Sessions:

Friday, April 21:

  • 8AM Eastern – How to Use Parameters Like a Pro and Boost Performance by Guy Glantser
  • 10AM – Introducing the SQL Server 2016 Query Store by Enrico van de Laar
  • Noon – Worst Practices & Less Known Limitations for Columnstore Indexes by Niko Neugebauer
  • 2PM – Gems to Help You Troubleshoot Query Performance by Pedro Lopes
  • 4PM – Crash Course on Better SQL Development by Vladimir Oselsky

Friday, April 28:

  • 8AM Eastern – SQL Server 2016 Features for Performance Tuning Lovers by Matan Yungman
  • 10AM – SAN Primer for the DBA by Doug Bernhardt
  • Noon – Bringing DevOps to the Database by Steve Jones
  • 2PM – Azure SQL Databases: A Guided Tour by Mike Walsh
  • 4PM – Hacking SQL Server by André Melancia

What to do When Failover Cluster fails

Featured Image and great Blog with an Introduction to Windows Clusters for SQL DBAs from Brent Ozar’s site: Intro to SQL Server Clusters  (Love Kendra Little’s Art)

WFC (Windows Failover Clustering or FCM Failover Cluster Manager) is used to configure the failover cluster for a clustered SQL Server database or SQL Server Always On use. If you need High availability with SQL Server then you will need a cluster. If you are a DBA, hopefully the cluster is created for you by the Sys Admin. If you need to create the cluster there are many blogs on how to create the cluster. Here is a good starting link on how to create a cluster:Microsoft page on Creating Clusters

What do you do when the cluster fails

What do you do when the cluster fails and you (the DBA) need to troubleshoot the issue.

  1. Open Windows Failover Cluster and get the node names and server names.
  2. Go to PowerShell and create the cluster log on each node (Server). This log needs to be manually created. The command is “Get-ClusterLog -Node NodeName”GetLog
  3. Review the created log (C:\Windows\Cluster\Reports\Cluster.log) for information on the time of failover and the reason why the cluster failed over. The log will be on the actual server for the node you ran the log for.
  4. Go to Failover Cluster Manager and review the Cluster Events – Same on all nodes
  5. Go to Administrative Tools\Event Viewer for each node and review the events for the time period of the cluster failover in these files:
    • Windows Logs\Application
    • Windows Logs\System
    • Applications and Services Logs\Microsoft\Hyper-V-High-Availability\Admin
    • Applications and Services Logs\Microsoft\FailoverClustering-Manager
    • Applications and Services Logs\Microsoft\FailoverClustering\Operational
  6. optional: Create an excel spreadsheet with each meaningful event from the log files. This helps me to see the events and order them. I also like to send this to management as documentation.
    • Cluster Node
    • Cluster Server
    • date
    • time
    • Where found (example: Event Viewer\Hyper-V-High-Availablity\Admin)
    • level (Error,Information, Warning, or Critical)
    • Source
    • event source
    • eventID
    • Task Category
    • Desc
  7. Go to (C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Policies\DatabaseEngine\1033\Windows Event Log Cluster Disk Resource Corruption Error.XML) on each node and review.

 

Gather all your data, review, and send an email out with the information on why the cluster failed. If you find the error is Always-On or SQL Server related, more research is needed into the SQL Server logs.

Windows Cluster LOG ERR [RCM] [GIM] ResType Virtual Machine has no resources

While reviewing my cluster log on Node A, I found the error: ERR   [RCM] [GIM] ResType Virtual Machine has no resources, not collecting local utilization info

Short Answer: If there is no Hyper-V this error can be ignored.

Long Answer:

I found the answer on Windows 2012 board

“I realize this is old, but wanted to provide some direction for those who may be seeing this error and don’t know why. At a high level, the cluster logging process uses a set of cluster resources types that it queries for health/status info. You can see these by running the PowerShell command Get-ClusterResourceType.

The problem is if you do not have a resource that matches the type, it will return an error when you have debug logging enabled. Meaning, if you are running a SQL Failover Cluster, your cluster resource type would be “SQL Server”. The error you are seeing is because there are no Virtual Machine resource types, which would come in to play in a Hyper-V cluster.

You are running SQL, not Hyper-V, so the error can be ignored, or if you know you will not be running Hyper-V at any time on the cluster, you can simply remove that cluster resource type by running the following in and admin PowerShell prompt.”

Remove-ClusterResourceType “Virtual Machine”

Size does matter: SSIS DefaultBuffer

While researching how to improve performance for SSIS code, I found two properties that should always be changed on every SSIS package data task. Unfortunately I could not find anywhere  on the web information on where to make the change, just that I should.

There are many blogs on how to calculate the values for the DefaultBufferMaxRows and DefaultBufferSize, but none show you where to make the change. Go to the bottom of this blog page for the links on How and Why to change the DefaultBuffer properties for SSIS Data Tasks.


Where to change the DefaultBuffer properties

The change can only be made on Data Flow Tasks. Make sure the properties window is showing and then click on each data task. Calculate the values for DefaultBufferMaxRows and DefaultBufferSize and then input in the values in the properties window.

BufferProperties


Blogs on Why and How to Calculate the DefaultBuffers:

Intelligent SQL – Tuning Max Buffer Rows

Desert Isle – Size does Matter

Sam Vanga – Simple Tips To Speed Up Your SSIS Data Flow

SQL Solace: Tuning Buffer Size