Interview Question of the Week #054 – Retrieve User Defined Object Details from sys.objects

Sometimes questions in the interview are extremely open ended and such questions can be very useful for candidate to play around with. For example, in one of the recent interview one of the interviewer asked the candidate that what is his favorite T-SQL script as a database administrator. The answer of the candidate was very simple, however, he was able to earn some brownie points with the help of his script.

Question: How do you retrieve user defined object details?

Answer: Here is a simple script which can help retrieve user defined object’s details with the help of sys.objects.

The sys.objects object catalog view contains a row for each user-defined, schema-scoped object that is created within a database. You can retrieve any user defined object details by querying sys.objects database.

Let us see one example of sys.objects database usage. You can run following query to retrieve all the information regarding name of the foreign key, name of the table it FK belongs and the schema owner name of the table.

USE AdventureWorks;
GO
SELECT name AS ObjectName,
OBJECT_NAME(schema_id) SchemaName,
OBJECT_NAME(parent_object_id) ParentObjectName, name, *
FROM sys.objects
WHERE TYPE = 'F'
GO

You can use any of the following in your WHERE clause and retrieve necessary information.

Object type:

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML trigger
IF = SQL inline table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
IT = Internal table

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Performance counter missing! DMV sys.dm_os_performance_counters is Empty

The topic around performance tuning is the most sought out search on this blog. Almost every single day the top search that lands here are around performance or around some random errors one might receive from the application. If you are a DBA, the first high level tool that you might be using to watch perf data is around using Perfmon data. Interestingly, these PerfMon data is actually exposed via the DMV’s too.

One of uncommon issue, which I found on one of my client SQL Server box, was to get zero rows in sys.dm_os_performance_counters dynamic management views.

My first question was – “did that ever show some values?” and the answer was “yes. It was working and it broke when we changed service account”.

My next question was – how did you change the service account? And as expected, the answer was – we have used the services. ms. And once again, I wanted to pull my hairs because of seeing same mistake again. There has been enough documentation from Microsoft, which suggests to use the SQL Server Configuration Manager. I asked them to show ERRORLOG.

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Here is what pointed to the issue.

Error: 8319, Severity: 16, State: 1.
Windows kernel object ‘Global\SQL_90_MEMOBJ_MSSQLSERVER_0’ already exists. It’s not owned by the SQL Server service account. SQL Server performance counters are disabled.
Error: 3409, Severity: 16, State: 1.

Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.

Below are the things which were done to fix the problem.

  • Change the account that start SQL Server Service and SQL Agent Service to the Local System Account.
  • Restart SQL Server Service and SQL Agent Service in order to apply the last change.
  • The Follow permissions must be granted to the SQL Server account in the Local Security Policy> * User Rights Assignment.
    • Adjust Memory for A Process
    • Log on as Service
    • Log on as a batch job
    • Locks pages in memory
    • Act as part of the operating system
    • Bypass traverse checking
    • Replace a process level token
  • Add the SQL Server Domain Account to the SQL Server Groups on the server.
  • Change the account that start SQL Server Service and SQL Agent Service to the SQL Server Domain Account.
  • Restart SQL Server service and SQL Server Agent Service.
  • Check your ERRORLOG and performance counters.

This is one of the reasons, I strongly recommend changing the service account from the SQL Server Configuration Manager. Above worked for my friend. Have you faced some similar issue?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Starting / Stopping SQL Server Agent Services using PowerShell

Writing utility scripts are one way to become smarter when working with computers. From time to time I get asked about some tasks folks use repetitively and are being counterproductive. On this note, I wanted to paint a scenario which I saw one of the DBA’s doing on their environment of multiple SQL Server – restarting or stopping the SQL Server Agent Services. He was painstakingly expanding each of the notes using SSMS and stopping the services.

The reason for that is beyond the scope here. But I do remember it was a highly available AlwaysOn Cluster of SQL Server running. When I saw that, I immediately asked him if this was something he wanted to automate? The immediate reaction was – “Definitely Yes. If you can.”

So gave a simple powershell script to stop the Agent Services on his patch of multiple servers. The script was as shown below:

foreach($replica in "localhost", "Server2", "Server3")
{
"StartAgent instance " + $replica.tostring() + " "
(Get-wmiobject -ComputerName  $replica Win32_Service -Filter "Name='SQLSERVERAGENT'" ).InvokeMethod("StopService",$null)
}

This quick script allowed for stopping and obviously with a simple change, I was also able to start the 3 SQL Server instances which were stopped. You might want to change this in your environments accordingly.

foreach($replica in "localhost", "Server2", "Server3")
{
"StartAgent instance " + $replica.tostring() + " "
(Get-wmiobject -ComputerName  $replica Win32_Service -Filter "Name='SQLSERVERAGENT'" ).InvokeMethod("StartService",$null)
}

But interestingly, when I did it the first time on the server – I was getting an error.

PS C:\WINDOWS\system32> E:\StartSQLAgent.PS1
File E:\StartSQLAgent.PS1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
+ CategoryInfo          : SecurityError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : UnauthorizedAccess

If you go through the error and do it as per the documentation, we need to make sure the PowerShell window has been started in “Administrator mode” and post this, I need to run the below scrip

t:

[c
ode language="powershell" gutter="false"]
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
[/code]

This will showup and big dialog box as executed from my “Windows PowerShell ISE” window:

agent start powershell 01 SQL SERVER   Starting / Stopping SQL Server Agent Services using PowerShell

Accept the same with “Yes to All” or “Yes” and we are ready to run the above script.

The more I explore and look at Windows PowerShell, more automated I seem to make processes that I start to love this technology. Do let me know if you every did something like this in your environments?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Live Query Statistics in 2016 … and More! – Notes from the Field #111

mikel SQL SERVER   Live Query Statistics in 2016 … and More!   Notes from the Field #111[Note from Pinal]: In this episode of the Notes from the Field series database expert Mike Lawell explains about Live Query Statistics in SQL Server 2016.  Statistics are heart of SQL Server. They play a very powerful role when SQL Server Engine needs help to build a new execution plan for a query. In earlier version of SQL Server we had the only privilege to see statistics after the query is executed. However, with the advancement of SQL Server 2016, the scenario has been changed. Here is a very interesting new feature of SQL Server 2016 which can help every performance tuning expert. Read the experience of  Mike in his own words.


Your boss has asked you to implement SQL Server AlwaysOn Availability Groups in your environment. Exciting as it may, you’re an accidental DBA with little to no experience implementing any high availability solution, let alone, a fairly new availability group.

The first thing you say is, fantastic! Then, hmmm, fantastic. You realize this is above your head, but it is a great opportunity to learn.

This happened to one of my clients, where the DBA barely had SQL Server Database Mirroring experience. The product had a required up time of 99.999% (yeah, right), that they were having problems maintaining. They wanted to improve their availability and business continuity.

The DBA had a lot of anxiety with implementing AGs in this environment without experiencing considerable downtime.

Fortunately they were able to engage our servic

Welcome to 2016! This is going to be an exciting year for SQL Server professionals with the upcoming release of SQL Server 2016. With this in mind I’m going to talk a little bit about one of the cool new features in SQL Server 2016.

Many of you have had the opportunity to see me talk about one of my favorite topics, Execution Plans. I had the privilege to talk about Execution Plans at PASS Summit 2015, and many SQL Saturdays across the US. You can find my presentation here on my blog site at SQL Server Associates.

Keeping in step with my love of Execution Plans, Live Query Statistics is one of the new features in SQL Server 2016 that gives me a smile.

Live Query Statistics? What’s the benefit in that? I’ve already heard it from some senior DBAs I know. Have you ever had someone ask you to performance tune a query that never completes? It just takes forever and you know you’re not about to wait for it to complete before you can see the actual execution plan? Well, here’s your chance to look at the operators in progress and as they complete.

Live Query Stats even gives you the ability to see a percentage of completion as the operator is in progress. More about that later.

Here is how you enable Live Query Statistics from Management Studio. Select Query > Include Live Query Statistics.

111 1 SQL SERVER   Live Query Statistics in 2016 … and More!   Notes from the Field #111

Voila, there you have it. Now it is as simple as running your query.

This screenshot shows the query in process. Notice the first operator “Clustered Index Scan” has completed (100%). The other operators are at 8% or 0%.

111 2 SQL SERVER   Live Query Statistics in 2016 … and More!   Notes from the Field #111

Now here is the best part, hover over the operator that is currently in process, the one you think is the issue. Even though it is “in process” you can see the details about the operator. Notice the Actual Number of Rows information? There is a lot of information here to give you a start on identifying where and what the issue might be with the query.

111 3 SQL SERVER   Live Query Statistics in 2016 … and More!   Notes from the Field #111

Once the query has completed you get an execution plan that looks like the screenshot below.

111 4 SQL SERVER   Live Query Statistics in 2016 … and More!   Notes from the Field #111

And there you go. So simple my mom could take advantage of it. Of course mom would be saying, “Michael Mark, you had better not be lying to me. It can’t be that easy!”

Really mom, it’s that simple and it is going to help you performance tune like a pro…oh, wait, sorry mom, performance tuning is a little more complicated.

Wait, but there’s more…. In what versions does this work? Well, of course SQL Server 2016…duh… but wait there’s more!

111 5 SQL SERVER   Live Query Statistics in 2016 … and More!   Notes from the Field #111

Yep, that’s right! I just ran Live Query Statistics against a SQL Server 2014 server. Isn’t that awesome?

I’ve been able to use this new feature with several of the Linchpin People clients (even though SQL Server 2016 isn’t available yet) because it works on SQL Server 2014 installations. It just doesn’t get any better than that.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – System.IO.FileNotFoundException: Could not load file or assembly

In a recent R&D operation, I was trying to use some SQLCLR assembly as part of the installation process. It was not easy but it got me into a mess that I thought was worth debugging. One of the interesting issues which I faced recently was to deploy a .net assembly which was failing with an error. I thought the third party .msi and the .dlls they shared must have gone wrong. I wanted to understand what was going wrong here to get a better feeler. The Error mentioned as part of the stack was:

Error: System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.SqlServer.BatchParserClient, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Microsoft.SqlServer.BatchParserClient, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’
at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.SqlServer.Management.Common.ServerConnection.GetStatements(String query, ExecutionTypes executionType, Int32& statementsToReverse)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

Whenever there are such errors, we need to look at a few things.

  1. What is the error?
  2. What is assembly name?
  3. What is the version of the assembly?

Here is what we have in error message. I have highlighted them as well.

  1. IO.FileNotFoundException: Could not load file or assembly
  2. SqlServer.BatchParserClient
  3. Version=12.0.0.0

The assemblies are stored in C:\Windows\Assembly folder and they are shown as below

assembly 01 SQL SERVER   System.IO.FileNotFoundException: Could not load file or assembly

Two things to be noted in the screenshot. Version and Processor Architecture. The screenshot is NOT from the same machine where we received the error. On the problem machine, we were not seeing the needed assembly.

I have downloaded and installed “Microsoft® SQL Server® 2014 Shared Management Objects” (SMO) from https://www.microsoft.com/en-us/download/details.aspx?id=42295 and after that I was able to get the assembly and the code worked. Microsoft SQL Server Management Objects require – Microsoft SQL Server System CLR Types, that was also available on the same page.

I am sure most of us sometimes don’t get on this path of exploration, but I felt this was a great learning for me to explore. Do let me know if you did debug anything on these lines at your environments via the comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX: Msg 7395, Level 16, State 2 – Unable to start a nested transaction for OLE DB provider

While playing with linked server from SQL Server to SQL Server, I faced few errors and fixed them as well. It has become a custom to get errors working with Linked Servers almost every time. I personally thought it is worth blogging because I didn’t find any single blog having the cause and solution explained in detail.

Here is the query which I was running on SMALLSERVER which has linked server to BIGSERVER. It is a four part naming having format for ServerName.DatabaseName.SchemaName.ObjectName.  Basically, query is updating a value for a table called dbo.Employee in database called SQLAuthority on server BIGSERVER.

BEGIN DISTRIBUTED TRANSACTION
UPDATE
BIGSERVER.SQLAuthority.dbo.Employee
SET SALARY = 'Y'
WHERE ID = 5
COMMIT TRANSACTION

It was failing with below error message.

OLE DB provider “SQLNCLI11” for linked server “BIGSERVER” returned message “The transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “BIGSERVER” was unable to begin a distributed transaction.

Here is the important information in error message. “The transaction manager has disabled its support for remote/network transactions” which basically indicates issue with setting of DTC. To fix that, we need to go to Start > Run > dcomcnfg and open below interface (this is Windows 2012 R2 Sevrer)

dtc 01 SQL SERVER   FIX: Msg 7395, Level 16, State 2 – Unable to start a nested transaction for OLE DB provider

You can also launch the same interface from “Control Panel” > “Administrative Tools > Component Services”. Follow the screen shot and open “Properties” and go to security tab and change setting as below.

dtc 02 SQL SERVER   FIX: Msg 7395, Level 16, State 2 – Unable to start a nested transaction for OLE DB provider

Once changed, restart the DTC Service. Now, I got below error.

OLE DB provider “SQLNCLI11” for linked server “BIGSERVER” returned message “Cannot start more transactions on this session.”.
Msg 7395, Level 16, State 2, Line 3
Unable to start a nested transaction for OLE DB provider “SQLNCLI11” for linked server “BIGSERVER”. A nested transaction was required because the XACT_ABORT option was set to OFF.

Above error also tells the action needed – we need to enable the XACT_ABORT in the transaction. Here is the modified version of the query

SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRANSACTION
UPDATE
BIGSERVER.SQLAUTHORITY.DBO.EMPLOYEE
SET SALARY = 'Y'
WHERE ID = 5
COMMIT TRANSACTION

And that worked for me and it updated the value on remote server.

You must have also encountered some errors while using linked server? Share the problem and solution via comments section.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Working with Event Viewer and PowerShell

Sometimes I go into these simple explorations that make me learn something new that gets me off the routine. SQL Server is always on the agenda and that passion will never die. This exploration was based on the fact when I was talking to someone about some of the super cool stuff people work on – I heard a DBA complain to me that he was working on a “Windows Server Core” machine and he had no UI. I was pleasantly taken aback and got back search on the internet about. I also got to know “SQL Server” can be installed on these server machines. It was fascinating for me to hear such things and I wondered, how can someone work on such machines.

I thought, let me see if we can use some sort of scripting to work on things I take for granted when troubleshooting some error messages. I always look at opening up the Windows Event Viewer to watch through the errors. Now I thought it would be great not to open up Event Viewer and yet be able to query. That was surely a challenge and I wanted to learn something new and interesting to share.

This blog is at the moment a rudimentary shot at going the scripting route using Powershell. Here are some commands that I used. Let me progress from few simple commands to little interesting, complex queries I figured out to write:

1) List the event viewer logs on a given system. This is simple because I need to know what can be queried at any point in time.

get-eventlog -list

 SQL SERVER   Working with Event Viewer and PowerShell

As you can see I have about some 27k+ messages and this is a great place to make our query.

2) Let us next try to select the newest 50 messages of application log:

Get-EventLog -LogName Application -newest 50

The next, I was wondering if we can dump on data based on a date value. This gets me to the next query that was written.

3) Gathering logs after a particular date, we can also use “before” to select the messages prior to given dates as shown below:

Get-EventLog -LogName Application -after 1/10/2016

I wanted to make the query less complex by searching on a specific Event type and Event Source. Since I work with SQL Server and the source had to be MSSQLSERVER (default instance name).

4) Selecting only the messages which are logged as “information” for a source like “MSSQLSERVER” and using a clip to basically copy the output to the clipboard:

Get-EventLog -logname application -EntryType information -newest 50 -source *MSSQLSERVER* | clip

When I was writing the above query, I was little clueless to what are valid source types we can use. So I made a query to identify the source names.

5) Find the relevant source to be used in a query:

Get-EventLog -logname "Application" 
| Select-Object Source -unique

 SQL SERVER   Working with Event Viewer and PowerShell

As you can see, if we know how to play around with Powershell and know the query we need – we can always find nice and easy way to get the data. How many SQL DBA’s who read this blog have already tried playing around with PowerShell? What have you been doing with it? Please let me know via the comments below.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #053 – What is the Difference Between Deterministic Functions and Nondeterministic Functions?

Some questions are so theoretical that I believe they really do not add too much value if users know that question or not. Here is one such question I am very confident that you agree with my point of view.

Questions: What is the Difference Between Deterministic Functions and Nondeterministic Functions?

Answer: 

Deterministic functions always return the same output result all the time it is executed for same input values. i.e. ABS, DATEDIFF, ISNULL etc.

Nondeterministic functions may return different results each time they are executed. i.e. NEWID, RAND, @@CPU_BUSY etc. Functions that call extended stored procedures are nondeterministic. User-defined functions that create side effects on the database are not recommended.

Now you have read the answer – I have a question back to you.

Did you the difference between deterministic and nondeterministic function before this blog? If no, has it ever impacted your performance in your daily job?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX: Msg 3169, Level 16, State 1 – The database was backed up on a server running version. That version is incompatible with this server.

At this rate, I think I am going to exhaust the whole error set available inside SQL Server. I am glad that I am able to get into unique situations and then resolve them too. And this is more of a diary of the error messages I am getting into. I was playing with my Demo database on SQL Server 2016. Once done, I started restore from the previous copy.  But is failed with below error:

Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 13.00.0801. That version is incompatible with this server, which is running version 12.00.4213. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Then I realized that I have taken back-up from 13.00.0801 which is SQL Server 2016 and I was trying to restore on 12.00.4213 which is SQL Server 2014. Error message shows both versions, source and destination.

So, it’s is clear that there is no direct way to downgrade from a higher version to a lower version and it would fail with error message. Why? When an instance of SQL is upgraded, not only do the binaries for the database engine change, the schema level for the databases also changes. So it would not be possible to attach a database with a higher schema level to an instance on a lower version of SQL.

What can be done? One possible approach is to manually export and import the data. You can follow the below steps:

Create empty database

  • In Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Generate Scripts. Follow the steps in the wizard to script the database objects.
  • On the Choose Objects page, select “Script entire database and database objects”.
  • On the Set Scripting Options page, select Save scripts to a specific location.
  • select the Advanced button; under “Types of data to script” select “Schema Only” and under “Script for server version” select “SQL Server 2008” (or appropriate version)
  • Click on Next and finish to complete the process.
  • Now you can use the generated script file and execute this against the SQL server 2008 instance to create the complete database schema without data.

Move the data

To move the data, you have a couple of options but all of these are a bit tricky.

  1. Script out the data exactly the way described in the above steps, just by selecting “Types of data to script” as “Data Only”.

Or

  1. Script out the data for every object individually one by one instead of complete database if you have a large database.

Or

  1. BCP to export the data into files and then again use BCP to import the data back into the tables on to the new server.

In short, moving back-up from higher to lower version is not possible. Only option we have is to move the data using the ways described above. Do you know any more ways?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SSMA Error: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

I work majorly on SQL Server but rarely do I get a chance to work on Oracle in general. When some of the error messages reach my inbox, I get intrigued to why some occur. This error message was an outcome of that exploration. When I got this error message, I said I have no clue to why this is happening and I asked if it was anyway related to SQL Server?

The gentleman wrote back stating this was coming from a SQL Server tool and since they were doing a migration using SQL Server Migration Assistant Tool (SSMA). Now that statement got me interested because it was coming from a SQL Server migration.

I went ahead to asked about the environment to get a better hang of things. I asked the Oracle client version they were running. They responded saying, though we are running Oracle Client 9.2.0.8 we are getting an Oracle 8.1.7 or higher error strangely.

Following message is being displayed on the console:

Migrating data…
Analyzing metadata…
Preparing table SYSTBL.CMN_TBL_CUST_…
Preparing data migration package…
Starting data migration agent…
Starting data migration…
Exception during processing: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
Data migration complete.

0 table(s) successfully migrated.
0 table(s) partially migrated.
1 table(s) failed to migrate.

After a going through the complete problem statement. I saw that it was working on a local server, but was failing when it was called from remote server.

Reason After Investigation

The Oracle client provider is not there on the remote SQL Server. If working with remote machines, the Oracle Client provider should be present on the remote SQL Server.

I sent a mail to – Install the Oracle Client provider on the remote SQL Server and verify the data migration. As SSMA doesn’t have any limitation to work with remote SQL Server. Also as part of the installation, asked to check if the extension packs were installed on the remote servers.

After this exercise, I felt there was some good learning for me too and worth a share here. I am sure you are much more experienced in working with SSMA but feel free to let me know if I missed anything.

Reference: Pinal Dave (http://blog.sqlauthority.com)